[
https://issues.apache.org/jira/browse/CALCITE-6677?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17896069#comment-17896069
]
Tracy Sassaman commented on CALCITE-6677:
-----------------------------------------
Yes, it's failing on the latest main. (Or the latest as of 2 days ago). To
repro, you can try this test:
```
SqlValidatorFixture f =
fixture().withExtendedCatalog()
.withOperatorTable(operatorTableFor(SqlLibrary.BIG_QUERY));
final String sql = "select if(EMP.empno <= CAST(18 AS DOUBLE), 'youth',
'adult') as adult_or_child\n"
+ "from EMP \n"
+ "GROUP BY if(EMP.empno <= CAST(18 AS DOUBLE), 'youth', 'adult')\n"
+ " HAVING if(EMP.empno <= CAST(18 AS DOUBLE), 'youth',
'adult') = 'adult'";
f.withSql(sql).ok();
```
> 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)
