[
https://issues.apache.org/jira/browse/CALCITE-6503?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17869459#comment-17869459
]
xiong duan commented on CALCITE-6503:
-------------------------------------
[~corvinkuebler], A part of the entire SQL logic plan can be pushed down to the
JDBC data source, as long as we can ensure the results are correct. So the
behavior is correct.
> Simple `NOT IN` filter 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
> Priority: Critical
>
> 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.
> *Update:*
> The statement will be pushed if you add a limit that is exactly 1 to the not
> in condition
> e.g.
> {code:java}
> @Test void notInNotPushed() {
> CalciteAssert.model(JdbcTest.SCOTT_MODEL)
> .query("select * from dept where deptno not in (select deptno from
> emp limit 1)")
> .explainMatches("",
> CalciteAssert.checkResultContains("EnumerableMergeJoin", 0))
> .explainMatches("",
> CalciteAssert.checkResultContains("EnumerableAggregate", 0))
> .explainMatches("",
> CalciteAssert.checkResultContains("EnumerableSort", 0));
> }
> {code}
> works
> {code:java}
> @Test void notInNotPushed() {
> CalciteAssert.model(JdbcTest.SCOTT_MODEL)
> .query("select * from dept where deptno not in (select deptno from
> emp limit 2)")
> .explainMatches("",
> CalciteAssert.checkResultContains("EnumerableMergeJoin", 0))
> .explainMatches("",
> CalciteAssert.checkResultContains("EnumerableAggregate", 0))
> .explainMatches("",
> CalciteAssert.checkResultContains("EnumerableSort", 0));
> }
> {code}
> broken
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
