You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@calcite.apache.org by li...@apache.org on 2023/04/17 11:38:26 UTC
[calcite] branch main updated: [CALCITE-4771] Add `TRY_CAST` function (enabled in MSSQL library)
This is an automated email from the ASF dual-hosted git repository.
libenchao pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/calcite.git
The following commit(s) were added to refs/heads/main by this push:
new 83f136167d [CALCITE-4771] Add `TRY_CAST` function (enabled in MSSQL library)
83f136167d is described below
commit 83f136167d3637319eb9b6fbbdca0eb9dd74cc85
Author: zoudan <zo...@bytedance.com>
AuthorDate: Thu Mar 30 10:53:43 2023 +0800
[CALCITE-4771] Add `TRY_CAST` function (enabled in MSSQL library)
Close apache/calcite#3136
---
core/src/main/codegen/templates/Parser.jj | 2 +
.../calcite/adapter/enumerable/RexImpTable.java | 2 +
.../apache/calcite/sql/fun/SqlCastFunction.java | 6 +-
.../calcite/sql/fun/SqlLibraryOperators.java | 7 +-
.../calcite/sql2rel/StandardConvertletTable.java | 1 +
.../apache/calcite/sql/test/SqlAdvisorTest.java | 1 +
site/_docs/reference.md | 2 +
.../calcite/sql/test/SqlOperatorFixture.java | 65 +++--
.../apache/calcite/test/SqlOperatorFixtures.java | 20 +-
.../org/apache/calcite/test/SqlOperatorTest.java | 270 +++++++++++----------
10 files changed, 206 insertions(+), 170 deletions(-)
diff --git a/core/src/main/codegen/templates/Parser.jj b/core/src/main/codegen/templates/Parser.jj
index fe6e354daa..33750d1f56 100644
--- a/core/src/main/codegen/templates/Parser.jj
+++ b/core/src/main/codegen/templates/Parser.jj
@@ -6004,6 +6004,7 @@ SqlNode BuiltinFunctionCall() :
(
( <CAST> { f = SqlStdOperatorTable.CAST; }
| <SAFE_CAST> { f = SqlLibraryOperators.SAFE_CAST; }
+ | <TRY_CAST> { f = SqlLibraryOperators.TRY_CAST; }
)
{ s = span(); }
<LPAREN> AddExpression(args, ExprContext.ACCEPT_SUB_QUERY)
@@ -8353,6 +8354,7 @@ SqlPostfixOperator PostfixRowOperator() :
| < TRIM_ARRAY: "TRIM_ARRAY" >
| < TRUE: "TRUE" >
| < TRUNCATE: "TRUNCATE" >
+| < TRY_CAST: "TRY_CAST" >
| < TUESDAY: "TUESDAY" >
| < TUMBLE: "TUMBLE" >
| < TYPE: "TYPE" >
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 c197f4c001..f3716f6bf4 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
@@ -188,6 +188,7 @@ import static org.apache.calcite.sql.fun.SqlLibraryOperators.TO_BASE64;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.TO_CHAR;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.TRANSLATE3;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.TRUNC;
+import static org.apache.calcite.sql.fun.SqlLibraryOperators.TRY_CAST;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.UNIX_DATE;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.UNIX_MICROS;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.UNIX_MILLIS;
@@ -670,6 +671,7 @@ public class RexImpTable {
map.put(COALESCE, new CoalesceImplementor());
map.put(CAST, new CastImplementor());
map.put(SAFE_CAST, new CastImplementor());
+ map.put(TRY_CAST, new CastImplementor());
map.put(REINTERPRET, new ReinterpretImplementor());
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 a9bfd8cff8..74b1fc0236 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
@@ -86,11 +86,11 @@ public class SqlCastFunction extends SqlFunction {
//~ Constructors -----------------------------------------------------------
public SqlCastFunction() {
- this(SqlKind.CAST);
+ this(SqlKind.CAST.toString(), SqlKind.CAST);
}
- public SqlCastFunction(SqlKind kind) {
- super(kind.toString(), kind, returnTypeInference(kind == SqlKind.SAFE_CAST),
+ public SqlCastFunction(String name, SqlKind kind) {
+ super(name, kind, returnTypeInference(kind == SqlKind.SAFE_CAST),
InferTypes.FIRST_KNOWN, null, SqlFunctionCategory.SYSTEM);
checkArgument(kind == SqlKind.CAST || kind == SqlKind.SAFE_CAST, kind);
}
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 acf17da9c6..c090845f58 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
@@ -1219,7 +1219,12 @@ public abstract class SqlLibraryOperators {
* error. */
@LibraryOperator(libraries = {BIG_QUERY})
public static final SqlFunction SAFE_CAST =
- new SqlCastFunction(SqlKind.SAFE_CAST);
+ new SqlCastFunction("SAFE_CAST", SqlKind.SAFE_CAST);
+
+ /** The "TRY_CAST(expr AS type)" function, equivalent to SAFE_CAST. */
+ @LibraryOperator(libraries = {MSSQL})
+ public static final SqlFunction TRY_CAST =
+ new SqlCastFunction("TRY_CAST", SqlKind.SAFE_CAST);
/** NULL-safe "<=>" equal operator used by MySQL, for example
* {@code 1<=>NULL}. */
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 e9b5e7d7b0..926427713d 100644
--- a/core/src/main/java/org/apache/calcite/sql2rel/StandardConvertletTable.java
+++ b/core/src/main/java/org/apache/calcite/sql2rel/StandardConvertletTable.java
@@ -136,6 +136,7 @@ public class StandardConvertletTable extends ReflectiveConvertletTable {
// Register convertlets for specific objects.
registerOp(SqlStdOperatorTable.CAST, this::convertCast);
registerOp(SqlLibraryOperators.SAFE_CAST, this::convertCast);
+ registerOp(SqlLibraryOperators.TRY_CAST, this::convertCast);
registerOp(SqlLibraryOperators.INFIX_CAST, this::convertCast);
registerOp(SqlStdOperatorTable.IS_DISTINCT_FROM,
(cx, call) -> convertIsDistinctFrom(cx, call, false));
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 2d62211df0..b0cafeb246 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
@@ -241,6 +241,7 @@ class SqlAdvisorTest extends SqlValidatorTestCase {
"KEYWORD(TRIM)",
"KEYWORD(TRUE)",
"KEYWORD(TRUNCATE)",
+ "KEYWORD(TRY_CAST)",
"KEYWORD(UNIQUE)",
"KEYWORD(UNKNOWN)",
"KEYWORD(UPPER)",
diff --git a/site/_docs/reference.md b/site/_docs/reference.md
index eb78d3a4fc..a2c837639e 100644
--- a/site/_docs/reference.md
+++ b/site/_docs/reference.md
@@ -1061,6 +1061,7 @@ TRIGGER_SCHEMA,
**TRIM_ARRAY**,
**TRUE**,
**TRUNCATE**,
+**TRY_CAST**,
**TUESDAY**,
TUMBLE,
TYPE,
@@ -2761,6 +2762,7 @@ BigQuery's type system uses confusingly different names for types and functions:
| o p | TO_TIMESTAMP(string, format) | Converts *string* to a timestamp using the format *format*
| b o p | TRANSLATE(expr, fromString, toString) | Returns *expr* with all occurrences of each character in *fromString* replaced by its corresponding character in *toString*. Characters in *expr* that are not in *fromString* are not replaced
| b | TRUNC(numeric1 [, numeric2 ]) | Truncates *numeric1* to optionally *numeric2* (if not specified 0) places right to the decimal point
+| q | TRY_CAST(value AS type) | Converts *value* to *type*, returning NULL if conversion fails
| b | UNIX_MICROS(timestamp) | Returns the number of microseconds since 1970-01-01 00:00:00
| b | UNIX_MILLIS(timestamp) | Returns the number of milliseconds since 1970-01-01 00:00:00
| b | UNIX_SECONDS(timestamp) | Returns the number of seconds since 1970-01-01 00:00:00
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 164593741e..a00a2d1f52 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
@@ -597,81 +597,96 @@ public interface SqlOperatorFixture extends AutoCloseable {
.with("fun", "oracle"));
}
+ /**
+ * Types for cast.
+ */
+ enum CastType {
+ CAST("cast"),
+ SAFE_CAST("safe_cast"),
+ TRY_CAST("try_cast");
+
+ CastType(String name) {
+ this.name = name;
+ }
+
+ final String name;
+ }
+
default String getCastString(
String value,
String targetType,
boolean errorLoc,
- boolean safe) {
+ CastType castType) {
if (errorLoc) {
value = "^" + value + "^";
}
- String function = safe ? "safe_cast" : "cast";
+ String function = castType.name;
return function + "(" + value + " as " + targetType + ")";
}
default void checkCastToApproxOkay(String value, String targetType,
- Object expected, boolean safe) {
- checkScalarApprox(getCastString(value, targetType, false, safe),
- getTargetType(targetType, safe), expected);
+ Object expected, CastType castType) {
+ checkScalarApprox(getCastString(value, targetType, false, castType),
+ getTargetType(targetType, castType), expected);
}
default void checkCastToStringOkay(String value, String targetType,
- String expected, boolean safe) {
- final String castString = getCastString(value, targetType, false, safe);
- checkString(castString, expected, getTargetType(targetType, safe));
+ String expected, CastType castType) {
+ final String castString = getCastString(value, targetType, false, castType);
+ checkString(castString, expected, getTargetType(targetType, castType));
}
default void checkCastToScalarOkay(String value, String targetType,
- String expected, boolean safe) {
- final String castString = getCastString(value, targetType, false, safe);
- checkScalarExact(castString, getTargetType(targetType, safe), expected);
+ String expected, CastType castType) {
+ final String castString = getCastString(value, targetType, false, castType);
+ checkScalarExact(castString, getTargetType(targetType, castType), expected);
}
- default String getTargetType(String targetType, boolean safe) {
- return safe ? targetType : targetType + NON_NULLABLE_SUFFIX;
+ default String getTargetType(String targetType, CastType castType) {
+ return castType == CastType.CAST ? targetType + NON_NULLABLE_SUFFIX : targetType;
}
default void checkCastToScalarOkay(String value, String targetType,
- boolean safe) {
- checkCastToScalarOkay(value, targetType, value, safe);
+ CastType castType) {
+ checkCastToScalarOkay(value, targetType, value, castType);
}
default void checkCastFails(String value, String targetType,
- String expectedError, boolean runtime, boolean safe) {
- final String castString = getCastString(value, targetType, !runtime, safe);
+ String expectedError, boolean runtime, CastType castType) {
+ final String castString = getCastString(value, targetType, !runtime, castType);
checkFails(castString, expectedError, runtime);
}
default void checkCastToString(String value, @Nullable String type,
- @Nullable String expected, boolean safe) {
+ @Nullable String expected, CastType castType) {
String spaces = " ";
if (expected == null) {
expected = value.trim();
}
int len = expected.length();
if (type != null) {
- value = getCastString(value, type, false, safe);
+ value = getCastString(value, type, false, castType);
}
// currently no exception thrown for truncation
if (Bug.DT239_FIXED) {
checkCastFails(value,
"VARCHAR(" + (len - 1) + ")", STRING_TRUNC_MESSAGE,
- true, safe);
+ true, castType);
}
- checkCastToStringOkay(value, "VARCHAR(" + len + ")", expected, safe);
- checkCastToStringOkay(value, "VARCHAR(" + (len + 5) + ")", expected, safe);
+ checkCastToStringOkay(value, "VARCHAR(" + len + ")", expected, castType);
+ checkCastToStringOkay(value, "VARCHAR(" + (len + 5) + ")", expected, castType);
// currently no exception thrown for truncation
if (Bug.DT239_FIXED) {
checkCastFails(value,
"CHAR(" + (len - 1) + ")", STRING_TRUNC_MESSAGE,
- true, safe);
+ true, castType);
}
- checkCastToStringOkay(value, "CHAR(" + len + ")", expected, safe);
+ checkCastToStringOkay(value, "CHAR(" + len + ")", expected, castType);
checkCastToStringOkay(value, "CHAR(" + (len + 5) + ")",
- expected + spaces, safe);
+ expected + spaces, castType);
}
}
diff --git a/testkit/src/main/java/org/apache/calcite/test/SqlOperatorFixtures.java b/testkit/src/main/java/org/apache/calcite/test/SqlOperatorFixtures.java
index 1d91c97759..d6affd622e 100644
--- a/testkit/src/main/java/org/apache/calcite/test/SqlOperatorFixtures.java
+++ b/testkit/src/main/java/org/apache/calcite/test/SqlOperatorFixtures.java
@@ -30,15 +30,15 @@ class SqlOperatorFixtures {
}
/** Returns a fixture that converts each CAST test into a test for
- * SAFE_CAST. */
- static SqlOperatorFixture safeCastWrapper(SqlOperatorFixture fixture) {
+ * SAFE_CAST or TRY_CAST. */
+ static SqlOperatorFixture safeCastWrapper(SqlOperatorFixture fixture, String functionName) {
return (SqlOperatorFixture) Proxy.newProxyInstance(
SqlOperatorTest.class.getClassLoader(),
new Class[]{SqlOperatorFixture.class},
- new SqlOperatorFixtureInvocationHandler(fixture));
+ new SqlOperatorFixtureInvocationHandler(fixture, functionName));
}
- /** A helper for {@link #safeCastWrapper(SqlOperatorFixture)} that provides
+ /** A helper for {@link #safeCastWrapper(SqlOperatorFixture, String)} that provides
* alternative implementations of methods in {@link SqlOperatorFixture}.
*
* <p>Must be public, so that its methods can be seen via reflection. */
@@ -49,9 +49,11 @@ class SqlOperatorFixtures {
static final Pattern NOT_NULL_PATTERN = Pattern.compile(" NOT NULL");
final SqlOperatorFixture f;
+ final String functionName;
- SqlOperatorFixtureInvocationHandler(SqlOperatorFixture f) {
+ SqlOperatorFixtureInvocationHandler(SqlOperatorFixture f, String functionName) {
this.f = f;
+ this.functionName = functionName;
}
@Override protected Object getTarget() {
@@ -59,7 +61,7 @@ class SqlOperatorFixtures {
}
String addSafe(String sql) {
- return CAST_PATTERN.matcher(sql).replaceAll("SAFE_CAST(");
+ return CAST_PATTERN.matcher(sql).replaceAll(functionName + "(");
}
String removeNotNull(String type) {
@@ -67,11 +69,11 @@ class SqlOperatorFixtures {
}
/** Proxy for
- * {@link SqlOperatorFixture#checkCastToString(String, String, String, boolean)}. */
+ * {@link SqlOperatorFixture#checkCastToString(String, String, String, SqlOperatorFixture.CastType)}. */
public void checkCastToString(String value, @Nullable String type,
- @Nullable String expected, boolean safe) {
+ @Nullable String expected, SqlOperatorFixture.CastType castType) {
f.checkCastToString(addSafe(value),
- type == null ? null : removeNotNull(type), expected, safe);
+ type == null ? null : removeNotNull(type), expected, castType);
}
/** Proxy for {@link SqlOperatorFixture#checkBoolean(String, Boolean)}. */
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 f0a61ab7e5..12ae35ee29 100644
--- a/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
+++ b/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
@@ -52,6 +52,7 @@ import org.apache.calcite.sql.parser.SqlParserPos;
import org.apache.calcite.sql.pretty.SqlPrettyWriter;
import org.apache.calcite.sql.test.AbstractSqlTester;
import org.apache.calcite.sql.test.SqlOperatorFixture;
+import org.apache.calcite.sql.test.SqlOperatorFixture.CastType;
import org.apache.calcite.sql.test.SqlOperatorFixture.VmName;
import org.apache.calcite.sql.test.SqlTestFactory;
import org.apache.calcite.sql.test.SqlTester;
@@ -431,48 +432,53 @@ public class SqlOperatorTest {
true);
}
- /** 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);
+ SqlOperatorFixture f2 =
+ SqlOperatorFixtures.safeCastWrapper(f.withLibrary(SqlLibrary.BIG_QUERY), "SAFE_CAST");
+ SqlOperatorFixture f3 =
+ SqlOperatorFixtures.safeCastWrapper(f.withLibrary(SqlLibrary.MSSQL), "TRY_CAST");
return Stream.of(
- () -> new Object[] {false, f},
- () -> new Object[] {true, f3});
+ () -> new Object[] {CastType.CAST, f},
+ () -> new Object[] {CastType.SAFE_CAST, f2},
+ () -> new Object[] {CastType.TRY_CAST, f3});
+ }
+
+ /** Tests that CAST, SAFE_CAST and TRY_CAST are basically equivalent but SAFE_CAST is
+ * only available in BigQuery library and TRY_CAST is only available in MSSQL library. */
+ @ParameterizedTest
+ @MethodSource("safeParameters")
+ void testCast(CastType castType, SqlOperatorFixture f) {
+ // SAFE_CAST is available in BigQuery library but not by default.
+ // TRY_CAST is available in MSSQL library but not by default.
+ final SqlOperatorFixture f0 = fixture();
+ if (castType != CastType.CAST) {
+ f0.checkFails("^" + castType.name() + "(12 + 3 as varchar(10))^",
+ "No match found for function signature " + castType.name().toUpperCase(Locale.ROOT)
+ + "\\(<NUMERIC>, <CHARACTER>\\)", false);
+ }
+
+ f.checkScalar(castType.name() + "(12 + 3 as varchar(10))", "15", "VARCHAR(10) NOT NULL");
}
@ParameterizedTest
@MethodSource("safeParameters")
- void testCastToString(boolean safe, SqlOperatorFixture f) {
+ void testCastToString(CastType castType, SqlOperatorFixture f) {
f.setFor(SqlStdOperatorTable.CAST, VmName.EXPAND);
f.checkCastToString("cast(cast('abc' as char(4)) as varchar(6))", null,
- "abc ", safe);
+ "abc ", castType);
// integer
- f.checkCastToString("123", "CHAR(3)", "123", safe);
+ f.checkCastToString("123", "CHAR(3)", "123", castType);
- f.checkCastToString("0", "CHAR", "0", safe);
- f.checkCastToString("-123", "CHAR(4)", "-123", safe);
+ f.checkCastToString("0", "CHAR", "0", castType);
+ f.checkCastToString("-123", "CHAR(4)", "-123", castType);
// decimal
- 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.checkCastToString("123.4", "CHAR(5)", "123.4", castType);
+ f.checkCastToString("-0.0", "CHAR(2)", ".0", castType);
+ f.checkCastToString("-123.4", "CHAR(6)", "-123.4", castType);
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");
@@ -484,33 +490,33 @@ public class SqlOperatorTest {
"-1.29", "VARCHAR(10) NOT NULL");
// approximate
- 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);
+ f.checkCastToString("1.23E45", "CHAR(7)", "1.23E45", castType);
+ f.checkCastToString("CAST(0 AS DOUBLE)", "CHAR(3)", "0E0", castType);
+ f.checkCastToString("-1.20e-07", "CHAR(7)", "-1.2E-7", castType);
+ f.checkCastToString("cast(0e0 as varchar(5))", "CHAR(3)", "0E0", castType);
if (TODO) {
f.checkCastToString("cast(-45e-2 as varchar(17))", "CHAR(7)",
- "-4.5E-1", safe);
+ "-4.5E-1", castType);
}
if (TODO) {
f.checkCastToString("cast(4683442.3432498375e0 as varchar(20))",
"CHAR(19)",
- "4.683442343249838E6", safe);
+ "4.683442343249838E6", castType);
}
if (TODO) {
- f.checkCastToString("cast(-0.1 as real)", "CHAR(5)", "-1E-1", safe);
+ f.checkCastToString("cast(-0.1 as real)", "CHAR(5)", "-1E-1", castType);
}
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", 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("'abc'", "CHAR(1)", "a", castType);
+ f.checkCastToString("'abc'", "CHAR(3)", "abc", castType);
+ f.checkCastToString("cast('abc' as varchar(6))", "CHAR(3)", "abc", castType);
+ f.checkCastToString("cast(' abc ' as varchar(10))", null, " abc ", castType);
f.checkCastToString("cast(cast('abc' as char(4)) as varchar(6))", null,
- "abc ", safe);
+ "abc ", castType);
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' ",
@@ -530,26 +536,26 @@ public class SqlOperatorTest {
"INTEGER NOT NULL");
// date & time
- 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("date '2008-01-01'", "CHAR(10)", "2008-01-01", castType);
+ f.checkCastToString("time '1:2:3'", "CHAR(8)", "01:02:03", castType);
f.checkCastToString("timestamp '2008-1-1 1:2:3'", "CHAR(19)",
- "2008-01-01 01:02:03", safe);
+ "2008-01-01 01:02:03", castType);
f.checkCastToString("timestamp '2008-1-1 1:2:3'", "VARCHAR(30)",
- "2008-01-01 01:02:03", safe);
+ "2008-01-01 01:02:03", castType);
- 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 '3-2' year to month", "CHAR(5)", "+3-02", castType);
+ f.checkCastToString("interval '32' month", "CHAR(3)", "+32", castType);
f.checkCastToString("interval '1 2:3:4' day to second", "CHAR(11)",
- "+1 02:03:04", safe);
+ "+1 02:03:04", castType);
f.checkCastToString("interval '1234.56' second(4,2)", "CHAR(8)",
- "+1234.56", safe);
- f.checkCastToString("interval '60' day", "CHAR(8)", "+60 ", safe);
+ "+1234.56", castType);
+ f.checkCastToString("interval '60' day", "CHAR(8)", "+60 ", castType);
// boolean
- 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.checkCastToString("True", "CHAR(4)", "TRUE", castType);
+ f.checkCastToString("True", "CHAR(6)", "TRUE ", castType);
+ f.checkCastToString("True", "VARCHAR(6)", "TRUE", castType);
+ f.checkCastToString("False", "CHAR(5)", "FALSE", castType);
f.checkString("cast(true as char(3))", "TRU", "CHAR(3) NOT NULL");
f.checkString("cast(false as char(4))", "FALS", "CHAR(4) NOT NULL");
@@ -559,7 +565,7 @@ public class SqlOperatorTest {
@ParameterizedTest
@MethodSource("safeParameters")
- void testCastExactNumericLimits(boolean safe, SqlOperatorFixture f) {
+ void testCastExactNumericLimits(CastType castType, SqlOperatorFixture f) {
f.setFor(SqlStdOperatorTable.CAST, VmName.EXPAND);
// Test casting for min,max, out of range for exact numeric types
@@ -576,74 +582,74 @@ public class SqlOperatorTest {
}
// Convert from literal to type
- f.checkCastToScalarOkay(numeric.maxNumericString, type, safe);
- f.checkCastToScalarOkay(numeric.minNumericString, type, safe);
+ f.checkCastToScalarOkay(numeric.maxNumericString, type, castType);
+ f.checkCastToScalarOkay(numeric.minNumericString, type, castType);
// Overflow test
if (numeric == Numeric.BIGINT) {
// Literal of range
f.checkCastFails(numeric.maxOverflowNumericString,
- type, LITERAL_OUT_OF_RANGE_MESSAGE, false, safe);
+ type, LITERAL_OUT_OF_RANGE_MESSAGE, false, castType);
f.checkCastFails(numeric.minOverflowNumericString,
- type, LITERAL_OUT_OF_RANGE_MESSAGE, false, safe);
+ type, LITERAL_OUT_OF_RANGE_MESSAGE, false, castType);
} else {
if (Bug.CALCITE_2539_FIXED) {
f.checkCastFails(numeric.maxOverflowNumericString,
- type, OUT_OF_RANGE_MESSAGE, true, safe);
+ type, OUT_OF_RANGE_MESSAGE, true, castType);
f.checkCastFails(numeric.minOverflowNumericString,
- type, OUT_OF_RANGE_MESSAGE, true, safe);
+ type, OUT_OF_RANGE_MESSAGE, true, castType);
}
}
// Convert from string to type
f.checkCastToScalarOkay("'" + numeric.maxNumericString + "'",
- type, numeric.maxNumericString, safe);
+ type, numeric.maxNumericString, castType);
f.checkCastToScalarOkay("'" + numeric.minNumericString + "'",
- type, numeric.minNumericString, safe);
+ type, numeric.minNumericString, castType);
if (Bug.CALCITE_2539_FIXED) {
f.checkCastFails("'" + numeric.maxOverflowNumericString + "'",
- type, OUT_OF_RANGE_MESSAGE, true, safe);
+ type, OUT_OF_RANGE_MESSAGE, true, castType);
f.checkCastFails("'" + numeric.minOverflowNumericString + "'",
- type, OUT_OF_RANGE_MESSAGE, true, safe);
+ type, OUT_OF_RANGE_MESSAGE, true, castType);
}
// Convert from type to string
- f.checkCastToString(numeric.maxNumericString, null, null, safe);
- f.checkCastToString(numeric.maxNumericString, type, null, safe);
+ f.checkCastToString(numeric.maxNumericString, null, null, castType);
+ f.checkCastToString(numeric.maxNumericString, type, null, castType);
- f.checkCastToString(numeric.minNumericString, null, null, safe);
- f.checkCastToString(numeric.minNumericString, type, null, safe);
+ f.checkCastToString(numeric.minNumericString, null, null, castType);
+ f.checkCastToString(numeric.minNumericString, type, null, castType);
if (Bug.CALCITE_2539_FIXED) {
f.checkCastFails("'notnumeric'", type, INVALID_CHAR_MESSAGE, true,
- safe);
+ castType);
}
});
}
@ParameterizedTest
@MethodSource("safeParameters")
- void testCastToExactNumeric(boolean safe, SqlOperatorFixture f) {
+ void testCastToExactNumeric(CastType castType, SqlOperatorFixture f) {
f.setFor(SqlStdOperatorTable.CAST, VmName.EXPAND);
- 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);
+ f.checkCastToScalarOkay("1", "BIGINT", castType);
+ f.checkCastToScalarOkay("1", "INTEGER", castType);
+ f.checkCastToScalarOkay("1", "SMALLINT", castType);
+ f.checkCastToScalarOkay("1", "TINYINT", castType);
+ f.checkCastToScalarOkay("1", "DECIMAL(4, 0)", castType);
+ f.checkCastToScalarOkay("-1", "BIGINT", castType);
+ f.checkCastToScalarOkay("-1", "INTEGER", castType);
+ f.checkCastToScalarOkay("-1", "SMALLINT", castType);
+ f.checkCastToScalarOkay("-1", "TINYINT", castType);
+ f.checkCastToScalarOkay("-1", "DECIMAL(4, 0)", castType);
+
+ f.checkCastToScalarOkay("1.234E3", "INTEGER", "1234", castType);
+ f.checkCastToScalarOkay("-9.99E2", "INTEGER", "-999", castType);
+ f.checkCastToScalarOkay("'1'", "INTEGER", "1", castType);
+ f.checkCastToScalarOkay("' 01 '", "INTEGER", "1", castType);
+ f.checkCastToScalarOkay("'-1'", "INTEGER", "-1", castType);
+ f.checkCastToScalarOkay("' -00 '", "INTEGER", "0", castType);
// string to integer
f.checkScalarExact("cast('6543' as integer)", 6543);
@@ -655,7 +661,7 @@ public class SqlOperatorTest {
@ParameterizedTest
@MethodSource("safeParameters")
- void testCastStringToDecimal(boolean safe, SqlOperatorFixture f) {
+ void testCastStringToDecimal(CastType castType, SqlOperatorFixture f) {
f.setFor(SqlStdOperatorTable.CAST, VmName.EXPAND);
if (!DECIMAL) {
return;
@@ -685,7 +691,7 @@ public class SqlOperatorTest {
@ParameterizedTest
@MethodSource("safeParameters")
- void testCastIntervalToNumeric(boolean safe, SqlOperatorFixture f) {
+ void testCastIntervalToNumeric(CastType castType, SqlOperatorFixture f) {
f.setFor(SqlStdOperatorTable.CAST, VmName.EXPAND);
// interval to decimal
@@ -793,7 +799,7 @@ public class SqlOperatorTest {
@ParameterizedTest
@MethodSource("safeParameters")
- void testCastToInterval(boolean safe, SqlOperatorFixture f) {
+ void testCastToInterval(CastType castType, SqlOperatorFixture f) {
f.setFor(SqlStdOperatorTable.CAST, VmName.EXPAND);
f.checkScalar(
"cast(5 as interval second)",
@@ -848,7 +854,7 @@ public class SqlOperatorTest {
@ParameterizedTest
@MethodSource("safeParameters")
- void testCastIntervalToInterval(boolean safe, SqlOperatorFixture f) {
+ void testCastIntervalToInterval(CastType castType, SqlOperatorFixture f) {
f.checkScalar("cast(interval '2 5' day to hour as interval hour to minute)",
"+53:00",
"INTERVAL HOUR TO MINUTE NOT NULL");
@@ -868,7 +874,7 @@ public class SqlOperatorTest {
@ParameterizedTest
@MethodSource("safeParameters")
- void testCastWithRoundingToScalar(boolean safe, SqlOperatorFixture f) {
+ void testCastWithRoundingToScalar(CastType castType, SqlOperatorFixture f) {
f.setFor(SqlStdOperatorTable.CAST, VmName.EXPAND);
f.checkFails("cast(1.25 as int)", "INTEGER", true);
@@ -909,7 +915,7 @@ public class SqlOperatorTest {
@ParameterizedTest
@MethodSource("safeParameters")
- void testCastDecimalToDoubleToInteger(boolean safe, SqlOperatorFixture f) {
+ void testCastDecimalToDoubleToInteger(CastType castType, SqlOperatorFixture f) {
f.setFor(SqlStdOperatorTable.CAST, VmName.EXPAND);
f.checkFails("cast( cast(1.25 as double) as integer)", OUT_OF_RANGE_MESSAGE, true);
@@ -925,7 +931,7 @@ public class SqlOperatorTest {
@ParameterizedTest
@MethodSource("safeParameters")
- void testCastApproxNumericLimits(boolean safe, SqlOperatorFixture f) {
+ void testCastApproxNumericLimits(CastType castType, SqlOperatorFixture f) {
f.setFor(SqlStdOperatorTable.CAST, VmName.EXPAND);
// Test casting for min, max, out of range for approx numeric types
@@ -954,43 +960,43 @@ public class SqlOperatorTest {
f.checkCastToApproxOkay(numeric.maxNumericString, type,
isFloat
? isWithin(numeric.maxNumericAsDouble(), 1E32)
- : isExactly(numeric.maxNumericAsDouble()), safe);
+ : isExactly(numeric.maxNumericAsDouble()), castType);
f.checkCastToApproxOkay(numeric.minNumericString, type,
- isExactly(numeric.minNumericString), safe);
+ isExactly(numeric.minNumericString), castType);
if (isFloat) {
f.checkCastFails(numeric.maxOverflowNumericString, type,
- OUT_OF_RANGE_MESSAGE, true, safe);
+ OUT_OF_RANGE_MESSAGE, true, castType);
} else {
// Double: Literal out of range
f.checkCastFails(numeric.maxOverflowNumericString, type,
- LITERAL_OUT_OF_RANGE_MESSAGE, false, safe);
+ LITERAL_OUT_OF_RANGE_MESSAGE, false, castType);
}
// Underflow: goes to 0
f.checkCastToApproxOkay(numeric.minOverflowNumericString, type,
- isExactly(0), safe);
+ isExactly(0), castType);
// Convert from string to type
f.checkCastToApproxOkay("'" + numeric.maxNumericString + "'", type,
isFloat
? isWithin(numeric.maxNumericAsDouble(), 1E32)
- : isExactly(numeric.maxNumericAsDouble()), safe);
+ : isExactly(numeric.maxNumericAsDouble()), castType);
f.checkCastToApproxOkay("'" + numeric.minNumericString + "'", type,
- isExactly(numeric.minNumericAsDouble()), safe);
+ isExactly(numeric.minNumericAsDouble()), castType);
f.checkCastFails("'" + numeric.maxOverflowNumericString + "'", type,
- OUT_OF_RANGE_MESSAGE, true, safe);
+ OUT_OF_RANGE_MESSAGE, true, castType);
// Underflow: goes to 0
f.checkCastToApproxOkay("'" + numeric.minOverflowNumericString + "'",
- type, isExactly(0), safe);
+ type, isExactly(0), castType);
// Convert from type to string
// Treated as DOUBLE
f.checkCastToString(numeric.maxNumericString, null,
- isFloat ? null : "1.79769313486231E308", safe);
+ isFloat ? null : "1.79769313486231E308", castType);
// TODO: The following tests are slightly different depending on
// whether the java or fennel calc are used.
@@ -998,45 +1004,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", safe);
+ isFloat ? "3.402824E38" : "1.797693134862316E308", castType);
f.checkCastToString(numeric.minNumericString, null,
// Treated as FLOAT or DOUBLE
- isFloat ? null : "4.940656458412465E-324", safe);
+ isFloat ? null : "4.940656458412465E-324", castType);
f.checkCastToString(numeric.minNumericString, type,
- isFloat ? "1.401299E-45" : "4.940656458412465E-324", safe);
+ isFloat ? "1.401299E-45" : "4.940656458412465E-324", castType);
} else if (false /* JavaCalc */) {
// Treated as FLOAT or DOUBLE
f.checkCastToString(numeric.maxNumericString, type,
// Treated as DOUBLE
- isFloat ? "3.402823E38" : "1.797693134862316E308", safe);
+ isFloat ? "3.402823E38" : "1.797693134862316E308", castType);
f.checkCastToString(numeric.minNumericString, null,
- isFloat ? null : null, safe); // Treated as FLOAT or DOUBLE
+ isFloat ? null : null, castType); // Treated as FLOAT or DOUBLE
f.checkCastToString(numeric.minNumericString, type,
- isFloat ? "1.401298E-45" : null, safe);
+ isFloat ? "1.401298E-45" : null, castType);
}
- f.checkCastFails("'notnumeric'", type, INVALID_CHAR_MESSAGE, true, safe);
+ f.checkCastFails("'notnumeric'", type, INVALID_CHAR_MESSAGE, true, castType);
});
}
@ParameterizedTest
@MethodSource("safeParameters")
- void testCastToApproxNumeric(boolean safe, SqlOperatorFixture f) {
+ void testCastToApproxNumeric(CastType castType, SqlOperatorFixture f) {
f.setFor(SqlStdOperatorTable.CAST, VmName.EXPAND);
- 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("1", "DOUBLE", isExactly(1), castType);
+ f.checkCastToApproxOkay("1.0", "DOUBLE", isExactly(1), castType);
+ f.checkCastToApproxOkay("-2.3", "FLOAT", isWithin(-2.3, 0.000001), castType);
+ f.checkCastToApproxOkay("'1'", "DOUBLE", isExactly(1), castType);
f.checkCastToApproxOkay("' -1e-37 '", "DOUBLE", isExactly("-1.0E-37"),
- safe);
- f.checkCastToApproxOkay("1e0", "DOUBLE", isExactly(1), safe);
- f.checkCastToApproxOkay("0e0", "REAL", isExactly(0), safe);
+ castType);
+ f.checkCastToApproxOkay("1e0", "DOUBLE", isExactly(1), castType);
+ f.checkCastToApproxOkay("0e0", "REAL", isExactly(0), castType);
}
@ParameterizedTest
@MethodSource("safeParameters")
- void testCastNull(boolean safe, SqlOperatorFixture f) {
+ void testCastNull(CastType castType, SqlOperatorFixture f) {
f.setFor(SqlStdOperatorTable.CAST, VmName.EXPAND);
// null
@@ -1054,8 +1060,8 @@ public class SqlOperatorTest {
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'
+ if (castType != CastType.CAST) {
+ // In the following, 'cast' becomes 'safe_cast' or 'try_cast'
f.checkNull("cast('a' as time)");
f.checkNull("cast('a' as int)");
f.checkNull("cast('2023-03-17a' as date)");
@@ -1071,7 +1077,7 @@ public class SqlOperatorTest {
* Handling errors during constant reduction</a>. */
@ParameterizedTest
@MethodSource("safeParameters")
- void testCastInvalid(boolean safe, SqlOperatorFixture f) {
+ void testCastInvalid(CastType castType, 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.
@@ -1091,7 +1097,7 @@ public class SqlOperatorTest {
/** Test cast for DATE, TIME, TIMESTAMP types. */
@ParameterizedTest
@MethodSource("safeParameters")
- void testCastDateTime(boolean safe, SqlOperatorFixture f) {
+ void testCastDateTime(CastType castType, SqlOperatorFixture f) {
f.setFor(SqlStdOperatorTable.CAST, VmName.EXPAND);
f.checkScalar("cast(TIMESTAMP '1945-02-24 12:42:25.34' as TIMESTAMP)",
@@ -1120,9 +1126,9 @@ public class SqlOperatorTest {
"12:42:25", "TIME(0) NOT NULL");
// time <-> string
- f.checkCastToString("TIME '12:42:25'", null, "12:42:25", safe);
+ f.checkCastToString("TIME '12:42:25'", null, "12:42:25", castType);
if (TODO) {
- f.checkCastToString("TIME '12:42:25.34'", null, "12:42:25.34", safe);
+ f.checkCastToString("TIME '12:42:25.34'", null, "12:42:25.34", castType);
}
// Generate the current date as a string, e.g. "2007-04-18". The value
@@ -1157,7 +1163,7 @@ public class SqlOperatorTest {
@ParameterizedTest
@MethodSource("safeParameters")
- void testCastStringToDateTime(boolean safe, SqlOperatorFixture f) {
+ void testCastStringToDateTime(CastType castType, 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)",
@@ -1183,12 +1189,12 @@ public class SqlOperatorTest {
// timestamp <-> string
f.checkCastToString("TIMESTAMP '1945-02-24 12:42:25'", null,
- "1945-02-24 12:42:25", safe);
+ "1945-02-24 12:42:25", castType);
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", safe);
+ null, "1945-02-24 12:42:25.34", castType);
}
f.checkScalar("cast('1945-02-24 12:42:25' as TIMESTAMP)",
@@ -1219,8 +1225,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", safe);
- f.checkCastToString("DATE '1945-2-24'", null, "1945-02-24", safe);
+ f.checkCastToString("DATE '1945-02-24'", null, "1945-02-24", castType);
+ f.checkCastToString("DATE '1945-2-24'", null, "1945-02-24", castType);
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");
@@ -1327,7 +1333,7 @@ public class SqlOperatorTest {
@ParameterizedTest
@MethodSource("safeParameters")
- void testCastToBoolean(boolean safe, SqlOperatorFixture f) {
+ void testCastToBoolean(CastType castType, SqlOperatorFixture f) {
f.setFor(SqlStdOperatorTable.CAST, VmName.EXPAND);
// string to boolean
@@ -10127,7 +10133,7 @@ public class SqlOperatorTest {
@ParameterizedTest
@MethodSource("safeParameters")
- void testCastTruncates(boolean safe, SqlOperatorFixture f) {
+ void testCastTruncates(CastType castType, 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",