[CALCITE-1996] In JDBC adapter, generate correct VALUES syntax
Project: http://git-wip-us.apache.org/repos/asf/calcite/repo Commit: http://git-wip-us.apache.org/repos/asf/calcite/commit/fb760a6f Tree: http://git-wip-us.apache.org/repos/asf/calcite/tree/fb760a6f Diff: http://git-wip-us.apache.org/repos/asf/calcite/diff/fb760a6f Branch: refs/heads/master Commit: fb760a6f4e0cf8ccf92380dd676a72d00402d53e Parents: 7546ef2 Author: Julian Hyde <[email protected]> Authored: Wed Oct 4 11:56:57 2017 -0700 Committer: Julian Hyde <[email protected]> Committed: Thu Oct 5 16:52:02 2017 -0700 ---------------------------------------------------------------------- .../calcite/rel/rel2sql/RelToSqlConverter.java | 79 ++++++++++- .../java/org/apache/calcite/sql/SqlDialect.java | 13 ++ .../calcite/sql/dialect/OracleSqlDialect.java | 4 + .../calcite/sql2rel/SqlToRelConverter.java | 12 +- .../org/apache/calcite/tools/RelBuilder.java | 11 ++ .../rel/rel2sql/RelToSqlConverterTest.java | 136 +++++++++++++------ .../apache/calcite/test/JdbcAdapterTest.java | 2 +- .../org/apache/calcite/test/RelBuilderTest.java | 18 +++ .../apache/calcite/tools/FrameworksTest.java | 55 ++++++++ .../calcite/test/SqlToRelConverterTest.xml | 8 +- 10 files changed, 286 insertions(+), 52 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/calcite/blob/fb760a6f/core/src/main/java/org/apache/calcite/rel/rel2sql/RelToSqlConverter.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/rel/rel2sql/RelToSqlConverter.java b/core/src/main/java/org/apache/calcite/rel/rel2sql/RelToSqlConverter.java index 45ec2f7..1e60c10 100644 --- a/core/src/main/java/org/apache/calcite/rel/rel2sql/RelToSqlConverter.java +++ b/core/src/main/java/org/apache/calcite/rel/rel2sql/RelToSqlConverter.java @@ -61,15 +61,19 @@ import org.apache.calcite.sql.fun.SqlSingleValueAggFunction; import org.apache.calcite.sql.fun.SqlStdOperatorTable; import org.apache.calcite.sql.parser.SqlParserPos; import org.apache.calcite.sql.validate.SqlValidatorUtil; +import org.apache.calcite.util.Pair; import org.apache.calcite.util.ReflectUtil; import org.apache.calcite.util.ReflectiveVisitor; import com.google.common.base.Function; import com.google.common.collect.ImmutableList; import com.google.common.collect.ImmutableMap; +import com.google.common.collect.Iterables; import com.google.common.collect.Lists; +import java.util.ArrayDeque; import java.util.ArrayList; +import java.util.Deque; import java.util.List; import java.util.Locale; import java.util.Map; @@ -85,6 +89,8 @@ public class RelToSqlConverter extends SqlImplementor private final ReflectUtil.MethodDispatcher<Result> dispatcher; + private final Deque<Frame> stack = new ArrayDeque<>(); + /** Creates a RelToSqlConverter. */ public RelToSqlConverter(SqlDialect dialect) { super(dialect); @@ -99,7 +105,12 @@ public class RelToSqlConverter extends SqlImplementor } public Result visitChild(int i, RelNode e) { - return dispatch(e); + try { + stack.push(new Frame(i, e)); + return dispatch(e); + } finally { + stack.pop(); + } } /** @see #dispatch */ @@ -270,11 +281,58 @@ public class RelToSqlConverter extends SqlImplementor final List<Clause> clauses = ImmutableList.of(Clause.SELECT); final Map<String, RelDataType> pairs = ImmutableMap.of(); final Context context = aliasContext(pairs, false); - final SqlNodeList selects = new SqlNodeList(POS); - for (List<RexLiteral> tuple : e.getTuples()) { - selects.add(ANONYMOUS_ROW.createCall(exprList(context, tuple))); + SqlNode query; + final boolean rename = stack.size() <= 1 + || !(Iterables.get(stack, 1).r instanceof TableModify); + final List<String> fieldNames = e.getRowType().getFieldNames(); + if (!dialect.supportsAliasedValues() && rename) { + // Oracle does not support "AS t (c1, c2)". So instead of + // (VALUES (v0, v1), (v2, v3)) AS t (c0, c1) + // we generate + // SELECT v0 AS c0, v1 AS c1 FROM DUAL + // UNION ALL + // SELECT v2 AS c0, v3 AS c1 FROM DUAL + List<SqlSelect> list = new ArrayList<>(); + for (List<RexLiteral> tuple : e.getTuples()) { + final List<SqlNode> values2 = new ArrayList<>(); + final SqlNodeList exprList = exprList(context, tuple); + for (Pair<SqlNode, String> value : Pair.zip(exprList, fieldNames)) { + values2.add( + SqlStdOperatorTable.AS.createCall(POS, value.left, + new SqlIdentifier(value.right, POS))); + } + list.add( + new SqlSelect(POS, null, + new SqlNodeList(values2, POS), + new SqlIdentifier("DUAL", POS), null, null, + null, null, null, null, null)); + } + if (list.size() == 1) { + query = list.get(0); + } else { + query = SqlStdOperatorTable.UNION_ALL.createCall( + new SqlNodeList(list, POS)); + } + } else { + // Generate ANSI syntax + // (VALUES (v0, v1), (v2, v3)) + // or, if rename is required + // (VALUES (v0, v1), (v2, v3)) AS t (c0, c1) + final SqlNodeList selects = new SqlNodeList(POS); + for (List<RexLiteral> tuple : e.getTuples()) { + selects.add(ANONYMOUS_ROW.createCall(exprList(context, tuple))); + } + query = SqlStdOperatorTable.VALUES.createCall(selects); + if (rename) { + final List<SqlNode> list = new ArrayList<>(); + list.add(query); + list.add(new SqlIdentifier("t", POS)); + for (String fieldName : fieldNames) { + list.add(new SqlIdentifier(fieldName, POS)); + } + query = SqlStdOperatorTable.AS.createCall(POS, list); + } } - SqlNode query = SqlStdOperatorTable.VALUES.createCall(selects); return result(query, clauses, e, null); } @@ -494,6 +552,17 @@ public class RelToSqlConverter extends SqlImplementor correlTableMap.put(id, x.qualifiedContext()); } } + + /** Stack frame. */ + private static class Frame { + private final int ordinalInParent; + private final RelNode r; + + Frame(int ordinalInParent, RelNode r) { + this.ordinalInParent = ordinalInParent; + this.r = r; + } + } } // End RelToSqlConverter.java http://git-wip-us.apache.org/repos/asf/calcite/blob/fb760a6f/core/src/main/java/org/apache/calcite/sql/SqlDialect.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/sql/SqlDialect.java b/core/src/main/java/org/apache/calcite/sql/SqlDialect.java index f9cdf67..6023089 100644 --- a/core/src/main/java/org/apache/calcite/sql/SqlDialect.java +++ b/core/src/main/java/org/apache/calcite/sql/SqlDialect.java @@ -18,6 +18,7 @@ package org.apache.calcite.sql; import org.apache.calcite.avatica.util.DateTimeUtils; import org.apache.calcite.config.NullCollation; +import org.apache.calcite.linq4j.function.Experimental; import org.apache.calcite.rel.RelFieldCollation; import org.apache.calcite.rel.type.RelDataType; import org.apache.calcite.sql.dialect.AnsiSqlDialect; @@ -584,6 +585,18 @@ public class SqlDialect { } /** + * Returns whether the dialect supports VALUES in a sub-query with + * and an "AS t(column, ...)" values to define column names. + * + * <p>Currently, only Oracle does not. For this, we generate "SELECT v0 AS c0, + * v1 AS c1 ... UNION ALL ...". We may need to refactor this method when we + * support VALUES for other dialects. */ + @Experimental + public boolean supportsAliasedValues() { + return true; + } + + /** * A few utility functions copied from org.apache.calcite.util.Util. We have * copied them because we wish to keep SqlDialect's dependencies to a * minimum. http://git-wip-us.apache.org/repos/asf/calcite/blob/fb760a6f/core/src/main/java/org/apache/calcite/sql/dialect/OracleSqlDialect.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/sql/dialect/OracleSqlDialect.java b/core/src/main/java/org/apache/calcite/sql/dialect/OracleSqlDialect.java index cdac9d7..aa16ddb 100644 --- a/core/src/main/java/org/apache/calcite/sql/dialect/OracleSqlDialect.java +++ b/core/src/main/java/org/apache/calcite/sql/dialect/OracleSqlDialect.java @@ -48,6 +48,10 @@ public class OracleSqlDialect extends SqlDialect { return false; } + @Override public boolean supportsAliasedValues() { + return false; + } + @Override public void unparseCall(SqlWriter writer, SqlCall call, int leftPrec, int rightPrec) { if (call.getOperator() == SqlStdOperatorTable.SUBSTRING) { http://git-wip-us.apache.org/repos/asf/calcite/blob/fb760a6f/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java ---------------------------------------------------------------------- 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 9967bd5..9057f80 100644 --- a/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java +++ b/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java @@ -46,6 +46,7 @@ import org.apache.calcite.rel.core.RelFactories; import org.apache.calcite.rel.core.Sample; import org.apache.calcite.rel.core.Sort; import org.apache.calcite.rel.core.Uncollect; +import org.apache.calcite.rel.core.Values; import org.apache.calcite.rel.logical.LogicalAggregate; import org.apache.calcite.rel.logical.LogicalCorrelate; import org.apache.calcite.rel.logical.LogicalFilter; @@ -1931,7 +1932,16 @@ public class SqlToRelConverter { return; case AS: - convertFrom(bb, ((SqlCall) from).operand(0)); + call = (SqlCall) from; + convertFrom(bb, call.operand(0)); + if (call.operandCount() > 2 + && bb.root instanceof Values) { + final List<String> fieldNames = new ArrayList<>(); + for (SqlNode node : Util.skip(call.getOperandList(), 2)) { + fieldNames.add(((SqlIdentifier) node).getSimple()); + } + bb.setRoot(relBuilder.push(bb.root).rename(fieldNames).build(), true); + } return; case WITH_ITEM: http://git-wip-us.apache.org/repos/asf/calcite/blob/fb760a6f/core/src/main/java/org/apache/calcite/tools/RelBuilder.java ---------------------------------------------------------------------- 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 6499f42..c131f8e 100644 --- a/core/src/main/java/org/apache/calcite/tools/RelBuilder.java +++ b/core/src/main/java/org/apache/calcite/tools/RelBuilder.java @@ -1084,6 +1084,17 @@ public class RelBuilder { if (oldFieldNames.equals(newFieldNames)) { return this; } + if (peek() instanceof Values) { + // Special treatment for VALUES. Re-build it rather than add a project. + final Values v = (Values) build(); + final RelDataTypeFactory.Builder b = getTypeFactory().builder(); + for (Pair<String, RelDataTypeField> p + : Pair.zip(newFieldNames, v.getRowType().getFieldList())) { + b.add(p.left, p.right.getType()); + } + return values(v.tuples, b.build()); + } + project(fields(), newFieldNames, true); // If, after de-duplication, the field names are unchanged, discard the http://git-wip-us.apache.org/repos/asf/calcite/blob/fb760a6f/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java ---------------------------------------------------------------------- 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 6716d3d..a8682f8 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 @@ -28,7 +28,6 @@ import org.apache.calcite.rel.rules.UnionMergeRule; import org.apache.calcite.runtime.FlatLists; import org.apache.calcite.schema.SchemaPlus; import org.apache.calcite.sql.SqlDialect; -import org.apache.calcite.sql.SqlDialect.DatabaseProduct; import org.apache.calcite.sql.SqlNode; import org.apache.calcite.sql.dialect.CalciteSqlDialect; import org.apache.calcite.sql.parser.SqlParser; @@ -222,13 +221,13 @@ public class RelToSqlConverterTest { + "FROM \"foodmart\".\"product\"\n" + "GROUP BY \"product_id\") AS \"t1\""; sql(query) - .dialect(DatabaseProduct.ORACLE.getDialect()) + .withOracle() .ok(expectedOracle) - .dialect(DatabaseProduct.MYSQL.getDialect()) + .withMysql() .ok(expectedMySQL) - .dialect(DatabaseProduct.VERTICA.getDialect()) + .withVertica() .ok(expectedVertica) - .dialect(DatabaseProduct.POSTGRESQL.getDialect()) + .withPostgresql() .ok(expectedPostgresql); } @@ -338,8 +337,7 @@ public class RelToSqlConverterTest { final String expected = "SELECT product_id\n" + "FROM foodmart.product\n" + "LIMIT 100\nOFFSET 10"; - sql(query).dialect(SqlDialect.DatabaseProduct.HIVE.getDialect()) - .ok(expected); + sql(query).withHive().ok(expected); } @Test public void testSelectQueryWithLimitClauseWithoutOrder() { @@ -446,7 +444,7 @@ public class RelToSqlConverterTest { + "INNER JOIN (SELECT sales_fact_19970.customer_id\n" + "FROM foodmart.sales_fact_1997 AS sales_fact_19970) AS t0 ON t.customer_id = t0.customer_id"; - sql(query).dialect(DatabaseProduct.DB2.getDialect()).ok(expected); + sql(query).withDb2().ok(expected); } @Test public void testCartesianProductWithCommaSyntax() { @@ -501,7 +499,7 @@ public class RelToSqlConverterTest { + "FROM foodmart.employee AS employee\n" + "INNER JOIN foodmart.department AS department " + "ON employee.department_id = department.department_id"; - sql(query).dialect(DatabaseProduct.DB2.getDialect()).ok(expected); + sql(query).withDb2().ok(expected); } @Test public void testDb2DialectSelfJoinStar() { @@ -512,7 +510,7 @@ public class RelToSqlConverterTest { + "FROM foodmart.employee AS employee\n" + "INNER JOIN foodmart.employee AS employee0 " + "ON employee.department_id = employee0.department_id"; - sql(query).dialect(DatabaseProduct.DB2.getDialect()).ok(expected); + sql(query).withDb2().ok(expected); } @Test public void testDb2DialectJoin() { @@ -524,7 +522,7 @@ public class RelToSqlConverterTest { + "FROM foodmart.employee AS employee\n" + "INNER JOIN foodmart.department AS department " + "ON employee.department_id = department.department_id"; - sql(query).dialect(DatabaseProduct.DB2.getDialect()).ok(expected); + sql(query).withDb2().ok(expected); } @Test public void testDb2DialectSelfJoin() { @@ -536,7 +534,7 @@ public class RelToSqlConverterTest { + "FROM foodmart.employee AS employee\n" + "INNER JOIN foodmart.employee AS employee0 " + "ON employee.department_id = employee0.department_id"; - sql(query).dialect(DatabaseProduct.DB2.getDialect()).ok(expected); + sql(query).withDb2().ok(expected); } @Test public void testDb2DialectWhere() { @@ -545,7 +543,7 @@ public class RelToSqlConverterTest { final String expected = "SELECT employee.employee_id\n" + "FROM foodmart.employee AS employee\n" + "WHERE employee.department_id < 1000"; - sql(query).dialect(DatabaseProduct.DB2.getDialect()).ok(expected); + sql(query).withDb2().ok(expected); } @Test public void testDb2DialectJoinWhere() { @@ -559,7 +557,7 @@ public class RelToSqlConverterTest { + "INNER JOIN foodmart.department AS department " + "ON employee.department_id = department.department_id\n" + "WHERE employee.employee_id < 1000"; - sql(query).dialect(DatabaseProduct.DB2.getDialect()).ok(expected); + sql(query).withDb2().ok(expected); } @Test public void testDb2DialectSelfJoinWhere() { @@ -573,7 +571,7 @@ public class RelToSqlConverterTest { + "INNER JOIN foodmart.employee AS employee0 " + "ON employee.department_id = employee0.department_id\n" + "WHERE employee0.employee_id < 2000"; - sql(query).dialect(DatabaseProduct.DB2.getDialect()).ok(expected); + sql(query).withDb2().ok(expected); } @Test public void testDb2DialectCast() { @@ -582,7 +580,7 @@ public class RelToSqlConverterTest { final String expected = "SELECT reserve_employee.hire_date, " + "CAST(reserve_employee.hire_date AS VARCHAR(10))\n" + "FROM foodmart.reserve_employee AS reserve_employee"; - sql(query).dialect(DatabaseProduct.DB2.getDialect()).ok(expected); + sql(query).withDb2().ok(expected); } @Test public void testDb2DialectSelectQueryWithGroupByHaving() { @@ -593,7 +591,7 @@ public class RelToSqlConverterTest { + "FROM foodmart.product AS product\n" + "GROUP BY product.product_class_id, product.product_id\n" + "HAVING product.product_id > 10"; - sql(query).dialect(DatabaseProduct.DB2.getDialect()).ok(expected); + sql(query).withDb2().ok(expected); } @@ -607,7 +605,7 @@ public class RelToSqlConverterTest { + "WHERE product.cases_per_pallet > 100\n" + "GROUP BY product.product_id, product.units_per_case\n" + "ORDER BY product.units_per_case DESC"; - sql(query).dialect(DatabaseProduct.DB2.getDialect()).ok(expected); + sql(query).withDb2().ok(expected); } @Test public void testDb2DialectSelectQueryWithGroup() { @@ -623,7 +621,7 @@ public class RelToSqlConverterTest { + "AND (reserve_employee.position_title = 'SDE' OR " + "reserve_employee.position_title = 'SDM')\n" + "GROUP BY reserve_employee.store_id, reserve_employee.position_title"; - sql(query).dialect(DatabaseProduct.DB2.getDialect()).ok(expected); + sql(query).withDb2().ok(expected); } /** Test case for @@ -646,7 +644,7 @@ public class RelToSqlConverterTest { sql(sql) .schema(CalciteAssert.SchemaSpec.JDBC_SCOTT) .ok(expected) - .dialect(DatabaseProduct.DB2.getDialect()) + .withDb2() .ok(expected2); } @@ -790,9 +788,9 @@ public class RelToSqlConverterTest { private void checkLiteral2(String expression, String expected) { sql("VALUES " + expression) - .dialect(DatabaseProduct.HSQLDB.getDialect()) + .withHsqldb() .ok("SELECT *\n" - + "FROM (VALUES (" + expected + "))"); + + "FROM (VALUES (" + expected + ")) AS t (EXPR$0)"); } /** Test case for @@ -802,7 +800,7 @@ public class RelToSqlConverterTest { String query = "SELECT floor(\"hire_date\" TO MINUTE) FROM \"employee\""; String expected = "SELECT TRUNC(hire_date, 'MI')\nFROM foodmart.employee"; sql(query) - .dialect(DatabaseProduct.HSQLDB.getDialect()) + .withHsqldb() .ok(expected); } @@ -810,7 +808,7 @@ public class RelToSqlConverterTest { String query = "SELECT floor(\"hire_date\" TO MINUTE) FROM \"employee\""; String expected = "SELECT DATE_TRUNC('MINUTE', \"hire_date\")\nFROM \"foodmart\".\"employee\""; sql(query) - .dialect(DatabaseProduct.POSTGRESQL.getDialect()) + .withPostgresql() .ok(expected); } @@ -818,7 +816,7 @@ public class RelToSqlConverterTest { String query = "SELECT floor(\"hire_date\" TO MINUTE) FROM \"employee\""; String expected = "SELECT TRUNC(\"hire_date\", 'MINUTE')\nFROM \"foodmart\".\"employee\""; sql(query) - .dialect(DatabaseProduct.ORACLE.getDialect()) + .withOracle() .ok(expected); } @@ -828,7 +826,7 @@ public class RelToSqlConverterTest { + "DATEADD(day, - (6 + DATEPART(weekday, [hire_date] )) % 7, [hire_date] ), 126))\n" + "FROM [foodmart].[employee]"; sql(query) - .dialect(DatabaseProduct.MSSQL.getDialect()) + .withMssql() .ok(expected); } @@ -837,7 +835,7 @@ public class RelToSqlConverterTest { String expected = "SELECT CONVERT(DATETIME, CONVERT(VARCHAR(7), [hire_date] , 126)+'-01')\n" + "FROM [foodmart].[employee]"; sql(query) - .dialect(DatabaseProduct.MSSQL.getDialect()) + .withMssql() .ok(expected); } @@ -846,7 +844,7 @@ public class RelToSqlConverterTest { String expected = "SELECT DATE_FORMAT(`hire_date`, '%Y-%m-01')\n" + "FROM `foodmart`.`employee`"; sql(query) - .dialect(DatabaseProduct.MYSQL.getDialect()) + .withMysql() .ok(expected); } @@ -855,7 +853,7 @@ public class RelToSqlConverterTest { String expected = "SELECT STR_TO_DATE(DATE_FORMAT(`hire_date` , '%x%v-1'), '%x%v-%w')\n" + "FROM `foodmart`.`employee`"; sql(query) - .dialect(DatabaseProduct.MYSQL.getDialect()) + .withMysql() .ok(expected); } @@ -880,13 +878,13 @@ public class RelToSqlConverterTest { + "FROM `foodmart`.`employee`\n" + "GROUP BY DATE_FORMAT(`hire_date`, '%Y-%m-%d %k:%i:00')"; sql(query) - .dialect(DatabaseProduct.HSQLDB.getDialect()) + .withHsqldb() .ok(expected) - .dialect(DatabaseProduct.ORACLE.getDialect()) + .withOracle() .ok(expectedOracle) - .dialect(DatabaseProduct.POSTGRESQL.getDialect()) + .withPostgresql() .ok(expectedPostgresql) - .dialect(DatabaseProduct.MYSQL.getDialect()) + .withMysql() .ok(expectedMysql); } @@ -900,13 +898,13 @@ public class RelToSqlConverterTest { final String expectedMysql = "SELECT SUBSTRING(`brand_name` FROM 2)\n" + "FROM `foodmart`.`product`"; sql(query) - .dialect(DatabaseProduct.ORACLE.getDialect()) + .withOracle() .ok(expectedOracle) - .dialect(DatabaseProduct.POSTGRESQL.getDialect()) + .withPostgresql() .ok(expectedPostgresql) - .dialect(DatabaseProduct.MYSQL.getDialect()) + .withMysql() .ok(expectedMysql) - .dialect(DatabaseProduct.MSSQL.getDialect()) + .withMssql() // mssql does not support this syntax and so should fail .throws_("MSSQL SUBSTRING requires FROM and FOR arguments"); } @@ -923,13 +921,13 @@ public class RelToSqlConverterTest { final String expectedMssql = "SELECT SUBSTRING([brand_name], 2, 3)\n" + "FROM [foodmart].[product]"; sql(query) - .dialect(DatabaseProduct.ORACLE.getDialect()) + .withOracle() .ok(expectedOracle) - .dialect(DatabaseProduct.POSTGRESQL.getDialect()) + .withPostgresql() .ok(expectedPostgresql) - .dialect(DatabaseProduct.MYSQL.getDialect()) + .withMysql() .ok(expectedMysql) - .dialect(DatabaseProduct.MSSQL.getDialect()) + .withMssql() .ok(expectedMssql); } @@ -2013,6 +2011,30 @@ public class RelToSqlConverterTest { sql(sql).ok(expected); } + @Test public void testValues() { + final String sql = "select \"a\"\n" + + "from (values (1, 'x'), (2, 'yy')) as t(\"a\", \"b\")"; + final String expectedHsqldb = "SELECT a\n" + + "FROM (VALUES (1, 'x '),\n" + + " (2, 'yy')) AS t (a, b)"; + final String expectedPostgresql = "SELECT \"a\"\n" + + "FROM (VALUES (1, 'x '),\n" + + " (2, 'yy')) AS \"t\" (\"a\", \"b\")"; + final String expectedOracle = "SELECT \"a\"\n" + + "FROM (SELECT 1 \"a\", 'x ' \"b\"\n" + + "FROM \"DUAL\"\n" + + "UNION ALL\n" + + "SELECT 2 \"a\", 'yy' \"b\"\n" + + "FROM \"DUAL\")"; + sql(sql) + .withHsqldb() + .ok(expectedHsqldb) + .withPostgresql() + .ok(expectedPostgresql) + .withOracle() + .ok(expectedOracle); + } + /** Fluid interface to run tests. */ private static class Sql { private CalciteAssert.SchemaSpec schemaSpec; @@ -2035,6 +2057,38 @@ public class RelToSqlConverterTest { return new Sql(schemaSpec, sql, dialect, config, transforms); } + Sql withDb2() { + return dialect(SqlDialect.DatabaseProduct.DB2.getDialect()); + } + + Sql withHive() { + return dialect(SqlDialect.DatabaseProduct.HIVE.getDialect()); + } + + Sql withHsqldb() { + return dialect(SqlDialect.DatabaseProduct.HSQLDB.getDialect()); + } + + Sql withMssql() { + return dialect(SqlDialect.DatabaseProduct.MSSQL.getDialect()); + } + + Sql withMysql() { + return dialect(SqlDialect.DatabaseProduct.MYSQL.getDialect()); + } + + Sql withOracle() { + return dialect(SqlDialect.DatabaseProduct.ORACLE.getDialect()); + } + + Sql withPostgresql() { + return dialect(SqlDialect.DatabaseProduct.POSTGRESQL.getDialect()); + } + + Sql withVertica() { + return dialect(SqlDialect.DatabaseProduct.VERTICA.getDialect()); + } + Sql config(SqlToRelConverter.Config config) { return new Sql(schemaSpec, sql, dialect, config, transforms); } http://git-wip-us.apache.org/repos/asf/calcite/blob/fb760a6f/core/src/test/java/org/apache/calcite/test/JdbcAdapterTest.java ---------------------------------------------------------------------- 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 336c361..c35ef8a 100644 --- a/core/src/test/java/org/apache/calcite/test/JdbcAdapterTest.java +++ b/core/src/test/java/org/apache/calcite/test/JdbcAdapterTest.java @@ -54,7 +54,7 @@ public class JdbcAdapterTest { @Test public void testValuesPlan() { final String sql = "select * from \"days\", (values 1, 2) as t(c)"; final String explain = "PLAN=" - + "EnumerableCalc(expr#0..2=[{inputs}], day=[$t1], week_day=[$t2], EXPR$0=[$t0])\n" + + "EnumerableCalc(expr#0..2=[{inputs}], day=[$t1], week_day=[$t2], C=[$t0])\n" + " EnumerableJoin(condition=[true], joinType=[inner])\n" + " EnumerableValues(tuples=[[{ 1 }, { 2 }]])\n" + " JdbcToEnumerableConverter\n" http://git-wip-us.apache.org/repos/asf/calcite/blob/fb760a6f/core/src/test/java/org/apache/calcite/test/RelBuilderTest.java ---------------------------------------------------------------------- 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 93e3b30..a17a154 100644 --- a/core/src/test/java/org/apache/calcite/test/RelBuilderTest.java +++ b/core/src/test/java/org/apache/calcite/test/RelBuilderTest.java @@ -549,6 +549,24 @@ public class RelBuilderTest { } } + @Test public void testRenameValues() { + final RelBuilder builder = RelBuilder.create(config().build()); + RelNode root = + builder.values(new String[]{"a", "b"}, true, 1, false, -50) + .build(); + final String expected = + "LogicalValues(tuples=[[{ true, 1 }, { false, -50 }]])\n"; + assertThat(str(root), is(expected)); + + // When you rename Values, you get a Values with a new row type, no Project + root = + builder.push(root) + .rename(ImmutableList.of("x", "y z")) + .build(); + assertThat(str(root), is(expected)); + assertThat(root.getRowType().getFieldNames().toString(), is("[x, y z]")); + } + @Test public void testPermute() { final RelBuilder builder = RelBuilder.create(config().build()); RelNode root = http://git-wip-us.apache.org/repos/asf/calcite/blob/fb760a6f/core/src/test/java/org/apache/calcite/tools/FrameworksTest.java ---------------------------------------------------------------------- diff --git a/core/src/test/java/org/apache/calcite/tools/FrameworksTest.java b/core/src/test/java/org/apache/calcite/tools/FrameworksTest.java index 6de282e..6a89266 100644 --- a/core/src/test/java/org/apache/calcite/tools/FrameworksTest.java +++ b/core/src/test/java/org/apache/calcite/tools/FrameworksTest.java @@ -18,6 +18,7 @@ package org.apache.calcite.tools; import org.apache.calcite.adapter.enumerable.EnumerableConvention; import org.apache.calcite.adapter.enumerable.EnumerableTableScan; +import org.apache.calcite.jdbc.CalciteConnection; import org.apache.calcite.plan.RelOptAbstractTable; import org.apache.calcite.plan.RelOptCluster; import org.apache.calcite.plan.RelOptPlanner; @@ -48,6 +49,8 @@ import org.apache.calcite.sql.type.SqlTypeName; import org.apache.calcite.test.CalciteAssert; import org.apache.calcite.util.Util; +import com.google.common.base.Function; + import org.junit.Test; import java.math.BigDecimal; @@ -224,6 +227,58 @@ public class FrameworksTest { } } + /** Test case for + * <a href="https://issues.apache.org/jira/browse/CALCITE-1996";>[CALCITE-1996] + * VALUES syntax</a>. + * + * <p>With that bug, running a VALUES query would succeed before running a + * query that reads from a JDBC table, but fail after it. Before, the plan + * would use {@link org.apache.calcite.adapter.enumerable.EnumerableValues}, + * but after, it would use + * {@link org.apache.calcite.adapter.jdbc.JdbcRules.JdbcValues}, and would + * generate invalid SQL syntax. + * + * <p>Even though the SQL generator has been fixed, we are still interested in + * how JDBC convention gets lodged in the planner's state. */ + @Test public void testJdbcValues() throws Exception { + CalciteAssert.that() + .with(CalciteAssert.SchemaSpec.JDBC_SCOTT) + .doWithConnection(new Function<CalciteConnection, Void>() { + public Void apply(CalciteConnection conn) { + try { + final FrameworkConfig config = Frameworks.newConfigBuilder() + .defaultSchema(conn.getRootSchema()) + .build(); + final RelBuilder builder = RelBuilder.create(config); + final RelRunner runner = conn.unwrap(RelRunner.class); + + final RelNode values = + builder.values(new String[]{"a", "b"}, "X", 1, "Y", 2) + .project(builder.field("a")) + .build(); + + // If you run the "values" query before the "scan" query, + // everything works fine. JdbcValues is never instantiated in any + // of the 3 queries. + if (false) { + runner.prepare(values).executeQuery(); + } + + final RelNode scan = builder.scan("JDBC_SCOTT", "EMP").build(); + runner.prepare(scan).executeQuery(); + builder.clear(); + + // running this after the scott query causes the exception + RelRunner runner2 = conn.unwrap(RelRunner.class); + runner2.prepare(values).executeQuery(); + return null; + } catch (Exception e) { + throw new RuntimeException(e); + } + } + }); + } + /** Dummy type system, similar to Hive's, accessed via an INSTANCE member. */ public static class HiveLikeTypeSystem extends RelDataTypeSystemImpl { public static final RelDataTypeSystem INSTANCE = new HiveLikeTypeSystem(); http://git-wip-us.apache.org/repos/asf/calcite/blob/fb760a6f/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml ---------------------------------------------------------------------- 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 41fce01..fb92801 100644 --- a/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml +++ b/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml @@ -2034,7 +2034,7 @@ group by "$f2"]]> <Resource name="plan"> <![CDATA[ LogicalAggregate(group=[{0}], EXPR$1=[MAX($1)], EXPR$2=[MAX($2)]) - LogicalProject($f2=[$0], EXPR$1=[$1], $f3=[+($1, 1)]) + LogicalProject($f2=[$0], X=[$1], $f3=[+($1, 1)]) LogicalValues(tuples=[[{ 1, 2 }]]) ]]> </Resource> @@ -2250,7 +2250,7 @@ group by cube(a, b)]]> <![CDATA[ LogicalProject(EXPR$0=[1]) LogicalAggregate(group=[{0, 1}], groups=[[{0, 1}, {0}, {1}, {}]]) - LogicalProject(EXPR$0=[$0], EXPR$1=[$1]) + LogicalProject(A=[$0], B=[$1]) LogicalValues(tuples=[[{ 1, 2, 3, 4 }]]) ]]> </Resource> @@ -2265,7 +2265,7 @@ group by rollup(b, (a, d))]]> <![CDATA[ LogicalProject(EXPR$0=[1]) LogicalAggregate(group=[{0, 1, 2}], groups=[[{0, 1, 2}, {0}, {}]]) - LogicalProject(EXPR$1=[$1], EXPR$0=[$0], EXPR$3=[$3]) + LogicalProject(B=[$1], A=[$0], D=[$3]) LogicalValues(tuples=[[{ 1, 2, 3, 4 }]]) ]]> </Resource> @@ -3631,7 +3631,7 @@ group by d.deptno]]> <Resource name="plan"> <![CDATA[ LogicalAggregate(group=[{0}], EMPID=[MIN($1)]) - LogicalProject(DEPTNO=[$3], EXPR$0=[$0]) + LogicalProject(DEPTNO=[$3], EMPID=[$0]) LogicalJoin(condition=[=($2, $3)], joinType=[inner]) LogicalValues(tuples=[[{ 100, 'Bill', 1 }]]) LogicalValues(tuples=[[{ 1, 'LeaderShip' }]])
