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 64068d6 [CALCITE-1178] Allow SqlBetweenOperator to compare DATE and
TIMESTAMP
64068d6 is described below
commit 64068d69ceee8450e56c4705d26b7a2f944909b9
Author: yuzhao.cyz <[email protected]>
AuthorDate: Mon Sep 23 13:42:45 2019 +0800
[CALCITE-1178] Allow SqlBetweenOperator to compare DATE and TIMESTAMP
* Add implicit type coercion rules for BETWEEN operator and add test
cases
* For comparison of NUMERIC and CHARACTER, we coerce the CHARACTER to
NUMERIC type which is breaking change.(Before this patch, CHARACTER would
be coerced to INT during sql-to-rel conversion)
* For "DATETIME and CHARACTER" comparison and equality, add coercion rules
during sql
node validation, before this patch, the coercion happens during sql-to-rel
conversion
* Some code refactoring for TypeCoercionConverterTest
---
.../sql/type/ComparableOperandTypeChecker.java | 12 +-
.../validate/implicit/AbstractTypeCoercion.java | 33 +++-
.../sql/validate/implicit/TypeCoercion.java | 3 +
.../sql/validate/implicit/TypeCoercionImpl.java | 189 ++++++++++++--------
.../calcite/test/TypeCoercionConverterTest.java | 85 +++------
.../org/apache/calcite/test/TypeCoercionTest.java | 34 ++--
.../calcite/test/TypeCoercionConverterTest.xml | 192 ++++-----------------
core/src/test/resources/sql/misc.iq | 12 +-
8 files changed, 243 insertions(+), 317 deletions(-)
diff --git
a/core/src/main/java/org/apache/calcite/sql/type/ComparableOperandTypeChecker.java
b/core/src/main/java/org/apache/calcite/sql/type/ComparableOperandTypeChecker.java
index e48920f..659282e 100644
---
a/core/src/main/java/org/apache/calcite/sql/type/ComparableOperandTypeChecker.java
+++
b/core/src/main/java/org/apache/calcite/sql/type/ComparableOperandTypeChecker.java
@@ -59,19 +59,20 @@ public class ComparableOperandTypeChecker extends
SameOperandTypeChecker {
RelDataType type = callBinding.getOperandType(i);
if (!checkType(callBinding, throwOnFailure, type)) {
b = false;
+ break;
}
}
if (b) {
// Coerce type first.
if (callBinding.getValidator().isTypeCoercionEnabled()) {
TypeCoercion typeCoercion =
callBinding.getValidator().getTypeCoercion();
- // For comparable operators, e.g. >, <, =, >=, <=.
- typeCoercion.binaryArithmeticCoercion(callBinding);
+ // For comparison operators, i.e. >, <, =, >=, <=.
+ typeCoercion.binaryComparisonCoercion(callBinding);
}
b = super.checkOperandTypes(callBinding, false);
- if (!b && throwOnFailure) {
- throw callBinding.newValidationSignatureError();
- }
+ }
+ if (!b && throwOnFailure) {
+ throw callBinding.newValidationSignatureError();
}
return b;
}
@@ -104,6 +105,7 @@ public class ComparableOperandTypeChecker extends
SameOperandTypeChecker {
RelDataType type = callBinding.getOperandType(i);
if (type.getComparability().ordinal() < requiredComparability.ordinal())
{
b = false;
+ break;
}
}
if (b) {
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 3e0f4ee..acd0cdf 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
@@ -115,6 +115,25 @@ public abstract class AbstractTypeCoercion implements
TypeCoercion {
}
/**
+ * Coerce all the operands to {@code commonType}.
+ *
+ * @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,
+ SqlCall call,
+ RelDataType commonType) {
+ boolean coerced = false;
+ for (int i = 0; i < call.operandCount(); i++) {
+ coerced = coerceOperandType(scope, call, i, commonType) || coerced;
+ }
+ return coerced;
+ }
+
+ /**
* Cast column at index {@code index} to target type.
*
* @param scope validator scope for the node list
@@ -419,11 +438,15 @@ public abstract class AbstractTypeCoercion implements
TypeCoercion {
return null;
}
- if (SqlTypeUtil.isString(type1) && SqlTypeUtil.isDatetime(type2)
- || SqlTypeUtil.isDatetime(type1) && SqlTypeUtil.isString(type2)) {
- // Returns null instead of VARCHAR,
- // because calcite will do the cast in SqlToRelConverter.
- return null;
+ // DATETIME + CHARACTER -> DATETIME
+ // REVIEW Danny 2019-09-23: There is some legacy redundant code in
SqlToRelConverter
+ // that coerce Datetime and CHARACTER comparison.
+ if (SqlTypeUtil.isCharacter(type1) && SqlTypeUtil.isDatetime(type2)) {
+ return type2;
+ }
+
+ if (SqlTypeUtil.isDatetime(type1) && SqlTypeUtil.isCharacter(type2)) {
+ return type1;
}
// DATE + TIMESTAMP -> TIMESTAMP
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 7ba068b..e6b6cfe 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
@@ -118,6 +118,9 @@ public interface TypeCoercion {
/** Coerce operand of binary arithmetic expressions to Numeric type.*/
boolean binaryArithmeticCoercion(SqlCallBinding binding);
+ /** Coerce operands in binary comparison expressions. */
+ boolean binaryComparisonCoercion(SqlCallBinding binding);
+
/**
* Coerce CASE WHEN statement branches to one common type.
*
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 a66dd47..fe2f347 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
@@ -35,11 +35,14 @@ import org.apache.calcite.sql.type.SqlTypeUtil;
import org.apache.calcite.sql.validate.SqlUserDefinedTableMacro;
import org.apache.calcite.sql.validate.SqlValidator;
import org.apache.calcite.sql.validate.SqlValidatorScope;
+import org.apache.calcite.util.Util;
import java.math.BigDecimal;
import java.util.AbstractList;
import java.util.ArrayList;
+import java.util.Arrays;
import java.util.List;
+import java.util.stream.Collectors;
/**
* Default implementation of Calcite implicit type cast.
@@ -71,7 +74,7 @@ public class TypeCoercionImpl extends AbstractTypeCoercion {
* @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.
+ * @return true if any type coercion actually happens
*/
public boolean rowTypeCoercion(
SqlValidatorScope scope,
@@ -113,28 +116,22 @@ public class TypeCoercionImpl extends
AbstractTypeCoercion {
/**
* Coerce operands in binary arithmetic expressions to NUMERIC types.
*
- * <p>Rules:</p>
- * <ul>
- * <li>For binary arithmetic operators like [+, -, *, /, %]: 1. If the
operand is VARCHAR type,
- * coerce it to data type of the other operand if its data type is
NUMERIC.</li>
- * <li>For EQUALS(=) operator: 1. If operands are BOOLEAN and NUMERIC,
evaluate
- * `1=true` and `0=false` all to be true; 2. If operands are datetime and
string,
- * do nothing because the SqlToRelConverter already makes the type
coercion.</li>
- * <li>For binary comparision [=, >, >=, <, <=]: try to find
the common type,
- * i.e. "1 > '1'" will be converted to "1 > 1".</li>
- * <li>Some single agg functions: coerce string operand to DECIMAL
type.</li>
- * </ul>
+ * <p>For binary arithmetic operators like [+, -, *, /, %]:
+ * If the operand is VARCHAR,
+ * coerce it to data type of the other operand if its data type is NUMERIC;
+ * If the other operand is DECIMAL,
+ * coerce the STRING operand to max precision/scale DECIMAL.
*/
public boolean binaryArithmeticCoercion(SqlCallBinding binding) {
- // Assert that the operator has NUMERIC family operand type checker.
+ // Assume that the operator has NUMERIC family operand type checker.
SqlOperator operator = binding.getOperator();
SqlKind kind = operator.getKind();
boolean coerced = false;
// Binary operator
if (binding.getOperandCount() == 2) {
- RelDataType type1 = binding.getOperandType(0);
- RelDataType type2 = binding.getOperandType(1);
- // Special case for datetime +/- interval
+ final RelDataType type1 = binding.getOperandType(0);
+ final RelDataType type2 = binding.getOperandType(1);
+ // Special case for datetime + interval or datetime - interval
if (kind == SqlKind.PLUS || kind == SqlKind.MINUS) {
if (SqlTypeUtil.isInterval(type1) || SqlTypeUtil.isInterval(type2)) {
return false;
@@ -144,49 +141,6 @@ public class TypeCoercionImpl extends AbstractTypeCoercion
{
if (kind.belongsTo(SqlKind.BINARY_ARITHMETIC)) {
coerced = binaryArithmeticWithStrings(binding, type1, type2);
}
- // EQUALS(=) NOT_EQUALS(<>) operator
- if (kind.belongsTo(SqlKind.BINARY_EQUALITY)) {
- // STRING and datetime
- // BOOLEAN and NUMERIC | BOOLEAN and literal
- coerced = dateTimeStringEquality(binding, type1, type2) || coerced;
- coerced = booleanEquality(binding, type1, type2) || coerced;
- }
- // Binary comparision operator like: = > >= < <=
- if (kind.belongsTo(SqlKind.BINARY_COMPARISON)) {
- RelDataType commonType = commonTypeForBinaryComparison(type1, type2);
- if (null != commonType) {
- coerced = coerceOperandType(binding.getScope(), binding.getCall(),
0, commonType)
- || coerced;
- coerced = coerceOperandType(binding.getScope(), binding.getCall(),
1, commonType)
- || coerced;
- }
- }
- }
- // Single operand agg function, cast string operand to DECIMAL.
- if (binding.getOperandCount() == 1) {
- RelDataType type = validator.deriveType(binding.getScope(),
binding.operand(0));
- boolean isStringType = SqlTypeUtil.isCharacter(type);
- if (operator.getName().equalsIgnoreCase("ABS")
- && isStringType) {
- return coerceOperandType(binding.getScope(), binding.getCall(), 0,
- SqlTypeUtil.getMaxPrecisionScaleDecimal(factory));
- }
- // Better to move these functions to a single method.
- switch (kind) {
- case SUM:
- case SUM0:
- case AVG:
- case STDDEV_POP:
- case STDDEV_SAMP:
- case MINUS_PREFIX:
- case PLUS_PREFIX:
- case VAR_POP:
- case VAR_SAMP:
- if (isStringType) {
- return coerceOperandType(binding.getScope(), binding.getCall(), 0,
- SqlTypeUtil.getMaxPrecisionScaleDecimal(factory));
- }
- }
}
return coerced;
}
@@ -224,6 +178,105 @@ public class TypeCoercionImpl extends
AbstractTypeCoercion {
}
/**
+ * Coerce operands in binary comparison expressions.
+ *
+ * <p>Rules:</p>
+ * <ul>
+ * <li>For EQUALS(=) operator: 1. If operands are BOOLEAN and NUMERIC,
evaluate
+ * `1=true` and `0=false` all to be true; 2. If operands are datetime and
string,
+ * do nothing because the SqlToRelConverter already makes the type
coercion;</li>
+ * <li>For binary comparision [=, >, >=, <, <=]: try to find
the common type,
+ * i.e. "1 > '1'" will be converted to "1 > 1";</li>
+ * <li>For BETWEEN operator, find the common comparison data type of all
the operands,
+ * the common type is deduced from left to right, i.e. for expression "A
between B and C",
+ * finds common comparison type D between A and B
+ * then common comparison type E between D and C as the final common
type.</li>
+ * </ul>
+ */
+ public boolean binaryComparisonCoercion(SqlCallBinding binding) {
+ SqlOperator operator = binding.getOperator();
+ SqlKind kind = operator.getKind();
+ int operandCnt = binding.getOperandCount();
+ boolean coerced = false;
+ // Binary operator
+ if (operandCnt == 2) {
+ final RelDataType type1 = binding.getOperandType(0);
+ final RelDataType type2 = binding.getOperandType(1);
+ // EQUALS(=) NOT_EQUALS(<>) operator
+ if (kind.belongsTo(SqlKind.BINARY_EQUALITY)) {
+ // STRING and datetime
+ // BOOLEAN and NUMERIC | BOOLEAN and literal
+ coerced = dateTimeStringEquality(binding, type1, type2) || coerced;
+ coerced = booleanEquality(binding, type1, type2) || coerced;
+ }
+ // Binary comparision operator like: = > >= < <=
+ if (kind.belongsTo(SqlKind.BINARY_COMPARISON)) {
+ final RelDataType commonType = commonTypeForBinaryComparison(type1,
type2);
+ if (null != commonType) {
+ coerced = coerceOperandsType(binding.getScope(), binding.getCall(),
commonType);
+ }
+ }
+ }
+ // Infix operator like: BETWEEN
+ if (kind == SqlKind.BETWEEN) {
+ final List<RelDataType> operandTypes = Util.range(operandCnt).stream()
+ .map(binding::getOperandType)
+ .collect(Collectors.toList());
+ final RelDataType commonType = commonTypeForComparison(operandTypes);
+ if (null != commonType) {
+ coerced = coerceOperandsType(binding.getScope(), binding.getCall(),
commonType);
+ }
+ }
+ return coerced;
+ }
+
+ /**
+ * Finds the common type for binary comparison
+ * when the size of operands {@code dataTypes} is more than 2.
+ * If there are N(more than 2) operands,
+ * finds the common type between two operands from left to right:
+ *
+ * <p>Rules:</p>
+ * <pre>
+ * type1 type2 type3
+ * | | |
+ * +- type4 -+ |
+ * | |
+ * +--- type5 ---+
+ * </pre>
+ * For operand data types (type1, type2, type3), deduce the common type type4
+ * from type1 and type2, then common type type5 from type4 and type3.
+ */
+ protected RelDataType commonTypeForComparison(List<RelDataType> dataTypes) {
+ assert dataTypes.size() > 2;
+ final RelDataType type1 = dataTypes.get(0);
+ final RelDataType type2 = dataTypes.get(1);
+ // No need to do type coercion if all the data types have the same type
name.
+ boolean allWithSameName = SqlTypeUtil.sameNamedType(type1, type2);
+ for (int i = 2; i < dataTypes.size() && allWithSameName; i++) {
+ allWithSameName = SqlTypeUtil.sameNamedType(dataTypes.get(i - 1),
dataTypes.get(i));
+ }
+ if (allWithSameName) {
+ return null;
+ }
+
+ RelDataType commonType;
+ if (SqlTypeUtil.sameNamedType(type1, type2)) {
+ commonType = factory.leastRestrictive(Arrays.asList(type1, type2));
+ } else {
+ commonType = commonTypeForBinaryComparison(type1, type2);
+ }
+ for (int i = 2; i < dataTypes.size() && commonType != null; i++) {
+ if (SqlTypeUtil.sameNamedType(commonType, dataTypes.get(i))) {
+ commonType = factory.leastRestrictive(Arrays.asList(commonType,
dataTypes.get(i)));
+ } else {
+ commonType = commonTypeForBinaryComparison(commonType,
dataTypes.get(i));
+ }
+ }
+ return commonType;
+ }
+
+ /**
* 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.
@@ -234,15 +287,15 @@ public class TypeCoercionImpl extends
AbstractTypeCoercion {
RelDataType right) {
// REVIEW Danny 2018-05-23 we do not need to coerce type for EQUALS
// because SqlToRelConverter already does this.
- if (binding.getCall().getKind() == SqlKind.NOT_EQUALS) {
- if (SqlTypeUtil.isCharacter(left)
- && SqlTypeUtil.isDatetime(right)) {
- return coerceOperandType(binding.getScope(), binding.getCall(), 0,
right);
- }
- if (SqlTypeUtil.isCharacter(right)
- && SqlTypeUtil.isDatetime(left)) {
- return coerceOperandType(binding.getScope(), binding.getCall(), 1,
left);
- }
+ // REVIEW Danny 2019-09-23, we should unify the coercion rules in
TypeCoercion
+ // instead of SqlToRelConverter.
+ if (SqlTypeUtil.isCharacter(left)
+ && SqlTypeUtil.isDatetime(right)) {
+ return coerceOperandType(binding.getScope(), binding.getCall(), 0,
right);
+ }
+ if (SqlTypeUtil.isCharacter(right)
+ && SqlTypeUtil.isDatetime(left)) {
+ return coerceOperandType(binding.getScope(), binding.getCall(), 1, left);
}
return false;
}
diff --git
a/core/src/test/java/org/apache/calcite/test/TypeCoercionConverterTest.java
b/core/src/test/java/org/apache/calcite/test/TypeCoercionConverterTest.java
index 3551b60..ed5ca2b 100644
--- a/core/src/test/java/org/apache/calcite/test/TypeCoercionConverterTest.java
+++ b/core/src/test/java/org/apache/calcite/test/TypeCoercionConverterTest.java
@@ -37,47 +37,28 @@ public class TypeCoercionConverterTest extends
SqlToRelTestBase {
}
/** Test case for {@link TypeCoercion#commonTypeForBinaryComparison}. */
- @Test public void testBinaryComparable() {
+ @Test public void testBinaryComparison() {
// for constant cast, there is reduce rule
- checkPlanEquals("select 1<'1' from (values true)");
- }
-
- @Test public void testBinaryComparable1() {
- checkPlanEquals("select 1<='1' from (values true)");
- }
-
- @Test public void testBinaryComparable2() {
- checkPlanEquals("select 1>'1' from (values true)");
- }
-
- @Test public void testBinaryComparable3() {
- checkPlanEquals("select 1>='1' from (values true)");
- }
-
- @Test public void testBinaryComparable4() {
- checkPlanEquals("select 1='1' from (values true)");
- }
-
- @Test public void testBinaryComparable5() {
- checkPlanEquals("select t1_date > t1_timestamp from t1");
- }
-
- @Test public void testBinaryComparable6() {
- checkPlanEquals("select '2' is not distinct from 2 from (values true)");
+ checkPlanEquals("select\n"
+ + "1<'1' as f0,\n"
+ + "1<='1' as f1,\n"
+ + "1>'1' as f2,\n"
+ + "1>='1' as f3,\n"
+ + "1='1' as f4,\n"
+ + "t1_date > t1_timestamp as f5,\n"
+ + "'2' is not distinct from 2 as f6,\n"
+ + "'2019-09-23' between t1_date and t1_timestamp as f7,\n"
+ + "cast('2019-09-23' as date) between t1_date and t1_timestamp as f8\n"
+ + "from t1");
}
/** Test cases for {@link TypeCoercion#inOperationCoercion}. */
@Test public void testInOperation() {
- checkPlanEquals("select 1 in ('1', '2', '3') from (values true)");
- }
-
- @Test public void testInOperation1() {
- checkPlanEquals("select (1, 2) in (select '1', '2' "
- + "from (values (true, true))) from (values true)");
- }
-
- @Test public void testInOperation2() {
- checkPlanEquals("select (1, 2) in (('1', '2'), ('3', '4')) from (values
true)");
+ checkPlanEquals("select\n"
+ + "1 in ('1', '2', '3') as f0,\n"
+ + "(1, 2) in (('1', '2')) as f1,\n"
+ + "(1, 2) in (('1', '2'), ('3', '4')) as f2\n"
+ + "from (values (true, true, true))");
}
/** Test cases for
@@ -87,28 +68,14 @@ public class TypeCoercionConverterTest extends
SqlToRelTestBase {
// Calcite execution runtime, but we still add cast in the plan so other
systems
// using Calcite can rewrite Cast operator implementation.
// for this case, we replace the boolean literal with numeric 1.
- checkPlanEquals("select 1=true from (values true)");
- }
-
- @Test public void testBooleanEquality1() {
- checkPlanEquals("select 1.0=true from (values true)");
- }
-
- @Test public void testBooleanEquality2() {
- checkPlanEquals("select 0.0=true from (values true)");
- }
-
- @Test public void testBooleanEquality3() {
- checkPlanEquals("select 1.23=t1_boolean from t1");
- }
-
- @Test public void testBooleanEquality4() {
- // int boolean
- checkPlanEquals("select t1_smallint=t1_boolean from t1");
- }
-
- @Test public void testBooleanEquality5() {
- checkPlanEquals("select 10000000000=true from (values true)");
+ checkPlanEquals("select\n"
+ + "1=true as f0,\n"
+ + "1.0=true as f1,\n"
+ + "0.0=true=true as f2,\n"
+ + "1.23=t1_boolean as f3,\n"
+ + "t1_smallint=t1_boolean as f4,\n"
+ + "10000000000=true as f5\n"
+ + "from t1");
}
@Test public void testCaseWhen() {
@@ -123,7 +90,7 @@ public class TypeCoercionConverterTest extends
SqlToRelTestBase {
checkPlanEquals("select * from (values(1, '3')) union select * from
(values('2', 4))");
}
- @Test public void testSetOperations() {
+ @Test public void testSetOperation() {
// int decimal smallint double
// char decimal float bigint
// char decimal float double
diff --git a/core/src/test/java/org/apache/calcite/test/TypeCoercionTest.java
b/core/src/test/java/org/apache/calcite/test/TypeCoercionTest.java
index 46b72bd..c7075df 100644
--- a/core/src/test/java/org/apache/calcite/test/TypeCoercionTest.java
+++ b/core/src/test/java/org/apache/calcite/test/TypeCoercionTest.java
@@ -536,21 +536,31 @@ public class TypeCoercionTest extends
SqlValidatorTestCase {
/** Test cases for binary comparison expressions. */
@Test public void testBinaryComparisonCoercion() {
- sql("select '2' = 3 from (values true)")
+ checkExpType("'2' = 3", "BOOLEAN NOT NULL");
+ checkExpType("'2' > 3", "BOOLEAN NOT NULL");
+ checkExpType("'2' >= 3", "BOOLEAN NOT NULL");
+ checkExpType("'2' < 3", "BOOLEAN NOT NULL");
+ checkExpType("'2' <= 3", "BOOLEAN NOT NULL");
+ checkExpType("'2' is distinct from 3", "BOOLEAN NOT NULL");
+ checkExpType("'2' is not distinct from 3", "BOOLEAN NOT NULL");
+ // NULL operand
+ checkExpType("'2' = null", "BOOLEAN");
+ checkExpType("'2' > null", "BOOLEAN");
+ checkExpType("'2' >= null", "BOOLEAN");
+ checkExpType("'2' < null", "BOOLEAN");
+ checkExpType("'2' <= null", "BOOLEAN");
+ checkExpType("'2' is distinct from null", "BOOLEAN NOT NULL");
+ checkExpType("'2' is not distinct from null", "BOOLEAN NOT NULL");
+ // BETWEEN operator
+ checkExpType("'2' between 1 and 3", "BOOLEAN NOT NULL");
+ checkExpType("NULL between 1 and 3", "BOOLEAN");
+ sql("select '2019-09-23' between t1_date and t1_timestamp from t1")
.columnType("BOOLEAN NOT NULL");
- sql("select '2' > 3 from (values true)")
+ sql("select t1_date between '2019-09-23' and t1_timestamp from t1")
.columnType("BOOLEAN NOT NULL");
- sql("select '2' >= 3 from (values true)")
+ sql("select cast('2019-09-23' as date) between t1_date and t1_timestamp
from t1")
.columnType("BOOLEAN NOT NULL");
- sql("select '2' < 3 from (values true)")
- .columnType("BOOLEAN NOT NULL");
- sql("select '2' <= 3 from (values true)")
- .columnType("BOOLEAN NOT NULL");
- sql("select '2' is distinct from 3 from (values true)")
- .columnType("BOOLEAN NOT NULL");
- sql("select '2' is not distinct from 3 from (values true)")
- .columnType("BOOLEAN NOT NULL");
- sql("select '2' is not distinct from 3 from (values true)")
+ sql("select t1_date between cast('2019-09-23' as date) and t1_timestamp
from t1")
.columnType("BOOLEAN NOT NULL");
}
diff --git
a/core/src/test/resources/org/apache/calcite/test/TypeCoercionConverterTest.xml
b/core/src/test/resources/org/apache/calcite/test/TypeCoercionConverterTest.xml
index d0cf220..f697ae2 100644
---
a/core/src/test/resources/org/apache/calcite/test/TypeCoercionConverterTest.xml
+++
b/core/src/test/resources/org/apache/calcite/test/TypeCoercionConverterTest.xml
@@ -18,190 +18,58 @@ limitations under the License.
<Root>
<TestCase name="testInOperation">
<Resource name="sql">
- <![CDATA[select 1 in ('1', '2', '3') from (values true)]]>
+ <![CDATA[select
+1 in ('1', '2', '3') as f0,
+(1, 2) in (('1', '2')) as f1,
+(1, 2) in (('1', '2'), ('3', '4')) as f2
+from (values (true, true, true))"]]>
</Resource>
<Resource name="plan">
<![CDATA[
-LogicalProject(EXPR$0=[true])
- LogicalValues(tuples=[[{ true }]])
-]]>
- </Resource>
- </TestCase>
- <TestCase name="testInOperation1">
- <Resource name="sql">
- <![CDATA[select (1, 2) in (select '1', '2'
-from (values (true, true))) from (values true))]]>
- </Resource>
- <Resource name="plan">
- <![CDATA[
-LogicalProject(EXPR$0=[CAST(OR(AND(IS NOT NULL($7), <>($1, 0)), AND(<($2, $1),
null, <>($1, 0), IS NULL($7)))):BOOLEAN NOT NULL])
- LogicalJoin(condition=[AND(=($3, $5), =($4, $6))], joinType=[left])
- LogicalProject(EXPR$0=[$0], $f0=[$1], $f1=[$2], $f3=[1], $f4=[2])
- LogicalJoin(condition=[true], joinType=[inner])
- LogicalValues(tuples=[[{ true }]])
- LogicalAggregate(group=[{}], agg#0=[COUNT()], agg#1=[COUNT($0, $1)])
- LogicalProject(EXPR$0=[1], EXPR$1=[2], $f2=[true])
- LogicalValues(tuples=[[{ true, true }]])
- LogicalAggregate(group=[{0, 1}], agg#0=[MIN($2)])
- LogicalProject(EXPR$0=[1], EXPR$1=[2], $f2=[true])
- LogicalValues(tuples=[[{ true, true }]])
-]]>
- </Resource>
- </TestCase>
- <TestCase name="testInOperation2">
- <Resource name="sql">
- <![CDATA[select 1 in ('1', '2', '3') from (values true)]]>
- </Resource>
- <Resource name="plan">
- <![CDATA[
-LogicalProject(EXPR$0=[true])
- LogicalValues(tuples=[[{ true }]])
+LogicalProject(F0=[true], F1=[true], F2=[true])
+ LogicalValues(tuples=[[{ true, true, true }]])
]]>
</Resource>
</TestCase>
<TestCase name="testBooleanEquality">
<Resource name="sql">
- <![CDATA[select 1=true from (values true)]]>
- </Resource>
- <Resource name="plan">
- <![CDATA[
-LogicalProject(EXPR$0=[true])
- LogicalValues(tuples=[[{ true }]])
-]]>
- </Resource>
- </TestCase>
- <TestCase name="testBooleanEquality1">
- <Resource name="sql">
- <![CDATA[select 1.0=true from (values true)]]>
- </Resource>
- <Resource name="plan">
- <![CDATA[
-LogicalProject(EXPR$0=[true])
- LogicalValues(tuples=[[{ true }]])
-]]>
- </Resource>
- </TestCase>
- <TestCase name="testBooleanEquality2">
- <Resource name="sql">
- <![CDATA[select 0.0=true from (values true)]]>
- </Resource>
- <Resource name="plan">
- <![CDATA[
-LogicalProject(EXPR$0=[false])
- LogicalValues(tuples=[[{ true }]])
-]]>
- </Resource>
- </TestCase>
- <TestCase name="testBooleanEquality3">
- <Resource name="sql">
- <![CDATA[select 1.23=t1_boolean from t1]]>
+ <![CDATA[select
+1=true as f0,
+1.0=true as f1,
+0.0=true=true as f2,
+1.23=t1_boolean as f3,
+t1_smallint=t1_boolean as f4,
+10000000000=true as f5
+from t1]]>
</Resource>
<Resource name="plan">
<![CDATA[
-LogicalProject(EXPR$0=[NOT($10)])
+LogicalProject(F0=[true], F1=[true], F2=[false], F3=[NOT($10)], F4=[=($1,
CASE($10, 1:SMALLINT, 0:SMALLINT))], F5=[false])
LogicalTableScan(table=[[CATALOG, SALES, T1]])
]]>
</Resource>
</TestCase>
- <TestCase name="testBooleanEquality4">
- <Resource name="sql">
- <![CDATA[select t1_smallint=t1_boolean from t1]]>
- </Resource>
- <Resource name="plan">
- <![CDATA[
-LogicalProject(EXPR$0=[=($1, CASE($10, 1:SMALLINT, 0:SMALLINT))])
- LogicalTableScan(table=[[CATALOG, SALES, T1]])
-]]>
- </Resource>
- </TestCase>
- <TestCase name="testBooleanEquality5">
- <Resource name="sql">
- <![CDATA[select 10000000000=true from (values true)]]>
- </Resource>
- <Resource name="plan">
- <![CDATA[
-LogicalProject(EXPR$0=[false])
- LogicalValues(tuples=[[{ true }]])
-]]>
- </Resource>
- </TestCase>
- <TestCase name="testBinaryComparable">
- <Resource name="sql">
- <![CDATA[select 1<'1' from (values true)]]>
- </Resource>
- <Resource name="plan">
- <![CDATA[
-LogicalProject(EXPR$0=[false])
- LogicalValues(tuples=[[{ true }]])
-]]>
- </Resource>
- </TestCase>
- <TestCase name="testBinaryComparable1">
- <Resource name="sql">
- <![CDATA[select 1<='1' from (values true)]]>
- </Resource>
- <Resource name="plan">
- <![CDATA[
-LogicalProject(EXPR$0=[true])
- LogicalValues(tuples=[[{ true }]])
-]]>
- </Resource>
- </TestCase>
- <TestCase name="testBinaryComparable2">
- <Resource name="sql">
- <![CDATA[select 1>'1' from (values true)]]>
- </Resource>
- <Resource name="plan">
- <![CDATA[
-LogicalProject(EXPR$0=[false])
- LogicalValues(tuples=[[{ true }]])
-]]>
- </Resource>
- </TestCase>
- <TestCase name="testBinaryComparable3">
- <Resource name="sql">
- <![CDATA[select 1>='1' from (values true)]]>
- </Resource>
- <Resource name="plan">
- <![CDATA[
-LogicalProject(EXPR$0=[true])
- LogicalValues(tuples=[[{ true }]])
-]]>
- </Resource>
- </TestCase>
- <TestCase name="testBinaryComparable4">
- <Resource name="sql">
- <![CDATA[select 1='1' from (values true)]]>
- </Resource>
- <Resource name="plan">
- <![CDATA[
-LogicalProject(EXPR$0=[true])
- LogicalValues(tuples=[[{ true }]])
-]]>
- </Resource>
- </TestCase>
- <TestCase name="testBinaryComparable5">
+ <TestCase name="testBinaryComparison">
<Resource name="sql">
- <![CDATA[select t1_date > t1_timestamp from t1]]>
+ <![CDATA["select
+1<'1' as f0,
+1<='1' as f1,
+1>'1' as f2,
+1>='1' as f3,
+1='1' as f4,
+t1_date > t1_timestamp as f5,
+'2' is not distinct from 2 as f6,
+'2019-09-23' between t1_date and t1_timestamp as f7,
+cast('2019-09-23' as date) between t1_date and t1_timestamp as f8
+from t1"]]>
</Resource>
<Resource name="plan">
<![CDATA[
-LogicalProject(EXPR$0=[>(CAST($8):TIMESTAMP(0) NOT NULL, $7)])
+LogicalProject(F0=[false], F1=[true], F2=[false], F3=[true], F4=[true],
F5=[>(CAST($8):TIMESTAMP(0) NOT NULL, $7)], F6=[true], F7=[AND(>=(2019-09-23
00:00:00, CAST($8):TIMESTAMP(0) NOT NULL), <=(2019-09-23 00:00:00, $7))],
F8=[AND(>=(2019-09-23 00:00:00, CAST($8):TIMESTAMP(0) NOT NULL), <=(2019-09-23
00:00:00, $7))])
LogicalTableScan(table=[[CATALOG, SALES, T1]])
]]>
</Resource>
</TestCase>
- <TestCase name="testBinaryComparable6">
- <Resource name="sql">
- <![CDATA[select '2' is not distinct from 2 from (values true)]]>
- </Resource>
- <Resource name="plan">
- <![CDATA[
-LogicalProject(EXPR$0=[true])
- LogicalValues(tuples=[[{ true }]])
-]]>
- </Resource>
- </TestCase>
<TestCase name="testBuiltinFunctionCoercion">
<Resource name="sql">
<![CDATA[select 1||'a' from (values true)]]>
@@ -227,7 +95,7 @@ LogicalUnion(all=[false])
]]>
</Resource>
</TestCase>
- <TestCase name="testSetOperations">
+ <TestCase name="testSetOperation">
<Resource name="sql">
<