[jira] [Commented] (CALCITE-5486) SubQuery not support HAVING alias in where condition

["Benchao Li (Jira)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/CALCITE-5486?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17691332#comment-17691332 ] 

Benchao Li commented on CALCITE-5486:
-------------------------------------

[~jiajunbernoulli] I've gone through the PR again, and I think current solution seems not good enough.  

The reason for this issue is that validator tries to infer unknown types for sub-queries before expanding expressions. You'll find more errors, e.g. scalar sub-queries in select also suffers this, group by alias and order by alias also not work in a similar way. 

Your fix puts a {{validateHavingClause}} in {{validateWhereOrOn}}, this makes the code obscure and hard to maintain IMO. 

> SubQuery not support HAVING alias in where condition
> ----------------------------------------------------
>
>                 Key: CALCITE-5486
>                 URL: https://issues.apache.org/jira/browse/CALCITE-5486
>             Project: Calcite
>          Issue Type: Improvement
>          Components: core
>            Reporter: Jiajun Xie
>            Assignee: Jiajun Xie
>            Priority: Major
>              Labels: pull-request-available
>          Time Spent: 1h 20m
>  Remaining Estimate: 0h
>
> Spark can allow HAVING alias in condition. 
> We also should allow HAVING alias in condition, here is the simple case.
> {code:java}
> sql("select * from emp where sal >\n"
>     + " (select avg(sal) as s"
>     + "    from emp having ^s^ > 0"
>     + "  )")
>     .withConformance(SqlConformanceEnum.LENIENT)
>     .fails("Column 'S' not found in any table"); // should be ok, but now is fails{code}



--
This message was sent by Atlassian Jira
(v8.20.10#820010)