This is an automated email from the ASF dual-hosted git repository.
libenchao 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 8a2c680b96 [CALCITE-5680] Wrong plan for multiple IN sub-queries with
only literal operands
8a2c680b96 is described below
commit 8a2c680b963261b30323f9babbcc60be1dca305d
Author: Runkang He <[email protected]>
AuthorDate: Sat Apr 29 10:42:38 2023 +0800
[CALCITE-5680] Wrong plan for multiple IN sub-queries with only literal
operands
Close apache/calcite#3182
---
.../calcite/rel/rules/SubQueryRemoveRule.java | 6 +-
.../org/apache/calcite/test/RelOptRulesTest.java | 38 +++++++++
.../org/apache/calcite/test/RelOptRulesTest.xml | 96 ++++++++++++++++++++++
core/src/test/resources/sql/sub-query.iq | 29 +++++++
4 files changed, 166 insertions(+), 3 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 6eecf9c1ae..2dcd4932f6 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
@@ -758,11 +758,11 @@ public class SubQueryRemoveRule
// for the case of non-correlated sub-queries
if (variablesSet.isEmpty()) {
operands.add(
- builder.isNull(builder.field("c")),
+ builder.isNull(builder.field(dtAlias, "c")),
falseLiteral);
}
operands.add(
- builder.equals(builder.field("cs"), falseLiteral),
+ builder.equals(builder.field(dtAlias, "cs"), falseLiteral),
b);
} else {
operands.add(
@@ -779,7 +779,7 @@ public class SubQueryRemoveRule
}
if (allLiterals) {
- operands.add(builder.isNotNull(builder.field("cs")),
+ operands.add(builder.isNotNull(builder.field(dtAlias, "cs")),
trueLiteral);
} else {
operands.add(builder.isNotNull(last(builder.fields())),
diff --git a/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
b/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
index 841c32a700..ac4ddcdb17 100644
--- a/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
+++ b/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
@@ -6448,6 +6448,44 @@ class RelOptRulesTest extends RelOptTestBase {
.check();
}
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-5680";>[CALCITE-5680]
+ * Wrong field reference lookup due to same intermediate table alias
+ * of multiple sub-queries with only literal operands in subquery remove
phase</a>. */
+ @Test void testExpandFilterConstantInCorrelatedWithTwoSubQueries() {
+ final String sql = "select empno from sales.empnullables as e\n"
+ + "where 1 in (\n"
+ + " select deptno from sales.deptnullables where e.ename = name and
deptno > 10)\n"
+ + "or 2 in (\n"
+ + " select deptno from sales.deptnullables where e.ename = name and
deptno < 20)";
+ // We disable expression simplification and enable trim to make plan
+ // more straightforward and easier to identify whether the plan is correct
+ sql(sql)
+ .withSubQueryRules()
+ .withRelBuilderSimplify(false)
+ .withTrim(true)
+ .check();
+ }
+
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-5680";>[CALCITE-5680]
+ * Wrong field reference lookup due to same intermediate table alias
+ * of multiple sub-queries with only literal operands in subquery remove
phase</a>. */
+ @Test void testExpandFilterConstantInWithTwoSubQueries() {
+ final String sql = "select empno from sales.empnullables\n"
+ + "where 1 in (\n"
+ + " select deptno from sales.deptnullables where name = 'dept1')\n"
+ + "or 2 in (\n"
+ + " select deptno from sales.deptnullables where name = 'dept2')";
+ // We disable expression simplification and enable trim to make plan
+ // more straightforward and easier to identify whether the plan is correct
+ sql(sql)
+ .withSubQueryRules()
+ .withRelBuilderSimplify(false)
+ .withTrim(true)
+ .check();
+ }
+
/** An EXISTS filter that can be converted into true/false. */
@Test void testExpandFilterExists() {
final String sql = "select empno\n"
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 72567797e3..a5c83a3ccc 100644
--- a/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
+++ b/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
@@ -3202,6 +3202,102 @@ LogicalProject(DEPTNO=[$0], NAME=[$1])
<![CDATA[
LogicalProject(DEPTNO=[$0], NAME=[$1])
LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testExpandFilterConstantInCorrelatedWithTwoSubQueries">
+ <Resource name="sql">
+ <![CDATA[select empno from sales.empnullables as e
+where 1 in (
+ select deptno from sales.deptnullables where e.ename = name and deptno > 10)
+or 2 in (
+ select deptno from sales.deptnullables where e.ename = name and deptno <
20)]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalProject(EMPNO=[$0])
+ LogicalFilter(condition=[OR(IN(1, {
+LogicalProject(DEPTNO=[$0])
+ LogicalFilter(condition=[AND(=($cor0.ENAME, CAST($1):VARCHAR(20)), >($0,
10))])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPTNULLABLES]])
+}), IN(2, {
+LogicalProject(DEPTNO=[$0])
+ LogicalFilter(condition=[AND(=($cor0.ENAME, CAST($1):VARCHAR(20)), <($0,
20))])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPTNULLABLES]])
+}))], variablesSet=[[$cor0]])
+ LogicalProject(EMPNO=[$0], ENAME=[$1])
+ LogicalTableScan(table=[[CATALOG, SALES, EMPNULLABLES]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+LogicalProject(EMPNO=[$0])
+ LogicalProject(EMPNO=[$0], ENAME=[$1])
+ LogicalFilter(condition=[OR(CASE(=($2, false), null:BOOLEAN, IS NOT
NULL($2), true, false), CASE(=($3, false), null:BOOLEAN, IS NOT NULL($3), true,
false))])
+ LogicalCorrelate(correlation=[$cor0], joinType=[left],
requiredColumns=[{1}])
+ LogicalCorrelate(correlation=[$cor0], joinType=[left],
requiredColumns=[{1}])
+ LogicalProject(EMPNO=[$0], ENAME=[$1])
+ LogicalTableScan(table=[[CATALOG, SALES, EMPNULLABLES]])
+ 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]])
+ 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>
+ <TestCase name="testExpandFilterConstantInWithTwoSubQueries">
+ <Resource name="sql">
+ <![CDATA[select empno from sales.empnullables
+where 1 in (
+ select deptno from sales.deptnullables where name = 'dept1')
+or 2 in (
+ select deptno from sales.deptnullables where name = 'dept2')]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalFilter(condition=[OR(IN(1, {
+LogicalProject(DEPTNO=[$0])
+ LogicalFilter(condition=[=($1, 'dept1')])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPTNULLABLES]])
+}), IN(2, {
+LogicalProject(DEPTNO=[$0])
+ LogicalFilter(condition=[=($1, 'dept2')])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPTNULLABLES]])
+}))])
+ LogicalProject(EMPNO=[$0])
+ LogicalTableScan(table=[[CATALOG, SALES, EMPNULLABLES]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+LogicalProject(EMPNO=[$0])
+ LogicalFilter(condition=[OR(CASE(IS NULL($2), false, =($1, false),
null:BOOLEAN, IS NOT NULL($1), true, false), CASE(IS NULL($4), false, =($3,
false), null:BOOLEAN, IS NOT NULL($3), true, false))])
+ LogicalJoin(condition=[true], joinType=[left])
+ LogicalJoin(condition=[true], joinType=[left])
+ LogicalProject(EMPNO=[$0])
+ LogicalTableScan(table=[[CATALOG, SALES, EMPNULLABLES]])
+ LogicalSort(sort0=[$0], dir0=[DESC], fetch=[1])
+ LogicalAggregate(group=[{0}], c=[COUNT()])
+ LogicalProject(cs=[IS NOT NULL($0)])
+ LogicalFilter(condition=[OR(=(1, $0), IS NULL($0))])
+ LogicalProject(DEPTNO=[$0])
+ LogicalFilter(condition=[=($1, 'dept1')])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPTNULLABLES]])
+ LogicalSort(sort0=[$0], dir0=[DESC], fetch=[1])
+ LogicalAggregate(group=[{0}], c=[COUNT()])
+ LogicalProject(cs=[IS NOT NULL($0)])
+ LogicalFilter(condition=[OR(=(2, $0), IS NULL($0))])
+ LogicalProject(DEPTNO=[$0])
+ LogicalFilter(condition=[=($1, 'dept2')])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPTNULLABLES]])
]]>
</Resource>
</TestCase>
diff --git a/core/src/test/resources/sql/sub-query.iq
b/core/src/test/resources/sql/sub-query.iq
index 277c8ef0b5..1cd28283c9 100644
--- a/core/src/test/resources/sql/sub-query.iq
+++ b/core/src/test/resources/sql/sub-query.iq
@@ -3544,4 +3544,33 @@ FROM UNNEST(ARRAY['a', 'b']) AS a(attidentity);
!ok
+# Test case for [CALCITE-5680] Wrong plan for multiple IN sub-queries with
only literal operands
+# Note that the first sub-query produces empty result using a fake dept name
+select empno from "scott".emp as e
+where 10 in (
+ select deptno from "scott".dept where dname = 'FAKE-DEPT')
+or 20 in (
+ select deptno from "scott".dept where dname = 'RESEARCH');
++-------+
+| EMPNO |
++-------+
+| 7369 |
+| 7499 |
+| 7521 |
+| 7566 |
+| 7654 |
+| 7698 |
+| 7782 |
+| 7788 |
+| 7839 |
+| 7844 |
+| 7876 |
+| 7900 |
+| 7902 |
+| 7934 |
++-------+
+(14 rows)
+
+!ok
+
# End sub-query.iq
