[
https://issues.apache.org/jira/browse/CALCITE-1946?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Pawel Ruchaj updated CALCITE-1946:
----------------------------------
Description:
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 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}
was:
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}
> 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}
> - PostgreSQL 9.6:
> {code}ERROR: aggregate function calls cannot 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)
