Corvin Kuebler created CALCITE-6503: ---------------------------------------
Summary: Simple `NOT IN` is not pushed via JdbcAdapter Key: CALCITE-6503 URL: https://issues.apache.org/jira/browse/CALCITE-6503 Project: Calcite Issue Type: Bug Components: core, jdbc-adapter Affects Versions: 1.37.0 Reporter: Corvin Kuebler Hey all! Adding the following test to JdbcAdapterTests fails: {code:java} @Test void notInNotPushed() { CalciteAssert.model(JdbcTest.SCOTT_MODEL) .query("select * from dept where deptno not in (select deptno from emp)") .explainMatches("", CalciteAssert.checkResultContains("EnumerableMergeJoin", 0)) .explainMatches("", CalciteAssert.checkResultContains("EnumerableAggregate", 0)) .explainMatches("", CalciteAssert.checkResultContains("EnumerableSort", 0)); } {code} Failure: {noformat} PLAN=EnumerableCalc(expr#0..6=[{inputs}], expr#7=[0], expr#8=[=($t3, $t7)], expr#9=[IS NULL($t6)], expr#10=[>=($t4, $t3)], expr#11=[AND($t9, $t10)], expr#12=[OR($t8, $t11)], proj#0..2=[{exprs}], $condition=[$t12]) EnumerableMergeJoin(condition=[=($0, $5)], joinType=[left]) JdbcToEnumerableConverter JdbcSort(sort0=[$0], dir0=[ASC]) JdbcJoin(condition=[true], joinType=[inner]) JdbcTableScan(table=[[SCOTT, DEPT]]) JdbcAggregate(group=[{}], c=[COUNT()], ck=[COUNT($7)]) JdbcTableScan(table=[[SCOTT, EMP]]) EnumerableSort(sort0=[$0], dir0=[ASC]) EnumerableAggregate(group=[{7}], i=[LITERAL_AGG(true)]) JdbcToEnumerableConverter JdbcTableScan(table=[[SCOTT, EMP]]) should have 0 occurrence of EnumerableMergeJoin Expected :0 Actual :1 {noformat} The test shows, that instead of pushing the statement to the database, it gets rewritten and then evaulated in memory. -- This message was sent by Atlassian Jira (v8.20.10#820010)