You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@calcite.apache.org by da...@apache.org on 2020/04/18 04:17:02 UTC
[calcite] branch master updated: [CALCITE-3934] Allow type-coercion
in CONCAT operator
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 238cf03 [CALCITE-3934] Allow type-coercion in CONCAT operator
238cf03 is described below
commit 238cf037ebcd0e0e6f121f2304fa46a61c90632b
Author: yuzhao.cyz <yu...@gmail.com>
AuthorDate: Fri Apr 17 14:47:00 2020 +0800
[CALCITE-3934] Allow type-coercion in CONCAT operator
Such as CONCAT(123, 'abc', DATE '2020-04-17') yields '123abc2020-04-17'.
This change actually enables implicit type coercion for
all the operators with repeat type families.
---
.../sql/type/CompositeOperandTypeChecker.java | 48 ++++++++++++++--------
.../validate/implicit/AbstractTypeCoercion.java | 32 +++++++--------
.../sql/validate/implicit/TypeCoercionImpl.java | 43 ++++++++++---------
.../org/apache/calcite/test/SqlValidatorTest.java | 21 ++++++++--
4 files changed, 84 insertions(+), 60 deletions(-)
diff --git a/core/src/main/java/org/apache/calcite/sql/type/CompositeOperandTypeChecker.java b/core/src/main/java/org/apache/calcite/sql/type/CompositeOperandTypeChecker.java
index 4bfad35..71bd867 100644
--- a/core/src/main/java/org/apache/calcite/sql/type/CompositeOperandTypeChecker.java
+++ b/core/src/main/java/org/apache/calcite/sql/type/CompositeOperandTypeChecker.java
@@ -28,6 +28,7 @@ import com.google.common.collect.ImmutableList;
import java.util.AbstractList;
import java.util.ArrayList;
+import java.util.Collections;
import java.util.List;
import java.util.Objects;
import java.util.stream.Collectors;
@@ -274,6 +275,9 @@ public class CompositeOperandTypeChecker implements SqlOperandTypeChecker {
callBinding.getCall().operand(operand),
0,
false)) {
+ if (callBinding.isTypeCoercionEnabled()) {
+ return coerceOperands(callBinding, true);
+ }
return false;
}
}
@@ -293,23 +297,7 @@ public class CompositeOperandTypeChecker implements SqlOperandTypeChecker {
0,
false)) {
if (callBinding.isTypeCoercionEnabled()) {
- // Try type coercion for the call,
- // collect SqlTypeFamily and data type of all the operands.
- final List<SqlTypeFamily> families = allowedRules.stream()
- .filter(r -> r instanceof ImplicitCastOperandTypeChecker)
- .map(r -> ((ImplicitCastOperandTypeChecker) r).getOperandSqlTypeFamily(0))
- .collect(Collectors.toList());
- if (families.size() < allowedRules.size()) {
- // Not all the checkers are ImplicitCastOperandTypeChecker, returns early.
- return false;
- }
- final List<RelDataType> operandTypes = new ArrayList<>();
- for (int i = 0; i < callBinding.getOperandCount(); i++) {
- operandTypes.add(callBinding.getOperandType(i));
- }
- TypeCoercion typeCoercion = callBinding.getValidator().getTypeCoercion();
- return typeCoercion.builtinFunctionCoercion(callBinding,
- operandTypes, families);
+ return coerceOperands(callBinding, false);
}
return false;
}
@@ -347,6 +335,32 @@ public class CompositeOperandTypeChecker implements SqlOperandTypeChecker {
}
}
+ /** Tries to coerce the operands based on the defined type families. */
+ private boolean coerceOperands(SqlCallBinding callBinding, boolean repeat) {
+ // Type coercion for the call,
+ // collect SqlTypeFamily and data type of all the operands.
+ List<SqlTypeFamily> families = allowedRules.stream()
+ .filter(r -> r instanceof ImplicitCastOperandTypeChecker)
+ // All the rules are SqlSingleOperandTypeChecker.
+ .map(r -> ((ImplicitCastOperandTypeChecker) r).getOperandSqlTypeFamily(0))
+ .collect(Collectors.toList());
+ if (families.size() < allowedRules.size()) {
+ // Not all the checkers are ImplicitCastOperandTypeChecker, returns early.
+ return false;
+ }
+ if (repeat) {
+ assert families.size() == 1;
+ families = Collections.nCopies(callBinding.getOperandCount(), families.get(0));
+ }
+ final List<RelDataType> operandTypes = new ArrayList<>();
+ for (int i = 0; i < callBinding.getOperandCount(); i++) {
+ operandTypes.add(callBinding.getOperandType(i));
+ }
+ TypeCoercion typeCoercion = callBinding.getValidator().getTypeCoercion();
+ return typeCoercion.builtinFunctionCoercion(callBinding,
+ operandTypes, families);
+ }
+
private boolean checkWithoutTypeCoercion(SqlCallBinding callBinding) {
if (!callBinding.isTypeCoercionEnabled()) {
return false;
diff --git a/core/src/main/java/org/apache/calcite/sql/validate/implicit/AbstractTypeCoercion.java b/core/src/main/java/org/apache/calcite/sql/validate/implicit/AbstractTypeCoercion.java
index da132b9..c4e6015 100644
--- a/core/src/main/java/org/apache/calcite/sql/validate/implicit/AbstractTypeCoercion.java
+++ b/core/src/main/java/org/apache/calcite/sql/validate/implicit/AbstractTypeCoercion.java
@@ -116,7 +116,6 @@ public abstract class AbstractTypeCoercion implements TypeCoercion {
* @param scope Validator scope
* @param call the call
* @param commonType common type to coerce to
- * @return true if any operand is coerced
*/
protected boolean coerceOperandsType(
SqlValidatorScope scope,
@@ -132,12 +131,10 @@ public abstract class AbstractTypeCoercion implements TypeCoercion {
/**
* Cast column at index {@code index} to target type.
*
- * @param scope validator scope for the node list
- * @param nodeList column node list
- * @param index index of column
- * @param targetType target type to cast to
- *
- * @return true if type coercion actually happens.
+ * @param scope Validator scope for the node list
+ * @param nodeList Column node list
+ * @param index Index of column
+ * @param targetType Target type to cast to
*/
protected boolean coerceColumnType(
SqlValidatorScope scope,
@@ -276,7 +273,7 @@ public abstract class AbstractTypeCoercion implements TypeCoercion {
* before cast operation, see {@link #coerceColumnType}, {@link #coerceOperandType}.
*
* <p>Ignore constant reduction which should happen in RexSimplify.
- * */
+ */
private SqlNode castTo(SqlNode node, RelDataType type) {
return SqlStdOperatorTable.CAST.createCall(SqlParserPos.ZERO, node,
SqlTypeUtil.convertTypeToSpec(type).withNullable(type.isNullable()));
@@ -298,10 +295,10 @@ public abstract class AbstractTypeCoercion implements TypeCoercion {
* Update inferred row type for a query, i.e. SqlCall that returns struct type
* or SqlSelect.
*
- * @param scope validator scope
- * @param query node to inferred type
- * @param columnIndex column index to update
- * @param desiredType desired column type
+ * @param scope Validator scope
+ * @param query Node to inferred type
+ * @param columnIndex Column index to update
+ * @param desiredType Desired column type
*/
protected void updateInferredColumnType(
SqlValidatorScope scope,
@@ -327,7 +324,7 @@ public abstract class AbstractTypeCoercion implements TypeCoercion {
* Case1: type widening with no precision loss.
* Find the tightest common type of two types that might be used in binary expression.
*
- * @return tightest common type i.e. INTEGER + DECIMAL(10, 2) will return DECIMAL(10, 2)
+ * @return tightest common type, i.e. INTEGER + DECIMAL(10, 2) returns DECIMAL(10, 2)
*/
public RelDataType getTightestCommonType(RelDataType type1, RelDataType type2) {
if (type1 == null || type2 == null) {
@@ -606,9 +603,8 @@ public abstract class AbstractTypeCoercion implements TypeCoercion {
* We will check the type one by one, that means the 1th type and 1th family,
* 2th type and 2th family, and the like.
*
- * @param types data type need to check
- * @param families desired type families list
- * @return true if we can do type coercion
+ * @param types Data type need to check
+ * @param families Desired type families list
*/
boolean canImplicitTypeCast(List<RelDataType> types, List<SqlTypeFamily> families) {
boolean needed = false;
@@ -634,8 +630,8 @@ public abstract class AbstractTypeCoercion implements TypeCoercion {
* See <a href="https://docs.google.com/spreadsheets/d/1GhleX5h5W8-kJKh7NMJ4vtoE78pwfaZRJl88ULX_MgU/edit?usp=sharing">CalciteImplicitCasts</a>
* for the details.
*
- * @param in inferred operand type
- * @param expected expected {@link SqlTypeFamily} of registered SqlFunction
+ * @param in Inferred operand type
+ * @param expected Expected {@link SqlTypeFamily} of registered SqlFunction
* @return common type of implicit cast, null if we do not find any
*/
public RelDataType implicitCast(RelDataType in, SqlTypeFamily expected) {
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 dc52dee..1eb91fb 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
@@ -56,7 +56,7 @@ public class TypeCoercionImpl extends AbstractTypeCoercion {
}
/**
- * Widen a SqlNode's field type to target type,
+ * Widen a SqlNode's field type to common type,
* mainly used for set operations like UNION, INTERSECT and EXCEPT.
*
* <p>Rules:
@@ -72,11 +72,10 @@ public class TypeCoercionImpl extends AbstractTypeCoercion {
* 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 on.
*
- * @param scope validator scope
- * @param query query node to update the field type for
- * @param columnIndex target column index
- * @param targetType target type to cast to
- * @return true if any type coercion actually happens
+ * @param scope Validator scope
+ * @param query Query node to update the field type for
+ * @param columnIndex Target column index
+ * @param targetType Target type to cast to
*/
public boolean rowTypeCoercion(
SqlValidatorScope scope,
@@ -123,7 +122,7 @@ public class TypeCoercionImpl extends AbstractTypeCoercion {
}
/**
- * Coerce operands in binary arithmetic expressions to NUMERIC types.
+ * Coerces operands in binary arithmetic expressions to NUMERIC types.
*
* <p>For binary arithmetic operators like [+, -, *, /, %]:
* If the operand is VARCHAR,
@@ -132,7 +131,7 @@ public class TypeCoercionImpl extends AbstractTypeCoercion {
* coerce the STRING operand to max precision/scale DECIMAL.
*/
public boolean binaryArithmeticCoercion(SqlCallBinding binding) {
- // Assume that the operator has NUMERIC family operand type checker.
+ // Assume the operator has NUMERIC family operand type checker.
SqlOperator operator = binding.getOperator();
SqlKind kind = operator.getKind();
boolean coerced = false;
@@ -161,14 +160,15 @@ public class TypeCoercionImpl extends AbstractTypeCoercion {
SqlCallBinding binding,
RelDataType left,
RelDataType right) {
- // PostgreSQL and SQL-SERVER would cast the CHARACTER type operand to type
- // of another numeric operand, i.e. for '9' / 2, '9' would be casted to INTEGER,
- // while for '9' / 3.3, '9' would be casted to DOUBLE.
- // It does not allow two CHARACTER operands for binary arithmetic operators.
+ // For expression "NUMERIC <OP> CHARACTER",
+ // PostgreSQL and MS-SQL coerce the CHARACTER operand to NUMERIC,
+ // i.e. for '9':VARCHAR(1) / 2: INT, '9' would be coerced to INTEGER,
+ // while for '9':VARCHAR(1) / 3.3: DOUBLE, '9' would be coerced to DOUBLE.
+ // They do not allow both CHARACTER operands for binary arithmetic operators.
// MySQL and Oracle would coerce all the string operands to DOUBLE.
- // Keep sync with PostgreSQL and SQL-SERVER because their behaviors are more in
+ // Keep sync with PostgreSQL and MS-SQL because their behaviors are more in
// line with the SQL standard.
if (SqlTypeUtil.isString(left) && SqlTypeUtil.isNumeric(right)) {
// If the numeric operand is DECIMAL type, coerce the STRING operand to
@@ -187,7 +187,7 @@ public class TypeCoercionImpl extends AbstractTypeCoercion {
}
/**
- * Coerce operands in binary comparison expressions.
+ * Coerces operands in binary comparison expressions.
*
* <p>Rules:</p>
* <ul>
@@ -211,11 +211,12 @@ public class TypeCoercionImpl extends AbstractTypeCoercion {
if (operandCnt == 2) {
final RelDataType type1 = binding.getOperandType(0);
final RelDataType type2 = binding.getOperandType(1);
- // EQUALS(=) NOT_EQUALS(<>) operator
+ // EQUALS(=) NOT_EQUALS(<>)
if (kind.belongsTo(SqlKind.BINARY_EQUALITY)) {
// STRING and datetime
- // BOOLEAN and NUMERIC | BOOLEAN and literal
coerced = dateTimeStringEquality(binding, type1, type2) || coerced;
+ // BOOLEAN and NUMERIC
+ // BOOLEAN and literal
coerced = booleanEquality(binding, type1, type2) || coerced;
}
// Binary comparision operator like: = > >= < <=
@@ -287,7 +288,7 @@ public class TypeCoercionImpl extends AbstractTypeCoercion {
/**
* Datetime and STRING equality: cast STRING type to datetime type, SqlToRelConverter already
- * make the conversion but we still keep this interface overridable
+ * makes the conversion but we still keep this interface overridable
* so user can have their custom implementation.
*/
protected boolean dateTimeStringEquality(
@@ -310,7 +311,7 @@ public class TypeCoercionImpl extends AbstractTypeCoercion {
}
/**
- * Cast "BOOLEAN = NUMERIC" to "NUMERIC = NUMERIC". Expressions like 1=`expr` and
+ * Casts "BOOLEAN = NUMERIC" to "NUMERIC = NUMERIC". Expressions like 1=`expr` and
* 0=`expr` can be simplified to `expr` and `not expr`, but this better happens
* in {@link org.apache.calcite.rex.RexSimplify}.
*
@@ -369,9 +370,9 @@ public class TypeCoercionImpl extends AbstractTypeCoercion {
}
/**
- * Case when and COALESCE type coercion, collect all the branches types including then
+ * CASE and COALESCE type coercion, collect all the branches types including then
* operands and else operands to find a common type, then cast the operands to the common type
- * if it is needed.
+ * when needed.
*/
public boolean caseWhenCoercion(SqlCallBinding callBinding) {
// For sql statement like:
@@ -614,8 +615,6 @@ public class TypeCoercionImpl extends AbstractTypeCoercion {
* @param query Query
* @param columnIndex Source column index to coerce type
* @param targetType Target type
- *
- * @return True if any type coercion happens
*/
private boolean coerceSourceRowType(
SqlValidatorScope sourceScope,
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 28b37d2..7cadfdc 100644
--- a/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
+++ b/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
@@ -707,15 +707,30 @@ class SqlValidatorTest extends SqlValidatorTestCase {
.columnType("VARCHAR(10) NOT NULL");
s.expr("concat('aabbcc', CAST(NULL AS VARCHAR(20)), '+-')")
.columnType("VARCHAR(28)");
- s.expr("concat('aabbcc', 2)").withWhole(true)
+ s.expr("concat('aabbcc', 2)")
+ .withWhole(true)
+ .withTypeCoercion(false)
.fails("(?s)Cannot apply 'CONCAT' to arguments of type "
+ "'CONCAT\\(<CHAR\\(6\\)>, <INTEGER>\\)'\\. .*");
- s.expr("concat('abc', 'ab', 123)").withWhole(true)
+ s.expr("concat('aabbcc', 2)").ok();
+ s.expr("concat('abc', 'ab', 123)")
+ .withWhole(true)
+ .withTypeCoercion(false)
.fails("(?s)Cannot apply 'CONCAT' to arguments of type "
+ "'CONCAT\\(<CHAR\\(3\\)>, <CHAR\\(2\\)>, <INTEGER>\\)'\\. .*");
- s.expr("concat(true, false)").withWhole(true)
+ s.expr("concat('abc', 'ab', 123)").ok();
+ s.expr("concat(true, false)")
+ .withWhole(true)
+ .withTypeCoercion(false)
.fails("(?s)Cannot apply 'CONCAT' to arguments of type "
+ "'CONCAT\\(<BOOLEAN>, <BOOLEAN>\\)'\\. .*");
+ s.expr("concat(true, false)").ok();
+ s.expr("concat(DATE '2020-04-17', TIMESTAMP '2020-04-17 14:17:51')")
+ .withWhole(true)
+ .withTypeCoercion(false)
+ .fails("(?s)Cannot apply 'CONCAT' to arguments of type "
+ + "'CONCAT\\(<DATE>, <TIMESTAMP\\(0\\)>\\)'\\. .*");
+ s.expr("concat(DATE '2020-04-17', TIMESTAMP '2020-04-17 14:17:51')").ok();
}
@Test void testBetween() {