[ https://issues.apache.org/jira/browse/CALCITE-5518?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17690859#comment-17690859 ]
Jiajun Xie commented on CALCITE-5518: ------------------------------------- [~julianhyde], your advice is useful. Thank you very much! > 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: 50m > 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)