Pawel Ruchaj created CALCITE-1946: ------------------------------------- Summary: Query containing cascaded aggregation gets converted to incorrect sql Key: CALCITE-1946 URL: https://issues.apache.org/jira/browse/CALCITE-1946 Project: Calcite Issue Type: Bug Components: core Affects Versions: 1.13.0 Reporter: Pawel Ruchaj Assignee: Julian Hyde
Below query, containing 2 SUM aggregation functions and sub-select, gets converted to a SQL that fails when executed in various SQL engines. h4. SQL {code:sql} SELECT SUM("net_weight1") AS "net_weight_converted" FROM ( SELECT SUM(" net_weight") AS "net_weight1" FROM "foodmart"."product" GROUP BY "product_id") {code} h4. Expected: {code:sql} SELECT SUM("net_weight1") AS "net_weight_converted" FROM (SELECT "product_id", SUM("net_weight") AS "net_weight1" FROM "foodmart"."product" GROUP BY "product_id") AS "t0" {code} h4. Actual: {code:sql} SELECT SUM(SUM("net_weight")) AS "net_weight_converted" FROM "foodmart"."product" GROUP BY "product_id" {code} h4. Returned errors: - MySQL 5.1.73 {code}Invalid use of group function{code} - MemSQL 5.5.8: {code}[HY000][1111] Invalid use of group function{code} - HP Vertica: 7.2.1-0: {code}[42803][2135] [Vertica][VJDBC](2135) ERROR: Aggregate function calls may not be nested java.lang.RuntimeException: com.vertica.support.exceptions.SyntaxErrorException: [Vertica][VJDBC](2135) ERROR: Aggregate function calls may not be nested{code} h4. Test Case {code:java} @Test public void testSumSelectSum() { final String sql = "select\n" + " SUM(\"net_weight1\") as \"net_weight_converted\"\n" + " from (" + " select\n" + " SUM(\"net_weight\") as \"net_weight1\"\n" + " from \"foodmart\".\"product\"\n" + " group by \"product_id\")"; final String expected = "SELECT SUM(\"net_weight1\") AS \"net_weight_converted\"\n" + "FROM (SELECT \"product_id\", SUM(\"net_weight\") AS \"net_weight1\"\n" + "FROM \"foodmart\".\"product\"\n" + "GROUP BY \"product_id\") AS \"t0\""; sql(sql).ok(expected); } {code} -- This message was sent by Atlassian JIRA (v6.4.14#64029)