[
https://issues.apache.org/jira/browse/CALCITE-4541?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17342577#comment-17342577
]
yanjing.wang commented on CALCITE-4541:
---------------------------------------
I find the query *SELECT '3' FROM t group by '3' on BigQuery* throws
{color:#FF0000}cannot GROUP BY literal values{color} exception, but the query
*SELECT '3' as a FROM t group by a* passes. Calcite passes all, I think
BigQuery's behavior is somewhat weird.
And I find GROUP BY EXACT INT LITERAL on BigQuery will always be treated as
GROUP BY Ordinal, but GROUP BY DECIMAL whose fractional part is non-zero not.
for example: *SELECT 3.2 FROM t group by 3.2* will throws
{color:#FF0000}cannot GROUP BY literal values{color} exception. *SELECT 3 FROM
t group by 3* throws {color:#FF0000}out of range{color} exception.
for this problem, I think the following solution may be reasonable,
# for Group By decimal with non-zero fractional part, we treat it as Group By
literal.
# for Group By exact int, if the int value is out of select column number
range, and we find equal select column, we prefer literal, otherwise we prefer
ordinal.
how do you think? [~julianhyde]
> 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)
