This is an automated email from the ASF dual-hosted git repository.
mbudiu pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/calcite.git
The following commit(s) were added to refs/heads/main by this push:
new 3fce658c05 [CALCITE-6786] ANY/SOME operator yields multiple rows in
correlated queries
3fce658c05 is described below
commit 3fce658c05aa88b44af8cbcfd0809a420896d62b
Author: Rafael Acevedo <[email protected]>
AuthorDate: Wed Jan 15 08:10:10 2025 -0300
[CALCITE-6786] ANY/SOME operator yields multiple rows in correlated queries
---
.../calcite/rel/rules/SubQueryRemoveRule.java | 13 ++--
.../org/apache/calcite/test/RelOptRulesTest.xml | 18 +++---
core/src/test/resources/sql/some.iq | 35 +++++++++++
core/src/test/resources/sql/sub-query.iq | 73 +++++++++++++---------
4 files changed, 94 insertions(+), 45 deletions(-)
diff --git
a/core/src/main/java/org/apache/calcite/rel/rules/SubQueryRemoveRule.java
b/core/src/main/java/org/apache/calcite/rel/rules/SubQueryRemoveRule.java
index 88065c61d5..585d938d7f 100644
--- a/core/src/main/java/org/apache/calcite/rel/rules/SubQueryRemoveRule.java
+++ b/core/src/main/java/org/apache/calcite/rel/rules/SubQueryRemoveRule.java
@@ -705,16 +705,15 @@ private static RexNode rewriteIn(RexSubQuery e,
Set<CorrelationId> variablesSet,
if (variablesSet.isEmpty()) {
builder.aggregate(builder.groupKey(builder.field("cs")),
builder.count(false, "c"));
-
- // sorts input with desc order since we are interested
- // only in the case when one of the values is true.
- // When true value is absent then we are interested
- // only in false value.
- builder.sortLimit(0, 1,
- ImmutableList.of(builder.desc(builder.field("cs"))));
} else {
builder.distinct();
}
+ // sorts input with desc order since we are interested
+ // only in the case when one of the values is true.
+ // When true value is absent then we are interested
+ // only in false value.
+ builder.sortLimit(0, 1,
+ ImmutableList.of(builder.desc(builder.field("cs"))));
}
// clears expressionOperands and fields lists since
// all expressions were used in the filter
diff --git
a/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
b/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
index 038ca94a57..60c683ee50 100644
--- a/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
+++ b/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
@@ -3662,18 +3662,20 @@ LogicalProject(EMPNO=[$0])
LogicalCorrelate(correlation=[$cor0], joinType=[left],
requiredColumns=[{1}])
LogicalProject(EMPNO=[$0], ENAME=[$1])
LogicalTableScan(table=[[CATALOG, SALES, EMPNULLABLES]])
+ LogicalSort(sort0=[$0], dir0=[DESC], fetch=[1])
+ LogicalAggregate(group=[{0}])
+ LogicalProject(cs=[IS NOT NULL($0)])
+ LogicalFilter(condition=[OR(=(1, $0), IS NULL($0))])
+ LogicalProject(DEPTNO=[$0])
+ LogicalFilter(condition=[AND(=($cor0.ENAME,
CAST($1):VARCHAR(20)), >($0, 10))])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPTNULLABLES]])
+ LogicalSort(sort0=[$0], dir0=[DESC], fetch=[1])
LogicalAggregate(group=[{0}])
LogicalProject(cs=[IS NOT NULL($0)])
- LogicalFilter(condition=[OR(=(1, $0), IS NULL($0))])
+ LogicalFilter(condition=[OR(=(2, $0), IS NULL($0))])
LogicalProject(DEPTNO=[$0])
- LogicalFilter(condition=[AND(=($cor0.ENAME,
CAST($1):VARCHAR(20)), >($0, 10))])
+ LogicalFilter(condition=[AND(=($cor0.ENAME,
CAST($1):VARCHAR(20)), <($0, 20))])
LogicalTableScan(table=[[CATALOG, SALES, DEPTNULLABLES]])
- LogicalAggregate(group=[{0}])
- LogicalProject(cs=[IS NOT NULL($0)])
- LogicalFilter(condition=[OR(=(2, $0), IS NULL($0))])
- LogicalProject(DEPTNO=[$0])
- LogicalFilter(condition=[AND(=($cor0.ENAME,
CAST($1):VARCHAR(20)), <($0, 20))])
- LogicalTableScan(table=[[CATALOG, SALES, DEPTNULLABLES]])
]]>
</Resource>
</TestCase>
diff --git a/core/src/test/resources/sql/some.iq
b/core/src/test/resources/sql/some.iq
index 4cb8692a17..8b76390081 100644
--- a/core/src/test/resources/sql/some.iq
+++ b/core/src/test/resources/sql/some.iq
@@ -895,6 +895,41 @@ where sal > some (4000, 2000);
!ok
+# CALCITE-6786: ANY/SOME operator yields multiple rows in correlated queries
+
+WITH tb as (select array(SELECT * FROM (VALUES (TRUE), (NULL)) as x(a)) as a)
+SELECT TRUE IN (SELECT b FROM UNNEST(a) AS x1(b)) AS test FROM tb;
++------+
+| TEST |
++------+
+| true |
++------+
+(1 row)
+
+!ok
+
+WITH tb as (select array(SELECT * FROM (VALUES (FALSE), (NULL)) as x(a)) as a)
+SELECT TRUE IN (SELECT b FROM UNNEST(a) AS x1(b)) AS test FROM tb;
++------+
+| TEST |
++------+
+| |
++------+
+(1 row)
+
+!ok
+
+WITH tb as (select array(SELECT * FROM (VALUES (FALSE), (TRUE)) as x(a)) as a)
+SELECT TRUE IN (SELECT b FROM UNNEST(a) AS x1(b)) AS test FROM tb;
++------+
+| TEST |
++------+
+| true |
++------+
+(1 row)
+
+!ok
+
select sal, sal > some (4000, 2000, null) from "scott".emp;
+---------+--------+
| SAL | EXPR$1 |
diff --git a/core/src/test/resources/sql/sub-query.iq
b/core/src/test/resources/sql/sub-query.iq
index 45e3e72a1b..1bee162a1e 100644
--- a/core/src/test/resources/sql/sub-query.iq
+++ b/core/src/test/resources/sql/sub-query.iq
@@ -1871,13 +1871,15 @@ select sal from "scott".emp e
(0 rows)
!ok
-EnumerableCalc(expr#0..4=[{inputs}], expr#5=[RAND()],
expr#6=[CAST($t5):INTEGER NOT NULL], expr#7=[2], expr#8=[MOD($t6, $t7)],
expr#9=[3], expr#10=[=($t8, $t9)], expr#11=[OR($t10, $t4)], SAL=[$t1],
$condition=[$t11])
- EnumerableMergeJoin(condition=[=($2, $3)], joinType=[left])
- EnumerableSort(sort0=[$2], dir0=[ASC])
- EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0], SAL=[$t5],
DEPTNO=[$t7])
- EnumerableTableScan(table=[[scott, EMP]])
- EnumerableCalc(expr#0..2=[{inputs}], expr#3=[false], DEPTNO=[$t0],
$f1=[$t3])
- EnumerableTableScan(table=[[scott, DEPT]])
+EnumerableCalc(expr#0..2=[{inputs}], expr#3=[RAND()],
expr#4=[CAST($t3):INTEGER NOT NULL], expr#5=[2], expr#6=[MOD($t4, $t5)],
expr#7=[3], expr#8=[=($t6, $t7)], expr#9=[OR($t8, $t2)], SAL=[$t0],
$condition=[$t9])
+ EnumerableCorrelate(correlation=[$cor0], joinType=[left],
requiredColumns=[{1}])
+ EnumerableCalc(expr#0..7=[{inputs}], SAL=[$t5], DEPTNO=[$t7])
+ EnumerableTableScan(table=[[scott, EMP]])
+ EnumerableLimit(fetch=[1])
+ EnumerableSort(sort0=[$0], dir0=[DESC])
+ EnumerableAggregate(group=[{0}])
+ EnumerableCalc(expr#0..2=[{inputs}], expr#3=[false], expr#4=[$cor0],
expr#5=[$t4.DEPTNO], expr#6=[=($t0, $t5)], cs=[$t3], $condition=[$t6])
+ EnumerableTableScan(table=[[scott, DEPT]])
!plan
# Test filter null IN nullable correlated
@@ -1958,13 +1960,15 @@ select sal from "scott".emp e
(0 rows)
!ok
-EnumerableCalc(expr#0..4=[{inputs}], expr#5=[NOT($t4)], expr#6=[IS NOT
NULL($t4)], expr#7=[OR($t5, $t6)], expr#8=[IS NOT TRUE($t7)], SAL=[$t1],
$condition=[$t8])
- EnumerableMergeJoin(condition=[=($2, $3)], joinType=[left])
- EnumerableSort(sort0=[$2], dir0=[ASC])
- EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0], SAL=[$t5],
DEPTNO=[$t7])
- EnumerableTableScan(table=[[scott, EMP]])
- EnumerableCalc(expr#0..2=[{inputs}], expr#3=[false], DEPTNO=[$t0],
$f1=[$t3])
- EnumerableTableScan(table=[[scott, DEPT]])
+EnumerableCalc(expr#0..2=[{inputs}], expr#3=[NOT($t2)], expr#4=[IS NOT
NULL($t2)], expr#5=[OR($t3, $t4)], expr#6=[IS NOT TRUE($t5)], SAL=[$t0],
$condition=[$t6])
+ EnumerableCorrelate(correlation=[$cor0], joinType=[left],
requiredColumns=[{1}])
+ EnumerableCalc(expr#0..7=[{inputs}], SAL=[$t5], DEPTNO=[$t7])
+ EnumerableTableScan(table=[[scott, EMP]])
+ EnumerableLimit(fetch=[1])
+ EnumerableSort(sort0=[$0], dir0=[DESC])
+ EnumerableAggregate(group=[{0}])
+ EnumerableCalc(expr#0..2=[{inputs}], expr#3=[false], expr#4=[$cor0],
expr#5=[$t4.DEPTNO], expr#6=[=($t5, $t0)], cs=[$t3], $condition=[$t6])
+ EnumerableTableScan(table=[[scott, DEPT]])
!plan
# Test filter null NOT IN literal correlated
@@ -2027,13 +2031,14 @@ select sal from "scott".emp e
(11 rows)
!ok
-EnumerableCalc(expr#0..4=[{inputs}], expr#5=[NOT($t4)], expr#6=[IS NOT
NULL($t4)], expr#7=[OR($t5, $t6)], expr#8=[IS NOT TRUE($t7)], SAL=[$t1],
$condition=[$t8])
- EnumerableMergeJoin(condition=[=($2, $3)], joinType=[left])
- EnumerableSort(sort0=[$2], dir0=[ASC])
- EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0], SAL=[$t5],
DEPTNO=[$t7])
- EnumerableTableScan(table=[[scott, EMP]])
- EnumerableCalc(expr#0..2=[{inputs}], expr#3=[true], expr#4=[10],
expr#5=[CAST($t0):INTEGER NOT NULL], expr#6=[=($t4, $t5)], DEPTNO1=[$t0],
$f1=[$t3], $condition=[$t6])
- EnumerableTableScan(table=[[scott, DEPT]])
+EnumerableCalc(expr#0..2=[{inputs}], expr#3=[NOT($t2)], expr#4=[IS NOT
NULL($t2)], expr#5=[OR($t3, $t4)], expr#6=[IS NOT TRUE($t5)], SAL=[$t0],
$condition=[$t6])
+ EnumerableCorrelate(correlation=[$cor0], joinType=[left],
requiredColumns=[{1}])
+ EnumerableCalc(expr#0..7=[{inputs}], SAL=[$t5], DEPTNO=[$t7])
+ EnumerableTableScan(table=[[scott, EMP]])
+ EnumerableLimit(fetch=[1])
+ EnumerableSort(sort0=[$0], dir0=[DESC])
+ EnumerableCalc(expr#0..2=[{inputs}], expr#3=[true], expr#4=[$cor0],
expr#5=[$t4.DEPTNO], expr#6=[=($t5, $t0)], expr#7=[10],
expr#8=[CAST($t0):INTEGER NOT NULL], expr#9=[=($t7, $t8)], expr#10=[AND($t6,
$t9)], cs=[$t3], $condition=[$t10])
+ EnumerableTableScan(table=[[scott, DEPT]])
!plan
# Test filter literal NOT IN nullable correlated
@@ -2057,13 +2062,14 @@ select sal from "scott".emp e
(11 rows)
!ok
-EnumerableCalc(expr#0..4=[{inputs}], expr#5=[NOT($t3)], expr#6=[IS NOT
NULL($t3)], expr#7=[OR($t5, $t6)], expr#8=[IS NOT TRUE($t7)], SAL=[$t1],
$condition=[$t8])
- EnumerableMergeJoin(condition=[=($2, $4)], joinType=[left])
- EnumerableSort(sort0=[$2], dir0=[ASC])
- EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0], SAL=[$t5],
DEPTNO=[$t7])
- EnumerableTableScan(table=[[scott, EMP]])
- EnumerableCalc(expr#0..2=[{inputs}], expr#3=[true],
expr#4=[CAST($t0):INTEGER], expr#5=[10], expr#6=[=($t4, $t5)], cs=[$t3],
DEPTNO=[$t0], $condition=[$t6])
- EnumerableTableScan(table=[[scott, DEPT]])
+EnumerableCalc(expr#0..2=[{inputs}], expr#3=[NOT($t2)], expr#4=[IS NOT
NULL($t2)], expr#5=[OR($t3, $t4)], expr#6=[IS NOT TRUE($t5)], SAL=[$t0],
$condition=[$t6])
+ EnumerableCorrelate(correlation=[$cor0], joinType=[left],
requiredColumns=[{1}])
+ EnumerableCalc(expr#0..7=[{inputs}], SAL=[$t5], DEPTNO=[$t7])
+ EnumerableTableScan(table=[[scott, EMP]])
+ EnumerableLimit(fetch=[1])
+ EnumerableSort(sort0=[$0], dir0=[DESC])
+ EnumerableCalc(expr#0..2=[{inputs}], expr#3=[IS NOT NULL($t0)],
expr#4=[$cor0], expr#5=[$t4.DEPTNO], expr#6=[=($t5, $t0)],
expr#7=[CAST($t0):INTEGER], expr#8=[10], expr#9=[=($t7, $t8)],
expr#10=[AND($t6, $t9)], cs=[$t3], $condition=[$t10])
+ EnumerableTableScan(table=[[scott, DEPT]])
!plan
# Test filter null IN required is unknown correlated
@@ -2090,8 +2096,15 @@ select sal from "scott".emp e
(14 rows)
!ok
-EnumerableCalc(expr#0..7=[{inputs}], SAL=[$t5])
- EnumerableTableScan(table=[[scott, EMP]])
+EnumerableCalc(expr#0..2=[{inputs}], SAL=[$t0])
+ EnumerableCorrelate(correlation=[$cor0], joinType=[left],
requiredColumns=[{1}])
+ EnumerableCalc(expr#0..7=[{inputs}], SAL=[$t5], DEPTNO=[$t7])
+ EnumerableTableScan(table=[[scott, EMP]])
+ EnumerableLimit(fetch=[1])
+ EnumerableSort(sort0=[$0], dir0=[DESC])
+ EnumerableAggregate(group=[{0}])
+ EnumerableCalc(expr#0..2=[{inputs}], expr#3=[true], expr#4=[$cor0],
expr#5=[$t4.DEPTNO], expr#6=[=($t5, $t0)], cs=[$t3], $condition=[$t6])
+ EnumerableTableScan(table=[[scott, DEPT]])
!plan
