[
https://issues.apache.org/jira/browse/CALCITE-6503?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17869353#comment-17869353
]
Corvin Kuebler commented on CALCITE-6503:
-----------------------------------------
Hey !
This actually might be the issue.
But imho the rewrite from
[SubQueryRemoveRule|https://github.com/apache/calcite/blob/0808a1db21e8d60a06fd00f2ec160ce99485595f/core/src/main/java/org/apache/calcite/rel/rules/SubQueryRemoveRule.java#L555]
should never happen if the result cannot be pushed down via JdbcAdapter. Would
you agree [~nobigo]?
> 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)
