You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@calcite.apache.org by li...@apache.org on 2023/04/21 02:08:01 UTC
[calcite] 01/01: [CALCITE-5655] Wrong plan for multiple IN/SOME sub-queries with OR predicate
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
commit 74524cf1df5112ec17aaa219d97121e9fa236169
Author: 何润康 <he...@bytedance.com>
AuthorDate: Mon Apr 17 11:59:00 2023 +0000
[CALCITE-5655] Wrong plan for multiple IN/SOME sub-queries with OR predicate
Below tests are not affacted by this bug, we added them to improve the test coverage:
RelOptRulesTest#testExpandProjectInWithTwoCorrelatedSubQueries
RelOptRulesTest#testExpandProjectInWithTwoSubQueries
Close apache/calcite#3159
---
.../calcite/rel/rules/SubQueryRemoveRule.java | 98 +++---
.../apache/calcite/sql/test/SqlAdvisorTest.java | 1 +
.../org/apache/calcite/test/RelOptRulesTest.java | 94 ++++++
.../org/apache/calcite/test/RelOptRulesTest.xml | 332 +++++++++++++++++++++
.../test/catalog/MockCatalogReaderSimple.java | 10 +
5 files changed, 494 insertions(+), 41 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 c73da2625e..6eecf9c1ae 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
@@ -88,7 +88,7 @@ public class SubQueryRemoveRule
protected RexNode apply(RexSubQuery e, Set<CorrelationId> variablesSet,
RelOptUtil.Logic logic,
- RelBuilder builder, int inputCount, int offset) {
+ RelBuilder builder, int inputCount, int offset, int subQueryIndex) {
switch (e.getKind()) {
case SCALAR_QUERY:
return rewriteScalarQuery(e, variablesSet, builder, inputCount, offset);
@@ -98,9 +98,9 @@ public class SubQueryRemoveRule
return rewriteCollection(e, variablesSet, builder,
inputCount, offset);
case SOME:
- return rewriteSome(e, variablesSet, builder);
+ return rewriteSome(e, variablesSet, builder, subQueryIndex);
case IN:
- return rewriteIn(e, variablesSet, logic, builder, offset);
+ return rewriteIn(e, variablesSet, logic, builder, offset, subQueryIndex);
case EXISTS:
return rewriteExists(e, variablesSet, logic, builder);
case UNIQUE:
@@ -161,13 +161,14 @@ public class SubQueryRemoveRule
/**
* Rewrites a SOME sub-query into a {@link Join}.
*
- * @param e SOME sub-query to rewrite
- * @param builder Builder
+ * @param e SOME sub-query to rewrite
+ * @param builder Builder
+ * @param subQueryIndex sub-query index in multiple sub-queries
*
* @return Expression that may be used to replace the RexSubQuery
*/
private static RexNode rewriteSome(RexSubQuery e, Set<CorrelationId> variablesSet,
- RelBuilder builder) {
+ RelBuilder builder, int subQueryIndex) {
// Most general case, where the left and right keys might have nulls, and
// caller requires 3-valued logic return.
//
@@ -213,6 +214,11 @@ public class SubQueryRemoveRule
? SqlStdOperatorTable.MIN
: SqlStdOperatorTable.MAX;
+ String qAlias = "q";
+ if (subQueryIndex != 0) {
+ qAlias = "q" + subQueryIndex;
+ }
+
if (variablesSet.isEmpty()) {
switch (op.comparisonKind) {
case GREATER_THAN_OR_EQUAL:
@@ -241,21 +247,21 @@ public class SubQueryRemoveRule
builder.aggregateCall(minMax, builder.field(0)).as("m"),
builder.count(false, "c"),
builder.count(false, "d", builder.field(0)))
- .as("q")
+ .as(qAlias)
.join(JoinRelType.INNER);
caseRexNode =
builder.call(SqlStdOperatorTable.CASE,
- builder.equals(builder.field("q", "c"), builder.literal(0)),
+ builder.equals(builder.field(qAlias, "c"), builder.literal(0)),
literalFalse,
builder.call(SqlStdOperatorTable.IS_TRUE,
builder.call(RexUtil.op(op.comparisonKind),
- e.operands.get(0), builder.field("q", "m"))),
+ e.operands.get(0), builder.field(qAlias, "m"))),
literalTrue,
- builder.greaterThan(builder.field("q", "c"),
- builder.field("q", "d")),
+ builder.greaterThan(builder.field(qAlias, "c"),
+ builder.field(qAlias, "d")),
literalUnknown,
builder.call(RexUtil.op(op.comparisonKind),
- e.operands.get(0), builder.field("q", "m")));
+ e.operands.get(0), builder.field(qAlias, "m")));
break;
case NOT_EQUALS:
@@ -284,7 +290,7 @@ public class SubQueryRemoveRule
builder.count(false, "c"),
builder.count(false, "d", builder.field(0)),
builder.max(builder.field(0)).as("m"))
- .as("q")
+ .as(qAlias)
.join(JoinRelType.INNER);
caseRexNode =
builder.call(SqlStdOperatorTable.CASE,
@@ -297,10 +303,10 @@ public class SubQueryRemoveRule
builder.lessThanOrEqual(builder.field("d"),
builder.literal(1))),
builder.or(
- builder.notEquals(e.operands.get(0), builder.field("q", "m")),
+ builder.notEquals(e.operands.get(0), builder.field(qAlias, "m")),
literalUnknown),
builder.equals(builder.field("d"), builder.literal(1)),
- builder.notEquals(e.operands.get(0), builder.field("q", "m")),
+ builder.notEquals(e.operands.get(0), builder.field(qAlias, "m")),
literalTrue);
break;
@@ -344,23 +350,23 @@ public class SubQueryRemoveRule
parentQueryFields.addAll(builder.fields());
parentQueryFields.add(builder.alias(literalTrue, indicator));
- builder.project(parentQueryFields).as("q");
+ builder.project(parentQueryFields).as(qAlias);
builder.join(JoinRelType.LEFT, literalTrue, variablesSet);
caseRexNode =
builder.call(SqlStdOperatorTable.CASE,
- builder.isNull(builder.field("q", indicator)),
+ builder.isNull(builder.field(qAlias, indicator)),
literalFalse,
- builder.equals(builder.field("q", "c"), builder.literal(0)),
+ builder.equals(builder.field(qAlias, "c"), builder.literal(0)),
literalFalse,
builder.call(SqlStdOperatorTable.IS_TRUE,
builder.call(RexUtil.op(op.comparisonKind),
- e.operands.get(0), builder.field("q", "m"))),
+ e.operands.get(0), builder.field(qAlias, "m"))),
literalTrue,
- builder.greaterThan(builder.field("q", "c"),
- builder.field("q", "d")),
+ builder.greaterThan(builder.field(qAlias, "c"),
+ builder.field(qAlias, "d")),
literalUnknown,
builder.call(RexUtil.op(op.comparisonKind),
- e.operands.get(0), builder.field("q", "m")));
+ e.operands.get(0), builder.field(qAlias, "m")));
break;
case NOT_EQUALS:
@@ -397,11 +403,11 @@ public class SubQueryRemoveRule
parentQueryFields.addAll(builder.fields());
parentQueryFields.add(builder.alias(literalTrue, indicator));
- builder.project(parentQueryFields).as("q"); // TODO use projectPlus
+ builder.project(parentQueryFields).as(qAlias); // TODO use projectPlus
builder.join(JoinRelType.LEFT, literalTrue, variablesSet);
caseRexNode =
builder.call(SqlStdOperatorTable.CASE,
- builder.isNull(builder.field("q", indicator)),
+ builder.isNull(builder.field(qAlias, indicator)),
literalFalse,
builder.equals(builder.field("c"), builder.literal(0)),
literalFalse,
@@ -412,10 +418,10 @@ public class SubQueryRemoveRule
builder.lessThanOrEqual(builder.field("d"),
builder.literal(1))),
builder.or(
- builder.notEquals(e.operands.get(0), builder.field("q", "m")),
+ builder.notEquals(e.operands.get(0), builder.field(qAlias, "m")),
literalUnknown),
builder.equals(builder.field("d"), builder.literal(1)),
- builder.notEquals(e.operands.get(0), builder.field("q", "m")),
+ builder.notEquals(e.operands.get(0), builder.field(qAlias, "m")),
literalTrue);
break;
@@ -536,17 +542,18 @@ public class SubQueryRemoveRule
/**
* Rewrites an IN RexSubQuery into a {@link Join}.
*
- * @param e IN sub-query to rewrite
- * @param variablesSet A set of variables used by a relational
- * expression of the specified RexSubQuery
- * @param logic Logic for evaluating
- * @param builder Builder
- * @param offset Offset to shift {@link RexInputRef}
+ * @param e IN sub-query to rewrite
+ * @param variablesSet A set of variables used by a relational
+ * expression of the specified RexSubQuery
+ * @param logic Logic for evaluating
+ * @param builder Builder
+ * @param offset Offset to shift {@link RexInputRef}
+ * @param subQueryIndex sub-query index in multiple sub-queries
*
* @return Expression that may be used to replace the RexSubQuery
*/
private static RexNode rewriteIn(RexSubQuery e, Set<CorrelationId> variablesSet,
- RelOptUtil.Logic logic, RelBuilder builder, int offset) {
+ RelOptUtil.Logic logic, RelBuilder builder, int offset, int subQueryIndex) {
// Most general case, where the left and right keys might have nulls, and
// caller requires 3-valued logic return.
//
@@ -628,6 +635,11 @@ public class SubQueryRemoveRule
// order by cs desc limit 1) as dt
//
+ String ctAlias = "ct";
+ if (subQueryIndex != 0) {
+ ctAlias = "ct" + subQueryIndex;
+ }
+
boolean allLiterals = RexUtil.allLiterals(e.getOperands());
final List<RexNode> expressionOperands = new ArrayList<>(e.getOperands());
@@ -698,7 +710,7 @@ public class SubQueryRemoveRule
builder.aggregate(builder.groupKey(),
builder.count(false, "c"),
builder.count(builder.fields()).as("ck"));
- builder.as("ct");
+ builder.as(ctAlias);
if (!variablesSet.isEmpty()) {
builder.join(JoinRelType.LEFT, trueLiteral, variablesSet);
} else {
@@ -714,7 +726,11 @@ public class SubQueryRemoveRule
}
}
- builder.as("dt");
+ String dtAlias = "dt";
+ if (subQueryIndex != 0) {
+ dtAlias = "dt" + subQueryIndex;
+ }
+ builder.as(dtAlias);
int refOffset = offset;
final List<RexNode> conditions =
Pair.zip(expressionOperands, builder.fields()).stream()
@@ -750,7 +766,7 @@ public class SubQueryRemoveRule
b);
} else {
operands.add(
- builder.equals(builder.field("ct", "c"), builder.literal(0)),
+ builder.equals(builder.field(ctAlias, "c"), builder.literal(0)),
falseLiteral);
}
break;
@@ -775,8 +791,8 @@ public class SubQueryRemoveRule
case TRUE_FALSE_UNKNOWN:
case UNKNOWN_AS_TRUE:
operands.add(
- builder.lessThan(builder.field("ct", "ck"),
- builder.field("ct", "c")),
+ builder.lessThan(builder.field(ctAlias, "ck"),
+ builder.field(ctAlias, "c")),
b);
break;
default:
@@ -825,7 +841,7 @@ public class SubQueryRemoveRule
final Set<CorrelationId> variablesSet =
RelOptUtil.getVariablesUsed(e.rel);
final RexNode target =
- rule.apply(e, variablesSet, logic, builder, 1, fieldCount);
+ rule.apply(e, variablesSet, logic, builder, 1, fieldCount, 0);
final RexShuttle shuttle = new ReplaceSubQueryShuttle(e, target);
builder.project(shuttle.apply(project.getProjects()),
project.getRowType().getFieldNames());
@@ -852,7 +868,7 @@ public class SubQueryRemoveRule
RelOptUtil.getVariablesUsed(e.rel);
final RexNode target =
rule.apply(e, variablesSet, logic,
- builder, 1, builder.peek().getRowType().getFieldCount());
+ builder, 1, builder.peek().getRowType().getFieldCount(), count);
final RexShuttle shuttle = new ReplaceSubQueryShuttle(e, target);
c = c.accept(shuttle);
}
@@ -876,7 +892,7 @@ public class SubQueryRemoveRule
final Set<CorrelationId> variablesSet =
RelOptUtil.getVariablesUsed(e.rel);
final RexNode target =
- rule.apply(e, variablesSet, logic, builder, 2, fieldCount);
+ rule.apply(e, variablesSet, logic, builder, 2, fieldCount, 0);
final RexShuttle shuttle = new ReplaceSubQueryShuttle(e, target);
builder.join(join.getJoinType(), shuttle.apply(join.getCondition()));
builder.project(fields(builder, join.getRowType().getFieldCount()));
diff --git a/core/src/test/java/org/apache/calcite/sql/test/SqlAdvisorTest.java b/core/src/test/java/org/apache/calcite/sql/test/SqlAdvisorTest.java
index b0cafeb246..15a1e6c349 100644
--- a/core/src/test/java/org/apache/calcite/sql/test/SqlAdvisorTest.java
+++ b/core/src/test/java/org/apache/calcite/sql/test/SqlAdvisorTest.java
@@ -90,6 +90,7 @@ class SqlAdvisorTest extends SqlValidatorTestCase {
"TABLE(CATALOG.SALES.EMPTY_PRODUCTS)",
"TABLE(CATALOG.SALES.EMP_ADDRESS)",
"TABLE(CATALOG.SALES.DEPT)",
+ "TABLE(CATALOG.SALES.DEPTNULLABLES)",
"TABLE(CATALOG.SALES.DEPT_SINGLE)",
"TABLE(CATALOG.SALES.DEPT_NESTED)",
"TABLE(CATALOG.SALES.DEPT_NESTED_EXPANDED)",
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 26866db065..ef814a91dd 100644
--- a/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
+++ b/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
@@ -6219,6 +6219,40 @@ class RelOptRulesTest extends RelOptTestBase {
sql(sql).withSubQueryRules().withLateDecorrelate(true).check();
}
+ /** Test case for
+ * <a href="https://issues.apache.org/jira/browse/CALCITE-5655">[CALCITE-5655]
+ * Wrong field reference lookup due to same intermediate table alias
+ * of multiple sub-queries in subquery remove phase</a>. */
+ @Test public void testSomeWithTwoCorrelatedSubQueries() {
+ final String sql = "select empno from sales.empnullables as e\n"
+ + "where deptno > some(\n"
+ + " select deptno from sales.deptnullables where e.ename = name and deptno > 10)\n"
+ + "or deptno < some(\n"
+ + " select deptno from sales.deptnullables where e.ename = name and deptno < 20)";
+ sql(sql)
+ .withSubQueryRules()
+ .withRelBuilderSimplify(false)
+ .withTrim(true)
+ .check();
+ }
+
+ /** Test case for
+ * <a href="https://issues.apache.org/jira/browse/CALCITE-5655">[CALCITE-5655]
+ * Wrong field reference lookup due to same intermediate table alias
+ * of multiple sub-queries in subquery remove phase</a>. */
+ @Test public void testSomeWithTwoSubQueries() {
+ final String sql = "select empno from sales.empnullables\n"
+ + "where deptno > some(\n"
+ + " select deptno from sales.deptnullables where name = 'dept1')\n"
+ + "or deptno < some(\n"
+ + " select deptno from sales.deptnullables where name = 'dept2')";
+ sql(sql)
+ .withSubQueryRules()
+ .withRelBuilderSimplify(false)
+ .withTrim(true)
+ .check();
+ }
+
/** Test case for
* <a href="https://issues.apache.org/jira/browse/CALCITE-1546">[CALCITE-1546]
* Sub-queries connected by OR</a>. */
@@ -6252,6 +6286,32 @@ class RelOptRulesTest extends RelOptTestBase {
.check();
}
+ @Test void testExpandProjectInWithTwoCorrelatedSubQueries() {
+ final String sql = "select empno, deptno in (\n"
+ + " select deptno from sales.deptnullables where e.ename = name and deptno > 10)\n"
+ + "or deptno in (\n"
+ + " select deptno from sales.deptnullables where e.ename = name and deptno < 20)\n"
+ + "from sales.empnullables as e";
+ sql(sql)
+ .withSubQueryRules()
+ .withRelBuilderSimplify(false)
+ .withTrim(true)
+ .check();
+ }
+
+ @Test void testExpandProjectInWithTwoSubQueries() {
+ final String sql = "select empno, deptno in (\n"
+ + " select deptno from sales.deptnullables where name = 'dept1')\n"
+ + "or deptno in (\n"
+ + " select deptno from sales.deptnullables where name = 'dept2')\n"
+ + "from sales.empnullables";
+ sql(sql)
+ .withSubQueryRules()
+ .withRelBuilderSimplify(false)
+ .withTrim(true)
+ .check();
+ }
+
@Test void testExpandProjectInComposite() {
final String sql = "select empno, (empno, deptno) in (\n"
+ " select empno, deptno from sales.emp where empno < 20) as d\n"
@@ -6298,6 +6358,40 @@ class RelOptRulesTest extends RelOptTestBase {
sql(sql).withSubQueryRules().check();
}
+ /** Test case for
+ * <a href="https://issues.apache.org/jira/browse/CALCITE-5655">[CALCITE-5655]
+ * Wrong field reference lookup due to same intermediate table alias
+ * of multiple sub-queries in subquery remove phase</a>. */
+ @Test void testExpandFilterInCorrelatedWithTwoSubQueries() {
+ final String sql = "select empno from sales.empnullables as e\n"
+ + "where deptno in (\n"
+ + " select deptno from sales.deptnullables where e.ename = name and deptno > 10)\n"
+ + "or deptno in (\n"
+ + " select deptno from sales.deptnullables where e.ename = name and deptno < 20)";
+ sql(sql)
+ .withSubQueryRules()
+ .withRelBuilderSimplify(false)
+ .withTrim(true)
+ .check();
+ }
+
+ /** Test case for
+ * <a href="https://issues.apache.org/jira/browse/CALCITE-5655">[CALCITE-5655]
+ * Wrong field reference lookup due to same intermediate table alias
+ * of multiple sub-queries in subquery remove phase</a>. */
+ @Test void testExpandFilterInWithTwoSubQueries() {
+ final String sql = "select empno from sales.empnullables\n"
+ + "where deptno in (\n"
+ + " select deptno from sales.deptnullables where name = 'dept1')\n"
+ + "or deptno in (\n"
+ + " select deptno from sales.deptnullables where name = 'dept2')";
+ sql(sql)
+ .withSubQueryRules()
+ .withRelBuilderSimplify(false)
+ .withTrim(true)
+ .check();
+ }
+
/** An IN filter that requires full 3-value logic (true, false, unknown). */
@Test void testExpandFilterIn3Value() {
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 5ae464f906..162e3f35c9 100644
--- a/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
+++ b/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
@@ -3446,6 +3446,112 @@ LogicalProject(EMPNO=[$0])
LogicalProject(EMPNO=[$0], DEPTNO=[$7], i=[true])
LogicalFilter(condition=[<($0, 20)])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testExpandFilterInCorrelatedWithTwoSubQueries">
+ <Resource name="sql">
+ <![CDATA[select empno from sales.empnullables as e
+where deptno in (
+ select deptno from sales.deptnullables where e.ename = name and deptno > 10)
+or deptno 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($2, {
+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], DEPTNO=[$7])
+ LogicalTableScan(table=[[CATALOG, SALES, EMPNULLABLES]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+LogicalProject(EMPNO=[$0])
+ LogicalProject(EMPNO=[$0], ENAME=[$1], DEPTNO=[$2])
+ LogicalFilter(condition=[OR(CASE(=($3, 0), false, IS NULL($2), null:BOOLEAN, IS NOT NULL($6), true, <($4, $3), null:BOOLEAN, false), CASE(=($7, 0), false, IS NULL($2), null:BOOLEAN, IS NOT NULL($10), true, <($8, $7), null:BOOLEAN, false))])
+ LogicalCorrelate(correlation=[$cor0], joinType=[left], requiredColumns=[{1, 2}])
+ LogicalCorrelate(correlation=[$cor0], joinType=[left], requiredColumns=[{1}])
+ LogicalCorrelate(correlation=[$cor0], joinType=[left], requiredColumns=[{1, 2}])
+ LogicalCorrelate(correlation=[$cor0], joinType=[left], requiredColumns=[{1}])
+ LogicalProject(EMPNO=[$0], ENAME=[$1], DEPTNO=[$7])
+ LogicalTableScan(table=[[CATALOG, SALES, EMPNULLABLES]])
+ LogicalAggregate(group=[{}], c=[COUNT()], ck=[COUNT($0)])
+ LogicalProject(DEPTNO=[$0])
+ LogicalFilter(condition=[AND(=($cor0.ENAME, CAST($1):VARCHAR(20)), >($0, 10))])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPTNULLABLES]])
+ LogicalFilter(condition=[=($cor0.DEPTNO, $0)])
+ LogicalProject(DEPTNO=[$0], i=[true])
+ LogicalFilter(condition=[AND(=($cor0.ENAME, CAST($1):VARCHAR(20)), >($0, 10))])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPTNULLABLES]])
+ LogicalAggregate(group=[{}], c=[COUNT()], ck=[COUNT($0)])
+ LogicalProject(DEPTNO=[$0])
+ LogicalFilter(condition=[AND(=($cor0.ENAME, CAST($1):VARCHAR(20)), <($0, 20))])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPTNULLABLES]])
+ LogicalFilter(condition=[=($cor0.DEPTNO, $0)])
+ LogicalProject(DEPTNO=[$0], i=[true])
+ LogicalFilter(condition=[AND(=($cor0.ENAME, CAST($1):VARCHAR(20)), <($0, 20))])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPTNULLABLES]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testExpandFilterInWithTwoSubQueries">
+ <Resource name="sql">
+ <![CDATA[select empno from sales.empnullables
+where deptno in (
+ select deptno from sales.deptnullables where name = 'dept1')
+or deptno in (
+ select deptno from sales.deptnullables where name = 'dept2')]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalProject(EMPNO=[$0])
+ LogicalFilter(condition=[OR(IN($1, {
+LogicalProject(DEPTNO=[$0])
+ LogicalFilter(condition=[=($1, 'dept1')])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPTNULLABLES]])
+}), IN($1, {
+LogicalProject(DEPTNO=[$0])
+ LogicalFilter(condition=[=($1, 'dept2')])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPTNULLABLES]])
+}))])
+ LogicalProject(EMPNO=[$0], DEPTNO=[$7])
+ LogicalTableScan(table=[[CATALOG, SALES, EMPNULLABLES]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+LogicalProject(EMPNO=[$0])
+ LogicalProject(EMPNO=[$0], DEPTNO=[$1])
+ LogicalFilter(condition=[OR(CASE(=($2, 0), false, IS NULL($1), null:BOOLEAN, IS NOT NULL($5), true, <($3, $2), null:BOOLEAN, false), CASE(=($6, 0), false, IS NULL($1), null:BOOLEAN, IS NOT NULL($9), true, <($7, $6), null:BOOLEAN, false))])
+ LogicalJoin(condition=[=($1, $8)], joinType=[left])
+ LogicalJoin(condition=[true], joinType=[inner])
+ LogicalJoin(condition=[=($1, $4)], joinType=[left])
+ LogicalJoin(condition=[true], joinType=[inner])
+ LogicalProject(EMPNO=[$0], DEPTNO=[$7])
+ LogicalTableScan(table=[[CATALOG, SALES, EMPNULLABLES]])
+ LogicalAggregate(group=[{}], c=[COUNT()], ck=[COUNT($0)])
+ LogicalProject(DEPTNO=[$0])
+ LogicalFilter(condition=[=($1, 'dept1')])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPTNULLABLES]])
+ LogicalProject(DEPTNO=[$0], i=[true])
+ LogicalFilter(condition=[=($1, 'dept1')])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPTNULLABLES]])
+ LogicalAggregate(group=[{}], c=[COUNT()], ck=[COUNT($0)])
+ LogicalProject(DEPTNO=[$0])
+ LogicalFilter(condition=[=($1, 'dept2')])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPTNULLABLES]])
+ LogicalProject(DEPTNO=[$0], i=[true])
+ LogicalFilter(condition=[=($1, 'dept2')])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPTNULLABLES]])
]]>
</Resource>
</TestCase>
@@ -3752,6 +3858,102 @@ LogicalProject(EMPNO=[$0], D=[CASE(=($9, 0), false, IS NULL(CASE(true, CAST($7):
LogicalFilter(condition=[<($0, 20)])
LogicalProject(EMPNO=[$0], DEPTNO=[CASE(true, CAST($7):INTEGER, null:INTEGER)])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testExpandProjectInWithTwoCorrelatedSubQueries">
+ <Resource name="sql">
+ <![CDATA[select empno, deptno in (
+ select deptno from sales.deptnullables where e.ename = name and deptno > 10)
+or deptno in (
+ select deptno from sales.deptnullables where e.ename = name and deptno < 20)
+from sales.empnullables as e]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalProject(EMPNO=[$0], EXPR$1=[OR(IN($7, {
+LogicalProject(DEPTNO=[$0])
+ LogicalFilter(condition=[AND(=($cor0.ENAME, CAST($1):VARCHAR(20)), >($0, 10))])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPTNULLABLES]])
+}), IN($7, {
+LogicalProject(DEPTNO=[$0])
+ LogicalFilter(condition=[AND(=($cor0.ENAME, CAST($1):VARCHAR(20)), <($0, 20))])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPTNULLABLES]])
+}))])
+ LogicalTableScan(table=[[CATALOG, SALES, EMPNULLABLES]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+LogicalProject(EMPNO=[$0], EXPR$1=[OR(CASE(=($9, 0), false, IS NULL($7), null:BOOLEAN, IS NOT NULL($12), true, <($10, $9), null:BOOLEAN, false), CASE(=($13, 0), false, IS NULL($7), null:BOOLEAN, IS NOT NULL($16), true, <($14, $13), null:BOOLEAN, false))])
+ LogicalCorrelate(correlation=[$cor0], joinType=[left], requiredColumns=[{1, 7}])
+ LogicalCorrelate(correlation=[$cor0], joinType=[left], requiredColumns=[{1}])
+ LogicalCorrelate(correlation=[$cor0], joinType=[left], requiredColumns=[{1, 7}])
+ LogicalCorrelate(correlation=[$cor0], joinType=[left], requiredColumns=[{1}])
+ LogicalTableScan(table=[[CATALOG, SALES, EMPNULLABLES]])
+ LogicalAggregate(group=[{}], c=[COUNT()], ck=[COUNT($0)])
+ LogicalProject(DEPTNO=[$0])
+ LogicalFilter(condition=[AND(=($cor0.ENAME, CAST($1):VARCHAR(20)), >($0, 10))])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPTNULLABLES]])
+ LogicalFilter(condition=[=($cor0.DEPTNO, $0)])
+ LogicalProject(DEPTNO=[$0], i=[true])
+ LogicalFilter(condition=[AND(=($cor0.ENAME, CAST($1):VARCHAR(20)), >($0, 10))])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPTNULLABLES]])
+ LogicalAggregate(group=[{}], c=[COUNT()], ck=[COUNT($0)])
+ LogicalProject(DEPTNO=[$0])
+ LogicalFilter(condition=[AND(=($cor0.ENAME, CAST($1):VARCHAR(20)), <($0, 20))])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPTNULLABLES]])
+ LogicalFilter(condition=[=($cor0.DEPTNO, $0)])
+ LogicalProject(DEPTNO=[$0], i=[true])
+ LogicalFilter(condition=[AND(=($cor0.ENAME, CAST($1):VARCHAR(20)), <($0, 20))])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPTNULLABLES]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testExpandProjectInWithTwoSubQueries">
+ <Resource name="sql">
+ <![CDATA[select empno, deptno in (
+ select deptno from sales.deptnullables where name = 'dept1')
+or deptno in (
+ select deptno from sales.deptnullables where name = 'dept2')
+from sales.empnullables]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalProject(EMPNO=[$0], EXPR$1=[OR(IN($7, {
+LogicalProject(DEPTNO=[$0])
+ LogicalFilter(condition=[=($1, 'dept1')])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPTNULLABLES]])
+}), IN($7, {
+LogicalProject(DEPTNO=[$0])
+ LogicalFilter(condition=[=($1, 'dept2')])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPTNULLABLES]])
+}))])
+ LogicalTableScan(table=[[CATALOG, SALES, EMPNULLABLES]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+LogicalProject(EMPNO=[$0], EXPR$1=[OR(CASE(=($9, 0), false, IS NULL($7), null:BOOLEAN, IS NOT NULL($12), true, <($10, $9), null:BOOLEAN, false), CASE(=($13, 0), false, IS NULL($7), null:BOOLEAN, IS NOT NULL($16), true, <($14, $13), null:BOOLEAN, false))])
+ LogicalJoin(condition=[=($7, $15)], joinType=[left])
+ LogicalJoin(condition=[true], joinType=[inner])
+ LogicalJoin(condition=[=($7, $11)], joinType=[left])
+ LogicalJoin(condition=[true], joinType=[inner])
+ LogicalTableScan(table=[[CATALOG, SALES, EMPNULLABLES]])
+ LogicalAggregate(group=[{}], c=[COUNT()], ck=[COUNT($0)])
+ LogicalProject(DEPTNO=[$0])
+ LogicalFilter(condition=[=($1, 'dept1')])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPTNULLABLES]])
+ LogicalProject(DEPTNO=[$0], i=[true])
+ LogicalFilter(condition=[=($1, 'dept1')])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPTNULLABLES]])
+ LogicalAggregate(group=[{}], c=[COUNT()], ck=[COUNT($0)])
+ LogicalProject(DEPTNO=[$0])
+ LogicalFilter(condition=[=($1, 'dept2')])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPTNULLABLES]])
+ LogicalProject(DEPTNO=[$0], i=[true])
+ LogicalFilter(condition=[=($1, 'dept2')])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPTNULLABLES]])
]]>
</Resource>
</TestCase>
@@ -12727,6 +12929,136 @@ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$
LogicalAggregate(group=[{}], c=[COUNT()], m=[MAX($0)])
LogicalProject(DEPTNO=[$0])
LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testSomeWithTwoCorrelatedSubQueries">
+ <Resource name="sql">
+ <![CDATA[select empno from sales.empnullables as e
+where deptno > some(
+ select deptno from sales.deptnullables where e.ename = name and deptno > 10)
+or deptno < some(
+ select deptno from sales.deptnullables where e.ename = name and deptno < 20)]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalProject(EMPNO=[$0])
+ LogicalFilter(condition=[OR(> SOME($2, {
+LogicalProject(DEPTNO=[$0])
+ LogicalFilter(condition=[AND(=($cor0.ENAME, CAST($1):VARCHAR(20)), >($0, 10))])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPTNULLABLES]])
+}), < SOME($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], DEPTNO=[$7])
+ LogicalTableScan(table=[[CATALOG, SALES, EMPNULLABLES]])
+]]>
+ </Resource>
+ <Resource name="planMid">
+ <![CDATA[
+LogicalProject(EMPNO=[$0])
+ LogicalProject(EMPNO=[$0], ENAME=[$1], DEPTNO=[$2])
+ LogicalFilter(condition=[OR(AND(>($2, $3), IS NOT TRUE(OR(IS NULL($6), =($4, 0)))), AND(>($2, $3), IS NOT TRUE(OR(IS NULL($6), =($4, 0))), IS NOT TRUE(>($2, $3)), IS NOT TRUE(>($4, $5))), AND(<($2, $7), IS NOT TRUE(OR(IS NULL($10), =($8, 0)))), AND(<($2, $7), IS NOT TRUE(OR(IS NULL($10), =($8, 0))), IS NOT TRUE(<($2, $7)), IS NOT TRUE(>($8, $9))))])
+ LogicalCorrelate(correlation=[$cor0], joinType=[left], requiredColumns=[{1}])
+ LogicalCorrelate(correlation=[$cor0], joinType=[left], requiredColumns=[{1}])
+ LogicalProject(EMPNO=[$0], ENAME=[$1], DEPTNO=[$7])
+ LogicalTableScan(table=[[CATALOG, SALES, EMPNULLABLES]])
+ LogicalProject(m=[$0], c=[$1], d=[$2], trueLiteral=[true])
+ LogicalAggregate(group=[{}], m=[MIN($0)], c=[COUNT()], d=[COUNT($0)])
+ LogicalProject(DEPTNO=[$0])
+ LogicalFilter(condition=[AND(=($cor0.ENAME, CAST($1):VARCHAR(20)), >($0, 10))])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPTNULLABLES]])
+ LogicalProject(m=[$0], c=[$1], d=[$2], trueLiteral=[true])
+ LogicalAggregate(group=[{}], m=[MAX($0)], c=[COUNT()], d=[COUNT($0)])
+ LogicalProject(DEPTNO=[$0])
+ LogicalFilter(condition=[AND(=($cor0.ENAME, CAST($1):VARCHAR(20)), <($0, 20))])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPTNULLABLES]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+LogicalProject(EMPNO=[$0])
+ LogicalProject(EMPNO=[$0], ENAME=[$1], DEPTNO=[$2])
+ LogicalFilter(condition=[OR(CASE(IS NULL($6), false, =($4, 0), false, IS TRUE(>($2, $3)), true, >($4, $5), null:BOOLEAN, >($2, $3)), CASE(IS NULL($10), false, =($8, 0), false, IS TRUE(<($2, $7)), true, >($8, $9), null:BOOLEAN, <($2, $7)))])
+ LogicalCorrelate(correlation=[$cor0], joinType=[left], requiredColumns=[{1}])
+ LogicalCorrelate(correlation=[$cor0], joinType=[left], requiredColumns=[{1}])
+ LogicalProject(EMPNO=[$0], ENAME=[$1], DEPTNO=[$7])
+ LogicalTableScan(table=[[CATALOG, SALES, EMPNULLABLES]])
+ LogicalProject(m=[$0], c=[$1], d=[$2], trueLiteral=[true])
+ LogicalAggregate(group=[{}], m=[MIN($0)], c=[COUNT()], d=[COUNT($0)])
+ LogicalProject(DEPTNO=[$0])
+ LogicalFilter(condition=[AND(=($cor0.ENAME, CAST($1):VARCHAR(20)), >($0, 10))])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPTNULLABLES]])
+ LogicalProject(m=[$0], c=[$1], d=[$2], trueLiteral=[true])
+ LogicalAggregate(group=[{}], m=[MAX($0)], c=[COUNT()], d=[COUNT($0)])
+ LogicalProject(DEPTNO=[$0])
+ LogicalFilter(condition=[AND(=($cor0.ENAME, CAST($1):VARCHAR(20)), <($0, 20))])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPTNULLABLES]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testSomeWithTwoSubQueries">
+ <Resource name="sql">
+ <![CDATA[select empno from sales.empnullables
+where deptno > some(
+ select deptno from sales.deptnullables where name = 'dept1')
+or deptno < some(
+ select deptno from sales.deptnullables where name = 'dept2')]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalProject(EMPNO=[$0])
+ LogicalFilter(condition=[OR(> SOME($1, {
+LogicalProject(DEPTNO=[$0])
+ LogicalFilter(condition=[=($1, 'dept1')])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPTNULLABLES]])
+}), < SOME($1, {
+LogicalProject(DEPTNO=[$0])
+ LogicalFilter(condition=[=($1, 'dept2')])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPTNULLABLES]])
+}))])
+ LogicalProject(EMPNO=[$0], DEPTNO=[$7])
+ LogicalTableScan(table=[[CATALOG, SALES, EMPNULLABLES]])
+]]>
+ </Resource>
+ <Resource name="planMid">
+ <![CDATA[
+LogicalProject(EMPNO=[$0])
+ LogicalProject(EMPNO=[$0], DEPTNO=[$1])
+ LogicalFilter(condition=[OR(AND(>($1, $2), <>($3, 0)), AND(>($1, $2), <>($3, 0), IS NOT TRUE(>($1, $2)), <=($3, $4)), AND(<($1, $5), <>($6, 0)), AND(<($1, $5), <>($6, 0), IS NOT TRUE(<($1, $5)), <=($6, $7)))])
+ LogicalJoin(condition=[true], joinType=[inner])
+ LogicalJoin(condition=[true], joinType=[inner])
+ LogicalProject(EMPNO=[$0], DEPTNO=[$7])
+ LogicalTableScan(table=[[CATALOG, SALES, EMPNULLABLES]])
+ LogicalAggregate(group=[{}], m=[MIN($0)], c=[COUNT()], d=[COUNT($0)])
+ LogicalProject(DEPTNO=[$0])
+ LogicalFilter(condition=[=($1, 'dept1')])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPTNULLABLES]])
+ LogicalAggregate(group=[{}], m=[MAX($0)], c=[COUNT()], d=[COUNT($0)])
+ LogicalProject(DEPTNO=[$0])
+ LogicalFilter(condition=[=($1, 'dept2')])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPTNULLABLES]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+LogicalProject(EMPNO=[$0])
+ LogicalProject(EMPNO=[$0], DEPTNO=[$1])
+ LogicalFilter(condition=[OR(CASE(=($3, 0), false, IS TRUE(>($1, $2)), true, >($3, $4), null:BOOLEAN, >($1, $2)), CASE(=($6, 0), false, IS TRUE(<($1, $5)), true, >($6, $7), null:BOOLEAN, <($1, $5)))])
+ LogicalJoin(condition=[true], joinType=[inner])
+ LogicalJoin(condition=[true], joinType=[inner])
+ LogicalProject(EMPNO=[$0], DEPTNO=[$7])
+ LogicalTableScan(table=[[CATALOG, SALES, EMPNULLABLES]])
+ LogicalAggregate(group=[{}], m=[MIN($0)], c=[COUNT()], d=[COUNT($0)])
+ LogicalProject(DEPTNO=[$0])
+ LogicalFilter(condition=[=($1, 'dept1')])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPTNULLABLES]])
+ LogicalAggregate(group=[{}], m=[MAX($0)], c=[COUNT()], d=[COUNT($0)])
+ LogicalProject(DEPTNO=[$0])
+ LogicalFilter(condition=[=($1, 'dept2')])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPTNULLABLES]])
]]>
</Resource>
</TestCase>
diff --git a/testkit/src/main/java/org/apache/calcite/test/catalog/MockCatalogReaderSimple.java b/testkit/src/main/java/org/apache/calcite/test/catalog/MockCatalogReaderSimple.java
index 75704b3a08..ac201378e6 100644
--- a/testkit/src/main/java/org/apache/calcite/test/catalog/MockCatalogReaderSimple.java
+++ b/testkit/src/main/java/org/apache/calcite/test/catalog/MockCatalogReaderSimple.java
@@ -141,6 +141,13 @@ public class MockCatalogReaderSimple extends MockCatalogReader {
registerTable(deptTable);
}
+ private void registerTableDeptNullables(MockSchema salesSchema, Fixture fixture) {
+ MockTable deptNullablesTable = MockTable.create(this, salesSchema, "DEPTNULLABLES", false, 4);
+ deptNullablesTable.addColumn("DEPTNO", fixture.intTypeNull, true);
+ deptNullablesTable.addColumn("NAME", fixture.varchar10TypeNull);
+ registerTable(deptNullablesTable);
+ }
+
private void registerTableDeptSingle(MockSchema salesSchema, Fixture fixture) {
MockTable deptSingleTable =
MockTable.create(this, salesSchema, "DEPT_SINGLE", false, 4);
@@ -477,6 +484,9 @@ public class MockCatalogReaderSimple extends MockCatalogReader {
// Register "DEPT" table.
registerTableDept(salesSchema, fixture);
+ // Register "DEPTNULLABLES" table.
+ registerTableDeptNullables(salesSchema, fixture);
+
// Register "DEPT_SINGLE" table.
registerTableDeptSingle(salesSchema, fixture);