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 <[email protected]> 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 |
