Github user JamesRTaylor commented on a diff in the pull request:
https://github.com/apache/phoenix/pull/320#discussion_r206715618
--- Diff:
phoenix-core/src/test/java/org/apache/phoenix/compile/QueryCompilerTest.java ---
@@ -2951,7 +2951,129 @@ public void testOrderPreservingGroupBy() throws
Exception {
}
}
}
-
+
+ @Test
+ public void testOrderPreservingGroupByForNotPkColumns() throws
Exception {
+
+ try (Connection conn= DriverManager.getConnection(getUrl())) {
+
+ conn.createStatement().execute("CREATE TABLE test (\n" +
+ " pk1 varchar, \n" +
+ " pk2 varchar, \n" +
+ " pk3 varchar, \n" +
+ " pk4 varchar, \n" +
+ " v1 varchar, \n" +
+ " v2 varchar,\n" +
+ " CONSTRAINT pk PRIMARY KEY (\n" +
+ " pk1,\n" +
+ " pk2,\n" +
+ " pk3,\n" +
+ " pk4\n" +
+ " )\n" +
+ " )");
+ String[] queries = new String[] {
+ "SELECT pk3 FROM test WHERE v2 = 'a' GROUP BY
substr(v2,0,1),pk3 ORDER BY pk3",
+ "SELECT pk3 FROM test WHERE pk1 = 'c' and v2 =
substr('abc',1,1) GROUP BY v2,pk3 ORDER BY pk3",
+ "SELECT pk3 FROM test WHERE v1 = 'a' and v2 = 'b'
GROUP BY length(v1)+length(v2),pk3 ORDER BY pk3",
+ "SELECT pk3 FROM test WHERE pk1 = 'a' and v2 = 'b'
GROUP BY length(pk1)+length(v2),pk3 ORDER BY pk3",
+ "SELECT pk3 FROM test WHERE v1 = 'a' and v2 =
substr('abc',2,1) GROUP BY pk4,CASE WHEN v1 > v2 THEN v1 ELSE v2 END,pk3 ORDER
BY pk4,pk3",
+ "SELECT pk3 FROM test WHERE pk1 = 'a' and v2 =
substr('abc',2,1) GROUP BY pk4,CASE WHEN pk1 > v2 THEN pk1 ELSE v2 END,pk3
ORDER BY pk4,pk3",
+ "SELECT pk3 FROM test WHERE pk1 = 'a' and pk2 = 'b'
and v1 = 'c' GROUP BY CASE WHEN pk1 > pk2 THEN v1 WHEN pk1 = pk2 THEN pk1 ELSE
pk2 END,pk3 ORDER BY pk3"
+ };
+ int index = 0;
+ for (String query : queries) {
+ QueryPlan plan = getQueryPlan(conn, query);
+ assertTrue((index + 1) + ") " + queries[index],
plan.getOrderBy().getOrderByExpressions().isEmpty());
+ index++;
+ }
+ }
+ }
+
+ @Test
+ public void testOrderPreservingGroupByForClientAggregatePlan() throws
Exception {
+ Connection conn = null;
+ try {
+ conn = DriverManager.getConnection(getUrl());
+ String tableName1 = "test_table";
+ String sql = "create table " + tableName1 + "( "+
+ " pk1 varchar not null , " +
+ " pk2 varchar not null, " +
+ " pk3 varchar not null," +
+ " v1 varchar, " +
+ " v2 varchar, " +
+ " CONSTRAINT TEST_PK PRIMARY KEY ( "+
+ "pk1,"+
+ "pk2,"+
+ "pk3 ))";
+ conn.createStatement().execute(sql);
+
+ String[] queries = new String[] {
+ "select a.ak3 "+
+ "from (select substr(pk1,1,1) ak1,substr(pk2,1,1)
ak2,substr(pk3,1,1) ak3,substr(v1,1,1) av1,substr(v2,1,1) av2 from
"+tableName1+" order by pk2,pk3 limit 10) a "+
+ "group by a.ak3,a.av1 order by a.ak3,a.av1",
+
+ "select a.ak3 "+
+ "from (select substr(pk1,1,1) ak1,substr(pk2,1,1)
ak2,substr(pk3,1,1) ak3,substr(v1,1,1) av1,substr(v2,1,1) av2 from
"+tableName1+" order by pk2,pk3 limit 10) a "+
+ "where a.av2 = 'a' GROUP BY substr(a.av2,0,1),ak3 ORDER
BY ak3",
+
+ //for InListExpression
+ "select a.ak3 "+
+ "from (select substr(pk1,1,1) ak1,substr(pk2,1,1)
ak2,substr(pk3,1,1) ak3,substr(v1,1,1) av1,substr(v2,1,1) av2 from
"+tableName1+" order by pk2,pk3 limit 10) a "+
+ "where a.av2 in('a') GROUP BY substr(a.av2,0,1),ak3
ORDER BY ak3",
--- End diff --
For the IN test, please add more than one constant (or this gets optimized
to a.av2='a'
---
