[ https://issues.apache.org/jira/browse/CALCITE-5486?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17681511#comment-17681511 ]
Jiajun Xie edited comment on CALCITE-5486 at 1/28/23 4:37 AM: -------------------------------------------------------------- Agree with [~julianhyde] , it should apply to sub-queries as well as top-level queries. In my opinion, we need to do some things for SqlSelect that in condition. I have another simple case. {code:java} sql("select * from emp where sal in (" + "select emp order by sal offset ? rows fetch next ? rows only" + ")") .fails("Illegal use of dynamic parameter"); // should be ok {code} The solution is very similar, just append some codes in [SqlValidatorImpl#validateWhereOrOn|https://github.com/apache/calcite/blob/413eded693a9087402cc1a6eefeca7a29445d337/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java#L4422] {code:java} // Special treatment for sql select condition.accept(new SqlShuttle() { @Override public @Nullable SqlNode visit(SqlCall call) { if (call.getKind() == SqlKind.SELECT) { SqlSelect select = (SqlSelect) call; validateHavingClause(select); // support having alias handleOffsetFetch(select.getOffset(), select.getFetch()); // support dynamic parameter return select; } call.getOperandList().stream().forEach(node -> node.accept(this)); return call; } }); {code} was (Author: jiajunbernoulli): Agree with [~julianhyde] , it should apply to sub-queries as well as top-level queries. In my opinion, we need to validate many things for SqlSelect that in condition. I have another simple case. {code:java} sql("select * from emp where sal in (" + "select emp order by sal offset ? rows fetch next ? rows only" + ")") .fails("Illegal use of dynamic parameter"); // should be ok {code} The solution is very similar, just append some codes in [SqlValidatorImpl#validateWhereOrOn|https://github.com/apache/calcite/blob/413eded693a9087402cc1a6eefeca7a29445d337/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java#L4422] {code:java} // Special treatment for sql select condition.accept(new SqlShuttle() { @Override public @Nullable SqlNode visit(SqlCall call) { if (call.getKind() == SqlKind.SELECT) { SqlSelect select = (SqlSelect) call; validateHavingClause(select); // support having alias handleOffsetFetch(select.getOffset(), select.getFetch()); // support dynamic parameter return select; } call.getOperandList().stream().forEach(node -> node.accept(this)); return call; } }); {code} > Subquery not support HAVING alias in condition > ---------------------------------------------- > > Key: CALCITE-5486 > URL: https://issues.apache.org/jira/browse/CALCITE-5486 > Project: Calcite > Issue Type: Improvement > Components: core > Reporter: Jiajun Xie > Priority: Major > > 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)