[jira] [Commented] (CALCITE-1946) Dialects lacking support for nested aggregations should use sub select instead

["Pawel Ruchaj (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/CALCITE-1946?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16152796#comment-16152796 ] 

Pawel Ruchaj commented on CALCITE-1946:
---------------------------------------

I think we need a code which checks that:
 - current SQL dialect does not support nested aggregations
 - looks at the current and the next relation, to see if current and next relations are aggregations.
 - if the same field is in use in both aggregations and also used inside aggregation functions
 - only then the new layer is created in such scenarios.

The problem with above algo is that due to recursive nature of this code I have no access to see next relation.
WDYT [~julianhyde]

> Dialects lacking support for nested aggregations should use sub select instead
> ------------------------------------------------------------------------------
>
>                 Key: CALCITE-1946
>                 URL: https://issues.apache.org/jira/browse/CALCITE-1946
>             Project: Calcite
>          Issue Type: Bug
>          Components: core
>    Affects Versions: 1.14.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}
> - PostgreSQL 9.6:
>    {code}ERROR: aggregate function calls cannot be nested{code}
> h4. Test Case
> {code:java}
>   @Test public void testEnginesLackingSupportForNestedAggregationsShouldUseSubSelectInstead() {
>     final String query = "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 expectedOracle = "SELECT SUM(SUM(\"net_weight\")) \"net_weight_converted\"\n"
>         + "FROM \"foodmart\".\"product\"\n"
>         + "GROUP BY \"product_id\"";
>     final String expectedMySQL = "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`";
>     final String expectedVertica = "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\"";
>     final String expectedPostgresql = "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(query)
>         .dialect(DatabaseProduct.ORACLE.getDialect())
>         .ok(expectedOracle)
>         .dialect(DatabaseProduct.MYSQL.getDialect())
>         .ok(expectedMySQL)
>         .dialect(DatabaseProduct.VERTICA.getDialect())
>         .ok(expectedVertica)
>         .dialect(DatabaseProduct.POSTGRESQL.getDialect())
>         .ok(expectedPostgresql);
>  }
> {code}



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)