This is an automated email from the ASF dual-hosted git repository.
xiong pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/calcite.git
The following commit(s) were added to refs/heads/main by this push:
new 93f914c505 [CALCITE-6481] Optimize 'VALUES...UNION...VALUES' to a
single 'VALUES' the IN-list contains CAST and it is converted to VALUES
93f914c505 is described below
commit 93f914c50560bd822e7153aafdbd5cbbf771dc3b
Author: Xiong Duan <[email protected]>
AuthorDate: Sat Aug 10 09:04:42 2024 +0800
[CALCITE-6481] Optimize 'VALUES...UNION...VALUES' to a single 'VALUES' the
IN-list contains CAST and it is converted to VALUES
---
.../org/apache/calcite/rel/rules/CoreRules.java | 5 +
.../calcite/rel/rules/UnionToValuesRule.java | 145 +++++++++
.../org/apache/calcite/rel/type/RelDataType.java | 27 ++
.../calcite/rel/rel2sql/RelToSqlConverterTest.java | 22 ++
.../org/apache/calcite/test/RelOptRulesTest.java | 150 +++++++++
.../org/apache/calcite/test/RelOptRulesTest.xml | 351 +++++++++++++++++++++
.../org/apache/calcite/test/RelOptFixture.java | 4 +
7 files changed, 704 insertions(+)
diff --git a/core/src/main/java/org/apache/calcite/rel/rules/CoreRules.java
b/core/src/main/java/org/apache/calcite/rel/rules/CoreRules.java
index aca746905d..69766761fb 100644
--- a/core/src/main/java/org/apache/calcite/rel/rules/CoreRules.java
+++ b/core/src/main/java/org/apache/calcite/rel/rules/CoreRules.java
@@ -771,6 +771,11 @@ public class CoreRules {
public static final UnionToDistinctRule UNION_TO_DISTINCT =
UnionToDistinctRule.Config.DEFAULT.toRule();
+ /** Rule that translates a {@link Union} to {@link Values}
+ * if it's all input are {@link Values}. */
+ public static final UnionToValuesRule UNION_TO_VALUES =
+ UnionToValuesRule.Config.DEFAULT.toRule();
+
/** Rule that rewrite {@link Sample} which is bernoulli to the {@link
Filter}. */
public static final SampleToFilterRule SAMPLE_TO_FILTER =
SampleToFilterRule.Config.DEFAULT.toRule();
diff --git
a/core/src/main/java/org/apache/calcite/rel/rules/UnionToValuesRule.java
b/core/src/main/java/org/apache/calcite/rel/rules/UnionToValuesRule.java
new file mode 100644
index 0000000000..dc16e591d4
--- /dev/null
+++ b/core/src/main/java/org/apache/calcite/rel/rules/UnionToValuesRule.java
@@ -0,0 +1,145 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to you under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+package org.apache.calcite.rel.rules;
+
+import org.apache.calcite.plan.RelOptRuleCall;
+import org.apache.calcite.plan.RelRule;
+import org.apache.calcite.plan.hep.HepRelVertex;
+import org.apache.calcite.plan.volcano.RelSubset;
+import org.apache.calcite.rel.RelNode;
+import org.apache.calcite.rel.core.Union;
+import org.apache.calcite.rel.core.Values;
+import org.apache.calcite.rel.type.RelDataType;
+import org.apache.calcite.rex.RexLiteral;
+
+import com.google.common.collect.ImmutableCollection;
+import com.google.common.collect.ImmutableList;
+import com.google.common.collect.ImmutableSet;
+
+import org.immutables.value.Value;
+
+import java.util.List;
+
+/**
+ * Planner rule that converts
+ * {@link org.apache.calcite.rel.core.Union} with
+ * inputs only containing {@link org.apache.calcite.rel.core.Values}
+ * into an {@link org.apache.calcite.rel.core.Values}.
+ *
+ * <p>For example,
+ *
+ * <blockquote><pre>{@code
+ * Union(all=[true])
+ * Values(tuples=[[{ 3, null }]])
+ * Values(tuples=[[{ 7369, null }]])
+ * Values(tuples=[[{ 1, 2 }]])
+ * }</pre></blockquote>
+ *
+ * <p>becomes
+ *
+ * <blockquote><pre>{@code
+ * Values(tuples=[[{ 3, null }, { 7369, null }, { 1, 2 }]])
+ * }</pre></blockquote>
+ *
+ * <p>If (<code>all</code> = <code>false</code>),
+ * {@link org.apache.calcite.rel.core.Values} value will be set list.
+ *
+ */
[email protected]
+public class UnionToValuesRule extends RelRule<UnionToValuesRule.Config>
+ implements TransformationRule {
+
+ /**
+ * Creates a UnionToValuesRule.
+ */
+ protected UnionToValuesRule(UnionToValuesRule.Config config) {
+ super(config);
+ }
+
+ //~ Methods ----------------------------------------------------------------
+
+ @Override public void onMatch(RelOptRuleCall call) {
+ Union union = call.rel(0);
+ List<RelNode> relNodeList = union.getInputs();
+ final RelDataType relDataType = union.getRowType();
+ if (!relNodeList.stream().allMatch(relNode ->
+ isValuesWithSpecifiedDataType(relNode, relDataType))) {
+ return;
+ }
+ ImmutableCollection.Builder<ImmutableList<RexLiteral>> tupleList =
+ getImmutableListBuilder(union.all, relNodeList);
+ Values values =
+ (Values) call.builder().values(tupleList.build(),
union.getRowType()).build();
+ call.transformTo(values);
+ }
+
+ private static ImmutableCollection.Builder<ImmutableList<RexLiteral>>
getImmutableListBuilder(
+ Boolean all, List<RelNode> relNodeList) {
+ ImmutableCollection.Builder<ImmutableList<RexLiteral>> tupleList;
+ if (all) {
+ tupleList = ImmutableList.builder();
+ } else {
+ tupleList = ImmutableSet.builder();
+ }
+ for (RelNode relNode : relNodeList) {
+ Values values = (Values) relNode.stripped();
+ ImmutableList<ImmutableList<RexLiteral>> immutableLists =
values.getTuples();
+ for (ImmutableList<RexLiteral> immutableList : immutableLists) {
+ ImmutableList.Builder<RexLiteral> tuple = ImmutableList.builder();
+ for (RexLiteral rexLiteral : immutableList) {
+ tuple.add(rexLiteral);
+ }
+ tupleList.add(tuple.build());
+ }
+ }
+ return tupleList;
+ }
+
+ private static boolean isValuesWithSpecifiedDataType(RelNode node,
RelDataType relDataType) {
+ if (node instanceof Values
+ && relDataType.equalsSansFieldNamesAndNullability(node.getRowType())) {
+ return true;
+ }
+ if (node instanceof HepRelVertex || node instanceof RelSubset) {
+ return isValuesWithSpecifiedDataType(node.stripped(), relDataType);
+ }
+ return false;
+ }
+
+ /**
+ * Rule configuration.
+ */
+ @Value.Immutable
+ public interface Config extends RelRule.Config {
+ Config DEFAULT = ImmutableUnionToValuesRule.Config.of()
+ .withDescription("UnionToValuesRule")
+ .withOperandFor(Union.class);
+
+ @Override default UnionToValuesRule toRule() {
+ return new UnionToValuesRule(this);
+ }
+
+ /**
+ * Defines an operand tree for the given classes.
+ */
+ default UnionToValuesRule.Config withOperandFor(Class<? extends Union>
unionClass) {
+ return withOperandSupplier(b0 ->
+ b0.operand(unionClass).anyInputs())
+ .as(UnionToValuesRule.Config.class);
+ }
+ }
+}
diff --git a/core/src/main/java/org/apache/calcite/rel/type/RelDataType.java
b/core/src/main/java/org/apache/calcite/rel/type/RelDataType.java
index bbd2bf5536..e1fa9fcfd7 100644
--- a/core/src/main/java/org/apache/calcite/rel/type/RelDataType.java
+++ b/core/src/main/java/org/apache/calcite/rel/type/RelDataType.java
@@ -286,6 +286,33 @@ public interface RelDataType {
}
}
+ /**
+ * Same as {@link #equalsSansFieldNames}, but ignore nullability also.
+ */
+ default boolean equalsSansFieldNamesAndNullability(@Nullable RelDataType
that) {
+ if (this == that) {
+ return true;
+ }
+ if (that == null || getClass() != that.getClass()) {
+ return false;
+ }
+ if (isStruct()) {
+ List<RelDataTypeField> l1 = this.getFieldList();
+ List<RelDataTypeField> l2 = that.getFieldList();
+ if (l1.size() != l2.size()) {
+ return false;
+ }
+ for (int i = 0; i < l1.size(); i++) {
+ if (!SqlTypeUtil.equalSansNullability(l1.get(i).getType(),
l2.get(i).getType())) {
+ return false;
+ }
+ }
+ return true;
+ } else {
+ return equals(that);
+ }
+ }
+
/** Returns whether this type is a measure.
*
* @see SqlTypeUtil#fromMeasure(RelDataTypeFactory, RelDataType)
diff --git
a/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java
b/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java
index bf80bd8fee..2542f1a3e9 100644
---
a/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java
+++
b/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java
@@ -491,6 +491,28 @@ class RelToSqlConverterTest {
relFn(relFn).ok(expected);
}
+ @Test void testSelectWhereIn2() {
+ final Function<RelBuilder, RelNode> relFn = b -> b
+ .scan("EMP")
+ .filter(b.in(b.field("COMM"), b.cast(b.literal(1.1),
SqlTypeName.INTEGER), b.literal(2)))
+ .build();
+ final String expected = "SELECT *\n"
+ + "FROM \"scott\".\"EMP\"\n"
+ + "WHERE \"COMM\" IN (1, 2)";
+ relFn(relFn).ok(expected);
+ }
+
+ @Test void testSelectWhereIn3() {
+ final Function<RelBuilder, RelNode> relFn = b -> b
+ .scan("EMP")
+ .filter(b.in(b.field("COMM"), b.cast(b.literal(1.1),
SqlTypeName.INTEGER), b.literal(2)))
+ .build();
+ final String expected = "SELECT *\n"
+ + "FROM \"scott\".\"EMP\"\n"
+ + "WHERE \"COMM\" IN (1, 2)";
+ relFn(relFn).ok(expected);
+ }
+
@Test void testUsesSubqueryWhenSortingByIdThenOrdinal() {
final Function<RelBuilder, RelNode> relFn = b -> b
.scan("EMP")
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 f9eb6dad23..8b350a2051 100644
--- a/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
+++ b/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
@@ -3093,6 +3093,156 @@ class RelOptRulesTest extends RelOptTestBase {
.check();
}
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-6481";>[CALCITE-6481]
+ * Optimize 'VALUES...UNION...VALUES' to a single 'VALUES' the IN-list
contains CAST
+ * and it is converted to VALUES</a>. */
+ @Test void testUnionToValuesByInList() {
+ final String sql = ""
+ + "\n"
+ + "with t1(a, y) as (select * from (values (1, 2), (3, null), (7369,
null), (7499, 30), (null, 20), (null, 5)) as t1)\n"
+ + "select *\n"
+ + "from t1\n"
+ + "where (t1.a, t1.y) in ((1, 2), (3, null), (7369, null), (7499, 30),
(null, 20), (null, 5))";
+ sql(sql)
+ .withRule(CoreRules.UNION_TO_VALUES)
+ .withInSubQueryThreshold(0)
+ .check();
+ }
+
+ @Test void testUnionToValuesByInList2() {
+ final String sql = ""
+ + "\n"
+ + "with t1(a, y) as (select * from (values (1, 2), (3, null), (7369,
null), (7499, 30), (null, 20), (null, 5)) as t1)\n"
+ + "select *\n"
+ + "from t1\n"
+ + "where (t1.a, t1.y) in ((cast(1.1 as int), 2), (3, null), (7369,
null), (7499, 30), (null, cast(20.2 as int)), (null, 5))";
+ sql(sql)
+ .withRule(CoreRules.UNION_TO_VALUES)
+ .withInSubQueryThreshold(0)
+ .check();
+ }
+
+ @Test void testUnionToValuesByInList3() {
+ final String sql = "select * from dept where deptno in (12, 34, cast(56.4
as int))";
+ sql(sql)
+ .withRule(CoreRules.UNION_TO_VALUES)
+ .withInSubQueryThreshold(0)
+ .check();
+ }
+
+ @Test void testUnionToValuesByInList4() {
+ final String sql = "select * from dept where deptno in (12, 34, cast(56.4
as double))";
+ sql(sql)
+ .withRule(CoreRules.UNION_TO_VALUES)
+ .withInSubQueryThreshold(0)
+ .check();
+ }
+
+ @Test void testUnionToValuesByInList5() {
+ final String sql = "select deptno in (12, 34, cast(56.4 as double)) from
dept";
+ sql(sql)
+ .withRule(CoreRules.UNION_TO_VALUES)
+ .withInSubQueryThreshold(0)
+ .check();
+ }
+
+ @Test void testUnionToValuesByUnion() {
+ final String sql = "select *\n"
+ + "from (values (1, 'a'), (2, 'b')) as t(x, y)\n"
+ + "union\n"
+ + "select *\n"
+ + "from (values (1, 'a'), (2, 'b'), (1, 'b'), (2, 'c'), (2, 'c')) as
t(x, y)";
+ sql(sql)
+ .withRule(CoreRules.PROJECT_REMOVE, CoreRules.UNION_TO_VALUES)
+ .withInSubQueryThreshold(0)
+ .check();
+ }
+
+ @Test void testUnionToValuesByUnion2() {
+ final String sql = "select *\n"
+ + "from (values ('a'), ('b')) as t(x)\n"
+ + "union\n"
+ + "select *\n"
+ + "from (values ('a'), ('b'), ('b'), ('c'), ('c')) as t(y)";
+ sql(sql)
+ .withRule(CoreRules.PROJECT_REMOVE, CoreRules.UNION_TO_VALUES)
+ .withInSubQueryThreshold(0)
+ .check();
+ }
+
+ @Test void testUnionToValuesByUnion3() {
+ final String sql = "select *\n"
+ + "from (values (5.0)) as t(x)\n"
+ + "union\n"
+ + "select *\n"
+ + "from (values (5)) as t(y)";
+ sql(sql)
+ .withRule(CoreRules.PROJECT_REMOVE, CoreRules.UNION_TO_VALUES)
+ .withInSubQueryThreshold(0)
+ .check();
+ }
+
+ @Test void testUnionToValuesByUnion4() {
+ final String sql = "select *\n"
+ + "from (values (cast(5.0 as int))) as t(x)\n"
+ + "union\n"
+ + "select *\n"
+ + "from (values (5)) as t(y)";
+ sql(sql)
+ .withRule(CoreRules.PROJECT_REMOVE, CoreRules.UNION_TO_VALUES)
+ .withInSubQueryThreshold(0)
+ .check();
+ }
+
+ @Test void testUnionToValuesByUnionAll() {
+ final String sql = "select *\n"
+ + "from (values (1, 'a'), (2, 'b')) as t(x, y)\n"
+ + "union all\n"
+ + "select *\n"
+ + "from (values (1, 'a'), (2, 'b'), (1, 'b'), (2, 'c'), (2, 'c')) as
t(x, y)";
+ sql(sql)
+ .withRule(CoreRules.PROJECT_REMOVE, CoreRules.UNION_TO_VALUES)
+ .withInSubQueryThreshold(0)
+ .check();
+ }
+
+ @Test void testUnionToValuesByUnionAll2() {
+ final String sql = "select *\n"
+ + "from (values ('a'), ('b')) as t(x)\n"
+ + "union all\n"
+ + "select *\n"
+ + "from (values ('a'), ('b'), ('b'), ('c'), ('c')) as t(y)";
+ sql(sql)
+ .withRule(CoreRules.PROJECT_REMOVE, CoreRules.UNION_TO_VALUES)
+ .withInSubQueryThreshold(0)
+ .check();
+ }
+
+ @Test void testUnionToValuesByUnionAll3() {
+ final String sql = "select *\n"
+ + "from (values (5.0)) as t(x)\n"
+ + "union all\n"
+ + "select *\n"
+ + "from (values (5)) as t(y)";
+ sql(sql)
+ .withRule(CoreRules.PROJECT_REMOVE, CoreRules.UNION_TO_VALUES)
+ .withInSubQueryThreshold(0)
+ .check();
+ }
+
+ @Test void testUnionToValuesByUnionAll4() {
+ final String sql = "select *\n"
+ + "from (values (cast(5.0 as int))) as t(x)\n"
+ + "union all\n"
+ + "select *\n"
+ + "from (values (5)) as t(y)";
+ sql(sql)
+ .withRule(CoreRules.PROJECT_REMOVE, CoreRules.UNION_TO_VALUES)
+ .withInSubQueryThreshold(0)
+ .check();
+ }
+
@Test void testMinusMergeRule() {
final String sql = "select * from (\n"
+ "select * from (\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 a849461992..3135c5e958 100644
--- a/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
+++ b/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
@@ -16334,6 +16334,357 @@ LogicalUnion(all=[false])
LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
LogicalProject(DEPTNO=[$0], NAME=[$1])
LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testUnionToValuesByInList">
+ <Resource name="sql">
+ <![CDATA[
+with t1(a, y) as (select * from (values (1, 2), (3, null), (7369, null),
(7499, 30), (null, 20), (null, 5)) as t1)
+select *
+from t1
+where (t1.a, t1.y) in ((1, 2), (3, null), (7369, null), (7499, 30), (null,
20), (null, 5))]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalProject(A=[$0], Y=[$1])
+ LogicalJoin(condition=[AND(=($0, $2), =($1, $3))], joinType=[inner])
+ LogicalProject(EXPR$0=[$0], EXPR$1=[$1])
+ LogicalValues(tuples=[[{ 1, 2 }, { 3, null }, { 7369, null }, { 7499, 30
}, { null, 20 }, { null, 5 }]])
+ LogicalAggregate(group=[{0, 1}])
+ LogicalUnion(all=[true])
+ LogicalValues(tuples=[[{ 3, null }]])
+ LogicalValues(tuples=[[{ 7369, null }]])
+ LogicalValues(tuples=[[{ null, 20 }]])
+ LogicalValues(tuples=[[{ null, 5 }]])
+ LogicalValues(tuples=[[{ 1, 2 }, { 7499, 30 }]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+LogicalProject(A=[$0], Y=[$1])
+ LogicalJoin(condition=[AND(=($0, $2), =($1, $3))], joinType=[inner])
+ LogicalProject(EXPR$0=[$0], EXPR$1=[$1])
+ LogicalValues(tuples=[[{ 1, 2 }, { 3, null }, { 7369, null }, { 7499, 30
}, { null, 20 }, { null, 5 }]])
+ LogicalAggregate(group=[{0, 1}])
+ LogicalValues(tuples=[[{ 3, null }, { 7369, null }, { null, 20 }, {
null, 5 }, { 1, 2 }, { 7499, 30 }]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testUnionToValuesByInList2">
+ <Resource name="sql">
+ <![CDATA[
+with t1(a, y) as (select * from (values (1, 2), (3, null), (7369, null),
(7499, 30), (null, 20), (null, 5)) as t1)
+select *
+from t1
+where (t1.a, t1.y) in ((cast(1.1 as int), 2), (3, null), (7369, null), (7499,
30), (null, cast(20.2 as int)), (null, 5))]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalProject(A=[$0], Y=[$1])
+ LogicalJoin(condition=[AND(=($0, $2), =($1, $3))], joinType=[inner])
+ LogicalProject(EXPR$0=[$0], EXPR$1=[$1])
+ LogicalValues(tuples=[[{ 1, 2 }, { 3, null }, { 7369, null }, { 7499, 30
}, { null, 20 }, { null, 5 }]])
+ LogicalAggregate(group=[{0, 1}])
+ LogicalUnion(all=[true])
+ LogicalValues(tuples=[[{ 1, 2 }]])
+ LogicalValues(tuples=[[{ 3, null }]])
+ LogicalValues(tuples=[[{ 7369, null }]])
+ LogicalValues(tuples=[[{ null, 20 }]])
+ LogicalValues(tuples=[[{ null, 5 }]])
+ LogicalValues(tuples=[[{ 7499, 30 }]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+LogicalProject(A=[$0], Y=[$1])
+ LogicalJoin(condition=[AND(=($0, $2), =($1, $3))], joinType=[inner])
+ LogicalProject(EXPR$0=[$0], EXPR$1=[$1])
+ LogicalValues(tuples=[[{ 1, 2 }, { 3, null }, { 7369, null }, { 7499, 30
}, { null, 20 }, { null, 5 }]])
+ LogicalAggregate(group=[{0, 1}])
+ LogicalValues(tuples=[[{ 1, 2 }, { 3, null }, { 7369, null }, { null, 20
}, { null, 5 }, { 7499, 30 }]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testUnionToValuesByInList3">
+ <Resource name="sql">
+ <![CDATA[select * from dept where deptno in (12, 34, cast(56.4 as
int))]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalProject(DEPTNO=[$0], NAME=[$1])
+ LogicalJoin(condition=[=($0, $2)], joinType=[inner])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+ LogicalAggregate(group=[{0}])
+ LogicalUnion(all=[true])
+ LogicalValues(tuples=[[{ 56 }]])
+ LogicalValues(tuples=[[{ 12 }, { 34 }]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+LogicalProject(DEPTNO=[$0], NAME=[$1])
+ LogicalJoin(condition=[=($0, $2)], joinType=[inner])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+ LogicalAggregate(group=[{0}])
+ LogicalValues(tuples=[[{ 56 }, { 12 }, { 34 }]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testUnionToValuesByInList4">
+ <Resource name="sql">
+ <![CDATA[select * from dept where deptno in (12, 34, cast(56.4 as
double))]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalProject(DEPTNO=[$0], NAME=[$1])
+ LogicalJoin(condition=[=($2, $3)], joinType=[inner])
+ LogicalProject(DEPTNO=[$0], NAME=[$1], DEPTNO0=[CAST($0):DOUBLE NOT NULL])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+ LogicalAggregate(group=[{0}])
+ LogicalUnion(all=[true])
+ LogicalValues(tuples=[[{ 12 }]])
+ LogicalValues(tuples=[[{ 34 }]])
+ LogicalValues(tuples=[[{ 56.4 }]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+LogicalProject(DEPTNO=[$0], NAME=[$1])
+ LogicalJoin(condition=[=($2, $3)], joinType=[inner])
+ LogicalProject(DEPTNO=[$0], NAME=[$1], DEPTNO0=[CAST($0):DOUBLE NOT NULL])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+ LogicalAggregate(group=[{0}])
+ LogicalValues(tuples=[[{ 12 }, { 34 }, { 56.4 }]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testUnionToValuesByInList5">
+ <Resource name="sql">
+ <![CDATA[select deptno in (12, 34, cast(56.4 as double)) from dept]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalProject(EXPR$0=[CAST(OR(AND(IS NOT NULL($6), <>($2, 0)), AND(<($3, $2),
null, <>($2, 0), IS NULL($6)))):BOOLEAN NOT NULL])
+ LogicalJoin(condition=[=($4, $5)], joinType=[left])
+ LogicalProject(DEPTNO=[$0], NAME=[$1], $f0=[$2], $f1=[$3],
DEPTNO0=[CAST($0):DOUBLE NOT NULL])
+ LogicalJoin(condition=[true], joinType=[inner])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+ LogicalAggregate(group=[{}], agg#0=[COUNT()], agg#1=[COUNT($0)])
+ LogicalProject(EXPR$0=[$0], $f1=[true])
+ LogicalUnion(all=[true])
+ LogicalValues(tuples=[[{ 12 }]])
+ LogicalValues(tuples=[[{ 34 }]])
+ LogicalValues(tuples=[[{ 56.4 }]])
+ LogicalAggregate(group=[{0}], agg#0=[MIN($1)])
+ LogicalProject(EXPR$0=[$0], $f1=[true])
+ LogicalUnion(all=[true])
+ LogicalValues(tuples=[[{ 12 }]])
+ LogicalValues(tuples=[[{ 34 }]])
+ LogicalValues(tuples=[[{ 56.4 }]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+LogicalProject(EXPR$0=[CAST(OR(AND(IS NOT NULL($6), <>($2, 0)), AND(<($3, $2),
null, <>($2, 0), IS NULL($6)))):BOOLEAN NOT NULL])
+ LogicalJoin(condition=[=($4, $5)], joinType=[left])
+ LogicalProject(DEPTNO=[$0], NAME=[$1], $f0=[$2], $f1=[$3],
DEPTNO0=[CAST($0):DOUBLE NOT NULL])
+ LogicalJoin(condition=[true], joinType=[inner])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+ LogicalAggregate(group=[{}], agg#0=[COUNT()], agg#1=[COUNT($0)])
+ LogicalProject(EXPR$0=[$0], $f1=[true])
+ LogicalValues(tuples=[[{ 12 }, { 34 }, { 56.4 }]])
+ LogicalAggregate(group=[{0}], agg#0=[MIN($1)])
+ LogicalProject(EXPR$0=[$0], $f1=[true])
+ LogicalValues(tuples=[[{ 12 }, { 34 }, { 56.4 }]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testUnionToValuesByUnion">
+ <Resource name="sql">
+ <![CDATA[select *
+from (values (1, 'a'), (2, 'b')) as t(x, y)
+union
+select *
+from (values (1, 'a'), (2, 'b'), (1, 'b'), (2, 'c'), (2, 'c')) as t(x, y)]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalUnion(all=[false])
+ LogicalProject(X=[$0], Y=[$1])
+ LogicalValues(tuples=[[{ 1, 'a' }, { 2, 'b' }]])
+ LogicalProject(X=[$0], Y=[$1])
+ LogicalValues(tuples=[[{ 1, 'a' }, { 2, 'b' }, { 1, 'b' }, { 2, 'c' }, {
2, 'c' }]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+LogicalValues(tuples=[[{ 1, 'a' }, { 2, 'b' }, { 1, 'b' }, { 2, 'c' }]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testUnionToValuesByUnion2">
+ <Resource name="sql">
+ <![CDATA[select *
+from (values ('a'), ('b')) as t(x)
+union
+select *
+from (values ('a'), ('b'), ('b'), ('c'), ('c')) as t(y)]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalUnion(all=[false])
+ LogicalProject(X=[$0])
+ LogicalValues(tuples=[[{ 'a' }, { 'b' }]])
+ LogicalProject(Y=[$0])
+ LogicalValues(tuples=[[{ 'a' }, { 'b' }, { 'b' }, { 'c' }, { 'c' }]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+LogicalValues(tuples=[[{ 'a' }, { 'b' }, { 'c' }]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testUnionToValuesByUnion3">
+ <Resource name="sql">
+ <![CDATA[select *
+from (values (5.0)) as t(x)
+union
+select *
+from (values (5)) as t(y)]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalUnion(all=[false])
+ LogicalProject(X=[$0])
+ LogicalValues(tuples=[[{ 5.0 }]])
+ LogicalProject(Y=[$0])
+ LogicalValues(tuples=[[{ 5 }]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+LogicalUnion(all=[false])
+ LogicalValues(tuples=[[{ 5.0 }]])
+ LogicalValues(tuples=[[{ 5 }]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testUnionToValuesByUnion4">
+ <Resource name="sql">
+ <![CDATA[select *
+from (values (cast(5.0 as int))) as t(x)
+union
+select *
+from (values (5)) as t(y)]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalUnion(all=[false])
+ LogicalProject(X=[$0])
+ LogicalValues(tuples=[[{ 5 }]])
+ LogicalProject(Y=[$0])
+ LogicalValues(tuples=[[{ 5 }]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+LogicalValues(tuples=[[{ 5 }]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testUnionToValuesByUnionAll">
+ <Resource name="sql">
+ <![CDATA[select *
+from (values (1, 'a'), (2, 'b')) as t(x, y)
+union all
+select *
+from (values (1, 'a'), (2, 'b'), (1, 'b'), (2, 'c'), (2, 'c')) as t(x, y)]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalUnion(all=[true])
+ LogicalProject(X=[$0], Y=[$1])
+ LogicalValues(tuples=[[{ 1, 'a' }, { 2, 'b' }]])
+ LogicalProject(X=[$0], Y=[$1])
+ LogicalValues(tuples=[[{ 1, 'a' }, { 2, 'b' }, { 1, 'b' }, { 2, 'c' }, {
2, 'c' }]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+LogicalValues(tuples=[[{ 1, 'a' }, { 2, 'b' }, { 1, 'a' }, { 2, 'b' }, { 1,
'b' }, { 2, 'c' }, { 2, 'c' }]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testUnionToValuesByUnionAll2">
+ <Resource name="sql">
+ <![CDATA[select *
+from (values ('a'), ('b')) as t(x)
+union all
+select *
+from (values ('a'), ('b'), ('b'), ('c'), ('c')) as t(y)]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalUnion(all=[true])
+ LogicalProject(X=[$0])
+ LogicalValues(tuples=[[{ 'a' }, { 'b' }]])
+ LogicalProject(Y=[$0])
+ LogicalValues(tuples=[[{ 'a' }, { 'b' }, { 'b' }, { 'c' }, { 'c' }]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+LogicalValues(tuples=[[{ 'a' }, { 'b' }, { 'a' }, { 'b' }, { 'b' }, { 'c' }, {
'c' }]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testUnionToValuesByUnionAll3">
+ <Resource name="sql">
+ <![CDATA[select *
+from (values (5.0)) as t(x)
+union all
+select *
+from (values (5)) as t(y)]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalUnion(all=[true])
+ LogicalProject(X=[$0])
+ LogicalValues(tuples=[[{ 5.0 }]])
+ LogicalProject(Y=[$0])
+ LogicalValues(tuples=[[{ 5 }]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+LogicalUnion(all=[true])
+ LogicalValues(tuples=[[{ 5.0 }]])
+ LogicalValues(tuples=[[{ 5 }]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testUnionToValuesByUnionAll4">
+ <Resource name="sql">
+ <![CDATA[select *
+from (values (cast(5.0 as int))) as t(x)
+union all
+select *
+from (values (5)) as t(y)]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalUnion(all=[true])
+ LogicalProject(X=[$0])
+ LogicalValues(tuples=[[{ 5 }]])
+ LogicalProject(Y=[$0])
+ LogicalValues(tuples=[[{ 5 }]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+LogicalValues(tuples=[[{ 5 }, { 5 }]])
]]>
</Resource>
</TestCase>
diff --git a/testkit/src/main/java/org/apache/calcite/test/RelOptFixture.java
b/testkit/src/main/java/org/apache/calcite/test/RelOptFixture.java
index fbeda9527d..55e8ba283c 100644
--- a/testkit/src/main/java/org/apache/calcite/test/RelOptFixture.java
+++ b/testkit/src/main/java/org/apache/calcite/test/RelOptFixture.java
@@ -250,6 +250,10 @@ public class RelOptFixture {
return withConfig(c -> c.withExpand(expand));
}
+ public RelOptFixture withInSubQueryThreshold(final int inSubQueryThreshold) {
+ return withConfig(c -> c.withInSubQueryThreshold(inSubQueryThreshold));
+ }
+
public RelOptFixture withConfig(
UnaryOperator<SqlToRelConverter.Config> transform) {
return withFactory(f -> f.withSqlToRelConfig(transform));
