[ https://issues.apache.org/jira/browse/CALCITE-5518?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17686964#comment-17686964 ]
Leonid Chistov commented on CALCITE-5518: ----------------------------------------- The source of this bug is the following: {code:java} private List<SqlNode> generateGroupList(Builder builder, List<SqlNode> selectList, Aggregate aggregate, List<Integer> groupList) { ......... switch (aggregate.getGroupType()) { .......... case ROLLUP: return ImmutableList.of( SqlStdOperatorTable.ROLLUP.createCall(SqlParserPos.ZERO, groupKeys));{code} Here we create ROLLUP SQL clause ignoring the "rolling up order" of original Aggregate. Grouping sets "\{0,1}, \{0}, {}" and "\{0, 1}, \{1}, {}" are both classified as ROLLUP-s and they have equal list of grouping keys (0, 1), but different SQL needs to be generated for them. > 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)