Skip to content

Commit 83f1361

Browse files
zoudanlibenchao
authored andcommitted
[CALCITE-4771] Add TRY_CAST function (enabled in MSSQL library)
Close #3136
1 parent 96da529 commit 83f1361

File tree

10 files changed

+206
-170
lines changed

10 files changed

+206
-170
lines changed

core/src/main/codegen/templates/Parser.jj

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -6004,6 +6004,7 @@ SqlNode BuiltinFunctionCall() :
60046004
(
60056005
( <CAST> { f = SqlStdOperatorTable.CAST; }
60066006
| <SAFE_CAST> { f = SqlLibraryOperators.SAFE_CAST; }
6007+
| <TRY_CAST> { f = SqlLibraryOperators.TRY_CAST; }
60076008
)
60086009
{ s = span(); }
60096010
<LPAREN> AddExpression(args, ExprContext.ACCEPT_SUB_QUERY)
@@ -8353,6 +8354,7 @@ SqlPostfixOperator PostfixRowOperator() :
83538354
| < TRIM_ARRAY: "TRIM_ARRAY" >
83548355
| < TRUE: "TRUE" >
83558356
| < TRUNCATE: "TRUNCATE" >
8357+
| < TRY_CAST: "TRY_CAST" >
83568358
| < TUESDAY: "TUESDAY" >
83578359
| < TUMBLE: "TUMBLE" >
83588360
| < TYPE: "TYPE" >

core/src/main/java/org/apache/calcite/adapter/enumerable/RexImpTable.java

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -188,6 +188,7 @@
188188
import static org.apache.calcite.sql.fun.SqlLibraryOperators.TO_CHAR;
189189
import static org.apache.calcite.sql.fun.SqlLibraryOperators.TRANSLATE3;
190190
import static org.apache.calcite.sql.fun.SqlLibraryOperators.TRUNC;
191+
import static org.apache.calcite.sql.fun.SqlLibraryOperators.TRY_CAST;
191192
import static org.apache.calcite.sql.fun.SqlLibraryOperators.UNIX_DATE;
192193
import static org.apache.calcite.sql.fun.SqlLibraryOperators.UNIX_MICROS;
193194
import static org.apache.calcite.sql.fun.SqlLibraryOperators.UNIX_MILLIS;
@@ -670,6 +671,7 @@ Builder populate2() {
670671
map.put(COALESCE, new CoalesceImplementor());
671672
map.put(CAST, new CastImplementor());
672673
map.put(SAFE_CAST, new CastImplementor());
674+
map.put(TRY_CAST, new CastImplementor());
673675

674676
map.put(REINTERPRET, new ReinterpretImplementor());
675677

core/src/main/java/org/apache/calcite/sql/fun/SqlCastFunction.java

Lines changed: 3 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -86,11 +86,11 @@ public class SqlCastFunction extends SqlFunction {
8686
//~ Constructors -----------------------------------------------------------
8787

8888
public SqlCastFunction() {
89-
this(SqlKind.CAST);
89+
this(SqlKind.CAST.toString(), SqlKind.CAST);
9090
}
9191

92-
public SqlCastFunction(SqlKind kind) {
93-
super(kind.toString(), kind, returnTypeInference(kind == SqlKind.SAFE_CAST),
92+
public SqlCastFunction(String name, SqlKind kind) {
93+
super(name, kind, returnTypeInference(kind == SqlKind.SAFE_CAST),
9494
InferTypes.FIRST_KNOWN, null, SqlFunctionCategory.SYSTEM);
9595
checkArgument(kind == SqlKind.CAST || kind == SqlKind.SAFE_CAST, kind);
9696
}

core/src/main/java/org/apache/calcite/sql/fun/SqlLibraryOperators.java

Lines changed: 6 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1219,7 +1219,12 @@ static RelDataType deriveTypeSplit(SqlOperatorBinding operatorBinding,
12191219
* error. */
12201220
@LibraryOperator(libraries = {BIG_QUERY})
12211221
public static final SqlFunction SAFE_CAST =
1222-
new SqlCastFunction(SqlKind.SAFE_CAST);
1222+
new SqlCastFunction("SAFE_CAST", SqlKind.SAFE_CAST);
1223+
1224+
/** The "TRY_CAST(expr AS type)" function, equivalent to SAFE_CAST. */
1225+
@LibraryOperator(libraries = {MSSQL})
1226+
public static final SqlFunction TRY_CAST =
1227+
new SqlCastFunction("TRY_CAST", SqlKind.SAFE_CAST);
12231228

12241229
/** NULL-safe "&lt;=&gt;" equal operator used by MySQL, for example
12251230
* {@code 1<=>NULL}. */

core/src/main/java/org/apache/calcite/sql2rel/StandardConvertletTable.java

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -136,6 +136,7 @@ private StandardConvertletTable() {
136136
// Register convertlets for specific objects.
137137
registerOp(SqlStdOperatorTable.CAST, this::convertCast);
138138
registerOp(SqlLibraryOperators.SAFE_CAST, this::convertCast);
139+
registerOp(SqlLibraryOperators.TRY_CAST, this::convertCast);
139140
registerOp(SqlLibraryOperators.INFIX_CAST, this::convertCast);
140141
registerOp(SqlStdOperatorTable.IS_DISTINCT_FROM,
141142
(cx, call) -> convertIsDistinctFrom(cx, call, false));

core/src/test/java/org/apache/calcite/sql/test/SqlAdvisorTest.java

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -241,6 +241,7 @@ class SqlAdvisorTest extends SqlValidatorTestCase {
241241
"KEYWORD(TRIM)",
242242
"KEYWORD(TRUE)",
243243
"KEYWORD(TRUNCATE)",
244+
"KEYWORD(TRY_CAST)",
244245
"KEYWORD(UNIQUE)",
245246
"KEYWORD(UNKNOWN)",
246247
"KEYWORD(UPPER)",

site/_docs/reference.md

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1061,6 +1061,7 @@ TRIGGER_SCHEMA,
10611061
**TRIM_ARRAY**,
10621062
**TRUE**,
10631063
**TRUNCATE**,
1064+
**TRY_CAST**,
10641065
**TUESDAY**,
10651066
TUMBLE,
10661067
TYPE,
@@ -2761,6 +2762,7 @@ BigQuery's type system uses confusingly different names for types and functions:
27612762
| o p | TO_TIMESTAMP(string, format) | Converts *string* to a timestamp using the format *format*
27622763
| 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
27632764
| b | TRUNC(numeric1 [, numeric2 ]) | Truncates *numeric1* to optionally *numeric2* (if not specified 0) places right to the decimal point
2765+
| q | TRY_CAST(value AS type) | Converts *value* to *type*, returning NULL if conversion fails
27642766
| b | UNIX_MICROS(timestamp) | Returns the number of microseconds since 1970-01-01 00:00:00
27652767
| b | UNIX_MILLIS(timestamp) | Returns the number of milliseconds since 1970-01-01 00:00:00
27662768
| b | UNIX_SECONDS(timestamp) | Returns the number of seconds since 1970-01-01 00:00:00

testkit/src/main/java/org/apache/calcite/sql/test/SqlOperatorFixture.java

Lines changed: 40 additions & 25 deletions
Original file line numberDiff line numberDiff line change
@@ -597,81 +597,96 @@ default SqlOperatorFixture forOracle(SqlConformance conformance) {
597597
.with("fun", "oracle"));
598598
}
599599

600+
/**
601+
* Types for cast.
602+
*/
603+
enum CastType {
604+
CAST("cast"),
605+
SAFE_CAST("safe_cast"),
606+
TRY_CAST("try_cast");
607+
608+
CastType(String name) {
609+
this.name = name;
610+
}
611+
612+
final String name;
613+
}
614+
600615
default String getCastString(
601616
String value,
602617
String targetType,
603618
boolean errorLoc,
604-
boolean safe) {
619+
CastType castType) {
605620
if (errorLoc) {
606621
value = "^" + value + "^";
607622
}
608-
String function = safe ? "safe_cast" : "cast";
623+
String function = castType.name;
609624
return function + "(" + value + " as " + targetType + ")";
610625
}
611626

612627
default void checkCastToApproxOkay(String value, String targetType,
613-
Object expected, boolean safe) {
614-
checkScalarApprox(getCastString(value, targetType, false, safe),
615-
getTargetType(targetType, safe), expected);
628+
Object expected, CastType castType) {
629+
checkScalarApprox(getCastString(value, targetType, false, castType),
630+
getTargetType(targetType, castType), expected);
616631
}
617632

618633
default void checkCastToStringOkay(String value, String targetType,
619-
String expected, boolean safe) {
620-
final String castString = getCastString(value, targetType, false, safe);
621-
checkString(castString, expected, getTargetType(targetType, safe));
634+
String expected, CastType castType) {
635+
final String castString = getCastString(value, targetType, false, castType);
636+
checkString(castString, expected, getTargetType(targetType, castType));
622637
}
623638

624639
default void checkCastToScalarOkay(String value, String targetType,
625-
String expected, boolean safe) {
626-
final String castString = getCastString(value, targetType, false, safe);
627-
checkScalarExact(castString, getTargetType(targetType, safe), expected);
640+
String expected, CastType castType) {
641+
final String castString = getCastString(value, targetType, false, castType);
642+
checkScalarExact(castString, getTargetType(targetType, castType), expected);
628643
}
629644

630-
default String getTargetType(String targetType, boolean safe) {
631-
return safe ? targetType : targetType + NON_NULLABLE_SUFFIX;
645+
default String getTargetType(String targetType, CastType castType) {
646+
return castType == CastType.CAST ? targetType + NON_NULLABLE_SUFFIX : targetType;
632647
}
633648

634649
default void checkCastToScalarOkay(String value, String targetType,
635-
boolean safe) {
636-
checkCastToScalarOkay(value, targetType, value, safe);
650+
CastType castType) {
651+
checkCastToScalarOkay(value, targetType, value, castType);
637652
}
638653

639654
default void checkCastFails(String value, String targetType,
640-
String expectedError, boolean runtime, boolean safe) {
641-
final String castString = getCastString(value, targetType, !runtime, safe);
655+
String expectedError, boolean runtime, CastType castType) {
656+
final String castString = getCastString(value, targetType, !runtime, castType);
642657
checkFails(castString, expectedError, runtime);
643658
}
644659

645660
default void checkCastToString(String value, @Nullable String type,
646-
@Nullable String expected, boolean safe) {
661+
@Nullable String expected, CastType castType) {
647662
String spaces = " ";
648663
if (expected == null) {
649664
expected = value.trim();
650665
}
651666
int len = expected.length();
652667
if (type != null) {
653-
value = getCastString(value, type, false, safe);
668+
value = getCastString(value, type, false, castType);
654669
}
655670

656671
// currently no exception thrown for truncation
657672
if (Bug.DT239_FIXED) {
658673
checkCastFails(value,
659674
"VARCHAR(" + (len - 1) + ")", STRING_TRUNC_MESSAGE,
660-
true, safe);
675+
true, castType);
661676
}
662677

663-
checkCastToStringOkay(value, "VARCHAR(" + len + ")", expected, safe);
664-
checkCastToStringOkay(value, "VARCHAR(" + (len + 5) + ")", expected, safe);
678+
checkCastToStringOkay(value, "VARCHAR(" + len + ")", expected, castType);
679+
checkCastToStringOkay(value, "VARCHAR(" + (len + 5) + ")", expected, castType);
665680

666681
// currently no exception thrown for truncation
667682
if (Bug.DT239_FIXED) {
668683
checkCastFails(value,
669684
"CHAR(" + (len - 1) + ")", STRING_TRUNC_MESSAGE,
670-
true, safe);
685+
true, castType);
671686
}
672687

673-
checkCastToStringOkay(value, "CHAR(" + len + ")", expected, safe);
688+
checkCastToStringOkay(value, "CHAR(" + len + ")", expected, castType);
674689
checkCastToStringOkay(value, "CHAR(" + (len + 5) + ")",
675-
expected + spaces, safe);
690+
expected + spaces, castType);
676691
}
677692
}

testkit/src/main/java/org/apache/calcite/test/SqlOperatorFixtures.java

Lines changed: 11 additions & 9 deletions
Original file line numberDiff line numberDiff line change
@@ -30,15 +30,15 @@ private SqlOperatorFixtures() {
3030
}
3131

3232
/** Returns a fixture that converts each CAST test into a test for
33-
* SAFE_CAST. */
34-
static SqlOperatorFixture safeCastWrapper(SqlOperatorFixture fixture) {
33+
* SAFE_CAST or TRY_CAST. */
34+
static SqlOperatorFixture safeCastWrapper(SqlOperatorFixture fixture, String functionName) {
3535
return (SqlOperatorFixture) Proxy.newProxyInstance(
3636
SqlOperatorTest.class.getClassLoader(),
3737
new Class[]{SqlOperatorFixture.class},
38-
new SqlOperatorFixtureInvocationHandler(fixture));
38+
new SqlOperatorFixtureInvocationHandler(fixture, functionName));
3939
}
4040

41-
/** A helper for {@link #safeCastWrapper(SqlOperatorFixture)} that provides
41+
/** A helper for {@link #safeCastWrapper(SqlOperatorFixture, String)} that provides
4242
* alternative implementations of methods in {@link SqlOperatorFixture}.
4343
*
4444
* <p>Must be public, so that its methods can be seen via reflection. */
@@ -49,29 +49,31 @@ public static class SqlOperatorFixtureInvocationHandler
4949
static final Pattern NOT_NULL_PATTERN = Pattern.compile(" NOT NULL");
5050

5151
final SqlOperatorFixture f;
52+
final String functionName;
5253

53-
SqlOperatorFixtureInvocationHandler(SqlOperatorFixture f) {
54+
SqlOperatorFixtureInvocationHandler(SqlOperatorFixture f, String functionName) {
5455
this.f = f;
56+
this.functionName = functionName;
5557
}
5658

5759
@Override protected Object getTarget() {
5860
return f;
5961
}
6062

6163
String addSafe(String sql) {
62-
return CAST_PATTERN.matcher(sql).replaceAll("SAFE_CAST(");
64+
return CAST_PATTERN.matcher(sql).replaceAll(functionName + "(");
6365
}
6466

6567
String removeNotNull(String type) {
6668
return NOT_NULL_PATTERN.matcher(type).replaceAll("");
6769
}
6870

6971
/** Proxy for
70-
* {@link SqlOperatorFixture#checkCastToString(String, String, String, boolean)}. */
72+
* {@link SqlOperatorFixture#checkCastToString(String, String, String, SqlOperatorFixture.CastType)}. */
7173
public void checkCastToString(String value, @Nullable String type,
72-
@Nullable String expected, boolean safe) {
74+
@Nullable String expected, SqlOperatorFixture.CastType castType) {
7375
f.checkCastToString(addSafe(value),
74-
type == null ? null : removeNotNull(type), expected, safe);
76+
type == null ? null : removeNotNull(type), expected, castType);
7577
}
7678

7779
/** Proxy for {@link SqlOperatorFixture#checkBoolean(String, Boolean)}. */

0 commit comments

Comments
 (0)