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:00 UTC
[calcite] branch main updated (e2028ad853 -> 74524cf1df)
This is an automated email from the ASF dual-hosted git repository.
libenchao pushed a change to branch main
in repository https://gitbox.apache.org/repos/asf/calcite.git
omit e2028ad853 [CALCITE-5655] Intermediate table alias should be different to avoid wrong field reference lookup in subquery remove phase
new 74524cf1df [CALCITE-5655] Wrong plan for multiple IN/SOME sub-queries with OR predicate
This update added new revisions after undoing existing revisions.
That is to say, some revisions that were in the old version of the
branch are not in the new version. This situation occurs
when a user --force pushes a change and generates a repository
containing something like this:
* -- * -- B -- O -- O -- O (e2028ad853)
\
N -- N -- N refs/heads/main (74524cf1df)
You should already have received notification emails for all of the O
revisions, and so the following emails describe only the N revisions
from the common base, B.
Any revisions marked "omit" are not gone; other references still
refer to them. Any revisions marked "discard" are gone forever.
The 1 revisions listed above as "new" are entirely new to this
repository and will be described in separate emails. The revisions
listed as "add" were already present in the repository and have only
been added to this reference.
Summary of changes:
[calcite] 01/01: [CALCITE-5655] Wrong plan for multiple IN/SOME sub-queries with OR predicate
Posted by li...@apache.org.
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);