[
https://issues.apache.org/jira/browse/CALCITE-5518?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17687541#comment-17687541
]
Jiajun Xie commented on CALCITE-5518:
-------------------------------------
Here is my understanding of {*}Natural Order{*}:
* 2 fileds
{code:java}
-- natural order
ROLLUP(a, b)
is equals to
GROUPING SETS(
(a, b),
(a),
()
)
-- non-natural order
ROLLUP(b, a)
is equals to
GROUPING SETS(
(a, b), -- (b, a) is natural
(b),
()
){code}
* 3 fileds
{code:java}
-- natural order
ROLLUP(a, b, c)
is equals to
GROUPING SETS(
(a, b, c),
(a, b),
(a),
()
)
-- non-natural order
ROLLUP(a, c, b)
is equals to
GROUPING SETS(
(a, b, c), -- (a, c, b) is natural
(a, c),
(a),
()
) {code}
We could reverse group set to fix the bug, here is PR:
https://github.com/apache/calcite/pull/3066
> RelToSql converter generates invalid order of ROLLUP fields
> -----------------------------------------------------------
>
> Key: CALCITE-5518
> URL: https://issues.apache.org/jira/browse/CALCITE-5518
> Project: Calcite
> Issue Type: Bug
> Components: jdbc-adapter
> Affects Versions: 1.32.0
> Reporter: Leonid Chistov
> Assignee: Jiajun Xie
> Priority: Major
> Labels: pull-request-available
> Time Spent: 10m
> Remaining Estimate: 0h
>
> RelToSqlConverter generates wrong code for Aggregate nodes with grouping sets
> classified as `ROLLUP` grouping but with order of rollup not matching order
> of grouping fields.
> This can be demonstrated by the following test, that would fail if added to
> RelToSqlConverterTest class:
> {code:java}
> @Test void testGroupingSetsRollupNonNaturalOrder() {
> final String query = "select \"product_class_id\", \"brand_name\"\n"
> + "from \"product\"\n"
> + "group by GROUPING SETS ((\"product_class_id\", \"brand_name\"),"
> + " (\"brand_name\"), ())\n";
> final String expected = "SELECT \"product_class_id\", \"brand_name\"\n"
> + "FROM \"foodmart\".\"product\"\n"
> + "GROUP BY ROLLUP (\"brand_name\", \"product_class_id\")";
> sql(query)
> .withPostgresql().ok(expected);
> }{code}
> As the result we get the following SQL code:
> {code:java}
> SELECT product_class_id, brand_name
> FROM foodmart.product
> GROUP BY ROLLUP(product_class_id, brand_name){code}
> While the correct code would be:
> {code:java}
> SELECT product_class_id, brand_name
> FROM foodmart.product
> GROUP BY ROLLUP(brand_name, product_class_id){code}
> Source of the bug is that we treat grouping sets \{0, 1}, \{1}, {} as rollup
> grouping, but right after that we generate SQL code as if grouping sets were
> \{0, 1}, \{0}, {}.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
