[jira] [Created] (CALCITE-1946) Query containing cascaded aggregation gets converted to incorrect sql

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

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)