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(); + } + } +}
