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 2020/08/07 22:52:50 UTC
[calcite] 01/02: [CALCITE-4150] JDBC adapter throws
UnsupportedOperationException when generating SQL for untyped NULL literal
(Anton Haidai)
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
commit f813298b67923e665fffee7bf4d388dcad994cb1
Author: Anton Haidai <an...@logianalytics.com>
AuthorDate: Mon Aug 3 10:43:23 2020 +0300
[CALCITE-4150] JDBC adapter throws UnsupportedOperationException when generating SQL for untyped NULL literal (Anton Haidai)
Fix handling of the NULL type in RelToSqlConverter and
SqlTypeUtil. For null literal with null type, we now generate
NULL rather than CAST(NULL AS NULL).
This can be overridden in the dialect if desired. (Julian Hyde)
Close apache/calcite#2093
---
.../calcite/rel/rel2sql/RelToSqlConverter.java | 25 +++--
.../java/org/apache/calcite/sql/SqlDialect.java | 8 +-
.../org/apache/calcite/sql/type/SqlTypeUtil.java | 16 ++-
.../validate/implicit/AbstractTypeCoercion.java | 2 +-
.../calcite/rel/rel2sql/RelToSqlConverterTest.java | 114 ++++++++++++---------
.../apache/calcite/sql/type/SqlTypeUtilTest.java | 4 +
.../apache/calcite/test/SqlToRelConverterTest.java | 12 +++
.../apache/calcite/test/SqlToRelConverterTest.xml | 33 ++++++
8 files changed, 151 insertions(+), 63 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 67325b3..b625dd0 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
@@ -339,8 +339,9 @@ public class RelToSqlConverter extends SqlImplementor
for (RexNode ref : e.getProjects()) {
SqlNode sqlExpr = builder.context.toSql(null, ref);
if (SqlUtil.isNullLiteral(sqlExpr, false)) {
- sqlExpr = castNullType(sqlExpr,
- e.getRowType().getFieldList().get(selectList.size()));
+ final RelDataTypeField field =
+ e.getRowType().getFieldList().get(selectList.size());
+ sqlExpr = castNullType(sqlExpr, field.getType());
}
addSelect(selectList, sqlExpr, e.getRowType());
}
@@ -350,15 +351,19 @@ public class RelToSqlConverter extends SqlImplementor
return builder.result();
}
- /**
- * Wrap the {@code sqlNodeNull} in a CAST operator with target type as {@code field}.
- * @param sqlNodeNull NULL literal
- * @param field field description of {@code sqlNodeNull}
- * @return null literal wrapped in CAST call.
+ /** Wraps a NULL literal in a CAST operator to a target type.
+ *
+ * @param nullLiteral NULL literal
+ * @param type Target type
+ *
+ * @return null literal wrapped in CAST call
*/
- private SqlNode castNullType(SqlNode sqlNodeNull, RelDataTypeField field) {
- return SqlStdOperatorTable.CAST.createCall(POS,
- sqlNodeNull, dialect.getCastSpec(field.getType()));
+ private SqlNode castNullType(SqlNode nullLiteral, RelDataType type) {
+ final SqlNode typeNode = dialect.getCastSpec(type);
+ if (typeNode == null) {
+ return nullLiteral;
+ }
+ return SqlStdOperatorTable.CAST.createCall(POS, nullLiteral, typeNode);
}
/** Visits a Window; called by {@link #dispatch} via reflection. */
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 5dedfc8..6a74591 100644
--- a/core/src/main/java/org/apache/calcite/sql/SqlDialect.java
+++ b/core/src/main/java/org/apache/calcite/sql/SqlDialect.java
@@ -763,11 +763,17 @@ public class SqlDialect {
}
/** Returns SqlNode for type in "cast(column as type)", which might be
- * different between databases by type name, precision etc. */
+ * different between databases by type name, precision etc.
+ *
+ * <p>If this method returns null, the cast will be omitted. In the default
+ * implementation, this is the case for the NULL type, and therefore
+ * {@code CAST(NULL AS <nulltype>)} is rendered as {@code NULL}. */
public SqlNode getCastSpec(RelDataType type) {
if (type instanceof BasicSqlType) {
int maxPrecision = -1;
switch (type.getSqlTypeName()) {
+ case NULL:
+ return null;
case VARCHAR:
// if needed, adjust varchar length to max length supported by the system
maxPrecision = getTypeSystem().getMaxPrecision(type.getSqlTypeName());
diff --git a/core/src/main/java/org/apache/calcite/sql/type/SqlTypeUtil.java b/core/src/main/java/org/apache/calcite/sql/type/SqlTypeUtil.java
index 7b7d44b..fce8b7b 100644
--- a/core/src/main/java/org/apache/calcite/sql/type/SqlTypeUtil.java
+++ b/core/src/main/java/org/apache/calcite/sql/type/SqlTypeUtil.java
@@ -478,7 +478,7 @@ public abstract class SqlTypeUtil {
return isExactNumeric(type) || isApproximateNumeric(type);
}
- /** Returns whether a type is null. */
+ /** Returns whether a type is the NULL type. */
public static boolean isNull(RelDataType type) {
SqlTypeName typeName = type.getSqlTypeName();
if (typeName == null) {
@@ -994,7 +994,7 @@ public abstract class SqlTypeUtil {
assert typeName != null;
final SqlTypeNameSpec typeNameSpec;
- if (isAtomic(type)) {
+ if (isAtomic(type) || isNull(type)) {
int precision = typeName.allowsPrec() ? type.getPrecision() : -1;
// fix up the precision.
if (maxPrecision > 0 && precision > maxPrecision) {
@@ -1609,13 +1609,21 @@ public abstract class SqlTypeUtil {
return SqlTypeFamily.CHARACTER.contains(type);
}
- /** Returns whether a type is a CHARACTER or contains a CHARACTER type. */
+ /** Returns whether a type is a CHARACTER or contains a CHARACTER type.
+ *
+ * @deprecated Use {@link #hasCharacter(RelDataType)} */
+ @Deprecated // to be removed before 2.0
public static boolean hasCharactor(RelDataType type) {
+ return hasCharacter(type);
+ }
+
+ /** Returns whether a type is a CHARACTER or contains a CHARACTER type. */
+ public static boolean hasCharacter(RelDataType type) {
if (isCharacter(type)) {
return true;
}
if (isArray(type)) {
- return hasCharactor(type.getComponentType());
+ return hasCharacter(type.getComponentType());
}
return false;
}
diff --git a/core/src/main/java/org/apache/calcite/sql/validate/implicit/AbstractTypeCoercion.java b/core/src/main/java/org/apache/calcite/sql/validate/implicit/AbstractTypeCoercion.java
index 86fd549..fcb424e 100644
--- a/core/src/main/java/org/apache/calcite/sql/validate/implicit/AbstractTypeCoercion.java
+++ b/core/src/main/java/org/apache/calcite/sql/validate/implicit/AbstractTypeCoercion.java
@@ -583,7 +583,7 @@ public abstract class AbstractTypeCoercion implements TypeCoercion {
List<RelDataType> nonCharacterTypes = new ArrayList<>();
for (RelDataType tp : types) {
- if (SqlTypeUtil.hasCharactor(tp)) {
+ if (SqlTypeUtil.hasCharacter(tp)) {
withCharacterTypes.add(tp);
} else {
nonCharacterTypes.add(tp);
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 9378bf2..8d07aee 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
@@ -5049,89 +5049,109 @@ class RelToSqlConverterTest {
assertTrue(postgresqlDialect.supportsDataType(integerDataType));
}
- @Test void testSelectNull() {
- String query = "SELECT CAST(NULL AS INT)";
+ /** Test case for
+ * <a href="https://issues.apache.org/jira/browse/CALCITE-4150">[CALCITE-4150]
+ * JDBC adapter throws UnsupportedOperationException when generating SQL
+ * for untyped NULL literal</a>. */
+ @Test void testSelectRawNull() {
+ final String query = "SELECT NULL FROM \"product\"";
+ final String expected = "SELECT NULL\n"
+ + "FROM \"foodmart\".\"product\"";
+ sql(query).ok(expected);
+ }
+
+ @Test void testSelectRawNullWithAlias() {
+ final String query = "SELECT NULL AS DUMMY FROM \"product\"";
+ final String expected = "SELECT NULL AS \"DUMMY\"\n"
+ + "FROM \"foodmart\".\"product\"";
+ sql(query).ok(expected);
+ }
+
+ @Test void testSelectNullWithCast() {
+ final String query = "SELECT CAST(NULL AS INT)";
final String expected = "SELECT CAST(NULL AS INTEGER)\n"
- + "FROM (VALUES (0)) AS \"t\" (\"ZERO\")";
+ + "FROM (VALUES (0)) AS \"t\" (\"ZERO\")";
sql(query).ok(expected);
// validate
sql(expected).exec();
}
@Test void testSelectNullWithCount() {
- String query = "SELECT COUNT(CAST(NULL AS INT))";
+ final String query = "SELECT COUNT(CAST(NULL AS INT))";
final String expected = "SELECT COUNT(CAST(NULL AS INTEGER))\n"
- + "FROM (VALUES (0)) AS \"t\" (\"ZERO\")";
+ + "FROM (VALUES (0)) AS \"t\" (\"ZERO\")";
sql(query).ok(expected);
// validate
sql(expected).exec();
}
@Test void testSelectNullWithGroupByNull() {
- String query = "SELECT COUNT(CAST(NULL AS INT)) FROM (VALUES (0))\n"
- + "AS \"t\" GROUP BY CAST(NULL AS VARCHAR CHARACTER SET \"ISO-8859-1\")";
+ final String query = "SELECT COUNT(CAST(NULL AS INT))\n"
+ + "FROM (VALUES (0))AS \"t\"\n"
+ + "GROUP BY CAST(NULL AS VARCHAR CHARACTER SET \"ISO-8859-1\")";
final String expected = "SELECT COUNT(CAST(NULL AS INTEGER))\n"
- + "FROM (VALUES (0)) AS \"t\" (\"EXPR$0\")\nGROUP BY CAST(NULL "
- + "AS VARCHAR CHARACTER SET \"ISO-8859-1\")";
+ + "FROM (VALUES (0)) AS \"t\" (\"EXPR$0\")\nGROUP BY CAST(NULL "
+ + "AS VARCHAR CHARACTER SET \"ISO-8859-1\")";
sql(query).ok(expected);
// validate
sql(expected).exec();
}
@Test void testSelectNullWithGroupByVar() {
- String query = "SELECT COUNT(CAST(NULL AS INT)) FROM \"account\"\n"
- + "AS \"t\" GROUP BY \"account_type\"";
+ final String query = "SELECT COUNT(CAST(NULL AS INT))\n"
+ + "FROM \"account\" AS \"t\"\n"
+ + "GROUP BY \"account_type\"";
final String expected = "SELECT COUNT(CAST(NULL AS INTEGER))\n"
- + "FROM \"foodmart\".\"account\"\n"
- + "GROUP BY \"account_type\"";
+ + "FROM \"foodmart\".\"account\"\n"
+ + "GROUP BY \"account_type\"";
sql(query).ok(expected);
// validate
sql(expected).exec();
}
@Test void testSelectNullWithInsert() {
- String query = "insert into\n"
- + "\"account\"(\"account_id\",\"account_parent\",\"account_type\",\"account_rollup\")\n"
- + "select 1, cast(NULL AS INT), cast(123 as varchar), cast(123 as varchar)";
+ final String query = "insert into\n"
+ + "\"account\"(\"account_id\",\"account_parent\",\"account_type\",\"account_rollup\")\n"
+ + "select 1, cast(NULL AS INT), cast(123 as varchar), cast(123 as varchar)";
final String expected = "INSERT INTO \"foodmart\".\"account\" ("
- + "\"account_id\", \"account_parent\", \"account_description\", "
- + "\"account_type\", \"account_rollup\", \"Custom_Members\")\n"
- + "(SELECT 1 AS \"account_id\", CAST(NULL AS INTEGER) AS \"account_parent\","
- + " CAST(NULL AS VARCHAR(30) CHARACTER SET "
- + "\"ISO-8859-1\") AS \"account_description\", '123' AS \"account_type\", "
- + "'123' AS \"account_rollup\", CAST(NULL AS VARCHAR"
- + "(255) CHARACTER SET \"ISO-8859-1\") AS \"Custom_Members\"\n"
- + "FROM (VALUES (0)) AS \"t\" (\"ZERO\"))";
+ + "\"account_id\", \"account_parent\", \"account_description\", "
+ + "\"account_type\", \"account_rollup\", \"Custom_Members\")\n"
+ + "(SELECT 1 AS \"account_id\", CAST(NULL AS INTEGER) AS \"account_parent\","
+ + " CAST(NULL AS VARCHAR(30) CHARACTER SET "
+ + "\"ISO-8859-1\") AS \"account_description\", '123' AS \"account_type\", "
+ + "'123' AS \"account_rollup\", CAST(NULL AS VARCHAR"
+ + "(255) CHARACTER SET \"ISO-8859-1\") AS \"Custom_Members\"\n"
+ + "FROM (VALUES (0)) AS \"t\" (\"ZERO\"))";
sql(query).ok(expected);
// validate
sql(expected).exec();
}
@Test void testSelectNullWithInsertFromJoin() {
- String query = "insert into\n"
- + "\"account\"(\"account_id\",\"account_parent\",\n"
- + "\"account_type\",\"account_rollup\")\n"
- + "select \"product\".\"product_id\",\n"
- + "cast(NULL AS INT),\n"
- + "cast(\"product\".\"product_id\" as varchar),\n"
- + "cast(\"sales_fact_1997\".\"store_id\" as varchar)\n"
- + "from \"product\"\n"
- + "inner join \"sales_fact_1997\"\n"
- + "on \"product\".\"product_id\" = \"sales_fact_1997\".\"product_id\"";
+ final String query = "insert into\n"
+ + "\"account\"(\"account_id\",\"account_parent\",\n"
+ + "\"account_type\",\"account_rollup\")\n"
+ + "select \"product\".\"product_id\",\n"
+ + "cast(NULL AS INT),\n"
+ + "cast(\"product\".\"product_id\" as varchar),\n"
+ + "cast(\"sales_fact_1997\".\"store_id\" as varchar)\n"
+ + "from \"product\"\n"
+ + "inner join \"sales_fact_1997\"\n"
+ + "on \"product\".\"product_id\" = \"sales_fact_1997\".\"product_id\"";
final String expected = "INSERT INTO \"foodmart\".\"account\" "
- + "(\"account_id\", \"account_parent\", \"account_description\", "
- + "\"account_type\", \"account_rollup\", \"Custom_Members\")\n"
- + "(SELECT \"product\".\"product_id\" AS \"account_id\", "
- + "CAST(NULL AS INTEGER) AS \"account_parent\", CAST(NULL AS VARCHAR"
- + "(30) CHARACTER SET \"ISO-8859-1\") AS \"account_description\", "
- + "CAST(\"product\".\"product_id\" AS VARCHAR CHARACTER SET "
- + "\"ISO-8859-1\") AS \"account_type\", "
- + "CAST(\"sales_fact_1997\".\"store_id\" AS VARCHAR CHARACTER SET \"ISO-8859-1\") AS "
- + "\"account_rollup\", "
- + "CAST(NULL AS VARCHAR(255) CHARACTER SET \"ISO-8859-1\") AS \"Custom_Members\"\n"
- + "FROM \"foodmart\".\"product\"\n"
- + "INNER JOIN \"foodmart\".\"sales_fact_1997\" "
- + "ON \"product\".\"product_id\" = \"sales_fact_1997\".\"product_id\")";
+ + "(\"account_id\", \"account_parent\", \"account_description\", "
+ + "\"account_type\", \"account_rollup\", \"Custom_Members\")\n"
+ + "(SELECT \"product\".\"product_id\" AS \"account_id\", "
+ + "CAST(NULL AS INTEGER) AS \"account_parent\", CAST(NULL AS VARCHAR"
+ + "(30) CHARACTER SET \"ISO-8859-1\") AS \"account_description\", "
+ + "CAST(\"product\".\"product_id\" AS VARCHAR CHARACTER SET "
+ + "\"ISO-8859-1\") AS \"account_type\", "
+ + "CAST(\"sales_fact_1997\".\"store_id\" AS VARCHAR CHARACTER SET \"ISO-8859-1\") AS "
+ + "\"account_rollup\", "
+ + "CAST(NULL AS VARCHAR(255) CHARACTER SET \"ISO-8859-1\") AS \"Custom_Members\"\n"
+ + "FROM \"foodmart\".\"product\"\n"
+ + "INNER JOIN \"foodmart\".\"sales_fact_1997\" "
+ + "ON \"product\".\"product_id\" = \"sales_fact_1997\".\"product_id\")";
sql(query).ok(expected);
// validate
sql(expected).exec();
diff --git a/core/src/test/java/org/apache/calcite/sql/type/SqlTypeUtilTest.java b/core/src/test/java/org/apache/calcite/sql/type/SqlTypeUtilTest.java
index 80cdf04..6e2ade3 100644
--- a/core/src/test/java/org/apache/calcite/sql/type/SqlTypeUtilTest.java
+++ b/core/src/test/java/org/apache/calcite/sql/type/SqlTypeUtilTest.java
@@ -151,6 +151,10 @@ class SqlTypeUtilTest {
}
@Test void testConvertTypeToSpec() {
+ SqlBasicTypeNameSpec nullSpec =
+ (SqlBasicTypeNameSpec) convertTypeToSpec(f.sqlNull).getTypeNameSpec();
+ assertThat(nullSpec.getTypeName().getSimple(), is("NULL"));
+
SqlBasicTypeNameSpec basicSpec =
(SqlBasicTypeNameSpec) convertTypeToSpec(f.sqlBigInt).getTypeNameSpec();
assertThat(basicSpec.getTypeName().getSimple(), is("BIGINT"));
diff --git a/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java b/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
index 741be09..0a146b8 100644
--- a/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
+++ b/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
@@ -607,6 +607,18 @@ class SqlToRelConverterTest extends SqlToRelTestBase {
sql("SELECT * FROM (VALUES (0, 0)) AS T(A, \"*\")").ok();
}
+ @Test void testSelectNull() {
+ sql("select null from emp").ok();
+ }
+
+ @Test void testSelectNullWithAlias() {
+ sql("select null as dummy from emp").ok();
+ }
+
+ @Test void testSelectNullWithCast() {
+ sql("select cast(null as timestamp) dummy from emp").ok();
+ }
+
@Test void testSelectDistinct() {
sql("select distinct sal + 5 from emp").ok();
}
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 35026af..2f88b96 100644
--- a/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
+++ b/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
@@ -1228,6 +1228,39 @@ LogicalProject(EXPR$0=[*($0, 3660000:INTERVAL HOUR TO MINUTE)])
]]>
</Resource>
</TestCase>
+ <TestCase name="testSelectNull">
+ <Resource name="sql">
+ <![CDATA[select null from emp]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalProject(EXPR$0=[null:NULL])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testSelectNullWithAlias">
+ <Resource name="sql">
+ <![CDATA[select null as dummy from emp]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalProject(DUMMY=[null:NULL])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testSelectNullWithCast">
+ <Resource name="sql">
+ <![CDATA[select cast(null as timestamp) dummy from emp]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalProject(DUMMY=[null:TIMESTAMP(0)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
<TestCase name="testSelectDistinct">
<Resource name="sql">
<![CDATA[select distinct sal + 5 from emp]]>