weidong3630 commented on a change in pull request #1606: [CALCITE-3466] Do not
drop the "group by" clause of subquery in a select statement with "group by"
clause in JDBC adapter.
URL: https://github.com/apache/calcite/pull/1606#discussion_r358643229
##########
File path:
core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java
##########
@@ -349,6 +349,42 @@ private static String toSql(RelNode root, SqlDialect
dialect) {
.ok(expectedMySql);
}
+ /** Test query with group by and a sub-query which is also with group by. */
+ @Test public void testSelectQueryWithGroupBySubQuery1() {
+ final String query = "select \"product_class_id\", avg(\"product_id\")\n"
+ + "from (select \"product_class_id\", \"product_id\",
avg(\"product_class_id\")\n"
+ + "from \"product\"\n"
+ + "group by \"product_class_id\", \"product_id\") as t\n"
+ + "group by \"product_class_id\"";
+ final String expected = "SELECT \"product_class_id\",
AVG(\"product_id\")\n"
+ + "FROM (SELECT \"product_class_id\", \"product_id\"\n"
+ + "FROM \"foodmart\".\"product\"\n"
+ + "GROUP BY \"product_class_id\", \"product_id\") AS \"t1\"\n"
+ + "GROUP BY \"product_class_id\"";
+ sql(query).ok(expected);
+ }
+
+ /** Test query without group by but an aggregate function
+ * and a sub-query which is with group by. */
+ @Test public void testSelectQueryWithGroupBySubQuery2() {
+ final String query = "select sum(\"product_id\")\n"
+ + "from (select \"product_class_id\", \"product_id\"\n"
+ + "from \"product\"\n"
+ + "group by \"product_class_id\", \"product_id\") as t";
+ final String expected = "SELECT SUM(\"product_id\")\n"
+ + "FROM (SELECT \"product_id\"\n"
+ + "FROM \"foodmart\".\"product\"\n"
+ + "GROUP BY \"product_class_id\", \"product_id\") AS \"t1\"";
+ final String expectedMysql = "SELECT SUM(`product_id`)\n"
+ + "FROM (SELECT `product_id`\n"
+ + "FROM `foodmart`.`product`\n"
+ + "GROUP BY `product_class_id`, `product_id`) AS `t1`";
Review comment:
Yes, I see this in other test cases.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
[email protected]
With regards,
Apache Git Services
