phoenix-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From JamesRTaylor <...@git.apache.org>
Subject [GitHub] phoenix pull request #320: PHOENIX-4820
Date Tue, 31 Jul 2018 23:42:02 GMT
Github user JamesRTaylor commented on a diff in the pull request:

    https://github.com/apache/phoenix/pull/320#discussion_r206717157
  
    --- 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 --
    
    Never mind - just saw your comment in the new visitor and you only want to optimize this
single constant case. Would it make sense to have a negative test for this below to make sure
it doesn't get optimized when there are more than a single constant in an IN clause?


---

Mime
View raw message