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 2023/03/22 21:08:03 UTC
[calcite] 01/02: [CALCITE-5557] Add SAFE_CAST function (enabled in BigQuery library)
This is an automated email from the ASF dual-hosted git repository.
jhyde pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/calcite.git
commit ba36004fb1e52b9bfb06623c7d869e6e01e25082
Author: Oliver Lee <ol...@google.com>
AuthorDate: Thu Mar 2 22:49:05 2023 +0000
[CALCITE-5557] Add SAFE_CAST function (enabled in BigQuery library)
SAFE_CAST is similar to CAST, except that it returns NULL
rather than throwing an error if conversion fails.
The Java code generation generates the same code as for CAST,
wraps it as a lambda, and converts the exception to Java null.
Add a wrapper around SqlOperatorFixture so that existing
tests for CAST can also test SAFE_CAST (Julian Hyde).
Close apache/calcite#3093
Co-authored-by: Oliver Lee <ol...@google.com>
Co-authored-by: Julian Hyde <jh...@apache.org>
---
babel/src/test/resources/sql/big-query.iq | 130 +++++
core/src/main/codegen/templates/Parser.jj | 10 +-
.../calcite/adapter/enumerable/RexImpTable.java | 5 +-
.../adapter/enumerable/RexToLixTranslator.java | 556 ++++++++++-----------
.../rules/AggregateProjectPullUpConstantsRule.java | 2 +-
.../calcite/rel/rules/ReduceExpressionsRule.java | 5 +-
.../rel/rules/UnionPullUpConstantsRule.java | 2 +-
.../apache/calcite/rel/rules/ValuesReduceRule.java | 6 +-
.../java/org/apache/calcite/rex/RexBuilder.java | 45 +-
.../org/apache/calcite/rex/RexCallBinding.java | 1 +
.../java/org/apache/calcite/rex/RexSimplify.java | 13 +-
.../main/java/org/apache/calcite/rex/RexUtil.java | 3 +-
.../java/org/apache/calcite/sql/SqlFunction.java | 11 +
.../main/java/org/apache/calcite/sql/SqlKind.java | 4 +
.../calcite/sql/SqlSplittableAggFunction.java | 2 +-
.../apache/calcite/sql/fun/SqlCastFunction.java | 62 ++-
.../calcite/sql/fun/SqlLibraryOperators.java | 9 +-
.../apache/calcite/sql2rel/SqlToRelConverter.java | 2 +-
.../calcite/sql2rel/StandardConvertletTable.java | 70 +--
.../org/apache/calcite/util/BuiltInMethod.java | 1 +
.../org/apache/calcite/plan/RelOptUtilTest.java | 4 +-
.../apache/calcite/rex/RexProgramBuilderBase.java | 2 +-
.../org/apache/calcite/rex/RexProgramTest.java | 13 +-
.../apache/calcite/sql/test/SqlAdvisorTest.java | 1 +
core/src/test/resources/sql/winagg.iq | 20 +
.../org/apache/calcite/piglet/PigRelBuilder.java | 3 +-
site/_docs/reference.md | 2 +
.../calcite/sql/test/SqlOperatorFixture.java | 59 ++-
.../test/RexImplicationCheckerFixtures.java | 2 +-
.../apache/calcite/test/SqlOperatorFixtures.java | 116 +++++
.../org/apache/calcite/test/SqlOperatorTest.java | 321 +++++++-----
31 files changed, 947 insertions(+), 535 deletions(-)
diff --git a/babel/src/test/resources/sql/big-query.iq b/babel/src/test/resources/sql/big-query.iq
index e3723221a0..fa7ecb90e8 100755
--- a/babel/src/test/resources/sql/big-query.iq
+++ b/babel/src/test/resources/sql/big-query.iq
@@ -762,6 +762,136 @@ SELECT LENGTH("hello") as length;
!ok
+#####################################################################
+# SAFE_CAST(x AS type)
+#
+# Identical to CAST(), except it returns NULL instead of raising an error.
+
+WITH Casted AS (
+ SELECT SAFE_CAST("a" as int) as casted, "a" as input, "int" as as UNION ALL
+ SELECT SAFE_CAST("a" as varchar(1)), "a", "varchar(1)" UNION ALL
+ SELECT SAFE_CAST("2023-03-07" as DATE), DATE("2023-03-07"), "date" UNION ALL
+ SELECT SAFE_CAST("2023-03-07a" as DATE), "2023-03-07a", "date" UNION ALL
+ SELECT SAFE_CAST(0 as BOOLEAN), 0, "boolean"
+)
+SELECT
+ *
+FROM Casted;
++------------+-------------+------------+
+| casted | input | as |
++------------+-------------+------------+
+| | a | int |
+| a | a | varchar(1) |
+| 2023-03-07 | 2023-03-07 | date |
+| | 2023-03-07a | date |
+| FALSE | 0 | boolean |
++------------+-------------+------------+
+(5 rows)
+
+!ok
+
+WITH Casted AS (
+ SELECT SAFE_CAST("12:12:11" as TIME) as casted,
+ "12:12:11" as input, "time" as as UNION ALL
+ SELECT SAFE_CAST("12:12:11a" as TIME), "12:12:11a", "time"
+)
+SELECT
+ *
+FROM Casted;
++----------+-----------+------+
+| casted | input | as |
++----------+-----------+------+
+| 12:12:11 | 12:12:11 | time |
+| | 12:12:11a | time |
++----------+-----------+------+
+(2 rows)
+
+!ok
+
+WITH Casted AS (
+ SELECT SAFE_CAST(TRUE as BOOLEAN) as casted, "true" as input,
+ "boolean" as as UNION ALL
+ SELECT SAFE_CAST(FALSE as BOOLEAN) as casted, "false" as input,
+ "boolean" as as
+)
+SELECT
+ *
+FROM Casted;
++--------+-------+---------+
+| casted | input | as |
++--------+-------+---------+
+| true | true | boolean |
+| false | false | boolean |
++--------+-------+---------+
+(2 rows)
+
+!ok
+
+WITH Casted AS (
+ SELECT SAFE_CAST(interval '12' month as interval year) as casted,
+ "interval 1 month" as input,
+ "interval year" as as UNION ALL
+ SELECT SAFE_CAST("a" as interval year), "a",
+ "interval year" UNION ALL
+ SELECT SAFE_CAST(null as interval year), "null", "interval year"
+)
+SELECT
+ *
+FROM Casted;
++--------+------------------+---------------+
+| casted | input | as |
++--------+------------------+---------------+
+| +1 | interval 1 month | interval year |
+| | a | interval year |
+| | null | interval year |
++--------+------------------+---------------+
+(3 rows)
+
+!ok
+
+WITH Casted AS (
+ SELECT SAFE_CAST(interval '1:1' hour to minute as interval minute to second)
+ as casted, "interval 1:1 hour to minute" as input,
+ "interval minute to second" as as UNION ALL
+ SELECT SAFE_CAST("a" as interval minute to second),
+ "a", "interval minute to second"
+)
+SELECT
+ *
+FROM Casted;
++---------------+-----------------------------+---------------------------+
+| casted | input | as |
++---------------+-----------------------------+---------------------------+
+| +61:00.000000 | interval 1:1 hour to minute | interval minute to second |
+| | a | interval minute to second |
++---------------+-----------------------------+---------------------------+
+(2 rows)
+
+!ok
+
+WITH Casted AS (
+ SELECT SAFE_CAST('true' as BIGINT) as casted, "true" as input,
+ "bigint" as as UNION ALL
+ SELECT SAFE_CAST(1.0 as BIGINT), "1.0", "bigint" UNION ALL
+ SELECT SAFE_CAST(1 as BIGINT), "1", "bigint" UNION ALL
+ SELECT SAFE_CAST(SAFE_CAST(TRUE AS BOOLEAN) AS BIGINT),
+ "TRUE", "bigint"
+)
+SELECT
+ *
+FROM Casted;
++--------+-------+--------+
+| casted | input | as |
++--------+-------+--------+
+| | true | bigint |
+| 1 | 1.0 | bigint |
+| 1 | 1 | bigint |
+| 1 | TRUE | bigint |
++--------+-------+--------+
+(4 rows)
+
+!ok
+
#####################################################################
# DATE
#
diff --git a/core/src/main/codegen/templates/Parser.jj b/core/src/main/codegen/templates/Parser.jj
index da7166b53d..fe6e354daa 100644
--- a/core/src/main/codegen/templates/Parser.jj
+++ b/core/src/main/codegen/templates/Parser.jj
@@ -54,6 +54,7 @@ import org.apache.calcite.sql.SqlDynamicParam;
import org.apache.calcite.sql.SqlExplain;
import org.apache.calcite.sql.SqlExplainFormat;
import org.apache.calcite.sql.SqlExplainLevel;
+import org.apache.calcite.sql.SqlFunction;
import org.apache.calcite.sql.SqlFunctionCategory;
import org.apache.calcite.sql.SqlHint;
import org.apache.calcite.sql.SqlIdentifier;
@@ -5996,11 +5997,15 @@ SqlNode BuiltinFunctionCall() :
final SqlIntervalQualifier unit;
final SqlNode node;
final SqlLiteral style; // mssql convert 'style' operand
+ final SqlFunction f;
}
{
//~ FUNCTIONS WITH SPECIAL SYNTAX ---------------------------------------
(
- <CAST> { s = span(); }
+ ( <CAST> { f = SqlStdOperatorTable.CAST; }
+ | <SAFE_CAST> { f = SqlLibraryOperators.SAFE_CAST; }
+ )
+ { s = span(); }
<LPAREN> AddExpression(args, ExprContext.ACCEPT_SUB_QUERY)
<AS>
(
@@ -6009,7 +6014,7 @@ SqlNode BuiltinFunctionCall() :
<INTERVAL> e = IntervalQualifier() { args.add(e); }
)
<RPAREN> {
- return SqlStdOperatorTable.CAST.createCall(s.end(this), args);
+ return f.createCall(s.end(this), args);
}
|
<EXTRACT> { s = span(); }
@@ -8189,6 +8194,7 @@ SqlPostfixOperator PostfixRowOperator() :
| < ROW_NUMBER: "ROW_NUMBER" >
| < ROWS: "ROWS" >
| < RUNNING: "RUNNING" >
+| < SAFE_CAST: "SAFE_CAST" >
| < SATURDAY: "SATURDAY" >
| < SAVEPOINT: "SAVEPOINT" >
| < SCALAR: "SCALAR" >
diff --git a/core/src/main/java/org/apache/calcite/adapter/enumerable/RexImpTable.java b/core/src/main/java/org/apache/calcite/adapter/enumerable/RexImpTable.java
index dabf91393d..6f74859d38 100644
--- a/core/src/main/java/org/apache/calcite/adapter/enumerable/RexImpTable.java
+++ b/core/src/main/java/org/apache/calcite/adapter/enumerable/RexImpTable.java
@@ -165,6 +165,7 @@ import static org.apache.calcite.sql.fun.SqlLibraryOperators.REVERSE;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.RIGHT;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.RLIKE;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.RPAD;
+import static org.apache.calcite.sql.fun.SqlLibraryOperators.SAFE_CAST;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.SHA1;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.SINH;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.SOUNDEX;
@@ -649,6 +650,7 @@ public class RexImpTable {
map.put(COALESCE, new CoalesceImplementor());
map.put(CAST, new CastImplementor());
+ map.put(SAFE_CAST, new CastImplementor());
map.put(REINTERPRET, new ReinterpretImplementor());
@@ -2895,8 +2897,9 @@ public class RexImpTable {
}
final RelDataType targetType =
nullifyType(translator.typeFactory, call.getType(), false);
+ boolean safe = call.getKind() == SqlKind.SAFE_CAST;
return translator.translateCast(sourceType,
- targetType, argValueList.get(0));
+ targetType, argValueList.get(0), safe);
}
private static RelDataType nullifyType(JavaTypeFactory typeFactory,
diff --git a/core/src/main/java/org/apache/calcite/adapter/enumerable/RexToLixTranslator.java b/core/src/main/java/org/apache/calcite/adapter/enumerable/RexToLixTranslator.java
index 35a691be0d..2eba9bf112 100644
--- a/core/src/main/java/org/apache/calcite/adapter/enumerable/RexToLixTranslator.java
+++ b/core/src/main/java/org/apache/calcite/adapter/enumerable/RexToLixTranslator.java
@@ -56,6 +56,7 @@ import org.apache.calcite.sql.SqlIntervalQualifier;
import org.apache.calcite.sql.SqlOperator;
import org.apache.calcite.sql.SqlWindowTableFunction;
import org.apache.calcite.sql.fun.SqlStdOperatorTable;
+import org.apache.calcite.sql.type.SqlTypeFamily;
import org.apache.calcite.sql.type.SqlTypeUtil;
import org.apache.calcite.sql.validate.SqlConformance;
import org.apache.calcite.util.BuiltInMethod;
@@ -79,7 +80,9 @@ import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
+import java.util.function.Supplier;
+import static org.apache.calcite.linq4j.tree.Expressions.constant;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.TRANSLATE3;
import static org.apache.calcite.sql.fun.SqlStdOperatorTable.CASE;
import static org.apache.calcite.sql.fun.SqlStdOperatorTable.CHAR_LENGTH;
@@ -98,7 +101,7 @@ import static java.util.Objects.requireNonNull;
public class RexToLixTranslator implements RexVisitor<RexToLixTranslator.Result> {
public static final Map<Method, SqlOperator> JAVA_TO_SQL_METHOD_MAP =
ImmutableMap.<Method, SqlOperator>builder()
- .put(findMethod(String.class, "toUpperCase"), UPPER)
+ .put(BuiltInMethod.STRING_TO_UPPER.method, UPPER)
.put(BuiltInMethod.SUBSTRING.method, SUBSTRING)
.put(BuiltInMethod.OCTET_LENGTH.method, OCTET_LENGTH)
.put(BuiltInMethod.CHAR_LENGTH.method, CHAR_LENGTH)
@@ -143,15 +146,6 @@ public class RexToLixTranslator implements RexVisitor<RexToLixTranslator.Result>
private @Nullable Type currentStorageType;
- private static Method findMethod(
- Class<?> clazz, String name, Class... parameterTypes) {
- try {
- return clazz.getMethod(name, parameterTypes);
- } catch (NoSuchMethodException e) {
- throw new RuntimeException(e);
- }
- }
-
private RexToLixTranslator(@Nullable RexProgram program,
JavaTypeFactory typeFactory,
Expression root,
@@ -267,238 +261,249 @@ public class RexToLixTranslator implements RexVisitor<RexToLixTranslator.Result>
return nullAs.handle(translated);
}
+ /**
+ * Used for safe operators that return null if an exception is thrown.
+ */
+ private static Expression expressionHandlingSafe(Expression body, boolean safe) {
+ return safe ? safeExpression(body) : body;
+ }
+
+ private static Expression safeExpression(Expression body) {
+ final ParameterExpression e_ =
+ Expressions.parameter(Exception.class, new BlockBuilder().newName("e"));
+
+ return Expressions.call(
+ Expressions.lambda(
+ Expressions.block(
+ Expressions.tryCatch(
+ Expressions.return_(null, body),
+ Expressions.catch_(e_,
+ Expressions.return_(null, constant(null)))))),
+ BuiltInMethod.FUNCTION0_APPLY.method);
+ }
+
Expression translateCast(
+ RelDataType sourceType,
+ RelDataType targetType,
+ Expression operand,
+ boolean safe) {
+ Expression convert = getConvertExpression(sourceType, targetType, operand);
+ Expression convert2 = checkExpressionPadTruncate(convert, sourceType, targetType);
+ Expression convert3 = expressionHandlingSafe(convert2, safe);
+ return scaleIntervalToNumber(sourceType, targetType, convert3);
+ }
+
+ private Expression getConvertExpression(
RelDataType sourceType,
RelDataType targetType,
Expression operand) {
- Expression convert = null;
+ final Supplier<Expression> defaultExpression = () ->
+ EnumUtils.convert(operand, typeFactory.getJavaClass(targetType));
+
switch (targetType.getSqlTypeName()) {
case ANY:
- convert = operand;
- break;
+ return operand;
+
case GEOMETRY:
switch (sourceType.getSqlTypeName()) {
case CHAR:
case VARCHAR:
- convert = Expressions.call(BuiltInMethod.ST_GEOM_FROM_EWKT.method, operand);
- break;
+ return Expressions.call(BuiltInMethod.ST_GEOM_FROM_EWKT.method, operand);
+
default:
- break;
+ return defaultExpression.get();
}
- break;
+
case DATE:
- convert = translateCastToDate(sourceType, operand);
- break;
+ return translateCastToDate(sourceType, operand, defaultExpression);
+
case TIME:
- convert = translateCastToTime(sourceType, operand);
- break;
+ return translateCastToTime(sourceType, operand, defaultExpression);
+
case TIME_WITH_LOCAL_TIME_ZONE:
switch (sourceType.getSqlTypeName()) {
case CHAR:
case VARCHAR:
- convert =
- Expressions.call(BuiltInMethod.STRING_TO_TIME_WITH_LOCAL_TIME_ZONE.method,
- operand);
- break;
+ return Expressions.call(BuiltInMethod.STRING_TO_TIME_WITH_LOCAL_TIME_ZONE.method,
+ operand);
+
case TIME:
- convert =
- Expressions.call(
- BuiltInMethod.TIME_STRING_TO_TIME_WITH_LOCAL_TIME_ZONE.method,
- RexImpTable.optimize2(operand,
- Expressions.call(BuiltInMethod.UNIX_TIME_TO_STRING.method,
- operand)),
- Expressions.call(BuiltInMethod.TIME_ZONE.method, root));
- break;
+ return Expressions.call(
+ BuiltInMethod.TIME_STRING_TO_TIME_WITH_LOCAL_TIME_ZONE.method,
+ RexImpTable.optimize2(operand,
+ Expressions.call(BuiltInMethod.UNIX_TIME_TO_STRING.method,
+ operand)),
+ Expressions.call(BuiltInMethod.TIME_ZONE.method, root));
+
case TIMESTAMP:
- convert =
- Expressions.call(
- BuiltInMethod.TIMESTAMP_STRING_TO_TIMESTAMP_WITH_LOCAL_TIME_ZONE.method,
- RexImpTable.optimize2(operand,
- Expressions.call(
- BuiltInMethod.UNIX_TIMESTAMP_TO_STRING.method,
- operand)),
- Expressions.call(BuiltInMethod.TIME_ZONE.method, root));
- break;
- case TIMESTAMP_WITH_LOCAL_TIME_ZONE:
- convert =
+ return Expressions.call(
+ BuiltInMethod.TIMESTAMP_STRING_TO_TIMESTAMP_WITH_LOCAL_TIME_ZONE.method,
RexImpTable.optimize2(operand,
- Expressions.call(
- BuiltInMethod
- .TIMESTAMP_WITH_LOCAL_TIME_ZONE_TO_TIME_WITH_LOCAL_TIME_ZONE
- .method,
- operand));
- break;
+ Expressions.call(BuiltInMethod.UNIX_TIMESTAMP_TO_STRING.method,
+ operand)),
+ Expressions.call(BuiltInMethod.TIME_ZONE.method, root));
+
+ case TIMESTAMP_WITH_LOCAL_TIME_ZONE:
+ return RexImpTable.optimize2(operand,
+ Expressions.call(
+ BuiltInMethod
+ .TIMESTAMP_WITH_LOCAL_TIME_ZONE_TO_TIME_WITH_LOCAL_TIME_ZONE
+ .method,
+ operand));
+
default:
- break;
+ return defaultExpression.get();
}
- break;
+
case TIMESTAMP:
switch (sourceType.getSqlTypeName()) {
case CHAR:
case VARCHAR:
- convert =
- Expressions.call(BuiltInMethod.STRING_TO_TIMESTAMP.method, operand);
- break;
+ return Expressions.call(BuiltInMethod.STRING_TO_TIMESTAMP.method,
+ operand);
+
case DATE:
- convert =
- Expressions.multiply(Expressions.convert_(operand, long.class),
- Expressions.constant(DateTimeUtils.MILLIS_PER_DAY));
- break;
+ return Expressions.multiply(Expressions.convert_(operand, long.class),
+ Expressions.constant(DateTimeUtils.MILLIS_PER_DAY));
+
case TIME:
- convert =
- Expressions.add(
- Expressions.multiply(
- Expressions.convert_(
- Expressions.call(BuiltInMethod.CURRENT_DATE.method, root),
- long.class),
- Expressions.constant(DateTimeUtils.MILLIS_PER_DAY)),
- Expressions.convert_(operand, long.class));
- break;
+ return Expressions.add(
+ Expressions.multiply(
+ Expressions.convert_(
+ Expressions.call(BuiltInMethod.CURRENT_DATE.method, root),
+ long.class),
+ Expressions.constant(DateTimeUtils.MILLIS_PER_DAY)),
+ Expressions.convert_(operand, long.class));
+
case TIME_WITH_LOCAL_TIME_ZONE:
- convert =
- RexImpTable.optimize2(operand,
- Expressions.call(
- BuiltInMethod.TIME_WITH_LOCAL_TIME_ZONE_TO_TIMESTAMP.method,
- Expressions.call(BuiltInMethod.UNIX_DATE_TO_STRING.method,
- Expressions.call(BuiltInMethod.CURRENT_DATE.method, root)),
- operand,
- Expressions.call(BuiltInMethod.TIME_ZONE.method, root)));
- break;
+ return RexImpTable.optimize2(operand,
+ Expressions.call(
+ BuiltInMethod.TIME_WITH_LOCAL_TIME_ZONE_TO_TIMESTAMP.method,
+ Expressions.call(BuiltInMethod.UNIX_DATE_TO_STRING.method,
+ Expressions.call(BuiltInMethod.CURRENT_DATE.method, root)),
+ operand,
+ Expressions.call(BuiltInMethod.TIME_ZONE.method, root)));
+
case TIMESTAMP_WITH_LOCAL_TIME_ZONE:
- convert =
- RexImpTable.optimize2(operand,
- Expressions.call(
- BuiltInMethod.TIMESTAMP_WITH_LOCAL_TIME_ZONE_TO_TIMESTAMP.method,
- operand,
- Expressions.call(BuiltInMethod.TIME_ZONE.method, root)));
- break;
+ return RexImpTable.optimize2(operand,
+ Expressions.call(
+ BuiltInMethod.TIMESTAMP_WITH_LOCAL_TIME_ZONE_TO_TIMESTAMP.method,
+ operand,
+ Expressions.call(BuiltInMethod.TIME_ZONE.method, root)));
+
default:
- break;
+ return defaultExpression.get();
}
- break;
+
case TIMESTAMP_WITH_LOCAL_TIME_ZONE:
switch (sourceType.getSqlTypeName()) {
case CHAR:
case VARCHAR:
- convert =
- Expressions.call(
- BuiltInMethod.STRING_TO_TIMESTAMP_WITH_LOCAL_TIME_ZONE.method,
- operand);
- break;
+ return Expressions.call(
+ BuiltInMethod.STRING_TO_TIMESTAMP_WITH_LOCAL_TIME_ZONE.method,
+ operand);
+
case DATE:
- convert =
- Expressions.call(
- BuiltInMethod.TIMESTAMP_STRING_TO_TIMESTAMP_WITH_LOCAL_TIME_ZONE.method,
- RexImpTable.optimize2(operand,
- Expressions.call(
- BuiltInMethod.UNIX_TIMESTAMP_TO_STRING.method,
- Expressions.multiply(
- Expressions.convert_(operand, long.class),
- Expressions.constant(DateTimeUtils.MILLIS_PER_DAY)))),
- Expressions.call(BuiltInMethod.TIME_ZONE.method, root));
- break;
+ return Expressions.call(
+ BuiltInMethod.TIMESTAMP_STRING_TO_TIMESTAMP_WITH_LOCAL_TIME_ZONE.method,
+ RexImpTable.optimize2(operand,
+ Expressions.call(
+ BuiltInMethod.UNIX_TIMESTAMP_TO_STRING.method,
+ Expressions.multiply(
+ Expressions.convert_(operand, long.class),
+ Expressions.constant(DateTimeUtils.MILLIS_PER_DAY)))),
+ Expressions.call(BuiltInMethod.TIME_ZONE.method, root));
+
case TIME:
- convert =
- Expressions.call(
- BuiltInMethod.TIMESTAMP_STRING_TO_TIMESTAMP_WITH_LOCAL_TIME_ZONE.method,
- RexImpTable.optimize2(operand,
- Expressions.call(
- BuiltInMethod.UNIX_TIMESTAMP_TO_STRING.method,
- Expressions.add(
- Expressions.multiply(
- Expressions.convert_(
- Expressions.call(BuiltInMethod.CURRENT_DATE.method, root),
- long.class),
- Expressions.constant(DateTimeUtils.MILLIS_PER_DAY)),
- Expressions.convert_(operand, long.class)))),
- Expressions.call(BuiltInMethod.TIME_ZONE.method, root));
- break;
+ return Expressions.call(
+ BuiltInMethod.TIMESTAMP_STRING_TO_TIMESTAMP_WITH_LOCAL_TIME_ZONE.method,
+ RexImpTable.optimize2(operand,
+ Expressions.call(BuiltInMethod.UNIX_TIMESTAMP_TO_STRING.method,
+ Expressions.add(
+ Expressions.multiply(
+ Expressions.convert_(
+ Expressions.call(BuiltInMethod.CURRENT_DATE.method, root),
+ long.class),
+ Expressions.constant(DateTimeUtils.MILLIS_PER_DAY)),
+ Expressions.convert_(operand, long.class)))),
+ Expressions.call(BuiltInMethod.TIME_ZONE.method, root));
+
case TIME_WITH_LOCAL_TIME_ZONE:
- convert =
+ return RexImpTable.optimize2(operand,
+ Expressions.call(
+ BuiltInMethod
+ .TIME_WITH_LOCAL_TIME_ZONE_TO_TIMESTAMP_WITH_LOCAL_TIME_ZONE
+ .method,
+ Expressions.call(BuiltInMethod.UNIX_DATE_TO_STRING.method,
+ Expressions.call(BuiltInMethod.CURRENT_DATE.method, root)),
+ operand));
+
+ case TIMESTAMP:
+ return Expressions.call(
+ BuiltInMethod.TIMESTAMP_STRING_TO_TIMESTAMP_WITH_LOCAL_TIME_ZONE.method,
RexImpTable.optimize2(operand,
Expressions.call(
- BuiltInMethod
- .TIME_WITH_LOCAL_TIME_ZONE_TO_TIMESTAMP_WITH_LOCAL_TIME_ZONE
- .method,
- Expressions.call(BuiltInMethod.UNIX_DATE_TO_STRING.method,
- Expressions.call(BuiltInMethod.CURRENT_DATE.method, root)),
- operand));
- break;
- case TIMESTAMP:
- convert =
- Expressions.call(
- BuiltInMethod.TIMESTAMP_STRING_TO_TIMESTAMP_WITH_LOCAL_TIME_ZONE.method,
- RexImpTable.optimize2(operand,
- Expressions.call(
- BuiltInMethod.UNIX_TIMESTAMP_TO_STRING.method,
- operand)),
- Expressions.call(BuiltInMethod.TIME_ZONE.method, root));
- break;
+ BuiltInMethod.UNIX_TIMESTAMP_TO_STRING.method,
+ operand)),
+ Expressions.call(BuiltInMethod.TIME_ZONE.method, root));
+
default:
- break;
+ return defaultExpression.get();
}
- break;
+
case BOOLEAN:
switch (sourceType.getSqlTypeName()) {
case CHAR:
case VARCHAR:
- convert =
- Expressions.call(BuiltInMethod.STRING_TO_BOOLEAN.method, operand);
- break;
+ return Expressions.call(BuiltInMethod.STRING_TO_BOOLEAN.method, operand);
+
default:
- break;
+ return defaultExpression.get();
}
- break;
+
case CHAR:
case VARCHAR:
final SqlIntervalQualifier interval =
sourceType.getIntervalQualifier();
switch (sourceType.getSqlTypeName()) {
case DATE:
- convert =
- RexImpTable.optimize2(operand,
- Expressions.call(BuiltInMethod.UNIX_DATE_TO_STRING.method,
- operand));
- break;
+ return RexImpTable.optimize2(operand,
+ Expressions.call(BuiltInMethod.UNIX_DATE_TO_STRING.method,
+ operand));
+
case TIME:
- convert =
- RexImpTable.optimize2(operand,
- Expressions.call(BuiltInMethod.UNIX_TIME_TO_STRING.method,
- operand));
- break;
+ return RexImpTable.optimize2(operand,
+ Expressions.call(BuiltInMethod.UNIX_TIME_TO_STRING.method,
+ operand));
+
case TIME_WITH_LOCAL_TIME_ZONE:
- convert =
- RexImpTable.optimize2(operand,
- Expressions.call(
- BuiltInMethod.TIME_WITH_LOCAL_TIME_ZONE_TO_STRING.method,
- operand,
- Expressions.call(BuiltInMethod.TIME_ZONE.method, root)));
- break;
+ return RexImpTable.optimize2(operand,
+ Expressions.call(
+ BuiltInMethod.TIME_WITH_LOCAL_TIME_ZONE_TO_STRING.method,
+ operand,
+ Expressions.call(BuiltInMethod.TIME_ZONE.method, root)));
+
case TIMESTAMP:
- convert =
- RexImpTable.optimize2(operand,
- Expressions.call(BuiltInMethod.UNIX_TIMESTAMP_TO_STRING.method,
- operand));
- break;
+ return RexImpTable.optimize2(operand,
+ Expressions.call(BuiltInMethod.UNIX_TIMESTAMP_TO_STRING.method,
+ operand));
+
case TIMESTAMP_WITH_LOCAL_TIME_ZONE:
- convert =
- RexImpTable.optimize2(operand,
- Expressions.call(
- BuiltInMethod.TIMESTAMP_WITH_LOCAL_TIME_ZONE_TO_STRING.method,
- operand,
- Expressions.call(BuiltInMethod.TIME_ZONE.method, root)));
- break;
+ return RexImpTable.optimize2(operand,
+ Expressions.call(
+ BuiltInMethod.TIMESTAMP_WITH_LOCAL_TIME_ZONE_TO_STRING.method,
+ operand,
+ Expressions.call(BuiltInMethod.TIME_ZONE.method, root)));
+
case INTERVAL_YEAR:
case INTERVAL_YEAR_MONTH:
case INTERVAL_MONTH:
- convert =
- RexImpTable.optimize2(operand,
- Expressions.call(
- BuiltInMethod.INTERVAL_YEAR_MONTH_TO_STRING.method,
- operand,
- Expressions.constant(
- requireNonNull(interval, "interval").timeUnitRange)));
- break;
+ return RexImpTable.optimize2(operand,
+ Expressions.call(BuiltInMethod.INTERVAL_YEAR_MONTH_TO_STRING.method,
+ operand,
+ Expressions.constant(
+ requireNonNull(interval, "interval").timeUnitRange)));
+
case INTERVAL_DAY:
case INTERVAL_DAY_HOUR:
case INTERVAL_DAY_MINUTE:
@@ -509,33 +514,33 @@ public class RexToLixTranslator implements RexVisitor<RexToLixTranslator.Result>
case INTERVAL_MINUTE:
case INTERVAL_MINUTE_SECOND:
case INTERVAL_SECOND:
- convert =
- RexImpTable.optimize2(operand,
- Expressions.call(
- BuiltInMethod.INTERVAL_DAY_TIME_TO_STRING.method,
- operand,
- Expressions.constant(
- requireNonNull(interval, "interval").timeUnitRange),
- Expressions.constant(
- interval.getFractionalSecondPrecision(
- typeFactory.getTypeSystem()))));
- break;
+ return RexImpTable.optimize2(operand,
+ Expressions.call(BuiltInMethod.INTERVAL_DAY_TIME_TO_STRING.method,
+ operand,
+ Expressions.constant(
+ requireNonNull(interval, "interval").timeUnitRange),
+ Expressions.constant(
+ interval.getFractionalSecondPrecision(
+ typeFactory.getTypeSystem()))));
+
case BOOLEAN:
- convert =
- RexImpTable.optimize2(operand,
- Expressions.call(BuiltInMethod.BOOLEAN_TO_STRING.method,
- operand));
- break;
+ return RexImpTable.optimize2(operand,
+ Expressions.call(BuiltInMethod.BOOLEAN_TO_STRING.method,
+ operand));
+
default:
- break;
+ return defaultExpression.get();
}
- break;
+
default:
- break;
- }
- if (convert == null) {
- convert = EnumUtils.convert(operand, typeFactory.getJavaClass(targetType));
+ return defaultExpression.get();
}
+ }
+
+ private static Expression checkExpressionPadTruncate(
+ Expression operand,
+ RelDataType sourceType,
+ RelDataType targetType) {
// Going from anything to CHAR(n) or VARCHAR(n), make sure value is no
// longer than n.
boolean pad = false;
@@ -548,51 +553,51 @@ public class RexToLixTranslator implements RexVisitor<RexToLixTranslator.Result>
case VARCHAR:
case VARBINARY:
final int targetPrecision = targetType.getPrecision();
- if (targetPrecision >= 0) {
- switch (sourceType.getSqlTypeName()) {
- case CHAR:
- case VARCHAR:
- case BINARY:
- case VARBINARY:
- // If this is a widening cast, no need to truncate.
- final int sourcePrecision = sourceType.getPrecision();
- if (SqlTypeUtil.comparePrecision(sourcePrecision, targetPrecision)
- <= 0) {
- truncate = false;
- }
- // If this is a widening cast, no need to pad.
- if (SqlTypeUtil.comparePrecision(sourcePrecision, targetPrecision)
- >= 0) {
- pad = false;
- }
- // fall through
- default:
- if (truncate || pad) {
- convert =
- Expressions.call(
- pad
- ? BuiltInMethod.TRUNCATE_OR_PAD.method
- : BuiltInMethod.TRUNCATE.method,
- convert,
- Expressions.constant(targetPrecision));
- }
+ if (targetPrecision < 0) {
+ return operand;
+ }
+ switch (sourceType.getSqlTypeName()) {
+ case CHAR:
+ case VARCHAR:
+ case BINARY:
+ case VARBINARY:
+ // If this is a widening cast, no need to truncate.
+ final int sourcePrecision = sourceType.getPrecision();
+ if (SqlTypeUtil.comparePrecision(sourcePrecision, targetPrecision)
+ <= 0) {
+ truncate = false;
+ }
+ // If this is a widening cast, no need to pad.
+ if (SqlTypeUtil.comparePrecision(sourcePrecision, targetPrecision)
+ >= 0) {
+ pad = false;
}
+ // fall through
+ default:
+ if (truncate || pad) {
+ final Method method =
+ pad ? BuiltInMethod.TRUNCATE_OR_PAD.method
+ : BuiltInMethod.TRUNCATE.method;
+ return Expressions.call(method, operand,
+ Expressions.constant(targetPrecision));
+ }
+ return operand;
}
- break;
+
+ // Checkstyle thinks that the previous branch should have a break, but it
+ // is mistaken.
+ // CHECKSTYLE: IGNORE 1
case TIMESTAMP:
int targetScale = targetType.getScale();
if (targetScale == RelDataType.SCALE_NOT_SPECIFIED) {
targetScale = 0;
}
if (targetScale < sourceType.getScale()) {
- convert =
- Expressions.call(
- BuiltInMethod.ROUND_LONG.method,
- convert,
- Expressions.constant(
- (long) Math.pow(10, 3 - targetScale)));
+ return Expressions.call(BuiltInMethod.ROUND_LONG.method, operand,
+ Expressions.constant((long) Math.pow(10, 3 - targetScale)));
}
- break;
+ return operand;
+
case INTERVAL_YEAR:
case INTERVAL_YEAR_MONTH:
case INTERVAL_MONTH:
@@ -606,66 +611,61 @@ public class RexToLixTranslator implements RexVisitor<RexToLixTranslator.Result>
case INTERVAL_MINUTE:
case INTERVAL_MINUTE_SECOND:
case INTERVAL_SECOND:
- switch (requireNonNull(sourceType.getSqlTypeName().getFamily(),
- () -> "null SqlTypeFamily for " + sourceType + ", SqlTypeName "
- + sourceType.getSqlTypeName())) {
+ final SqlTypeFamily family =
+ requireNonNull(sourceType.getSqlTypeName().getFamily(),
+ () -> "null SqlTypeFamily for " + sourceType + ", SqlTypeName "
+ + sourceType.getSqlTypeName());
+ switch (family) {
case NUMERIC:
final BigDecimal multiplier =
targetType.getSqlTypeName().getEndUnit().multiplier;
final BigDecimal divider = BigDecimal.ONE;
- convert = RexImpTable.multiplyDivide(convert, multiplier, divider);
- break;
+ return RexImpTable.multiplyDivide(operand, multiplier, divider);
+
default:
- break;
+ return operand;
}
- break;
+
default:
- break;
+ return operand;
}
- return scaleIntervalToNumber(sourceType, targetType, convert);
}
- private @Nullable Expression translateCastToTime(RelDataType sourceType,
- Expression operand) {
- Expression convert = null;
+ private Expression translateCastToTime(RelDataType sourceType,
+ Expression operand, Supplier<Expression> defaultExpression) {
switch (sourceType.getSqlTypeName()) {
case CHAR:
case VARCHAR:
- convert =
- Expressions.call(BuiltInMethod.STRING_TO_TIME.method, operand);
- break;
+ return Expressions.call(BuiltInMethod.STRING_TO_TIME.method, operand);
+
case TIME_WITH_LOCAL_TIME_ZONE:
- convert =
- RexImpTable.optimize2(operand,
- Expressions.call(
- BuiltInMethod.TIME_WITH_LOCAL_TIME_ZONE_TO_TIME.method,
- operand,
- Expressions.call(BuiltInMethod.TIME_ZONE.method, root)));
- break;
+ return RexImpTable.optimize2(operand,
+ Expressions.call(
+ BuiltInMethod.TIME_WITH_LOCAL_TIME_ZONE_TO_TIME.method,
+ operand,
+ Expressions.call(BuiltInMethod.TIME_ZONE.method, root)));
+
case TIMESTAMP:
- convert =
- Expressions.convert_(
- Expressions.call(BuiltInMethod.FLOOR_MOD.method,
- operand,
- Expressions.constant(DateTimeUtils.MILLIS_PER_DAY)),
- int.class);
- break;
+ return Expressions.convert_(
+ Expressions.call(BuiltInMethod.FLOOR_MOD.method,
+ operand,
+ Expressions.constant(DateTimeUtils.MILLIS_PER_DAY)),
+ int.class);
+
case TIMESTAMP_WITH_LOCAL_TIME_ZONE:
- convert =
- RexImpTable.optimize2(operand,
- Expressions.call(
- BuiltInMethod.TIMESTAMP_WITH_LOCAL_TIME_ZONE_TO_TIME.method,
- operand,
- Expressions.call(BuiltInMethod.TIME_ZONE.method, root)));
- break;
+ return RexImpTable.optimize2(operand,
+ Expressions.call(
+ BuiltInMethod.TIMESTAMP_WITH_LOCAL_TIME_ZONE_TO_TIME.method,
+ operand,
+ Expressions.call(BuiltInMethod.TIME_ZONE.method, root)));
+
default:
- break;
+ return defaultExpression.get();
}
- return convert;
}
- private @Nullable Expression translateCastToDate(RelDataType sourceType,
- Expression operand) {
+ private Expression translateCastToDate(RelDataType sourceType,
+ Expression operand, Supplier<Expression> defaultExpression) {
switch (sourceType.getSqlTypeName()) {
case CHAR:
case VARCHAR:
@@ -685,7 +685,7 @@ public class RexToLixTranslator implements RexVisitor<RexToLixTranslator.Result>
Expressions.call(BuiltInMethod.TIME_ZONE.method, root)));
default:
- return null;
+ return defaultExpression.get();
}
}
diff --git a/core/src/main/java/org/apache/calcite/rel/rules/AggregateProjectPullUpConstantsRule.java b/core/src/main/java/org/apache/calcite/rel/rules/AggregateProjectPullUpConstantsRule.java
index 356286e65f..b587d83a1a 100644
--- a/core/src/main/java/org/apache/calcite/rel/rules/AggregateProjectPullUpConstantsRule.java
+++ b/core/src/main/java/org/apache/calcite/rel/rules/AggregateProjectPullUpConstantsRule.java
@@ -160,7 +160,7 @@ public class AggregateProjectPullUpConstantsRule
RelDataType originalType =
aggregate.getRowType().getFieldList().get(projects.size()).getType();
if (!originalType.equals(rexNode.getType())) {
- expr = rexBuilder.makeCast(originalType, rexNode, true);
+ expr = rexBuilder.makeCast(originalType, rexNode, true, false);
} else {
expr = rexNode;
}
diff --git a/core/src/main/java/org/apache/calcite/rel/rules/ReduceExpressionsRule.java b/core/src/main/java/org/apache/calcite/rel/rules/ReduceExpressionsRule.java
index be62c28052..44f978e5a3 100644
--- a/core/src/main/java/org/apache/calcite/rel/rules/ReduceExpressionsRule.java
+++ b/core/src/main/java/org/apache/calcite/rel/rules/ReduceExpressionsRule.java
@@ -35,6 +35,7 @@ import org.apache.calcite.rel.logical.LogicalFilter;
import org.apache.calcite.rel.logical.LogicalProject;
import org.apache.calcite.rel.logical.LogicalWindow;
import org.apache.calcite.rel.metadata.RelMetadataQuery;
+import org.apache.calcite.rel.type.RelDataType;
import org.apache.calcite.rel.type.RelDataTypeFactory;
import org.apache.calcite.rex.RexBuilder;
import org.apache.calcite.rex.RexCall;
@@ -959,8 +960,8 @@ public abstract class ReduceExpressionsRule<C extends ReduceExpressionsRule.Conf
// If we make 'abc' of type VARCHAR(4), we may later encounter
// the same expression in a Project's digest where it has
// type VARCHAR(3), and that's wrong.
- replacement =
- simplify.rexBuilder.makeAbstractCast(call.getType(), replacement);
+ RelDataType type = call.getType();
+ replacement = simplify.rexBuilder.makeAbstractCast(type, replacement, false);
}
return replacement;
}
diff --git a/core/src/main/java/org/apache/calcite/rel/rules/UnionPullUpConstantsRule.java b/core/src/main/java/org/apache/calcite/rel/rules/UnionPullUpConstantsRule.java
index e1d994571d..e236844075 100644
--- a/core/src/main/java/org/apache/calcite/rel/rules/UnionPullUpConstantsRule.java
+++ b/core/src/main/java/org/apache/calcite/rel/rules/UnionPullUpConstantsRule.java
@@ -98,7 +98,7 @@ public class UnionPullUpConstantsRule
if (constant.getType().equals(field.getType())) {
topChildExprs.add(constant);
} else {
- topChildExprs.add(rexBuilder.makeCast(field.getType(), constant, true));
+ topChildExprs.add(rexBuilder.makeCast(field.getType(), constant, true, false));
}
topChildExprsFields.add(field.getName());
} else {
diff --git a/core/src/main/java/org/apache/calcite/rel/rules/ValuesReduceRule.java b/core/src/main/java/org/apache/calcite/rel/rules/ValuesReduceRule.java
index 15715f1e13..eedc22b524 100644
--- a/core/src/main/java/org/apache/calcite/rel/rules/ValuesReduceRule.java
+++ b/core/src/main/java/org/apache/calcite/rel/rules/ValuesReduceRule.java
@@ -156,9 +156,9 @@ public class ValuesReduceRule
++k;
RexNode e = projectExpr.accept(shuttle);
if (RexLiteral.isNullLiteral(e)) {
- e =
- rexBuilder.makeAbstractCast(
- project.getRowType().getFieldList().get(k).getType(), e);
+ RelDataType type =
+ project.getRowType().getFieldList().get(k).getType();
+ e = rexBuilder.makeAbstractCast(type, e, false);
}
reducibleExps.add(e);
}
diff --git a/core/src/main/java/org/apache/calcite/rex/RexBuilder.java b/core/src/main/java/org/apache/calcite/rex/RexBuilder.java
index 3ff84514ec..c19a1001c6 100644
--- a/core/src/main/java/org/apache/calcite/rex/RexBuilder.java
+++ b/core/src/main/java/org/apache/calcite/rex/RexBuilder.java
@@ -35,6 +35,7 @@ import org.apache.calcite.sql.SqlOperator;
import org.apache.calcite.sql.SqlSpecialOperator;
import org.apache.calcite.sql.SqlUtil;
import org.apache.calcite.sql.fun.SqlCountAggFunction;
+import org.apache.calcite.sql.fun.SqlLibraryOperators;
import org.apache.calcite.sql.fun.SqlStdOperatorTable;
import org.apache.calcite.sql.type.ArraySqlType;
import org.apache.calcite.sql.type.MapSqlType;
@@ -528,12 +529,20 @@ public class RexBuilder {
public RexNode makeCast(
RelDataType type,
RexNode exp) {
- return makeCast(type, exp, false);
+ return makeCast(type, exp, false, false);
+ }
+
+ @Deprecated // to be removed before 2.0
+ public RexNode makeCast(
+ RelDataType type,
+ RexNode exp,
+ boolean matchNullability) {
+ return makeCast(type, exp, matchNullability, false);
}
/**
* Creates a call to the CAST operator, expanding if possible, and optionally
- * also preserving nullability.
+ * also preserving nullability, and optionally in safe mode.
*
* <p>Tries to expand the cast, and therefore the result may be something
* other than a {@link RexCall} to the CAST operator, such as a
@@ -543,12 +552,14 @@ public class RexBuilder {
* @param exp Expression being cast
* @param matchNullability Whether to ensure the result has the same
* nullability as {@code type}
+ * @param safe Whether to return NULL if cast fails
* @return Call to CAST operator
*/
public RexNode makeCast(
RelDataType type,
RexNode exp,
- boolean matchNullability) {
+ boolean matchNullability,
+ boolean safe) {
final SqlTypeName sqlType = type.getSqlTypeName();
if (exp instanceof RexLiteral) {
RexLiteral literal = (RexLiteral) exp;
@@ -608,7 +619,7 @@ public class RexBuilder {
if (type.isNullable()
&& !literal2.getType().isNullable()
&& matchNullability) {
- return makeAbstractCast(type, literal2);
+ return makeAbstractCast(type, literal2, safe);
}
return literal2;
}
@@ -622,7 +633,7 @@ public class RexBuilder {
&& SqlTypeUtil.isExactNumeric(type)) {
return makeCastBooleanToExact(type, exp);
}
- return makeAbstractCast(type, exp);
+ return makeAbstractCast(type, exp, safe);
}
/** Returns the lowest granularity unit for the given unit.
@@ -792,20 +803,24 @@ public class RexBuilder {
matchNullability(bigintType, node), node, makeLiteral(false));
}
+ @Deprecated // to be removed before 2.0
+ public RexNode makeAbstractCast(RelDataType type, RexNode exp) {
+ return makeAbstractCast(type, exp, false);
+ }
+
/**
- * Creates a call to the CAST operator.
+ * Creates a call to CAST or SAFE_CAST operator.
*
* @param type Type to cast to
* @param exp Expression being cast
+ * @param safe Whether to return NULL if cast fails
* @return Call to CAST operator
*/
- public RexNode makeAbstractCast(
- RelDataType type,
- RexNode exp) {
- return new RexCall(
- type,
- SqlStdOperatorTable.CAST,
- ImmutableList.of(exp));
+ public RexNode makeAbstractCast(RelDataType type, RexNode exp, boolean safe) {
+ SqlOperator operator =
+ safe ? SqlLibraryOperators.SAFE_CAST
+ : SqlStdOperatorTable.CAST;
+ return new RexCall(type, operator, ImmutableList.of(exp));
}
/**
@@ -843,7 +858,7 @@ public class RexBuilder {
}
final RelDataType notNullType =
typeFactory.createTypeWithNullability(type, false);
- return makeAbstractCast(notNullType, exp);
+ return makeAbstractCast(notNullType, exp, false);
}
/**
@@ -1585,7 +1600,7 @@ public class RexBuilder {
typeFactory.createTypeWithNullability(type, false);
if (allowCast) {
RexNode literalNotNull = makeLiteral(value, typeNotNull, allowCast);
- return makeAbstractCast(type, literalNotNull);
+ return makeAbstractCast(type, literalNotNull, false);
}
type = typeNotNull;
}
diff --git a/core/src/main/java/org/apache/calcite/rex/RexCallBinding.java b/core/src/main/java/org/apache/calcite/rex/RexCallBinding.java
index a4cd8b1b1e..66df15f55f 100644
--- a/core/src/main/java/org/apache/calcite/rex/RexCallBinding.java
+++ b/core/src/main/java/org/apache/calcite/rex/RexCallBinding.java
@@ -74,6 +74,7 @@ public class RexCallBinding extends SqlOperatorBinding {
: call.getOperands();
switch (call.getKind()) {
case CAST:
+ case SAFE_CAST:
return new RexCastCallBinding(typeFactory, call.getOperator(),
operands, call.getType(), inputCollations);
default:
diff --git a/core/src/main/java/org/apache/calcite/rex/RexSimplify.java b/core/src/main/java/org/apache/calcite/rex/RexSimplify.java
index 37dacd68eb..38b2b2b040 100644
--- a/core/src/main/java/org/apache/calcite/rex/RexSimplify.java
+++ b/core/src/main/java/org/apache/calcite/rex/RexSimplify.java
@@ -192,7 +192,7 @@ public class RexSimplify {
&& SqlTypeUtil.equalSansNullability(rexBuilder.typeFactory, e2.getType(), e.getType())) {
return e2;
}
- final RexNode e3 = rexBuilder.makeCast(e.getType(), e2, matchNullability);
+ final RexNode e3 = rexBuilder.makeCast(e.getType(), e2, matchNullability, false);
if (e3.equals(e)) {
return e;
}
@@ -286,6 +286,7 @@ public class RexSimplify {
case COALESCE:
return simplifyCoalesce((RexCall) e);
case CAST:
+ case SAFE_CAST:
return simplifyCast((RexCall) e);
case CEIL:
case FLOOR:
@@ -1197,7 +1198,7 @@ public class RexSimplify {
if (sameTypeOrNarrowsNullability(caseType, value.getType())) {
return value;
} else {
- return rexBuilder.makeAbstractCast(caseType, value);
+ return rexBuilder.makeAbstractCast(caseType, value, false);
}
}
@@ -1417,7 +1418,7 @@ public class RexSimplify {
final RexNode cond = isTrue(branch.cond);
final RexNode value;
if (!branchType.equals(branch.value.getType())) {
- value = rexBuilder.makeAbstractCast(branchType, branch.value);
+ value = rexBuilder.makeAbstractCast(branchType, branch.value, false);
} else {
value = branch.value;
}
@@ -2193,6 +2194,7 @@ public class RexSimplify {
return rexBuilder.makeCast(e.getType(), intExpr);
}
}
+ final boolean safe = e.getKind() == SqlKind.SAFE_CAST;
switch (operand.getKind()) {
case LITERAL:
final RexLiteral literal = (RexLiteral) operand;
@@ -2221,7 +2223,8 @@ public class RexSimplify {
break;
}
final List<RexNode> reducedValues = new ArrayList<>();
- final RexNode simplifiedExpr = rexBuilder.makeCast(e.getType(), operand);
+ final RexNode simplifiedExpr =
+ rexBuilder.makeCast(e.getType(), operand, safe, safe);
executor.reduce(rexBuilder, ImmutableList.of(simplifiedExpr), reducedValues);
return requireNonNull(
Iterables.getOnlyElement(reducedValues));
@@ -2229,7 +2232,7 @@ public class RexSimplify {
if (operand == e.getOperands().get(0)) {
return e;
} else {
- return rexBuilder.makeCast(e.getType(), operand);
+ return rexBuilder.makeCast(e.getType(), operand, safe, safe);
}
}
}
diff --git a/core/src/main/java/org/apache/calcite/rex/RexUtil.java b/core/src/main/java/org/apache/calcite/rex/RexUtil.java
index d4bbae443b..02fb508f2b 100644
--- a/core/src/main/java/org/apache/calcite/rex/RexUtil.java
+++ b/core/src/main/java/org/apache/calcite/rex/RexUtil.java
@@ -2973,7 +2973,8 @@ public class RexUtil {
if (simplifiedNode.getType().equals(call.getType())) {
return simplifiedNode;
}
- return simplify.rexBuilder.makeCast(call.getType(), simplifiedNode, matchNullability);
+ return simplify.rexBuilder.makeCast(call.getType(), simplifiedNode,
+ matchNullability, false);
}
}
diff --git a/core/src/main/java/org/apache/calcite/sql/SqlFunction.java b/core/src/main/java/org/apache/calcite/sql/SqlFunction.java
index c368f67a55..df48b7c916 100644
--- a/core/src/main/java/org/apache/calcite/sql/SqlFunction.java
+++ b/core/src/main/java/org/apache/calcite/sql/SqlFunction.java
@@ -255,6 +255,17 @@ public class SqlFunction extends SqlOperator {
validator.getTypeFactory(), getNameAsId(), argTypes, argNames,
getFunctionType(), SqlSyntax.FUNCTION, getKind(),
validator.getCatalogReader().nameMatcher(), false);
+
+ // If the call already has an operator and its syntax is SPECIAL, it must
+ // have been created intentionally by the parser.
+ if (function == null
+ && call.getOperator().getSyntax() == SqlSyntax.SPECIAL
+ && call.getOperator() instanceof SqlFunction
+ && validator.getOperatorTable().getOperatorList().contains(
+ call.getOperator())) {
+ function = (SqlFunction) call.getOperator();
+ }
+
try {
// if we have a match on function name and parameter count, but
// couldn't find a function with a COLUMN_LIST type, retry, but
diff --git a/core/src/main/java/org/apache/calcite/sql/SqlKind.java b/core/src/main/java/org/apache/calcite/sql/SqlKind.java
index 67400ed69a..4fc34072f2 100644
--- a/core/src/main/java/org/apache/calcite/sql/SqlKind.java
+++ b/core/src/main/java/org/apache/calcite/sql/SqlKind.java
@@ -639,6 +639,10 @@ public enum SqlKind {
*/
CAST,
+ /** The {@code SAFE_CAST} function, which is similar to {@link #CAST} but
+ * returns NULL rather than throwing an error if the conversion fails. */
+ SAFE_CAST,
+
/**
* The "NEXT VALUE OF sequence" operator.
*/
diff --git a/core/src/main/java/org/apache/calcite/sql/SqlSplittableAggFunction.java b/core/src/main/java/org/apache/calcite/sql/SqlSplittableAggFunction.java
index 3e56f9c6ae..7bd55d7b22 100644
--- a/core/src/main/java/org/apache/calcite/sql/SqlSplittableAggFunction.java
+++ b/core/src/main/java/org/apache/calcite/sql/SqlSplittableAggFunction.java
@@ -302,7 +302,7 @@ public interface SqlSplittableAggFunction {
break;
case 2:
node = rexBuilder.makeCall(SqlStdOperatorTable.MULTIPLY, merges);
- node = rexBuilder.makeAbstractCast(aggregateCall.type, node);
+ node = rexBuilder.makeAbstractCast(aggregateCall.type, node, false);
break;
default:
throw new AssertionError("unexpected count " + merges);
diff --git a/core/src/main/java/org/apache/calcite/sql/fun/SqlCastFunction.java b/core/src/main/java/org/apache/calcite/sql/fun/SqlCastFunction.java
index d3088823ce..a9bfd8cff8 100644
--- a/core/src/main/java/org/apache/calcite/sql/fun/SqlCastFunction.java
+++ b/core/src/main/java/org/apache/calcite/sql/fun/SqlCastFunction.java
@@ -17,6 +17,7 @@
package org.apache.calcite.sql.fun;
import org.apache.calcite.rel.type.RelDataType;
+import org.apache.calcite.rel.type.RelDataTypeFactory;
import org.apache.calcite.rel.type.RelDataTypeFamily;
import org.apache.calcite.sql.SqlCall;
import org.apache.calcite.sql.SqlCallBinding;
@@ -33,10 +34,10 @@ import org.apache.calcite.sql.SqlUtil;
import org.apache.calcite.sql.SqlWriter;
import org.apache.calcite.sql.type.InferTypes;
import org.apache.calcite.sql.type.SqlOperandCountRanges;
+import org.apache.calcite.sql.type.SqlReturnTypeInference;
import org.apache.calcite.sql.type.SqlTypeFamily;
import org.apache.calcite.sql.type.SqlTypeUtil;
import org.apache.calcite.sql.validate.SqlMonotonicity;
-import org.apache.calcite.sql.validate.SqlValidatorImpl;
import com.google.common.collect.ImmutableSetMultimap;
import com.google.common.collect.SetMultimap;
@@ -44,6 +45,8 @@ import com.google.common.collect.SetMultimap;
import java.text.Collator;
import java.util.Objects;
+import static com.google.common.base.Preconditions.checkArgument;
+
import static org.apache.calcite.util.Static.RESOURCE;
/**
@@ -83,35 +86,44 @@ public class SqlCastFunction extends SqlFunction {
//~ Constructors -----------------------------------------------------------
public SqlCastFunction() {
- super("CAST", SqlKind.CAST, SqlCastFunction::inferReturnTypeImpl,
- InferTypes.FIRST_KNOWN,
- null,
- SqlFunctionCategory.SYSTEM);
+ this(SqlKind.CAST);
+ }
+
+ public SqlCastFunction(SqlKind kind) {
+ super(kind.toString(), kind, returnTypeInference(kind == SqlKind.SAFE_CAST),
+ InferTypes.FIRST_KNOWN, null, SqlFunctionCategory.SYSTEM);
+ checkArgument(kind == SqlKind.CAST || kind == SqlKind.SAFE_CAST, kind);
}
//~ Methods ----------------------------------------------------------------
- static RelDataType inferReturnTypeImpl(SqlOperatorBinding opBinding) {
- assert opBinding.getOperandCount() == 2;
- final RelDataType firstType = opBinding.getOperandType(0);
- final RelDataType ret =
- opBinding.getTypeFactory().createTypeWithNullability(
- opBinding.getOperandType(1),
- firstType.isNullable());
- if (opBinding instanceof SqlCallBinding) {
- SqlCallBinding callBinding = (SqlCallBinding) opBinding;
- SqlNode operand0 = callBinding.operand(0);
-
- // dynamic parameters and null constants need their types assigned
- // to them using the type they are casted to.
- if (SqlUtil.isNullLiteral(operand0, false)
- || (operand0 instanceof SqlDynamicParam)) {
- final SqlValidatorImpl validator =
- (SqlValidatorImpl) callBinding.getValidator();
- validator.setValidatedNodeType(operand0, ret);
+ static SqlReturnTypeInference returnTypeInference(boolean safe) {
+ return opBinding -> {
+ assert opBinding.getOperandCount() == 2;
+ final RelDataType ret =
+ deriveType(opBinding.getTypeFactory(), opBinding.getOperandType(0),
+ opBinding.getOperandType(1), safe);
+
+ if (opBinding instanceof SqlCallBinding) {
+ final SqlCallBinding callBinding = (SqlCallBinding) opBinding;
+ SqlNode operand0 = callBinding.operand(0);
+
+ // dynamic parameters and null constants need their types assigned
+ // to them using the type they are casted to.
+ if (SqlUtil.isNullLiteral(operand0, false)
+ || operand0 instanceof SqlDynamicParam) {
+ callBinding.getValidator().setValidatedNodeType(operand0, ret);
+ }
}
- }
- return ret;
+ return ret;
+ };
+ }
+
+ /** Derives the type of "CAST(expression AS targetType)". */
+ public static RelDataType deriveType(RelDataTypeFactory typeFactory,
+ RelDataType expressionType, RelDataType targetType, boolean safe) {
+ return typeFactory.createTypeWithNullability(targetType,
+ expressionType.isNullable() || safe);
}
@Override public String getSignatureTemplate(final int operandsCount) {
diff --git a/core/src/main/java/org/apache/calcite/sql/fun/SqlLibraryOperators.java b/core/src/main/java/org/apache/calcite/sql/fun/SqlLibraryOperators.java
index 2cd4def708..8e892394c6 100644
--- a/core/src/main/java/org/apache/calcite/sql/fun/SqlLibraryOperators.java
+++ b/core/src/main/java/org/apache/calcite/sql/fun/SqlLibraryOperators.java
@@ -146,7 +146,7 @@ public abstract class SqlLibraryOperators {
public static final SqlFunction MSSQL_CONVERT =
SqlBasicFunction.create(SqlKind.CAST,
ReturnTypes.andThen(SqlLibraryOperators::transformConvert,
- SqlCastFunction::inferReturnTypeImpl),
+ SqlCastFunction.returnTypeInference(false)),
OperandTypes.repeat(SqlOperandCountRanges.between(2, 3),
OperandTypes.ANY))
.withName("CONVERT")
@@ -1187,6 +1187,13 @@ public abstract class SqlLibraryOperators {
public static final SqlOperator INFIX_CAST =
new SqlCastOperator();
+ /** The "SAFE_CAST(expr AS type)" function; identical to CAST(),
+ * except that if conversion fails, it returns NULL instead of raising an
+ * error. */
+ @LibraryOperator(libraries = {BIG_QUERY})
+ public static final SqlFunction SAFE_CAST =
+ new SqlCastFunction(SqlKind.SAFE_CAST);
+
/** NULL-safe "<=>" equal operator used by MySQL, for example
* {@code 1<=>NULL}. */
@LibraryOperator(libraries = { MYSQL })
diff --git a/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java b/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java
index 581fa48a82..e298500c3c 100644
--- a/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java
+++ b/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java
@@ -5454,7 +5454,7 @@ public class SqlToRelConverter {
subQuery = requireNonNull(getSubQuery(expr, null));
rex = requireNonNull(subQuery.expr);
return StandardConvertletTable.castToValidatedType(expr, rex,
- validator(), rexBuilder);
+ validator(), rexBuilder, false);
case SELECT:
case EXISTS:
diff --git a/core/src/main/java/org/apache/calcite/sql2rel/StandardConvertletTable.java b/core/src/main/java/org/apache/calcite/sql2rel/StandardConvertletTable.java
index 28fe7c0374..7d466a4a2f 100644
--- a/core/src/main/java/org/apache/calcite/sql2rel/StandardConvertletTable.java
+++ b/core/src/main/java/org/apache/calcite/sql2rel/StandardConvertletTable.java
@@ -56,6 +56,7 @@ import org.apache.calcite.sql.SqlWindowTableFunction;
import org.apache.calcite.sql.fun.SqlArrayValueConstructor;
import org.apache.calcite.sql.fun.SqlBetweenOperator;
import org.apache.calcite.sql.fun.SqlCase;
+import org.apache.calcite.sql.fun.SqlCastFunction;
import org.apache.calcite.sql.fun.SqlDatetimeSubtractionOperator;
import org.apache.calcite.sql.fun.SqlExtractFunction;
import org.apache.calcite.sql.fun.SqlInternalOperators;
@@ -78,7 +79,6 @@ import org.apache.calcite.sql.type.SqlTypeFamily;
import org.apache.calcite.sql.type.SqlTypeName;
import org.apache.calcite.sql.type.SqlTypeUtil;
import org.apache.calcite.sql.validate.SqlValidator;
-import org.apache.calcite.sql.validate.SqlValidatorImpl;
import org.apache.calcite.util.Pair;
import org.apache.calcite.util.Util;
@@ -96,6 +96,8 @@ import java.util.Objects;
import java.util.function.UnaryOperator;
import java.util.stream.Collectors;
+import static com.google.common.base.Preconditions.checkArgument;
+
import static org.apache.calcite.sql.type.NonNullableAccessors.getComponentTypeOrThrow;
import static org.apache.calcite.util.Util.first;
@@ -132,6 +134,7 @@ public class StandardConvertletTable extends ReflectiveConvertletTable {
// Register convertlets for specific objects.
registerOp(SqlStdOperatorTable.CAST, this::convertCast);
+ registerOp(SqlLibraryOperators.SAFE_CAST, this::convertCast);
registerOp(SqlLibraryOperators.INFIX_CAST, this::convertCast);
registerOp(SqlStdOperatorTable.IS_DISTINCT_FROM,
(cx, call) -> convertIsDistinctFrom(cx, call, false));
@@ -597,9 +600,13 @@ public class StandardConvertletTable extends ReflectiveConvertletTable {
SqlRexContext cx,
final SqlCall call) {
RelDataTypeFactory typeFactory = cx.getTypeFactory();
- assert call.getKind() == SqlKind.CAST;
+ final SqlValidator validator = cx.getValidator();
+ final SqlKind kind = call.getKind();
+ checkArgument(kind == SqlKind.CAST || kind == SqlKind.SAFE_CAST, kind);
+ final boolean safe = kind == SqlKind.SAFE_CAST;
final SqlNode left = call.operand(0);
final SqlNode right = call.operand(1);
+ final RexBuilder rexBuilder = cx.getRexBuilder();
if (right instanceof SqlIntervalQualifier) {
final SqlIntervalQualifier intervalQualifier =
(SqlIntervalQualifier) right;
@@ -609,35 +616,34 @@ public class StandardConvertletTable extends ReflectiveConvertletTable {
BigDecimal sourceValue =
(BigDecimal) sourceInterval.getValue();
RexLiteral castedInterval =
- cx.getRexBuilder().makeIntervalLiteral(sourceValue,
+ rexBuilder.makeIntervalLiteral(sourceValue,
intervalQualifier);
- return castToValidatedType(cx, call, castedInterval);
+ return castToValidatedType(call, castedInterval, validator, rexBuilder,
+ safe);
} else if (left instanceof SqlNumericLiteral) {
RexLiteral sourceInterval =
(RexLiteral) cx.convertExpression(left);
BigDecimal sourceValue =
- (BigDecimal) sourceInterval.getValue();
+ requireNonNull(sourceInterval.getValueAs(BigDecimal.class),
+ "sourceValue");
final BigDecimal multiplier = intervalQualifier.getUnit().multiplier;
- sourceValue = SqlFunctions.multiply(sourceValue, multiplier);
RexLiteral castedInterval =
- cx.getRexBuilder().makeIntervalLiteral(
- sourceValue,
+ rexBuilder.makeIntervalLiteral(
+ SqlFunctions.multiply(sourceValue, multiplier),
intervalQualifier);
- return castToValidatedType(cx, call, castedInterval);
+ return castToValidatedType(call, castedInterval, validator, rexBuilder,
+ safe);
}
- return castToValidatedType(cx, call, cx.convertExpression(left));
- }
- SqlDataTypeSpec dataType = (SqlDataTypeSpec) right;
- RelDataType type = dataType.deriveType(cx.getValidator());
- if (type == null) {
- type = cx.getValidator().getValidatedNodeType(dataType.getTypeName());
- }
- RexNode arg = cx.convertExpression(left);
- if (arg.getType().isNullable()) {
- type = typeFactory.createTypeWithNullability(type, true);
+ RexNode value = cx.convertExpression(left);
+ return castToValidatedType(call, value, validator, rexBuilder, safe);
}
+
+ final RexNode arg = cx.convertExpression(left);
+ final SqlDataTypeSpec dataType = (SqlDataTypeSpec) right;
+ RelDataType type =
+ SqlCastFunction.deriveType(cx.getTypeFactory(), arg.getType(),
+ dataType.deriveType(validator), safe);
if (SqlUtil.isNullLiteral(left, false)) {
- final SqlValidatorImpl validator = (SqlValidatorImpl) cx.getValidator();
validator.setValidatedNodeType(left, type);
return cx.convertExpression(left);
}
@@ -646,7 +652,7 @@ public class StandardConvertletTable extends ReflectiveConvertletTable {
// arg.getType() may be ANY
if (argComponentType == null) {
- argComponentType = dataType.getComponentTypeSpec().deriveType(cx.getValidator());
+ argComponentType = dataType.getComponentTypeSpec().deriveType(validator);
}
requireNonNull(argComponentType, () -> "componentType of " + arg);
@@ -668,7 +674,7 @@ public class StandardConvertletTable extends ReflectiveConvertletTable {
type = typeFactory.createTypeWithNullability(type, isn);
}
}
- return cx.getRexBuilder().makeCast(type, arg);
+ return rexBuilder.makeCast(type, arg, safe, safe);
}
protected RexNode convertFloorCeil(SqlRexContext cx, SqlCall call) {
@@ -1332,18 +1338,20 @@ public class StandardConvertletTable extends ReflectiveConvertletTable {
return Pair.of(r0, r1);
}
- /**
- * Casts a RexNode value to the validated type of a SqlCall. If the value
- * was already of the validated type, then the value is returned without an
- * additional cast.
- */
+ @Deprecated // to be removed before 2.0
public RexNode castToValidatedType(
@UnknownInitialization StandardConvertletTable this,
SqlRexContext cx,
SqlCall call,
RexNode value) {
return castToValidatedType(call, value, cx.getValidator(),
- cx.getRexBuilder());
+ cx.getRexBuilder(), false);
+ }
+
+ @Deprecated // to be removed before 2.0
+ public static RexNode castToValidatedType(SqlNode node, RexNode e,
+ SqlValidator validator, RexBuilder rexBuilder) {
+ return castToValidatedType(node, e, validator, rexBuilder, false);
}
/**
@@ -1352,12 +1360,12 @@ public class StandardConvertletTable extends ReflectiveConvertletTable {
* additional cast.
*/
public static RexNode castToValidatedType(SqlNode node, RexNode e,
- SqlValidator validator, RexBuilder rexBuilder) {
+ SqlValidator validator, RexBuilder rexBuilder, boolean safe) {
final RelDataType type = validator.getValidatedNodeType(node);
if (e.getType() == type) {
return e;
}
- return rexBuilder.makeCast(type, e);
+ return rexBuilder.makeCast(type, e, safe, safe);
}
/** Convertlet that handles {@code COVAR_POP}, {@code COVAR_SAMP},
@@ -1918,7 +1926,7 @@ public class StandardConvertletTable extends ReflectiveConvertletTable {
// If the TIMESTAMPADD call has type TIMESTAMP and op2 has type DATE
// (which can happen for sub-day time frames such as HOUR), cast op2 to
// TIMESTAMP.
- final RexNode op2b = rexBuilder.makeCast(type, op2, false);
+ final RexNode op2b = rexBuilder.makeCast(type, op2);
return rexBuilder.makeCall(type, SqlStdOperatorTable.TIMESTAMP_ADD,
ImmutableList.of(timeFrameName, op1, op2b));
}
diff --git a/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java b/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java
index 2b3eaaf085..f45d3f065a 100644
--- a/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java
+++ b/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java
@@ -303,6 +303,7 @@ public enum BuiltInMethod {
Calendar.class),
TIME_ZONE_GET_OFFSET(TimeZone.class, "getOffset", long.class),
LONG_VALUE(Number.class, "longValue"),
+ STRING_TO_UPPER(String.class, "toUpperCase"),
COMPARATOR_COMPARE(Comparator.class, "compare", Object.class, Object.class),
COLLECTIONS_REVERSE_ORDER(Collections.class, "reverseOrder"),
COLLECTIONS_EMPTY_LIST(Collections.class, "emptyList"),
diff --git a/core/src/test/java/org/apache/calcite/plan/RelOptUtilTest.java b/core/src/test/java/org/apache/calcite/plan/RelOptUtilTest.java
index 2ec94aa054..54611b0ad3 100644
--- a/core/src/test/java/org/apache/calcite/plan/RelOptUtilTest.java
+++ b/core/src/test/java/org/apache/calcite/plan/RelOptUtilTest.java
@@ -664,12 +664,12 @@ class RelOptUtilTest {
rexBuilder.makeCast(
fieldTypeEmpnoNullable,
RexInputRef.of(0, agg.getRowType()),
- true),
+ true, false),
RexInputRef.of(1, agg.getRowType()),
rexBuilder.makeCast(
fieldTypeJobCntNullable,
RexInputRef.of(2, agg.getRowType()),
- true))
+ true, false))
.build();
assertThat(castNode.explain(), is(expectNode.explain()));
diff --git a/core/src/test/java/org/apache/calcite/rex/RexProgramBuilderBase.java b/core/src/test/java/org/apache/calcite/rex/RexProgramBuilderBase.java
index 26daffb38f..dfa1269add 100644
--- a/core/src/test/java/org/apache/calcite/rex/RexProgramBuilderBase.java
+++ b/core/src/test/java/org/apache/calcite/rex/RexProgramBuilderBase.java
@@ -241,7 +241,7 @@ public abstract class RexProgramBuilderBase {
* @return call to CAST operator
*/
protected RexNode abstractCast(RexNode e, RelDataType type) {
- return rexBuilder.makeAbstractCast(type, e);
+ return rexBuilder.makeAbstractCast(type, e, false);
}
/**
diff --git a/core/src/test/java/org/apache/calcite/rex/RexProgramTest.java b/core/src/test/java/org/apache/calcite/rex/RexProgramTest.java
index 0a570e3a45..6b094e1f54 100644
--- a/core/src/test/java/org/apache/calcite/rex/RexProgramTest.java
+++ b/core/src/test/java/org/apache/calcite/rex/RexProgramTest.java
@@ -1759,21 +1759,24 @@ class RexProgramTest extends RexProgramTestBase {
// =>
// "=(CAST(?0.int0):INTEGER NOT NULL, 5)"
checkSimplify(
- and(ne(rexBuilder.makeCast(intType, intExpr, true), literal3),
- eq(rexBuilder.makeCast(intType, intExpr, true), literal5)),
+ and(ne(rexBuilder.makeCast(intType, intExpr, true, false), literal3),
+ eq(rexBuilder.makeCast(intType, intExpr, true, false), literal5)),
"=(CAST(?0.int0):INTEGER NOT NULL, 5)");
// "AND(<>(CAST(?0.int0):INTEGER NOT NULL, 3), =(CAST(?0.int0):INTEGER NOT NULL, 5))"
// =>
// "=(CAST(?0.int0):INTEGER NOT NULL, 5)"
checkSimplify(
- and(ne(rexBuilder.makeCast(intType, intExpr, true), literal3),
- eq(rexBuilder.makeCast(intType, intExpr, true), literal5)),
+ and(ne(rexBuilder.makeCast(intType, intExpr, true, false), literal3),
+ eq(rexBuilder.makeCast(intType, intExpr, true, false), literal5)),
"=(CAST(?0.int0):INTEGER NOT NULL, 5)");
// "AND(<>(CAST(?0.int0):INTEGER NOT NULL, 3), =(?0.int0, 5))"
// =>
// "AND(<>(CAST(?0.int0):INTEGER NOT NULL, 3), =(?0.int0, 5))"
checkSimplifyUnchanged(
- and(ne(rexBuilder.makeCast(intType, intExpr, true), literal3), eq(intExpr, literal5)));
+ and(
+ ne(
+ rexBuilder.makeCast(intType, intExpr, true, false),
+ literal3), eq(intExpr, literal5)));
}
@Test void testSimplifyAndIsNull() {
diff --git a/core/src/test/java/org/apache/calcite/sql/test/SqlAdvisorTest.java b/core/src/test/java/org/apache/calcite/sql/test/SqlAdvisorTest.java
index e9443a0626..2d62211df0 100644
--- a/core/src/test/java/org/apache/calcite/sql/test/SqlAdvisorTest.java
+++ b/core/src/test/java/org/apache/calcite/sql/test/SqlAdvisorTest.java
@@ -224,6 +224,7 @@ class SqlAdvisorTest extends SqlValidatorTestCase {
"KEYWORD(ROW)",
"KEYWORD(ROW_NUMBER)",
"KEYWORD(RUNNING)",
+ "KEYWORD(SAFE_CAST)",
"KEYWORD(SECOND)",
"KEYWORD(SESSION_USER)",
"KEYWORD(SOME)",
diff --git a/core/src/test/resources/sql/winagg.iq b/core/src/test/resources/sql/winagg.iq
index da9786d817..e6228a3966 100644
--- a/core/src/test/resources/sql/winagg.iq
+++ b/core/src/test/resources/sql/winagg.iq
@@ -78,6 +78,26 @@ from emp;
!ok
+# STDDEV applied to nullable column
+select empno,
+ stddev(comm) over (order by empno rows unbounded preceding) as stdev
+from emp
+where deptno = 30
+order by 1;
++-------+-------------------------------------------------+
+| EMPNO | STDEV |
++-------+-------------------------------------------------+
+| 7499 | |
+| 7521 | 141.421356237309510106570087373256683349609375 |
+| 7654 | 585.9465277082316561063635163009166717529296875 |
+| 7698 | 585.9465277082316561063635163009166717529296875 |
+| 7844 | 602.7713773341707792496890760958194732666015625 |
+| 7900 | 602.7713773341707792496890760958194732666015625 |
++-------+-------------------------------------------------+
+(6 rows)
+
+!ok
+
!use post
# [CALCITE-1540] Support multiple columns in PARTITION BY clause of window function
select gender,deptno,
diff --git a/piglet/src/main/java/org/apache/calcite/piglet/PigRelBuilder.java b/piglet/src/main/java/org/apache/calcite/piglet/PigRelBuilder.java
index d42e49e454..79420e2c5c 100644
--- a/piglet/src/main/java/org/apache/calcite/piglet/PigRelBuilder.java
+++ b/piglet/src/main/java/org/apache/calcite/piglet/PigRelBuilder.java
@@ -357,7 +357,8 @@ public class PigRelBuilder extends RelBuilder {
projectionExprs.add(fieldProject);
} else {
// Different types, CAST is required
- projectionExprs.add(getRexBuilder().makeCast(outputField.getType(), fieldProject));
+ projectionExprs.add(
+ getRexBuilder().makeCast(outputField.getType(), fieldProject));
}
} else {
final RelDataType columnType = outputField.getType();
diff --git a/site/_docs/reference.md b/site/_docs/reference.md
index ddd8dd7e3a..9bd3fbd8a8 100644
--- a/site/_docs/reference.md
+++ b/site/_docs/reference.md
@@ -903,6 +903,7 @@ ROUTINE_SCHEMA,
ROW_COUNT,
**ROW_NUMBER**,
**RUNNING**,
+**SAFE_CAST**,
**SATURDAY**,
**SAVEPOINT**,
SCALAR,
@@ -2709,6 +2710,7 @@ BigQuery's type system uses confusingly different names for types and functions:
| h s | string1 NOT RLIKE string2 | Whether *string1* does not match regex pattern *string2* (similar to `NOT LIKE`, but uses Java regex)
| b o | RPAD(string, length[, pattern ]) | Returns a string or bytes value that consists of *string* appended to *length* with *pattern*
| b o | RTRIM(string) | Returns *string* with all blanks removed from the end
+| b | SAFE_CAST(value AS type) | Converts *value* to *type*, returning NULL if conversion fails
| b m p | SHA1(string) | Calculates a SHA-1 hash value of *string* and returns it as a hex string
| b o | SINH(numeric) | Returns the hyperbolic sine of *numeric*
| b m o p | SOUNDEX(string) | Returns the phonetic representation of *string*; throws if *string* is encoded with multi-byte encoding such as UTF-8
diff --git a/testkit/src/main/java/org/apache/calcite/sql/test/SqlOperatorFixture.java b/testkit/src/main/java/org/apache/calcite/sql/test/SqlOperatorFixture.java
index 869ab5ca26..70ae0a89b7 100644
--- a/testkit/src/main/java/org/apache/calcite/sql/test/SqlOperatorFixture.java
+++ b/testkit/src/main/java/org/apache/calcite/sql/test/SqlOperatorFixture.java
@@ -600,71 +600,78 @@ public interface SqlOperatorFixture extends AutoCloseable {
default String getCastString(
String value,
String targetType,
- boolean errorLoc) {
+ boolean errorLoc,
+ boolean safe) {
if (errorLoc) {
value = "^" + value + "^";
}
- return "cast(" + value + " as " + targetType + ")";
+ String function = safe ? "safe_cast" : "cast";
+ return function + "(" + value + " as " + targetType + ")";
}
default void checkCastToApproxOkay(String value, String targetType,
- Object expected) {
- checkScalarApprox(getCastString(value, targetType, false),
- targetType + NON_NULLABLE_SUFFIX, expected);
+ Object expected, boolean safe) {
+ checkScalarApprox(getCastString(value, targetType, false, safe),
+ getTargetType(targetType, safe), expected);
}
default void checkCastToStringOkay(String value, String targetType,
- String expected) {
- checkString(getCastString(value, targetType, false), expected,
- targetType + NON_NULLABLE_SUFFIX);
+ String expected, boolean safe) {
+ final String castString = getCastString(value, targetType, false, safe);
+ checkString(castString, expected, getTargetType(targetType, safe));
}
default void checkCastToScalarOkay(String value, String targetType,
- String expected) {
- checkScalarExact(getCastString(value, targetType, false),
- targetType + NON_NULLABLE_SUFFIX,
- expected);
+ String expected, boolean safe) {
+ final String castString = getCastString(value, targetType, false, safe);
+ checkScalarExact(castString, getTargetType(targetType, safe), expected);
}
- default void checkCastToScalarOkay(String value, String targetType) {
- checkCastToScalarOkay(value, targetType, value);
+ default String getTargetType(String targetType, boolean safe) {
+ return safe ? targetType : targetType + NON_NULLABLE_SUFFIX;
+ }
+
+ default void checkCastToScalarOkay(String value, String targetType,
+ boolean safe) {
+ checkCastToScalarOkay(value, targetType, value, safe);
}
default void checkCastFails(String value, String targetType,
- String expectedError, boolean runtime) {
- checkFails(getCastString(value, targetType, !runtime), expectedError,
- runtime);
+ String expectedError, boolean runtime, boolean safe) {
+ final String castString = getCastString(value, targetType, !runtime, safe);
+ checkFails(castString, expectedError, runtime);
}
- default void checkCastToString(String value, String type,
- @Nullable String expected) {
+ default void checkCastToString(String value, @Nullable String type,
+ @Nullable String expected, boolean safe) {
String spaces = " ";
if (expected == null) {
expected = value.trim();
}
int len = expected.length();
if (type != null) {
- value = getCastString(value, type, false);
+ value = getCastString(value, type, false, safe);
}
// currently no exception thrown for truncation
if (Bug.DT239_FIXED) {
checkCastFails(value,
"VARCHAR(" + (len - 1) + ")", STRING_TRUNC_MESSAGE,
- true);
+ true, safe);
}
- checkCastToStringOkay(value, "VARCHAR(" + len + ")", expected);
- checkCastToStringOkay(value, "VARCHAR(" + (len + 5) + ")", expected);
+ checkCastToStringOkay(value, "VARCHAR(" + len + ")", expected, safe);
+ checkCastToStringOkay(value, "VARCHAR(" + (len + 5) + ")", expected, safe);
// currently no exception thrown for truncation
if (Bug.DT239_FIXED) {
checkCastFails(value,
"CHAR(" + (len - 1) + ")", STRING_TRUNC_MESSAGE,
- true);
+ true, safe);
}
- checkCastToStringOkay(value, "CHAR(" + len + ")", expected);
- checkCastToStringOkay(value, "CHAR(" + (len + 5) + ")", expected + spaces);
+ checkCastToStringOkay(value, "CHAR(" + len + ")", expected, safe);
+ checkCastToStringOkay(value, "CHAR(" + (len + 5) + ")",
+ expected + spaces, safe);
}
}
diff --git a/testkit/src/main/java/org/apache/calcite/test/RexImplicationCheckerFixtures.java b/testkit/src/main/java/org/apache/calcite/test/RexImplicationCheckerFixtures.java
index c447394498..ed6d468a39 100644
--- a/testkit/src/main/java/org/apache/calcite/test/RexImplicationCheckerFixtures.java
+++ b/testkit/src/main/java/org/apache/calcite/test/RexImplicationCheckerFixtures.java
@@ -230,7 +230,7 @@ public interface RexImplicationCheckerFixtures {
}
public RexNode cast(RelDataType type, RexNode exp) {
- return rexBuilder.makeCast(type, exp, true);
+ return rexBuilder.makeCast(type, exp, true, false);
}
void checkImplies(RexNode node1, RexNode node2) {
diff --git a/testkit/src/main/java/org/apache/calcite/test/SqlOperatorFixtures.java b/testkit/src/main/java/org/apache/calcite/test/SqlOperatorFixtures.java
new file mode 100644
index 0000000000..1d91c97759
--- /dev/null
+++ b/testkit/src/main/java/org/apache/calcite/test/SqlOperatorFixtures.java
@@ -0,0 +1,116 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to you under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+package org.apache.calcite.test;
+
+import org.apache.calcite.sql.test.SqlOperatorFixture;
+import org.apache.calcite.util.DelegatingInvocationHandler;
+
+import org.checkerframework.checker.nullness.qual.Nullable;
+
+import java.lang.reflect.Proxy;
+import java.util.regex.Pattern;
+
+/** Utilities for {@link SqlOperatorFixture}. */
+class SqlOperatorFixtures {
+ private SqlOperatorFixtures() {
+ }
+
+ /** Returns a fixture that converts each CAST test into a test for
+ * SAFE_CAST. */
+ static SqlOperatorFixture safeCastWrapper(SqlOperatorFixture fixture) {
+ return (SqlOperatorFixture) Proxy.newProxyInstance(
+ SqlOperatorTest.class.getClassLoader(),
+ new Class[]{SqlOperatorFixture.class},
+ new SqlOperatorFixtureInvocationHandler(fixture));
+ }
+
+ /** A helper for {@link #safeCastWrapper(SqlOperatorFixture)} that provides
+ * alternative implementations of methods in {@link SqlOperatorFixture}.
+ *
+ * <p>Must be public, so that its methods can be seen via reflection. */
+ @SuppressWarnings("unused")
+ public static class SqlOperatorFixtureInvocationHandler
+ extends DelegatingInvocationHandler {
+ static final Pattern CAST_PATTERN = Pattern.compile("(?i)\\bCAST\\(");
+ static final Pattern NOT_NULL_PATTERN = Pattern.compile(" NOT NULL");
+
+ final SqlOperatorFixture f;
+
+ SqlOperatorFixtureInvocationHandler(SqlOperatorFixture f) {
+ this.f = f;
+ }
+
+ @Override protected Object getTarget() {
+ return f;
+ }
+
+ String addSafe(String sql) {
+ return CAST_PATTERN.matcher(sql).replaceAll("SAFE_CAST(");
+ }
+
+ String removeNotNull(String type) {
+ return NOT_NULL_PATTERN.matcher(type).replaceAll("");
+ }
+
+ /** Proxy for
+ * {@link SqlOperatorFixture#checkCastToString(String, String, String, boolean)}. */
+ public void checkCastToString(String value, @Nullable String type,
+ @Nullable String expected, boolean safe) {
+ f.checkCastToString(addSafe(value),
+ type == null ? null : removeNotNull(type), expected, safe);
+ }
+
+ /** Proxy for {@link SqlOperatorFixture#checkBoolean(String, Boolean)}. */
+ public void checkFails(String expression, @Nullable Boolean result) {
+ f.checkBoolean(addSafe(expression), result);
+ }
+
+ /** Proxy for {@link SqlOperatorFixture#checkNull(String)}. */
+ public void checkNull(String expression) {
+ f.checkNull(addSafe(expression));
+ }
+
+ /** Proxy for
+ * {@link SqlOperatorFixture#checkFails(String, String, boolean)}. */
+ public void checkFails(String expression, String expectedError, boolean runtime) {
+ f.checkFails(addSafe(expression), expectedError, runtime);
+ }
+
+ /** Proxy for
+ * {@link SqlOperatorFixture#checkScalar(String, Object, String)}. */
+ public void checkScalar(String expression, Object result, String resultType) {
+ f.checkScalar(addSafe(expression), result, removeNotNull(resultType));
+ }
+
+ /** Proxy for {@link SqlOperatorFixture#checkScalarExact(String, int)}. */
+ public void checkScalarExact(String expression, int result) {
+ f.checkScalarExact(addSafe(expression), result);
+ }
+
+ /** Proxy for
+ * {@link SqlOperatorFixture#checkScalarExact(String, String, String)}. */
+ public void checkScalarExact(String expression, String expectedType, String result) {
+ f.checkScalarExact(addSafe(expression), removeNotNull(expectedType), result);
+ }
+
+ /** Proxy for
+ * {@link SqlOperatorFixture#checkString(String, String, String)}. */
+ public void checkString(String expression, String result, String resultType) {
+ f.checkString(addSafe(expression), result, removeNotNull(resultType));
+ }
+ }
+}
diff --git a/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java b/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
index 523cbe0609..bb9f055c50 100644
--- a/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
+++ b/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
@@ -80,6 +80,8 @@ import org.junit.jupiter.api.Disabled;
import org.junit.jupiter.api.Tag;
import org.junit.jupiter.api.Test;
import org.junit.jupiter.params.ParameterizedTest;
+import org.junit.jupiter.params.provider.Arguments;
+import org.junit.jupiter.params.provider.MethodSource;
import org.junit.jupiter.params.provider.ValueSource;
import org.slf4j.Logger;
@@ -102,6 +104,7 @@ import java.util.TimeZone;
import java.util.function.Consumer;
import java.util.function.UnaryOperator;
import java.util.regex.Pattern;
+import java.util.stream.Stream;
import static org.apache.calcite.linq4j.tree.Expressions.list;
import static org.apache.calcite.rel.type.RelDataTypeImpl.NON_NULLABLE_SUFFIX;
@@ -425,21 +428,48 @@ public class SqlOperatorTest {
true);
}
- @Test void testCastToString() {
- final SqlOperatorFixture f = fixture();
+ /** Tests that CAST and SAFE_CAST are basically equivalent but SAFE_CAST is
+ * only available in BigQuery library. */
+ @Test void testCastVsSafeCast() {
+ // SAFE_CAST is available in BigQuery library but not by default
+ final SqlOperatorFixture f0 = fixture();
+ f0.checkScalar("cast(12 + 3 as varchar(10))", "15", "VARCHAR(10) NOT NULL");
+ f0.checkFails("^safe_cast(12 + 3 as varchar(10))^",
+ "No match found for function signature SAFE_CAST\\(<NUMERIC>, <CHARACTER>\\)",
+ false);
+
+ final SqlOperatorFixture f = f0.withLibrary(SqlLibrary.BIG_QUERY);
+ f.checkScalar("cast(12 + 3 as varchar(10))", "15", "VARCHAR(10) NOT NULL");
+ f.checkScalar("safe_cast(12 + 3 as varchar(10))", "15", "VARCHAR(10)");
+ }
+
+ /** Generates parameters to test both regular and safe cast. */
+ static Stream<Arguments> safeParameters() {
+ SqlOperatorFixture f = SqlOperatorFixtureImpl.DEFAULT;
+ SqlOperatorFixture f2 = f.withLibrary(SqlLibrary.BIG_QUERY);
+ SqlOperatorFixture f3 = SqlOperatorFixtures.safeCastWrapper(f2);
+ return Stream.of(
+ () -> new Object[] {false, f},
+ () -> new Object[] {true, f3});
+ }
+
+ @ParameterizedTest
+ @MethodSource("safeParameters")
+ void testCastToString(boolean safe, SqlOperatorFixture f) {
f.setFor(SqlStdOperatorTable.CAST, VmName.EXPAND);
f.checkCastToString("cast(cast('abc' as char(4)) as varchar(6))", null,
- "abc ");
+ "abc ", safe);
// integer
- f.checkCastToString("123", "CHAR(3)", "123");
- f.checkCastToString("0", "CHAR", "0");
- f.checkCastToString("-123", "CHAR(4)", "-123");
+ f.checkCastToString("123", "CHAR(3)", "123", safe);
+
+ f.checkCastToString("0", "CHAR", "0", safe);
+ f.checkCastToString("-123", "CHAR(4)", "-123", safe);
// decimal
- f.checkCastToString("123.4", "CHAR(5)", "123.4");
- f.checkCastToString("-0.0", "CHAR(2)", ".0");
- f.checkCastToString("-123.4", "CHAR(6)", "-123.4");
+ f.checkCastToString("123.4", "CHAR(5)", "123.4", safe);
+ f.checkCastToString("-0.0", "CHAR(2)", ".0", safe);
+ f.checkCastToString("-123.4", "CHAR(6)", "-123.4", safe);
f.checkString("cast(1.29 as varchar(10))", "1.29", "VARCHAR(10) NOT NULL");
f.checkString("cast(.48 as varchar(10))", ".48", "VARCHAR(10) NOT NULL");
@@ -451,33 +481,33 @@ public class SqlOperatorTest {
"-1.29", "VARCHAR(10) NOT NULL");
// approximate
- f.checkCastToString("1.23E45", "CHAR(7)", "1.23E45");
- f.checkCastToString("CAST(0 AS DOUBLE)", "CHAR(3)", "0E0");
- f.checkCastToString("-1.20e-07", "CHAR(7)", "-1.2E-7");
- f.checkCastToString("cast(0e0 as varchar(5))", "CHAR(3)", "0E0");
+ f.checkCastToString("1.23E45", "CHAR(7)", "1.23E45", safe);
+ f.checkCastToString("CAST(0 AS DOUBLE)", "CHAR(3)", "0E0", safe);
+ f.checkCastToString("-1.20e-07", "CHAR(7)", "-1.2E-7", safe);
+ f.checkCastToString("cast(0e0 as varchar(5))", "CHAR(3)", "0E0", safe);
if (TODO) {
f.checkCastToString("cast(-45e-2 as varchar(17))", "CHAR(7)",
- "-4.5E-1");
+ "-4.5E-1", safe);
}
if (TODO) {
f.checkCastToString("cast(4683442.3432498375e0 as varchar(20))",
"CHAR(19)",
- "4.683442343249838E6");
+ "4.683442343249838E6", safe);
}
if (TODO) {
- f.checkCastToString("cast(-0.1 as real)", "CHAR(5)", "-1E-1");
+ f.checkCastToString("cast(-0.1 as real)", "CHAR(5)", "-1E-1", safe);
}
f.checkString("cast(1.3243232e0 as varchar(4))", "1.32",
"VARCHAR(4) NOT NULL");
f.checkString("cast(1.9e5 as char(4))", "1.9E", "CHAR(4) NOT NULL");
// string
- f.checkCastToString("'abc'", "CHAR(1)", "a");
- f.checkCastToString("'abc'", "CHAR(3)", "abc");
- f.checkCastToString("cast('abc' as varchar(6))", "CHAR(3)", "abc");
- f.checkCastToString("cast(' abc ' as varchar(10))", null, " abc ");
+ f.checkCastToString("'abc'", "CHAR(1)", "a", safe);
+ f.checkCastToString("'abc'", "CHAR(3)", "abc", safe);
+ f.checkCastToString("cast('abc' as varchar(6))", "CHAR(3)", "abc", safe);
+ f.checkCastToString("cast(' abc ' as varchar(10))", null, " abc ", safe);
f.checkCastToString("cast(cast('abc' as char(4)) as varchar(6))", null,
- "abc ");
+ "abc ", safe);
f.checkString("cast(cast('a' as char(2)) as varchar(3)) || 'x' ",
"a x", "VARCHAR(4) NOT NULL");
f.checkString("cast(cast('a' as char(3)) as varchar(5)) || 'x' ",
@@ -497,26 +527,26 @@ public class SqlOperatorTest {
"INTEGER NOT NULL");
// date & time
- f.checkCastToString("date '2008-01-01'", "CHAR(10)", "2008-01-01");
- f.checkCastToString("time '1:2:3'", "CHAR(8)", "01:02:03");
+ f.checkCastToString("date '2008-01-01'", "CHAR(10)", "2008-01-01", safe);
+ f.checkCastToString("time '1:2:3'", "CHAR(8)", "01:02:03", safe);
f.checkCastToString("timestamp '2008-1-1 1:2:3'", "CHAR(19)",
- "2008-01-01 01:02:03");
+ "2008-01-01 01:02:03", safe);
f.checkCastToString("timestamp '2008-1-1 1:2:3'", "VARCHAR(30)",
- "2008-01-01 01:02:03");
+ "2008-01-01 01:02:03", safe);
- f.checkCastToString("interval '3-2' year to month", "CHAR(5)", "+3-02");
- f.checkCastToString("interval '32' month", "CHAR(3)", "+32");
+ f.checkCastToString("interval '3-2' year to month", "CHAR(5)", "+3-02", safe);
+ f.checkCastToString("interval '32' month", "CHAR(3)", "+32", safe);
f.checkCastToString("interval '1 2:3:4' day to second", "CHAR(11)",
- "+1 02:03:04");
+ "+1 02:03:04", safe);
f.checkCastToString("interval '1234.56' second(4,2)", "CHAR(8)",
- "+1234.56");
- f.checkCastToString("interval '60' day", "CHAR(8)", "+60 ");
+ "+1234.56", safe);
+ f.checkCastToString("interval '60' day", "CHAR(8)", "+60 ", safe);
// boolean
- f.checkCastToString("True", "CHAR(4)", "TRUE");
- f.checkCastToString("True", "CHAR(6)", "TRUE ");
- f.checkCastToString("True", "VARCHAR(6)", "TRUE");
- f.checkCastToString("False", "CHAR(5)", "FALSE");
+ f.checkCastToString("True", "CHAR(4)", "TRUE", safe);
+ f.checkCastToString("True", "CHAR(6)", "TRUE ", safe);
+ f.checkCastToString("True", "VARCHAR(6)", "TRUE", safe);
+ f.checkCastToString("False", "CHAR(5)", "FALSE", safe);
f.checkString("cast(true as char(3))", "TRU", "CHAR(3) NOT NULL");
f.checkString("cast(false as char(4))", "FALS", "CHAR(4) NOT NULL");
@@ -524,8 +554,9 @@ public class SqlOperatorTest {
f.checkString("cast(false as varchar(4))", "FALS", "VARCHAR(4) NOT NULL");
}
- @Test void testCastExactNumericLimits() {
- final SqlOperatorFixture f = fixture();
+ @ParameterizedTest
+ @MethodSource("safeParameters")
+ void testCastExactNumericLimits(boolean safe, SqlOperatorFixture f) {
f.setFor(SqlStdOperatorTable.CAST, VmName.EXPAND);
// Test casting for min,max, out of range for exact numeric types
@@ -542,72 +573,74 @@ public class SqlOperatorTest {
}
// Convert from literal to type
- f.checkCastToScalarOkay(numeric.maxNumericString, type);
- f.checkCastToScalarOkay(numeric.minNumericString, type);
+ f.checkCastToScalarOkay(numeric.maxNumericString, type, safe);
+ f.checkCastToScalarOkay(numeric.minNumericString, type, safe);
// Overflow test
if (numeric == Numeric.BIGINT) {
// Literal of range
f.checkCastFails(numeric.maxOverflowNumericString,
- type, LITERAL_OUT_OF_RANGE_MESSAGE, false);
+ type, LITERAL_OUT_OF_RANGE_MESSAGE, false, safe);
f.checkCastFails(numeric.minOverflowNumericString,
- type, LITERAL_OUT_OF_RANGE_MESSAGE, false);
+ type, LITERAL_OUT_OF_RANGE_MESSAGE, false, safe);
} else {
if (Bug.CALCITE_2539_FIXED) {
f.checkCastFails(numeric.maxOverflowNumericString,
- type, OUT_OF_RANGE_MESSAGE, true);
+ type, OUT_OF_RANGE_MESSAGE, true, safe);
f.checkCastFails(numeric.minOverflowNumericString,
- type, OUT_OF_RANGE_MESSAGE, true);
+ type, OUT_OF_RANGE_MESSAGE, true, safe);
}
}
// Convert from string to type
f.checkCastToScalarOkay("'" + numeric.maxNumericString + "'",
- type, numeric.maxNumericString);
+ type, numeric.maxNumericString, safe);
f.checkCastToScalarOkay("'" + numeric.minNumericString + "'",
- type, numeric.minNumericString);
+ type, numeric.minNumericString, safe);
if (Bug.CALCITE_2539_FIXED) {
f.checkCastFails("'" + numeric.maxOverflowNumericString + "'",
- type, OUT_OF_RANGE_MESSAGE, true);
+ type, OUT_OF_RANGE_MESSAGE, true, safe);
f.checkCastFails("'" + numeric.minOverflowNumericString + "'",
- type, OUT_OF_RANGE_MESSAGE, true);
+ type, OUT_OF_RANGE_MESSAGE, true, safe);
}
// Convert from type to string
- f.checkCastToString(numeric.maxNumericString, null, null);
- f.checkCastToString(numeric.maxNumericString, type, null);
+ f.checkCastToString(numeric.maxNumericString, null, null, safe);
+ f.checkCastToString(numeric.maxNumericString, type, null, safe);
- f.checkCastToString(numeric.minNumericString, null, null);
- f.checkCastToString(numeric.minNumericString, type, null);
+ f.checkCastToString(numeric.minNumericString, null, null, safe);
+ f.checkCastToString(numeric.minNumericString, type, null, safe);
if (Bug.CALCITE_2539_FIXED) {
- f.checkCastFails("'notnumeric'", type, INVALID_CHAR_MESSAGE, true);
+ f.checkCastFails("'notnumeric'", type, INVALID_CHAR_MESSAGE, true,
+ safe);
}
});
}
- @Test void testCastToExactNumeric() {
- final SqlOperatorFixture f = fixture();
+ @ParameterizedTest
+ @MethodSource("safeParameters")
+ void testCastToExactNumeric(boolean safe, SqlOperatorFixture f) {
f.setFor(SqlStdOperatorTable.CAST, VmName.EXPAND);
- f.checkCastToScalarOkay("1", "BIGINT");
- f.checkCastToScalarOkay("1", "INTEGER");
- f.checkCastToScalarOkay("1", "SMALLINT");
- f.checkCastToScalarOkay("1", "TINYINT");
- f.checkCastToScalarOkay("1", "DECIMAL(4, 0)");
- f.checkCastToScalarOkay("-1", "BIGINT");
- f.checkCastToScalarOkay("-1", "INTEGER");
- f.checkCastToScalarOkay("-1", "SMALLINT");
- f.checkCastToScalarOkay("-1", "TINYINT");
- f.checkCastToScalarOkay("-1", "DECIMAL(4, 0)");
-
- f.checkCastToScalarOkay("1.234E3", "INTEGER", "1234");
- f.checkCastToScalarOkay("-9.99E2", "INTEGER", "-999");
- f.checkCastToScalarOkay("'1'", "INTEGER", "1");
- f.checkCastToScalarOkay("' 01 '", "INTEGER", "1");
- f.checkCastToScalarOkay("'-1'", "INTEGER", "-1");
- f.checkCastToScalarOkay("' -00 '", "INTEGER", "0");
+ f.checkCastToScalarOkay("1", "BIGINT", safe);
+ f.checkCastToScalarOkay("1", "INTEGER", safe);
+ f.checkCastToScalarOkay("1", "SMALLINT", safe);
+ f.checkCastToScalarOkay("1", "TINYINT", safe);
+ f.checkCastToScalarOkay("1", "DECIMAL(4, 0)", safe);
+ f.checkCastToScalarOkay("-1", "BIGINT", safe);
+ f.checkCastToScalarOkay("-1", "INTEGER", safe);
+ f.checkCastToScalarOkay("-1", "SMALLINT", safe);
+ f.checkCastToScalarOkay("-1", "TINYINT", safe);
+ f.checkCastToScalarOkay("-1", "DECIMAL(4, 0)", safe);
+
+ f.checkCastToScalarOkay("1.234E3", "INTEGER", "1234", safe);
+ f.checkCastToScalarOkay("-9.99E2", "INTEGER", "-999", safe);
+ f.checkCastToScalarOkay("'1'", "INTEGER", "1", safe);
+ f.checkCastToScalarOkay("' 01 '", "INTEGER", "1", safe);
+ f.checkCastToScalarOkay("'-1'", "INTEGER", "-1", safe);
+ f.checkCastToScalarOkay("' -00 '", "INTEGER", "0", safe);
// string to integer
f.checkScalarExact("cast('6543' as integer)", 6543);
@@ -617,8 +650,9 @@ public class SqlOperatorTest {
"654342432412312");
}
- @Test void testCastStringToDecimal() {
- final SqlOperatorFixture f = fixture();
+ @ParameterizedTest
+ @MethodSource("safeParameters")
+ void testCastStringToDecimal(boolean safe, SqlOperatorFixture f) {
f.setFor(SqlStdOperatorTable.CAST, VmName.EXPAND);
if (!DECIMAL) {
return;
@@ -646,8 +680,9 @@ public class SqlOperatorTest {
true);
}
- @Test void testCastIntervalToNumeric() {
- final SqlOperatorFixture f = fixture();
+ @ParameterizedTest
+ @MethodSource("safeParameters")
+ void testCastIntervalToNumeric(boolean safe, SqlOperatorFixture f) {
f.setFor(SqlStdOperatorTable.CAST, VmName.EXPAND);
// interval to decimal
@@ -753,8 +788,9 @@ public class SqlOperatorTest {
"-1");
}
- @Test void testCastToInterval() {
- final SqlOperatorFixture f = fixture();
+ @ParameterizedTest
+ @MethodSource("safeParameters")
+ void testCastToInterval(boolean safe, SqlOperatorFixture f) {
f.setFor(SqlStdOperatorTable.CAST, VmName.EXPAND);
f.checkScalar(
"cast(5 as interval second)",
@@ -807,8 +843,9 @@ public class SqlOperatorTest {
"INTERVAL MINUTE(4) NOT NULL");
}
- @Test void testCastIntervalToInterval() {
- final SqlOperatorFixture f = fixture();
+ @ParameterizedTest
+ @MethodSource("safeParameters")
+ void testCastIntervalToInterval(boolean safe, SqlOperatorFixture f) {
f.checkScalar("cast(interval '2 5' day to hour as interval hour to minute)",
"+53:00",
"INTERVAL HOUR TO MINUTE NOT NULL");
@@ -826,8 +863,9 @@ public class SqlOperatorTest {
"INTERVAL DAY TO HOUR NOT NULL");
}
- @Test void testCastWithRoundingToScalar() {
- final SqlOperatorFixture f = fixture();
+ @ParameterizedTest
+ @MethodSource("safeParameters")
+ void testCastWithRoundingToScalar(boolean safe, SqlOperatorFixture f) {
f.setFor(SqlStdOperatorTable.CAST, VmName.EXPAND);
f.checkFails("cast(1.25 as int)", "INTEGER", true);
@@ -866,8 +904,9 @@ public class SqlOperatorTest {
true);
}
- @Test void testCastDecimalToDoubleToInteger() {
- final SqlOperatorFixture f = fixture();
+ @ParameterizedTest
+ @MethodSource("safeParameters")
+ void testCastDecimalToDoubleToInteger(boolean safe, SqlOperatorFixture f) {
f.setFor(SqlStdOperatorTable.CAST, VmName.EXPAND);
f.checkFails("cast( cast(1.25 as double) as integer)", OUT_OF_RANGE_MESSAGE, true);
@@ -881,8 +920,9 @@ public class SqlOperatorTest {
f.checkFails("cast( cast(-1.5 as double) as integer)", OUT_OF_RANGE_MESSAGE, true);
}
- @Test void testCastApproxNumericLimits() {
- final SqlOperatorFixture f = fixture();
+ @ParameterizedTest
+ @MethodSource("safeParameters")
+ void testCastApproxNumericLimits(boolean safe, SqlOperatorFixture f) {
f.setFor(SqlStdOperatorTable.CAST, VmName.EXPAND);
// Test casting for min, max, out of range for approx numeric types
@@ -911,43 +951,43 @@ public class SqlOperatorTest {
f.checkCastToApproxOkay(numeric.maxNumericString, type,
isFloat
? isWithin(numeric.maxNumericAsDouble(), 1E32)
- : isExactly(numeric.maxNumericAsDouble()));
+ : isExactly(numeric.maxNumericAsDouble()), safe);
f.checkCastToApproxOkay(numeric.minNumericString, type,
- isExactly(numeric.minNumericString));
+ isExactly(numeric.minNumericString), safe);
if (isFloat) {
f.checkCastFails(numeric.maxOverflowNumericString, type,
- OUT_OF_RANGE_MESSAGE, true);
+ OUT_OF_RANGE_MESSAGE, true, safe);
} else {
// Double: Literal out of range
f.checkCastFails(numeric.maxOverflowNumericString, type,
- LITERAL_OUT_OF_RANGE_MESSAGE, false);
+ LITERAL_OUT_OF_RANGE_MESSAGE, false, safe);
}
// Underflow: goes to 0
f.checkCastToApproxOkay(numeric.minOverflowNumericString, type,
- isExactly(0));
+ isExactly(0), safe);
// Convert from string to type
f.checkCastToApproxOkay("'" + numeric.maxNumericString + "'", type,
isFloat
? isWithin(numeric.maxNumericAsDouble(), 1E32)
- : isExactly(numeric.maxNumericAsDouble()));
+ : isExactly(numeric.maxNumericAsDouble()), safe);
f.checkCastToApproxOkay("'" + numeric.minNumericString + "'", type,
- isExactly(numeric.minNumericAsDouble()));
+ isExactly(numeric.minNumericAsDouble()), safe);
f.checkCastFails("'" + numeric.maxOverflowNumericString + "'", type,
- OUT_OF_RANGE_MESSAGE, true);
+ OUT_OF_RANGE_MESSAGE, true, safe);
// Underflow: goes to 0
f.checkCastToApproxOkay("'" + numeric.minOverflowNumericString + "'",
- type, isExactly(0));
+ type, isExactly(0), safe);
// Convert from type to string
// Treated as DOUBLE
f.checkCastToString(numeric.maxNumericString, null,
- isFloat ? null : "1.79769313486231E308");
+ isFloat ? null : "1.79769313486231E308", safe);
// TODO: The following tests are slightly different depending on
// whether the java or fennel calc are used.
@@ -955,42 +995,45 @@ public class SqlOperatorTest {
if (false /* fennel calc*/) { // Treated as FLOAT or DOUBLE
f.checkCastToString(numeric.maxNumericString, type,
// Treated as DOUBLE
- isFloat ? "3.402824E38" : "1.797693134862316E308");
+ isFloat ? "3.402824E38" : "1.797693134862316E308", safe);
f.checkCastToString(numeric.minNumericString, null,
// Treated as FLOAT or DOUBLE
- isFloat ? null : "4.940656458412465E-324");
+ isFloat ? null : "4.940656458412465E-324", safe);
f.checkCastToString(numeric.minNumericString, type,
- isFloat ? "1.401299E-45" : "4.940656458412465E-324");
+ isFloat ? "1.401299E-45" : "4.940656458412465E-324", safe);
} else if (false /* JavaCalc */) {
// Treated as FLOAT or DOUBLE
f.checkCastToString(numeric.maxNumericString, type,
// Treated as DOUBLE
- isFloat ? "3.402823E38" : "1.797693134862316E308");
+ isFloat ? "3.402823E38" : "1.797693134862316E308", safe);
f.checkCastToString(numeric.minNumericString, null,
- isFloat ? null : null); // Treated as FLOAT or DOUBLE
+ isFloat ? null : null, safe); // Treated as FLOAT or DOUBLE
f.checkCastToString(numeric.minNumericString, type,
- isFloat ? "1.401298E-45" : null);
+ isFloat ? "1.401298E-45" : null, safe);
}
- f.checkCastFails("'notnumeric'", type, INVALID_CHAR_MESSAGE, true);
+ f.checkCastFails("'notnumeric'", type, INVALID_CHAR_MESSAGE, true, safe);
});
}
- @Test void testCastToApproxNumeric() {
- final SqlOperatorFixture f = fixture();
+ @ParameterizedTest
+ @MethodSource("safeParameters")
+ void testCastToApproxNumeric(boolean safe, SqlOperatorFixture f) {
f.setFor(SqlStdOperatorTable.CAST, VmName.EXPAND);
- f.checkCastToApproxOkay("1", "DOUBLE", isExactly(1));
- f.checkCastToApproxOkay("1.0", "DOUBLE", isExactly(1));
- f.checkCastToApproxOkay("-2.3", "FLOAT", isWithin(-2.3, 0.000001));
- f.checkCastToApproxOkay("'1'", "DOUBLE", isExactly(1));
- f.checkCastToApproxOkay("' -1e-37 '", "DOUBLE", isExactly("-1.0E-37"));
- f.checkCastToApproxOkay("1e0", "DOUBLE", isExactly(1));
- f.checkCastToApproxOkay("0e0", "REAL", isExactly(0));
+ f.checkCastToApproxOkay("1", "DOUBLE", isExactly(1), safe);
+ f.checkCastToApproxOkay("1.0", "DOUBLE", isExactly(1), safe);
+ f.checkCastToApproxOkay("-2.3", "FLOAT", isWithin(-2.3, 0.000001), safe);
+ f.checkCastToApproxOkay("'1'", "DOUBLE", isExactly(1), safe);
+ f.checkCastToApproxOkay("' -1e-37 '", "DOUBLE", isExactly("-1.0E-37"),
+ safe);
+ f.checkCastToApproxOkay("1e0", "DOUBLE", isExactly(1), safe);
+ f.checkCastToApproxOkay("0e0", "REAL", isExactly(0), safe);
}
- @Test void testCastNull() {
- final SqlOperatorFixture f = fixture();
+ @ParameterizedTest
+ @MethodSource("safeParameters")
+ void testCastNull(boolean safe, SqlOperatorFixture f) {
f.setFor(SqlStdOperatorTable.CAST, VmName.EXPAND);
// null
@@ -1007,16 +1050,28 @@ public class SqlOperatorTest {
f.checkNull("cast(null as interval year to month)");
f.checkNull("cast(null as interval day to second(3))");
f.checkNull("cast(null as boolean)");
+
+ if (safe) {
+ // In the following, 'cast' becomes 'safe_cast'
+ f.checkNull("cast('a' as time)");
+ f.checkNull("cast('a' as int)");
+ f.checkNull("cast('2023-03-17a' as date)");
+ f.checkNull("cast('12:12:11a' as time)");
+ f.checkNull("cast('a' as interval year)");
+ f.checkNull("cast('a' as interval minute to second)");
+ f.checkNull("cast('True' as bigint)");
+ }
}
/** Test case for
* <a href="https://issues.apache.org/jira/browse/CALCITE-1439">[CALCITE-1439]
* Handling errors during constant reduction</a>. */
- @Test void testCastInvalid() {
+ @ParameterizedTest
+ @MethodSource("safeParameters")
+ void testCastInvalid(boolean safe, SqlOperatorFixture f) {
// Before CALCITE-1439 was fixed, constant reduction would kick in and
// generate Java constants that throw when the class is loaded, thus
// ExceptionInInitializerError.
- final SqlOperatorFixture f = fixture();
f.checkScalarExact("cast('15' as integer)", "INTEGER NOT NULL", "15");
if (Bug.CALCITE_2539_FIXED) {
f.checkFails("cast('15.4' as integer)", "xxx", true);
@@ -1030,9 +1085,10 @@ public class SqlOperatorTest {
}
}
- @Test void testCastDateTime() {
- // Test cast for date/time/timestamp
- final SqlOperatorFixture f = fixture();
+ /** Test cast for DATE, TIME, TIMESTAMP types. */
+ @ParameterizedTest
+ @MethodSource("safeParameters")
+ void testCastDateTime(boolean safe, SqlOperatorFixture f) {
f.setFor(SqlStdOperatorTable.CAST, VmName.EXPAND);
f.checkScalar("cast(TIMESTAMP '1945-02-24 12:42:25.34' as TIMESTAMP)",
@@ -1061,17 +1117,17 @@ public class SqlOperatorTest {
"12:42:25", "TIME(0) NOT NULL");
// time <-> string
- f.checkCastToString("TIME '12:42:25'", null, "12:42:25");
+ f.checkCastToString("TIME '12:42:25'", null, "12:42:25", safe);
if (TODO) {
- f.checkCastToString("TIME '12:42:25.34'", null, "12:42:25.34");
+ f.checkCastToString("TIME '12:42:25.34'", null, "12:42:25.34", safe);
}
// Generate the current date as a string, e.g. "2007-04-18". The value
// is guaranteed to be good for at least 2 minutes, which should give
// us time to run the rest of the tests.
final String today =
- new SimpleDateFormat("yyyy-MM-dd", Locale.ROOT).format(
- getCalendarNotTooNear(Calendar.DAY_OF_MONTH).getTime());
+ new SimpleDateFormat("yyyy-MM-dd", Locale.ROOT)
+ .format(getCalendarNotTooNear(Calendar.DAY_OF_MONTH).getTime());
f.checkScalar("cast(DATE '1945-02-24' as TIMESTAMP)",
"1945-02-24 00:00:00", "TIMESTAMP(0) NOT NULL");
@@ -1096,8 +1152,9 @@ public class SqlOperatorTest {
"1945-02-24 00:00:00", "TIMESTAMP(0) NOT NULL");
}
- @Test void testCastStringToDateTime() {
- final SqlOperatorFixture f = fixture();
+ @ParameterizedTest
+ @MethodSource("safeParameters")
+ void testCastStringToDateTime(boolean safe, SqlOperatorFixture f) {
f.checkScalar("cast('12:42:25' as TIME)",
"12:42:25", "TIME(0) NOT NULL");
f.checkScalar("cast('1:42:25' as TIME)",
@@ -1123,12 +1180,12 @@ public class SqlOperatorTest {
// timestamp <-> string
f.checkCastToString("TIMESTAMP '1945-02-24 12:42:25'", null,
- "1945-02-24 12:42:25");
+ "1945-02-24 12:42:25", safe);
if (TODO) {
// TODO: casting allows one to discard precision without error
f.checkCastToString("TIMESTAMP '1945-02-24 12:42:25.34'",
- null, "1945-02-24 12:42:25.34");
+ null, "1945-02-24 12:42:25.34", safe);
}
f.checkScalar("cast('1945-02-24 12:42:25' as TIMESTAMP)",
@@ -1159,8 +1216,8 @@ public class SqlOperatorTest {
"1945-01-24 12:23:34", "TIMESTAMP(0) NOT NULL");
// date <-> string
- f.checkCastToString("DATE '1945-02-24'", null, "1945-02-24");
- f.checkCastToString("DATE '1945-2-24'", null, "1945-02-24");
+ f.checkCastToString("DATE '1945-02-24'", null, "1945-02-24", safe);
+ f.checkCastToString("DATE '1945-2-24'", null, "1945-02-24", safe);
f.checkScalar("cast('1945-02-24' as DATE)", "1945-02-24", "DATE NOT NULL");
f.checkScalar("cast(' 1945-2-4 ' as DATE)", "1945-02-04", "DATE NOT NULL");
@@ -1265,8 +1322,9 @@ public class SqlOperatorTest {
}
}
- @Test void testCastToBoolean() {
- final SqlOperatorFixture f = fixture();
+ @ParameterizedTest
+ @MethodSource("safeParameters")
+ void testCastToBoolean(boolean safe, SqlOperatorFixture f) {
f.setFor(SqlStdOperatorTable.CAST, VmName.EXPAND);
// string to boolean
@@ -9908,8 +9966,9 @@ public class SqlOperatorTest {
}
}
- @Test void testCastTruncates() {
- final SqlOperatorFixture f = fixture();
+ @ParameterizedTest
+ @MethodSource("safeParameters")
+ void testCastTruncates(boolean safe, SqlOperatorFixture f) {
f.setFor(SqlStdOperatorTable.CAST, VmName.EXPAND);
f.checkScalar("CAST('ABCD' AS CHAR(2))", "AB", "CHAR(2) NOT NULL");
f.checkScalar("CAST('ABCD' AS VARCHAR(2))", "AB",