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: 何润康 <[email protected]> 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);
