[
https://issues.apache.org/jira/browse/CALCITE-4100?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17149811#comment-17149811
]
Rui Wang commented on CALCITE-4100:
-----------------------------------
Let's wait to see what are other people's opinions.
> Join plan with select MAX subquery drops MAX operator
> ------------------------------------------------------
>
> Key: CALCITE-4100
> URL: https://issues.apache.org/jira/browse/CALCITE-4100
> Project: Calcite
> Issue Type: Bug
> Components: core
> Affects Versions: 1.23.0
> Reporter: Sean Broeder
> Priority: Major
>
> The following SQL query can be used to demonstrate the issue
> {code:sql}
> // Some comments here
> select e.empno, e.sal, e.deptno emp_dept, d.deptno dep_dept
> from emp e
> left join
> dept d
> on e.deptno = (
> select max(sal)
> from emp
> where deptno = e.deptno)
> {code}
> The above query generates the following plan which does not contain the MAX
> operator
> {code:java}
> LogicalProject(EMPNO=[$0], SAL=[$5], EMP_DEPT=[$7], DEP_DEPT=[$9])
> LogicalJoin(condition=[=($0, $7)], joinType=[left])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> {code}
> However, if I just issue the subquery with the max operation I do get a plan
> with the MAX operator.
> select max(sal) from emp
> This query generates the following plan
> {code:java}
> LogicalAggregate(group=[{}], EXPR$0=[MAX($0)])
> LogicalProject(SAL=[$5])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> {code}
>
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
