[
https://issues.apache.org/jira/browse/IGNITE-25077?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17947304#comment-17947304
]
Iurii Gerzhedovich commented on IGNITE-25077:
---------------------------------------------
SQL standard says GROUP BY should be followed by ‘grouping column reference’,
empty group or cube() call or `rollup()` call.
‘grouping column reference’ is a basic identifier chain (qualified column
names).
I see no mention of where expression or whatever else can follow GROUP BY.
In Syntax Rules (7.13 <group by clause>):
{code:java}
Each <grouping column reference> shall unambiguously reference a column of the
table resulting from the <from clause>
{code}
I’ve checked it on a few databases, and what we have:
Oracle - ORA-00904: "K": invalid identifier
PG - Error 42703 column "k" does not exist
MS SQL - can execute, result is 1, 6
SQLite - can execute, result is 1, 6
Based on the comment, consider that the error is expected
> Sql. Introduced alias can not be referenced in GROUP BY and GROUPING SET
> clauses
> --------------------------------------------------------------------------------
>
> Key: IGNITE-25077
> URL: https://issues.apache.org/jira/browse/IGNITE-25077
> Project: Ignite
> Issue Type: Bug
> Components: sql
> Reporter: Maksim Zhuravkov
> Assignee: Iurii Gerzhedovich
> Priority: Major
> Labels: ignite-3
>
> Prior to Calcite 1.39 the following queries worked:
> {noformat}
> # aliases can be referenced in group by <columns> clause.
> query II
> SELECT 1 AS k, SUM(i) FROM integers GROUP BY k+1 ORDER BY 2
> ----
> 1 6
> # aliases can be referenced in group by <grouping sets> clause.
> query II
> SELECT 1 AS k, SUM(i) FROM integers GROUP BY GROUPING SETS ((k+1)) ORDER BY 2
> ----
> 1 6
> {noformat}
> With calcite 1.39 they both result in an error:
> {noformat}
> Column 'K' not found in any table
> {noformat}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
