Uploaded image for project: 'Calcite'
  1. Calcite
  2. CALCITE-1946

Dialects lacking support for nested aggregations should use sub select instead

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 1.13.0
    • Fix Version/s: 1.15.0
    • Component/s: jdbc-adapter
    • Labels:
      None

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

        Attachments

          Activity

            People

            • Assignee:
              julianhyde Julian Hyde
              Reporter:
              pawel.ruchaj Pawel Ruchaj
            • Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: