[
https://issues.apache.org/jira/browse/CALCITE-5412?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17642143#comment-17642143
]
Leonid Chistov commented on CALCITE-5412:
-----------------------------------------
[~julianhyde]
Sure, will do, thanks for review.
About the "GROUPING SETS" I was probably not specific enough, I meant that we
have an issue with all three dialects mentioned above:
* For HIVE it seems to be supported only in the 'postfix' format "GROUP BY a,
b, c GROUPING SETS ( (a, b, c), (a, b), (b, c), (a, c), (a), (b), (c), ( ))."
* For MySQL5.7 it seems to be not supported at all
* For MsSQL - I actually start to wonder if this dialect assumes all versions
of SQL Server up to the current one (2022). If this is true, then we have
another bug: we don't specify that it (at least new versions) supports
ROLLUP(...) and CUBE(...) syntax in addition to non-standard WITH ROLLUP and
WITH CUBE.
> ROLLUP/CUBE non-standard syntax support logic is buggy
> ------------------------------------------------------
>
> Key: CALCITE-5412
> URL: https://issues.apache.org/jira/browse/CALCITE-5412
> Project: Calcite
> Issue Type: Bug
> Affects Versions: 1.32.0
> Reporter: Leonid Chistov
> Priority: Minor
>
> There are currently several dialects supported by Calcite which share a logic
> of using 'GROUP BY ... WITH ROLLUP' or 'GROUP BY ... WITH CUBE' syntax
> instead of 'GROUP BY ROLLUP(...)' and 'GROUP BY CUBE(...)' syntax: MySQL5,
> MsSQL, Hive and Spark.
> Note: actually Spark should not be on this list anymore, see
> https://issues.apache.org/jira/browse/CALCITE-5411
> Following issues exist regarding current implementation of this logic:
> * There is a bug in 'Result RelToSqlConverter::visit(Sort e)' method. Check
> 'hasTrickyRollup' does not take into account possibility of different order
> of fields in 'ROLLUP' clause and 'ORDER BY' clause. Because of this, we loose
> information about order of fields in 'ORDER BY' clause in the conversion
> under this check.
> * CUBE clause seems to be not supported in MYSQL5.x in any form
> ([https://dev.mysql.com/doc/refman/5.7/en/group-by-modifiers.html]), but we
> don’t check it.
> * We don't have an check for the “GROUP BY GROUPING SETS(...)” syntax
> support, thus we always generate SQL code for such clause, as if it was
> supported
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
