This is an automated email from the ASF dual-hosted git repository.
asolimando 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 83882017b1 [CALCITE-7174] Improve lossless cast detection for numeric
types
83882017b1 is described below
commit 83882017b188222a06916145b0f4867b6a7e2b1f
Author: Alessandro Solimando <[email protected]>
AuthorDate: Thu Sep 11 19:17:07 2025 +0200
[CALCITE-7174] Improve lossless cast detection for numeric types
Deprecated getMinValue/getMaxValue methods in favor of integerBound
---
.../main/java/org/apache/calcite/rex/RexUtil.java | 86 +++++-
.../org/apache/calcite/sql/type/SqlTypeUtil.java | 86 +++++-
.../apache/calcite/rex/RexLosslessCastTest.java | 325 ++++++++++++++++++++-
.../apache/calcite/rex/RexProgramBuilderBase.java | 34 ++-
.../apache/calcite/sql/type/SqlTypeUtilTest.java | 59 +++-
core/src/test/resources/sql/planner.iq | 38 +--
core/src/test/resources/sql/sub-query.iq | 14 +-
7 files changed, 589 insertions(+), 53 deletions(-)
diff --git a/core/src/main/java/org/apache/calcite/rex/RexUtil.java
b/core/src/main/java/org/apache/calcite/rex/RexUtil.java
index cfa0f25b3c..d4cc04c6eb 100644
--- a/core/src/main/java/org/apache/calcite/rex/RexUtil.java
+++ b/core/src/main/java/org/apache/calcite/rex/RexUtil.java
@@ -1676,7 +1676,6 @@ public static boolean isLosslessCast(RexNode node) {
return isLosslessCast(((RexCall) node).getOperands().get(0).getType(),
node.getType());
}
-
/**
* Returns whether the conversion from {@code source} to {@code target} type
* is a 'loss-less' cast, that is, a cast from which
@@ -1693,22 +1692,31 @@ public static boolean isLosslessCast(RexNode node) {
* @return 'true' when the conversion can certainly be determined to be
loss-less cast,
* but may return 'false' for some lossless casts.
*/
- @API(since = "1.22", status = API.Status.EXPERIMENTAL)
+ @API(since = "1.22", status = API.Status.STABLE)
public static boolean isLosslessCast(RelDataType source, RelDataType target)
{
final SqlTypeName sourceSqlTypeName = source.getSqlTypeName();
final SqlTypeName targetSqlTypeName = target.getSqlTypeName();
- // 1) Both INT numeric types
- if (SqlTypeFamily.INTEGER.getTypeNames().contains(sourceSqlTypeName)
- && SqlTypeFamily.INTEGER.getTypeNames().contains(targetSqlTypeName)) {
- return targetSqlTypeName.compareTo(sourceSqlTypeName) >= 0;
+
+ // INT -> INT: use range containment (signed/unsigned)
+ if (SqlTypeUtil.isIntType(source) && SqlTypeUtil.isIntType(target)) {
+ final boolean sourceIsUnsigned =
+
SqlTypeFamily.UNSIGNED_NUMERIC.getTypeNames().contains(sourceSqlTypeName);
+ final boolean targetIsUnsigned =
+
SqlTypeFamily.UNSIGNED_NUMERIC.getTypeNames().contains(targetSqlTypeName);
+ if (!sourceIsUnsigned && targetIsUnsigned) {
+ return false;
+ }
+ return SqlTypeUtil.integerRangeContains(target, source);
}
- // 2) Both CHARACTER types: it depends on the precision (length)
+
+ // CHARACTER -> CHARACTER: valid if target order >= source and length grows
if (SqlTypeFamily.CHARACTER.getTypeNames().contains(sourceSqlTypeName)
&& SqlTypeFamily.CHARACTER.getTypeNames().contains(targetSqlTypeName))
{
return targetSqlTypeName.compareTo(sourceSqlTypeName) >= 0
&& source.getPrecision() <= target.getPrecision();
}
- // 3) From NUMERIC family to CHARACTER family: it depends on the
precision/scale
+
+ // NUMERIC -> CHARACTER: allow when target length accommodates sign/scale
if (sourceSqlTypeName.getFamily() == SqlTypeFamily.NUMERIC
&& targetSqlTypeName.getFamily() == SqlTypeFamily.CHARACTER) {
int sourceLength = source.getPrecision() + 1; // include sign
@@ -1718,7 +1726,67 @@ public static boolean isLosslessCast(RelDataType source,
RelDataType target) {
final int targetPrecision = target.getPrecision();
return targetPrecision == PRECISION_NOT_SPECIFIED || targetPrecision >=
sourceLength;
}
- // Return FALSE by default
+
+ // DECIMAL -> DECIMAL: allow when precision/scale can only expand
+ if (sourceSqlTypeName == SqlTypeName.DECIMAL
+ && targetSqlTypeName == SqlTypeName.DECIMAL) {
+ int sourcePrecision = source.getPrecision();
+ int sourceScale = Math.max(source.getScale(), 0);
+ int targetPrecision = target.getPrecision();
+ int targetScale = Math.max(target.getScale(), 0);
+ if (sourcePrecision <= 0 || targetPrecision <= 0) {
+ return false;
+ }
+ return targetScale >= sourceScale
+ && (targetPrecision - targetScale) >= (sourcePrecision -
sourceScale);
+ }
+
+ // INT (signed/unsigned) -> DECIMAL: valid if integer digits fit within
target precision
+ if (SqlTypeUtil.isIntType(source)
+ && targetSqlTypeName == SqlTypeName.DECIMAL) {
+ int targetPrecision = target.getPrecision();
+ int targetScale = Math.max(target.getScale(), 0);
+ int sourcePrecision = source.getPrecision();
+ return sourcePrecision > 0 && (targetPrecision - targetScale) >=
sourcePrecision;
+ }
+
+ // DECIMAL -> INT: only when scale = 0 and range fits target integer type
+ if (sourceSqlTypeName == SqlTypeName.DECIMAL &&
SqlTypeUtil.isIntType(target)) {
+ if (source.getScale() != 0) {
+ return false;
+ }
+ return SqlTypeUtil.integerRangeContains(target, source);
+ }
+
+ // APPROXIMATE NUMERIC -> APPROXIMATE NUMERIC: allow when target precision
>= source precision
+ if
(SqlTypeFamily.APPROXIMATE_NUMERIC.getTypeNames().contains(sourceSqlTypeName)
+ &&
SqlTypeFamily.APPROXIMATE_NUMERIC.getTypeNames().contains(targetSqlTypeName)) {
+ // Lossless if target has at least as many significant digits as source
+ final int sourcePrecision = source.getPrecision();
+ final int targetPrecision = target.getPrecision();
+ return targetPrecision >= sourcePrecision;
+ }
+
+ // EXACT NUMERIC -> APPROXIMATE NUMERIC: allow only for scale=0 values
within target digits
+ if (SqlTypeFamily.EXACT_NUMERIC.getTypeNames().contains(sourceSqlTypeName)
+ &&
SqlTypeFamily.APPROXIMATE_NUMERIC.getTypeNames().contains(targetSqlTypeName)) {
+ final int targetPrecision = target.getPrecision();
+
+ // DECIMAL -> APPROXIMATE NUMERIC
+ if (sourceSqlTypeName == SqlTypeName.DECIMAL) {
+ final int sourcePrecision = source.getPrecision();
+ if (sourcePrecision <= 0 || source.getScale() != 0) {
+ return false;
+ }
+ // scale is 0, just check precision
+ return sourcePrecision <= targetPrecision;
+ }
+
+ // INT (signed/unsigned) -> APPROXIMATE NUMERIC
+ int sourcePrecision = source.getPrecision();
+ return sourcePrecision > 0 && sourcePrecision <= targetPrecision;
+ }
+
return false;
}
diff --git a/core/src/main/java/org/apache/calcite/sql/type/SqlTypeUtil.java
b/core/src/main/java/org/apache/calcite/sql/type/SqlTypeUtil.java
index 3de5e17df9..6a952aeaf3 100644
--- a/core/src/main/java/org/apache/calcite/sql/type/SqlTypeUtil.java
+++ b/core/src/main/java/org/apache/calcite/sql/type/SqlTypeUtil.java
@@ -51,6 +51,7 @@
import org.checkerframework.checker.nullness.qual.Nullable;
import java.math.BigDecimal;
+import java.math.BigInteger;
import java.math.RoundingMode;
import java.nio.charset.Charset;
import java.util.AbstractList;
@@ -556,6 +557,85 @@ public static boolean isExactNumeric(RelDataType type) {
}
}
+ /**
+ * Returns whether {@code container} can represent every value produced by
+ * {@code content} without loss of information.
+ *
+ * <p>The {@code container} type must be one of the integer types (signed or
+ * unsigned). The {@code content} type can be integer, or a DECIMAL with
+ * scale {@code 0}. For all other types this method returns {@code false}.
+ *
+ * @throws IllegalArgumentException if {@code container} is not an integer
type
+ */
+ public static boolean integerRangeContains(RelDataType container,
RelDataType content) {
+ checkArgument(isIntType(container),
+ "container must be an integer type: %s", container);
+
+ final SqlTypeName contentType = content.getSqlTypeName();
+ final boolean contentIsDecimal = contentType == SqlTypeName.DECIMAL;
+ if (!isIntType(content) && (!contentIsDecimal || content.getScale() != 0))
{
+ return false;
+ }
+
+ final BigInteger containerMin = integerBound(container, false);
+ final BigInteger containerMax = integerBound(container, true);
+ if (containerMin == null || containerMax == null) {
+ return false;
+ }
+
+ final BigInteger contentMin = integerBound(content, false);
+ final BigInteger contentMax = integerBound(content, true);
+ if (contentMin == null || contentMax == null) {
+ return false;
+ }
+
+ return containerMin.compareTo(contentMin) <= 0
+ && containerMax.compareTo(contentMax) >= 0;
+ }
+
+ /**
+ * Returns the numeric bound for an integer or zero-scale decimal type.
+ *
+ * @param type Type whose bounds should be computed
+ * @param upper If {@code true}, returns the maximum inclusive bound;
+ * otherwise returns the minimum bound
+ * @return Bound as {@link BigInteger}, or {@code null} if the bound cannot
be
+ * determined (for example, type is not integer or has non-zero
scale)
+ */
+ public static @Nullable BigInteger integerBound(RelDataType type, boolean
upper) {
+ final SqlTypeName typeName = type.getSqlTypeName();
+
+ final boolean isDecimal = typeName == SqlTypeName.DECIMAL;
+ if (!isDecimal && !isIntType(type)) {
+ return null;
+ }
+ if (isDecimal && type.getScale() != 0) {
+ return null;
+ }
+
+ final int precision = isDecimal ? type.getPrecision() : -1;
+ final int scale = isDecimal ? type.getScale() : -1;
+ final Object limit =
+ typeName.getLimit(upper, SqlTypeName.Limit.OVERFLOW,
+ false,
+ precision,
+ scale);
+ if (limit == null) {
+ return null;
+ }
+ if (limit instanceof BigDecimal) {
+ try {
+ return ((BigDecimal) limit).toBigIntegerExact();
+ } catch (ArithmeticException ignored) {
+ return null;
+ }
+ }
+ if (limit instanceof Number) {
+ return BigInteger.valueOf(((Number) limit).longValue());
+ }
+ return null;
+ }
+
/** Returns whether a type's scale is set. */
public static boolean hasScale(RelDataType type) {
return type.getScale() != Integer.MIN_VALUE;
@@ -717,8 +797,9 @@ public static int getMaxByteSize(RelDataType type) {
/** Returns the minimum unscaled value of a numeric type.
*
- * @param type a numeric type
+ * @deprecated Use {@link #integerBound(RelDataType, boolean)} with {@code
upper = false}
*/
+ @Deprecated // to be removed before 2.0
public static long getMinValue(RelDataType type) {
SqlTypeName typeName = type.getSqlTypeName();
switch (typeName) {
@@ -744,8 +825,9 @@ public static long getMinValue(RelDataType type) {
/** Returns the maximum unscaled value of a numeric type.
* DOES NOT WORK CORRECTLY FOR U/BIGINT and many DECIMAL types.
*
- * @param type a numeric type
+ * @deprecated Use {@link #integerBound(RelDataType, boolean)} with {@code
upper = true}
*/
+ @Deprecated // to be removed before 2.0
public static long getMaxValue(RelDataType type) {
SqlTypeName typeName = type.getSqlTypeName();
switch (typeName) {
diff --git a/core/src/test/java/org/apache/calcite/rex/RexLosslessCastTest.java
b/core/src/test/java/org/apache/calcite/rex/RexLosslessCastTest.java
index 334b3d1ed4..86c13c7427 100644
--- a/core/src/test/java/org/apache/calcite/rex/RexLosslessCastTest.java
+++ b/core/src/test/java/org/apache/calcite/rex/RexLosslessCastTest.java
@@ -16,7 +16,10 @@
*/
package org.apache.calcite.rex;
+import org.apache.calcite.jdbc.JavaTypeFactoryImpl;
import org.apache.calcite.rel.type.RelDataType;
+import org.apache.calcite.rel.type.RelDataTypeSystem;
+import org.apache.calcite.rel.type.RelDataTypeSystemImpl;
import org.apache.calcite.sql.type.SqlTypeName;
import org.junit.jupiter.api.Test;
@@ -34,13 +37,15 @@ class RexLosslessCastTest extends RexProgramTestBase {
final RelDataType smallIntType =
typeFactory.createSqlType(SqlTypeName.SMALLINT);
final RelDataType intType = typeFactory.createSqlType(SqlTypeName.INTEGER);
final RelDataType bigIntType =
typeFactory.createSqlType(SqlTypeName.BIGINT);
- final RelDataType floatType = typeFactory.createSqlType(SqlTypeName.FLOAT);
+ final RelDataType doubleType =
typeFactory.createSqlType(SqlTypeName.DOUBLE);
final RelDataType booleanType =
typeFactory.createSqlType(SqlTypeName.BOOLEAN);
+
final RelDataType charType5 = typeFactory.createSqlType(SqlTypeName.CHAR,
5);
final RelDataType charType6 = typeFactory.createSqlType(SqlTypeName.CHAR,
6);
+
+ final RelDataType varcharType =
typeFactory.createSqlType(SqlTypeName.VARCHAR);
final RelDataType varCharType10 =
typeFactory.createSqlType(SqlTypeName.VARCHAR, 10);
final RelDataType varCharType11 =
typeFactory.createSqlType(SqlTypeName.VARCHAR, 11);
- final RelDataType varcharType =
typeFactory.createSqlType(SqlTypeName.VARCHAR);
// Negative
assertThat(RexUtil.isLosslessCast(rexBuilder.makeInputRef(intType, 0)),
is(false));
@@ -59,7 +64,7 @@ class RexLosslessCastTest extends RexProgramTestBase {
assertThat(
RexUtil.isLosslessCast(
rexBuilder.makeCast(
- bigIntType, rexBuilder.makeInputRef(floatType, 0))),
is(false));
+ bigIntType, rexBuilder.makeInputRef(doubleType, 0))),
is(false));
assertThat(
RexUtil.isLosslessCast(
rexBuilder.makeCast(
@@ -132,22 +137,300 @@ class RexLosslessCastTest extends RexProgramTestBase {
varcharType, rexBuilder.makeInputRef(intType, 0))), is(true));
}
+ @Test void testLosslessCastIntegerToApproximate() {
+ final RelDataType tinyIntType =
typeFactory.createSqlType(SqlTypeName.TINYINT);
+ final RelDataType smallIntType =
typeFactory.createSqlType(SqlTypeName.SMALLINT);
+ final RelDataType intType = typeFactory.createSqlType(SqlTypeName.INTEGER);
+ final RelDataType bigIntType =
typeFactory.createSqlType(SqlTypeName.BIGINT);
+ final RelDataType doubleType =
typeFactory.createSqlType(SqlTypeName.DOUBLE);
+ final RelDataType realType = typeFactory.createSqlType(SqlTypeName.REAL);
+
+ // Positive: tiny/small/int -> double
+ assertThat(
+ RexUtil.isLosslessCast(
+ rexBuilder.makeCast(doubleType,
+ rexBuilder.makeInputRef(tinyIntType, 0))), is(true));
+ assertThat(
+ RexUtil.isLosslessCast(
+ rexBuilder.makeCast(doubleType,
+ rexBuilder.makeInputRef(smallIntType, 0))), is(true));
+ assertThat(
+ RexUtil.isLosslessCast(
+ rexBuilder.makeCast(doubleType,
+ rexBuilder.makeInputRef(intType, 0))), is(true));
+
+ // Negative: bigint -> double can be lossy
+ assertThat(
+ RexUtil.isLosslessCast(
+ rexBuilder.makeCast(doubleType,
+ rexBuilder.makeInputRef(bigIntType, 0))), is(false));
+
+ // Positive: tiny/small -> real
+ assertThat(
+ RexUtil.isLosslessCast(
+ rexBuilder.makeCast(realType,
+ rexBuilder.makeInputRef(tinyIntType, 0))), is(true));
+ assertThat(
+ RexUtil.isLosslessCast(
+ rexBuilder.makeCast(realType,
+ rexBuilder.makeInputRef(smallIntType, 0))), is(true));
+
+ // Negative: int/bigint -> real can be lossy
+ assertThat(
+ RexUtil.isLosslessCast(
+ rexBuilder.makeCast(realType,
+ rexBuilder.makeInputRef(intType, 0))), is(false));
+ assertThat(
+ RexUtil.isLosslessCast(
+ rexBuilder.makeCast(realType,
+ rexBuilder.makeInputRef(bigIntType, 0))), is(false));
+ }
+
+ @Test void testLosslessCastIntegerToDecimal() {
+ final RelDataType tinyIntType =
typeFactory.createSqlType(SqlTypeName.TINYINT);
+ final RelDataType smallIntType =
typeFactory.createSqlType(SqlTypeName.SMALLINT);
+ final RelDataType intType = typeFactory.createSqlType(SqlTypeName.INTEGER);
+ final RelDataType bigIntType =
typeFactory.createSqlType(SqlTypeName.BIGINT);
+
+ final RelDataType dec_3_0 =
typeFactory.createSqlType(SqlTypeName.DECIMAL, 3, 0);
+ final RelDataType dec_5_0 =
typeFactory.createSqlType(SqlTypeName.DECIMAL, 5, 0);
+ final RelDataType dec_9_0 =
typeFactory.createSqlType(SqlTypeName.DECIMAL, 9, 0);
+ final RelDataType dec_10_0 =
typeFactory.createSqlType(SqlTypeName.DECIMAL, 10, 0);
+ final RelDataType dec_18_0 =
typeFactory.createSqlType(SqlTypeName.DECIMAL, 18, 0);
+ final RelDataType dec_19_0 =
typeFactory.createSqlType(SqlTypeName.DECIMAL, 19, 0);
+ final RelDataType dec_4_1 =
typeFactory.createSqlType(SqlTypeName.DECIMAL, 4, 1);
+ final RelDataType dec_5_1 =
typeFactory.createSqlType(SqlTypeName.DECIMAL, 5, 1);
+ final RelDataType dec_12_2 =
typeFactory.createSqlType(SqlTypeName.DECIMAL, 12, 2);
+
+ // Positive: integer digits "precision - scale" is large enough
+ assertThat(
+ RexUtil.isLosslessCast(
+ rexBuilder.makeCast(dec_3_0,
+ rexBuilder.makeInputRef(tinyIntType, 0))), is(true));
+ assertThat(
+ RexUtil.isLosslessCast(
+ rexBuilder.makeCast(dec_5_0,
+ rexBuilder.makeInputRef(smallIntType, 0))), is(true));
+ assertThat(
+ RexUtil.isLosslessCast(
+ rexBuilder.makeCast(dec_10_0,
+ rexBuilder.makeInputRef(intType, 0))), is(true));
+ assertThat(
+ RexUtil.isLosslessCast(
+ rexBuilder.makeCast(dec_19_0,
+ rexBuilder.makeInputRef(bigIntType, 0))), is(true));
+
+ // Negative: not enough integer digits
+ assertThat(
+ RexUtil.isLosslessCast(
+ rexBuilder.makeCast(dec_9_0,
+ rexBuilder.makeInputRef(intType, 0))), is(false));
+ assertThat(
+ RexUtil.isLosslessCast(
+ rexBuilder.makeCast(dec_18_0,
+ rexBuilder.makeInputRef(bigIntType, 0))), is(false));
+
+ // Non-zero scale is still lossless if "precision - scale" covers integer
digits
+ assertThat(
+ RexUtil.isLosslessCast(
+ rexBuilder.makeCast(dec_12_2,
+ rexBuilder.makeInputRef(intType, 0))), is(true));
+ assertThat(
+ RexUtil.isLosslessCast(
+ rexBuilder.makeCast(dec_4_1,
+ rexBuilder.makeInputRef(smallIntType, 0))), is(false));
+ assertThat(
+ RexUtil.isLosslessCast(
+ rexBuilder.makeCast(dec_5_1,
+ rexBuilder.makeInputRef(tinyIntType, 0))), is(true));
+ }
+
+ @Test void testLosslessCastUnsignedAndSignedIntegers() {
+ final RelDataType uTinyIntType =
typeFactory.createSqlType(SqlTypeName.UTINYINT);
+ final RelDataType uIntType =
typeFactory.createSqlType(SqlTypeName.UINTEGER);
+ final RelDataType uBigIntType =
typeFactory.createSqlType(SqlTypeName.UBIGINT);
+
+ final RelDataType tinyIntType =
typeFactory.createSqlType(SqlTypeName.TINYINT);
+ final RelDataType smallIntType =
typeFactory.createSqlType(SqlTypeName.SMALLINT);
+ final RelDataType intType = typeFactory.createSqlType(SqlTypeName.INTEGER);
+ final RelDataType bigIntType =
typeFactory.createSqlType(SqlTypeName.BIGINT);
+
+ // unsigned -> signed of same width should be considered lossy
+ assertThat(
+ RexUtil.isLosslessCast(
+ rexBuilder.makeCast(tinyIntType,
rexBuilder.makeInputRef(uTinyIntType, 0))),
+ is(false));
+ assertThat(
+ RexUtil.isLosslessCast(
+ rexBuilder.makeCast(intType, rexBuilder.makeInputRef(uIntType,
0))),
+ is(false));
+ assertThat(
+ RexUtil.isLosslessCast(
+ rexBuilder.makeCast(bigIntType,
rexBuilder.makeInputRef(uBigIntType, 0))),
+ is(false));
+
+ // unsigned -> wider signed is safe
+ assertThat(
+ RexUtil.isLosslessCast(
+ rexBuilder.makeCast(smallIntType,
rexBuilder.makeInputRef(uTinyIntType, 0))),
+ is(true));
+ assertThat(
+ RexUtil.isLosslessCast(
+ rexBuilder.makeCast(bigIntType, rexBuilder.makeInputRef(uIntType,
0))),
+ is(true));
+
+ // signed -> unsigned can be lossy
+ assertThat(
+ RexUtil.isLosslessCast(
+ rexBuilder.makeCast(uIntType, rexBuilder.makeInputRef(intType,
0))),
+ is(false));
+ assertThat(
+ RexUtil.isLosslessCast(
+ rexBuilder.makeCast(uBigIntType,
rexBuilder.makeInputRef(bigIntType, 0))),
+ is(false));
+
+ final RelDataType dec_9_0 = typeFactory.createSqlType(SqlTypeName.DECIMAL,
9, 0);
+ final RelDataType dec_10_0 =
typeFactory.createSqlType(SqlTypeName.DECIMAL, 10, 0);
+ final RelDataType dec_10_1 =
typeFactory.createSqlType(SqlTypeName.DECIMAL, 10, 1);
+ final RelDataType dec19_0 = typeFactory.createSqlType(SqlTypeName.DECIMAL,
19, 0);
+
+ assertThat(
+ RexUtil.isLosslessCast(
+ rexBuilder.makeCast(dec_10_0, rexBuilder.makeInputRef(uIntType,
0))),
+ is(true));
+ assertThat(
+ RexUtil.isLosslessCast(
+ rexBuilder.makeCast(dec_9_0, rexBuilder.makeInputRef(uIntType,
0))),
+ is(false));
+ assertThat(
+ RexUtil.isLosslessCast(
+ rexBuilder.makeCast(uBigIntType, rexBuilder.makeInputRef(dec19_0,
0))),
+ is(false));
+
+ // DECIMAL -> integer: requires scale 0 and fitting range
+ assertThat(
+ RexUtil.isLosslessCast(
+ rexBuilder.makeCast(intType, rexBuilder.makeInputRef(dec_9_0, 0))),
+ is(true));
+ assertThat(
+ RexUtil.isLosslessCast(
+ rexBuilder.makeCast(intType, rexBuilder.makeInputRef(dec_10_0,
0))),
+ is(false));
+ assertThat(
+ RexUtil.isLosslessCast(
+ rexBuilder.makeCast(intType, rexBuilder.makeInputRef(dec_10_1,
0))),
+ is(false));
+ }
+
+ // DECIMAL(s = 0) -> APPROX: lossless iff precision <= target digits
+ @Test void testLosslessCastDecimalToApproximate() {
+ final RelDataType realType = typeFactory.createSqlType(SqlTypeName.REAL);
+ final RelDataType doubleType =
typeFactory.createSqlType(SqlTypeName.DOUBLE);
+
+ final RelDataType dec_7_0 = typeFactory.createSqlType(SqlTypeName.DECIMAL,
7, 0);
+ final RelDataType dec_8_0 = typeFactory.createSqlType(SqlTypeName.DECIMAL,
8, 0);
+ final RelDataType dec_15_0 =
typeFactory.createSqlType(SqlTypeName.DECIMAL, 15, 0);
+ final RelDataType dec_16_0 =
typeFactory.createSqlType(SqlTypeName.DECIMAL, 16, 0);
+
+ // real: 7 digits in default type system
+ assertThat(
+ RexUtil.isLosslessCast(
+ rexBuilder.makeCast(realType, rexBuilder.makeInputRef(dec_7_0, 0))),
is(true));
+ assertThat(
+ RexUtil.isLosslessCast(
+ rexBuilder.makeCast(realType, rexBuilder.makeInputRef(dec_8_0, 0))),
is(false));
+
+ // double: 15 digits in default type system
+ assertThat(
+ RexUtil.isLosslessCast(
+ rexBuilder.makeCast(doubleType, rexBuilder.makeInputRef(dec_15_0,
0))), is(true));
+ assertThat(
+ RexUtil.isLosslessCast(
+ rexBuilder.makeCast(doubleType, rexBuilder.makeInputRef(dec_16_0,
0))), is(false));
+
+ // DECIMAL with scale > 0 -> APPROX is potentially lossy
+ final RelDataType dec_10_2 =
typeFactory.createSqlType(SqlTypeName.DECIMAL, 10, 2);
+ assertThat(
+ RexUtil.isLosslessCast(
+ rexBuilder.makeCast(doubleType, rexBuilder.makeInputRef(dec_10_2,
0))), is(false));
+ }
+
+ @Test void testLosslessCastApproximateToApproximate() {
+ final RelDataType realType = typeFactory.createSqlType(SqlTypeName.REAL);
+ final RelDataType doubleType =
typeFactory.createSqlType(SqlTypeName.DOUBLE);
+
+ // real -> double: target has >= digits
+ assertThat(
+ RexUtil.isLosslessCast(
+ rexBuilder.makeCast(doubleType, rexBuilder.makeInputRef(realType,
0))), is(true));
+ // double -> real: target has fewer digits
+ assertThat(
+ RexUtil.isLosslessCast(
+ rexBuilder.makeCast(realType, rexBuilder.makeInputRef(doubleType,
0))), is(false));
+ }
+
+ @Test void testLosslessCastApproximateToExact() {
+ final RelDataType realType = typeFactory.createSqlType(SqlTypeName.REAL);
+ final RelDataType doubleType =
typeFactory.createSqlType(SqlTypeName.DOUBLE);
+ final RelDataType intType = typeFactory.createSqlType(SqlTypeName.INTEGER);
+ final RelDataType dec_19_0 =
typeFactory.createSqlType(SqlTypeName.DECIMAL, 19, 0);
+
+ // approx -> exact can be lossy
+ assertThat(
+ RexUtil.isLosslessCast(
+ rexBuilder.makeCast(intType, rexBuilder.makeInputRef(realType, 0))),
is(false));
+ assertThat(
+ RexUtil.isLosslessCast(
+ rexBuilder.makeCast(dec_19_0, rexBuilder.makeInputRef(doubleType,
0))), is(false));
+ }
+
+ @Test void testLosslessCastWithCustomTypeSystem() {
+ final RelDataType dec_10_0 =
typeFactory.createSqlType(SqlTypeName.DECIMAL, 10, 0);
+ final RelDataType decDefault =
typeFactory.createSqlType(SqlTypeName.DECIMAL);
+ assertThat(
+ RexUtil.isLosslessCast(
+ rexBuilder.makeCast(
+ dec_10_0, rexBuilder.makeInputRef(decDefault, 0))), is(false));
+
+ RelDataTypeSystem customTypeSystem = new RelDataTypeSystemImpl() {
+ @Override public int getDefaultPrecision(SqlTypeName typeName) {
+ if (typeName == SqlTypeName.DECIMAL) {
+ return 9;
+ }
+ return super.getDefaultPrecision(typeName);
+ }
+ };
+ final JavaTypeFactoryImpl customFactory = new
JavaTypeFactoryImpl(customTypeSystem);
+ final RexBuilder customBuilder = new RexBuilder(customFactory);
+
+ final RelDataType customDec_10_0 =
customFactory.createSqlType(SqlTypeName.DECIMAL, 10, 0);
+ final RelDataType customDecDefault =
customFactory.createSqlType(SqlTypeName.DECIMAL);
+
+ // custom target has 9 integer digits, so decimal with 10 won't fit
+ assertThat(
+ RexUtil.isLosslessCast(
+ customBuilder.makeCast(
+ customDecDefault, customBuilder.makeInputRef(customDec_10_0, 0))),
is(false));
+ }
+
@Test void removeRedundantCast() {
checkSimplify(cast(vInt(), nullable(tInt())), "?0.int0");
checkSimplifyUnchanged(cast(vInt(), tInt()));
checkSimplify(cast(vIntNotNull(), nullable(tInt())), "?0.notNullInt0");
checkSimplify(cast(vIntNotNull(), tInt()), "?0.notNullInt0");
- // Nested int int cast is removed
+ // Nested int cast is removed
checkSimplify(cast(cast(vVarchar(), tInt()), tInt()),
"CAST(?0.varchar0):INTEGER NOT NULL");
checkSimplifyUnchanged(cast(cast(vVarchar(), tInt()), tVarchar()));
}
- @Test void removeLosslesssCastInt() {
+ @Test void removeLosslessCastInt() {
checkSimplifyUnchanged(cast(vInt(), tBigInt()));
- // A.1
+ checkSimplifyUnchanged(cast(vInt(), tDouble()));
+ // A.1 INT -> BIGINT
checkSimplify(cast(cast(vInt(), tBigInt()), tInt()),
"CAST(?0.int0):INTEGER NOT NULL");
+ checkSimplify(cast(cast(vInt(), tDouble()), tInt()),
"CAST(?0.int0):INTEGER NOT NULL");
RexNode core = cast(vIntNotNull(), tBigInt());
checkSimplify(cast(core, tInt()), "?0.notNullInt0");
checkSimplify(
@@ -156,10 +439,38 @@ class RexLosslessCastTest extends RexProgramTestBase {
checkSimplify(
cast(cast(cast(core, tInt()), tBigInt()), tInt()),
"?0.notNullInt0");
+ // A.1 INT -> DOUBLE
+ core = cast(vIntNotNull(), tDouble());
+ checkSimplify(cast(core, tInt()), "?0.notNullInt0");
+ checkSimplify(
+ cast(cast(core, tInt()), tDouble()),
+ "CAST(?0.notNullInt0):DOUBLE NOT NULL");
+ checkSimplify(
+ cast(cast(cast(core, tInt()), tDouble()), tInt()),
+ "?0.notNullInt0");
+ // A.1 INT -> DECIMAL
+ core = cast(vIntNotNull(), tDecimal());
+ checkSimplify(cast(core, tInt()), "?0.notNullInt0");
+ checkSimplify(
+ cast(cast(core, tInt()), tDecimal()),
+ "CAST(?0.notNullInt0):DECIMAL(19, 0) NOT NULL");
+ checkSimplify(
+ cast(cast(cast(core, tInt()), tDecimal()), tInt()),
+ "?0.notNullInt0");
+ // A.1 SMALLINT -> REAL
+ core = cast(vSmallIntNotNull(), tReal());
+ checkSimplify(cast(core, tSmallInt()), "?0.notNullSmallint0");
+ checkSimplify(
+ cast(cast(core, tSmallInt()), tReal()),
+ "CAST(?0.notNullSmallint0):REAL NOT NULL");
+ checkSimplify(
+ cast(cast(cast(core, tSmallInt()), tReal()), tSmallInt()),
+ "?0.notNullSmallint0");
+ // A.1 INT -> VARCHAR
checkSimplify(cast(cast(vInt(), tVarchar()), tInt()),
"CAST(?0.int0):INTEGER NOT NULL");
}
- @Test void removeLosslesssCastChar() {
+ @Test void removeLosslessCastChar() {
checkSimplifyUnchanged(cast(vVarchar(), tChar(3)));
checkSimplifyUnchanged(cast(cast(vVarchar(), tChar(3)), tVarchar(5)));
diff --git
a/core/src/test/java/org/apache/calcite/rex/RexProgramBuilderBase.java
b/core/src/test/java/org/apache/calcite/rex/RexProgramBuilderBase.java
index 6051e75801..5c451ca8e5 100644
--- a/core/src/test/java/org/apache/calcite/rex/RexProgramBuilderBase.java
+++ b/core/src/test/java/org/apache/calcite/rex/RexProgramBuilderBase.java
@@ -62,6 +62,8 @@ public abstract class RexProgramBuilderBase {
protected RexLiteral nullSmallInt;
protected RexLiteral nullVarchar;
protected RexLiteral nullDecimal;
+ protected RexLiteral nullReal;
+ protected RexLiteral nullDouble;
protected RexLiteral nullVarbinary;
private RelDataType nullableBool;
@@ -79,6 +81,12 @@ public abstract class RexProgramBuilderBase {
private RelDataType nullableDecimal;
private RelDataType nonNullableDecimal;
+ private RelDataType nullableReal;
+ private RelDataType nonNullableReal;
+
+ private RelDataType nullableDouble;
+ private RelDataType nonNullableDouble;
+
private RelDataType nullableVarbinary;
private RelDataType nonNullableVarbinary;
@@ -123,6 +131,14 @@ public abstract class RexProgramBuilderBase {
nullableDecimal =
typeFactory.createTypeWithNullability(nonNullableDecimal, true);
nullDecimal = rexBuilder.makeNullLiteral(nullableDecimal);
+ nonNullableReal = typeFactory.createSqlType(SqlTypeName.REAL);
+ nullableReal = typeFactory.createTypeWithNullability(nonNullableReal,
true);
+ nullReal = rexBuilder.makeNullLiteral(nullableReal);
+
+ nonNullableDouble = typeFactory.createSqlType(SqlTypeName.DOUBLE);
+ nullableDouble = typeFactory.createTypeWithNullability(nonNullableDouble,
true);
+ nullDouble = rexBuilder.makeNullLiteral(nullableDouble);
+
nonNullableVarbinary = typeFactory.createSqlType(SqlTypeName.VARBINARY);
nullableVarbinary =
typeFactory.createTypeWithNullability(nonNullableVarbinary, true);
nullVarbinary = rexBuilder.makeNullLiteral(nullableVarbinary);
@@ -425,13 +441,29 @@ protected RelDataType tSmallInt(boolean nullable) {
}
protected RelDataType tDecimal() {
- return nonNullableDecimal;
+ return tDecimal(false);
}
protected RelDataType tDecimal(boolean nullable) {
return nullable ? nullableDecimal : nonNullableDecimal;
}
+ protected RelDataType tReal() {
+ return tReal(false);
+ }
+
+ protected RelDataType tReal(boolean nullable) {
+ return nullable ? nullableReal : nonNullableReal;
+ }
+
+ protected RelDataType tDouble() {
+ return tDouble(false);
+ }
+
+ protected RelDataType tDouble(boolean nullable) {
+ return nullable ? nullableDouble : nonNullableDouble;
+ }
+
protected RelDataType tBigInt() {
return tBigInt(false);
}
diff --git
a/core/src/test/java/org/apache/calcite/sql/type/SqlTypeUtilTest.java
b/core/src/test/java/org/apache/calcite/sql/type/SqlTypeUtilTest.java
index 11eb8e0e55..1f08bd232e 100644
--- a/core/src/test/java/org/apache/calcite/sql/type/SqlTypeUtilTest.java
+++ b/core/src/test/java/org/apache/calcite/sql/type/SqlTypeUtilTest.java
@@ -29,6 +29,7 @@
import org.hamcrest.Matcher;
import org.junit.jupiter.api.Test;
+import java.math.BigInteger;
import java.util.List;
import java.util.Locale;
import java.util.stream.Collectors;
@@ -37,10 +38,13 @@
import static org.apache.calcite.sql.type.SqlTypeUtil.convertTypeToSpec;
import static
org.apache.calcite.sql.type.SqlTypeUtil.equalAsCollectionSansNullability;
import static
org.apache.calcite.sql.type.SqlTypeUtil.equalAsMapSansNullability;
+import static org.apache.calcite.sql.type.SqlTypeUtil.integerBound;
+import static org.apache.calcite.sql.type.SqlTypeUtil.integerRangeContains;
import static org.apache.calcite.test.Matchers.isListOf;
import static org.hamcrest.CoreMatchers.is;
import static org.hamcrest.MatcherAssert.assertThat;
+import static org.junit.jupiter.api.Assertions.assertThrows;
/**
* Test of {@link org.apache.calcite.sql.type.SqlTypeUtil}.
@@ -110,6 +114,14 @@ class SqlTypeUtilTest {
is(false));
}
+ private RelDataType struct(RelDataType...relDataTypes) {
+ final RelDataTypeFactory.Builder builder = f.typeFactory.builder();
+ for (int i = 0; i < relDataTypes.length; i++) {
+ builder.add("field" + i, relDataTypes[i]);
+ }
+ return builder.build();
+ }
+
@Test void testModifyTypeCoercionMappings() {
SqlTypeMappingRules.Builder builder = SqlTypeMappingRules.builder();
final SqlTypeCoercionRule defaultRules = SqlTypeCoercionRule.instance();
@@ -208,18 +220,47 @@ private void checkConvert(SqlTypeCoercionRule rules,
sqlTypeName, is(SqlTypeName.VARCHAR));
}
- @Test void testGetMaxPrecisionScaleDecimal() {
- RelDataType decimal =
SqlTypeUtil.getMaxPrecisionScaleDecimal(f.typeFactory);
- assertThat(decimal, is(f.typeFactory.createSqlType(SqlTypeName.DECIMAL,
19, 9)));
+ @Test void testIntegerRangeContainsForSignedAndUnsigned() {
+ RelDataType uTinyInt = f.typeFactory.createSqlType(SqlTypeName.UTINYINT);
+ RelDataType tinyInt = f.typeFactory.createSqlType(SqlTypeName.TINYINT);
+ RelDataType smallInt = f.typeFactory.createSqlType(SqlTypeName.SMALLINT);
+ RelDataType intType = f.typeFactory.createSqlType(SqlTypeName.INTEGER);
+
+ assertThat(integerRangeContains(intType, smallInt), is(true));
+ assertThat(integerRangeContains(intType, tinyInt), is(true));
+ assertThat(integerRangeContains(intType, uTinyInt), is(true));
+ assertThat(integerRangeContains(tinyInt, uTinyInt), is(false));
+ assertThrows(IllegalArgumentException.class,
+ () -> integerRangeContains(f.sqlVarchar, intType));
}
+ @Test void testIntegerRangeContainsWithDecimal() {
+ RelDataType intType = f.typeFactory.createSqlType(SqlTypeName.INTEGER);
+ RelDataType dec9 = f.typeFactory.createSqlType(SqlTypeName.DECIMAL, 9, 0);
+ RelDataType dec10 = f.typeFactory.createSqlType(SqlTypeName.DECIMAL, 10,
0);
+ RelDataType dec10Scale1 = f.typeFactory.createSqlType(SqlTypeName.DECIMAL,
10, 1);
- private RelDataType struct(RelDataType...relDataTypes) {
- final RelDataTypeFactory.Builder builder = f.typeFactory.builder();
- for (int i = 0; i < relDataTypes.length; i++) {
- builder.add("field" + i, relDataTypes[i]);
- }
- return builder.build();
+ assertThat(integerRangeContains(intType, dec9), is(true));
+ assertThat(integerRangeContains(intType, dec10), is(false));
+ assertThat(integerRangeContains(intType, dec10Scale1), is(false));
+ }
+
+ @Test void testIntegerBound() {
+ RelDataType intType = f.typeFactory.createSqlType(SqlTypeName.INTEGER);
+ RelDataType uIntType = f.typeFactory.createSqlType(SqlTypeName.UINTEGER);
+ RelDataType dec10 = f.typeFactory.createSqlType(SqlTypeName.DECIMAL, 10,
0);
+ RelDataType dec10Scale1 = f.typeFactory.createSqlType(SqlTypeName.DECIMAL,
10, 1);
+
+ assertThat(integerBound(intType, false),
is(BigInteger.valueOf(Integer.MIN_VALUE)));
+ assertThat(integerBound(intType, true),
is(BigInteger.valueOf(Integer.MAX_VALUE)));
+ assertThat(integerBound(uIntType, true), is(BigInteger.valueOf((1L << 32)
- 1)));
+ assertThat(integerBound(dec10, true),
is(BigInteger.valueOf(9_999_999_999L)));
+ assertThat(integerBound(dec10Scale1, true), is((BigInteger) null));
+ }
+
+ @Test void testGetMaxPrecisionScaleDecimal() {
+ RelDataType decimal =
SqlTypeUtil.getMaxPrecisionScaleDecimal(f.typeFactory);
+ assertThat(decimal, is(f.typeFactory.createSqlType(SqlTypeName.DECIMAL,
19, 9)));
}
private void compareTypesIgnoringNullability(
diff --git a/core/src/test/resources/sql/planner.iq
b/core/src/test/resources/sql/planner.iq
index 0e9fd43baf..e9e48f6c7a 100644
--- a/core/src/test/resources/sql/planner.iq
+++ b/core/src/test/resources/sql/planner.iq
@@ -166,15 +166,16 @@ select a from (values (1.0), (4.0), (null)) as t3 (a);
!ok
-EnumerableCalc(expr#0=[{inputs}], expr#1=[CAST($t0):DECIMAL(11, 1)], A=[$t1])
- EnumerableNestedLoopJoin(condition=[OR(AND(IS NULL(CAST($0):DECIMAL(11, 1)),
IS NULL(CAST($1):DECIMAL(11, 1))), =(CAST($0):DECIMAL(11, 1),
CAST($1):DECIMAL(11, 1)))], joinType=[semi])
- EnumerableAggregate(group=[{0}])
- EnumerableHashJoin(condition=[=($1, $3)], joinType=[semi])
- EnumerableCalc(expr#0=[{inputs}], expr#1=[CAST($t0):DECIMAL(11, 1) NOT
NULL], expr#2=[CAST($t1):DECIMAL(11, 1) NOT NULL], A=[$t1], A0=[$t2])
- EnumerableValues(tuples=[[{ 1.0 }, { 2.0 }, { 3.0 }, { 4.0 }, { 5.0
}]])
- EnumerableCalc(expr#0=[{inputs}], expr#1=[CAST($t0):DECIMAL(11, 1) NOT
NULL], expr#2=[CAST($t1):DECIMAL(11, 1) NOT NULL], A=[$t1], A0=[$t2])
- EnumerableValues(tuples=[[{ 1 }, { 2 }]])
- EnumerableCalc(expr#0=[{inputs}], expr#1=[CAST($t0):DECIMAL(11, 1)],
A=[$t1])
+EnumerableCalc(expr#0..1=[{inputs}], expr#2=[CAST($t0):DECIMAL(11, 1)],
A=[$t2])
+ EnumerableHashJoin(condition=[=($1, $3)], joinType=[semi])
+ EnumerableCalc(expr#0=[{inputs}], expr#1=[CAST($t0):DECIMAL(11, 1)],
proj#0..1=[{exprs}])
+ EnumerableAggregate(group=[{0}])
+ EnumerableHashJoin(condition=[=($1, $3)], joinType=[semi])
+ EnumerableCalc(expr#0=[{inputs}], expr#1=[CAST($t0):DECIMAL(11, 1)
NOT NULL], A=[$t1], A0=[$t1])
+ EnumerableValues(tuples=[[{ 1.0 }, { 2.0 }, { 3.0 }, { 4.0 }, {
5.0 }]])
+ EnumerableCalc(expr#0=[{inputs}], expr#1=[CAST($t0):DECIMAL(11, 1)
NOT NULL], A=[$t1], A0=[$t1])
+ EnumerableValues(tuples=[[{ 1 }, { 2 }]])
+ EnumerableCalc(expr#0=[{inputs}], expr#1=[CAST($t0):DECIMAL(11, 1)],
A=[$t1], A0=[$t1])
EnumerableValues(tuples=[[{ 1.0 }, { 4.0 }, { null }]])
!plan
!set planner-rules original
@@ -223,15 +224,16 @@ select a from (values (1.0), (4.0), (null)) as t3 (a);
!ok
-EnumerableCalc(expr#0=[{inputs}], expr#1=[CAST($t0):DECIMAL(11, 1)], A=[$t1])
- EnumerableNestedLoopJoin(condition=[OR(AND(IS NULL(CAST($0):DECIMAL(11, 1)),
IS NULL(CAST($1):DECIMAL(11, 1))), =(CAST($0):DECIMAL(11, 1),
CAST($1):DECIMAL(11, 1)))], joinType=[anti])
- EnumerableAggregate(group=[{0}])
- EnumerableNestedLoopJoin(condition=[=(CAST($0):DECIMAL(11, 1) NOT NULL,
CAST($1):DECIMAL(11, 1) NOT NULL)], joinType=[anti])
- EnumerableCalc(expr#0=[{inputs}], expr#1=[CAST($t0):DECIMAL(11, 1) NOT
NULL], A=[$t1])
- EnumerableValues(tuples=[[{ 1.0 }, { 2.0 }, { 3.0 }, { 4.0 }, { 5.0
}]])
- EnumerableCalc(expr#0=[{inputs}], expr#1=[CAST($t0):DECIMAL(11, 1) NOT
NULL], A=[$t1])
- EnumerableValues(tuples=[[{ 1 }, { 2 }]])
- EnumerableCalc(expr#0=[{inputs}], expr#1=[CAST($t0):DECIMAL(11, 1)],
A=[$t1])
+EnumerableCalc(expr#0..1=[{inputs}], expr#2=[CAST($t0):DECIMAL(11, 1)],
A=[$t2])
+ EnumerableHashJoin(condition=[=($1, $3)], joinType=[anti])
+ EnumerableCalc(expr#0=[{inputs}], expr#1=[CAST($t0):DECIMAL(11, 1)],
proj#0..1=[{exprs}])
+ EnumerableAggregate(group=[{0}])
+ EnumerableNestedLoopJoin(condition=[=(CAST($0):DECIMAL(11, 1) NOT
NULL, CAST($1):DECIMAL(11, 1) NOT NULL)], joinType=[anti])
+ EnumerableCalc(expr#0=[{inputs}], expr#1=[CAST($t0):DECIMAL(11, 1)
NOT NULL], A=[$t1])
+ EnumerableValues(tuples=[[{ 1.0 }, { 2.0 }, { 3.0 }, { 4.0 }, {
5.0 }]])
+ EnumerableCalc(expr#0=[{inputs}], expr#1=[CAST($t0):DECIMAL(11, 1)
NOT NULL], A=[$t1])
+ EnumerableValues(tuples=[[{ 1 }, { 2 }]])
+ EnumerableCalc(expr#0=[{inputs}], expr#1=[CAST($t0):DECIMAL(11, 1)],
A=[$t1], A0=[$t1])
EnumerableValues(tuples=[[{ 1.0 }, { 4.0 }, { null }]])
!plan
!set planner-rules original
diff --git a/core/src/test/resources/sql/sub-query.iq
b/core/src/test/resources/sql/sub-query.iq
index 330e3d990b..f673e4b322 100644
--- a/core/src/test/resources/sql/sub-query.iq
+++ b/core/src/test/resources/sql/sub-query.iq
@@ -3678,7 +3678,7 @@ select * from "scott".emp where comm in (300, 500, null);
!ok
-EnumerableCalc(expr#0..7=[{inputs}], expr#8=[CAST($t6):DECIMAL(12, 2)],
expr#9=[Sarg[300.00:DECIMAL(12, 2), 500.00:DECIMAL(12, 2)]:DECIMAL(12, 2)],
expr#10=[SEARCH($t8, $t9)], expr#11=[null:DECIMAL(12, 2)], expr#12=[=($t8,
$t11)], expr#13=[OR($t10, $t12)], proj#0..7=[{exprs}], $condition=[$t13])
+EnumerableCalc(expr#0..7=[{inputs}], expr#8=[CAST($t6):DECIMAL(12, 2)],
expr#9=[Sarg[300.00:DECIMAL(12, 2), 500.00:DECIMAL(12, 2)]:DECIMAL(12, 2)],
expr#10=[SEARCH($t8, $t9)], proj#0..7=[{exprs}], $condition=[$t10])
EnumerableTableScan(table=[[scott, EMP]])
!plan
@@ -3706,7 +3706,7 @@ select *, comm in (300, 500, null) as i from "scott".emp;
!ok
-EnumerableCalc(expr#0..7=[{inputs}], expr#8=[CAST($t6):DECIMAL(12, 2)],
expr#9=[Sarg[300.00:DECIMAL(12, 2), 500.00:DECIMAL(12, 2)]:DECIMAL(12, 2)],
expr#10=[SEARCH($t8, $t9)], expr#11=[null:DECIMAL(12, 2)], expr#12=[=($t8,
$t11)], expr#13=[OR($t10, $t12)], proj#0..7=[{exprs}], I=[$t13])
+EnumerableCalc(expr#0..7=[{inputs}], expr#8=[CAST($t6):DECIMAL(12, 2)],
expr#9=[Sarg[300.00:DECIMAL(12, 2), 500.00:DECIMAL(12, 2)]:DECIMAL(12, 2)],
expr#10=[SEARCH($t8, $t9)], expr#11=[null:BOOLEAN], expr#12=[OR($t10, $t11)],
proj#0..7=[{exprs}], I=[$t12])
EnumerableTableScan(table=[[scott, EMP]])
!plan
@@ -3747,7 +3747,7 @@ select *, comm not in (300, 500, null) as i from
"scott".emp;
!ok
-EnumerableCalc(expr#0..7=[{inputs}], expr#8=[CAST($t6):DECIMAL(12, 2)],
expr#9=[Sarg[(-∞..300.00:DECIMAL(12, 2)), (300.00:DECIMAL(12,
2)..500.00:DECIMAL(12, 2)), (500.00:DECIMAL(12, 2)..+∞)]:DECIMAL(12, 2)],
expr#10=[SEARCH($t8, $t9)], expr#11=[null:DECIMAL(12, 2)], expr#12=[<>($t8,
$t11)], expr#13=[AND($t10, $t12)], proj#0..7=[{exprs}], I=[$t13])
+EnumerableCalc(expr#0..7=[{inputs}], expr#8=[CAST($t6):DECIMAL(12, 2)],
expr#9=[Sarg[(-∞..300.00:DECIMAL(12, 2)), (300.00:DECIMAL(12,
2)..500.00:DECIMAL(12, 2)), (500.00:DECIMAL(12, 2)..+∞)]:DECIMAL(12, 2)],
expr#10=[SEARCH($t8, $t9)], expr#11=[null:BOOLEAN], expr#12=[AND($t10, $t11)],
proj#0..7=[{exprs}], I=[$t12])
EnumerableTableScan(table=[[scott, EMP]])
!plan
@@ -4627,7 +4627,7 @@ select comm, comm in (500, 300, 0) from emp;
!ok
-EnumerableCalc(expr#0..6=[{inputs}], expr#7=[0], expr#8=[=($t2, $t7)],
expr#9=[false], expr#10=[CAST($t1):DECIMAL(12, 2)], expr#11=[IS NULL($t10)],
expr#12=[null:BOOLEAN], expr#13=[IS NOT NULL($t6)], expr#14=[true],
expr#15=[<($t3, $t2)], expr#16=[CASE($t8, $t9, $t11, $t12, $t13, $t14, $t15,
$t12, $t9)], COMM=[$t1], EXPR$1=[$t16])
+EnumerableCalc(expr#0..6=[{inputs}], expr#7=[IS NULL($t1)],
expr#8=[null:BOOLEAN], expr#9=[0], expr#10=[<>($t2, $t9)], expr#11=[AND($t7,
$t8, $t10)], expr#12=[IS NOT NULL($t6)], expr#13=[IS NOT NULL($t1)],
expr#14=[AND($t12, $t10, $t13)], expr#15=[<($t3, $t2)], expr#16=[IS NULL($t6)],
expr#17=[AND($t15, $t8, $t10, $t13, $t16)], expr#18=[OR($t11, $t14, $t17)],
COMM=[$t1], EXPR$1=[$t18])
EnumerableMergeJoin(condition=[=($4, $5)], joinType=[left])
EnumerableSort(sort0=[$4], dir0=[ASC])
EnumerableCalc(expr#0..3=[{inputs}], expr#4=[CAST($t1):DECIMAL(12, 2)],
proj#0..4=[{exprs}])
@@ -4667,7 +4667,7 @@ select comm, comm in (500, 300, 0, null) from emp;
!ok
-EnumerableCalc(expr#0..6=[{inputs}], expr#7=[0], expr#8=[=($t2, $t7)],
expr#9=[false], expr#10=[CAST($t1):DECIMAL(12, 2)], expr#11=[IS NULL($t10)],
expr#12=[null:BOOLEAN], expr#13=[IS NOT NULL($t6)], expr#14=[true],
expr#15=[<($t3, $t2)], expr#16=[CASE($t8, $t9, $t11, $t12, $t13, $t14, $t15,
$t12, $t9)], COMM=[$t1], EXPR$1=[$t16])
+EnumerableCalc(expr#0..6=[{inputs}], expr#7=[IS NULL($t1)],
expr#8=[null:BOOLEAN], expr#9=[0], expr#10=[<>($t2, $t9)], expr#11=[AND($t7,
$t8, $t10)], expr#12=[IS NOT NULL($t6)], expr#13=[IS NOT NULL($t1)],
expr#14=[AND($t12, $t10, $t13)], expr#15=[<($t3, $t2)], expr#16=[IS NULL($t6)],
expr#17=[AND($t15, $t8, $t10, $t13, $t16)], expr#18=[OR($t11, $t14, $t17)],
COMM=[$t1], EXPR$1=[$t18])
EnumerableMergeJoin(condition=[=($4, $5)], joinType=[left])
EnumerableSort(sort0=[$4], dir0=[ASC])
EnumerableCalc(expr#0..3=[{inputs}], expr#4=[CAST($t1):DECIMAL(12, 2)],
proj#0..4=[{exprs}])
@@ -4740,7 +4740,7 @@ select comm, (comm, comm) in ((500, 500), (300, 300), (0,
0)) from emp;
!ok
-EnumerableCalc(expr#0..8=[{inputs}], expr#9=[0], expr#10=[=($t2, $t9)],
expr#11=[false], expr#12=[CAST($t1):DECIMAL(12, 2)], expr#13=[IS NULL($t12)],
expr#14=[null:BOOLEAN], expr#15=[IS NOT NULL($t8)], expr#16=[true],
expr#17=[<($t3, $t2)], expr#18=[CASE($t10, $t11, $t13, $t14, $t15, $t16, $t17,
$t14, $t11)], COMM=[$t1], EXPR$1=[$t18])
+EnumerableCalc(expr#0..8=[{inputs}], expr#9=[IS NULL($t1)],
expr#10=[null:BOOLEAN], expr#11=[0], expr#12=[<>($t2, $t11)], expr#13=[AND($t9,
$t10, $t12)], expr#14=[IS NOT NULL($t8)], expr#15=[IS NOT NULL($t1)],
expr#16=[AND($t14, $t12, $t15)], expr#17=[<($t3, $t2)], expr#18=[IS NULL($t8)],
expr#19=[AND($t17, $t10, $t12, $t15, $t18)], expr#20=[OR($t13, $t16, $t19)],
COMM=[$t1], EXPR$1=[$t20])
EnumerableMergeJoin(condition=[AND(=($4, $6), =($5, $7))], joinType=[left])
EnumerableSort(sort0=[$4], sort1=[$5], dir0=[ASC], dir1=[ASC])
EnumerableCalc(expr#0..3=[{inputs}], expr#4=[CAST($t1):DECIMAL(12, 2)],
proj#0..4=[{exprs}], COMM1=[$t4])
@@ -4779,7 +4779,7 @@ select comm, (comm, comm) in ((500, 500), (300, 300), (0,
0), (null , null)) fro
!ok
-EnumerableCalc(expr#0..8=[{inputs}], expr#9=[0], expr#10=[=($t2, $t9)],
expr#11=[false], expr#12=[CAST($t1):DECIMAL(12, 2)], expr#13=[IS NULL($t12)],
expr#14=[null:BOOLEAN], expr#15=[IS NOT NULL($t8)], expr#16=[true],
expr#17=[<($t3, $t2)], expr#18=[CASE($t10, $t11, $t13, $t14, $t15, $t16, $t17,
$t14, $t11)], COMM=[$t1], EXPR$1=[$t18])
+EnumerableCalc(expr#0..8=[{inputs}], expr#9=[IS NULL($t1)],
expr#10=[null:BOOLEAN], expr#11=[0], expr#12=[<>($t2, $t11)], expr#13=[AND($t9,
$t10, $t12)], expr#14=[IS NOT NULL($t8)], expr#15=[IS NOT NULL($t1)],
expr#16=[AND($t14, $t12, $t15)], expr#17=[<($t3, $t2)], expr#18=[IS NULL($t8)],
expr#19=[AND($t17, $t10, $t12, $t15, $t18)], expr#20=[OR($t13, $t16, $t19)],
COMM=[$t1], EXPR$1=[$t20])
EnumerableMergeJoin(condition=[AND(=($4, $6), =($5, $7))], joinType=[left])
EnumerableSort(sort0=[$4], sort1=[$5], dir0=[ASC], dir1=[ASC])
EnumerableCalc(expr#0..3=[{inputs}], expr#4=[CAST($t1):DECIMAL(12, 2)],
proj#0..4=[{exprs}], COMM1=[$t4])
