You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@calcite.apache.org by jh...@apache.org on 2017/10/06 17:55:10 UTC
[2/2] calcite git commit: [CALCITE-1996] In JDBC adapter,
generate correct VALUES syntax
[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 <jh...@apache.org>
Authored: Wed Oct 4 11:56:57 2017 -0700
Committer: Julian Hyde <jh...@apache.org>
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' }]])