You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@calcite.apache.org by da...@apache.org on 2019/07/01 02:30:52 UTC
[calcite] 01/01: [CALCITE-3155] Empty LogicalValues can not be
converted to UNION_ALL without operands which can not be unparsed(Musbah EL
FIL)
This is an automated email from the ASF dual-hosted git repository.
danny0405 pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/calcite.git
commit 8dd4fed46ff31df8737788847fdd2963944295f9
Author: Musbah EL FIL <me...@murex.com>
AuthorDate: Sat Jun 29 16:28:47 2019 +0300
[CALCITE-3155] Empty LogicalValues can not be converted to UNION_ALL without operands which can not be unparsed(Musbah EL FIL)
Empty LogicalValues would be converted to
UNION_ALL Operator with no operands for rel to sql conversion,
which can not be unparsed to SQL string(invalid).
The fix converts the empty values to a "select NULL, NULL ...
from DUAL where 1 = 0"
Fix-ups(Danny Chan):
* Replace empty values check with Values.isEmpty(e)
* Always generate false condition as `1 = 0`
close apache/calcite#1286
---
.../calcite/rel/rel2sql/RelToSqlConverter.java | 57 +++++++++++++++++++---
.../calcite/rel/rel2sql/RelToSqlConverterTest.java | 33 +++++++++++++
2 files changed, 84 insertions(+), 6 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 3f391ee..dcc7e45 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
@@ -81,6 +81,7 @@ import com.google.common.collect.Ordering;
import java.util.ArrayDeque;
import java.util.ArrayList;
+import java.util.Arrays;
import java.util.Deque;
import java.util.LinkedHashSet;
import java.util.List;
@@ -89,6 +90,7 @@ import java.util.Map;
import java.util.Set;
import java.util.SortedSet;
import java.util.stream.Collectors;
+import java.util.stream.IntStream;
/**
* Utility to convert relational expressions to SQL abstract syntax tree.
@@ -390,7 +392,22 @@ public class RelToSqlConverter extends SqlImplementor
new SqlIdentifier("DUAL", POS), null, null,
null, null, null, null, null));
}
- if (list.size() == 1) {
+ if (list.isEmpty()) {
+ // In this case we need to construct the following query:
+ // SELECT NULL as C0, NULL as C1, NULL as C2 ... FROM DUAL WHERE FALSE
+ // This would return an empty result set with the same number of columns as the field names.
+ final List<SqlNode> nullColumnNames = new ArrayList<>();
+ for (String fieldName : fieldNames) {
+ SqlCall nullColumnName = SqlStdOperatorTable.AS.createCall(
+ POS, SqlLiteral.createNull(POS),
+ new SqlIdentifier(fieldName, POS));
+ nullColumnNames.add(nullColumnName);
+ }
+ query = new SqlSelect(POS, null,
+ new SqlNodeList(nullColumnNames, POS),
+ new SqlIdentifier("DUAL", POS), createAlwaysFalseCondition(), null,
+ null, null, null, null, null);
+ } else if (list.size() == 1) {
query = list.get(0);
} else {
query = SqlStdOperatorTable.UNION_ALL.createCall(
@@ -402,8 +419,19 @@ public class RelToSqlConverter extends SqlImplementor
// 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)));
+ final boolean isEmpty = Values.isEmpty(e);
+ if (isEmpty) {
+ // In case of empty values, we need to build:
+ // select * from VALUES(NULL, NULL ...) as T (C1, C2 ...)
+ // where 1=0.
+ List<SqlNode> nulls = IntStream.range(0, fieldNames.size())
+ .mapToObj(i ->
+ SqlLiteral.createNull(POS)).collect(Collectors.toList());
+ selects.add(ANONYMOUS_ROW.createCall(new SqlNodeList(nulls, POS)));
+ } else {
+ for (List<RexLiteral> tuple : e.getTuples()) {
+ selects.add(ANONYMOUS_ROW.createCall(exprList(context, tuple)));
+ }
}
query = SqlStdOperatorTable.VALUES.createCall(selects);
if (rename) {
@@ -415,10 +443,27 @@ public class RelToSqlConverter extends SqlImplementor
}
query = SqlStdOperatorTable.AS.createCall(POS, list);
}
+ if (isEmpty) {
+ query = new SqlSelect(POS, null,
+ null, query,
+ createAlwaysFalseCondition(),
+ null, null, null,
+ null, null, null);
+ }
}
return result(query, clauses, e, null);
}
+ private SqlNode createAlwaysFalseCondition() {
+ // Building the select query in the form:
+ // select * from VALUES(NULL,NULL ...) where 1=0
+ // Use condition 1=0 since "where false" does not seem to be supported
+ // on some DB vendors.
+ return SqlStdOperatorTable.EQUALS.createCall(POS,
+ Arrays.asList(SqlLiteral.createExactNumeric("1", POS),
+ SqlLiteral.createExactNumeric("0", POS)));
+ }
+
/** @see #dispatch */
public Result visit(Sort e) {
if (e.getInput() instanceof Aggregate) {
@@ -490,10 +535,10 @@ public class RelToSqlConverter extends SqlImplementor
return !dialect.supportsAggregateFunction(SqlKind.ROLLUP)
&& dialect.supportsGroupByWithRollup()
&& (aggregate.getGroupType() == Aggregate.Group.ROLLUP
- || aggregate.getGroupType() == Aggregate.Group.CUBE
- && aggregate.getGroupSet().cardinality() == 1)
+ || aggregate.getGroupType() == Aggregate.Group.CUBE
+ && aggregate.getGroupSet().cardinality() == 1)
&& e.collation.getFieldCollations().stream().allMatch(fc ->
- fc.getFieldIndex() < aggregate.getGroupSet().cardinality());
+ fc.getFieldIndex() < aggregate.getGroupSet().cardinality());
}
/** @see #dispatch */
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 ebaf443..95b2b05 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
@@ -3410,6 +3410,39 @@ public class RelToSqlConverterTest {
sql(query).ok(expected);
}
+ @Test public void testUnionAllWithNoOperandsUsingOracleDialect() {
+ String query = "select A.\"department_id\" "
+ + "from \"foodmart\".\"employee\" A "
+ + " where A.\"department_id\" = ( select min( A.\"department_id\") from \"foodmart\".\"department\" B where 1=2 )";
+ final String expected = "SELECT \"employee\".\"department_id\"\n"
+ + "FROM \"foodmart\".\"employee\"\n"
+ + "INNER JOIN (SELECT \"t1\".\"department_id\" \"department_id0\", MIN(\"t1\".\"department_id\")\n"
+ + "FROM (SELECT NULL \"department_id\", NULL \"department_description\"\nFROM \"DUAL\"\nWHERE 1 = 0) \"t\",\n"
+ + "(SELECT \"department_id\"\nFROM \"foodmart\".\"employee\"\nGROUP BY \"department_id\") \"t1\"\n"
+ + "GROUP BY \"t1\".\"department_id\") \"t3\" ON \"employee\".\"department_id\" = \"t3\".\"department_id0\""
+ + " AND \"employee\".\"department_id\" = MIN(\"t1\".\"department_id\")";
+ sql(query).withOracle().ok(expected);
+ }
+
+ @Test public void testUnionAllWithNoOperands() {
+ String query = "select A.\"department_id\" "
+ + "from \"foodmart\".\"employee\" A "
+ + " where A.\"department_id\" = ( select min( A.\"department_id\") from \"foodmart\".\"department\" B where 1=2 )";
+ final String expected = "SELECT \"employee\".\"department_id\"\n"
+ + "FROM \"foodmart\".\"employee\"\n"
+ + "INNER JOIN (SELECT \"t1\".\"department_id\" AS \"department_id0\","
+ + " MIN(\"t1\".\"department_id\")\n"
+ + "FROM (SELECT *\nFROM (VALUES (NULL, NULL))"
+ + " AS \"t\" (\"department_id\", \"department_description\")"
+ + "\nWHERE 1 = 0) AS \"t\","
+ + "\n(SELECT \"department_id\"\nFROM \"foodmart\".\"employee\""
+ + "\nGROUP BY \"department_id\") AS \"t1\""
+ + "\nGROUP BY \"t1\".\"department_id\") AS \"t3\" "
+ + "ON \"employee\".\"department_id\" = \"t3\".\"department_id0\""
+ + " AND \"employee\".\"department_id\" = MIN(\"t1\".\"department_id\")";
+ sql(query).ok(expected);
+ }
+
/** Fluid interface to run tests. */
static class Sql {
private final SchemaPlus schema;