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 2018/11/10 20:59:15 UTC
[12/22] phoenix git commit: PHOENIX-4981 Add tests for ORDER BY,
GROUP BY and salted tables using phoenix-spark
http://git-wip-us.apache.org/repos/asf/phoenix/blob/9bfaf183/phoenix-core/src/it/java/org/apache/phoenix/end2end/OrderByIT.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/OrderByIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/OrderByIT.java
index 578a3af..792d08f 100644
--- a/phoenix-core/src/it/java/org/apache/phoenix/end2end/OrderByIT.java
+++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/OrderByIT.java
@@ -17,17 +17,7 @@
*/
package org.apache.phoenix.end2end;
-import static org.apache.phoenix.util.TestUtil.ROW1;
-import static org.apache.phoenix.util.TestUtil.ROW2;
-import static org.apache.phoenix.util.TestUtil.ROW3;
-import static org.apache.phoenix.util.TestUtil.ROW4;
-import static org.apache.phoenix.util.TestUtil.ROW5;
-import static org.apache.phoenix.util.TestUtil.ROW6;
-import static org.apache.phoenix.util.TestUtil.ROW7;
-import static org.apache.phoenix.util.TestUtil.ROW8;
-import static org.apache.phoenix.util.TestUtil.ROW9;
import static org.apache.phoenix.util.TestUtil.TEST_PROPERTIES;
-import static org.apache.phoenix.util.TestUtil.assertResultSet;
import static org.junit.Assert.assertEquals;
import static org.junit.Assert.assertFalse;
import static org.junit.Assert.assertTrue;
@@ -40,83 +30,10 @@ import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;
-import org.apache.phoenix.jdbc.PhoenixStatement;
import org.apache.phoenix.util.PropertiesUtil;
import org.junit.Test;
-
-public class OrderByIT extends ParallelStatsDisabledIT {
-
- @Test
- public void testMultiOrderByExpr() throws Exception {
- String tenantId = getOrganizationId();
- String tableName = initATableValues(tenantId, getDefaultSplits(tenantId), getUrl());
- String query = "SELECT entity_id FROM " + tableName + " ORDER BY b_string, entity_id";
- Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
- Connection conn = DriverManager.getConnection(getUrl(), props);
- try {
- PreparedStatement statement = conn.prepareStatement(query);
- ResultSet rs = statement.executeQuery();
- assertTrue (rs.next());
- assertEquals(ROW1,rs.getString(1));
- assertTrue (rs.next());
- assertEquals(ROW4,rs.getString(1));
- assertTrue (rs.next());
- assertEquals(ROW7,rs.getString(1));
- assertTrue (rs.next());
- assertEquals(ROW2,rs.getString(1));
- assertTrue (rs.next());
- assertEquals(ROW5,rs.getString(1));
- assertTrue (rs.next());
- assertEquals(ROW8,rs.getString(1));
- assertTrue (rs.next());
- assertEquals(ROW3,rs.getString(1));
- assertTrue (rs.next());
- assertEquals(ROW6,rs.getString(1));
- assertTrue (rs.next());
- assertEquals(ROW9,rs.getString(1));
-
- assertFalse(rs.next());
- } finally {
- conn.close();
- }
- }
-
-
- @Test
- public void testDescMultiOrderByExpr() throws Exception {
- String tenantId = getOrganizationId();
- String tableName = initATableValues(tenantId, getDefaultSplits(tenantId), getUrl());
- String query = "SELECT entity_id FROM " + tableName + " ORDER BY b_string || entity_id desc";
- Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
- Connection conn = DriverManager.getConnection(getUrl(), props);
- try {
- PreparedStatement statement = conn.prepareStatement(query);
- ResultSet rs = statement.executeQuery();
- assertTrue (rs.next());
- assertEquals(ROW9,rs.getString(1));
- assertTrue (rs.next());
- assertEquals(ROW6,rs.getString(1));
- assertTrue (rs.next());
- assertEquals(ROW3,rs.getString(1));
- assertTrue (rs.next());
- assertEquals(ROW8,rs.getString(1));
- assertTrue (rs.next());
- assertEquals(ROW5,rs.getString(1));
- assertTrue (rs.next());
- assertEquals(ROW2,rs.getString(1));
- assertTrue (rs.next());
- assertEquals(ROW7,rs.getString(1));
- assertTrue (rs.next());
- assertEquals(ROW4,rs.getString(1));
- assertTrue (rs.next());
- assertEquals(ROW1,rs.getString(1));
-
- assertFalse(rs.next());
- } finally {
- conn.close();
- }
- }
+public class OrderByIT extends BaseOrderByIT {
@Test
public void testOrderByWithPosition() throws Exception {
@@ -151,8 +68,8 @@ public class OrderByIT extends ParallelStatsDisabledIT {
assertTrue(rs.next());
assertEquals(1,rs.getInt(1));
assertTrue(rs.next());
- assertEquals(1,rs.getInt(1));
- assertFalse(rs.next());
+ assertEquals(1,rs.getInt(1));
+ assertFalse(rs.next());
query = "select a_string x, col1 y from " + tableName + " order by x";
rs = conn.createStatement().executeQuery(query);
@@ -163,9 +80,9 @@ public class OrderByIT extends ParallelStatsDisabledIT {
assertEquals("b",rs.getString(1));
assertEquals(20,rs.getInt(2));
assertTrue(rs.next());
- assertEquals("c",rs.getString(1));
+ assertEquals("c",rs.getString(1));
assertEquals(30,rs.getInt(2));
- assertFalse(rs.next());
+ assertFalse(rs.next());
query = "select * from " + tableName + " order by 2";
rs = conn.createStatement().executeQuery(query);
@@ -173,113 +90,12 @@ public class OrderByIT extends ParallelStatsDisabledIT {
assertEquals("b",rs.getString(1));
assertEquals(20,rs.getInt(2));
assertTrue(rs.next());
- assertEquals("c",rs.getString(1));
- assertEquals(30,rs.getInt(2));
- assertTrue(rs.next());
- assertEquals("a",rs.getString(1));
- assertEquals(40,rs.getInt(2));
- assertFalse(rs.next());
- } finally {
- conn.close();
- }
- }
-
-
- @Test
- public void testColumnFamily() throws Exception {
- Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
- Connection conn = DriverManager.getConnection(getUrl(), props);
- conn.setAutoCommit(false);
-
- try {
- String tableName = generateUniqueName();
- String ddl = "CREATE TABLE " + tableName +
- " (a_string varchar not null, cf1.a integer, cf1.b varchar, col1 integer, cf2.c varchar, cf2.d integer, col2 integer" +
- " CONSTRAINT pk PRIMARY KEY (a_string))\n";
- createTestTable(getUrl(), ddl);
- String dml = "UPSERT INTO " + tableName + " VALUES(?,?,?,?,?,?,?)";
- PreparedStatement stmt = conn.prepareStatement(dml);
- stmt.setString(1, "a");
- stmt.setInt(2, 40);
- stmt.setString(3, "aa");
- stmt.setInt(4, 10);
- stmt.setString(5, "bb");
- stmt.setInt(6, 20);
- stmt.setInt(7, 1);
- stmt.execute();
- stmt.setString(1, "c");
- stmt.setInt(2, 30);
- stmt.setString(3, "cc");
- stmt.setInt(4, 50);
- stmt.setString(5, "dd");
- stmt.setInt(6, 60);
- stmt.setInt(7, 3);
- stmt.execute();
- stmt.setString(1, "b");
- stmt.setInt(2, 40);
- stmt.setString(3, "bb");
- stmt.setInt(4, 5);
- stmt.setString(5, "aa");
- stmt.setInt(6, 80);
- stmt.setInt(7, 2);
- stmt.execute();
- conn.commit();
-
- String query = "select * from " + tableName + " order by 2, 5";
- ResultSet rs = conn.createStatement().executeQuery(query);
- assertTrue(rs.next());
assertEquals("c",rs.getString(1));
assertEquals(30,rs.getInt(2));
- assertEquals("cc",rs.getString(3));
- assertEquals(50,rs.getInt(4));
- assertEquals("dd",rs.getString(5));
- assertEquals(60,rs.getInt(6));
- assertEquals(3,rs.getInt(7));
- assertTrue(rs.next());
- assertEquals("b",rs.getString(1));
- assertEquals(40,rs.getInt(2));
- assertEquals("bb",rs.getString(3));
- assertEquals(5,rs.getInt(4));
- assertEquals("aa",rs.getString(5));
- assertEquals(80,rs.getInt(6));
- assertEquals(2,rs.getInt(7));
- assertTrue(rs.next());
- assertEquals("a",rs.getString(1));
- assertEquals(40,rs.getInt(2));
- assertEquals("aa",rs.getString(3));
- assertEquals(10,rs.getInt(4));
- assertEquals("bb",rs.getString(5));
- assertEquals(20,rs.getInt(6));
- assertEquals(1,rs.getInt(7));
- assertFalse(rs.next());
-
- query = "select * from " + tableName + " order by 7";
- rs = conn.createStatement().executeQuery(query);
assertTrue(rs.next());
- assertEquals("a",rs.getString(1));
- assertEquals(40,rs.getInt(2));
- assertEquals("aa",rs.getString(3));
- assertEquals(10,rs.getInt(4));
- assertEquals("bb",rs.getString(5));
- assertEquals(20,rs.getInt(6));
- assertEquals(1,rs.getInt(7));
- assertTrue(rs.next());
- assertEquals("b",rs.getString(1));
+ assertEquals("a",rs.getString(1));
assertEquals(40,rs.getInt(2));
- assertEquals("bb",rs.getString(3));
- assertEquals(5,rs.getInt(4));
- assertEquals("aa",rs.getString(5));
- assertEquals(80,rs.getInt(6));
- assertEquals(2,rs.getInt(7));
- assertTrue(rs.next());
- assertEquals("c",rs.getString(1));
- assertEquals(30,rs.getInt(2));
- assertEquals("cc",rs.getString(3));
- assertEquals(50,rs.getInt(4));
- assertEquals("dd",rs.getString(5));
- assertEquals(60,rs.getInt(6));
- assertEquals(3,rs.getInt(7));
- assertFalse(rs.next());
+ assertFalse(rs.next());
} finally {
conn.close();
}
@@ -343,19 +159,19 @@ public class OrderByIT extends ParallelStatsDisabledIT {
String query = "select t1.* from " + tableName1 + " t1 join " + tableName2 + " t2 on t1.a_string = t2.a_string order by 3";
ResultSet rs = conn.createStatement().executeQuery(query);
assertTrue(rs.next());
- assertEquals("a",rs.getString(1));
+ assertEquals("a",rs.getString(1));
assertEquals(40,rs.getInt(2));
assertEquals("aa",rs.getString(3));
assertEquals(10,rs.getInt(4));
assertEquals("bb",rs.getString(5));
assertEquals(20,rs.getInt(6));
assertTrue(rs.next());
- assertEquals("b",rs.getString(1));
+ assertEquals("b",rs.getString(1));
assertEquals(40,rs.getInt(2));
assertEquals("bb",rs.getString(3));
assertEquals(5,rs.getInt(4));
assertEquals("aa",rs.getString(5));
- assertEquals(80,rs.getInt(6));
+ assertEquals(80,rs.getInt(6));
assertTrue(rs.next());
assertEquals("c",rs.getString(1));
assertEquals(30,rs.getInt(2));
@@ -363,20 +179,20 @@ public class OrderByIT extends ParallelStatsDisabledIT {
assertEquals(50,rs.getInt(4));
assertEquals("dd",rs.getString(5));
assertEquals(60,rs.getInt(6));
- assertFalse(rs.next());
+ assertFalse(rs.next());
query = "select t1.a_string, t2.col1 from " + tableName1 + " t1 join " + tableName2 + " t2 on t1.a_string = t2.a_string order by 2";
rs = conn.createStatement().executeQuery(query);
assertTrue(rs.next());
- assertEquals("b",rs.getString(1));
+ assertEquals("b",rs.getString(1));
assertEquals(20,rs.getInt(2));
assertTrue(rs.next());
- assertEquals("c",rs.getString(1));
+ assertEquals("c",rs.getString(1));
assertEquals(30,rs.getInt(2));
assertTrue(rs.next());
- assertEquals("a",rs.getString(1));
+ assertEquals("a",rs.getString(1));
assertEquals(40,rs.getInt(2));
- assertFalse(rs.next());
+ assertFalse(rs.next());
} finally {
conn.close();
}
@@ -441,24 +257,24 @@ public class OrderByIT extends ParallelStatsDisabledIT {
String query = "select a_string, cf2.d from " + tableName1 + " union all select * from " + tableName2 + " order by 2";
ResultSet rs = conn.createStatement().executeQuery(query);
assertTrue(rs.next());
- assertEquals("bb",rs.getString(1));
+ assertEquals("bb",rs.getString(1));
assertEquals(10,rs.getInt(2));
assertTrue(rs.next());
- assertEquals("a",rs.getString(1));
- assertEquals(20,rs.getInt(2));
+ assertEquals("a",rs.getString(1));
+ assertEquals(20,rs.getInt(2));
assertTrue(rs.next());
assertEquals("cc",rs.getString(1));
assertEquals(30,rs.getInt(2));
assertTrue(rs.next());
- assertEquals("aa",rs.getString(1));
+ assertEquals("aa",rs.getString(1));
assertEquals(40,rs.getInt(2));
assertTrue(rs.next());
- assertEquals("c",rs.getString(1));
- assertEquals(60,rs.getInt(2));
+ assertEquals("c",rs.getString(1));
+ assertEquals(60,rs.getInt(2));
assertTrue(rs.next());
assertEquals("b",rs.getString(1));
assertEquals(80,rs.getInt(2));
- assertFalse(rs.next());
+ assertFalse(rs.next());
} finally {
conn.close();
}
@@ -514,7 +330,8 @@ public class OrderByIT extends ParallelStatsDisabledIT {
conn.close();
}
}
-
+
+
@Test
public void testOrderByRVC() throws Exception {
Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
@@ -546,636 +363,102 @@ public class OrderByIT extends ParallelStatsDisabledIT {
}
@Test
- public void testAggregateOrderBy() throws Exception {
- Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
- Connection conn = DriverManager.getConnection(getUrl(), props);
- String tableName = generateUniqueName();
- String ddl = "create table " + tableName + " (ID VARCHAR NOT NULL PRIMARY KEY, VAL1 VARCHAR, VAL2 INTEGER)";
- conn.createStatement().execute(ddl);
-
- conn.createStatement().execute("upsert into " + tableName + " values ('ABC','aa123', 11)");
- conn.createStatement().execute("upsert into " + tableName + " values ('ABD','ba124', 1)");
- conn.createStatement().execute("upsert into " + tableName + " values ('ABE','cf125', 13)");
- conn.createStatement().execute("upsert into " + tableName + " values ('ABF','dan126', 4)");
- conn.createStatement().execute("upsert into " + tableName + " values ('ABG','elf127', 15)");
- conn.createStatement().execute("upsert into " + tableName + " values ('ABH','fan128', 6)");
- conn.createStatement().execute("upsert into " + tableName + " values ('AAA','get211', 100)");
- conn.createStatement().execute("upsert into " + tableName + " values ('AAB','hat212', 7)");
- conn.createStatement().execute("upsert into " + tableName + " values ('AAC','aap12', 2)");
- conn.createStatement().execute("upsert into " + tableName + " values ('AAD','ball12', 3)");
- conn.createStatement().execute("upsert into " + tableName + " values ('AAE','inn2110', 13)");
- conn.createStatement().execute("upsert into " + tableName + " values ('AAF','key2112', 40)");
- conn.commit();
-
- ResultSet rs;
- PhoenixStatement stmt = conn.createStatement().unwrap(PhoenixStatement.class);
- rs = stmt.executeQuery("select distinct ID, VAL1, VAL2 from " + tableName + " where ID in ('ABC','ABD','ABE','ABF','ABG','ABH','AAA', 'AAB', 'AAC','AAD','AAE','AAF') order by VAL1");
- assertFalse(stmt.getQueryPlan().getOrderBy().getOrderByExpressions().isEmpty());
- assertTrue(rs.next());
- assertEquals("ABC", rs.getString(1));
- assertEquals("aa123", rs.getString(2));
- assertTrue(rs.next());
- assertEquals("aap12", rs.getString(2));
- assertTrue(rs.next());
- assertEquals("ba124", rs.getString(2));
- assertTrue(rs.next());
- assertEquals("ball12", rs.getString(2));
- assertTrue(rs.next());
- assertEquals("cf125", rs.getString(2));
- assertTrue(rs.next());
- assertEquals("dan126", rs.getString(2));
- assertTrue(rs.next());
- assertEquals("elf127", rs.getString(2));
- assertTrue(rs.next());
- assertEquals("fan128", rs.getString(2));
- assertTrue(rs.next());
- assertEquals("get211", rs.getString(2));
- assertTrue(rs.next());
- assertEquals("hat212", rs.getString(2));
- assertTrue(rs.next());
- assertEquals("inn2110", rs.getString(2));
- assertTrue(rs.next());
- assertEquals("AAF", rs.getString(1));
- assertEquals("key2112", rs.getString(2));
- assertFalse(rs.next());
- }
-
- @Test
- public void testAggregateOptimizedOutOrderBy() throws Exception {
+ public void testColumnFamily() throws Exception {
Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
Connection conn = DriverManager.getConnection(getUrl(), props);
- String tableName = generateUniqueName();
- String ddl = "create table " + tableName + " (K1 VARCHAR NOT NULL, K2 VARCHAR NOT NULL, VAL1 VARCHAR, VAL2 INTEGER, CONSTRAINT pk PRIMARY KEY(K1,K2))";
- conn.createStatement().execute(ddl);
-
- conn.createStatement().execute("upsert into " + tableName + " values ('ABC','ABC','aa123', 11)");
- conn.createStatement().execute("upsert into " + tableName + " values ('ABD','ABC','ba124', 1)");
- conn.createStatement().execute("upsert into " + tableName + " values ('ABE','ABC','cf125', 13)");
- conn.createStatement().execute("upsert into " + tableName + " values ('ABF','ABC','dan126', 4)");
- conn.createStatement().execute("upsert into " + tableName + " values ('ABG','ABC','elf127', 15)");
- conn.createStatement().execute("upsert into " + tableName + " values ('ABH','ABC','fan128', 6)");
- conn.createStatement().execute("upsert into " + tableName + " values ('AAA','ABC','get211', 100)");
- conn.createStatement().execute("upsert into " + tableName + " values ('AAB','ABC','hat212', 7)");
- conn.createStatement().execute("upsert into " + tableName + " values ('AAC','ABC','aap12', 2)");
- conn.createStatement().execute("upsert into " + tableName + " values ('AAD','ABC','ball12', 3)");
- conn.createStatement().execute("upsert into " + tableName + " values ('AAE','ABC','inn2110', 13)");
- conn.createStatement().execute("upsert into " + tableName + " values ('AAF','ABC','key2112', 40)");
- conn.commit();
-
- ResultSet rs;
- PhoenixStatement stmt = conn.createStatement().unwrap(PhoenixStatement.class);
- rs = stmt.executeQuery("select distinct K2, VAL1, VAL2 from " + tableName + " where K2 = 'ABC' order by VAL1");
- assertTrue(stmt.getQueryPlan().getOrderBy().getOrderByExpressions().isEmpty());
- assertTrue(rs.next());
- assertEquals("ABC", rs.getString(1));
- assertEquals("aa123", rs.getString(2));
- assertTrue(rs.next());
- assertEquals("aap12", rs.getString(2));
- assertTrue(rs.next());
- assertEquals("ba124", rs.getString(2));
- assertTrue(rs.next());
- assertEquals("ball12", rs.getString(2));
- assertTrue(rs.next());
- assertEquals("cf125", rs.getString(2));
- assertTrue(rs.next());
- assertEquals("dan126", rs.getString(2));
- assertTrue(rs.next());
- assertEquals("elf127", rs.getString(2));
- assertTrue(rs.next());
- assertEquals("fan128", rs.getString(2));
- assertTrue(rs.next());
- assertEquals("get211", rs.getString(2));
- assertTrue(rs.next());
- assertEquals("hat212", rs.getString(2));
- assertTrue(rs.next());
- assertEquals("inn2110", rs.getString(2));
- assertTrue(rs.next());
- assertEquals("ABC", rs.getString(1));
- assertEquals("key2112", rs.getString(2));
- assertFalse(rs.next());
- }
-
- @Test
- public void testNullsLastWithDesc() throws Exception {
- Connection conn=null;
- try {
- Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
- conn = DriverManager.getConnection(getUrl(), props);
-
- String tableName=generateUniqueName();
- String sql="CREATE TABLE "+tableName+" ( "+
- "ORGANIZATION_ID VARCHAR,"+
- "CONTAINER_ID VARCHAR,"+
- "ENTITY_ID VARCHAR NOT NULL,"+
- "CONSTRAINT TEST_PK PRIMARY KEY ( "+
- "ORGANIZATION_ID DESC,"+
- "CONTAINER_ID DESC,"+
- "ENTITY_ID"+
- "))";
- conn.createStatement().execute(sql);
-
- conn.createStatement().execute("UPSERT INTO "+tableName+" VALUES ('a',null,'11')");
- conn.createStatement().execute("UPSERT INTO "+tableName+" VALUES (null,'2','22')");
- conn.createStatement().execute("UPSERT INTO "+tableName+" VALUES ('c','3','33')");
- conn.commit();
-
- //-----ORGANIZATION_ID
-
- sql="SELECT CONTAINER_ID,ORGANIZATION_ID FROM "+tableName+" order by ORGANIZATION_ID ASC NULLS FIRST";
- ResultSet rs=conn.prepareStatement(sql).executeQuery();
- assertResultSet(rs, new String[][]{{"2",null},{null,"a"},{"3","c"},});
-
- sql="SELECT CONTAINER_ID,ORGANIZATION_ID FROM "+tableName+" order by ORGANIZATION_ID ASC NULLS LAST";
- rs=conn.prepareStatement(sql).executeQuery();
- assertResultSet(rs, new String[][]{{null,"a"},{"3","c"},{"2",null}});
-
- sql="SELECT CONTAINER_ID,ORGANIZATION_ID FROM "+tableName+" order by ORGANIZATION_ID DESC NULLS FIRST";
- rs=conn.prepareStatement(sql).executeQuery();
- assertResultSet(rs, new String[][]{{"2",null},{"3","c"},{null,"a"}});
-
- sql="SELECT CONTAINER_ID,ORGANIZATION_ID FROM "+tableName+" order by ORGANIZATION_ID DESC NULLS LAST";
- rs=conn.prepareStatement(sql).executeQuery();
- assertResultSet(rs, new String[][]{{"3","c"},{null,"a"},{"2",null}});
-
- //----CONTAINER_ID
-
- sql="SELECT CONTAINER_ID,ORGANIZATION_ID FROM "+tableName+" order by CONTAINER_ID ASC NULLS FIRST";
- rs=conn.prepareStatement(sql).executeQuery();
- assertResultSet(rs, new String[][]{{null,"a"},{"2",null},{"3","c"}});
-
- sql="SELECT CONTAINER_ID,ORGANIZATION_ID FROM "+tableName+" order by CONTAINER_ID ASC NULLS LAST";
- rs=conn.prepareStatement(sql).executeQuery();
- assertResultSet(rs, new String[][]{{"2",null},{"3","c"},{null,"a"}});
-
- sql="SELECT CONTAINER_ID,ORGANIZATION_ID FROM "+tableName+" order by CONTAINER_ID DESC NULLS FIRST";
- rs=conn.prepareStatement(sql).executeQuery();
- assertResultSet(rs, new String[][]{{null,"a"},{"3","c"},{"2",null}});
-
- sql="SELECT CONTAINER_ID,ORGANIZATION_ID FROM "+tableName+" order by CONTAINER_ID DESC NULLS LAST";
- rs=conn.prepareStatement(sql).executeQuery();
- assertResultSet(rs, new String[][]{{"3","c"},{"2",null},{null,"a"}});
-
- conn.createStatement().execute("UPSERT INTO "+tableName+" VALUES (null,null,'44')");
- conn.commit();
-
- //-----ORGANIZATION_ID ASC CONTAINER_ID ASC
-
- sql="SELECT CONTAINER_ID,ORGANIZATION_ID FROM "+tableName+" order by ORGANIZATION_ID NULLS FIRST,CONTAINER_ID NULLS FIRST";
- rs=conn.prepareStatement(sql).executeQuery();
- assertResultSet(rs, new String[][]{{null,null},{"2",null},{null,"a"},{"3","c"}});
-
- sql="SELECT CONTAINER_ID,ORGANIZATION_ID FROM "+tableName+" order by ORGANIZATION_ID NULLS FIRST,CONTAINER_ID NULLS LAST";
- rs=conn.prepareStatement(sql).executeQuery();
- assertResultSet(rs, new String[][]{{"2",null},{null,null},{null,"a"},{"3","c"}});
-
- sql="SELECT CONTAINER_ID,ORGANIZATION_ID FROM "+tableName+" order by ORGANIZATION_ID NULLS LAST,CONTAINER_ID NULLS FIRST";
- rs=conn.prepareStatement(sql).executeQuery();
- assertResultSet(rs, new String[][]{{null,"a"},{"3","c"},{null,null},{"2",null}});
-
- sql="SELECT CONTAINER_ID,ORGANIZATION_ID FROM "+tableName+" order by ORGANIZATION_ID NULLS LAST,CONTAINER_ID NULLS LAST";
- rs=conn.prepareStatement(sql).executeQuery();
- assertResultSet(rs, new String[][]{{null,"a"},{"3","c"},{"2",null},{null,null}});
-
-
- //-----ORGANIZATION_ID ASC CONTAINER_ID DESC
-
- sql="SELECT CONTAINER_ID,ORGANIZATION_ID FROM "+tableName+" order by ORGANIZATION_ID NULLS FIRST,CONTAINER_ID DESC NULLS FIRST";
- rs=conn.prepareStatement(sql).executeQuery();
- assertResultSet(rs, new String[][]{{null,null},{"2",null},{null,"a"},{"3","c"}});
-
- sql="SELECT CONTAINER_ID,ORGANIZATION_ID FROM "+tableName+" order by ORGANIZATION_ID NULLS FIRST,CONTAINER_ID DESC NULLS LAST";
- rs=conn.prepareStatement(sql).executeQuery();
- assertResultSet(rs, new String[][]{{"2",null},{null,null},{null,"a"},{"3","c"}});
-
- sql="SELECT CONTAINER_ID,ORGANIZATION_ID FROM "+tableName+" order by ORGANIZATION_ID NULLS LAST,CONTAINER_ID DESC NULLS FIRST";
- rs=conn.prepareStatement(sql).executeQuery();
- assertResultSet(rs, new String[][]{{null,"a"},{"3","c"},{null,null},{"2",null}});
-
- sql="SELECT CONTAINER_ID,ORGANIZATION_ID FROM "+tableName+" order by ORGANIZATION_ID NULLS LAST,CONTAINER_ID DESC NULLS LAST";
- rs=conn.prepareStatement(sql).executeQuery();
- assertResultSet(rs, new String[][]{{null,"a"},{"3","c"},{"2",null},{null,null}});
-
- //-----ORGANIZATION_ID DESC CONTAINER_ID ASC
-
- sql="SELECT CONTAINER_ID,ORGANIZATION_ID FROM "+tableName+" order by ORGANIZATION_ID DESC NULLS FIRST,CONTAINER_ID NULLS FIRST";
- rs=conn.prepareStatement(sql).executeQuery();
- assertResultSet(rs, new String[][]{{null,null},{"2",null},{"3","c"},{null,"a"}});
-
- sql="SELECT CONTAINER_ID,ORGANIZATION_ID FROM "+tableName+" order by ORGANIZATION_ID DESC NULLS FIRST,CONTAINER_ID NULLS LAST";
- rs=conn.prepareStatement(sql).executeQuery();
- assertResultSet(rs, new String[][]{{"2",null},{null,null},{"3","c"},{null,"a"}});
-
- sql="SELECT CONTAINER_ID,ORGANIZATION_ID FROM "+tableName+" order by ORGANIZATION_ID DESC NULLS LAST,CONTAINER_ID NULLS FIRST";
- rs=conn.prepareStatement(sql).executeQuery();
- assertResultSet(rs, new String[][]{{"3","c"},{null,"a"},{null,null},{"2",null}});
-
- sql="SELECT CONTAINER_ID,ORGANIZATION_ID FROM "+tableName+" order by ORGANIZATION_ID DESC NULLS LAST,CONTAINER_ID NULLS LAST";
- rs=conn.prepareStatement(sql).executeQuery();
- assertResultSet(rs, new String[][]{{"3","c"},{null,"a"},{"2",null},{null,null}});
-
- //-----ORGANIZATION_ID DESC CONTAINER_ID DESC
-
- sql="SELECT CONTAINER_ID,ORGANIZATION_ID FROM "+tableName+" order by ORGANIZATION_ID DESC NULLS FIRST,CONTAINER_ID DESC NULLS FIRST";
- rs=conn.prepareStatement(sql).executeQuery();
- assertResultSet(rs, new String[][]{{null,null},{"2",null},{"3","c"},{null,"a"}});
-
- sql="SELECT CONTAINER_ID,ORGANIZATION_ID FROM "+tableName+" order by ORGANIZATION_ID DESC NULLS FIRST,CONTAINER_ID DESC NULLS LAST";
- rs=conn.prepareStatement(sql).executeQuery();
- assertResultSet(rs, new String[][]{{"2",null},{null,null},{"3","c"},{null,"a"}});
-
- sql="SELECT CONTAINER_ID,ORGANIZATION_ID FROM "+tableName+" order by ORGANIZATION_ID DESC NULLS LAST,CONTAINER_ID DESC NULLS FIRST";
- rs=conn.prepareStatement(sql).executeQuery();
- assertResultSet(rs, new String[][]{{"3","c"},{null,"a"},{null,null},{"2",null}});
-
- sql="SELECT CONTAINER_ID,ORGANIZATION_ID FROM "+tableName+" order by ORGANIZATION_ID DESC NULLS LAST,CONTAINER_ID DESC NULLS LAST";
- rs=conn.prepareStatement(sql).executeQuery();
- assertResultSet(rs, new String[][]{{"3","c"},{null,"a"},{"2",null},{null,null}});
-
- //-----CONTAINER_ID ASC ORGANIZATION_ID ASC
-
- sql="SELECT CONTAINER_ID,ORGANIZATION_ID FROM "+tableName+" order by CONTAINER_ID NULLS FIRST,ORGANIZATION_ID NULLS FIRST";
- rs=conn.prepareStatement(sql).executeQuery();
- assertResultSet(rs, new String[][]{{null,null},{null,"a"},{"2",null},{"3","c"}});
-
- sql="SELECT CONTAINER_ID,ORGANIZATION_ID FROM "+tableName+" order by CONTAINER_ID NULLS FIRST,ORGANIZATION_ID NULLS LAST";
- rs=conn.prepareStatement(sql).executeQuery();
- assertResultSet(rs, new String[][]{{null,"a"},{null,null},{"2",null},{"3","c"}});
-
- sql="SELECT CONTAINER_ID,ORGANIZATION_ID FROM "+tableName+" order by CONTAINER_ID NULLS LAST,ORGANIZATION_ID NULLS FIRST";
- rs=conn.prepareStatement(sql).executeQuery();
- assertResultSet(rs, new String[][]{{"2",null},{"3","c"},{null,null},{null,"a"}});
-
- sql="SELECT CONTAINER_ID,ORGANIZATION_ID FROM "+tableName+" order by CONTAINER_ID NULLS LAST,ORGANIZATION_ID NULLS LAST";
- rs=conn.prepareStatement(sql).executeQuery();
- assertResultSet(rs, new String[][]{{"2",null},{"3","c"},{null,"a"},{null,null}});
-
- //-----CONTAINER_ID ASC ORGANIZATION_ID DESC
-
- sql="SELECT CONTAINER_ID,ORGANIZATION_ID FROM "+tableName+" order by CONTAINER_ID ASC NULLS FIRST,ORGANIZATION_ID DESC NULLS FIRST";
- rs=conn.prepareStatement(sql).executeQuery();
- assertResultSet(rs, new String[][]{{null,null},{null,"a"},{"2",null},{"3","c"}});
-
- sql="SELECT CONTAINER_ID,ORGANIZATION_ID FROM "+tableName+" order by CONTAINER_ID ASC NULLS FIRST,ORGANIZATION_ID DESC NULLS LAST";
- rs=conn.prepareStatement(sql).executeQuery();
- assertResultSet(rs, new String[][]{{null,"a"},{null,null},{"2",null},{"3","c"}});
-
- sql="SELECT CONTAINER_ID,ORGANIZATION_ID FROM "+tableName+" order by CONTAINER_ID ASC NULLS LAST,ORGANIZATION_ID DESC NULLS FIRST";
- rs=conn.prepareStatement(sql).executeQuery();
- assertResultSet(rs, new String[][]{{"2",null},{"3","c"},{null,null},{null,"a"}});
-
- sql="SELECT CONTAINER_ID,ORGANIZATION_ID FROM "+tableName+" order by CONTAINER_ID ASC NULLS LAST,ORGANIZATION_ID DESC NULLS LAST";
- rs=conn.prepareStatement(sql).executeQuery();
- assertResultSet(rs, new String[][]{{"2",null},{"3","c"},{null,"a"},{null,null}});
-
- //-----CONTAINER_ID DESC ORGANIZATION_ID ASC
-
- sql="SELECT CONTAINER_ID,ORGANIZATION_ID FROM "+tableName+" order by CONTAINER_ID DESC NULLS FIRST,ORGANIZATION_ID ASC NULLS FIRST";
- rs=conn.prepareStatement(sql).executeQuery();
- assertResultSet(rs, new String[][]{{null,null},{null,"a"},{"3","c"},{"2",null}});
-
- sql="SELECT CONTAINER_ID,ORGANIZATION_ID FROM "+tableName+" order by CONTAINER_ID DESC NULLS FIRST,ORGANIZATION_ID ASC NULLS LAST";
- rs=conn.prepareStatement(sql).executeQuery();
- assertResultSet(rs, new String[][]{{null,"a"},{null,null},{"3","c"},{"2",null}});
-
- sql="SELECT CONTAINER_ID,ORGANIZATION_ID FROM "+tableName+" order by CONTAINER_ID DESC NULLS LAST,ORGANIZATION_ID ASC NULLS FIRST";
- rs=conn.prepareStatement(sql).executeQuery();
- assertResultSet(rs, new String[][]{{"3","c"},{"2",null},{null,null},{null,"a"}});
-
- sql="SELECT CONTAINER_ID,ORGANIZATION_ID FROM "+tableName+" order by CONTAINER_ID DESC NULLS LAST,ORGANIZATION_ID ASC NULLS LAST";
- rs=conn.prepareStatement(sql).executeQuery();
- assertResultSet(rs, new String[][]{{"3","c"},{"2",null},{null,"a"},{null,null}});
-
- //-----CONTAINER_ID DESC ORGANIZATION_ID DESC
-
- sql="SELECT CONTAINER_ID,ORGANIZATION_ID FROM "+tableName+" order by CONTAINER_ID DESC NULLS FIRST,ORGANIZATION_ID DESC NULLS FIRST";
- rs=conn.prepareStatement(sql).executeQuery();
- assertResultSet(rs, new String[][]{{null,null},{null,"a"},{"3","c"},{"2",null}});
-
- sql="SELECT CONTAINER_ID,ORGANIZATION_ID FROM "+tableName+" order by CONTAINER_ID DESC NULLS FIRST,ORGANIZATION_ID DESC NULLS LAST";
- rs=conn.prepareStatement(sql).executeQuery();
- assertResultSet(rs, new String[][]{{null,"a"},{null,null},{"3","c"},{"2",null}});
-
- sql="SELECT CONTAINER_ID,ORGANIZATION_ID FROM "+tableName+" order by CONTAINER_ID DESC NULLS LAST,ORGANIZATION_ID DESC NULLS FIRST";
- rs=conn.prepareStatement(sql).executeQuery();
- assertResultSet(rs, new String[][]{{"3","c"},{"2",null},{null,null},{null,"a"}});
-
- sql="SELECT CONTAINER_ID,ORGANIZATION_ID FROM "+tableName+" order by CONTAINER_ID DESC NULLS LAST,ORGANIZATION_ID DESC NULLS LAST";
- rs=conn.prepareStatement(sql).executeQuery();
- assertResultSet(rs, new String[][]{{"3","c"},{"2",null},{null,"a"},{null,null}});
- } finally {
- if(conn!=null) {
- conn.close();
- }
- }
- }
-
- @Test
- public void testOrderByReverseOptimization() throws Exception {
- for(boolean salted: new boolean[]{true,false}) {
- doTestOrderByReverseOptimization(salted,true,true,true);
- doTestOrderByReverseOptimization(salted,true,true,false);
- doTestOrderByReverseOptimization(salted,true,false,true);
- doTestOrderByReverseOptimization(salted,true,false,false);
- doTestOrderByReverseOptimization(salted,false,true,true);
- doTestOrderByReverseOptimization(salted,false,true,false);
- doTestOrderByReverseOptimization(salted,false,false,true);
- doTestOrderByReverseOptimization(salted,false,false,false);
- }
- }
-
- private void doTestOrderByReverseOptimization(boolean salted,boolean desc1,boolean desc2,boolean desc3) throws Exception {
- Connection conn = null;
- try {
- Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
- conn = DriverManager.getConnection(getUrl(), props);
- String tableName=generateUniqueName();
- String sql="CREATE TABLE "+tableName+" ( "+
- "ORGANIZATION_ID INTEGER NOT NULL,"+
- "CONTAINER_ID INTEGER NOT NULL,"+
- "SCORE INTEGER NOT NULL,"+
- "ENTITY_ID INTEGER NOT NULL,"+
- "CONSTRAINT TEST_PK PRIMARY KEY ( "+
- "ORGANIZATION_ID" +(desc1 ? " DESC" : "" )+","+
- "CONTAINER_ID"+(desc2 ? " DESC" : "" )+","+
- "SCORE"+(desc3 ? " DESC" : "" )+","+
- "ENTITY_ID"+
- ")) "+(salted ? "SALT_BUCKETS =4" : "split on(4)");
- conn.createStatement().execute(sql);
-
- conn.createStatement().execute("UPSERT INTO "+tableName+" VALUES (1,1,1,1)");
- conn.createStatement().execute("UPSERT INTO "+tableName+" VALUES (2,2,2,2)");
- conn.createStatement().execute("UPSERT INTO "+tableName+" VALUES (3,3,3,3)");
- conn.createStatement().execute("UPSERT INTO "+tableName+" VALUES (4,4,4,4)");
- conn.createStatement().execute("UPSERT INTO "+tableName+" VALUES (5,5,5,5)");
- conn.createStatement().execute("UPSERT INTO "+tableName+" VALUES (6,6,6,6)");
- conn.createStatement().execute("UPSERT INTO "+tableName+" VALUES (1,1,1,11)");
- conn.createStatement().execute("UPSERT INTO "+tableName+" VALUES (2,2,2,22)");
- conn.createStatement().execute("UPSERT INTO "+tableName+" VALUES (3,3,3,33)");
- conn.createStatement().execute("UPSERT INTO "+tableName+" VALUES (4,4,4,44)");
- conn.createStatement().execute("UPSERT INTO "+tableName+" VALUES (5,5,5,55)");
- conn.createStatement().execute("UPSERT INTO "+tableName+" VALUES (6,6,6,66)");
- conn.commit();
-
- //groupBy orderPreserving orderBy asc asc
- sql="SELECT ORGANIZATION_ID,CONTAINER_ID FROM "+tableName+" group by ORGANIZATION_ID, CONTAINER_ID ORDER BY ORGANIZATION_ID ASC, CONTAINER_ID ASC";
- ResultSet rs=conn.prepareStatement(sql).executeQuery();
- assertResultSet(rs, new Object[][]{{1,1},{2,2},{3,3},{4,4},{5,5},{6,6}});
-
- //groupBy orderPreserving orderBy asc desc
- sql="SELECT ORGANIZATION_ID,CONTAINER_ID FROM "+tableName+" group by ORGANIZATION_ID, CONTAINER_ID ORDER BY ORGANIZATION_ID ASC, CONTAINER_ID desc";
- rs=conn.prepareStatement(sql).executeQuery();
- assertResultSet(rs, new Object[][]{{1,1},{2,2},{3,3},{4,4},{5,5},{6,6}});
-
- //groupBy orderPreserving orderBy desc asc
- sql="SELECT ORGANIZATION_ID,CONTAINER_ID FROM "+tableName+" group by ORGANIZATION_ID, CONTAINER_ID ORDER BY ORGANIZATION_ID DESC, CONTAINER_ID DESC";
- rs=conn.prepareStatement(sql).executeQuery();
- assertResultSet(rs, new Object[][]{{6,6},{5,5},{4,4},{3,3},{2,2},{1,1}});
-
- //groupBy orderPreserving orderBy desc desc
- sql="SELECT ORGANIZATION_ID,CONTAINER_ID FROM "+tableName+" group by ORGANIZATION_ID, CONTAINER_ID ORDER BY ORGANIZATION_ID DESC, CONTAINER_ID DESC";
- rs=conn.prepareStatement(sql).executeQuery();
- assertResultSet(rs, new Object[][]{{6,6},{5,5},{4,4},{3,3},{2,2},{1,1}});
-
- //groupBy not orderPreserving orderBy asc asc
- sql="SELECT ORGANIZATION_ID,SCORE FROM "+tableName+" group by ORGANIZATION_ID, SCORE ORDER BY ORGANIZATION_ID ASC, SCORE ASC";
- rs=conn.prepareStatement(sql).executeQuery();
- assertResultSet(rs, new Object[][]{{1,1},{2,2},{3,3},{4,4},{5,5},{6,6}});
-
- //groupBy not orderPreserving orderBy asc desc
- sql="SELECT ORGANIZATION_ID,SCORE FROM "+tableName+" group by ORGANIZATION_ID, SCORE ORDER BY ORGANIZATION_ID ASC, SCORE ASC";
- rs=conn.prepareStatement(sql).executeQuery();
- assertResultSet(rs, new Object[][]{{1,1},{2,2},{3,3},{4,4},{5,5},{6,6}});
-
- //groupBy not orderPreserving orderBy desc asc
- sql="SELECT ORGANIZATION_ID,SCORE FROM "+tableName+" group by ORGANIZATION_ID, SCORE ORDER BY ORGANIZATION_ID DESC, SCORE DESC";
- rs=conn.prepareStatement(sql).executeQuery();
- assertResultSet(rs, new Object[][]{{6,6},{5,5},{4,4},{3,3},{2,2},{1,1}});
-
- //groupBy not orderPreserving orderBy desc desc
- sql="SELECT ORGANIZATION_ID,SCORE FROM "+tableName+" group by ORGANIZATION_ID, SCORE ORDER BY ORGANIZATION_ID DESC, SCORE DESC";
- rs=conn.prepareStatement(sql).executeQuery();
- assertResultSet(rs, new Object[][]{{6,6},{5,5},{4,4},{3,3},{2,2},{1,1}});
- } finally {
- if(conn!=null) {
- conn.close();
- }
- }
- }
-
- @Test
- public void testOrderByReverseOptimizationWithNullsLast() throws Exception{
- for(boolean salted: new boolean[]{true,false}) {
- doTestOrderByReverseOptimizationWithNullsLast(salted,true,true,true);
- doTestOrderByReverseOptimizationWithNullsLast(salted,true,true,false);
- doTestOrderByReverseOptimizationWithNullsLast(salted,true,false,true);
- doTestOrderByReverseOptimizationWithNullsLast(salted,true,false,false);
- doTestOrderByReverseOptimizationWithNullsLast(salted,false,true,true);
- doTestOrderByReverseOptimizationWithNullsLast(salted,false,true,false);
- doTestOrderByReverseOptimizationWithNullsLast(salted,false,false,true);
- doTestOrderByReverseOptimizationWithNullsLast(salted,false,false,false);
- }
- }
+ conn.setAutoCommit(false);
- private void doTestOrderByReverseOptimizationWithNullsLast(boolean salted,boolean desc1,boolean desc2,boolean desc3) throws Exception {
- Connection conn = null;
try {
- Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
- conn = DriverManager.getConnection(getUrl(), props);
- String tableName=generateUniqueName();
- String sql="CREATE TABLE "+tableName+" ( "+
- "ORGANIZATION_ID VARCHAR,"+
- "CONTAINER_ID VARCHAR,"+
- "SCORE VARCHAR,"+
- "ENTITY_ID VARCHAR NOT NULL,"+
- "CONSTRAINT TEST_PK PRIMARY KEY ( "+
- "ORGANIZATION_ID" +(desc1 ? " DESC" : "" )+","+
- "CONTAINER_ID"+(desc2 ? " DESC" : "" )+","+
- "SCORE"+(desc3 ? " DESC" : "" )+","+
- "ENTITY_ID"+
- ")) "+(salted ? "SALT_BUCKETS =4" : "split on('4')");
- conn.createStatement().execute(sql);
-
- for(int i=1;i<=6;i++) {
- conn.createStatement().execute("UPSERT INTO "+tableName+" VALUES (null,'"+i+"','"+i+"','"+i+"')");
- conn.createStatement().execute("UPSERT INTO "+tableName+" VALUES (null,'"+i+"',null,'"+i+"')");
- conn.createStatement().execute("UPSERT INTO "+tableName+" VALUES (null,null,'"+i+"','"+i+"')");
- conn.createStatement().execute("UPSERT INTO "+tableName+" VALUES (null,null,null,'"+i+"')");
- conn.createStatement().execute("UPSERT INTO "+tableName+" VALUES ('"+i+"','"+i+"','"+i+"','"+i+"')");
- conn.createStatement().execute("UPSERT INTO "+tableName+" VALUES ('"+i+"','"+i+"',null,'"+i+"')");
- conn.createStatement().execute("UPSERT INTO "+tableName+" VALUES ('"+i+"',null,'"+i+"','"+i+"')");
- conn.createStatement().execute("UPSERT INTO "+tableName+" VALUES ('"+i+"',null,null,'"+i+"')");
- }
- conn.createStatement().execute("UPSERT INTO "+tableName+" VALUES (null,null,null,'66')");
+ String tableName = generateUniqueName();
+ String ddl = "CREATE TABLE " + tableName +
+ " (a_string varchar not null, cf1.a integer, cf1.b varchar, col1 integer, cf2.c varchar, cf2.d integer, col2 integer" +
+ " CONSTRAINT pk PRIMARY KEY (a_string))\n";
+ createTestTable(getUrl(), ddl);
+ String dml = "UPSERT INTO " + tableName + " VALUES(?,?,?,?,?,?,?)";
+ PreparedStatement stmt = conn.prepareStatement(dml);
+ stmt.setString(1, "a");
+ stmt.setInt(2, 40);
+ stmt.setString(3, "aa");
+ stmt.setInt(4, 10);
+ stmt.setString(5, "bb");
+ stmt.setInt(6, 20);
+ stmt.setInt(7, 1);
+ stmt.execute();
+ stmt.setString(1, "c");
+ stmt.setInt(2, 30);
+ stmt.setString(3, "cc");
+ stmt.setInt(4, 50);
+ stmt.setString(5, "dd");
+ stmt.setInt(6, 60);
+ stmt.setInt(7, 3);
+ stmt.execute();
+ stmt.setString(1, "b");
+ stmt.setInt(2, 40);
+ stmt.setString(3, "bb");
+ stmt.setInt(4, 5);
+ stmt.setString(5, "aa");
+ stmt.setInt(6, 80);
+ stmt.setInt(7, 2);
+ stmt.execute();
conn.commit();
- //groupBy orderPreserving orderBy asc asc
-
- sql="SELECT ORGANIZATION_ID,CONTAINER_ID FROM "+tableName+" group by ORGANIZATION_ID, CONTAINER_ID ORDER BY ORGANIZATION_ID ASC NULLS FIRST, CONTAINER_ID ASC NULLS FIRST";
- ResultSet rs=conn.prepareStatement(sql).executeQuery();
- assertResultSet(rs, new Object[][]{{null,null},{null,"1"},{null,"2"},{null,"3"},{null,"4"},{null,"5"},{null,"6"},{"1",null},{"1","1"},{"2",null},{"2","2"},{"3",null},{"3","3"},{"4",null},{"4","4"},{"5",null},{"5","5"},{"6",null},{"6","6"}});
-
- sql="SELECT ORGANIZATION_ID,CONTAINER_ID FROM "+tableName+" group by ORGANIZATION_ID, CONTAINER_ID ORDER BY ORGANIZATION_ID ASC NULLS FIRST, CONTAINER_ID ASC NULLS LAST";
- rs=conn.prepareStatement(sql).executeQuery();
- assertResultSet(rs, new Object[][]{{null,"1"},{null,"2"},{null,"3"},{null,"4"},{null,"5"},{null,"6"},{null,null},{"1","1"},{"1",null},{"2","2"},{"2",null},{"3","3"},{"3",null},{"4","4"},{"4",null},{"5","5"},{"5",null},{"6","6"},{"6",null}});
-
- sql="SELECT ORGANIZATION_ID,CONTAINER_ID FROM "+tableName+" group by ORGANIZATION_ID, CONTAINER_ID ORDER BY ORGANIZATION_ID ASC NULLS LAST, CONTAINER_ID ASC NULLS FIRST";
- rs=conn.prepareStatement(sql).executeQuery();
- assertResultSet(rs, new Object[][]{{"1",null},{"1","1"},{"2",null},{"2","2"},{"3",null},{"3","3"},{"4",null},{"4","4"},{"5",null},{"5","5"},{"6",null},{"6","6"},{null,null},{null,"1"},{null,"2"},{null,"3"},{null,"4"},{null,"5"},{null,"6"}});
-
- sql="SELECT ORGANIZATION_ID,CONTAINER_ID FROM "+tableName+" group by ORGANIZATION_ID, CONTAINER_ID ORDER BY ORGANIZATION_ID ASC NULLS LAST, CONTAINER_ID ASC NULLS LAST";
- rs=conn.prepareStatement(sql).executeQuery();
- assertResultSet(rs, new Object[][]{{"1","1"},{"1",null},{"2","2"},{"2",null},{"3","3"},{"3",null},{"4","4"},{"4",null},{"5","5"},{"5",null},{"6","6"},{"6",null},{null,"1"},{null,"2"},{null,"3"},{null,"4"},{null,"5"},{null,"6"},{null,null}});
-
- //groupBy orderPreserving orderBy asc desc
-
- sql="SELECT ORGANIZATION_ID,CONTAINER_ID FROM "+tableName+" group by ORGANIZATION_ID, CONTAINER_ID ORDER BY ORGANIZATION_ID ASC NULLS FIRST, CONTAINER_ID DESC NULLS FIRST";
- rs=conn.prepareStatement(sql).executeQuery();
- assertResultSet(rs, new Object[][]{{null,null},{null,"6"},{null,"5"},{null,"4"},{null,"3"},{null,"2"},{null,"1"},{"1",null},{"1","1"},{"2",null},{"2","2"},{"3",null},{"3","3"},{"4",null},{"4","4"},{"5",null},{"5","5"},{"6",null},{"6","6"}});
-
- sql="SELECT ORGANIZATION_ID,CONTAINER_ID FROM "+tableName+" group by ORGANIZATION_ID, CONTAINER_ID ORDER BY ORGANIZATION_ID ASC NULLS FIRST, CONTAINER_ID DESC NULLS LAST";
- rs=conn.prepareStatement(sql).executeQuery();
- assertResultSet(rs, new Object[][]{{null,"6"},{null,"5"},{null,"4"},{null,"3"},{null,"2"},{null,"1"},{null,null},{"1","1"},{"1",null},{"2","2"},{"2",null},{"3","3"},{"3",null},{"4","4"},{"4",null},{"5","5"},{"5",null},{"6","6"},{"6",null}});
-
- sql="SELECT ORGANIZATION_ID,CONTAINER_ID FROM "+tableName+" group by ORGANIZATION_ID, CONTAINER_ID ORDER BY ORGANIZATION_ID ASC NULLS LAST, CONTAINER_ID DESC NULLS FIRST";
- rs=conn.prepareStatement(sql).executeQuery();
- assertResultSet(rs, new Object[][]{{"1",null},{"1","1"},{"2",null},{"2","2"},{"3",null},{"3","3"},{"4",null},{"4","4"},{"5",null},{"5","5"},{"6",null},{"6","6"},{null,null},{null,"6"},{null,"5"},{null,"4"},{null,"3"},{null,"2"},{null,"1"}});
-
- sql="SELECT ORGANIZATION_ID,CONTAINER_ID FROM "+tableName+" group by ORGANIZATION_ID, CONTAINER_ID ORDER BY ORGANIZATION_ID ASC NULLS LAST, CONTAINER_ID DESC NULLS LAST";
- rs=conn.prepareStatement(sql).executeQuery();
- assertResultSet(rs, new Object[][]{{"1","1"},{"1",null},{"2","2"},{"2",null},{"3","3"},{"3",null},{"4","4"},{"4",null},{"5","5"},{"5",null},{"6","6"},{"6",null},{null,"6"},{null,"5"},{null,"4"},{null,"3"},{null,"2"},{null,"1"},{null,null}});
-
- //groupBy orderPreserving orderBy desc asc
-
- sql="SELECT ORGANIZATION_ID,CONTAINER_ID FROM "+tableName+" group by ORGANIZATION_ID, CONTAINER_ID ORDER BY ORGANIZATION_ID DESC NULLS FIRST, CONTAINER_ID ASC NULLS FIRST";
- rs=conn.prepareStatement(sql).executeQuery();
- assertResultSet(rs, new Object[][]{{null,null},{null,"1"},{null,"2"},{null,"3"},{null,"4"},{null,"5"},{null,"6"},{"6",null},{"6","6"},{"5",null},{"5","5"},{"4",null},{"4","4"},{"3",null},{"3","3"},{"2",null},{"2","2"},{"1",null},{"1","1"}});
-
- sql="SELECT ORGANIZATION_ID,CONTAINER_ID FROM "+tableName+" group by ORGANIZATION_ID, CONTAINER_ID ORDER BY ORGANIZATION_ID DESC NULLS FIRST, CONTAINER_ID ASC NULLS LAST";
- rs=conn.prepareStatement(sql).executeQuery();
- assertResultSet(rs, new Object[][]{{null,"1"},{null,"2"},{null,"3"},{null,"4"},{null,"5"},{null,"6"},{null,null},{"6","6"},{"6",null},{"5","5"},{"5",null},{"4","4"},{"4",null},{"3","3"},{"3",null},{"2","2"},{"2",null},{"1","1"},{"1",null}});
-
- sql="SELECT ORGANIZATION_ID,CONTAINER_ID FROM "+tableName+" group by ORGANIZATION_ID, CONTAINER_ID ORDER BY ORGANIZATION_ID DESC NULLS LAST, CONTAINER_ID ASC NULLS FIRST";
- rs=conn.prepareStatement(sql).executeQuery();
- assertResultSet(rs, new Object[][]{{"6",null},{"6","6"},{"5",null},{"5","5"},{"4",null},{"4","4"},{"3",null},{"3","3"},{"2",null},{"2","2"},{"1",null},{"1","1"},{null,null},{null,"1"},{null,"2"},{null,"3"},{null,"4"},{null,"5"},{null,"6"}});
-
- sql="SELECT ORGANIZATION_ID,CONTAINER_ID FROM "+tableName+" group by ORGANIZATION_ID, CONTAINER_ID ORDER BY ORGANIZATION_ID DESC NULLS LAST, CONTAINER_ID ASC NULLS LAST";
- rs=conn.prepareStatement(sql).executeQuery();
- assertResultSet(rs, new Object[][]{{"6","6"},{"6",null},{"5","5"},{"5",null},{"4","4"},{"4",null},{"3","3"},{"3",null},{"2","2"},{"2",null},{"1","1"},{"1",null},{null,"1"},{null,"2"},{null,"3"},{null,"4"},{null,"5"},{null,"6"},{null,null}});
-
- //groupBy orderPreserving orderBy desc desc
-
- sql="SELECT ORGANIZATION_ID,CONTAINER_ID FROM "+tableName+" group by ORGANIZATION_ID, CONTAINER_ID ORDER BY ORGANIZATION_ID DESC NULLS FIRST, CONTAINER_ID DESC NULLS FIRST";
- rs=conn.prepareStatement(sql).executeQuery();
- assertResultSet(rs, new Object[][]{{null,null},{null,"6"},{null,"5"},{null,"4"},{null,"3"},{null,"2"},{null,"1"},{"6",null},{"6","6"},{"5",null},{"5","5"},{"4",null},{"4","4"},{"3",null},{"3","3"},{"2",null},{"2","2"},{"1",null},{"1","1"}});
-
- sql="SELECT ORGANIZATION_ID,CONTAINER_ID FROM "+tableName+" group by ORGANIZATION_ID, CONTAINER_ID ORDER BY ORGANIZATION_ID DESC NULLS FIRST, CONTAINER_ID DESC NULLS LAST";
- rs=conn.prepareStatement(sql).executeQuery();
- assertResultSet(rs, new Object[][]{{null,"6"},{null,"5"},{null,"4"},{null,"3"},{null,"2"},{null,"1"},{null,null},{"6","6"},{"6",null},{"5","5"},{"5",null},{"4","4"},{"4",null},{"3","3"},{"3",null},{"2","2"},{"2",null},{"1","1"},{"1",null}});
-
- sql="SELECT ORGANIZATION_ID,CONTAINER_ID FROM "+tableName+" group by ORGANIZATION_ID, CONTAINER_ID ORDER BY ORGANIZATION_ID DESC NULLS LAST, CONTAINER_ID DESC NULLS FIRST";
- rs=conn.prepareStatement(sql).executeQuery();
- assertResultSet(rs, new Object[][]{{"6",null},{"6","6"},{"5",null},{"5","5"},{"4",null},{"4","4"},{"3",null},{"3","3"},{"2",null},{"2","2"},{"1",null},{"1","1"},{null,null},{null,"6"},{null,"5"},{null,"4"},{null,"3"},{null,"2"},{null,"1"}});
-
- sql="SELECT ORGANIZATION_ID,CONTAINER_ID FROM "+tableName+" group by ORGANIZATION_ID, CONTAINER_ID ORDER BY ORGANIZATION_ID DESC NULLS LAST, CONTAINER_ID DESC NULLS LAST";
- rs=conn.prepareStatement(sql).executeQuery();
- assertResultSet(rs, new Object[][]{{"6","6"},{"6",null},{"5","5"},{"5",null},{"4","4"},{"4",null},{"3","3"},{"3",null},{"2","2"},{"2",null},{"1","1"},{"1",null},{null,"6"},{null,"5"},{null,"4"},{null,"3"},{null,"2"},{null,"1"},{null,null}});
-
- //-----groupBy not orderPreserving--
-
- //groupBy not orderPreserving orderBy asc asc
-
- sql="SELECT ORGANIZATION_ID,SCORE FROM "+tableName+" group by ORGANIZATION_ID, SCORE ORDER BY ORGANIZATION_ID ASC NULLS FIRST, SCORE ASC NULLS FIRST";
- rs=conn.prepareStatement(sql).executeQuery();
- assertResultSet(rs, new Object[][]{{null,null},{null,"1"},{null,"2"},{null,"3"},{null,"4"},{null,"5"},{null,"6"},{"1",null},{"1","1"},{"2",null},{"2","2"},{"3",null},{"3","3"},{"4",null},{"4","4"},{"5",null},{"5","5"},{"6",null},{"6","6"}});
-
- sql="SELECT ORGANIZATION_ID,SCORE FROM "+tableName+" group by ORGANIZATION_ID, SCORE ORDER BY ORGANIZATION_ID ASC NULLS FIRST, SCORE ASC NULLS LAST";
- rs=conn.prepareStatement(sql).executeQuery();
- assertResultSet(rs, new Object[][]{{null,"1"},{null,"2"},{null,"3"},{null,"4"},{null,"5"},{null,"6"},{null,null},{"1","1"},{"1",null},{"2","2"},{"2",null},{"3","3"},{"3",null},{"4","4"},{"4",null},{"5","5"},{"5",null},{"6","6"},{"6",null}});
-
- sql="SELECT ORGANIZATION_ID,SCORE FROM "+tableName+" group by ORGANIZATION_ID, SCORE ORDER BY ORGANIZATION_ID ASC NULLS LAST, SCORE ASC NULLS FIRST";
- rs=conn.prepareStatement(sql).executeQuery();
- assertResultSet(rs, new Object[][]{{"1",null},{"1","1"},{"2",null},{"2","2"},{"3",null},{"3","3"},{"4",null},{"4","4"},{"5",null},{"5","5"},{"6",null},{"6","6"},{null,null},{null,"1"},{null,"2"},{null,"3"},{null,"4"},{null,"5"},{null,"6"}});
-
- sql="SELECT ORGANIZATION_ID,SCORE FROM "+tableName+" group by ORGANIZATION_ID, SCORE ORDER BY ORGANIZATION_ID ASC NULLS LAST, SCORE ASC NULLS LAST";
- rs=conn.prepareStatement(sql).executeQuery();
- assertResultSet(rs, new Object[][]{{"1","1"},{"1",null},{"2","2"},{"2",null},{"3","3"},{"3",null},{"4","4"},{"4",null},{"5","5"},{"5",null},{"6","6"},{"6",null},{null,"1"},{null,"2"},{null,"3"},{null,"4"},{null,"5"},{null,"6"},{null,null}});
-
- //groupBy not orderPreserving orderBy asc desc
-
- sql="SELECT ORGANIZATION_ID,SCORE FROM "+tableName+" group by ORGANIZATION_ID, SCORE ORDER BY ORGANIZATION_ID ASC NULLS FIRST, SCORE DESC NULLS FIRST";
- rs=conn.prepareStatement(sql).executeQuery();
- assertResultSet(rs, new Object[][]{{null,null},{null,"6"},{null,"5"},{null,"4"},{null,"3"},{null,"2"},{null,"1"},{"1",null},{"1","1"},{"2",null},{"2","2"},{"3",null},{"3","3"},{"4",null},{"4","4"},{"5",null},{"5","5"},{"6",null},{"6","6"}});
-
- sql="SELECT ORGANIZATION_ID,SCORE FROM "+tableName+" group by ORGANIZATION_ID, SCORE ORDER BY ORGANIZATION_ID ASC NULLS FIRST, SCORE DESC NULLS LAST";
- rs=conn.prepareStatement(sql).executeQuery();
- assertResultSet(rs, new Object[][]{{null,"6"},{null,"5"},{null,"4"},{null,"3"},{null,"2"},{null,"1"},{null,null},{"1","1"},{"1",null},{"2","2"},{"2",null},{"3","3"},{"3",null},{"4","4"},{"4",null},{"5","5"},{"5",null},{"6","6"},{"6",null}});
-
- sql="SELECT ORGANIZATION_ID,SCORE FROM "+tableName+" group by ORGANIZATION_ID, SCORE ORDER BY ORGANIZATION_ID ASC NULLS LAST, SCORE DESC NULLS FIRST";
- rs=conn.prepareStatement(sql).executeQuery();
- assertResultSet(rs, new Object[][]{{"1",null},{"1","1"},{"2",null},{"2","2"},{"3",null},{"3","3"},{"4",null},{"4","4"},{"5",null},{"5","5"},{"6",null},{"6","6"},{null,null},{null,"6"},{null,"5"},{null,"4"},{null,"3"},{null,"2"},{null,"1"}});
-
- sql="SELECT ORGANIZATION_ID,SCORE FROM "+tableName+" group by ORGANIZATION_ID, SCORE ORDER BY ORGANIZATION_ID ASC NULLS LAST, SCORE DESC NULLS LAST";
- rs=conn.prepareStatement(sql).executeQuery();
- assertResultSet(rs, new Object[][]{{"1","1"},{"1",null},{"2","2"},{"2",null},{"3","3"},{"3",null},{"4","4"},{"4",null},{"5","5"},{"5",null},{"6","6"},{"6",null},{null,"6"},{null,"5"},{null,"4"},{null,"3"},{null,"2"},{null,"1"},{null,null}});
-
- //groupBy not orderPreserving orderBy desc asc
-
- sql="SELECT ORGANIZATION_ID,SCORE FROM "+tableName+" group by ORGANIZATION_ID, SCORE ORDER BY ORGANIZATION_ID DESC NULLS FIRST, SCORE ASC NULLS FIRST";
- rs=conn.prepareStatement(sql).executeQuery();
- assertResultSet(rs, new Object[][]{{null,null},{null,"1"},{null,"2"},{null,"3"},{null,"4"},{null,"5"},{null,"6"},{"6",null},{"6","6"},{"5",null},{"5","5"},{"4",null},{"4","4"},{"3",null},{"3","3"},{"2",null},{"2","2"},{"1",null},{"1","1"}});
-
- sql="SELECT ORGANIZATION_ID,SCORE FROM "+tableName+" group by ORGANIZATION_ID, SCORE ORDER BY ORGANIZATION_ID DESC NULLS FIRST, SCORE ASC NULLS LAST";
- rs=conn.prepareStatement(sql).executeQuery();
- assertResultSet(rs, new Object[][]{{null,"1"},{null,"2"},{null,"3"},{null,"4"},{null,"5"},{null,"6"},{null,null},{"6","6"},{"6",null},{"5","5"},{"5",null},{"4","4"},{"4",null},{"3","3"},{"3",null},{"2","2"},{"2",null},{"1","1"},{"1",null}});
-
- sql="SELECT ORGANIZATION_ID,SCORE FROM "+tableName+" group by ORGANIZATION_ID, SCORE ORDER BY ORGANIZATION_ID DESC NULLS LAST, SCORE ASC NULLS FIRST";
- rs=conn.prepareStatement(sql).executeQuery();
- assertResultSet(rs, new Object[][]{{"6",null},{"6","6"},{"5",null},{"5","5"},{"4",null},{"4","4"},{"3",null},{"3","3"},{"2",null},{"2","2"},{"1",null},{"1","1"},{null,null},{null,"1"},{null,"2"},{null,"3"},{null,"4"},{null,"5"},{null,"6"}});
-
- sql="SELECT ORGANIZATION_ID,SCORE FROM "+tableName+" group by ORGANIZATION_ID, SCORE ORDER BY ORGANIZATION_ID DESC NULLS LAST, SCORE ASC NULLS LAST";
- rs=conn.prepareStatement(sql).executeQuery();
- assertResultSet(rs, new Object[][]{{"6","6"},{"6",null},{"5","5"},{"5",null},{"4","4"},{"4",null},{"3","3"},{"3",null},{"2","2"},{"2",null},{"1","1"},{"1",null},{null,"1"},{null,"2"},{null,"3"},{null,"4"},{null,"5"},{null,"6"},{null,null}});
-
- //groupBy not orderPreserving orderBy desc desc
-
- sql="SELECT ORGANIZATION_ID,SCORE FROM "+tableName+" group by ORGANIZATION_ID, SCORE ORDER BY ORGANIZATION_ID DESC NULLS FIRST, SCORE DESC NULLS FIRST";
- rs=conn.prepareStatement(sql).executeQuery();
- assertResultSet(rs, new Object[][]{{null,null},{null,"6"},{null,"5"},{null,"4"},{null,"3"},{null,"2"},{null,"1"},{"6",null},{"6","6"},{"5",null},{"5","5"},{"4",null},{"4","4"},{"3",null},{"3","3"},{"2",null},{"2","2"},{"1",null},{"1","1"}});
-
- sql="SELECT ORGANIZATION_ID,SCORE FROM "+tableName+" group by ORGANIZATION_ID, SCORE ORDER BY ORGANIZATION_ID DESC NULLS FIRST, SCORE DESC NULLS LAST";
- rs=conn.prepareStatement(sql).executeQuery();
- assertResultSet(rs, new Object[][]{{null,"6"},{null,"5"},{null,"4"},{null,"3"},{null,"2"},{null,"1"},{null,null},{"6","6"},{"6",null},{"5","5"},{"5",null},{"4","4"},{"4",null},{"3","3"},{"3",null},{"2","2"},{"2",null},{"1","1"},{"1",null}});
-
- sql="SELECT ORGANIZATION_ID,SCORE FROM "+tableName+" group by ORGANIZATION_ID, SCORE ORDER BY ORGANIZATION_ID DESC NULLS LAST, SCORE DESC NULLS FIRST";
- rs=conn.prepareStatement(sql).executeQuery();
- assertResultSet(rs, new Object[][]{{"6",null},{"6","6"},{"5",null},{"5","5"},{"4",null},{"4","4"},{"3",null},{"3","3"},{"2",null},{"2","2"},{"1",null},{"1","1"},{null,null},{null,"6"},{null,"5"},{null,"4"},{null,"3"},{null,"2"},{null,"1"}});
-
- sql="SELECT ORGANIZATION_ID,SCORE FROM "+tableName+" group by ORGANIZATION_ID, SCORE ORDER BY ORGANIZATION_ID DESC NULLS LAST, SCORE DESC NULLS LAST";
- rs=conn.prepareStatement(sql).executeQuery();
- assertResultSet(rs, new Object[][]{{"6","6"},{"6",null},{"5","5"},{"5",null},{"4","4"},{"4",null},{"3","3"},{"3",null},{"2","2"},{"2",null},{"1","1"},{"1",null},{null,"6"},{null,"5"},{null,"4"},{null,"3"},{null,"2"},{null,"1"},{null,null}});
-
- //-------test only one return column----------------------------------
-
- sql="SELECT SCORE FROM "+tableName+" group by SCORE ORDER BY SCORE ASC NULLS FIRST";
- rs=conn.prepareStatement(sql).executeQuery();
- assertResultSet(rs, new Object[][]{{null},{"1"},{"2"},{"3"},{"4"},{"5"},{"6"}});
-
- sql="SELECT SCORE FROM "+tableName+" group by SCORE ORDER BY SCORE ASC NULLS LAST";
- rs=conn.prepareStatement(sql).executeQuery();
- assertResultSet(rs, new Object[][]{{"1"},{"2"},{"3"},{"4"},{"5"},{"6"},{null}});
-
- sql="SELECT SCORE FROM "+tableName+" group by SCORE ORDER BY SCORE DESC NULLS FIRST";
- rs=conn.prepareStatement(sql).executeQuery();
- assertResultSet(rs, new Object[][]{{null},{"6"},{"5"},{"4"},{"3"},{"2"},{"1"}});
+ String query = "select * from " + tableName + " order by 2, 5";
+ ResultSet rs = conn.createStatement().executeQuery(query);
+ assertTrue(rs.next());
+ assertEquals("c",rs.getString(1));
+ assertEquals(30,rs.getInt(2));
+ assertEquals("cc",rs.getString(3));
+ assertEquals(50,rs.getInt(4));
+ assertEquals("dd",rs.getString(5));
+ assertEquals(60,rs.getInt(6));
+ assertEquals(3,rs.getInt(7));
+ assertTrue(rs.next());
+ assertEquals("b",rs.getString(1));
+ assertEquals(40,rs.getInt(2));
+ assertEquals("bb",rs.getString(3));
+ assertEquals(5,rs.getInt(4));
+ assertEquals("aa",rs.getString(5));
+ assertEquals(80,rs.getInt(6));
+ assertEquals(2,rs.getInt(7));
+ assertTrue(rs.next());
+ assertEquals("a",rs.getString(1));
+ assertEquals(40,rs.getInt(2));
+ assertEquals("aa",rs.getString(3));
+ assertEquals(10,rs.getInt(4));
+ assertEquals("bb",rs.getString(5));
+ assertEquals(20,rs.getInt(6));
+ assertEquals(1,rs.getInt(7));
+ assertFalse(rs.next());
- sql="SELECT SCORE FROM "+tableName+" group by SCORE ORDER BY SCORE DESC NULLS LAST";
- rs=conn.prepareStatement(sql).executeQuery();
- assertResultSet(rs, new Object[][]{{"6"},{"5"},{"4"},{"3"},{"2"},{"1"},{null}});
+ query = "select * from " + tableName + " order by 7";
+ rs = conn.createStatement().executeQuery(query);
+ assertTrue(rs.next());
+ assertEquals("a",rs.getString(1));
+ assertEquals(40,rs.getInt(2));
+ assertEquals("aa",rs.getString(3));
+ assertEquals(10,rs.getInt(4));
+ assertEquals("bb",rs.getString(5));
+ assertEquals(20,rs.getInt(6));
+ assertEquals(1,rs.getInt(7));
+ assertTrue(rs.next());
+ assertEquals("b",rs.getString(1));
+ assertEquals(40,rs.getInt(2));
+ assertEquals("bb",rs.getString(3));
+ assertEquals(5,rs.getInt(4));
+ assertEquals("aa",rs.getString(5));
+ assertEquals(80,rs.getInt(6));
+ assertEquals(2,rs.getInt(7));
+ assertTrue(rs.next());
+ assertEquals("c",rs.getString(1));
+ assertEquals(30,rs.getInt(2));
+ assertEquals("cc",rs.getString(3));
+ assertEquals(50,rs.getInt(4));
+ assertEquals("dd",rs.getString(5));
+ assertEquals(60,rs.getInt(6));
+ assertEquals(3,rs.getInt(7));
+ assertFalse(rs.next());
} finally {
- if(conn!=null) {
- conn.close();
- }
+ conn.close();
}
}
-
-}
\ No newline at end of file
+}
http://git-wip-us.apache.org/repos/asf/phoenix/blob/9bfaf183/phoenix-core/src/it/java/org/apache/phoenix/end2end/ParallelStatsDisabledIT.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/ParallelStatsDisabledIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/ParallelStatsDisabledIT.java
index 98939da..561aee5 100644
--- a/phoenix-core/src/it/java/org/apache/phoenix/end2end/ParallelStatsDisabledIT.java
+++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/ParallelStatsDisabledIT.java
@@ -18,12 +18,26 @@
package org.apache.phoenix.end2end;
+import org.apache.commons.lang.StringUtils;
import org.apache.phoenix.query.BaseTest;
+import org.apache.phoenix.util.QueryBuilder;
+import org.apache.phoenix.util.QueryUtil;
import org.apache.phoenix.util.ReadOnlyProps;
import org.junit.AfterClass;
import org.junit.BeforeClass;
import org.junit.experimental.categories.Category;
+import java.sql.Connection;
+import java.sql.PreparedStatement;
+import java.sql.ResultSet;
+import java.sql.SQLException;
+
+import static org.junit.Assert.assertEquals;
+import static org.junit.Assert.assertTrue;
+import static org.junit.Assert.fail;
+
+
+
/**
* Base class for tests whose methods run in parallel with statistics disabled.
* You must create unique names using {@link #generateUniqueName()} for each
@@ -41,4 +55,30 @@ public abstract class ParallelStatsDisabledIT extends BaseTest {
public static void tearDownMiniCluster() throws Exception {
BaseTest.tearDownMiniClusterIfBeyondThreshold();
}
+
+ protected ResultSet executeQuery(Connection conn, QueryBuilder queryBuilder) throws SQLException {
+ PreparedStatement statement = conn.prepareStatement(queryBuilder.build());
+ ResultSet rs = statement.executeQuery();
+ return rs;
+ }
+
+ protected ResultSet executeQueryThrowsException(Connection conn, QueryBuilder queryBuilder,
+ String expectedPhoenixExceptionMsg, String expectedSparkExceptionMsg) {
+ ResultSet rs = null;
+ try {
+ rs = executeQuery(conn, queryBuilder);
+ fail();
+ }
+ catch(Exception e) {
+ assertTrue(e.getMessage().contains(expectedPhoenixExceptionMsg));
+ }
+ return rs;
+ }
+
+ protected void validateQueryPlan(Connection conn, QueryBuilder queryBuilder, String expectedPhoenixPlan, String expectedSparkPlan) throws SQLException {
+ if (StringUtils.isNotBlank(expectedPhoenixPlan)) {
+ ResultSet rs = conn.createStatement().executeQuery("EXPLAIN " + queryBuilder.build());
+ assertEquals(expectedPhoenixPlan, QueryUtil.getExplainPlan(rs));
+ }
+ }
}
http://git-wip-us.apache.org/repos/asf/phoenix/blob/9bfaf183/phoenix-core/src/it/java/org/apache/phoenix/end2end/salted/BaseSaltedTableIT.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/salted/BaseSaltedTableIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/salted/BaseSaltedTableIT.java
new file mode 100644
index 0000000..3051cd6
--- /dev/null
+++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/salted/BaseSaltedTableIT.java
@@ -0,0 +1,474 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+package org.apache.phoenix.end2end.salted;
+
+import static org.apache.phoenix.util.TestUtil.TABLE_WITH_SALTING;
+import static org.apache.phoenix.util.TestUtil.TEST_PROPERTIES;
+import static org.junit.Assert.assertEquals;
+import static org.junit.Assert.assertFalse;
+import static org.junit.Assert.assertTrue;
+
+import java.sql.Connection;
+import java.sql.DriverManager;
+import java.sql.PreparedStatement;
+import java.sql.ResultSet;
+import java.util.Properties;
+
+import org.apache.phoenix.end2end.ParallelStatsDisabledIT;
+import org.apache.phoenix.util.PropertiesUtil;
+import org.apache.phoenix.util.QueryBuilder;
+import org.apache.phoenix.util.QueryUtil;
+import org.junit.Test;
+
+import com.google.common.collect.Lists;
+
+public abstract class BaseSaltedTableIT extends ParallelStatsDisabledIT {
+
+ protected static String initTableValues(byte[][] splits) throws Exception {
+ String tableName = generateUniqueName();
+ Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
+ Connection conn = DriverManager.getConnection(getUrl(), props);
+
+ // Rows we inserted:
+ // 1ab123abc111
+ // 1abc456abc111
+ // 1de123abc111
+ // 2abc123def222
+ // 3abc123ghi333
+ // 4abc123jkl444
+ try {
+ // Upsert with no column specifies.
+ ensureTableCreated(getUrl(), tableName, TABLE_WITH_SALTING, splits, null, null);
+ String query = "UPSERT INTO " + tableName + " VALUES(?,?,?,?,?)";
+ PreparedStatement stmt = conn.prepareStatement(query);
+ stmt.setInt(1, 1);
+ stmt.setString(2, "ab");
+ stmt.setString(3, "123");
+ stmt.setString(4, "abc");
+ stmt.setInt(5, 111);
+ stmt.execute();
+ conn.commit();
+
+ stmt.setInt(1, 1);
+ stmt.setString(2, "abc");
+ stmt.setString(3, "456");
+ stmt.setString(4, "abc");
+ stmt.setInt(5, 111);
+ stmt.execute();
+ conn.commit();
+
+ // Test upsert when statement explicitly specifies the columns to upsert into.
+ query = "UPSERT INTO " + tableName +
+ " (a_integer, a_string, a_id, b_string, b_integer) " +
+ " VALUES(?,?,?,?,?)";
+ stmt = conn.prepareStatement(query);
+
+ stmt.setInt(1, 1);
+ stmt.setString(2, "de");
+ stmt.setString(3, "123");
+ stmt.setString(4, "abc");
+ stmt.setInt(5, 111);
+ stmt.execute();
+ conn.commit();
+
+ stmt.setInt(1, 2);
+ stmt.setString(2, "abc");
+ stmt.setString(3, "123");
+ stmt.setString(4, "def");
+ stmt.setInt(5, 222);
+ stmt.execute();
+ conn.commit();
+
+ // Test upsert when order of column is shuffled.
+ query = "UPSERT INTO " + tableName +
+ " (a_string, a_integer, a_id, b_string, b_integer) " +
+ " VALUES(?,?,?,?,?)";
+ stmt = conn.prepareStatement(query);
+ stmt.setString(1, "abc");
+ stmt.setInt(2, 3);
+ stmt.setString(3, "123");
+ stmt.setString(4, "ghi");
+ stmt.setInt(5, 333);
+ stmt.execute();
+ conn.commit();
+
+ stmt.setString(1, "abc");
+ stmt.setInt(2, 4);
+ stmt.setString(3, "123");
+ stmt.setString(4, "jkl");
+ stmt.setInt(5, 444);
+ stmt.execute();
+ conn.commit();
+ } finally {
+ conn.close();
+ }
+ return tableName;
+ }
+
+ @Test
+ public void testSelectValueNoWhereClause() throws Exception {
+ Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
+ Connection conn = DriverManager.getConnection(getUrl(), props);
+ try {
+ String tableName = initTableValues(null);
+ // "SELECT * FROM " + tableName;
+ QueryBuilder queryBuilder = new QueryBuilder()
+ .setSelectColumns(
+ Lists.newArrayList("A_INTEGER", "A_STRING", "A_ID", "B_STRING", "B_INTEGER"))
+ .setFullTableName(tableName);
+ ResultSet rs = executeQuery(conn, queryBuilder);
+ assertTrue(rs.next());
+ assertEquals(1, rs.getInt(1));
+ assertEquals("ab", rs.getString(2));
+ assertEquals("123", rs.getString(3));
+ assertEquals("abc", rs.getString(4));
+ assertEquals(111, rs.getInt(5));
+
+ assertTrue(rs.next());
+ assertEquals(1, rs.getInt(1));
+ assertEquals("abc", rs.getString(2));
+ assertEquals("456", rs.getString(3));
+ assertEquals("abc", rs.getString(4));
+ assertEquals(111, rs.getInt(5));
+
+ assertTrue(rs.next());
+ assertEquals(1, rs.getInt(1));
+ assertEquals("de", rs.getString(2));
+ assertEquals("123", rs.getString(3));
+ assertEquals("abc", rs.getString(4));
+ assertEquals(111, rs.getInt(5));
+
+ assertTrue(rs.next());
+ assertEquals(2, rs.getInt(1));
+ assertEquals("abc", rs.getString(2));
+ assertEquals("123", rs.getString(3));
+ assertEquals("def", rs.getString(4));
+ assertEquals(222, rs.getInt(5));
+
+ assertTrue(rs.next());
+ assertEquals(3, rs.getInt(1));
+ assertEquals("abc", rs.getString(2));
+ assertEquals("123", rs.getString(3));
+ assertEquals("ghi", rs.getString(4));
+ assertEquals(333, rs.getInt(5));
+
+ assertTrue(rs.next());
+ assertEquals(4, rs.getInt(1));
+ assertEquals("abc", rs.getString(2));
+ assertEquals("123", rs.getString(3));
+ assertEquals("jkl", rs.getString(4));
+ assertEquals(444, rs.getInt(5));
+
+ assertFalse(rs.next());
+ } finally {
+ conn.close();
+ }
+ }
+
+ @Test
+ public void testSelectValueWithFullyQualifiedWhereClause() throws Exception {
+ Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
+ Connection conn = DriverManager.getConnection(getUrl(), props);
+ try {
+ String tableName = initTableValues(null);
+ PreparedStatement stmt;
+ ResultSet rs;
+
+ // Variable length slot with bounded ranges.
+ QueryBuilder queryBuilder = new QueryBuilder()
+ .setSelectColumns(
+ Lists.newArrayList("A_INTEGER", "A_STRING", "A_ID", "B_STRING", "B_INTEGER"))
+ .setFullTableName(tableName)
+ .setWhereClause("a_integer = 1 AND a_string >= 'ab' AND a_string < 'de' AND a_id = '123'");
+ rs = executeQuery(conn, queryBuilder);
+ assertTrue(rs.next());
+ assertEquals(1, rs.getInt(1));
+ assertEquals("ab", rs.getString(2));
+ assertEquals("123", rs.getString(3));
+ assertEquals("abc", rs.getString(4));
+ assertEquals(111, rs.getInt(5));
+ assertFalse(rs.next());
+
+ // all single slots with one value.
+ queryBuilder.setWhereClause("a_integer = 1 AND a_string = 'ab' AND a_id = '123'");
+ rs = executeQuery(conn, queryBuilder);
+ assertTrue(rs.next());
+ assertEquals(1, rs.getInt(1));
+ assertEquals("ab", rs.getString(2));
+ assertEquals("123", rs.getString(3));
+ assertEquals("abc", rs.getString(4));
+ assertEquals(111, rs.getInt(5));
+ assertFalse(rs.next());
+
+ // all single slots with multiple values.
+ queryBuilder.setWhereClause("a_integer in (2, 4) AND a_string = 'abc' AND a_id = '123'");
+ rs = executeQuery(conn, queryBuilder);
+
+ assertTrue(rs.next());
+ assertEquals(2, rs.getInt(1));
+ assertEquals("abc", rs.getString(2));
+ assertEquals("123", rs.getString(3));
+ assertEquals("def", rs.getString(4));
+ assertEquals(222, rs.getInt(5));
+
+ assertTrue(rs.next());
+ assertEquals(4, rs.getInt(1));
+ assertEquals("abc", rs.getString(2));
+ assertEquals("123", rs.getString(3));
+ assertEquals("jkl", rs.getString(4));
+ assertEquals(444, rs.getInt(5));
+ assertFalse(rs.next());
+
+ queryBuilder.setWhereClause("A_INTEGER in (1,2,3,4) AND A_STRING in ('a', 'abc', 'de') AND A_ID = '123'");
+ queryBuilder.setSelectColumns(Lists.newArrayList("A_INTEGER", "A_STRING", "A_ID"));
+ rs = executeQuery(conn, queryBuilder);
+ assertTrue(rs.next());
+ assertEquals(1, rs.getInt(1));
+ assertEquals("de", rs.getString(2));
+
+ assertTrue(rs.next());
+ assertEquals(2, rs.getInt(1));
+ assertEquals("abc", rs.getString(2));
+
+ assertTrue(rs.next());
+ assertEquals(3, rs.getInt(1));
+ assertEquals("abc", rs.getString(2));
+
+ assertTrue(rs.next());
+ assertEquals(4, rs.getInt(1));
+ assertEquals("abc", rs.getString(2));
+ assertFalse(rs.next());
+
+ // fixed length slot with bounded ranges.
+ queryBuilder.setWhereClause("A_INTEGER > 1 AND A_INTEGER < 4 AND A_STRING = 'abc' AND A_ID = '123'");
+ queryBuilder.setSelectColumns(Lists.newArrayList("A_STRING", "A_ID", "A_INTEGER"));
+ rs = executeQuery(conn, queryBuilder);
+ assertTrue(rs.next());
+ assertEquals("abc", rs.getString(1));
+ assertEquals("123", rs.getString(2));
+
+ assertTrue(rs.next());
+ assertEquals("abc", rs.getString(1));
+ assertEquals("123", rs.getString(2));
+ assertFalse(rs.next());
+
+ // fixed length slot with unbound ranges.
+ queryBuilder.setWhereClause("A_INTEGER > 1 AND A_STRING = 'abc' AND A_ID = '123'");
+ queryBuilder.setSelectColumns(Lists.newArrayList("B_STRING", "B_INTEGER", "A_INTEGER", "A_STRING", "A_ID"));
+ rs = executeQuery(conn, queryBuilder);
+ assertTrue(rs.next());
+ assertEquals("def", rs.getString(1));
+ assertEquals(222, rs.getInt(2));
+
+ assertTrue(rs.next());
+ assertEquals("ghi", rs.getString(1));
+ assertEquals(333, rs.getInt(2));
+
+ assertTrue(rs.next());
+ assertEquals("jkl", rs.getString(1));
+ assertEquals(444, rs.getInt(2));
+ assertFalse(rs.next());
+
+ // Variable length slot with unbounded ranges.
+ queryBuilder.setWhereClause("A_INTEGER = 1 AND A_STRING > 'ab' AND A_ID = '123'");
+ queryBuilder.setSelectColumns(
+ Lists.newArrayList("A_INTEGER", "A_STRING", "A_ID", "B_STRING", "B_INTEGER"));
+ rs = executeQuery(conn, queryBuilder);
+ assertTrue(rs.next());
+ assertEquals(1, rs.getInt(1));
+ assertEquals("de", rs.getString(2));
+ assertEquals("123", rs.getString(3));
+ assertEquals("abc", rs.getString(4));
+ assertEquals(111, rs.getInt(5));
+ assertFalse(rs.next());
+
+ } finally {
+ conn.close();
+ }
+ }
+
+ @Test
+ public void testSelectValueWithNotFullyQualifiedWhereClause() throws Exception {
+ Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
+ Connection conn = DriverManager.getConnection(getUrl(), props);
+ try {
+ String tableName = initTableValues(null);
+
+ // Where without fully qualified key, point query.
+ String query = "SELECT * FROM " + tableName + " WHERE a_integer = ? AND a_string = ?";
+ PreparedStatement stmt = conn.prepareStatement(query);
+
+ stmt.setInt(1, 1);
+ stmt.setString(2, "abc");
+ ResultSet rs = stmt.executeQuery();
+ assertTrue(rs.next());
+ assertEquals(1, rs.getInt(1));
+ assertEquals("abc", rs.getString(2));
+ assertEquals("456", rs.getString(3));
+ assertEquals("abc", rs.getString(4));
+ assertEquals(111, rs.getInt(5));
+ assertFalse(rs.next());
+
+ // Where without fully qualified key, range query.
+ query = "SELECT * FROM " + tableName + " WHERE a_integer >= 2";
+ stmt = conn.prepareStatement(query);
+ rs = stmt.executeQuery();
+ assertTrue(rs.next());
+ assertEquals(2, rs.getInt(1));
+ assertEquals("abc", rs.getString(2));
+ assertEquals("123", rs.getString(3));
+ assertEquals("def", rs.getString(4));
+ assertEquals(222, rs.getInt(5));
+
+ assertTrue(rs.next());
+ assertEquals(3, rs.getInt(1));
+ assertEquals("abc", rs.getString(2));
+ assertEquals("123", rs.getString(3));
+ assertEquals("ghi", rs.getString(4));
+ assertEquals(333, rs.getInt(5));
+
+ assertTrue(rs.next());
+ assertEquals(4, rs.getInt(1));
+ assertEquals("abc", rs.getString(2));
+ assertEquals("123", rs.getString(3));
+ assertEquals("jkl", rs.getString(4));
+ assertEquals(444, rs.getInt(5));
+ assertFalse(rs.next());
+
+ // With point query.
+ query = "SELECT a_string FROM " + tableName + " WHERE a_string = ?";
+ stmt = conn.prepareStatement(query);
+ stmt.setString(1, "de");
+ rs = stmt.executeQuery();
+ assertTrue(rs.next());
+ assertEquals("de", rs.getString(1));
+ assertFalse(rs.next());
+
+ query = "SELECT a_id FROM " + tableName + " WHERE a_id = ?";
+ stmt = conn.prepareStatement(query);
+ stmt.setString(1, "456");
+ rs = stmt.executeQuery();
+ assertTrue(rs.next());
+ assertEquals("456", rs.getString(1));
+ assertFalse(rs.next());
+ } finally {
+ conn.close();
+ }
+ }
+
+ @Test
+ public void testSelectWithGroupBy() throws Exception {
+ Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
+ Connection conn = DriverManager.getConnection(getUrl(), props);
+ try {
+ String tableName = initTableValues(null);
+
+ String query = "SELECT a_integer FROM " + tableName + " GROUP BY a_integer";
+ PreparedStatement stmt = conn.prepareStatement(query);
+ ResultSet rs = stmt.executeQuery();
+ int count = 0;
+ while (rs.next()) {
+ count++;
+ }
+ assertEquals("Group by does not return the right count.", count, 4);
+ } finally {
+ conn.close();
+ }
+ }
+
+ @Test
+ public void testLimitScan() throws Exception {
+ Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
+ Connection conn = DriverManager.getConnection(getUrl(), props);
+ try {
+ String tableName = initTableValues(null);
+
+ String query = "SELECT a_integer FROM " + tableName + " WHERE a_string='abc' LIMIT 1";
+ PreparedStatement stmt = conn.prepareStatement(query);
+ ResultSet rs = stmt.executeQuery();
+ assertTrue(rs.next());
+ assertEquals(1, rs.getInt(1));
+ assertFalse(rs.next());
+ } finally {
+ conn.close();
+ }
+ }
+
+ @Test
+ public void testSelectWithOrderByRowKey() throws Exception {
+ Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
+ Connection conn = DriverManager.getConnection(getUrl(), props);
+ try {
+ String tableName = initTableValues(null);
+
+ String query = "SELECT * FROM " + tableName + " ORDER BY a_integer, a_string, a_id";
+ PreparedStatement statement = conn.prepareStatement(query);
+ ResultSet explainPlan = statement.executeQuery("EXPLAIN " + query);
+ // Confirm that ORDER BY in row key order will be optimized out for salted table
+ assertEquals("CLIENT PARALLEL 4-WAY FULL SCAN OVER " + tableName + "\n" +
+ "CLIENT MERGE SORT", QueryUtil.getExplainPlan(explainPlan));
+ ResultSet rs = statement.executeQuery();
+
+ assertTrue(rs.next());
+ assertEquals(1, rs.getInt(1));
+ assertEquals("ab", rs.getString(2));
+ assertEquals("123", rs.getString(3));
+ assertEquals("abc", rs.getString(4));
+ assertEquals(111, rs.getInt(5));
+
+ assertTrue(rs.next());
+ assertEquals(1, rs.getInt(1));
+ assertEquals("abc", rs.getString(2));
+ assertEquals("456", rs.getString(3));
+ assertEquals("abc", rs.getString(4));
+ assertEquals(111, rs.getInt(5));
+
+ assertTrue(rs.next());
+ assertEquals(1, rs.getInt(1));
+ assertEquals("de", rs.getString(2));
+ assertEquals("123", rs.getString(3));
+ assertEquals("abc", rs.getString(4));
+ assertEquals(111, rs.getInt(5));
+
+ assertTrue(rs.next());
+ assertEquals(2, rs.getInt(1));
+ assertEquals("abc", rs.getString(2));
+ assertEquals("123", rs.getString(3));
+ assertEquals("def", rs.getString(4));
+ assertEquals(222, rs.getInt(5));
+
+ assertTrue(rs.next());
+ assertEquals(3, rs.getInt(1));
+ assertEquals("abc", rs.getString(2));
+ assertEquals("123", rs.getString(3));
+ assertEquals("ghi", rs.getString(4));
+ assertEquals(333, rs.getInt(5));
+
+ assertTrue(rs.next());
+ assertEquals(4, rs.getInt(1));
+ assertEquals("abc", rs.getString(2));
+ assertEquals("123", rs.getString(3));
+ assertEquals("jkl", rs.getString(4));
+ assertEquals(444, rs.getInt(5));
+
+ assertFalse(rs.next());
+ } finally {
+ conn.close();
+ }
+ }
+}