You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@calcite.apache.org by jh...@apache.org on 2020/11/13 05:55:28 UTC
[calcite] branch master updated (0ce7685 -> ff4c16d)
This is an automated email from the ASF dual-hosted git repository.
jhyde pushed a change to branch master
in repository https://gitbox.apache.org/repos/asf/calcite.git.
from 0ce7685 [CALCITE-4390] SqlMatchRecognize returns wrong operand list (Dawid Wysakowicz)
add 6f94db0 [CALCITE-4380] Make class SqlNodeList implement List<SqlNode>
add 54a7bfa [CALCITE-4389] Calls to ROW and anonymous row operators sometimes print too many spaces
add e819b46 [CALCITE-4394] When generating code for a function call, take the inferred types of the operands into account
new ff4c16d [CALCITE-4383] In RelBuilder, optimize 'VALUES ... UNION ALL ... VALUES' to a single 'VALUES' with multiple rows
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:
core/src/main/codegen/templates/Parser.jj | 10 +-
.../calcite/adapter/enumerable/EnumUtils.java | 19 +--
.../calcite/adapter/enumerable/RexImpTable.java | 32 ++--
.../calcite/rel/rel2sql/RelToSqlConverter.java | 16 +-
.../org/apache/calcite/runtime/SqlFunctions.java | 4 +-
.../org/apache/calcite/sql/SqlCallBinding.java | 5 +-
.../java/org/apache/calcite/sql/SqlDialect.java | 28 ++--
.../main/java/org/apache/calcite/sql/SqlHint.java | 12 +-
.../java/org/apache/calcite/sql/SqlIdentifier.java | 12 ++
.../main/java/org/apache/calcite/sql/SqlKind.java | 3 +
.../main/java/org/apache/calcite/sql/SqlNode.java | 6 +-
.../java/org/apache/calcite/sql/SqlNodeList.java | 178 +++++++++++++++-----
.../java/org/apache/calcite/sql/SqlOperator.java | 70 ++++++--
.../main/java/org/apache/calcite/sql/SqlPivot.java | 4 +-
.../apache/calcite/sql/SqlWindowTableFunction.java | 7 +-
.../apache/calcite/sql/ddl/SqlCreateFunction.java | 2 +-
.../java/org/apache/calcite/sql/fun/SqlCase.java | 7 +-
.../apache/calcite/sql/fun/SqlCaseOperator.java | 3 +-
.../calcite/sql/fun/SqlInternalOperators.java | 37 +++++
.../calcite/sql/fun/SqlJsonObjectFunction.java | 25 ++-
.../calcite/sql/fun/SqlLibraryOperators.java | 12 +-
.../org/apache/calcite/sql/fun/SqlRowOperator.java | 1 -
.../calcite/sql/fun/SqlStdOperatorTable.java | 2 +
.../java/org/apache/calcite/sql/parser/Span.java | 8 +
.../apache/calcite/sql/parser/SqlParserUtil.java | 2 +-
.../sql/type/CompositeOperandTypeChecker.java | 18 +++
.../calcite/sql/type/SqlOperandTypeChecker.java | 8 +
.../org/apache/calcite/sql/util/SqlShuttle.java | 8 +-
.../org/apache/calcite/sql/validate/AggFinder.java | 7 +
.../calcite/sql/validate/AliasNamespace.java | 4 +-
.../apache/calcite/sql/validate/SqlValidator.java | 15 ++
.../calcite/sql/validate/SqlValidatorImpl.java | 74 +++++----
.../calcite/sql/validate/SqlValidatorUtil.java | 5 +-
.../calcite/sql/validate/TableNamespace.java | 4 +-
.../calcite/sql/validate/WithItemNamespace.java | 8 +-
.../apache/calcite/sql/validate/WithNamespace.java | 2 +-
.../validate/implicit/AbstractTypeCoercion.java | 2 +-
.../apache/calcite/sql2rel/SqlToRelConverter.java | 84 ++++------
.../calcite/sql2rel/StandardConvertletTable.java | 81 +++++-----
.../java/org/apache/calcite/tools/RelBuilder.java | 104 +++++++++++-
.../org/apache/calcite/util/BuiltInMethod.java | 2 +-
.../calcite/adapter/enumerable/EnumUtilsTest.java | 22 +--
.../rel/rel2sql/RelToSqlConverterStructsTest.java | 3 +-
.../calcite/rel/rel2sql/RelToSqlConverterTest.java | 180 ++++++++++++++-------
.../java/org/apache/calcite/sql/SqlNodeTest.java | 76 +++++++++
.../apache/calcite/sql/parser/SqlParserTest.java | 2 +-
.../parserextensiontesting/SqlCreateTable.java | 4 +-
.../calcite/sql/test/SqlOperatorBaseTest.java | 56 ++++---
.../apache/calcite/sql/type/SqlTypeUtilTest.java | 6 +-
.../org/apache/calcite/test/JdbcAdapterTest.java | 40 ++---
.../java/org/apache/calcite/test/JdbcTest.java | 17 +-
.../org/apache/calcite/test/RelBuilderTest.java | 80 ++++++++-
.../org/apache/calcite/test/RelOptRulesTest.java | 5 +-
.../org/apache/calcite/test/RelOptRulesTest.xml | 10 +-
.../apache/calcite/test/SqlToRelConverterTest.xml | 42 ++---
.../calcite/test/TypeCoercionConverterTest.xml | 167 ++++++++++---------
core/src/test/resources/sql/agg.iq | 85 +---------
core/src/test/resources/sql/join.iq | 40 +----
core/src/test/resources/sql/misc.iq | 33 ++--
core/src/test/resources/sql/outer.iq | 20 +--
core/src/test/resources/sql/sub-query.iq | 25 +--
core/src/test/resources/sql/winagg.iq | 20 +--
.../calcite/piglet/PigRelToSqlConverter.java | 2 +-
.../java/org/apache/calcite/test/PigRelOpTest.java | 6 +-
.../apache/calcite/server/ServerDdlExecutor.java | 2 +-
.../enumerable/CodeGenerationBenchmark.java | 8 +-
66 files changed, 1122 insertions(+), 760 deletions(-)
create mode 100644 core/src/test/java/org/apache/calcite/sql/SqlNodeTest.java
[calcite] 01/01: [CALCITE-4383] In RelBuilder,
optimize 'VALUES ... UNION ALL ... VALUES' to a single 'VALUES'
with multiple rows
Posted by jh...@apache.org.
This is an automated email from the ASF dual-hosted git repository.
jhyde pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/calcite.git
commit ff4c16d1ea2192435e543fc9572ae3a44decbf79
Author: Julian Hyde <jh...@apache.org>
AuthorDate: Fri Nov 6 23:27:52 2020 -0800
[CALCITE-4383] In RelBuilder, optimize 'VALUES ... UNION ALL ... VALUES' to a single 'VALUES' with multiple rows
---
.../apache/calcite/sql2rel/SqlToRelConverter.java | 21 +--
.../java/org/apache/calcite/tools/RelBuilder.java | 66 +++++++-
.../rel/rel2sql/RelToSqlConverterStructsTest.java | 3 +-
.../calcite/rel/rel2sql/RelToSqlConverterTest.java | 109 +++++++++-----
.../org/apache/calcite/test/JdbcAdapterTest.java | 41 +++--
.../java/org/apache/calcite/test/JdbcTest.java | 17 ++-
.../org/apache/calcite/test/RelBuilderTest.java | 61 +++++++-
.../org/apache/calcite/test/RelOptRulesTest.java | 5 +-
.../org/apache/calcite/test/RelOptRulesTest.xml | 10 +-
.../apache/calcite/test/SqlToRelConverterTest.xml | 42 +++---
.../calcite/test/TypeCoercionConverterTest.xml | 167 ++++++++++-----------
core/src/test/resources/sql/agg.iq | 85 +----------
core/src/test/resources/sql/join.iq | 40 +----
core/src/test/resources/sql/misc.iq | 33 ++--
core/src/test/resources/sql/outer.iq | 20 +--
core/src/test/resources/sql/sub-query.iq | 25 +--
core/src/test/resources/sql/winagg.iq | 20 +--
17 files changed, 364 insertions(+), 401 deletions(-)
diff --git a/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java b/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java
index 187ff17..3a39960 100644
--- a/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java
+++ b/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java
@@ -4330,7 +4330,6 @@ public class SqlToRelConverter {
return;
}
- final List<RelNode> unionRels = new ArrayList<>();
for (SqlNode rowConstructor1 : values.getOperandList()) {
SqlCall rowConstructor = (SqlCall) rowConstructor1;
Blackboard tmpBb = createBlackboard(bb.scope, null, false);
@@ -4347,22 +4346,14 @@ public class SqlToRelConverter {
(null == tmpBb.root)
? LogicalValues.createOneRow(cluster)
: tmpBb.root;
- unionRels.add(relBuilder.push(in)
- .project(Pair.left(exps), Pair.right(exps))
- .build());
+ relBuilder.push(in)
+ .project(Pair.left(exps), Pair.right(exps));
}
- if (unionRels.size() == 0) {
- throw new AssertionError("empty values clause");
- } else if (unionRels.size() == 1) {
- bb.setRoot(
- unionRels.get(0),
- true);
- } else {
- bb.setRoot(
- LogicalUnion.create(unionRels, true),
- true);
- }
+ bb.setRoot(
+ relBuilder.union(true, values.getOperandList().size())
+ .build(),
+ true);
}
//~ Inner Classes ----------------------------------------------------------
diff --git a/core/src/main/java/org/apache/calcite/tools/RelBuilder.java b/core/src/main/java/org/apache/calcite/tools/RelBuilder.java
index 759b46c..24ac3be 100644
--- a/core/src/main/java/org/apache/calcite/tools/RelBuilder.java
+++ b/core/src/main/java/org/apache/calcite/tools/RelBuilder.java
@@ -131,6 +131,7 @@ import java.util.function.UnaryOperator;
import java.util.stream.Collectors;
import javax.annotation.Nonnull;
+import static org.apache.calcite.sql.SqlKind.UNION;
import static org.apache.calcite.util.Static.RESOURCE;
/**
@@ -1561,6 +1562,22 @@ public class RelBuilder {
}
return this;
}
+
+ // If the expressions are all literals, and the input is a Values with N
+ // rows, replace with a Values with same tuple N times.
+ if (config.simplifyValues()
+ && frame.rel instanceof Values
+ && nodeList.stream().allMatch(e -> e instanceof RexLiteral)) {
+ final Values values = (Values) build();
+ final RelDataTypeFactory.Builder typeBuilder = getTypeFactory().builder();
+ Pair.forEach(fieldNameList, nodeList, (name, expr) ->
+ typeBuilder.add(name, expr.getType()));
+ @SuppressWarnings({"unchecked", "rawtypes"})
+ final List<RexLiteral> tuple = (List<RexLiteral>) (List) nodeList;
+ return values(Collections.nCopies(values.tuples.size(), tuple),
+ typeBuilder.build());
+ }
+
final RelNode project =
struct.projectFactory.createProject(frame.rel,
ImmutableList.copyOf(hints),
@@ -1610,6 +1627,20 @@ public class RelBuilder {
childProject.getInput(), childProject.getProjects(), rowType);
stack.push(new Frame(newInput.attachHints(childProject.getHints()), frame.fields));
}
+ if (input instanceof Values && fieldNames != null) {
+ // Rename columns of child values if desired field names are given.
+ final Frame frame = stack.pop();
+ final Values values = (Values) frame.rel;
+ final RelDataTypeFactory.Builder typeBuilder =
+ getTypeFactory().builder();
+ Pair.forEach(fieldNameList, rowType.getFieldList(), (name, field) ->
+ typeBuilder.add(name, field.getType()));
+ final RelDataType newRowType = typeBuilder.build();
+ final RelNode newValues =
+ struct.valuesFactory.createValues(cluster, newRowType,
+ values.tuples);
+ stack.push(new Frame(newValues, frame.fields));
+ }
} else {
project(nodeList, rowType.getFieldNames(), force);
}
@@ -2033,12 +2064,26 @@ public class RelBuilder {
default:
throw new AssertionError("bad setOp " + kind);
}
- switch (n) {
- case 1:
+
+ if (n == 1) {
return push(inputs.get(0));
- default:
- return push(struct.setOpFactory.createSetOp(kind, inputs, all));
}
+
+ if (config.simplifyValues()
+ && kind == UNION
+ && inputs.stream().allMatch(r -> r instanceof Values)) {
+ RelDataType rowType = getTypeFactory()
+ .leastRestrictive(Util.transform(inputs, RelNode::getRowType));
+ final List<List<RexLiteral>> tuples = new ArrayList<>();
+ for (RelNode input : inputs) {
+ tuples.addAll(((Values) input).tuples);
+ }
+ final List<List<RexLiteral>> tuples2 =
+ all ? tuples : Util.distinctList(tuples);
+ return values(tuples2, rowType);
+ }
+
+ return push(struct.setOpFactory.createSetOp(kind, inputs, all));
}
/** Creates a {@link Union} of the two most recent
@@ -2057,7 +2102,7 @@ public class RelBuilder {
* @param n Number of inputs to the UNION operator
*/
public RelBuilder union(boolean all, int n) {
- return setOp(all, SqlKind.UNION, n);
+ return setOp(all, UNION, n);
}
/** Creates an {@link Intersect} of the two most
@@ -2602,7 +2647,7 @@ public class RelBuilder {
++changeCount;
}
}
- if (changeCount == 0) {
+ if (changeCount == 0 && tupleList instanceof ImmutableList) {
// don't make a copy if we don't have to
//noinspection unchecked
return (ImmutableList<ImmutableList<E>>) tupleList;
@@ -3557,6 +3602,15 @@ public class RelBuilder {
/** Sets {@link #simplifyLimit()}. */
Config withSimplifyLimit(boolean simplifyLimit);
+ /** Whether to simplify {@code Union(Values, Values)} or
+ * {@code Union(Project(Values))} to {@code Values}; default true. */
+ @ImmutableBeans.Property
+ @ImmutableBeans.BooleanDefault(true)
+ boolean simplifyValues();
+
+ /** Sets {@link #simplifyValues()}. */
+ Config withSimplifyValues(boolean simplifyValues);
+
/** Whether to create an Aggregate even if we know that the input is
* already unique; default false. */
@ImmutableBeans.Property
diff --git a/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterStructsTest.java b/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterStructsTest.java
index 7f9740c..34fcef1 100644
--- a/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterStructsTest.java
+++ b/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterStructsTest.java
@@ -41,6 +41,7 @@ import org.junit.jupiter.api.Test;
import java.util.Collection;
import java.util.Set;
+import java.util.function.UnaryOperator;
/**
* Tests for {@link RelToSqlConverter} on a schema that has nested structures of multiple
@@ -163,7 +164,7 @@ class RelToSqlConverterStructsTest {
private RelToSqlConverterTest.Sql sql(String sql) {
return new RelToSqlConverterTest.Sql(ROOT_SCHEMA, sql,
CalciteSqlDialect.DEFAULT, SqlParser.Config.DEFAULT,
- RelToSqlConverterTest.DEFAULT_REL_CONFIG, null, ImmutableList.of());
+ UnaryOperator.identity(), null, ImmutableList.of());
}
@Test void testNestedSchemaSelectStar() {
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 7f960a3..b9f967d 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
@@ -99,20 +99,12 @@ import static org.junit.jupiter.api.Assertions.assertTrue;
* Tests for {@link RelToSqlConverter}.
*/
class RelToSqlConverterTest {
- static final SqlToRelConverter.Config DEFAULT_REL_CONFIG =
- SqlToRelConverter.config()
- .withTrimUnusedFields(false);
-
- static final SqlToRelConverter.Config NO_EXPAND_CONFIG =
- SqlToRelConverter.config()
- .withTrimUnusedFields(false)
- .withExpand(false);
/** Initiates a test case with a given SQL query. */
private Sql sql(String sql) {
return new Sql(CalciteAssert.SchemaSpec.JDBC_FOODMART, sql,
CalciteSqlDialect.DEFAULT, SqlParser.Config.DEFAULT,
- DEFAULT_REL_CONFIG, null, ImmutableList.of());
+ UnaryOperator.identity(), null, ImmutableList.of());
}
/** Initiates a test case with a given {@link RelNode} supplier. */
@@ -2553,12 +2545,10 @@ class RelToSqlConverterTest {
+ " (SELECT 0 AS g) AS v\n"
+ "GROUP BY v.g";
final String expected = "SELECT"
- + " CASE WHEN \"t1\".\"G\" IN (0, 1) THEN 0 ELSE 1 END\n"
- + "FROM (SELECT *\n"
- + "FROM \"foodmart\".\"customer\") AS \"t\",\n"
- + "(SELECT 0 AS \"G\"\n"
- + "FROM (VALUES (0)) AS \"t\" (\"ZERO\")) AS \"t1\"\n"
- + "GROUP BY \"t1\".\"G\"";
+ + " CASE WHEN \"t0\".\"G\" IN (0, 1) THEN 0 ELSE 1 END\n"
+ + "FROM (SELECT *\nFROM \"foodmart\".\"customer\") AS \"t\",\n"
+ + "(VALUES (0)) AS \"t0\" (\"G\")\n"
+ + "GROUP BY \"t0\".\"G\"";
sql(query).ok(expected);
}
@@ -3490,7 +3480,7 @@ class RelToSqlConverterTest {
+ "WHERE EXISTS (SELECT COUNT(*)\n"
+ "FROM \"foodmart\".\"sales_fact_1997\"\n"
+ "WHERE \"product_id\" = \"product\".\"product_id\")";
- sql(query).config(NO_EXPAND_CONFIG).ok(expected);
+ sql(query).withConfig(c -> c.withExpand(false)).ok(expected);
}
@Test void testNotExistsWithExpand() {
@@ -3503,7 +3493,7 @@ class RelToSqlConverterTest {
+ "WHERE NOT EXISTS (SELECT COUNT(*)\n"
+ "FROM \"foodmart\".\"sales_fact_1997\"\n"
+ "WHERE \"product_id\" = \"product\".\"product_id\")";
- sql(query).config(NO_EXPAND_CONFIG).ok(expected);
+ sql(query).withConfig(c -> c.withExpand(false)).ok(expected);
}
@Test void testSubQueryInWithExpand() {
@@ -3516,7 +3506,7 @@ class RelToSqlConverterTest {
+ "WHERE \"product_id\" IN (SELECT \"product_id\"\n"
+ "FROM \"foodmart\".\"sales_fact_1997\"\n"
+ "WHERE \"product_id\" = \"product\".\"product_id\")";
- sql(query).config(NO_EXPAND_CONFIG).ok(expected);
+ sql(query).withConfig(c -> c.withExpand(false)).ok(expected);
}
@Test void testSubQueryInWithExpand2() {
@@ -3525,7 +3515,7 @@ class RelToSqlConverterTest {
String expected = "SELECT \"product_name\"\n"
+ "FROM \"foodmart\".\"product\"\n"
+ "WHERE \"product_id\" = 1 OR \"product_id\" = 2";
- sql(query).config(NO_EXPAND_CONFIG).ok(expected);
+ sql(query).withConfig(c -> c.withExpand(false)).ok(expected);
}
@Test void testSubQueryNotInWithExpand() {
@@ -3538,7 +3528,7 @@ class RelToSqlConverterTest {
+ "WHERE \"product_id\" NOT IN (SELECT \"product_id\"\n"
+ "FROM \"foodmart\".\"sales_fact_1997\"\n"
+ "WHERE \"product_id\" = \"product\".\"product_id\")";
- sql(query).config(NO_EXPAND_CONFIG).ok(expected);
+ sql(query).withConfig(c -> c.withExpand(false)).ok(expected);
}
@Test void testLike() {
@@ -5215,8 +5205,8 @@ class RelToSqlConverterTest {
@Test void testSelectNullWithCast() {
final String query = "SELECT CAST(NULL AS INT)";
- final String expected = "SELECT CAST(NULL AS INTEGER)\n"
- + "FROM (VALUES (0)) AS \"t\" (\"ZERO\")";
+ final String expected = "SELECT *\n"
+ + "FROM (VALUES (NULL)) AS \"t\" (\"EXPR$0\")";
sql(query).ok(expected);
// validate
sql(expected).exec();
@@ -5224,8 +5214,8 @@ class RelToSqlConverterTest {
@Test void testSelectNullWithCount() {
final String query = "SELECT COUNT(CAST(NULL AS INT))";
- final String expected = "SELECT COUNT(CAST(NULL AS INTEGER))\n"
- + "FROM (VALUES (0)) AS \"t\" (\"ZERO\")";
+ final String expected = "SELECT COUNT(\"$f0\")\n"
+ + "FROM (VALUES (NULL)) AS \"t\" (\"$f0\")";
sql(query).ok(expected);
// validate
sql(expected).exec();
@@ -5235,9 +5225,9 @@ class RelToSqlConverterTest {
final String query = "SELECT COUNT(CAST(NULL AS INT))\n"
+ "FROM (VALUES (0))AS \"t\"\n"
+ "GROUP BY CAST(NULL AS VARCHAR CHARACTER SET \"ISO-8859-1\")";
- final String expected = "SELECT COUNT(CAST(NULL AS INTEGER))\n"
- + "FROM (VALUES (0)) AS \"t\" (\"EXPR$0\")\n"
- + "GROUP BY CAST(NULL AS VARCHAR CHARACTER SET \"ISO-8859-1\")";
+ final String expected = "SELECT COUNT(\"$f1\")\n"
+ + "FROM (VALUES (NULL, NULL)) AS \"t\" (\"$f0\", \"$f1\")\n"
+ + "GROUP BY \"$f0\"";
sql(query).ok(expected);
// validate
sql(expected).exec();
@@ -5262,12 +5252,16 @@ class RelToSqlConverterTest {
final String expected = "INSERT INTO \"foodmart\".\"account\" ("
+ "\"account_id\", \"account_parent\", \"account_description\", "
+ "\"account_type\", \"account_rollup\", \"Custom_Members\")\n"
- + "(SELECT 1 AS \"account_id\", CAST(NULL AS INTEGER) AS \"account_parent\","
- + " CAST(NULL AS VARCHAR(30) CHARACTER SET "
- + "\"ISO-8859-1\") AS \"account_description\", '123' AS \"account_type\", "
- + "'123' AS \"account_rollup\", CAST(NULL AS VARCHAR"
- + "(255) CHARACTER SET \"ISO-8859-1\") AS \"Custom_Members\"\n"
- + "FROM (VALUES (0)) AS \"t\" (\"ZERO\"))";
+ + "(SELECT \"EXPR$0\" AS \"account_id\","
+ + " \"EXPR$1\" AS \"account_parent\","
+ + " CAST(NULL AS VARCHAR(30) CHARACTER SET \"ISO-8859-1\") "
+ + "AS \"account_description\","
+ + " \"EXPR$2\" AS \"account_type\","
+ + " \"EXPR$3\" AS \"account_rollup\","
+ + " CAST(NULL AS VARCHAR(255) CHARACTER SET \"ISO-8859-1\") "
+ + "AS \"Custom_Members\"\n"
+ + "FROM (VALUES (1, NULL, '123', '123')) "
+ + "AS \"t\" (\"EXPR$0\", \"EXPR$1\", \"EXPR$2\", \"EXPR$3\"))";
sql(query).ok(expected);
// validate
sql(expected).exec();
@@ -5366,24 +5360,40 @@ class RelToSqlConverterTest {
@Test void testRowValueExpression() {
String sql = "insert into \"DEPT\"\n"
- + "values ROW(1,'Fred', 'San Francisco'), ROW(2, 'Eric', 'Washington')";
+ + "values ROW(1,'Fred', 'San Francisco'),\n"
+ + " ROW(2, 'Eric', 'Washington')";
final String expectedDefault = "INSERT INTO \"SCOTT\".\"DEPT\""
+ " (\"DEPTNO\", \"DNAME\", \"LOC\")\n"
+ + "VALUES (1, 'Fred', 'San Francisco'),\n"
+ + "(2, 'Eric', 'Washington')";
+ final String expectedDefaultX = "INSERT INTO \"SCOTT\".\"DEPT\""
+ + " (\"DEPTNO\", \"DNAME\", \"LOC\")\n"
+ "SELECT 1, 'Fred', 'San Francisco'\n"
+ "FROM (VALUES (0)) AS \"t\" (\"ZERO\")\n"
+ "UNION ALL\n"
+ "SELECT 2, 'Eric', 'Washington'\n"
+ "FROM (VALUES (0)) AS \"t\" (\"ZERO\")";
final String expectedHive = "INSERT INTO SCOTT.DEPT (DEPTNO, DNAME, LOC)\n"
+ + "VALUES (1, 'Fred', 'San Francisco'),\n"
+ + "(2, 'Eric', 'Washington')";
+ final String expectedHiveX = "INSERT INTO SCOTT.DEPT (DEPTNO, DNAME, LOC)\n"
+ "SELECT 1, 'Fred', 'San Francisco'\n"
+ "UNION ALL\n"
+ "SELECT 2, 'Eric', 'Washington'";
final String expectedMysql = "INSERT INTO `SCOTT`.`DEPT`"
+ + " (`DEPTNO`, `DNAME`, `LOC`)\n"
+ + "VALUES (1, 'Fred', 'San Francisco'),\n"
+ + "(2, 'Eric', 'Washington')";
+ final String expectedMysqlX = "INSERT INTO `SCOTT`.`DEPT`"
+ " (`DEPTNO`, `DNAME`, `LOC`)\nSELECT 1, 'Fred', 'San Francisco'\n"
+ "UNION ALL\n"
+ "SELECT 2, 'Eric', 'Washington'";
final String expectedOracle = "INSERT INTO \"SCOTT\".\"DEPT\""
+ " (\"DEPTNO\", \"DNAME\", \"LOC\")\n"
+ + "VALUES (1, 'Fred', 'San Francisco'),\n"
+ + "(2, 'Eric', 'Washington')";
+ final String expectedOracleX = "INSERT INTO \"SCOTT\".\"DEPT\""
+ + " (\"DEPTNO\", \"DNAME\", \"LOC\")\n"
+ "SELECT 1, 'Fred', 'San Francisco'\n"
+ "FROM \"DUAL\"\n"
+ "UNION ALL\n"
@@ -5391,6 +5401,10 @@ class RelToSqlConverterTest {
+ "FROM \"DUAL\"";
final String expectedMssql = "INSERT INTO [SCOTT].[DEPT]"
+ " ([DEPTNO], [DNAME], [LOC])\n"
+ + "VALUES (1, 'Fred', 'San Francisco'),\n"
+ + "(2, 'Eric', 'Washington')";
+ final String expectedMssqlX = "INSERT INTO [SCOTT].[DEPT]"
+ + " ([DEPTNO], [DNAME], [LOC])\n"
+ "SELECT 1, 'Fred', 'San Francisco'\n"
+ "FROM (VALUES (0)) AS [t] ([ZERO])\n"
+ "UNION ALL\n"
@@ -5398,6 +5412,10 @@ class RelToSqlConverterTest {
+ "FROM (VALUES (0)) AS [t] ([ZERO])";
final String expectedCalcite = "INSERT INTO \"SCOTT\".\"DEPT\""
+ " (\"DEPTNO\", \"DNAME\", \"LOC\")\n"
+ + "VALUES (1, 'Fred', 'San Francisco'),\n"
+ + "(2, 'Eric', 'Washington')";
+ final String expectedCalciteX = "INSERT INTO \"SCOTT\".\"DEPT\""
+ + " (\"DEPTNO\", \"DNAME\", \"LOC\")\n"
+ "SELECT 1, 'Fred', 'San Francisco'\n"
+ "FROM (VALUES (0)) AS \"t\" (\"ZERO\")\n"
+ "UNION ALL\n"
@@ -5410,7 +5428,16 @@ class RelToSqlConverterTest {
.withMysql().ok(expectedMysql)
.withOracle().ok(expectedOracle)
.withMssql().ok(expectedMssql)
- .withCalcite().ok(expectedCalcite);
+ .withCalcite().ok(expectedCalcite)
+ .withConfig(c ->
+ c.withRelBuilderConfigTransform(b ->
+ b.withSimplifyValues(false)))
+ .withCalcite().ok(expectedDefaultX)
+ .withHive().ok(expectedHiveX)
+ .withMysql().ok(expectedMysqlX)
+ .withOracle().ok(expectedOracleX)
+ .withMssql().ok(expectedMssqlX)
+ .withCalcite().ok(expectedCalciteX);
}
@Test void testInsertValuesWithDynamicParams() {
@@ -5518,10 +5545,11 @@ class RelToSqlConverterTest {
private final Function<RelBuilder, RelNode> relFn;
private final List<Function<RelNode, RelNode>> transforms;
private final SqlParser.Config parserConfig;
- private final SqlToRelConverter.Config config;
+ private final UnaryOperator<SqlToRelConverter.Config> config;
Sql(CalciteAssert.SchemaSpec schemaSpec, String sql, SqlDialect dialect,
- SqlParser.Config parserConfig, SqlToRelConverter.Config config,
+ SqlParser.Config parserConfig,
+ UnaryOperator<SqlToRelConverter.Config> config,
Function<RelBuilder, RelNode> relFn,
List<Function<RelNode, RelNode>> transforms) {
final SchemaPlus rootSchema = Frameworks.createRootSchema(true);
@@ -5535,7 +5563,8 @@ class RelToSqlConverterTest {
}
Sql(SchemaPlus schema, String sql, SqlDialect dialect,
- SqlParser.Config parserConfig, SqlToRelConverter.Config config,
+ SqlParser.Config parserConfig,
+ UnaryOperator<SqlToRelConverter.Config> config,
Function<RelBuilder, RelNode> relFn,
List<Function<RelNode, RelNode>> transforms) {
this.schema = schema;
@@ -5680,7 +5709,7 @@ class RelToSqlConverterTest {
transforms);
}
- Sql config(SqlToRelConverter.Config config) {
+ Sql withConfig(UnaryOperator<SqlToRelConverter.Config> config) {
return new Sql(schema, sql, dialect, parserConfig, config, relFn,
transforms);
}
@@ -5721,6 +5750,8 @@ class RelToSqlConverterTest {
if (relFn != null) {
rel = relFn.apply(relBuilder());
} else {
+ final SqlToRelConverter.Config config = this.config.apply(SqlToRelConverter.config()
+ .withTrimUnusedFields(false));
final Planner planner =
getPlanner(null, parserConfig, schema, config);
SqlNode parse = planner.parse(sql);
diff --git a/core/src/test/java/org/apache/calcite/test/JdbcAdapterTest.java b/core/src/test/java/org/apache/calcite/test/JdbcAdapterTest.java
index 1080e8d..e2dc881 100644
--- a/core/src/test/java/org/apache/calcite/test/JdbcAdapterTest.java
+++ b/core/src/test/java/org/apache/calcite/test/JdbcAdapterTest.java
@@ -801,18 +801,15 @@ class JdbcAdapterTest {
+ "VALUES (666, 666, TIMESTAMP '1997-01-01 00:00:00',"
+ " 666, '666', 666, 666)";
final String explain = "PLAN=JdbcToEnumerableConverter\n"
- + " JdbcTableModify(table=[[foodmart, expense_fact]], operation=[INSERT], flattened=[false])\n"
- + " JdbcProject(store_id=[666], account_id=[666], exp_date=[1997-01-01 00:00:00], "
- + "time_id=[666], category_id=['666'], currency_id=[666], amount=[666:DECIMAL(10, 4)])\n"
- + " JdbcValues(tuples=[[{ 0 }]])\n\n";
+ + " JdbcTableModify(table=[[foodmart, expense_fact]], "
+ + "operation=[INSERT], flattened=[false])\n"
+ + " JdbcValues(tuples=[[{ 666, 666, 1997-01-01 00:00:00, 666, "
+ + "'666', 666, 666 }]])\n\n";
final String jdbcSql = "INSERT INTO \"foodmart\".\"expense_fact\" (\"store_id\", "
+ "\"account_id\", \"exp_date\", \"time_id\", \"category_id\", \"currency_id\", "
+ "\"amount\")\n"
- + "(SELECT 666 AS \"store_id\", 666 AS \"account_id\", "
- + "TIMESTAMP '1997-01-01 00:00:00' AS \"exp_date\", 666 AS \"time_id\", "
- + "'666' AS \"category_id\", 666 AS \"currency_id\", "
- + "666 AS \"amount\"\n"
- + "FROM (VALUES (0)) AS \"t\" (\"ZERO\"))";
+ + "VALUES (666, 666, TIMESTAMP '1997-01-01 00:00:00', 666, '666', "
+ + "666, 666)";
final AssertThat that =
CalciteAssert.model(JdbcTest.FOODMART_MODEL)
.enable(CalciteAssert.DB == DatabaseInstance.HSQLDB
@@ -837,21 +834,17 @@ class JdbcAdapterTest {
+ " (666, 777, TIMESTAMP '1997-01-01 00:00:00',"
+ " 666, '666', 666, 666)";
final String explain = "PLAN=JdbcToEnumerableConverter\n"
- + " JdbcTableModify(table=[[foodmart, expense_fact]], operation=[INSERT], flattened=[false])\n"
- + " JdbcUnion(all=[true])\n"
- + " JdbcProject(EXPR$0=[666], EXPR$1=[666], EXPR$2=[1997-01-01 00:00:00], EXPR$3=[666], EXPR$4=['666'], EXPR$5=[666], EXPR$6=[666:DECIMAL(10, 4)])\n"
- + " JdbcValues(tuples=[[{ 0 }]])\n"
- + " JdbcProject(EXPR$0=[666], EXPR$1=[777], EXPR$2=[1997-01-01 00:00:00], EXPR$3=[666], EXPR$4=['666'], EXPR$5=[666], EXPR$6=[666:DECIMAL(10, 4)])\n"
- + " JdbcValues(tuples=[[{ 0 }]])\n\n";
- final String jdbcSql = "INSERT INTO \"foodmart\".\"expense_fact\" (\"store_id\", "
- + "\"account_id\", \"exp_date\", \"time_id\", \"category_id\", \"currency_id\","
- + " \"amount\")\n"
- + "SELECT 666, 666, TIMESTAMP '1997-01-01 00:00:00', 666, '666', 666, 666\n"
- + "FROM (VALUES (0)) AS \"t\" (\"ZERO\")\n"
- + "UNION ALL\n"
- + "SELECT 666, 777, "
- + "TIMESTAMP '1997-01-01 00:00:00', 666, '666', 666, 666\n"
- + "FROM (VALUES (0)) AS \"t\" (\"ZERO\")";
+ + " JdbcTableModify(table=[[foodmart, expense_fact]], "
+ + "operation=[INSERT], flattened=[false])\n"
+ + " JdbcValues(tuples=[["
+ + "{ 666, 666, 1997-01-01 00:00:00, 666, '666', 666, 666 }, "
+ + "{ 666, 777, 1997-01-01 00:00:00, 666, '666', 666, 666 }]])\n\n";
+ final String jdbcSql = "INSERT INTO \"foodmart\".\"expense_fact\""
+ + " (\"store_id\", \"account_id\", \"exp_date\", \"time_id\", "
+ + "\"category_id\", \"currency_id\", \"amount\")\n"
+ + "VALUES "
+ + "(666, 666, TIMESTAMP '1997-01-01 00:00:00', 666, '666', 666, 666),\n"
+ + "(666, 777, TIMESTAMP '1997-01-01 00:00:00', 666, '666', 666, 666)";
final AssertThat that =
CalciteAssert.model(JdbcTest.FOODMART_MODEL)
.enable(CalciteAssert.DB == DatabaseInstance.HSQLDB
diff --git a/core/src/test/java/org/apache/calcite/test/JdbcTest.java b/core/src/test/java/org/apache/calcite/test/JdbcTest.java
index d01a09e..a7f056c 100644
--- a/core/src/test/java/org/apache/calcite/test/JdbcTest.java
+++ b/core/src/test/java/org/apache/calcite/test/JdbcTest.java
@@ -283,11 +283,18 @@ public class JdbcTest {
+ "insert into \"adhoc\".V\n"
+ "values ('Fred', 56, 123.4)");
assertThat(resultSet.next(), is(true));
- assertThat(resultSet.getString(1),
- isLinux(
- "EnumerableTableModify(table=[[adhoc, MUTABLE_EMPLOYEES]], operation=[INSERT], flattened=[false])\n"
- + " EnumerableCalc(expr#0=[{inputs}], expr#1=[56], expr#2=[10], expr#3=['Fred':JavaType(class java.lang.String)], expr#4=[CAST($t3):JavaType(class java.lang.String)], expr#5=[123.4:JavaType(float)], expr#6=[null:JavaType(class java.lang.Integer)], empid=[$t1], deptno=[$t2], name=[$t4], salary=[$t5], commission=[$t6])\n"
- + " EnumerableValues(tuples=[[{ 0 }]])\n"));
+ final String expected = ""
+ + "EnumerableTableModify(table=[[adhoc, MUTABLE_EMPLOYEES]], "
+ + "operation=[INSERT], flattened=[false])\n"
+ + " EnumerableCalc(expr#0..2=[{inputs}], "
+ + "expr#3=[CAST($t1):JavaType(int) NOT NULL], expr#4=[10], "
+ + "expr#5=[CAST($t0):JavaType(class java.lang.String)], "
+ + "expr#6=[CAST($t2):JavaType(float) NOT NULL], "
+ + "expr#7=[null:JavaType(class java.lang.Integer)], "
+ + "empid=[$t3], deptno=[$t4], name=[$t5], salary=[$t6], "
+ + "commission=[$t7])\n"
+ + " EnumerableValues(tuples=[[{ 'Fred', 56, 123.4 }]])\n";
+ assertThat(resultSet.getString(1), isLinux(expected));
// With named columns
resultSet =
diff --git a/core/src/test/java/org/apache/calcite/test/RelBuilderTest.java b/core/src/test/java/org/apache/calcite/test/RelBuilderTest.java
index 72823e9..836a826 100644
--- a/core/src/test/java/org/apache/calcite/test/RelBuilderTest.java
+++ b/core/src/test/java/org/apache/calcite/test/RelBuilderTest.java
@@ -87,6 +87,7 @@ import java.util.Locale;
import java.util.NoSuchElementException;
import java.util.TreeSet;
import java.util.concurrent.atomic.AtomicInteger;
+import java.util.function.BiFunction;
import java.util.function.Function;
import java.util.function.UnaryOperator;
@@ -798,7 +799,7 @@ public class RelBuilderTest {
}
private void project1(int value, SqlTypeName sqlTypeName, String message, String expected) {
- final RelBuilder builder = RelBuilder.create(config().build());
+ final RelBuilder builder = createBuilder(c -> c.withSimplifyValues(false));
RexBuilder rex = builder.getRexBuilder();
RelNode actual =
builder.values(new String[]{"x"}, 42)
@@ -2893,6 +2894,64 @@ public class RelBuilderTest {
assertThat(root.getRowType().getFullTypeString(), is(expectedType));
}
+ @Test void testValuesRename() {
+ final Function<RelBuilder, RelNode> f = b ->
+ b.values(new String[] {"a", "b"}, 1, true, 2, false)
+ .rename(Arrays.asList("x", "y"))
+ .build();
+ final String expected =
+ "LogicalValues(tuples=[[{ 1, true }, { 2, false }]])\n";
+ final String expectedRowType = "RecordType(INTEGER x, BOOLEAN y)";
+ assertThat(f.apply(createBuilder()), hasTree(expected));
+ assertThat(f.apply(createBuilder()).getRowType().toString(),
+ is(expectedRowType));
+ }
+
+ /** Tests that {@code Union(Project(Values), ... Project(Values))} is
+ * simplified to {@code Values}. It occurs commonly: people write
+ * {@code SELECT 1 UNION SELECT 2}. */
+ @Test void testUnionProjectValues() {
+ // Equivalent SQL:
+ // SELECT 'a', 1
+ // UNION ALL
+ // SELECT 'b', 2
+ final BiFunction<RelBuilder, Boolean, RelNode> f = (b, all) ->
+ b.values(new String[] {"zero"}, 0)
+ .project(b.literal("a"), b.literal(1))
+ .values(new String[] {"zero"}, 0)
+ .project(b.literal("b"), b.literal(2))
+ .union(all, 2)
+ .build();
+ final String expected =
+ "LogicalValues(tuples=[[{ 'a', 1 }, { 'b', 2 }]])\n";
+
+ // Same effect with and without ALL because tuples are distinct
+ assertThat(f.apply(createBuilder(), true), hasTree(expected));
+ assertThat(f.apply(createBuilder(), false), hasTree(expected));
+ }
+
+ @Test void testUnionProjectValues2() {
+ // Equivalent SQL:
+ // SELECT 'a', 1 FROM (VALUES (0), (0))
+ // UNION ALL
+ // SELECT 'b', 2
+ final BiFunction<RelBuilder, Boolean, RelNode> f = (b, all) ->
+ b.values(new String[] {"zero"}, 0)
+ .project(b.literal("a"), b.literal(1))
+ .values(new String[] {"zero"}, 0, 0)
+ .project(b.literal("b"), b.literal(2))
+ .union(all, 2)
+ .build();
+
+ // Different effect with and without ALL because tuples are not distinct.
+ final String expectedAll =
+ "LogicalValues(tuples=[[{ 'a', 1 }, { 'b', 2 }, { 'b', 2 }]])\n";
+ final String expectedDistinct =
+ "LogicalValues(tuples=[[{ 'a', 1 }, { 'b', 2 }]])\n";
+ assertThat(f.apply(createBuilder(), true), hasTree(expectedAll));
+ assertThat(f.apply(createBuilder(), false), hasTree(expectedDistinct));
+ }
+
@Test void testSort() {
// Equivalent SQL:
// SELECT *
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 28fd2ed..fc01691 100644
--- a/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
+++ b/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
@@ -2484,6 +2484,7 @@ class RelOptRulesTest extends RelOptTestBase {
final String sql = "select p1 is not distinct from p0\n"
+ "from (values (2, cast(null as integer))) as t(p0, p1)";
sql(sql)
+ .withRelBuilderConfig(b -> b.withSimplifyValues(false))
.withRule(CoreRules.PROJECT_REDUCE_EXPRESSIONS,
CoreRules.FILTER_REDUCE_EXPRESSIONS,
CoreRules.JOIN_REDUCE_EXPRESSIONS)
@@ -2667,7 +2668,9 @@ class RelOptRulesTest extends RelOptTestBase {
+ " select 'foreign table' from (values (true))\n"
+ " )\n"
+ ") where u = 'TABLE'";
- sql(sql).with(program).check();
+ sql(sql)
+ .withRelBuilderConfig(c -> c.withSimplifyValues(false))
+ .with(program).check();
}
@Test void testRemoveSemiJoin() {
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 e9dd972..e53e2f3 100644
--- a/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
+++ b/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
@@ -2703,8 +2703,8 @@ LogicalValues(tuples=[[{ 11, 1, 10 }, { 23, 3, 20 }]])
<Resource name="planBefore">
<![CDATA[
LogicalTableModify(table=[[CATALOG, SALES, EMPNULLABLES]], operation=[INSERT], flattened=[false])
- LogicalProject(EMPNO=[0], ENAME=['null'], JOB=[null:VARCHAR(10)], MGR=[null:INTEGER], HIREDATE=[null:TIMESTAMP(0)], SAL=[null:INTEGER], COMM=[null:INTEGER], DEPTNO=[null:INTEGER], SLACKER=[null:BOOLEAN])
- LogicalValues(tuples=[[{ 0 }]])
+ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[null:INTEGER], HIREDATE=[null:TIMESTAMP(0)], SAL=[null:INTEGER], COMM=[null:INTEGER], DEPTNO=[null:INTEGER], SLACKER=[null:BOOLEAN])
+ LogicalValues(tuples=[[{ 0, 'null', null }]])
]]>
</Resource>
<Resource name="planAfter">
@@ -7760,11 +7760,7 @@ LogicalProject(DEPTNO=[$0], NAME=[$1], NAME0=[$3], EXPR$1=[$4])
LogicalProject(DEPTNO=[$0], NAME=[$1], NAME0=[CAST($1):INTEGER NOT NULL])
LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
LogicalProject(NAME=[$0], EXPR$1=[IS NOT NULL($1)])
- LogicalUnion(all=[true])
- LogicalProject(EXPR$0=[2], EXPR$1=[null:INTEGER])
- LogicalValues(tuples=[[{ 0 }]])
- LogicalProject(EXPR$0=[2], EXPR$1=[1])
- LogicalValues(tuples=[[{ 0 }]])
+ LogicalValues(tuples=[[{ 2, null }, { 2, 1 }]])
]]>
</Resource>
<Resource name="planAfter">
diff --git a/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml b/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
index 430edab..135a8fe 100644
--- a/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
+++ b/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
@@ -55,8 +55,7 @@ LogicalProject(COLLECT_SKILL=[$1], COUNT_SKILL=[$2], COUNT_STAR=[$2], APPROX_COU
<TestCase name="testCase">
<Resource name="plan">
<![CDATA[
-LogicalProject(EXPR$0=[1])
- LogicalValues(tuples=[[{ 0 }]])
+LogicalValues(tuples=[[{ 1 }]])
]]>
</Resource>
<Resource name="sql">
@@ -558,8 +557,7 @@ LogicalProject(DEPTNO=[$0], ENAME=[$1])
FROM emp
WHERE deptno = t1.deptno)
ORDER BY ename DESC
- LIMIT 3
- )]]>
+ LIMIT 3]]>
</Resource>
<Resource name="plan">
<![CDATA[
@@ -927,8 +925,7 @@ LogicalUnion(all=[true])
LogicalProject(EXPR$0=[34])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
LogicalUnion(all=[true])
- LogicalProject(EXPR$0=[30])
- LogicalValues(tuples=[[{ 0 }]])
+ LogicalValues(tuples=[[{ 30 }]])
LogicalProject(EXPR$0=[+(45, 10)])
LogicalValues(tuples=[[{ 0 }]])
]]>
@@ -967,11 +964,7 @@ LogicalProject(DEPTNO=[$7])
<Resource name="plan">
<![CDATA[
LogicalProject(EXPR$0=[AND(OR(IS NOT NULL($0), IS NOT NULL($1)), IS NOT TRUE(=($0, $1)))])
- LogicalUnion(all=[true])
- LogicalProject(EXPR$0=[null:INTEGER], EXPR$1=[1])
- LogicalValues(tuples=[[{ 0 }]])
- LogicalProject(EXPR$0=[2], EXPR$1=[null:INTEGER])
- LogicalValues(tuples=[[{ 0 }]])
+ LogicalValues(tuples=[[{ null, 1 }, { 2, null }]])
]]>
</Resource>
<Resource name="sql">
@@ -984,11 +977,7 @@ from (values (cast(null as int), 1),
<Resource name="plan">
<![CDATA[
LogicalProject(EXPR$0=[OR(AND(IS NULL($0), IS NULL($1)), IS TRUE(=($0, $1)))])
- LogicalUnion(all=[true])
- LogicalProject(EXPR$0=[null:INTEGER], EXPR$1=[1])
- LogicalValues(tuples=[[{ 0 }]])
- LogicalProject(EXPR$0=[2], EXPR$1=[null:INTEGER])
- LogicalValues(tuples=[[{ 0 }]])
+ LogicalValues(tuples=[[{ null, 1 }, { 2, null }]])
]]>
</Resource>
<Resource name="sql">
@@ -3101,8 +3090,7 @@ group by rollup(a, b)]]>
<Resource name="plan">
<![CDATA[
LogicalAggregate(group=[{0, 1}], groups=[[{0, 1}, {0}, {}]], C=[COUNT()])
- LogicalProject(A=[null:INTEGER], B=[2])
- LogicalValues(tuples=[[{ 0 }]])
+ LogicalValues(tuples=[[{ null, 2 }]])
]]>
</Resource>
</TestCase>
@@ -7273,7 +7261,6 @@ LogicalProject(EMPNO=[$0])
LogicalProject(EMPNO=[$0])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
]]>
-
</Resource>
</TestCase>
<TestCase name="testJoinExpandAndDecorrelation">
@@ -7303,6 +7290,15 @@ LogicalAggregate(group=[{}], EXPR$0=[AVG($0)])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
]]>
</Resource>
+ <Resource name="sql">
+ <![CDATA[SELECT emp.deptno, emp.sal
+FROM dept
+JOIN emp ON emp.deptno = dept.deptno AND emp.sal < (
+ SELECT AVG(emp.sal)
+ FROM emp
+ WHERE emp.deptno = dept.deptno
+)]]>
+ </Resource>
</TestCase>
<TestCase name="testImplicitJoinExpandAndDecorrelation">
<Resource name="plan_extended">
@@ -7332,6 +7328,14 @@ LogicalAggregate(group=[{}], EXPR$0=[AVG($0)])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
]]>
</Resource>
+ <Resource name="sql">
+ <![CDATA[SELECT emp.deptno, emp.sal
+FROM dept, emp WHERE emp.deptno = dept.deptno AND emp.sal < (
+ SELECT AVG(emp.sal)
+ FROM emp
+ WHERE emp.deptno = dept.deptno
+)]]>
+ </Resource>
</TestCase>
<TestCase name="testCompositeOfCountRange">
<Resource name="sql">
diff --git a/core/src/test/resources/org/apache/calcite/test/TypeCoercionConverterTest.xml b/core/src/test/resources/org/apache/calcite/test/TypeCoercionConverterTest.xml
index 771c740..2f06f7c 100644
--- a/core/src/test/resources/org/apache/calcite/test/TypeCoercionConverterTest.xml
+++ b/core/src/test/resources/org/apache/calcite/test/TypeCoercionConverterTest.xml
@@ -16,21 +16,20 @@
~ limitations under the License.
-->
<Root>
- <TestCase name="testInOperation">
- <Resource name="sql">
- <![CDATA[select
+ <TestCase name="testInOperation">
+ <Resource name="sql">
+ <![CDATA[select
1 in ('1', '2', '3') as f0,
(1, 2) in (('1', '2')) as f1,
(1, 2) in (('1', '2'), ('3', '4')) as f2
from (values (true, true, true))]]>
- </Resource>
- <Resource name="plan">
- <![CDATA[
-LogicalProject(F0=[true], F1=[true], F2=[true])
- LogicalValues(tuples=[[{ true, true, true }]])
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalValues(tuples=[[{ true, true, true }]])
]]>
- </Resource>
- </TestCase>
+ </Resource>
+ </TestCase>
<TestCase name="testNotInOperation">
<Resource name="sql">
<![CDATA[select
@@ -41,27 +40,26 @@ from (values (false, false, false))]]>
</Resource>
<Resource name="plan">
<![CDATA[
-LogicalProject(F0=[false], F1=[false], F2=[false])
- LogicalValues(tuples=[[{ false, false, false }]])
+LogicalValues(tuples=[[{ false, false, false }]])
]]>
</Resource>
</TestCase>
<TestCase name="testInDateTimestamp">
- <Resource name="sql">
- <![CDATA[select (t1_timestamp, t1_date)
+ <Resource name="sql">
+ <![CDATA[select (t1_timestamp, t1_date)
in ((DATE '2020-04-16', TIMESTAMP '2020-04-16 11:40:53'))
from t1]]>
- </Resource>
- <Resource name="plan">
- <![CDATA[
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
LogicalProject(EXPR$0=[AND(=($7, 2020-04-16 00:00:00), =(CAST($8):TIMESTAMP(0) NOT NULL, 2020-04-16 11:40:53))])
LogicalTableScan(table=[[CATALOG, SALES, T1]])
]]>
- </Resource>
- </TestCase>
- <TestCase name="testBooleanEquality">
- <Resource name="sql">
- <![CDATA[select
+ </Resource>
+ </TestCase>
+ <TestCase name="testBooleanEquality">
+ <Resource name="sql">
+ <![CDATA[select
1=true as f0,
1.0=true as f1,
0.0=true=true as f2,
@@ -69,17 +67,17 @@ LogicalProject(EXPR$0=[AND(=($7, 2020-04-16 00:00:00), =(CAST($8):TIMESTAMP(0) N
t1_smallint=t1_boolean as f4,
10000000000=true as f5
from t1]]>
- </Resource>
- <Resource name="plan">
- <![CDATA[
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
LogicalProject(F0=[true], F1=[true], F2=[false], F3=[NOT($10)], F4=[=($1, CASE($10, 1:SMALLINT, 0:SMALLINT))], F5=[false])
LogicalTableScan(table=[[CATALOG, SALES, T1]])
]]>
- </Resource>
- </TestCase>
- <TestCase name="testBinaryComparison">
- <Resource name="sql">
- <![CDATA["select
+ </Resource>
+ </TestCase>
+ <TestCase name="testBinaryComparison">
+ <Resource name="sql">
+ <![CDATA[select
1<'1' as f0,
1<='1' as f1,
1>'1' as f2,
@@ -90,48 +88,45 @@ t1_date > t1_timestamp as f5,
'2019-09-23' between t1_date and t1_timestamp as f7,
cast('2019-09-23' as date) between t1_date and t1_timestamp as f8
from t1]]>
- </Resource>
- <Resource name="plan">
- <![CDATA[
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
LogicalProject(F0=[false], F1=[true], F2=[false], F3=[true], F4=[true], F5=[>(CAST($8):TIMESTAMP(0) NOT NULL, $7)], F6=[true], F7=[AND(>=(2019-09-23 00:00:00, CAST($8):TIMESTAMP(0) NOT NULL), <=(2019-09-23 00:00:00, $7))], F8=[AND(>=(2019-09-23 00:00:00, CAST($8):TIMESTAMP(0) NOT NULL), <=(2019-09-23 00:00:00, $7))])
LogicalTableScan(table=[[CATALOG, SALES, T1]])
]]>
- </Resource>
- </TestCase>
- <TestCase name="testBuiltinFunctionCoercion">
- <Resource name="sql">
- <![CDATA[select 1||'a' from (values true)]]>
- </Resource>
- <Resource name="plan">
- <![CDATA[
+ </Resource>
+ </TestCase>
+ <TestCase name="testBuiltinFunctionCoercion">
+ <Resource name="sql">
+ <![CDATA[select 1||'a' from (values true)]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
LogicalProject(EXPR$0=[||('1':VARCHAR, 'a')])
LogicalValues(tuples=[[{ true }]])
]]>
- </Resource>
- </TestCase>
- <TestCase name="testStarImplicitTypeCoercion">
- <Resource name="sql">
- <![CDATA[select * from (values(1, '3')) union select * from (values('2', 4))]]>
- </Resource>
- <Resource name="plan">
- <![CDATA[
+ </Resource>
+ </TestCase>
+ <TestCase name="testStarImplicitTypeCoercion">
+ <Resource name="sql">
+ <![CDATA[select * from (values(1, '3')) union select * from (values('2', 4))]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
LogicalUnion(all=[false])
LogicalProject(EXPR$0=[CAST($0):VARCHAR NOT NULL], EXPR$1=[$1])
LogicalValues(tuples=[[{ 1, '3' }]])
LogicalProject(EXPR$0=[$0], EXPR$1=[CAST($1):VARCHAR NOT NULL])
LogicalValues(tuples=[[{ '2', 4 }]])
]]>
- </Resource>
- </TestCase>
- <TestCase name="testSetOperation">
- <Resource name="sql">
- <![CDATA[select t1_int, t1_decimal, t1_smallint, t1_double from t1
-union select t2_varchar20, t2_decimal, t2_float, t2_bigint from t2
-union select t1_varchar20, t1_decimal, t1_float, t1_double from t1
-union select t2_varchar20, t2_decimal, t2_smallint, t2_double from t2]]>
- </Resource>
- <Resource name="plan">
- <![CDATA[
+ </Resource>
+ </TestCase>
+ <TestCase name="testSetOperation">
+ <Resource name="sql">
+ <![CDATA[select t1_int, t1_decimal, t1_smallint, t1_double from t1 union select t2_varchar20, t2_decimal, t2_float, t2_bigint from t2 union select t1_varchar20, t1_decimal, t1_float, t1_double from t1 union select t2_varchar20, t2_decimal, t2_smallint, t2_double from t2]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
LogicalUnion(all=[false])
LogicalUnion(all=[false])
LogicalUnion(all=[false])
@@ -144,42 +139,42 @@ LogicalUnion(all=[false])
LogicalProject(T2_VARCHAR20=[$0], T2_DECIMAL=[$6], T2_SMALLINT=[$1], T2_DOUBLE=[$5])
LogicalTableScan(table=[[CATALOG, SALES, T2]])
]]>
- </Resource>
- </TestCase>
- <TestCase name="testCaseWhen">
- <Resource name="sql">
- <![CDATA[select case when 1 > 0 then t2_bigint else t2_decimal end from t2]]>
- </Resource>
- <Resource name="plan">
- <![CDATA[
+ </Resource>
+ </TestCase>
+ <TestCase name="testCaseWhen">
+ <Resource name="sql">
+ <![CDATA[select case when 1 > 0 then t2_bigint else t2_decimal end from t2]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
LogicalProject(EXPR$0=[CAST($3):DECIMAL(19, 0) NOT NULL])
LogicalTableScan(table=[[CATALOG, SALES, T2]])
]]>
- </Resource>
- </TestCase>
- <TestCase name="testInsertQuerySourceCoercion">
- <Resource name="sql">
- <![CDATA[insert into t1 select t2_smallint, t2_int, t2_bigint, t2_float,
+ </Resource>
+ </TestCase>
+ <TestCase name="testInsertQuerySourceCoercion">
+ <Resource name="sql">
+ <![CDATA[insert into t1 select t2_smallint, t2_int, t2_bigint, t2_float,
t2_double, t2_decimal, t2_int, t2_date, t2_timestamp, t2_varchar20, t2_int from t2]]>
- </Resource>
- <Resource name="plan">
- <![CDATA[
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
LogicalTableModify(table=[[CATALOG, SALES, T1]], operation=[INSERT], flattened=[false])
LogicalProject(t1_varchar20=[CAST($1):VARCHAR(20) NOT NULL], t1_smallint=[CAST($2):SMALLINT NOT NULL], t1_int=[CAST($3):INTEGER NOT NULL], t1_bigint=[CAST($4):BIGINT NOT NULL], t1_float=[CAST($5):FLOAT NOT NULL], t1_double=[CAST($6):DOUBLE NOT NULL], t1_decimal=[CAST($2):DECIMAL(19, 0) NOT NULL], t1_timestamp=[CAST($8):TIMESTAMP(0) NOT NULL], t1_date=[CAST($7):DATE NOT NULL], t1_binary=[CAST($0):BINARY(1) NOT NULL], t1_boolean=[<>($2, 0)])
LogicalTableScan(table=[[CATALOG, SALES, T2]])
]]>
- </Resource>
- </TestCase>
- <TestCase name="testUpdateQuerySourceCoercion">
- <Resource name="sql">
- <![CDATA[update t1 set t1_varchar20=123, t1_date=TIMESTAMP '2020-01-03 10:14:34', t1_int=12.3]]>
- </Resource>
- <Resource name="plan">
- <![CDATA[
+ </Resource>
+ </TestCase>
+ <TestCase name="testUpdateQuerySourceCoercion">
+ <Resource name="sql">
+ <![CDATA[update t1 set t1_varchar20=123, t1_date=TIMESTAMP '2020-01-03 10:14:34', t1_int=12.3]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
LogicalTableModify(table=[[CATALOG, SALES, T1]], operation=[UPDATE], updateColumnList=[[t1_varchar20, t1_date, t1_int]], sourceExpressionList=[[CAST(123):VARCHAR(20) NOT NULL, CAST(2020-01-03 10:14:34):DATE NOT NULL, 12.3]], flattened=[false])
LogicalProject(t1_varchar20=[$0], t1_smallint=[$1], t1_int=[$2], t1_bigint=[$3], t1_float=[$4], t1_double=[$5], t1_decimal=[$6], t1_timestamp=[$7], t1_date=[$8], t1_binary=[$9], t1_boolean=[$10], EXPR$0=[123], EXPR$1=[2020-01-03 10:14:34], EXPR$2=[12.3:DECIMAL(3, 1)])
LogicalTableScan(table=[[CATALOG, SALES, T1]])
]]>
- </Resource>
- </TestCase>
+ </Resource>
+ </TestCase>
</Root>
diff --git a/core/src/test/resources/sql/agg.iq b/core/src/test/resources/sql/agg.iq
index 8764e35..807deb6 100644
--- a/core/src/test/resources/sql/agg.iq
+++ b/core/src/test/resources/sql/agg.iq
@@ -2239,8 +2239,7 @@ from (values (1,2),(3,4));
EnumerableCalc(expr#0=[{inputs}], expr#1=[0:BIGINT], expr#2=[=($t0, $t1)], expr#3=[null:BIGINT], expr#4=[CASE($t2, $t3, $t0)], EXPR$0=[$t4])
EnumerableAggregate(group=[{}], agg#0=[COUNT($0)])
EnumerableAggregate(group=[{0}])
- EnumerableCalc(expr#0..1=[{inputs}], expr#2=['1'], $f0=[$t2])
- EnumerableValues(tuples=[[{ 1, 2 }, { 3, 4 }]])
+ EnumerableValues(tuples=[[{ '1' }, { '1' }]])
!plan
!use scott
@@ -2776,25 +2775,7 @@ from emp group by gender;
!ok
EnumerableAggregate(group=[{1}], EXPR$1=[JSON_ARRAYAGG_ABSENT_ON_NULL($0) WITHIN GROUP ([0])], EXPR$2=[JSON_ARRAYAGG_ABSENT_ON_NULL($0) WITHIN GROUP ([0 DESC])])
- EnumerableUnion(all=[true])
- EnumerableCalc(expr#0=[{inputs}], expr#1=[10], expr#2=['F'], EXPR$1=[$t1], EXPR$2=[$t2])
- EnumerableValues(tuples=[[{ 0 }]])
- EnumerableCalc(expr#0=[{inputs}], expr#1=[10], expr#2=['M'], EXPR$1=[$t1], EXPR$2=[$t2])
- EnumerableValues(tuples=[[{ 0 }]])
- EnumerableCalc(expr#0=[{inputs}], expr#1=[20], expr#2=['M'], EXPR$1=[$t1], EXPR$2=[$t2])
- EnumerableValues(tuples=[[{ 0 }]])
- EnumerableCalc(expr#0=[{inputs}], expr#1=[30], expr#2=['F'], EXPR$1=[$t1], EXPR$2=[$t2])
- EnumerableValues(tuples=[[{ 0 }]])
- EnumerableCalc(expr#0=[{inputs}], expr#1=[30], expr#2=['F'], EXPR$1=[$t1], EXPR$2=[$t2])
- EnumerableValues(tuples=[[{ 0 }]])
- EnumerableCalc(expr#0=[{inputs}], expr#1=[50], expr#2=['M'], EXPR$1=[$t1], EXPR$2=[$t2])
- EnumerableValues(tuples=[[{ 0 }]])
- EnumerableCalc(expr#0=[{inputs}], expr#1=[50], expr#2=['F'], EXPR$1=[$t1], EXPR$2=[$t2])
- EnumerableValues(tuples=[[{ 0 }]])
- EnumerableCalc(expr#0=[{inputs}], expr#1=[60], expr#2=['F'], EXPR$1=[$t1], EXPR$2=[$t2])
- EnumerableValues(tuples=[[{ 0 }]])
- EnumerableCalc(expr#0=[{inputs}], expr#1=[null:INTEGER], expr#2=['F'], EXPR$1=[$t1], EXPR$2=[$t2])
- EnumerableValues(tuples=[[{ 0 }]])
+ EnumerableValues(tuples=[[{ 10, 'F' }, { 10, 'M' }, { 20, 'M' }, { 30, 'F' }, { 30, 'F' }, { 50, 'M' }, { 50, 'F' }, { 60, 'F' }, { null, 'F' }]])
!plan
# [CALCITE-2787] Json aggregate calls with different null clause get incorrectly merged
@@ -2813,25 +2794,7 @@ from emp group by gender;
!ok
EnumerableAggregate(group=[{1}], EXPR$1=[JSON_ARRAYAGG_ABSENT_ON_NULL($0)], EXPR$2=[JSON_ARRAYAGG_NULL_ON_NULL($0)])
- EnumerableUnion(all=[true])
- EnumerableCalc(expr#0=[{inputs}], expr#1=[10], expr#2=['F'], EXPR$1=[$t1], EXPR$2=[$t2])
- EnumerableValues(tuples=[[{ 0 }]])
- EnumerableCalc(expr#0=[{inputs}], expr#1=[10], expr#2=['M'], EXPR$1=[$t1], EXPR$2=[$t2])
- EnumerableValues(tuples=[[{ 0 }]])
- EnumerableCalc(expr#0=[{inputs}], expr#1=[20], expr#2=['M'], EXPR$1=[$t1], EXPR$2=[$t2])
- EnumerableValues(tuples=[[{ 0 }]])
- EnumerableCalc(expr#0=[{inputs}], expr#1=[30], expr#2=['F'], EXPR$1=[$t1], EXPR$2=[$t2])
- EnumerableValues(tuples=[[{ 0 }]])
- EnumerableCalc(expr#0=[{inputs}], expr#1=[30], expr#2=['F'], EXPR$1=[$t1], EXPR$2=[$t2])
- EnumerableValues(tuples=[[{ 0 }]])
- EnumerableCalc(expr#0=[{inputs}], expr#1=[50], expr#2=['M'], EXPR$1=[$t1], EXPR$2=[$t2])
- EnumerableValues(tuples=[[{ 0 }]])
- EnumerableCalc(expr#0=[{inputs}], expr#1=[50], expr#2=['F'], EXPR$1=[$t1], EXPR$2=[$t2])
- EnumerableValues(tuples=[[{ 0 }]])
- EnumerableCalc(expr#0=[{inputs}], expr#1=[60], expr#2=['F'], EXPR$1=[$t1], EXPR$2=[$t2])
- EnumerableValues(tuples=[[{ 0 }]])
- EnumerableCalc(expr#0=[{inputs}], expr#1=[null:INTEGER], expr#2=['F'], EXPR$1=[$t1], EXPR$2=[$t2])
- EnumerableValues(tuples=[[{ 0 }]])
+ EnumerableValues(tuples=[[{ 10, 'F' }, { 10, 'M' }, { 20, 'M' }, { 30, 'F' }, { 30, 'F' }, { 50, 'M' }, { 50, 'F' }, { 60, 'F' }, { null, 'F' }]])
!plan
select gender,
@@ -2848,25 +2811,7 @@ from emp group by gender;
!ok
EnumerableAggregate(group=[{2}], EXPR$1=[JSON_OBJECTAGG_NULL_ON_NULL($0, $1)], EXPR$2=[JSON_OBJECTAGG_ABSENT_ON_NULL($0, $1)])
- EnumerableUnion(all=[true])
- EnumerableCalc(expr#0=[{inputs}], expr#1=['Jane'], expr#2=[10], expr#3=['F'], EXPR$0=[$t1], EXPR$1=[$t2], EXPR$2=[$t3])
- EnumerableValues(tuples=[[{ 0 }]])
- EnumerableCalc(expr#0=[{inputs}], expr#1=['Bob'], expr#2=[10], expr#3=['M'], EXPR$0=[$t1], EXPR$1=[$t2], EXPR$2=[$t3])
- EnumerableValues(tuples=[[{ 0 }]])
- EnumerableCalc(expr#0=[{inputs}], expr#1=['Eric'], expr#2=[20], expr#3=['M'], EXPR$0=[$t1], EXPR$1=[$t2], EXPR$2=[$t3])
- EnumerableValues(tuples=[[{ 0 }]])
- EnumerableCalc(expr#0=[{inputs}], expr#1=['Susan'], expr#2=[30], expr#3=['F'], EXPR$0=[$t1], EXPR$1=[$t2], EXPR$2=[$t3])
- EnumerableValues(tuples=[[{ 0 }]])
- EnumerableCalc(expr#0=[{inputs}], expr#1=['Alice'], expr#2=[30], expr#3=['F'], EXPR$0=[$t1], EXPR$1=[$t2], EXPR$2=[$t3])
- EnumerableValues(tuples=[[{ 0 }]])
- EnumerableCalc(expr#0=[{inputs}], expr#1=['Adam'], expr#2=[50], expr#3=['M'], EXPR$0=[$t1], EXPR$1=[$t2], EXPR$2=[$t3])
- EnumerableValues(tuples=[[{ 0 }]])
- EnumerableCalc(expr#0=[{inputs}], expr#1=['Eve'], expr#2=[50], expr#3=['F'], EXPR$0=[$t1], EXPR$1=[$t2], EXPR$2=[$t3])
- EnumerableValues(tuples=[[{ 0 }]])
- EnumerableCalc(expr#0=[{inputs}], expr#1=['Grace'], expr#2=[60], expr#3=['F'], EXPR$0=[$t1], EXPR$1=[$t2], EXPR$2=[$t3])
- EnumerableValues(tuples=[[{ 0 }]])
- EnumerableCalc(expr#0=[{inputs}], expr#1=['Wilma'], expr#2=[null:INTEGER], expr#3=['F'], EXPR$0=[$t1], EXPR$1=[$t2], EXPR$2=[$t3])
- EnumerableValues(tuples=[[{ 0 }]])
+ EnumerableValues(tuples=[[{ 'Jane', 10, 'F' }, { 'Bob', 10, 'M' }, { 'Eric', 20, 'M' }, { 'Susan', 30, 'F' }, { 'Alice', 30, 'F' }, { 'Adam', 50, 'M' }, { 'Eve', 50, 'F' }, { 'Grace', 60, 'F' }, { 'Wilma', null, 'F' }]])
!plan
select listagg(ename) as combined_name from emp;
@@ -2889,26 +2834,8 @@ select listagg(ename) within group(order by gender, ename) as combined_name from
!ok
-EnumerableAggregate(group=[{}], COMBINED_NAME=[LISTAGG($0) WITHIN GROUP ([1, 0])])
- EnumerableUnion(all=[true])
- EnumerableCalc(expr#0=[{inputs}], expr#1=['Jane'], expr#2=['F'], EXPR$0=[$t1], EXPR$2=[$t2])
- EnumerableValues(tuples=[[{ 0 }]])
- EnumerableCalc(expr#0=[{inputs}], expr#1=['Bob'], expr#2=['M'], EXPR$0=[$t1], EXPR$2=[$t2])
- EnumerableValues(tuples=[[{ 0 }]])
- EnumerableCalc(expr#0=[{inputs}], expr#1=['Eric'], expr#2=['M'], EXPR$0=[$t1], EXPR$2=[$t2])
- EnumerableValues(tuples=[[{ 0 }]])
- EnumerableCalc(expr#0=[{inputs}], expr#1=['Susan'], expr#2=['F'], EXPR$0=[$t1], EXPR$2=[$t2])
- EnumerableValues(tuples=[[{ 0 }]])
- EnumerableCalc(expr#0=[{inputs}], expr#1=['Alice'], expr#2=['F'], EXPR$0=[$t1], EXPR$2=[$t2])
- EnumerableValues(tuples=[[{ 0 }]])
- EnumerableCalc(expr#0=[{inputs}], expr#1=['Adam'], expr#2=['M'], EXPR$0=[$t1], EXPR$2=[$t2])
- EnumerableValues(tuples=[[{ 0 }]])
- EnumerableCalc(expr#0=[{inputs}], expr#1=['Eve'], expr#2=['F'], EXPR$0=[$t1], EXPR$2=[$t2])
- EnumerableValues(tuples=[[{ 0 }]])
- EnumerableCalc(expr#0=[{inputs}], expr#1=['Grace'], expr#2=['F'], EXPR$0=[$t1], EXPR$2=[$t2])
- EnumerableValues(tuples=[[{ 0 }]])
- EnumerableCalc(expr#0=[{inputs}], expr#1=['Wilma'], expr#2=['F'], EXPR$0=[$t1], EXPR$2=[$t2])
- EnumerableValues(tuples=[[{ 0 }]])
+EnumerableAggregate(group=[{}], COMBINED_NAME=[LISTAGG($0) WITHIN GROUP ([2, 0])])
+ EnumerableValues(tuples=[[{ 'Jane', 10, 'F' }, { 'Bob', 10, 'M' }, { 'Eric', 20, 'M' }, { 'Susan', 30, 'F' }, { 'Alice', 30, 'F' }, { 'Adam', 50, 'M' }, { 'Eve', 50, 'F' }, { 'Grace', 60, 'F' }, { 'Wilma', null, 'F' }]])
!plan
select
diff --git a/core/src/test/resources/sql/join.iq b/core/src/test/resources/sql/join.iq
index 5918542..ccab10b 100644
--- a/core/src/test/resources/sql/join.iq
+++ b/core/src/test/resources/sql/join.iq
@@ -37,25 +37,7 @@ on emp.deptno = dept.deptno or emp.ename = dept.dname;
!ok
EnumerableNestedLoopJoin(condition=[OR(=($1, $3), =(CAST($0):CHAR(11) NOT NULL, $4))], joinType=[inner])
- EnumerableUnion(all=[true])
- EnumerableCalc(expr#0=[{inputs}], expr#1=['Jane'], expr#2=[10], expr#3=['F'], EXPR$0=[$t1], EXPR$1=[$t2], EXPR$2=[$t3])
- EnumerableValues(tuples=[[{ 0 }]])
- EnumerableCalc(expr#0=[{inputs}], expr#1=['Bob'], expr#2=[10], expr#3=['M'], EXPR$0=[$t1], EXPR$1=[$t2], EXPR$2=[$t3])
- EnumerableValues(tuples=[[{ 0 }]])
- EnumerableCalc(expr#0=[{inputs}], expr#1=['Eric'], expr#2=[20], expr#3=['M'], EXPR$0=[$t1], EXPR$1=[$t2], EXPR$2=[$t3])
- EnumerableValues(tuples=[[{ 0 }]])
- EnumerableCalc(expr#0=[{inputs}], expr#1=['Susan'], expr#2=[30], expr#3=['F'], EXPR$0=[$t1], EXPR$1=[$t2], EXPR$2=[$t3])
- EnumerableValues(tuples=[[{ 0 }]])
- EnumerableCalc(expr#0=[{inputs}], expr#1=['Alice'], expr#2=[30], expr#3=['F'], EXPR$0=[$t1], EXPR$1=[$t2], EXPR$2=[$t3])
- EnumerableValues(tuples=[[{ 0 }]])
- EnumerableCalc(expr#0=[{inputs}], expr#1=['Adam'], expr#2=[50], expr#3=['M'], EXPR$0=[$t1], EXPR$1=[$t2], EXPR$2=[$t3])
- EnumerableValues(tuples=[[{ 0 }]])
- EnumerableCalc(expr#0=[{inputs}], expr#1=['Eve'], expr#2=[50], expr#3=['F'], EXPR$0=[$t1], EXPR$1=[$t2], EXPR$2=[$t3])
- EnumerableValues(tuples=[[{ 0 }]])
- EnumerableCalc(expr#0=[{inputs}], expr#1=['Grace'], expr#2=[60], expr#3=['F'], EXPR$0=[$t1], EXPR$1=[$t2], EXPR$2=[$t3])
- EnumerableValues(tuples=[[{ 0 }]])
- EnumerableCalc(expr#0=[{inputs}], expr#1=['Wilma'], expr#2=[null:INTEGER], expr#3=['F'], EXPR$0=[$t1], EXPR$1=[$t2], EXPR$2=[$t3])
- EnumerableValues(tuples=[[{ 0 }]])
+ EnumerableValues(tuples=[[{ 'Jane', 10, 'F' }, { 'Bob', 10, 'M' }, { 'Eric', 20, 'M' }, { 'Susan', 30, 'F' }, { 'Alice', 30, 'F' }, { 'Adam', 50, 'M' }, { 'Eve', 50, 'F' }, { 'Grace', 60, 'F' }, { 'Wilma', null, 'F' }]])
EnumerableValues(tuples=[[{ 10, 'Sales ' }, { 20, 'Marketing ' }, { 30, 'Engineering' }, { 40, 'Empty ' }]])
!plan
@@ -83,25 +65,7 @@ on emp.deptno = dept.deptno or emp.ename = dept.dname;
# Cannot be decomposed into an equi-join; plan uses EnumerableNestedLoopJoin
EnumerableNestedLoopJoin(condition=[OR(=($1, $3), =(CAST($0):CHAR(11) NOT NULL, $4))], joinType=[left])
- EnumerableUnion(all=[true])
- EnumerableCalc(expr#0=[{inputs}], expr#1=['Jane'], expr#2=[10], expr#3=['F'], EXPR$0=[$t1], EXPR$1=[$t2], EXPR$2=[$t3])
- EnumerableValues(tuples=[[{ 0 }]])
- EnumerableCalc(expr#0=[{inputs}], expr#1=['Bob'], expr#2=[10], expr#3=['M'], EXPR$0=[$t1], EXPR$1=[$t2], EXPR$2=[$t3])
- EnumerableValues(tuples=[[{ 0 }]])
- EnumerableCalc(expr#0=[{inputs}], expr#1=['Eric'], expr#2=[20], expr#3=['M'], EXPR$0=[$t1], EXPR$1=[$t2], EXPR$2=[$t3])
- EnumerableValues(tuples=[[{ 0 }]])
- EnumerableCalc(expr#0=[{inputs}], expr#1=['Susan'], expr#2=[30], expr#3=['F'], EXPR$0=[$t1], EXPR$1=[$t2], EXPR$2=[$t3])
- EnumerableValues(tuples=[[{ 0 }]])
- EnumerableCalc(expr#0=[{inputs}], expr#1=['Alice'], expr#2=[30], expr#3=['F'], EXPR$0=[$t1], EXPR$1=[$t2], EXPR$2=[$t3])
- EnumerableValues(tuples=[[{ 0 }]])
- EnumerableCalc(expr#0=[{inputs}], expr#1=['Adam'], expr#2=[50], expr#3=['M'], EXPR$0=[$t1], EXPR$1=[$t2], EXPR$2=[$t3])
- EnumerableValues(tuples=[[{ 0 }]])
- EnumerableCalc(expr#0=[{inputs}], expr#1=['Eve'], expr#2=[50], expr#3=['F'], EXPR$0=[$t1], EXPR$1=[$t2], EXPR$2=[$t3])
- EnumerableValues(tuples=[[{ 0 }]])
- EnumerableCalc(expr#0=[{inputs}], expr#1=['Grace'], expr#2=[60], expr#3=['F'], EXPR$0=[$t1], EXPR$1=[$t2], EXPR$2=[$t3])
- EnumerableValues(tuples=[[{ 0 }]])
- EnumerableCalc(expr#0=[{inputs}], expr#1=['Wilma'], expr#2=[null:INTEGER], expr#3=['F'], EXPR$0=[$t1], EXPR$1=[$t2], EXPR$2=[$t3])
- EnumerableValues(tuples=[[{ 0 }]])
+ EnumerableValues(tuples=[[{ 'Jane', 10, 'F' }, { 'Bob', 10, 'M' }, { 'Eric', 20, 'M' }, { 'Susan', 30, 'F' }, { 'Alice', 30, 'F' }, { 'Adam', 50, 'M' }, { 'Eve', 50, 'F' }, { 'Grace', 60, 'F' }, { 'Wilma', null, 'F' }]])
EnumerableValues(tuples=[[{ 10, 'Sales ' }, { 20, 'Marketing ' }, { 30, 'Engineering' }, { 40, 'Empty ' }]])
!plan
diff --git a/core/src/test/resources/sql/misc.iq b/core/src/test/resources/sql/misc.iq
index e73a17e..3c945e2 100644
--- a/core/src/test/resources/sql/misc.iq
+++ b/core/src/test/resources/sql/misc.iq
@@ -1671,8 +1671,7 @@ values cast('1969-07-21 12:34:56' as timestamp);
(1 row)
!ok
-EnumerableCalc(expr#0=[{inputs}], expr#1=[1969-07-21 12:34:56], EXPR$0=[$t1])
- EnumerableValues(tuples=[[{ 0 }]])
+EnumerableValues(tuples=[[{ 1969-07-21 12:34:56 }]])
!plan
# Cast a character literal without time to a timestamp; note: the plan does not contain CAST
@@ -1685,8 +1684,7 @@ values cast('1969-07-21' as timestamp);
(1 row)
!ok
-EnumerableCalc(expr#0=[{inputs}], expr#1=[1969-07-21 00:00:00], EXPR$0=[$t1])
- EnumerableValues(tuples=[[{ 0 }]])
+EnumerableValues(tuples=[[{ 1969-07-21 00:00:00 }]])
!plan
# Cast a character literal to a date; note: the plan does not contain CAST
@@ -1699,8 +1697,7 @@ values cast('1969-07-21' as date);
(1 row)
!ok
-EnumerableCalc(expr#0=[{inputs}], expr#1=[1969-07-21], EXPR$0=[$t1])
- EnumerableValues(tuples=[[{ 0 }]])
+EnumerableValues(tuples=[[{ 1969-07-21 }]])
!plan
# Slightly different format
@@ -1725,8 +1722,7 @@ values cast('196907' as integer);
(1 row)
!ok
-EnumerableCalc(expr#0=[{inputs}], expr#1=[196907], EXPR$0=[$t1])
- EnumerableValues(tuples=[[{ 0 }]])
+EnumerableValues(tuples=[[{ 196907 }]])
!plan
# Cast an integer literal to a bigint; note: the plan does not contain CAST
@@ -1739,8 +1735,7 @@ values cast(123 as bigint);
(1 row)
!ok
-EnumerableCalc(expr#0=[{inputs}], expr#1=[123:BIGINT], EXPR$0=[$t1])
- EnumerableValues(tuples=[[{ 0 }]])
+EnumerableValues(tuples=[[{ 123 }]])
!plan
# Cast an integer literal to a decimal; note: the plan does not contain CAST
@@ -1753,8 +1748,7 @@ values cast('123.45' as decimal(5, 2));
(1 row)
!ok
-EnumerableCalc(expr#0=[{inputs}], expr#1=[123.45:DECIMAL(5, 2)], EXPR$0=[$t1])
- EnumerableValues(tuples=[[{ 0 }]])
+EnumerableValues(tuples=[[{ 123.45 }]])
!plan
# Cast a character literal to a decimal; note: the plan does not contain CAST
@@ -1767,8 +1761,7 @@ values cast('123.45' as decimal(5, 2));
(1 row)
!ok
-EnumerableCalc(expr#0=[{inputs}], expr#1=[123.45:DECIMAL(5, 2)], EXPR$0=[$t1])
- EnumerableValues(tuples=[[{ 0 }]])
+EnumerableValues(tuples=[[{ 123.45 }]])
!plan
# Cast a character literal to a double; note: the plan does not contain CAST
@@ -1781,8 +1774,7 @@ values cast('-123.45' as double);
(1 row)
!ok
-EnumerableCalc(expr#0=[{inputs}], expr#1=[-1.2345E2:DOUBLE], EXPR$0=[$t1])
- EnumerableValues(tuples=[[{ 0 }]])
+EnumerableValues(tuples=[[{ -1.2345E2 }]])
!plan
values cast('false' as boolean);
@@ -1794,8 +1786,7 @@ values cast('false' as boolean);
(1 row)
!ok
-EnumerableCalc(expr#0=[{inputs}], expr#1=[false], EXPR$0=[$t1])
- EnumerableValues(tuples=[[{ 0 }]])
+EnumerableValues(tuples=[[{ false }]])
!plan
values cast('TRUE' as boolean);
@@ -1807,8 +1798,7 @@ values cast('TRUE' as boolean);
(1 row)
!ok
-EnumerableCalc(expr#0=[{inputs}], expr#1=[true], EXPR$0=[$t1])
- EnumerableValues(tuples=[[{ 0 }]])
+EnumerableValues(tuples=[[{ true }]])
!plan
values cast('TR' || 'UE' as boolean);
@@ -2091,8 +2081,7 @@ select TIMESTAMP '2016-02-26 19:06:00.123456789',
(1 row)
!ok
-EnumerableCalc(expr#0=[{inputs}], expr#1=[2016-02-26 19:06:00.123:TIMESTAMP(3)], expr#2=[2016-02-26 19:06:00], expr#3=[2016-02-26 19:06:00.1:TIMESTAMP(1)], expr#4=[2016-02-26 19:06:00.12:TIMESTAMP(2)], EXPR$0=[$t1], EXPR$1=[$t2], EXPR$2=[$t2], EXPR$3=[$t3], EXPR$4=[$t4], EXPR$5=[$t1], EXPR$6=[$t1], EXPR$7=[$t1])
- EnumerableValues(tuples=[[{ 0 }]])
+EnumerableValues(tuples=[[{ 2016-02-26 19:06:00.123, 2016-02-26 19:06:00, 2016-02-26 19:06:00, 2016-02-26 19:06:00.1, 2016-02-26 19:06:00.12, 2016-02-26 19:06:00.123, 2016-02-26 19:06:00.123, 2016-02-26 19:06:00.123 }]])
!plan
# [CALCITE-1664] CAST('<string>' as TIMESTAMP) adds part of sub-second fraction to the value
diff --git a/core/src/test/resources/sql/outer.iq b/core/src/test/resources/sql/outer.iq
index dfa5e1a..ba86fd4 100644
--- a/core/src/test/resources/sql/outer.iq
+++ b/core/src/test/resources/sql/outer.iq
@@ -249,25 +249,7 @@ select * from (select * from emp where gender ='F') as emp full join dept on emp
!ok
EnumerableNestedLoopJoin(condition=[=(-($1, $3), 0)], joinType=[full])
EnumerableCalc(expr#0..2=[{inputs}], expr#3=['F'], expr#4=[=($t2, $t3)], proj#0..2=[{exprs}], $condition=[$t4])
- EnumerableUnion(all=[true])
- EnumerableCalc(expr#0=[{inputs}], expr#1=['Jane'], expr#2=[10], expr#3=['F'], EXPR$0=[$t1], EXPR$1=[$t2], EXPR$2=[$t3])
- EnumerableValues(tuples=[[{ 0 }]])
- EnumerableCalc(expr#0=[{inputs}], expr#1=['Bob'], expr#2=[10], expr#3=['M'], EXPR$0=[$t1], EXPR$1=[$t2], EXPR$2=[$t3])
- EnumerableValues(tuples=[[{ 0 }]])
- EnumerableCalc(expr#0=[{inputs}], expr#1=['Eric'], expr#2=[20], expr#3=['M'], EXPR$0=[$t1], EXPR$1=[$t2], EXPR$2=[$t3])
- EnumerableValues(tuples=[[{ 0 }]])
- EnumerableCalc(expr#0=[{inputs}], expr#1=['Susan'], expr#2=[30], expr#3=['F'], EXPR$0=[$t1], EXPR$1=[$t2], EXPR$2=[$t3])
- EnumerableValues(tuples=[[{ 0 }]])
- EnumerableCalc(expr#0=[{inputs}], expr#1=['Alice'], expr#2=[30], expr#3=['F'], EXPR$0=[$t1], EXPR$1=[$t2], EXPR$2=[$t3])
- EnumerableValues(tuples=[[{ 0 }]])
- EnumerableCalc(expr#0=[{inputs}], expr#1=['Adam'], expr#2=[50], expr#3=['M'], EXPR$0=[$t1], EXPR$1=[$t2], EXPR$2=[$t3])
- EnumerableValues(tuples=[[{ 0 }]])
- EnumerableCalc(expr#0=[{inputs}], expr#1=['Eve'], expr#2=[50], expr#3=['F'], EXPR$0=[$t1], EXPR$1=[$t2], EXPR$2=[$t3])
- EnumerableValues(tuples=[[{ 0 }]])
- EnumerableCalc(expr#0=[{inputs}], expr#1=['Grace'], expr#2=[60], expr#3=['F'], EXPR$0=[$t1], EXPR$1=[$t2], EXPR$2=[$t3])
- EnumerableValues(tuples=[[{ 0 }]])
- EnumerableCalc(expr#0=[{inputs}], expr#1=['Wilma'], expr#2=[null:INTEGER], expr#3=['F'], EXPR$0=[$t1], EXPR$1=[$t2], EXPR$2=[$t3])
- EnumerableValues(tuples=[[{ 0 }]])
+ EnumerableValues(tuples=[[{ 'Jane', 10, 'F' }, { 'Bob', 10, 'M' }, { 'Eric', 20, 'M' }, { 'Susan', 30, 'F' }, { 'Alice', 30, 'F' }, { 'Adam', 50, 'M' }, { 'Eve', 50, 'F' }, { 'Grace', 60, 'F' }, { 'Wilma', null, 'F' }]])
EnumerableValues(tuples=[[{ 10, 'Sales ' }, { 20, 'Marketing ' }, { 30, 'Engineering' }, { 40, 'Empty ' }]])
!plan
diff --git a/core/src/test/resources/sql/sub-query.iq b/core/src/test/resources/sql/sub-query.iq
index a7fc8a3..158f1f0 100644
--- a/core/src/test/resources/sql/sub-query.iq
+++ b/core/src/test/resources/sql/sub-query.iq
@@ -33,28 +33,13 @@ where t1.x not in (select t2.x from t2);
!ok
EnumerableCalc(expr#0..4=[{inputs}], expr#5=[0], expr#6=[=($t1, $t5)], expr#7=[IS NULL($t4)], expr#8=[>=($t2, $t1)], expr#9=[IS NOT NULL($t0)], expr#10=[AND($t7, $t8, $t9)], expr#11=[OR($t6, $t10)], X=[$t0], $condition=[$t11])
- EnumerableHashJoin(condition=[=($0, $3)], joinType=[left])
+ EnumerableMergeJoin(condition=[=($0, $3)], joinType=[left])
EnumerableNestedLoopJoin(condition=[true], joinType=[inner])
- EnumerableUnion(all=[true])
- EnumerableCalc(expr#0=[{inputs}], expr#1=[1], EXPR$0=[$t1])
- EnumerableValues(tuples=[[{ 0 }]])
- EnumerableCalc(expr#0=[{inputs}], expr#1=[2], EXPR$0=[$t1])
- EnumerableValues(tuples=[[{ 0 }]])
- EnumerableCalc(expr#0=[{inputs}], expr#1=[null:INTEGER], EXPR$0=[$t1])
- EnumerableValues(tuples=[[{ 0 }]])
+ EnumerableValues(tuples=[[{ 1 }, { 2 }, { null }]])
EnumerableAggregate(group=[{}], c=[COUNT()], ck=[COUNT($0)])
- EnumerableUnion(all=[true])
- EnumerableCalc(expr#0=[{inputs}], expr#1=[1], EXPR$0=[$t1])
- EnumerableValues(tuples=[[{ 0 }]])
- EnumerableCalc(expr#0=[{inputs}], expr#1=[null:INTEGER], EXPR$0=[$t1])
- EnumerableValues(tuples=[[{ 0 }]])
- EnumerableAggregate(group=[{0, 1}])
- EnumerableCalc(expr#0=[{inputs}], expr#1=[true], proj#0..1=[{exprs}])
- EnumerableUnion(all=[true])
- EnumerableCalc(expr#0=[{inputs}], expr#1=[1], EXPR$0=[$t1])
- EnumerableValues(tuples=[[{ 0 }]])
- EnumerableCalc(expr#0=[{inputs}], expr#1=[null:INTEGER], EXPR$0=[$t1])
- EnumerableValues(tuples=[[{ 0 }]])
+ EnumerableValues(tuples=[[{ 1 }, { null }]])
+ EnumerableCalc(expr#0=[{inputs}], expr#1=[true], proj#0..1=[{exprs}])
+ EnumerableValues(tuples=[[{ 1 }, { null }]])
!plan
# Use of case is to get around issue with directly specifying null in values
diff --git a/core/src/test/resources/sql/winagg.iq b/core/src/test/resources/sql/winagg.iq
index 2ed97d1..ce77cf7 100644
--- a/core/src/test/resources/sql/winagg.iq
+++ b/core/src/test/resources/sql/winagg.iq
@@ -546,25 +546,7 @@ select emp."ENAME", emp."DEPTNO",
from emp order by emp."ENAME";
EnumerableSort(sort0=[$0], dir0=[ASC])
EnumerableWindow(window#0=[window(aggs [NTH_VALUE($1, $2), NTH_VALUE($1, $3), NTH_VALUE($1, $4), NTH_VALUE($1, $5), NTH_VALUE($1, $6)])])
- EnumerableUnion(all=[true])
- EnumerableCalc(expr#0=[{inputs}], expr#1=['Jane'], expr#2=[10], EXPR$0=[$t1], EXPR$1=[$t2])
- EnumerableValues(tuples=[[{ 0 }]])
- EnumerableCalc(expr#0=[{inputs}], expr#1=['Bob'], expr#2=[10], EXPR$0=[$t1], EXPR$1=[$t2])
- EnumerableValues(tuples=[[{ 0 }]])
- EnumerableCalc(expr#0=[{inputs}], expr#1=['Eric'], expr#2=[20], EXPR$0=[$t1], EXPR$1=[$t2])
- EnumerableValues(tuples=[[{ 0 }]])
- EnumerableCalc(expr#0=[{inputs}], expr#1=['Susan'], expr#2=[30], EXPR$0=[$t1], EXPR$1=[$t2])
- EnumerableValues(tuples=[[{ 0 }]])
- EnumerableCalc(expr#0=[{inputs}], expr#1=['Alice'], expr#2=[30], EXPR$0=[$t1], EXPR$1=[$t2])
- EnumerableValues(tuples=[[{ 0 }]])
- EnumerableCalc(expr#0=[{inputs}], expr#1=['Adam'], expr#2=[50], EXPR$0=[$t1], EXPR$1=[$t2])
- EnumerableValues(tuples=[[{ 0 }]])
- EnumerableCalc(expr#0=[{inputs}], expr#1=['Eve'], expr#2=[50], EXPR$0=[$t1], EXPR$1=[$t2])
- EnumerableValues(tuples=[[{ 0 }]])
- EnumerableCalc(expr#0=[{inputs}], expr#1=['Grace'], expr#2=[60], EXPR$0=[$t1], EXPR$1=[$t2])
- EnumerableValues(tuples=[[{ 0 }]])
- EnumerableCalc(expr#0=[{inputs}], expr#1=['Wilma'], expr#2=[null:INTEGER], EXPR$0=[$t1], EXPR$1=[$t2])
- EnumerableValues(tuples=[[{ 0 }]])
+ EnumerableValues(tuples=[[{ 'Jane', 10 }, { 'Bob', 10 }, { 'Eric', 20 }, { 'Susan', 30 }, { 'Alice', 30 }, { 'Adam', 50 }, { 'Eve', 50 }, { 'Grace', 60 }, { 'Wilma', null }]])
!plan
+-------+--------+-------------+--------------+-------------+--------------+-------------+
| ENAME | DEPTNO | first_value | second_value | fifth_value | eighth_value | tenth_value |