You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@phoenix.apache.org by ja...@apache.org on 2017/04/17 17:27:14 UTC
phoenix git commit: PHOENIX-3777 Add test for NTH_VALUE() function
with multiple grouping sets (Loknath Priyatham Teja Singamsetty)
Repository: phoenix
Updated Branches:
refs/heads/master bcceaf802 -> 8309b2271
PHOENIX-3777 Add test for NTH_VALUE() function with multiple grouping sets (Loknath Priyatham Teja Singamsetty)
Project: http://git-wip-us.apache.org/repos/asf/phoenix/repo
Commit: http://git-wip-us.apache.org/repos/asf/phoenix/commit/8309b227
Tree: http://git-wip-us.apache.org/repos/asf/phoenix/tree/8309b227
Diff: http://git-wip-us.apache.org/repos/asf/phoenix/diff/8309b227
Branch: refs/heads/master
Commit: 8309b227164eb53ffc60cb1d63635db7166d4b45
Parents: bcceaf8
Author: James Taylor <ja...@apache.org>
Authored: Mon Apr 17 10:27:14 2017 -0700
Committer: James Taylor <ja...@apache.org>
Committed: Mon Apr 17 10:27:14 2017 -0700
----------------------------------------------------------------------
.../phoenix/end2end/NthValueFunctionIT.java | 65 ++++++++++++++++++++
1 file changed, 65 insertions(+)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/phoenix/blob/8309b227/phoenix-core/src/it/java/org/apache/phoenix/end2end/NthValueFunctionIT.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/NthValueFunctionIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/NthValueFunctionIT.java
index ff0f094..80da494 100644
--- a/phoenix-core/src/it/java/org/apache/phoenix/end2end/NthValueFunctionIT.java
+++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/NthValueFunctionIT.java
@@ -61,6 +61,71 @@ public class NthValueFunctionIT extends ParallelStatsDisabledIT {
}
@Test
+ public void multipleNthValueFunctionTest() throws Exception {
+ Connection conn = DriverManager.getConnection(getUrl());
+
+ String nthValue = generateUniqueName();
+ String ddl = "CREATE TABLE IF NOT EXISTS " + nthValue + " "
+ + "(id INTEGER NOT NULL, feid UNSIGNED_LONG NOT NULL,"
+ + " uid INTEGER NOT NULL, lrd INTEGER"
+ + " CONSTRAINT PKVIEW PRIMARY KEY ( id, feid, uid))";
+ conn.createStatement().execute(ddl);
+
+ conn.createStatement().execute(
+ "UPSERT INTO " + nthValue + " (id, feid, uid, lrd) VALUES (2, 8, 1, 7)");
+ conn.createStatement().execute(
+ "UPSERT INTO " + nthValue + " (id, feid, uid, lrd) VALUES (2, 8, 2, 9)");
+ conn.createStatement().execute(
+ "UPSERT INTO " + nthValue + " (id, feid, uid, lrd) VALUES (2, 8, 3, 4)");
+ conn.createStatement().execute(
+ "UPSERT INTO " + nthValue + " (id, feid, uid, lrd) VALUES (2, 8, 4, 2)");
+ conn.createStatement().execute(
+ "UPSERT INTO " + nthValue + " (id, feid, uid, lrd) VALUES (2, 9, 5, 1)");
+ conn.createStatement().execute(
+ "UPSERT INTO " + nthValue + " (id, feid, uid, lrd) VALUES (2, 9, 6, 3)");
+ conn.createStatement().execute(
+ "UPSERT INTO " + nthValue + " (id, feid, uid, lrd) VALUES (2, 9, 8, 5)");
+ conn.createStatement().execute(
+ "UPSERT INTO " + nthValue + " (id, feid, uid, lrd) VALUES (2, 9, 7, 8)");
+ conn.createStatement().execute(
+ "UPSERT INTO " + nthValue + " (id, feid, uid, lrd) VALUES (2, 10, 5, 1)");
+ conn.createStatement().execute(
+ "UPSERT INTO " + nthValue + " (id, feid, uid, lrd) VALUES (2, 10, 6, 3)");
+ conn.createStatement().execute(
+ "UPSERT INTO " + nthValue + " (id, feid, uid, lrd) VALUES (2, 10, 7, 5)");
+ conn.createStatement().execute(
+ "UPSERT INTO " + nthValue + " (id, feid, uid, lrd) VALUES (2, 10, 8, 8)");
+ conn.createStatement().execute(
+ "UPSERT INTO " + nthValue + " (id, feid, uid, lrd) VALUES (3, 10, 5, 1)");
+ conn.createStatement().execute(
+ "UPSERT INTO " + nthValue + " (id, feid, uid, lrd) VALUES (3, 10, 6, 3)");
+ conn.createStatement().execute(
+ "UPSERT INTO " + nthValue + " (id, feid, uid, lrd) VALUES (3, 10, 7, 5)");
+ conn.createStatement().execute(
+ "UPSERT INTO " + nthValue + " (id, feid, uid, lrd) VALUES (3, 10, 8, 8)");
+
+ conn.commit();
+
+ ResultSet rs = conn.createStatement().executeQuery(
+ "SELECT NTH_VALUE(uid, 1) WITHIN GROUP (ORDER BY lrd DESC) as nth1_user_id, NTH_VALUE(uid, 2) WITHIN GROUP (ORDER BY lrd DESC) as nth2_user_id, NTH_VALUE(uid, 3) WITHIN GROUP (ORDER BY lrd DESC) as nth3_user_id FROM " + nthValue
+ + " where id=2 and feid in (8, 9, 10) GROUP BY feid");
+
+ assertTrue(rs.next());
+ assertEquals(rs.getInt(1), 2);
+ assertEquals(rs.getInt(2), 1);
+ assertEquals(rs.getInt(3), 3);
+ assertTrue(rs.next());
+ assertEquals(rs.getInt(1), 7);
+ assertEquals(rs.getInt(2), 8);
+ assertEquals(rs.getInt(3), 6);
+ assertTrue(rs.next());
+ assertEquals(rs.getInt(1), 8);
+ assertEquals(rs.getInt(2), 7);
+ assertEquals(rs.getInt(3), 6);
+ assertFalse(rs.next());
+ }
+
+ @Test
public void offsetValueAscOrder() throws Exception {
Connection conn = DriverManager.getConnection(getUrl());