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
The following commit(s) were added to refs/heads/master by this push:
new 27e7f19 [CALCITE-2833] In JDBC adapter for Hive and BigQuery,
implement Values by generating SELECT without FROM (Stuti Gupta)
27e7f19 is described below
commit 27e7f19a75033f44895d1c83efc6adf4f0e64856
Author: stuti.gupta <[email protected]>
AuthorDate: Tue Feb 19 15:27:07 2019 +0530
[CALCITE-2833] In JDBC adapter for Hive and BigQuery, implement Values by
generating SELECT without FROM (Stuti Gupta)
Since this PR was created, we fixed [CALCITE-3191], which
covers a lot of the original PR. The improvements for Hive
and BigQuery are still worthwhile, so I rebased and fixed up
the PR. (Julian Hyde)
Close apache/calcite#1052
---
.../calcite/rel/rel2sql/RelToSqlConverter.java | 8 +-
.../calcite/sql/dialect/BigQuerySqlDialect.java | 4 +
.../apache/calcite/sql/dialect/HiveSqlDialect.java | 4 +
.../calcite/rel/rel2sql/RelToSqlConverterTest.java | 89 +++++++++++-----------
4 files changed, 59 insertions(+), 46 deletions(-)
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 1c61f8f..d7ee35e 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
@@ -578,12 +578,18 @@ public class RelToSqlConverter extends SqlImplementor
|| !(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
+ // Some dialects (such as Oracle and BigQuery) don't 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
+ // for Oracle and
+ // SELECT v0 AS c0, v1 AS c1
+ // UNION ALL
+ // SELECT v2 AS c0, v3 AS c1
+ // for dialects that support SELECT-without-FROM.
List<SqlSelect> list = new ArrayList<>();
for (List<RexLiteral> tuple : e.getTuples()) {
final List<SqlNode> values2 = new ArrayList<>();
diff --git
a/core/src/main/java/org/apache/calcite/sql/dialect/BigQuerySqlDialect.java
b/core/src/main/java/org/apache/calcite/sql/dialect/BigQuerySqlDialect.java
index f857233..cb7dca3 100644
--- a/core/src/main/java/org/apache/calcite/sql/dialect/BigQuerySqlDialect.java
+++ b/core/src/main/java/org/apache/calcite/sql/dialect/BigQuerySqlDialect.java
@@ -128,6 +128,10 @@ public class BigQuerySqlDialect extends SqlDialect {
unparseFetchUsingLimit(writer, offset, fetch);
}
+ @Override public boolean supportsAliasedValues() {
+ return false;
+ }
+
@Override public void unparseCall(final SqlWriter writer, final SqlCall
call, final int leftPrec,
final int rightPrec) {
switch (call.getKind()) {
diff --git
a/core/src/main/java/org/apache/calcite/sql/dialect/HiveSqlDialect.java
b/core/src/main/java/org/apache/calcite/sql/dialect/HiveSqlDialect.java
index aab8748..91311e3 100644
--- a/core/src/main/java/org/apache/calcite/sql/dialect/HiveSqlDialect.java
+++ b/core/src/main/java/org/apache/calcite/sql/dialect/HiveSqlDialect.java
@@ -58,6 +58,10 @@ public class HiveSqlDialect extends SqlDialect {
return false;
}
+ @Override public boolean supportsAliasedValues() {
+ return false;
+ }
+
@Override public void unparseOffsetFetch(SqlWriter writer, SqlNode offset,
SqlNode fetch) {
unparseFetchUsingLimit(writer, offset, fetch);
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 839fcd8..dc75751 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
@@ -1337,8 +1337,7 @@ class RelToSqlConverterTest {
/** Test case for
* <a
href="https://issues.apache.org/jira/browse/CALCITE-3663";>[CALCITE-3663]
- * Support for TRIM function in Bigquery dialect</a>. */
-
+ * Support for TRIM function in BigQuery dialect</a>. */
@Test void testBqTrimWithLeadingChar() {
final String query = "SELECT TRIM(LEADING 'a' from 'abcd')\n"
+ "from \"foodmart\".\"reserve_employee\"";
@@ -4534,6 +4533,14 @@ class RelToSqlConverterTest {
+ "UNION ALL\n"
+ "SELECT 2 \"a\", 'yy' \"b\"\n"
+ "FROM \"DUAL\")";
+ final String expectedHive = "SELECT a\n"
+ + "FROM (SELECT 1 a, 'x ' b\n"
+ + "UNION ALL\n"
+ + "SELECT 2 a, 'yy' b)";
+ final String expectedBigQuery = "SELECT a\n"
+ + "FROM (SELECT 1 AS a, 'x ' AS b\n"
+ + "UNION ALL\n"
+ + "SELECT 2 AS a, 'yy' AS b)";
final String expectedSnowflake = expectedPostgresql;
final String expectedRedshift = expectedPostgresql;
sql(sql)
@@ -4545,6 +4552,10 @@ class RelToSqlConverterTest {
.ok(expectedPostgresql)
.withOracle()
.ok(expectedOracle)
+ .withHive()
+ .ok(expectedHive)
+ .withBigQuery()
+ .ok(expectedBigQuery)
.withSnowflake()
.ok(expectedSnowflake)
.withRedshift()
@@ -4596,6 +4607,14 @@ class RelToSqlConverterTest {
assertThat(toSql(root), isLinux(expectedSql));
}
+ @Test void testSelectWithoutFromEmulationForHiveAndBigQuery() {
+ String query = "select 2 + 2";
+ final String expected = "SELECT 2 + 2";
+ sql(query)
+ .withHive().ok(expected)
+ .withBigQuery().ok(expected);
+ }
+
/** Test case for
* <a
href="https://issues.apache.org/jira/browse/CALCITE-2118";>[CALCITE-2118]
* RelToSqlConverter should only generate "*" if field names match</a>. */
@@ -5261,53 +5280,39 @@ class RelToSqlConverterTest {
}
@Test void testRowValueExpression() {
- final String expected0 = "INSERT INTO SCOTT.DEPT (DEPTNO, DNAME, LOC)\n"
+ String sql = "insert into \"DEPT\"\n"
+ + "values ROW(1,'Fred', 'San Francisco'), ROW(2, 'Eric',
'Washington')";
+ final String expectedDefault = "INSERT INTO \"SCOTT\".\"DEPT\""
+ + " (\"DEPTNO\", \"DNAME\", \"LOC\")\n"
+ "SELECT 1, 'Fred', 'San Francisco'\n"
- + "FROM (VALUES (0)) t (ZERO)\n"
+ + "FROM (VALUES (0)) AS \"t\" (\"ZERO\")\n"
+ "UNION ALL\n"
+ "SELECT 2, 'Eric', 'Washington'\n"
- + "FROM (VALUES (0)) t (ZERO)";
- String sql = "insert into \"DEPT\"\n"
- + "values ROW(1,'Fred', 'San Francisco'), ROW(2, 'Eric',
'Washington')";
- sql(sql)
- .schema(CalciteAssert.SchemaSpec.JDBC_SCOTT)
- .withHive()
- .ok(expected0);
-
- final String expected1 = "INSERT INTO `SCOTT`.`DEPT` (`DEPTNO`, `DNAME`,
`LOC`)\n"
+ + "FROM (VALUES (0)) AS \"t\" (\"ZERO\")";
+ final String expectedHive = "INSERT INTO SCOTT.DEPT (DEPTNO, DNAME, LOC)\n"
+ "SELECT 1, 'Fred', 'San Francisco'\n"
+ "UNION ALL\n"
+ "SELECT 2, 'Eric', 'Washington'";
- sql(sql)
- .schema(CalciteAssert.SchemaSpec.JDBC_SCOTT)
- .withMysql()
- .ok(expected1);
-
- final String expected2 = "INSERT INTO \"SCOTT\".\"DEPT\" (\"DEPTNO\", "
- + "\"DNAME\", \"LOC\")\n"
+ final String expectedMysql = "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"
+ "SELECT 1, 'Fred', 'San Francisco'\n"
+ "FROM \"DUAL\"\n"
+ "UNION ALL\n"
+ "SELECT 2, 'Eric', 'Washington'\n"
+ "FROM \"DUAL\"";
- sql(sql)
- .schema(CalciteAssert.SchemaSpec.JDBC_SCOTT)
- .withOracle()
- .ok(expected2);
-
- final String expected3 = "INSERT INTO [SCOTT].[DEPT] ([DEPTNO], [DNAME],
[LOC])\n"
+ final String expectedMssql = "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])";
- sql(sql)
- .schema(CalciteAssert.SchemaSpec.JDBC_SCOTT)
- .withMssql()
- .ok(expected3);
-
- final String expected4 = "INSERT INTO \"SCOTT\".\"DEPT\" (\"DEPTNO\", "
- + "\"DNAME\", \"LOC\")\n"
+ final String expectedCalcite = "INSERT INTO \"SCOTT\".\"DEPT\""
+ + " (\"DEPTNO\", \"DNAME\", \"LOC\")\n"
+ "SELECT 1, 'Fred', 'San Francisco'\n"
+ "FROM (VALUES (0)) AS \"t\" (\"ZERO\")\n"
+ "UNION ALL\n"
@@ -5315,18 +5320,12 @@ class RelToSqlConverterTest {
+ "FROM (VALUES (0)) AS \"t\" (\"ZERO\")";
sql(sql)
.schema(CalciteAssert.SchemaSpec.JDBC_SCOTT)
- .ok(expected4);
-
- final String expected5 = "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\")";
- sql(sql).withCalcite()
- .schema(CalciteAssert.SchemaSpec.JDBC_SCOTT)
- .ok(expected5);
+ .ok(expectedDefault)
+ .withHive().ok(expectedHive)
+ .withMysql().ok(expectedMysql)
+ .withOracle().ok(expectedOracle)
+ .withMssql().ok(expectedMssql)
+ .withCalcite().ok(expectedCalcite);
}
@Test void testInsertValuesWithDynamicParams() {
