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 2019/11/05 01:23:35 UTC
[calcite] branch master updated: [CALCITE-3475] JDBC adapter
generates invalid SQL for UNION ALL on BigQuery (Steven Talbot)
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 b2daea5 [CALCITE-3475] JDBC adapter generates invalid SQL for UNION ALL on BigQuery (Steven Talbot)
b2daea5 is described below
commit b2daea58fd3ca8b1e7bb8ee503078ff7b10e59d5
Author: Steven Talbot <st...@looker.com>
AuthorDate: Mon Nov 4 09:45:58 2019 -0800
[CALCITE-3475] JDBC adapter generates invalid SQL for UNION ALL on BigQuery (Steven Talbot)
Also, BigQuery does not support INTERSECT ALL and EXCEPT ALL. JDBC
adapter now throws if asked to generate SQL for these.
Close apache/calcite#1559
---
.../calcite/sql/dialect/BigQuerySqlDialect.java | 21 +++++++++++++-------
.../calcite/rel/rel2sql/RelToSqlConverterTest.java | 23 +++++++++++++++++++---
2 files changed, 34 insertions(+), 10 deletions(-)
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 dbd63f1..e3e1b75 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,19 +128,26 @@ public class BigQuerySqlDialect extends SqlDialect {
writer.endFunCall(frame);
break;
case UNION:
- if (!((SqlSetOperator) call.getOperator()).isAll()) {
- SqlSyntax.BINARY.unparse(writer, UNION_DISTINCT, call, leftPrec, rightPrec);
+ if (((SqlSetOperator) call.getOperator()).isAll()) {
+ super.unparseCall(writer, call, leftPrec, rightPrec);
+ } else {
+ SqlSyntax.BINARY.unparse(writer, UNION_DISTINCT, call, leftPrec,
+ rightPrec);
}
break;
case EXCEPT:
- if (!((SqlSetOperator) call.getOperator()).isAll()) {
- SqlSyntax.BINARY.unparse(writer, EXCEPT_DISTINCT, call, leftPrec, rightPrec);
+ if (((SqlSetOperator) call.getOperator()).isAll()) {
+ throw new RuntimeException("BigQuery does not support EXCEPT ALL");
}
+ SqlSyntax.BINARY.unparse(writer, EXCEPT_DISTINCT, call, leftPrec,
+ rightPrec);
break;
case INTERSECT:
- if (!((SqlSetOperator) call.getOperator()).isAll()) {
- SqlSyntax.BINARY.unparse(writer, INTERSECT_DISTINCT, call, leftPrec, rightPrec);
+ if (((SqlSetOperator) call.getOperator()).isAll()) {
+ throw new RuntimeException("BigQuery does not support INTERSECT ALL");
}
+ SqlSyntax.BINARY.unparse(writer, INTERSECT_DISTINCT, call, leftPrec,
+ rightPrec);
break;
default:
super.unparseCall(writer, call, leftPrec, rightPrec);
@@ -149,7 +156,7 @@ public class BigQuerySqlDialect extends SqlDialect {
/** BigQuery data type reference:
* <a href="https://cloud.google.com/bigquery/docs/reference/standard-sql/data-types">
- * Bigquery Standard SQL Data Types</a>
+ * BigQuery Standard SQL Data Types</a>
*/
@Override public SqlNode getCastSpec(final RelDataType type) {
if (type instanceof BasicSqlType) {
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 3a1aa61..1300eac 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
@@ -1093,7 +1093,20 @@ public class RelToSqlConverterTest {
+ "UNION select 1 from \"product\"";
final String expected = "SELECT MOD(11, 3)\n"
+ "FROM foodmart.product\n"
- + "UNION DISTINCT\nSELECT 1\nFROM foodmart.product";
+ + "UNION DISTINCT\n"
+ + "SELECT 1\n"
+ + "FROM foodmart.product";
+ sql(query).withBigQuery().ok(expected);
+ }
+
+ @Test public void testUnionAllOperatorForBigQuery() {
+ final String query = "select mod(11,3) from \"product\"\n"
+ + "UNION ALL select 1 from \"product\"";
+ final String expected = "SELECT MOD(11, 3)\n"
+ + "FROM foodmart.product\n"
+ + "UNION ALL\n"
+ + "SELECT 1\n"
+ + "FROM foodmart.product";
sql(query).withBigQuery().ok(expected);
}
@@ -1102,7 +1115,9 @@ public class RelToSqlConverterTest {
+ "INTERSECT select 1 from \"product\"";
final String expected = "SELECT MOD(11, 3)\n"
+ "FROM foodmart.product\n"
- + "INTERSECT DISTINCT\nSELECT 1\nFROM foodmart.product";
+ + "INTERSECT DISTINCT\n"
+ + "SELECT 1\n"
+ + "FROM foodmart.product";
sql(query).withBigQuery().ok(expected);
}
@@ -1111,7 +1126,9 @@ public class RelToSqlConverterTest {
+ "EXCEPT select 1 from \"product\"";
final String expected = "SELECT MOD(11, 3)\n"
+ "FROM foodmart.product\n"
- + "EXCEPT DISTINCT\nSELECT 1\nFROM foodmart.product";
+ + "EXCEPT DISTINCT\n"
+ + "SELECT 1\n"
+ + "FROM foodmart.product";
sql(query).withBigQuery().ok(expected);
}