[
https://issues.apache.org/jira/browse/CALCITE-6503?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Corvin Kuebler updated CALCITE-6503:
------------------------------------
Description:
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
was:
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.
> 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)
