[
https://issues.apache.org/jira/browse/CALCITE-4541?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17342744#comment-17342744
]
Julian Hyde commented on CALCITE-4541:
--------------------------------------
I propose the following behavior (when Calcite is running in a mode consistent
with PostgreSQL, group by ordinal and alias):
{code:java}
# valid, group by ordinal
SELECT deptno FROM Emp GROUP BY 1;
# invalid, group by an ordinal that is out of range
SELECT deptno FROM Emp GROUP BY -1;
# valid, group by an expression that happens to be constant
# (PostgreSQL throws "non-integer constant in GROUP BY",
# BigQuery throws "Cannot group by literal values")
SELECT deptno FROM Emp GROUP BY deptno, 'a';
# valid, group by an expression that happens to be constant
# (PostgreSQL and BigQuery throw)
SELECT deptno FROM Emp GROUP BY deptno, 1.0;
# valid, group by an expression that happens to be constant
# (PostgreSQL and BigQuery throw)
SELECT deptno FROM Emp GROUP BY deptno, null;
# valid, group by an expression that happens to be constant
# (BigQuery throws; PostgreSQL doesn't throw - I think they intended to!)
SELECT deptno FROM Emp GROUP BY deptno, true;
# valid (group by expression)
SELECT deptno FROM Emp GROUP BY deptno, 1 + 2;
# valid (group by expression)
SELECT deptno FROM Emp GROUP BY deptno, empno + 2;
# valid (group by alias)
SELECT '3' AS x FROM Emp GROUP BY x;
# valid (group by alias)
SELECT 3 AS x FROM Emp GROUP BY x;
# valid (group by alias)
SELECT deptno AS "1" FROM Emp GROUP BY "1";
{code}
In summary:
* Group by ordinal behavior kicks in only if the top-level group expression is
an integer literal (1 yes, 100 yes, -1 yes, 0 yes, 1.5 no, 1.0 no, '1' no, null
no, true no)
* PostgreSQL and BigQuery throw when a top-level group expression is a literal
but not an integer literal. I don't think it's useful to throw in this case.
Calcite does not and should not.
> group by numerical literal will collide with group by ordinal in default sql
> validator
> --------------------------------------------------------------------------------------
>
> Key: CALCITE-4541
> URL: https://issues.apache.org/jira/browse/CALCITE-4541
> Project: Calcite
> Issue Type: Bug
> Components: core
> Affects Versions: 1.26.0
> Environment: open-jdk8
> Reporter: yanjing.wang
> Priority: Major
> Fix For: 1.27.0
>
>
> {code:java}
> String sql = "select 20210101 as ds , count(distinct id) as c from users
> where ds = 20210101 group by ds";
> {code}
>
> 'group by ds' expression will be expanded to 'group by 20210101' at line
> 'expandGroupByOrHavingExpr' of 'validateGroupClause(SqlSelect select)' method.
>
> but when GroupByScope executes 'validateExpr(SqlNode expr)', it will check if
> 'group by 20210101' is a group by ordinal literal such as 'group by 1, 2'
> etc. . and in 'visit(SqlLiteral literal)' method of 'ExtendedExpander' class
> does this expanding.
>
> the problem raises when check if it is a 'group by ordinal', it only has a
> condition
>
> {code:java}
> boolean isOrdinalLiteral = literal == root;
> {code}
> this is always true in this scenario.
>
> it raise exception 'SqlValidatorException: Ordinal out of range when
> executing following code.
> {code:java}
> if (intValue < 1 || intValue > select.getSelectList().size()) { throw
> validator.newValidationError(literal, RESOURCE.orderByOrdinalOutOfRange());
> }{code}
>
> I think whether we need check 'originalExprs' instance variable of
> sqlValidatorImpl class contains the literal. if so, it can be verified as not
> an ordinal. but I'm not sure whether this approach is correct.
>
>
>
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
