[
https://issues.apache.org/jira/browse/CALCITE-5518?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17686961#comment-17686961
]
Leonid Chistov commented on CALCITE-5518:
-----------------------------------------
[~julianhyde] no, it is not a duplicate, unfortunately.
CALCITE-5416 is about wrong processing of ROLLUP clause under ORDER BY clause
in some situations.
Current issue is about wrong processing of some cases of ROLLUP (regardless of
having or not having subsequent ordering).
P.S.
It looks like a fix branch for CALCITE-5416 was updated according to your
review ([https://github.com/apache/calcite/pull/2997).
|https://github.com/apache/calcite/pull/2997)]
Would you mind to have a look once you have a time for that? :)
> 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
> Priority: Major
>
> 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)
