[
https://issues.apache.org/jira/browse/CALCITE-6677?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17896118#comment-17896118
]
Mihai Budiu commented on CALCITE-6677:
--------------------------------------
This is the validation sequence from SqlValidatorImpl:
{code:java}
validateWhereClause(select);
validateGroupClause(select);
validateHavingClause(select);
validateWindowClause(select);
validateQualifyClause(select);
handleOffsetFetch(select.getOffset(), select.getFetch());
// Validate the SELECT clause late, because a select item might
// depend on the GROUP BY list, or the window function might reference
// window name in the WINDOW clause etc.
final RelDataType rowType =
validateSelectList(selectItems, select, targetRowType);
{code}
So the GROUP BY is validated, and the cast is inserted by coercion in the GROUP
BY statement.
Then the HAVING is validated, and the having checks the SELECT.
But the SELECT itself hasn't been validated yet, so it doesn't have the cast
yet.
A second problem is that the HAVING validation checks the aggregation
conditions before inferring types, so it won't have the coercion either.
> HAVING clauses fail validation when type coercion is applied to GROUP BY
> clause
> -------------------------------------------------------------------------------
>
> Key: CALCITE-6677
> URL: https://issues.apache.org/jira/browse/CALCITE-6677
> Project: Calcite
> Issue Type: Bug
> Reporter: Tracy Sassaman
> Priority: Critical
>
> Given a sql statement like:
> select if(EMP.empno <= CAST(18 AS DOUBLE), 'youth', 'adult') as adult_or_child
> from EMP
> GROUP BY if(EMP.empno <= CAST(18 AS DOUBLE), 'youth', 'adult')
> HAVING if(EMP.empno <= CAST(18 AS DOUBLE), 'youth', 'adult') = 'adult'
>
> Sql Validation fails with the error:
> org.apache.calcite.sql.validate.SqlValidatorException: Expression 'EMP.EMPNO'
> is not being grouped
>
> Root cause: A cast is applied to the group by clause, to make it GROUP BY if(
> CAST (EMP.empno AS DOUBLE) <= CAST(18 AS DOUBLE), 'youth', 'adult')
> But no such cast is applied to the Having Clause. Consequently, an equality
> comparison on the two fails
>
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
