This is an automated email from the ASF dual-hosted git repository.
danny0405 pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/calcite.git
The following commit(s) were added to refs/heads/master by this push:
new d3c7183 [CALCITE-3331] Support implicit type cast for operators that
use single operand family checker
d3c7183 is described below
commit d3c718328d4c83fb24007c8349b31b420dddd187
Author: yuzhao.cyz <[email protected]>
AuthorDate: Wed Sep 11 13:23:41 2019 +0800
[CALCITE-3331] Support implicit type cast for operators that use single
operand family checker
* Add doc to SqlSingleOperandTypeChecker#checkSingleOperandType to note
that we should not support implicit type coercion for it's
implementation;
* Tweak the operands checking logic for SqlJsonRemoveFunction,
SqlRegexpReplaceFunction and SqlSubstringFunction to support implicit
type coercion, also add the test cases in SqlValidatorTest;
* Some cosmetic comments fix.
---
.../calcite/sql/fun/SqlDatePartFunction.java | 10 ++-
.../org/apache/calcite/sql/fun/SqlDotOperator.java | 2 +
.../calcite/sql/fun/SqlJsonRemoveFunction.java | 9 ++-
.../calcite/sql/fun/SqlRegexpReplaceFunction.java | 35 ++++++-----
.../calcite/sql/fun/SqlSubstringFunction.java | 73 +++++++---------------
.../sql/type/SqlSingleOperandTypeChecker.java | 7 +++
.../sql/validate/implicit/TypeCoercion.java | 29 +++++----
.../sql/validate/implicit/TypeCoercionImpl.java | 16 ++---
.../sql/validate/implicit/package-info.java | 22 +++----
.../org/apache/calcite/test/SqlValidatorTest.java | 36 ++++++++++-
10 files changed, 130 insertions(+), 109 deletions(-)
diff --git
a/core/src/main/java/org/apache/calcite/sql/fun/SqlDatePartFunction.java
b/core/src/main/java/org/apache/calcite/sql/fun/SqlDatePartFunction.java
index e6c5bde..f7e39f8 100644
--- a/core/src/main/java/org/apache/calcite/sql/fun/SqlDatePartFunction.java
+++ b/core/src/main/java/org/apache/calcite/sql/fun/SqlDatePartFunction.java
@@ -73,8 +73,14 @@ public class SqlDatePartFunction extends SqlFunction {
public boolean checkOperandTypes(SqlCallBinding callBinding,
boolean throwOnFailure) {
- return OperandTypes.DATETIME.checkSingleOperandType(callBinding,
- callBinding.operand(0), 0, throwOnFailure);
+ // Use #checkOperandTypes instead of #checkSingleOperandType to enable
implicit
+ // type coercion. REVIEW Danny 2019-09-10, because we declare that the
operand
+ // type family is DATETIME, that means it allows arguments of type DATE,
TIME
+ // or TIMESTAMP, so actually we can not figure out which type we want
precisely.
+ // For example, the YEAR(date) function, it actually allows a
DATE/TIMESTAMP operand,
+ // but we declare the required operand type family to be DATETIME.
+ // We just need some refactoring for the SqlDatePartFunction.
+ return OperandTypes.DATETIME.checkOperandTypes(callBinding,
throwOnFailure);
}
}
diff --git a/core/src/main/java/org/apache/calcite/sql/fun/SqlDotOperator.java
b/core/src/main/java/org/apache/calcite/sql/fun/SqlDotOperator.java
index 3739a40..b145a76 100644
--- a/core/src/main/java/org/apache/calcite/sql/fun/SqlDotOperator.java
+++ b/core/src/main/java/org/apache/calcite/sql/fun/SqlDotOperator.java
@@ -143,6 +143,8 @@ public class SqlDotOperator extends SqlSpecialOperator {
}
final RelDataType operandType = callBinding.getOperandType(0);
final SqlSingleOperandTypeChecker checker = getChecker(operandType);
+ // Actually operand0 always comes from parsing the SqlIdentifier, so there
+ // is no need to make implicit type coercion.
return checker.checkSingleOperandType(callBinding, right, 0,
throwOnFailure);
}
diff --git
a/core/src/main/java/org/apache/calcite/sql/fun/SqlJsonRemoveFunction.java
b/core/src/main/java/org/apache/calcite/sql/fun/SqlJsonRemoveFunction.java
index 3e7cb85..b9fac78 100644
--- a/core/src/main/java/org/apache/calcite/sql/fun/SqlJsonRemoveFunction.java
+++ b/core/src/main/java/org/apache/calcite/sql/fun/SqlJsonRemoveFunction.java
@@ -55,13 +55,12 @@ public class SqlJsonRemoveFunction extends SqlFunction {
callBinding, callBinding.operand(0), 0, throwOnFailure)) {
return false;
}
+ final SqlTypeFamily[] families = new SqlTypeFamily[operandCount];
+ families[0] = SqlTypeFamily.ANY;
for (int i = 1; i < operandCount; i++) {
- if (!OperandTypes.CHARACTER.checkSingleOperandType(
- callBinding, callBinding.operand(i), 0, throwOnFailure)) {
- return false;
- }
+ families[i] = SqlTypeFamily.CHARACTER;
}
- return true;
+ return OperandTypes.family(families).checkOperandTypes(callBinding,
throwOnFailure);
}
@Override public String getAllowedSignatures(String opNameToUse) {
diff --git
a/core/src/main/java/org/apache/calcite/sql/fun/SqlRegexpReplaceFunction.java
b/core/src/main/java/org/apache/calcite/sql/fun/SqlRegexpReplaceFunction.java
index 61a1e67..f444285 100644
---
a/core/src/main/java/org/apache/calcite/sql/fun/SqlRegexpReplaceFunction.java
+++
b/core/src/main/java/org/apache/calcite/sql/fun/SqlRegexpReplaceFunction.java
@@ -24,9 +24,13 @@ import org.apache.calcite.sql.SqlOperandCountRange;
import org.apache.calcite.sql.type.OperandTypes;
import org.apache.calcite.sql.type.ReturnTypes;
import org.apache.calcite.sql.type.SqlOperandCountRanges;
+import org.apache.calcite.sql.type.SqlTypeFamily;
import org.apache.calcite.sql.type.SqlTypeName;
import org.apache.calcite.sql.type.SqlTypeTransforms;
+import java.util.ArrayList;
+import java.util.List;
+
/**
* The REGEXP_REPLACE(source_string, pattern, replacement [, pos, occurrence,
match_type])
* searches for a regular expression pattern and replaces every occurrence of
the pattern
@@ -50,27 +54,28 @@ public class SqlRegexpReplaceFunction extends SqlFunction {
@Override public boolean checkOperandTypes(SqlCallBinding callBinding,
boolean throwOnFailure) {
final int operandCount = callBinding.getOperandCount();
- for (int i = 0; i < 3; i++) {
- if (!OperandTypes.STRING.checkSingleOperandType(
- callBinding, callBinding.operand(i), 0, throwOnFailure)) {
- return false;
- }
+ assert operandCount >= 3;
+ if (operandCount == 3) {
+ return OperandTypes.STRING_STRING_STRING
+ .checkOperandTypes(callBinding, throwOnFailure);
}
+ final List<SqlTypeFamily> families = new ArrayList<>();
+ families.add(SqlTypeFamily.STRING);
+ families.add(SqlTypeFamily.STRING);
+ families.add(SqlTypeFamily.STRING);
for (int i = 3; i < operandCount; i++) {
- if (i == 3 && !OperandTypes.INTEGER.checkSingleOperandType(
- callBinding, callBinding.operand(i), 0, throwOnFailure)) {
- return false;
+ if (i == 3) {
+ families.add(SqlTypeFamily.INTEGER);
}
- if (i == 4 && !OperandTypes.INTEGER.checkSingleOperandType(
- callBinding, callBinding.operand(i), 0, throwOnFailure)) {
- return false;
+ if (i == 4) {
+ families.add(SqlTypeFamily.INTEGER);
}
- if (i == 5 && !OperandTypes.STRING.checkSingleOperandType(
- callBinding, callBinding.operand(i), 0, throwOnFailure)) {
- return false;
+ if (i == 5) {
+ families.add(SqlTypeFamily.STRING);
}
}
- return true;
+ return OperandTypes.family(families.toArray(new SqlTypeFamily[0]))
+ .checkOperandTypes(callBinding, throwOnFailure);
}
}
diff --git
a/core/src/main/java/org/apache/calcite/sql/fun/SqlSubstringFunction.java
b/core/src/main/java/org/apache/calcite/sql/fun/SqlSubstringFunction.java
index 631e778..93d211b 100644
--- a/core/src/main/java/org/apache/calcite/sql/fun/SqlSubstringFunction.java
+++ b/core/src/main/java/org/apache/calcite/sql/fun/SqlSubstringFunction.java
@@ -28,14 +28,15 @@ import org.apache.calcite.sql.SqlOperandCountRange;
import org.apache.calcite.sql.SqlOperatorBinding;
import org.apache.calcite.sql.SqlUtil;
import org.apache.calcite.sql.SqlWriter;
+import org.apache.calcite.sql.type.FamilyOperandTypeChecker;
import org.apache.calcite.sql.type.OperandTypes;
import org.apache.calcite.sql.type.ReturnTypes;
import org.apache.calcite.sql.type.SqlOperandCountRanges;
+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.SqlMonotonicity;
import org.apache.calcite.sql.validate.SqlValidator;
-import org.apache.calcite.sql.validate.SqlValidatorScope;
import com.google.common.collect.ImmutableList;
@@ -95,68 +96,36 @@ public class SqlSubstringFunction extends SqlFunction {
public boolean checkOperandTypes(
SqlCallBinding callBinding,
boolean throwOnFailure) {
- SqlValidator validator = callBinding.getValidator();
- SqlValidatorScope scope = callBinding.getScope();
-
- final List<SqlNode> operands = callBinding.operands();
+ List<SqlNode> operands = callBinding.operands();
int n = operands.size();
assert (3 == n) || (2 == n);
- if (!OperandTypes.STRING.checkSingleOperandType(
- callBinding,
- operands.get(0),
- 0,
- throwOnFailure)) {
- return false;
- }
if (2 == n) {
- if (!OperandTypes.NUMERIC.checkSingleOperandType(
- callBinding,
- operands.get(1),
- 0,
- throwOnFailure)) {
+ return OperandTypes.family(SqlTypeFamily.STRING, SqlTypeFamily.NUMERIC)
+ .checkOperandTypes(callBinding, throwOnFailure);
+ } else {
+ final FamilyOperandTypeChecker checker1 =
OperandTypes.STRING_STRING_STRING;
+ final FamilyOperandTypeChecker checker2 = OperandTypes.family(
+ SqlTypeFamily.STRING,
+ SqlTypeFamily.NUMERIC,
+ SqlTypeFamily.NUMERIC);
+ // Put the STRING_NUMERIC_NUMERIC checker first because almost every
other type
+ // can be coerced to STRING.
+ if (!OperandTypes.or(checker2, checker1)
+ .checkOperandTypes(callBinding, throwOnFailure)) {
return false;
}
- } else {
- RelDataType t1 = validator.deriveType(scope, operands.get(1));
- RelDataType t2 = validator.deriveType(scope, operands.get(2));
-
+ // Reset the operands because they may be coerced during
+ // implicit type coercion.
+ operands = callBinding.getCall().getOperandList();
+ final SqlValidator validator = callBinding.getValidator();
+ final RelDataType t1 = validator.deriveType(callBinding.getScope(),
operands.get(1));
+ final RelDataType t2 = validator.deriveType(callBinding.getScope(),
operands.get(2));
if (SqlTypeUtil.inCharFamily(t1)) {
- if (!OperandTypes.STRING.checkSingleOperandType(
- callBinding,
- operands.get(1),
- 0,
- throwOnFailure)) {
- return false;
- }
- if (!OperandTypes.STRING.checkSingleOperandType(
- callBinding,
- operands.get(2),
- 0,
- throwOnFailure)) {
- return false;
- }
-
if (!SqlTypeUtil.isCharTypeComparable(callBinding, operands,
throwOnFailure)) {
return false;
}
- } else {
- if (!OperandTypes.NUMERIC.checkSingleOperandType(
- callBinding,
- operands.get(1),
- 0,
- throwOnFailure)) {
- return false;
- }
- if (!OperandTypes.NUMERIC.checkSingleOperandType(
- callBinding,
- operands.get(2),
- 0,
- throwOnFailure)) {
- return false;
- }
}
-
if (!SqlTypeUtil.inSameFamily(t1, t2)) {
if (throwOnFailure) {
throw callBinding.newValidationSignatureError();
diff --git
a/core/src/main/java/org/apache/calcite/sql/type/SqlSingleOperandTypeChecker.java
b/core/src/main/java/org/apache/calcite/sql/type/SqlSingleOperandTypeChecker.java
index 5b194d0..2371b58 100644
---
a/core/src/main/java/org/apache/calcite/sql/type/SqlSingleOperandTypeChecker.java
+++
b/core/src/main/java/org/apache/calcite/sql/type/SqlSingleOperandTypeChecker.java
@@ -46,6 +46,13 @@ public interface SqlSingleOperandTypeChecker extends
SqlOperandTypeChecker {
* <code>iFormalOperand</code> would be zero, even though the position of Z
* within call C is two.
*
+ * <p>Caution that we could not(shouldn't) implement implicit type coercion
for this checker,
+ * implicit type coercion has side effect(modify the AST), if this single
operand checker is
+ * subsumed in a composite rule(OR or AND), we can not make any side effect
if we
+ * can not make sure that all the single operands type check are passed(with
type coercion).
+ * But there is an exception: only if the call has just one operand, for
this case,
+ * use {@link SqlOperandTypeChecker#checkOperandTypes} instead.
+ *
* @param callBinding description of the call being checked; this is only
* provided for context when throwing an exception; the
* implementation should <em>NOT</em> examine the
diff --git
a/core/src/main/java/org/apache/calcite/sql/validate/implicit/TypeCoercion.java
b/core/src/main/java/org/apache/calcite/sql/validate/implicit/TypeCoercion.java
index 9cb773a..7ba068b 100644
---
a/core/src/main/java/org/apache/calcite/sql/validate/implicit/TypeCoercion.java
+++
b/core/src/main/java/org/apache/calcite/sql/validate/implicit/TypeCoercion.java
@@ -27,7 +27,7 @@ import org.apache.calcite.sql.validate.SqlValidatorScope;
import java.util.List;
/**
- * Default Strategies to coerce differing types that participate in
+ * Default strategies to coerce differing types that participate in
* operations into compatible ones.
*
* <p>Notes about type widening / tightest common types: Broadly, there are
two cases that need
@@ -65,20 +65,20 @@ public interface TypeCoercion {
/**
* Similar to {@link #getWiderTypeForTwo}, but can handle
* sequence types. {@link #getWiderTypeForTwo} doesn't satisfy the
associative law,
- * i.e. (a op b) op c may not equal to a op (b op c). This is only a problem
for StringType or
- * nested StringType in collection type like Array. Excluding these types,
+ * i.e. (a op b) op c may not equal to a op (b op c). This is only a problem
for STRING or
+ * nested STRING in collection type like ARRAY. Excluding these types,
* {@link #getWiderTypeForTwo} satisfies the associative law. For instance,
* (DATE, INTEGER, VARCHAR) should have VARCHAR as the wider common type.
*/
RelDataType getWiderTypeFor(List<RelDataType> typeList, boolean
stringPromotion);
/**
- * Finds a wider type when one or both types are decimal type.
+ * Finds a wider type when one or both types are DECIMAL type.
*
* <p>If the wider decimal type's precision/scale exceeds system limitation,
* this rule will truncate the decimal type to the max precision/scale.
- * For decimal and fractional types, returns a decimal type
- * which has the higher precision of the two.
+ * For DECIMAL and fractional types, returns DECIMAL type
+ * that has the higher precision of the two.
*
* <p>The default implementation depends on the max precision/scale of the
type system,
* you can override it based on the specific system requirement in
@@ -87,8 +87,8 @@ public interface TypeCoercion {
RelDataType getWiderTypeForDecimal(RelDataType type1, RelDataType type2);
/**
- * Determines common type for a comparison operator whose operands are String
- * type and the other (non String) type.
+ * Determines common type for a comparison operator whose operands are STRING
+ * type and the other (non STRING) type.
*/
RelDataType commonTypeForBinaryComparison(RelDataType type1, RelDataType
type2);
@@ -100,7 +100,7 @@ public interface TypeCoercion {
* @param query SqlNode which have children nodes as columns
* @param columnIndex target column index
* @param targetType target type to cast to
- * @return true if we add any cast in successfully.
+ * @return true if we add any cast in successfully
*/
boolean rowTypeCoercion(
SqlValidatorScope scope,
@@ -131,17 +131,16 @@ public interface TypeCoercion {
* {@link SqlTypeFamily} defined in the checkers, e.g. the
* {@link org.apache.calcite.sql.type.FamilyOperandTypeChecker}.
*
- * <p>Caution that we do not cast from numeric if desired type family is also
+ * <p>Caution that we do not cast from NUMERIC if desired type family is also
* {@link SqlTypeFamily#NUMERIC}.
*
* <p>If the {@link org.apache.calcite.sql.type.FamilyOperandTypeChecker}s
are
* subsumed in a
* {@link org.apache.calcite.sql.type.CompositeOperandTypeChecker}, check
them
- * based on their combination order. i.e. If we allows a (numeric, numeric)
OR
- * (string, numeric) family but with arguments (op1, op2) of types
- * (varchar(20), boolean), try to coerce op1 to numeric and op2 to numeric if
- * the type coercion rules allow it, or else try to coerce op2 to numeric and
- * keep op1 the type as it is.
+ * based on their combination order. i.e. If we allow a NUMERIC_NUMERIC OR
+ * STRING_NUMERIC family combination and are with arguments (op1:
VARCHAR(20), op2: BOOLEAN),
+ * try to coerce both op1 and op2 to NUMERIC if the type coercion rules
allow it,
+ * or else try to coerce op2 to NUMERIC and keep op1 the type as it is.
*
* <p>This is also very interrelated to the composition predicate for the
* checkers: if the predicate is AND, we would fail fast if the first family
diff --git
a/core/src/main/java/org/apache/calcite/sql/validate/implicit/TypeCoercionImpl.java
b/core/src/main/java/org/apache/calcite/sql/validate/implicit/TypeCoercionImpl.java
index cfc8cca..a66dd47 100644
---
a/core/src/main/java/org/apache/calcite/sql/validate/implicit/TypeCoercionImpl.java
+++
b/core/src/main/java/org/apache/calcite/sql/validate/implicit/TypeCoercionImpl.java
@@ -58,14 +58,14 @@ public class TypeCoercionImpl extends AbstractTypeCoercion {
* <pre>
*
* type1, type2 type3 select a, b, c from t1
- * | | |
- * type4 type5 type6 union
- * | | |
+ * \ \ \
+ * type4 type5 type6 UNION
+ * / / /
* type7 type8 type9 select d, e, f from t2
* </pre>
* For struct type (type1, type2, type3) union type (type4, type5, type6),
* infer the first result column type type7 as the wider type of type1 and
type4,
- * the second column type as the wider type of type2 and type5 and so forth.
+ * the second column type as the wider type of type2 and type5 and so on.
*
* @param scope validator scope
* @param query query node to update the field type for
@@ -111,7 +111,7 @@ public class TypeCoercionImpl extends AbstractTypeCoercion {
}
/**
- * Coerce operands in binary arithmetic expressions to Numeric types.
+ * Coerce operands in binary arithmetic expressions to NUMERIC types.
*
* <p>Rules:</p>
* <ul>
@@ -192,7 +192,7 @@ public class TypeCoercionImpl extends AbstractTypeCoercion {
}
/**
- * For numeric and string operands, cast string to data type of the other
operand.
+ * For NUMERIC and STRING operands, cast STRING to data type of the other
operand.
**/
protected boolean binaryArithmeticWithStrings(
SqlCallBinding binding,
@@ -224,7 +224,7 @@ public class TypeCoercionImpl extends AbstractTypeCoercion {
}
/**
- * Datetime and string equality: cast string type to datetime type,
SqlToRelConverter already
+ * Datetime and STRING equality: cast STRING type to datetime type,
SqlToRelConverter already
* make the conversion but we still keep this interface overridable
* so user can have their custom implementation.
*/
@@ -346,7 +346,7 @@ public class TypeCoercionImpl extends AbstractTypeCoercion {
/**
* STRATEGIES
*
- * <p>with/Without sub-query:
+ * <p>With(Without) sub-query:
*
* <ul>
*
diff --git
a/core/src/main/java/org/apache/calcite/sql/validate/implicit/package-info.java
b/core/src/main/java/org/apache/calcite/sql/validate/implicit/package-info.java
index 0552035..4fb46fe 100644
---
a/core/src/main/java/org/apache/calcite/sql/validate/implicit/package-info.java
+++
b/core/src/main/java/org/apache/calcite/sql/validate/implicit/package-info.java
@@ -16,7 +16,7 @@
*/
/**
- * Sql implicit type cast.
+ * <h1>SQL Implicit Type Cast</h1>
* <h2>Work Flow</h2>
* This package contains rules for implicit type coercion, it works during the
process of sql
* validation. The transformation entrance are all kinds of checkers. i.e.
@@ -47,30 +47,30 @@
* the exception as is before.</li>
* </ul>
*
- * <p> For some cases, although the validation passes, we still need the type
coercion, e.g. for
+ * <p>For some cases, although the validation passes, we still need the type
coercion, e.g. for
* expression 1 > '1', Calcite will just return false without type
coercion, we do type coercion
* eagerly here: the result expression would be transformed to "1 >
cast('1' as int)" and
* the result would be true.
*
- * <h2>Conversion Expressions</h2>
+ * <h2>Conversion SQL Contexts</h2>
* The supported conversion contexts are:
* <a
href="https://docs.google.com/document/d/1g2RUnLXyp_LjUlO-wbblKuP5hqEu3a_2Mt2k4dh6RwU/edit?usp=sharing";>Conversion
Expressions</a>
- * <p>Strategies for finding common type:</p>
+ * <p>Strategies for Finding Common Type:</p>
* <ul>
* <li>If the operator has expected data types, just take them as the
desired one. i.e. the UDF.
* </li>
* <li>If there is no expected data type but data type families are
registered, try to coerce
- * operand to the family's default data type, i.e. the String family will
have a VARCHAR type.
+ * operand to the family's default data type, i.e. the STRING family will
have a VARCHAR type.
* </li>
* <li>If neither expected data type nor families are specified, try to find
the tightest common
- * type of the node types, i.e. int and double will return double, the
numeric precision does not
- * lose for this case.</li>
- * <li>If no tightest common type found, try to find a wider type, i.e.
string and int
- * will return int, we allow some precision loss when widening decimal to
fractional,
- * or promote to string type.</li>
+ * type of the node types, i.e. INTEGER and DOUBLE will return DOUBLE, the
numeric precision
+ * does not lose for this case.</li>
+ * <li>If no tightest common type is found, try to find a wider type, i.e.
STRING and INT
+ * will return int, we allow some precision loss when widening DECIMAL to
fractional,
+ * or promote to STRING.</li>
* </ul>
*
- * <h2>Types Conversion Matrix</h2>
+ * <h2>Type Conversion Matrix</h2>
* See <a
href="https://docs.google.com/spreadsheets/d/1GhleX5h5W8-kJKh7NMJ4vtoE78pwfaZRJl88ULX_MgU/edit?usp=sharing";>CalciteImplicitCasts</a>
*/
@PackageMarker
diff --git a/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
b/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
index 43621a7..cd31eaa 100644
--- a/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
+++ b/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
@@ -921,11 +921,18 @@ public class SqlValidatorTest extends
SqlValidatorTestCase {
checkCharset(
"substring(_UTF16'10' FROM 1 FOR 2)",
Charset.forName("UTF-16LE"));
+ checkExp("substring('a', 1)");
+ checkExp("substring('a', 1, 3)");
+ // Implicit type coercion.
+ checkExpType("substring(12345, '1')", "VARCHAR NOT NULL");
+ checkExpType("substring('a', '1')", "VARCHAR(1) NOT NULL");
+ checkExpType("substring('a', 1, '3')", "VARCHAR(1) NOT NULL");
}
@Test public void testSubstringFails() {
checkWholeExpFails("substring('a' from 1 for 'b')",
- "(?s).*Cannot apply 'SUBSTRING' to arguments of type.*");
+ "(?s).*Cannot apply 'SUBSTRING' to arguments of type.*", false);
+ checkExpType("substring('a' from 1 for 'b')", "VARCHAR(1) NOT NULL");
checkWholeExpFails("substring(_UTF16'10' FROM '0' FOR '\\')",
"(?s).* not comparable to each other.*");
checkWholeExpFails("substring('10' FROM _UTF16'0' FOR '\\')",
@@ -11452,6 +11459,8 @@ public class SqlValidatorTest extends
SqlValidatorTestCase {
@Test public void testJsonRemove() {
checkExp("json_remove('{\"foo\":\"bar\"}', '$')");
checkExpType("json_remove('{\"foo\":\"bar\"}', '$')", "VARCHAR(2000)");
+ checkExpType("json_remove('{\"foo\":\"bar\"}', 1, '2', 3)",
"VARCHAR(2000)");
+ checkExpType("json_remove('{\"foo\":\"bar\"}', 1, 2, 3)", "VARCHAR(2000)");
checkFails("select ^json_remove('{\"foo\":\"bar\"}')^",
"(?s).*Invalid number of arguments.*");
}
@@ -11479,6 +11488,31 @@ public class SqlValidatorTest extends
SqlValidatorTestCase {
checkExpFails("^100 is json value^", "(?s).*Cannot apply.*", false);
}
+ @Test public void testRegexpReplace() {
+ tester = tester.withOperatorTable(
+ SqlLibraryOperatorTableFactory.INSTANCE
+ .getOperatorTable(SqlLibrary.STANDARD, SqlLibrary.ORACLE));
+ checkExpType("REGEXP_REPLACE('a b c', 'a', 'X')", "VARCHAR NOT NULL");
+ checkExpType("REGEXP_REPLACE('abc def ghi', '[a-z]+', 'X', 2)",
+ "VARCHAR NOT NULL");
+ checkExpType("REGEXP_REPLACE('abc def ghi', '[a-z]+', 'X', 1, 3)",
+ "VARCHAR NOT NULL");
+ checkExpType("REGEXP_REPLACE('abc def GHI', '[a-z]+', 'X', 1, 3, 'c')",
+ "VARCHAR NOT NULL");
+ // Implicit type coercion.
+ checkExpType("REGEXP_REPLACE(null, '(-)', '###')", "VARCHAR");
+ checkExpType("REGEXP_REPLACE('100-200', null, '###')", "VARCHAR");
+ checkExpType("REGEXP_REPLACE('100-200', '(-)', null)", "VARCHAR");
+ checkExpType("REGEXP_REPLACE('abc def ghi', '[a-z]+', 'X', '2')",
+ "VARCHAR NOT NULL");
+ checkExpType("REGEXP_REPLACE('abc def ghi', '[a-z]+', 'X', '1', '3')",
+ "VARCHAR NOT NULL");
+ // The last argument to REGEXP_REPLACE should be specific character, but
with
+ // implicit type coercion, the validation still passes.
+ checkExpType("REGEXP_REPLACE('abc def ghi', '[a-z]+', 'X', '1', '3', '1')",
+ "VARCHAR NOT NULL");
+ }
+
@Test public void testValidatorReportsOriginalQueryUsingReader()
throws Exception {
final String sql = "select a from b";
