Gian Merlino created CALCITE-1799: ------------------------------------- Summary: OR IN Subquery conversion wrong Key: CALCITE-1799 URL: https://issues.apache.org/jira/browse/CALCITE-1799 Project: Calcite Issue Type: Bug Components: core Affects Versions: 1.12.0 Reporter: Gian Merlino Assignee: Julian Hyde
This query: {code} select * from emp where deptno = 10 or deptno in ( select dept.deptno from dept where deptno < 5) {code} Is converted to this by SqlToRelConverter: {code} LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8]) LogicalFilter(condition=[OR(=($7, 10), true)]) LogicalJoin(condition=[=($7, $9)], joinType=[inner]) LogicalTableScan(table=[[CATALOG, SALES, EMP]]) LogicalAggregate(group=[{0}]) LogicalProject(DEPTNO=[$0]) LogicalFilter(condition=[<($0, 5)]) LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) {code} But that's not right. {code}LogicalFilter(condition=[OR(=($7, 10), true)]){code} is always true and is in the wrong place anyway (it's applied after the inner join, where all the deptno = 10 records have already been removed). -- This message was sent by Atlassian JIRA (v6.3.15#6346)