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
The following commit(s) were added to refs/heads/master by this push:
new e863294 [CALCITE-3210] JDBC adapter should generate "CAST(NULL AS
type)" rather than "NULL" conditionally (Wang Weidong)
e863294 is described below
commit e863294ccfbed9dd520c999f75ed0bbe03f9fb1d
Author: Wang Weidong <[email protected]>
AuthorDate: Tue Aug 20 21:05:29 2019 +0800
[CALCITE-3210] JDBC adapter should generate "CAST(NULL AS type)" rather
than "NULL" conditionally (Wang Weidong)
Always wrap NULL literal in Project project list with CAST operator
during rel-to-sql conversion.
close apache/calcite#1338
---
.../calcite/rel/rel2sql/RelToSqlConverter.java | 16 ++++
.../calcite/rel/rel2sql/RelToSqlConverterTest.java | 89 ++++++++++++++++++++++
2 files changed, 105 insertions(+)
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 0899ea4..cc1070c 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
@@ -41,6 +41,7 @@ import org.apache.calcite.rel.core.Values;
import org.apache.calcite.rel.logical.LogicalProject;
import org.apache.calcite.rel.logical.LogicalSort;
import org.apache.calcite.rel.type.RelDataType;
+import org.apache.calcite.rel.type.RelDataTypeField;
import org.apache.calcite.rex.RexBuilder;
import org.apache.calcite.rex.RexCall;
import org.apache.calcite.rex.RexInputRef;
@@ -64,6 +65,7 @@ import org.apache.calcite.sql.SqlNode;
import org.apache.calcite.sql.SqlNodeList;
import org.apache.calcite.sql.SqlSelect;
import org.apache.calcite.sql.SqlUpdate;
+import org.apache.calcite.sql.SqlUtil;
import org.apache.calcite.sql.fun.SqlRowOperator;
import org.apache.calcite.sql.fun.SqlSingleValueAggFunction;
import org.apache.calcite.sql.fun.SqlStdOperatorTable;
@@ -199,6 +201,9 @@ public class RelToSqlConverter extends SqlImplementor
final List<SqlNode> selectList = new ArrayList<>();
for (RexNode ref : e.getChildExps()) {
SqlNode sqlExpr = builder.context.toSql(null, ref);
+ if (SqlUtil.isNullLiteral(sqlExpr, false)) {
+ sqlExpr = castNullType(sqlExpr,
e.getRowType().getFieldList().get(selectList.size()));
+ }
addSelect(selectList, sqlExpr, e.getRowType());
}
@@ -206,6 +211,17 @@ 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.
+ */
+ private SqlNode castNullType(SqlNode sqlNodeNull, RelDataTypeField field) {
+ return SqlStdOperatorTable.CAST.createCall(POS,
+ sqlNodeNull, dialect.getCastSpec(field.getType()));
+ }
+
/** @see #dispatch */
public Result visit(Aggregate e) {
return visitAggregate(e, e.getGroupSet().toList());
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 5bebccc..7454730 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
@@ -3730,6 +3730,95 @@ public class RelToSqlConverterTest {
assertTrue(postgresqlDialect.supportsDataType(integerDataType));
}
+ @Test public void testSelectNull() {
+ String query = "SELECT CAST(NULL AS INT)";
+ final String expected = "SELECT CAST(NULL AS INTEGER)\n"
+ + "FROM (VALUES (0)) AS \"t\" (\"ZERO\")";
+ sql(query).ok(expected);
+ // validate
+ sql(expected).exec();
+ }
+
+ @Test public void testSelectNullWithCount() {
+ String query = "SELECT COUNT(CAST(NULL AS INT))";
+ final String expected = "SELECT COUNT(CAST(NULL AS INTEGER))\n"
+ + "FROM (VALUES (0)) AS \"t\" (\"ZERO\")";
+ sql(query).ok(expected);
+ // validate
+ sql(expected).exec();
+ }
+
+ @Test public 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 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\")";
+ sql(query).ok(expected);
+ // validate
+ sql(expected).exec();
+ }
+
+ @Test public void testSelectNullWithGroupByVar() {
+ String query = "SELECT COUNT(CAST(NULL AS INT)) FROM \"account\"\n"
+ + "AS \"t\" GROUP BY \"account_type\"";
+ final String expected = "SELECT COUNT(CAST(NULL AS INTEGER))\n"
+ + "FROM \"foodmart\".\"account\"\n"
+ + "GROUP BY \"account_type\"";
+ sql(query).ok(expected);
+ // validate
+ sql(expected).exec();
+ }
+
+ @Test public 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 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\"))";
+ sql(query).ok(expected);
+ // validate
+ sql(expected).exec();
+ }
+
+ @Test public 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 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\")";
+ sql(query).ok(expected);
+ // validate
+ sql(expected).exec();
+ }
+
+
@Test public void testDialectQuoteStringLiteral() {
dialects().forEach((dialect, databaseProduct) -> {
assertThat(dialect.quoteStringLiteral(""), is("''"));
