[ https://issues.apache.org/jira/browse/CALCITE-1946?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16129517#comment-16129517 ]
Pawel Ruchaj edited comment on CALCITE-1946 at 8/16/17 10:13 PM: ----------------------------------------------------------------- Triggering needNew=true in SqlImplementor.builder when building LogicalProject#9 creates required select {code} LogicalAggregate#10(groupSets={}, NET_WEIGHT=[SUM($0)]) LogicalProject#9(exps=[$1]) LogicalAggregate#8(groupSets={1}, NET_WEIGHT=[SUM($1)]) LogicalProject#7(product_id=[$1], net_weight=[$7]) JdbcTableScan#6(table=[[foodmart, product]]) {code} was (Author: pawel.ruchaj): Triggering needNew in SqlImplementor.builder when building LogicalProject#9 creates required select {code} LogicalAggregate#10(groupSets={}, NET_WEIGHT=[SUM($0)]) LogicalProject#9(exps=[$1]) LogicalAggregate#8(groupSets={1}, NET_WEIGHT=[SUM($1)]) LogicalProject#7(product_id=[$1], net_weight=[$7]) JdbcTableScan#6(table=[[foodmart, product]]) {code} > 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)