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

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 1.13.0
• Fix Version/s: 1.15.0
• Component/s: jdbc-adapter
• Assignee: Julian Hyde
• Reporter: Pawel Ruchaj
• Created: 16/Aug/17 20:37
• Updated: 09/Dec/17 18:21
• Resolved: 02/Oct/17 21:05

Description

Below query, containing 2 SUM aggregation functions and sub-select, gets converted to a SQL that fails when executed in various SQL engines.

SQL

SELECT SUM("net_weight1") AS "net_weight_converted"
FROM (
  SELECT SUM(" net_weight") AS "net_weight1"
  FROM "foodmart"."product"
  GROUP BY "product_id")

Expected:

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"

Actual:

SELECT SUM(SUM("net_weight")) AS "net_weight_converted"
FROM "foodmart"."product"
GROUP BY "product_id"

Returned errors:

• MySQL 5.1.73

  Invalid use of group function

• MemSQL 5.5.8:

  [HY000][1111] Invalid use of group function

• HP Vertica: 7.2.1-0:

  [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

• PostgreSQL 9.6:

  ERROR: aggregate function calls cannot be nested

Test Case

  @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);
 }