[
https://issues.apache.org/jira/browse/CALCITE-4340?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17312106#comment-17312106
]
Julian Hyde commented on CALCITE-4340:
--------------------------------------
Reviewing [PR 2364|https://github.com/apache/calcite/pull/2364] (and responding
to the ["Supporting subqueries in ON clauses" email
thread|https://lists.apache.org/thread.html/reeeaba9406c90bfe8b8eb785ed94d78bcc8f7169d9096f8de34eb86c%40%3Cdev.calcite.apache.org%3E]).
There are two ways of handling sub-queries: one is to have SqlToRelConverter
expand them during translation, and the other is to wrap them in RexSubQuery
and treat them as scalar expressions until they are handled by
SubQueryRemoveRule. I couldn't tell which approach you are taking, but I
recommend the latter because it is less disruptive to the intricate machinery
of SqlToRelConverter.
I see you have some SqlToRelConverter tests checking that particular exceptions
are thrown. I know that the exceptions reflect current limitations of
SqlToRelConverter, but still, we shouldn't expose such implementation details
to the user. I think those exceptions should be thrown by the validator, and
tested in SqlValidatorTest. Feel free to have SqlToRelConverter throw
AssertionError if it hits queries that it can't handle; SqlValidator should
have caught them first.
The error message "Subqueries in on clauses are only supported for inner and
left joins." would be better as "Sub-queries in ON clauses are only supported
for inner and left joins". (Note hyphen, capitalization, and period.)
> Correlated Queries in ON clause do not work when referring to left side
> -----------------------------------------------------------------------
>
> Key: CALCITE-4340
> URL: https://issues.apache.org/jira/browse/CALCITE-4340
> Project: Calcite
> Issue Type: Improvement
> Components: core
> Reporter: James Starr
> Priority: Major
> Labels: pull-request-available
> Time Spent: 1h 10m
> Remaining Estimate: 0h
>
> Changes from CALCITE-4210 does not robustly support correlated queries in ON
> clauses.
> For the short term I am going revert the logic for supporting ON clause and
> throw an exception when correlated queries occur in ON clause.
> Then I will follow up with a fix that will support single subqueries in on
> clauses. This will require fixing the offset in Blackboard.register,
> correctly detect if correlated variable is on the being used and adding a
> rule RelDecorrelator joined decorrelations.
> Given:
> {code:sql}
> SELECT *
> FROM dept
> LEFT JOIN bonus ON bonus.job = (
> SELECT emp.job
> FROM emp
> WHERE emp.deptno = dept.deptno
> )
> {code}
> Should generate
> {code}
> LogicalProject(DEPTNO=[$0], NAME=[$1], ENAME=[$2], JOB=[$3], SAL=[$4],
> COMM=[$5])
> LogicalCorrelate(correlation=[$cor0], joinType=[left],
> requiredColumns=[{0}]) //requiring dept.DEPTNO as the correlated value
> LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> LogicalFilter(condition=[=($1, $4)]) //This is comparing bonus.job =
> emp.job
> LogicalJoin(condition=[true], joinType=[left])
> LogicalTableScan(table=[[CATALOG, SALES, BONUS]])
> LogicalAggregate(group=[{}], agg#0=[SINGLE_VALUE($0)])
> LogicalProject(JOB=[$2])
> LogicalFilter(condition=[=($7, $cor0.DEPTNO)]) //all employees
> for a given department
> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> {code}
> But instead generates:
> {code}
> LogicalProject(DEPTNO=[$0], NAME=[$1], ENAME=[$2], JOB=[$3], SAL=[$4],
> COMM=[$5])
> LogicalJoin(condition=[=($3, $0)], joinType=[left])
> LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> LogicalCorrelate(correlation=[$cor0], joinType=[left],
> requiredColumns=[{0}])
> LogicalTableScan(table=[[CATALOG, SALES, BONUS]])
> LogicalAggregate(group=[{}], agg#0=[SINGLE_VALUE($0)])
> LogicalProject(JOB=[$2])
> LogicalFilter(condition=[=($7, $cor0.DEPTNO)])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> {code}
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
