This is an automated email from the ASF dual-hosted git repository.
mbudiu 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 052a5f8bf8 [CALCITE-6617] TypeCoercion is not applied correctly to
comparisons
052a5f8bf8 is described below
commit 052a5f8bf8abc12d0804071982b36189067c4453
Author: Mihai Budiu <[email protected]>
AuthorDate: Tue Oct 8 20:15:53 2024 -0700
[CALCITE-6617] TypeCoercion is not applied correctly to comparisons
Signed-off-by: Mihai Budiu <[email protected]>
---
.../calcite/adapter/arrow/ArrowAdapterTest.java | 6 +-
.../validate/implicit/AbstractTypeCoercion.java | 64 +++++++++-
.../sql/validate/implicit/TypeCoercion.java | 3 +-
.../sql/validate/implicit/TypeCoercionImpl.java | 14 +--
.../calcite/rel/rel2sql/RelToSqlConverterTest.java | 38 +++---
.../org/apache/calcite/test/RelOptRulesTest.java | 9 ++
.../org/apache/calcite/test/TCatalogReader.java | 4 +-
.../calcite/test/TypeCoercionConverterTest.java | 6 +-
.../org/apache/calcite/test/TypeCoercionTest.java | 132 ++++++++++++++++-----
.../org/apache/calcite/test/RelOptRulesTest.xml | 28 ++++-
.../calcite/test/TypeCoercionConverterTest.xml | 12 +-
core/src/test/resources/sql/agg.iq | 2 +-
core/src/test/resources/sql/sub-query.iq | 80 +++++++++----
.../org/apache/calcite/test/DruidAdapter2IT.java | 30 ++---
.../org/apache/calcite/test/DruidAdapterIT.java | 32 ++---
15 files changed, 319 insertions(+), 141 deletions(-)
diff --git
a/arrow/src/test/java/org/apache/calcite/adapter/arrow/ArrowAdapterTest.java
b/arrow/src/test/java/org/apache/calcite/adapter/arrow/ArrowAdapterTest.java
index 295ae6eae6..05bc053afc 100644
--- a/arrow/src/test/java/org/apache/calcite/adapter/arrow/ArrowAdapterTest.java
+++ b/arrow/src/test/java/org/apache/calcite/adapter/arrow/ArrowAdapterTest.java
@@ -374,7 +374,7 @@ class ArrowAdapterTest {
String sql = "select * from arrowdata\n"
+ " where \"floatField\"=15.0";
String plan = "PLAN=ArrowToEnumerableConverter\n"
- + " ArrowFilter(condition=[=(CAST($2):DOUBLE, 15.0E0)])\n"
+ + " ArrowFilter(condition=[=($2, 15.0E0)])\n"
+ " ArrowTableScan(table=[[ARROW, ARROWDATA]], fields=[[0, 1, 2,
3]])\n\n";
String result = "intField=15; stringField=15; floatField=15.0;
longField=15\n";
@@ -666,7 +666,7 @@ class ArrowAdapterTest {
@Test void testFilteredAgg() {
String sql = "select SUM(SAL) FILTER (WHERE COMM > 400) as SALESSUM from
EMP";
String plan = "PLAN=EnumerableAggregate(group=[{}], SALESSUM=[SUM($0)
FILTER $1])\n"
- + " EnumerableCalc(expr#0..7=[{inputs}], expr#8=[400], expr#9=[>($t6,
$t8)], "
+ + " EnumerableCalc(expr#0..7=[{inputs}], expr#8=[400:DECIMAL(19, 0)],
expr#9=[>($t6, $t8)], "
+ "expr#10=[IS TRUE($t9)], SAL=[$t5], $f1=[$t10])\n"
+ " ArrowToEnumerableConverter\n"
+ " ArrowTableScan(table=[[ARROW, EMP]], fields=[[0, 1, 2, 3, 4,
5, 6, 7]])\n\n";
@@ -684,7 +684,7 @@ class ArrowAdapterTest {
String sql = "select SUM(SAL) FILTER (WHERE COMM > 400) as SALESSUM from
EMP group by EMPNO";
String plan = "PLAN=EnumerableCalc(expr#0..1=[{inputs}], SALESSUM=[$t1])\n"
+ " EnumerableAggregate(group=[{0}], SALESSUM=[SUM($1) FILTER $2])\n"
- + " EnumerableCalc(expr#0..7=[{inputs}], expr#8=[400],
expr#9=[>($t6, $t8)], "
+ + " EnumerableCalc(expr#0..7=[{inputs}], expr#8=[400:DECIMAL(19,
0)], expr#9=[>($t6, $t8)], "
+ "expr#10=[IS TRUE($t9)], EMPNO=[$t0], SAL=[$t5], $f2=[$t10])\n"
+ " ArrowToEnumerableConverter\n"
+ " ArrowTableScan(table=[[ARROW, EMP]], fields=[[0, 1, 2, 3,
4, 5, 6, 7]])\n\n";
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 8475797717..e313a7bfd1 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
@@ -492,8 +492,8 @@ public abstract class AbstractTypeCoercion implements
TypeCoercion {
}
/**
- * Determines common type for a comparison operator when one operand is
String type and the
- * other is not. For date + timestamp operands, use timestamp as common type,
+ * Determines common type for a comparison operator.
+ * For date and timestamp operands, use timestamp as common type,
* i.e. Timestamp(2017-01-01 00:00 ...) > Date(2018) evaluates to be
false.
*/
@Override public @Nullable RelDataType commonTypeForBinaryComparison(
@@ -509,9 +509,11 @@ public abstract class AbstractTypeCoercion implements
TypeCoercion {
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.sameNamedType(type1, type2)) {
+ return factory.leastRestrictive(ImmutableList.of(type1, type2));
+ }
+
+ // DATETIME < CHARACTER -> DATETIME
if (SqlTypeUtil.isCharacter(type1) && SqlTypeUtil.isDatetime(type2)) {
return factory.createTypeWithNullability(type2, type1.isNullable());
}
@@ -520,7 +522,7 @@ public abstract class AbstractTypeCoercion implements
TypeCoercion {
return factory.createTypeWithNullability(type1, type2.isNullable());
}
- // DATE + TIMESTAMP -> TIMESTAMP
+ // DATE < TIMESTAMP -> TIMESTAMP
if (SqlTypeUtil.isDate(type1) && SqlTypeUtil.isTimestamp(type2)) {
return factory.createTypeWithNullability(type2, type1.isNullable());
}
@@ -556,6 +558,19 @@ public abstract class AbstractTypeCoercion implements
TypeCoercion {
return null;
}
+ if (SqlTypeUtil.isString(type1) && SqlTypeUtil.isString(type2)) {
+ // Return the string with the larger precision
+ if (type1.getPrecision() == RelDataType.PRECISION_NOT_SPECIFIED) {
+ return factory.createTypeWithNullability(type1, type2.isNullable());
+ } else if (type2.getPrecision() == RelDataType.PRECISION_NOT_SPECIFIED) {
+ return factory.createTypeWithNullability(type2, type1.isNullable());
+ } else if (type1.getPrecision() > type2.getPrecision()) {
+ return factory.createTypeWithNullability(type1, type2.isNullable());
+ } else {
+ return factory.createTypeWithNullability(type2, type1.isNullable());
+ }
+ }
+
// 1 > '1' will be coerced to 1 > 1.
if (SqlTypeUtil.isAtomic(type1) && SqlTypeUtil.isCharacter(type2)) {
if (SqlTypeUtil.isTimestamp(type1)) {
@@ -581,6 +596,43 @@ public abstract class AbstractTypeCoercion implements
TypeCoercion {
}
}
+ if (SqlTypeUtil.isApproximateNumeric(type1) &&
SqlTypeUtil.isApproximateNumeric(type2)) {
+ if (type1.getPrecision() > type2.getPrecision()) {
+ return factory.createTypeWithNullability(type1, type2.isNullable());
+ } else {
+ return factory.createTypeWithNullability(type2, type1.isNullable());
+ }
+ }
+
+ if (SqlTypeUtil.isApproximateNumeric(type1) &&
SqlTypeUtil.isExactNumeric(type2)) {
+ return factory.createTypeWithNullability(type1, type2.isNullable());
+ }
+
+ if (SqlTypeUtil.isApproximateNumeric(type2) &&
SqlTypeUtil.isExactNumeric(type1)) {
+ return factory.createTypeWithNullability(type2, type1.isNullable());
+ }
+
+ if (SqlTypeUtil.isExactNumeric(type1) &&
SqlTypeUtil.isExactNumeric(type2)) {
+ if (SqlTypeUtil.isDecimal(type1)) {
+ // Use max precision
+ RelDataType result =
+ factory.createSqlType(type1.getSqlTypeName(),
+ Math.max(type1.getPrecision(), type2.getPrecision()),
type1.getScale());
+ return factory.createTypeWithNullability(result, type1.isNullable() ||
type2.isNullable());
+ } else if (SqlTypeUtil.isDecimal(type2)) {
+ // Use max precision
+ RelDataType result =
+ factory.createSqlType(type2.getSqlTypeName(),
+ Math.max(type1.getPrecision(), type2.getPrecision()),
type2.getScale());
+ return factory.createTypeWithNullability(result, type1.isNullable() ||
type2.isNullable());
+ }
+ if (type1.getPrecision() > type2.getPrecision()) {
+ return factory.createTypeWithNullability(type1, type2.isNullable());
+ } else {
+ return factory.createTypeWithNullability(type2, type1.isNullable());
+ }
+ }
+
return null;
}
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 e3a6176486..2a343cca1b 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
@@ -93,8 +93,7 @@ public interface TypeCoercion {
@Nullable RelDataType type1, @Nullable 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.
*/
@Nullable RelDataType commonTypeForBinaryComparison(
@Nullable RelDataType type1, @Nullable RelDataType type2);
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 73de170e5c..711c8d22dc 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
@@ -48,7 +48,6 @@ import org.checkerframework.checker.nullness.qual.Nullable;
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;
@@ -291,18 +290,9 @@ public class TypeCoercionImpl extends AbstractTypeCoercion
{
return null;
}
- RelDataType commonType;
- if (SqlTypeUtil.sameNamedType(type1, type2)) {
- commonType = factory.leastRestrictive(Arrays.asList(type1, type2));
- } else {
- commonType = commonTypeForBinaryComparison(type1, type2);
- }
+ RelDataType 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));
- }
+ commonType = commonTypeForBinaryComparison(commonType, dataTypes.get(i));
}
return commonType;
}
diff --git
a/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java
b/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java
index c52851e14e..fd72b876b0 100644
---
a/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java
+++
b/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java
@@ -319,33 +319,33 @@ class RelToSqlConverterTest {
+ "where \"product_id\" > 0\n"
+ "group by \"product_id\"";
final String expectedDefault = "SELECT"
- + " SUM(\"shelf_width\") FILTER (WHERE \"net_weight\" > 0 IS TRUE),"
+ + " SUM(\"shelf_width\") FILTER (WHERE \"net_weight\" > 0E0 IS TRUE),"
+ " SUM(\"shelf_width\")\n"
+ "FROM \"foodmart\".\"product\"\n"
+ "WHERE \"product_id\" > 0\n"
+ "GROUP BY \"product_id\"";
final String expectedBigQuery = "SELECT"
- + " SUM(CASE WHEN net_weight > 0 IS TRUE"
+ + " SUM(CASE WHEN net_weight > 0E0 IS TRUE"
+ " THEN shelf_width ELSE NULL END), "
+ "SUM(shelf_width)\n"
+ "FROM foodmart.product\n"
+ "WHERE product_id > 0\n"
+ "GROUP BY product_id";
final String expectedFirebolt = "SELECT"
- + " SUM(CASE WHEN \"net_weight\" > 0 IS TRUE"
+ + " SUM(CASE WHEN \"net_weight\" > 0E0 IS TRUE"
+ " THEN \"shelf_width\" ELSE NULL END), "
+ "SUM(\"shelf_width\")\n"
+ "FROM \"foodmart\".\"product\"\n"
+ "WHERE \"product_id\" > 0\n"
+ "GROUP BY \"product_id\"";
final String expectedMysql = "SELECT"
- + " SUM(CASE WHEN `net_weight` > 0 IS TRUE"
+ + " SUM(CASE WHEN `net_weight` > 0E0 IS TRUE"
+ " THEN `shelf_width` ELSE NULL END), SUM(`shelf_width`)\n"
+ "FROM `foodmart`.`product`\n"
+ "WHERE `product_id` > 0\n"
+ "GROUP BY `product_id`";
final String expectedStarRocks = "SELECT"
- + " SUM(CASE WHEN `net_weight` > 0 IS TRUE"
+ + " SUM(CASE WHEN `net_weight` > 0E0 IS TRUE"
+ " THEN `shelf_width` ELSE NULL END), SUM(`shelf_width`)\n"
+ "FROM `foodmart`.`product`\n"
+ "WHERE `product_id` > 0\n"
@@ -539,7 +539,7 @@ class RelToSqlConverterTest {
final String expected = "SELECT *\n"
+ "FROM \"foodmart\".\"product\"\n"
+ "WHERE (\"product_id\" = 10 OR \"product_id\" <= 5) "
- + "AND (80 >= \"shelf_width\" OR \"shelf_width\" > 30)";
+ + "AND (CAST(80 AS DOUBLE) >= \"shelf_width\" OR \"shelf_width\" >
CAST(30 AS DOUBLE))";
sql(query).ok(expected);
}
@@ -2097,26 +2097,26 @@ class RelToSqlConverterTest {
+ " sum(\"gross_weight\") as \"" + alias + "\"\n"
+ "from \"product\"\n"
+ "group by \"product_id\"\n"
- + "having sum(\"product\".\"gross_weight\") < 200";
+ + "having sum(\"product\".\"gross_weight\") < 2.000E2";
// PostgreSQL has isHavingAlias=false, case-sensitive=true
final String expectedPostgresql = "SELECT \"product_id\" + 1,"
+ " SUM(\"gross_weight\") AS \"" + alias + "\"\n"
+ "FROM \"foodmart\".\"product\"\n"
+ "GROUP BY \"product_id\"\n"
- + "HAVING SUM(\"gross_weight\") < 200";
+ + "HAVING SUM(\"gross_weight\") < 2.000E2";
// MySQL has isHavingAlias=true, case-sensitive=true
final String expectedMysql = "SELECT `product_id` + 1, `" + alias + "`\n"
+ "FROM (SELECT `product_id`, SUM(`gross_weight`) AS `" + alias + "`\n"
+ "FROM `foodmart`.`product`\n"
+ "GROUP BY `product_id`\n"
- + "HAVING `" + alias + "` < 200) AS `t1`";
+ + "HAVING `" + alias + "` < 2.000E2) AS `t1`";
// BigQuery has isHavingAlias=true, case-sensitive=false
final String expectedBigQuery = upperAlias
? "SELECT product_id + 1, GROSS_WEIGHT\n"
+ "FROM (SELECT product_id, SUM(gross_weight) AS GROSS_WEIGHT\n"
+ "FROM foodmart.product\n"
+ "GROUP BY product_id\n"
- + "HAVING GROSS_WEIGHT < 200) AS t1"
+ + "HAVING GROSS_WEIGHT < 2.000E2) AS t1"
// Before [CALCITE-3896] was fixed, we got
// "HAVING SUM(gross_weight) < 200) AS t1"
// which on BigQuery gives you an error about aggregating aggregates
@@ -2124,7 +2124,7 @@ class RelToSqlConverterTest {
+ "FROM (SELECT product_id, SUM(gross_weight) AS gross_weight\n"
+ "FROM foodmart.product\n"
+ "GROUP BY product_id\n"
- + "HAVING gross_weight < 200) AS t1";
+ + "HAVING gross_weight < 2.000E2) AS t1";
sql(query)
.withBigQuery().ok(expectedBigQuery)
.withPostgresql().ok(expectedPostgresql)
@@ -2144,11 +2144,11 @@ class RelToSqlConverterTest {
final String expected = "SELECT \"product_id\"\n"
+ "FROM (SELECT \"product_id\", AVG(\"gross_weight\") AS \"AGW\"\n"
+ "FROM \"foodmart\".\"product\"\n"
- + "WHERE \"net_weight\" < 100\n"
+ + "WHERE \"net_weight\" < CAST(100 AS DOUBLE)\n"
+ "GROUP BY \"product_id\"\n"
- + "HAVING AVG(\"gross_weight\") > 50) AS \"t2\"\n"
+ + "HAVING AVG(\"gross_weight\") > CAST(50 AS DOUBLE)) AS \"t2\"\n"
+ "GROUP BY \"product_id\"\n"
- + "HAVING AVG(\"AGW\") > 60";
+ + "HAVING AVG(\"AGW\") > 6.00E1";
sql(query).ok(expected);
}
@@ -5366,21 +5366,21 @@ class RelToSqlConverterTest {
+ "UNION ALL\n"
+ "SELECT NULL) END AS `$f0`\n"
+ "FROM `foodmart`.`product`) AS `t0` ON TRUE\n"
- + "WHERE `product`.`net_weight` > `t0`.`$f0`";
+ + "WHERE `product`.`net_weight` > CAST(`t0`.`$f0` AS DOUBLE)";
final String expectedPostgresql = "SELECT \"product\".\"product_class_id\"
AS \"C\"\n"
+ "FROM \"foodmart\".\"product\"\n"
+ "LEFT JOIN (SELECT CASE COUNT(*) WHEN 0 THEN NULL WHEN 1 THEN
MIN(\"product_class_id\") ELSE (SELECT CAST(NULL AS INTEGER)\n"
+ "UNION ALL\n"
+ "SELECT CAST(NULL AS INTEGER)) END AS \"$f0\"\n"
+ "FROM \"foodmart\".\"product\") AS \"t0\" ON TRUE\n"
- + "WHERE \"product\".\"net_weight\" > \"t0\".\"$f0\"";
+ + "WHERE \"product\".\"net_weight\" > CAST(\"t0\".\"$f0\" AS DOUBLE
PRECISION)";
final String expectedHsqldb = "SELECT product.product_class_id AS C\n"
+ "FROM foodmart.product\n"
+ "LEFT JOIN (SELECT CASE COUNT(*) WHEN 0 THEN NULL WHEN 1 THEN
MIN(product_class_id) ELSE ((VALUES 0E0)\n"
+ "UNION ALL\n"
+ "(VALUES 0E0)) END AS $f0\n"
+ "FROM foodmart.product) AS t0 ON TRUE\n"
- + "WHERE product.net_weight > t0.$f0";
+ + "WHERE product.net_weight > CAST(t0.$f0 AS DOUBLE)";
sql(query)
.withConfig(c -> c.withExpand(true))
.withMysql().ok(expectedMysql)
@@ -6905,7 +6905,7 @@ class RelToSqlConverterTest {
+ "within group (order by \"net_weight\" desc) filter (where
\"net_weight\" > 0)"
+ "from \"product\" group by \"product_class_id\"";
final String expected = "SELECT \"product_class_id\",
COLLECT(\"net_weight\") "
- + "FILTER (WHERE \"net_weight\" > 0 IS TRUE) "
+ + "FILTER (WHERE \"net_weight\" > 0E0 IS TRUE) "
+ "WITHIN GROUP (ORDER BY \"net_weight\" DESC)\n"
+ "FROM \"foodmart\".\"product\"\n"
+ "GROUP BY \"product_class_id\"";
@@ -8241,7 +8241,7 @@ class RelToSqlConverterTest {
final String expected = "SELECT *\n"
+ "FROM TABLE(DEDUP(CURSOR ((SELECT \"product_id\", \"product_name\"\n"
+ "FROM \"foodmart\".\"product\"\n"
- + "WHERE \"net_weight\" > 100 AND \"product_name\" = 'Hello World')), "
+ + "WHERE \"net_weight\" > CAST(100 AS DOUBLE) AND \"product_name\" =
'Hello World')), "
+ "CURSOR ((SELECT \"employee_id\", \"full_name\"\n"
+ "FROM \"foodmart\".\"employee\"\n"
+ "GROUP BY \"employee_id\", \"full_name\")), 'NAME'))";
diff --git a/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
b/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
index 380b543409..4862a89039 100644
--- a/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
+++ b/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
@@ -3112,6 +3112,15 @@ class RelOptRulesTest extends RelOptTestBase {
.check();
}
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-6617";>[CALCITE-6617]
+ * TypeCoercion is not applied correctly to comparisons</a>. */
+ @Test void testRand() {
+ final String sql = "SELECT * FROM (SELECT 1, ROUND(RAND()) AS A)\n"
+ + "WHERE A BETWEEN 1 AND 10 OR A IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10)";
+ sql(sql).withRule(CoreRules.PROJECT_REDUCE_EXPRESSIONS).check();
+ }
+
/** Test case for
* <a
href="https://issues.apache.org/jira/browse/CALCITE-6481";>[CALCITE-6481]
* Optimize 'VALUES...UNION...VALUES' to a single 'VALUES' the IN-list
contains CAST
diff --git a/core/src/test/java/org/apache/calcite/test/TCatalogReader.java
b/core/src/test/java/org/apache/calcite/test/TCatalogReader.java
index afd195211a..c6ebd03869 100644
--- a/core/src/test/java/org/apache/calcite/test/TCatalogReader.java
+++ b/core/src/test/java/org/apache/calcite/test/TCatalogReader.java
@@ -49,7 +49,7 @@ public class TCatalogReader extends MockCatalogReader {
t1.addColumn("t1_smallint", f.smallintType);
t1.addColumn("t1_int", f.intType);
t1.addColumn("t1_bigint", f.bigintType);
- t1.addColumn("t1_float", f.floatType);
+ t1.addColumn("t1_real", f.realType);
t1.addColumn("t1_double", f.doubleType);
t1.addColumn("t1_decimal", f.decimalType);
t1.addColumn("t1_timestamp", f.timestampType);
@@ -64,7 +64,7 @@ public class TCatalogReader extends MockCatalogReader {
t2.addColumn("t2_smallint", f.smallintType);
t2.addColumn("t2_int", f.intType);
t2.addColumn("t2_bigint", f.bigintType);
- t2.addColumn("t2_float", f.floatType);
+ t2.addColumn("t2_real", f.realType);
t2.addColumn("t2_double", f.doubleType);
t2.addColumn("t2_decimal", f.decimalType);
t2.addColumn("t2_timestamp", f.timestampType);
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 41c14ed8d9..228b569062 100644
--- a/core/src/test/java/org/apache/calcite/test/TypeCoercionConverterTest.java
+++ b/core/src/test/java/org/apache/calcite/test/TypeCoercionConverterTest.java
@@ -161,14 +161,14 @@ class TypeCoercionConverterTest extends SqlToRelTestBase {
// char decimal float double
// char decimal smallint double
final String sql = "select t1_int, t1_decimal, t1_smallint, t1_double from
t1 "
- + "union select t2_varchar20, t2_decimal, t2_float, t2_bigint from t2 "
- + "union select t1_varchar20, t1_decimal, t1_float, t1_double from t1 "
+ + "union select t2_varchar20, t2_decimal, t2_real, t2_bigint from t2 "
+ + "union select t1_varchar20, t1_decimal, t1_real, t1_double from t1 "
+ "union select t2_varchar20, t2_decimal, t2_smallint, t2_double from
t2";
sql(sql).ok();
}
@Test void testInsertQuerySourceCoercion() {
- final String sql = "insert into t1 select t2_smallint, t2_int, t2_bigint,
t2_float,\n"
+ final String sql = "insert into t1 select t2_smallint, t2_int, t2_bigint,
t2_real,\n"
+ "t2_double, t2_decimal, t2_int, t2_date, t2_timestamp, t2_varchar20,
t2_int from t2";
sql(sql).ok();
}
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 b20c32ebc7..dc7e357e69 100644
--- a/core/src/test/java/org/apache/calcite/test/TypeCoercionTest.java
+++ b/core/src/test/java/org/apache/calcite/test/TypeCoercionTest.java
@@ -33,6 +33,7 @@ import org.apache.calcite.util.Pair;
import com.google.common.collect.ImmutableList;
+import org.checkerframework.checker.nullness.qual.Nullable;
import org.junit.jupiter.api.Test;
import java.util.List;
@@ -118,17 +119,17 @@ class TypeCoercionTest {
f.checkCommonType(f.intType, f.bigintType, f.bigintType, true);
f.checkCommonType(f.bigintType, f.bigintType, f.bigintType, true);
// FLOAT/DOUBLE
- f.checkCommonType(f.nullType, f.floatType, f.nullableFloatType, true);
+ f.checkCommonType(f.nullType, f.realType, f.nullableRealType, true);
f.checkCommonType(f.nullType, f.doubleType, f.nullableDoubleType, true);
// Use RelDataTypeFactory#leastRestrictive to find the common type; it's
not
// symmetric but it's ok because precision does not become lower.
- f.checkCommonType(f.floatType, f.doubleType, f.floatType, false);
- f.checkCommonType(f.floatType, f.floatType, f.floatType, true);
+ f.checkCommonType(f.realType, f.doubleType, f.doubleType, false);
+ f.checkCommonType(f.realType, f.realType, f.realType, true);
f.checkCommonType(f.doubleType, f.doubleType, f.doubleType, true);
// EXACT + FRACTIONAL
- f.checkCommonType(f.intType, f.floatType, f.floatType, true);
+ f.checkCommonType(f.intType, f.realType, f.realType, true);
f.checkCommonType(f.intType, f.doubleType, f.doubleType, true);
- f.checkCommonType(f.bigintType, f.floatType, f.floatType, true);
+ f.checkCommonType(f.bigintType, f.realType, f.realType, true);
f.checkCommonType(f.bigintType, f.doubleType, f.doubleType, true);
// Fixed precision decimal
RelDataType decimal54 =
@@ -226,12 +227,12 @@ class TypeCoercionTest {
sql("select LOCALTIME from (values(true)) union values '1'")
.type("RecordType(VARCHAR NOT NULL LOCALTIME) NOT NULL");
sql("select t1_int, t1_decimal, t1_smallint, t1_double from t1 "
- + "union select t2_varchar20, t2_decimal, t2_float, t2_bigint from t2 "
- + "union select t1_varchar20, t1_decimal, t1_float, t1_double from t1 "
+ + "union select t2_varchar20, t2_decimal, t2_real, t2_bigint from t2 "
+ + "union select t1_varchar20, t1_decimal, t1_real, t1_double from t1 "
+ "union select t2_varchar20, t2_decimal, t2_smallint, t2_double from
t2")
.type("RecordType(VARCHAR NOT NULL T1_INT,"
+ " DECIMAL(19, 0) NOT NULL T1_DECIMAL,"
- + " FLOAT NOT NULL T1_SMALLINT,"
+ + " REAL NOT NULL T1_SMALLINT,"
+ " DOUBLE NOT NULL T1_DOUBLE) NOT NULL");
// (int) union (int) union (varchar(20))
sql("select t1_int from t1 "
@@ -253,17 +254,17 @@ class TypeCoercionTest {
// intersect
sql("select t1_int, t1_decimal, t1_smallint, t1_double from t1 "
- + "intersect select t2_varchar20, t2_decimal, t2_float, t2_bigint from
t2 ")
+ + "intersect select t2_varchar20, t2_decimal, t2_real, t2_bigint from
t2 ")
.type("RecordType(VARCHAR NOT NULL T1_INT,"
+ " DECIMAL(19, 0) NOT NULL T1_DECIMAL,"
- + " FLOAT NOT NULL T1_SMALLINT,"
+ + " REAL NOT NULL T1_SMALLINT,"
+ " DOUBLE NOT NULL T1_DOUBLE) NOT NULL");
// except
sql("select t1_int, t1_decimal, t1_smallint, t1_double from t1 "
- + "except select t2_varchar20, t2_decimal, t2_float, t2_bigint from t2
")
+ + "except select t2_varchar20, t2_decimal, t2_real, t2_bigint from t2
")
.type("RecordType(VARCHAR NOT NULL T1_INT,"
+ " DECIMAL(19, 0) NOT NULL T1_DECIMAL,"
- + " FLOAT NOT NULL T1_SMALLINT,"
+ + " REAL NOT NULL T1_SMALLINT,"
+ " DOUBLE NOT NULL T1_DOUBLE) NOT NULL");
}
@@ -353,11 +354,65 @@ class TypeCoercionTest {
.columnType("BOOLEAN NOT NULL");
}
+ @Test void testComparisonCoercion() {
+ // NULL
+ final Fixture f = fixture();
+ f.comparisonCommonType(f.nullType, f.nullType, f.nullType);
+ // BOOLEAN
+ f.comparisonCommonType(f.nullType, f.booleanType, null);
+ f.comparisonCommonType(f.booleanType, f.booleanType, f.booleanType);
+ f.comparisonCommonType(f.intType, f.booleanType, null);
+ f.comparisonCommonType(f.bigintType, f.booleanType, null);
+ // INT
+ f.comparisonCommonType(f.smallintType, f.intType, f.intType);
+ f.comparisonCommonType(f.smallintType, f.bigintType, f.bigintType);
+ f.comparisonCommonType(f.intType, f.bigintType, f.bigintType);
+ f.comparisonCommonType(f.bigintType, f.bigintType, f.bigintType);
+ // FLOAT/DOUBLE
+ f.comparisonCommonType(f.realType, f.doubleType, f.doubleType);
+ f.comparisonCommonType(f.realType, f.realType, f.realType);
+ f.comparisonCommonType(f.doubleType, f.doubleType, f.doubleType);
+ // EXACT + FRACTIONAL
+ f.comparisonCommonType(f.intType, f.realType, f.realType);
+ f.comparisonCommonType(f.intType, f.doubleType, f.doubleType);
+ f.comparisonCommonType(f.bigintType, f.realType, f.realType);
+ f.comparisonCommonType(f.bigintType, f.doubleType, f.doubleType);
+ // Fixed precision decimal
+ RelDataType decimal54 =
+ f.typeFactory.createSqlType(SqlTypeName.DECIMAL, 5, 4);
+ RelDataType decimal71 =
+ f.typeFactory.createSqlType(SqlTypeName.DECIMAL, 7, 1);
+ RelDataType decimal104 =
+ f.typeFactory.createSqlType(SqlTypeName.DECIMAL, 10, 4);
+ f.comparisonCommonType(decimal54, decimal71, decimal104);
+ f.comparisonCommonType(decimal54, f.doubleType, f.doubleType);
+ f.comparisonCommonType(decimal54, f.intType, decimal104);
+ // CHAR/VARCHAR
+ f.comparisonCommonType(f.charType, f.varcharType, f.varcharType);
+ f.comparisonCommonType(f.intType, f.charType, f.intType);
+ f.comparisonCommonType(f.doubleType, f.charType, f.doubleType);
+ // TIMESTAMP
+ f.comparisonCommonType(f.timestampType, f.timestampType, f.timestampType);
+ f.comparisonCommonType(f.dateType, f.timestampType, f.timestampType);
+ f.comparisonCommonType(f.intType, f.timestampType, null);
+ f.comparisonCommonType(f.varcharType, f.timestampType, f.timestampType);
+ // generic
+ f.comparisonCommonType(f.charType, f.mapType(f.intType, f.charType), null);
+ f.comparisonCommonType(f.arrayType(f.intType),
f.recordType(ImmutableList.of()),
+ null);
+ f.comparisonCommonType(f.recordType("a", f.intType),
+ f.recordType("a", f.intType), f.recordType("a", f.intType));
+ f.comparisonCommonType(f.recordType("a", f.arrayType(f.intType)),
+ f.recordType("a", f.arrayType(f.intType)),
+ f.recordType("a", f.arrayType(f.intType)));
+ }
+
+
/** Test case for case when expression and COALESCE operator. */
@Test void testCaseWhen() {
// coalesce
// double int float
- sql("select COALESCE(t1_double, t1_int, t1_float) from t1")
+ sql("select COALESCE(t1_double, t1_int, t1_real) from t1")
.type("RecordType(DOUBLE NOT NULL EXPR$0) NOT NULL");
// bigint int decimal
sql("select COALESCE(t1_bigint, t1_int, t1_decimal) from t1")
@@ -369,13 +424,13 @@ class TypeCoercionTest {
sql("select COALESCE(t1_varchar20, t1_timestamp) from t1")
.type("RecordType(VARCHAR NOT NULL EXPR$0) NOT NULL");
// null float int
- sql("select COALESCE(null, t1_float, t1_int) from t1")
- .type("RecordType(FLOAT EXPR$0) NOT NULL");
+ sql("select COALESCE(null, t1_real, t1_int) from t1")
+ .type("RecordType(REAL EXPR$0) NOT NULL");
// null int decimal double
sql("select COALESCE(null, t1_int, t1_decimal, t1_double) from t1")
.type("RecordType(DOUBLE EXPR$0) NOT NULL");
// null float double varchar
- sql("select COALESCE(null, t1_float, t1_double, t1_varchar20) from t1")
+ sql("select COALESCE(null, t1_real, t1_double, t1_varchar20) from t1")
.type("RecordType(VARCHAR EXPR$0) NOT NULL");
// timestamp int varchar
sql("select COALESCE(t1_timestamp, t1_int, t1_varchar20) from t1")
@@ -404,7 +459,7 @@ class TypeCoercionTest {
+ "else t2_varchar20 end from t2")
.type("RecordType(VARCHAR NOT NULL EXPR$0) NOT NULL");
// float decimal
- sql("select case when 1 > 0 then t2_float else t2_decimal end from t2")
+ sql("select case when 1 > 0 then t2_real else t2_decimal end from t2")
.type("RecordType(DOUBLE NOT NULL EXPR$0) NOT NULL");
// bigint decimal
sql("select case when 1 > 0 then t2_bigint else t2_decimal end from t2")
@@ -459,7 +514,7 @@ class TypeCoercionTest {
f.shouldNotCast(checkedType4, SqlTypeFamily.APPROXIMATE_NUMERIC);
// FLOAT/REAL
- RelDataType checkedType5 = f.floatType;
+ RelDataType checkedType5 = f.realType;
f.checkShouldCast(checkedType5, combine(f.numericTypes, charTypes));
f.shouldCast(checkedType5, SqlTypeFamily.DECIMAL,
f.typeFactory.decimalOf(checkedType5));
@@ -581,7 +636,7 @@ class TypeCoercionTest {
expr("select t1_smallint||t1_int||t1_double from t1")
.columnType("VARCHAR");
// boolean float smallint
- expr("select t1_boolean||t1_float||t1_smallint from t1")
+ expr("select t1_boolean||t1_real||t1_smallint from t1")
.columnType("VARCHAR");
// decimal
expr("select t1_decimal||t1_varchar20 from t1")
@@ -598,7 +653,7 @@ class TypeCoercionTest {
+ "SMALLINT NOT NULL t1_smallint, "
+ "INTEGER NOT NULL t1_int, "
+ "BIGINT NOT NULL t1_bigint, "
- + "FLOAT NOT NULL t1_float, "
+ + "REAL NOT NULL t1_real, "
+ "DOUBLE NOT NULL t1_double, "
+ "DECIMAL(19, 0) NOT NULL t1_decimal, "
+ "TIMESTAMP(0) NOT NULL t1_timestamp, "
@@ -606,12 +661,12 @@ class TypeCoercionTest {
+ "BINARY(1) NOT NULL t1_binary, "
+ "BOOLEAN NOT NULL t1_boolean) NOT NULL";
- final String sql = "insert into t1 select t2_smallint, t2_int, t2_bigint,
t2_float,\n"
+ final String sql = "insert into t1 select t2_smallint, t2_int, t2_bigint,
t2_real,\n"
+ "t2_double, t2_decimal, t2_int, t2_date, t2_timestamp, t2_varchar20,
t2_int from t2";
sql(sql).type(expectRowType);
final String sql1 = "insert into ^t1^(t1_varchar20, t1_date, t1_int)\n"
- + "select t2_smallint, t2_timestamp, t2_float from t2";
+ + "select t2_smallint, t2_timestamp, t2_real from t2";
sql(sql1).fails("(?s).*Column 't1_smallint' has no default value and does
not allow NULLs.*");
final String sql2 = "update t1 set t1_varchar20=123, "
@@ -647,8 +702,8 @@ class TypeCoercionTest {
final RelDataType nullableIntType;
final RelDataType bigintType;
final RelDataType nullableBigintType;
- final RelDataType floatType;
- final RelDataType nullableFloatType;
+ final RelDataType realType;
+ final RelDataType nullableRealType;
final RelDataType doubleType;
final RelDataType nullableDoubleType;
final RelDataType decimalType;
@@ -695,8 +750,8 @@ class TypeCoercionTest {
nullableIntType = this.typeFactory.createTypeWithNullability(intType,
true);
bigintType = this.typeFactory.createSqlType(SqlTypeName.BIGINT);
nullableBigintType =
this.typeFactory.createTypeWithNullability(bigintType, true);
- floatType = this.typeFactory.createSqlType(SqlTypeName.FLOAT);
- nullableFloatType =
this.typeFactory.createTypeWithNullability(floatType, true);
+ realType = this.typeFactory.createSqlType(SqlTypeName.REAL);
+ nullableRealType = this.typeFactory.createTypeWithNullability(realType,
true);
doubleType = this.typeFactory.createSqlType(SqlTypeName.DOUBLE);
nullableDoubleType =
this.typeFactory.createTypeWithNullability(doubleType, true);
decimalType = this.typeFactory.createSqlType(SqlTypeName.DECIMAL);
@@ -871,7 +926,7 @@ class TypeCoercionTest {
return false;
}
- private String toStringNullable(Object o1) {
+ private String toStringNullable(@Nullable Object o1) {
if (o1 == null) {
return "NULL";
}
@@ -882,7 +937,7 @@ class TypeCoercionTest {
private void checkCommonType(
RelDataType type1,
RelDataType type2,
- RelDataType expected,
+ @Nullable RelDataType expected,
boolean isSymmetric) {
RelDataType result = typeCoercion.getTightestCommonType(type1, type2);
assertThat("Expected " + toStringNullable(expected)
@@ -901,11 +956,30 @@ class TypeCoercionTest {
}
}
+ private void comparisonCommonType(
+ RelDataType type1,
+ RelDataType type2,
+ @Nullable RelDataType expected) {
+ RelDataType result = typeCoercion.commonTypeForBinaryComparison(type1,
type2);
+ assertThat("Expected " + toStringNullable(expected)
+ + " as comparison common type for " + type1
+ + " and " + type2
+ + ", but found " + toStringNullable(result),
+ result,
+ sameInstance(expected));
+ RelDataType result1 = typeCoercion.commonTypeForBinaryComparison(type2,
type1);
+ assertThat("Expected " + toStringNullable(expected)
+ + " as common type for " + type2
+ + " and " + type1
+ + ", but found " + toStringNullable(result1),
+ result1, sameInstance(expected));
+ }
+
/** Decision method for finding a wider type. */
private void checkWiderType(
RelDataType type1,
RelDataType type2,
- RelDataType expected,
+ @Nullable RelDataType expected,
boolean stringPromotion,
boolean symmetric) {
RelDataType result =
diff --git
a/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
b/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
index eca85f5e4b..babaae6201 100644
--- a/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
+++ b/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
@@ -1566,7 +1566,7 @@ case when cast(ename as double) < 5 then 0.0
</Resource>
<Resource name="planBefore">
<![CDATA[
-LogicalProject(T=[CASE(<(CAST(CASE(>($1, 'abc'), $1,
null:VARCHAR(20))):DOUBLE, 5), 0.0E0:DOUBLE, CASE(IS NOT NULL(CAST(CASE(>($1,
'abc'), $1, null:VARCHAR(20))):DOUBLE), CAST(CAST(CASE(>($1, 'abc'), $1,
null:VARCHAR(20))):DOUBLE):DOUBLE NOT NULL, 1.0E0:DOUBLE))])
+LogicalProject(T=[CASE(<(CAST(CASE(>($1, 'abc'), $1,
null:VARCHAR(20))):DOUBLE, 5.0E0), 0.0E0:DOUBLE, CASE(IS NOT
NULL(CAST(CASE(>($1, 'abc'), $1, null:VARCHAR(20))):DOUBLE),
CAST(CAST(CASE(>($1, 'abc'), $1, null:VARCHAR(20))):DOUBLE):DOUBLE NOT NULL,
1.0E0:DOUBLE))])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
]]>
</Resource>
@@ -12192,6 +12192,28 @@ LogicalAggregate(group=[{0}], EXPR$1=[SUM($1)])
LogicalAggregate(group=[{0}], EXPR$1=[SUM($1)])
LogicalProject(ENAME=[$1], MGR=[$3])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testRand">
+ <Resource name="sql">
+ <![CDATA[SELECT * FROM (SELECT 1, ROUND(RAND()) AS A)
+WHERE A BETWEEN 1 AND 10 OR A IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10)]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalProject(EXPR$0=[$0], A=[$1])
+ LogicalFilter(condition=[OR(AND(>=($1, CAST(1):DOUBLE NOT NULL), <=($1,
CAST(10):DOUBLE NOT NULL)), =($1, CAST(1):DOUBLE NOT NULL), =($1,
CAST(2):DOUBLE NOT NULL), =($1, CAST(3):DOUBLE NOT NULL), =($1, CAST(4):DOUBLE
NOT NULL), =($1, CAST(5):DOUBLE NOT NULL), =($1, CAST(6):DOUBLE NOT NULL),
=($1, CAST(7):DOUBLE NOT NULL), =($1, CAST(8):DOUBLE NOT NULL), =($1,
CAST(9):DOUBLE NOT NULL), =($1, CAST(10):DOUBLE NOT NULL))])
+ LogicalProject(EXPR$0=[1], A=[ROUND(RAND())])
+ LogicalValues(tuples=[[{ 0 }]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+LogicalProject(EXPR$0=[1], A=[$1])
+ LogicalFilter(condition=[OR(AND(>=($1, CAST(1):DOUBLE NOT NULL), <=($1,
CAST(10):DOUBLE NOT NULL)), =($1, CAST(1):DOUBLE NOT NULL), =($1,
CAST(2):DOUBLE NOT NULL), =($1, CAST(3):DOUBLE NOT NULL), =($1, CAST(4):DOUBLE
NOT NULL), =($1, CAST(5):DOUBLE NOT NULL), =($1, CAST(6):DOUBLE NOT NULL),
=($1, CAST(7):DOUBLE NOT NULL), =($1, CAST(8):DOUBLE NOT NULL), =($1,
CAST(9):DOUBLE NOT NULL), =($1, CAST(10):DOUBLE NOT NULL))])
+ LogicalProject(EXPR$0=[1], A=[ROUND(RAND())])
+ LogicalValues(tuples=[[{ 0 }]])
]]>
</Resource>
</TestCase>
@@ -13080,14 +13102,14 @@ LogicalProject($0=[$3], $1=[$4])
<Resource name="planBefore">
<![CDATA[
LogicalProject(ENAME=[$1])
- LogicalFilter(condition=[>($5, 100.0:DECIMAL(4, 1))])
+ LogicalFilter(condition=[>(CAST($5):DECIMAL(10, 1) NOT NULL, 100.0)])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
]]>
</Resource>
<Resource name="planAfter">
<![CDATA[
LogicalProject(ENAME=[$1])
- LogicalCalc(expr#0..8=[{inputs}], expr#9=[10:BIGINT], expr#10=[*($t5, $t9)],
expr#11=[100.0:DECIMAL(4, 1)], expr#12=[Reinterpret($t11)], expr#13=[>($t10,
$t12)], proj#0..8=[{exprs}], $condition=[$t13])
+ LogicalCalc(expr#0..8=[{inputs}], expr#9=[10:BIGINT], expr#10=[*($t5, $t9)],
expr#11=[true], expr#12=[Reinterpret($t10, $t11)], expr#13=[Reinterpret($t12)],
expr#14=[100.0:DECIMAL(10, 1)], expr#15=[Reinterpret($t14)], expr#16=[>($t13,
$t15)], proj#0..8=[{exprs}], $condition=[$t16])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
]]>
</Resource>
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 c5476150a7..4253f0b7e1 100644
---
a/core/src/test/resources/org/apache/calcite/test/TypeCoercionConverterTest.xml
+++
b/core/src/test/resources/org/apache/calcite/test/TypeCoercionConverterTest.xml
@@ -136,13 +136,13 @@ LogicalProject(F0=[null:BOOLEAN],
F1=[CAST(true):BOOLEAN], F2=[null:BOOLEAN], F3
</TestCase>
<TestCase name="testInsertQuerySourceCoercion">
<Resource name="sql">
- <![CDATA[insert into t1 select t2_smallint, t2_int, t2_bigint, t2_float,
+ <![CDATA[insert into t1 select t2_smallint, t2_int, t2_bigint, t2_real,
t2_double, t2_decimal, t2_int, t2_date, t2_timestamp, t2_varchar20, t2_int
from t2]]>
</Resource>
<Resource name="plan">
<![CDATA[
LogicalTableModify(table=[[CATALOG, SALES, T1]], operation=[INSERT],
flattened=[false])
- LogicalProject(t1_varchar20=[CAST($1):VARCHAR(20) NOT NULL],
t1_smallint=[CAST($2):SMALLINT NOT NULL], t1_int=[CAST($3):INTEGER NOT NULL],
t1_bigint=[CAST($4):BIGINT NOT NULL], t1_float=[CAST($5):FLOAT NOT NULL],
t1_double=[CAST($6):DOUBLE NOT NULL], t1_decimal=[CAST($2):DECIMAL(19, 0) NOT
NULL], t1_timestamp=[CAST($8):TIMESTAMP(0) NOT NULL], t1_date=[CAST($7):DATE
NOT NULL], t1_binary=[CAST($0):BINARY(1) NOT NULL], t1_boolean=[<>($2, 0)])
+ LogicalProject(t1_varchar20=[CAST($1):VARCHAR(20) NOT NULL],
t1_smallint=[CAST($2):SMALLINT NOT NULL], t1_int=[CAST($3):INTEGER NOT NULL],
t1_bigint=[CAST($4):BIGINT NOT NULL], t1_real=[CAST($5):REAL NOT NULL],
t1_double=[CAST($6):DOUBLE NOT NULL], t1_decimal=[CAST($2):DECIMAL(19, 0) NOT
NULL], t1_timestamp=[CAST($8):TIMESTAMP(0) NOT NULL], t1_date=[CAST($7):DATE
NOT NULL], t1_binary=[CAST($0):BINARY(1) NOT NULL], t1_boolean=[<>($2, 0)])
LogicalTableScan(table=[[CATALOG, SALES, T2]])
]]>
</Resource>
@@ -211,7 +211,7 @@ LogicalProject(F0=[null:BOOLEAN], F1=[CAST(false):BOOLEAN],
F2=[null:BOOLEAN], F
</TestCase>
<TestCase name="testSetOperation">
<Resource name="sql">
- <![CDATA[select t1_int, t1_decimal, t1_smallint, t1_double from t1 union
select t2_varchar20, t2_decimal, t2_float, t2_bigint from t2 union select
t1_varchar20, t1_decimal, t1_float, t1_double from t1 union select
t2_varchar20, t2_decimal, t2_smallint, t2_double from t2]]>
+ <![CDATA[select t1_int, t1_decimal, t1_smallint, t1_double from t1 union
select t2_varchar20, t2_decimal, t2_real, t2_bigint from t2 union select
t1_varchar20, t1_decimal, t1_real, t1_double from t1 union select t2_varchar20,
t2_decimal, t2_smallint, t2_double from t2]]>
</Resource>
<Resource name="plan">
<![CDATA[
@@ -220,9 +220,9 @@ LogicalUnion(all=[false])
LogicalUnion(all=[false])
LogicalProject(T1_INT=[CAST($2):VARCHAR NOT NULL], T1_DECIMAL=[$6],
T1_SMALLINT=[$1], T1_DOUBLE=[$5])
LogicalTableScan(table=[[CATALOG, SALES, T1]])
- LogicalProject(T2_VARCHAR20=[$0], T2_DECIMAL=[$6], T2_FLOAT=[$4],
T2_BIGINT=[$3])
+ LogicalProject(T2_VARCHAR20=[$0], T2_DECIMAL=[$6], T2_REAL=[$4],
T2_BIGINT=[$3])
LogicalTableScan(table=[[CATALOG, SALES, T2]])
- LogicalProject(T1_VARCHAR20=[$0], T1_DECIMAL=[$6], T1_FLOAT=[$4],
T1_DOUBLE=[$5])
+ LogicalProject(T1_VARCHAR20=[$0], T1_DECIMAL=[$6], T1_REAL=[$4],
T1_DOUBLE=[$5])
LogicalTableScan(table=[[CATALOG, SALES, T1]])
LogicalProject(T2_VARCHAR20=[$0], T2_DECIMAL=[$6], T2_SMALLINT=[$1],
T2_DOUBLE=[$5])
LogicalTableScan(table=[[CATALOG, SALES, T2]])
@@ -250,7 +250,7 @@ LogicalUnion(all=[false])
<Resource name="plan">
<![CDATA[
LogicalTableModify(table=[[CATALOG, SALES, T1]], operation=[UPDATE],
updateColumnList=[[t1_varchar20, t1_date, t1_int]],
sourceExpressionList=[[CAST(123):VARCHAR(20) NOT NULL, CAST(2020-01-03
10:14:34):DATE NOT NULL, CAST(12.3:DECIMAL(3, 1)):INTEGER NOT NULL]],
flattened=[false])
- LogicalProject(t1_varchar20=[$0], t1_smallint=[$1], t1_int=[$2],
t1_bigint=[$3], t1_float=[$4], t1_double=[$5], t1_decimal=[$6],
t1_timestamp=[$7], t1_date=[$8], t1_binary=[$9], t1_boolean=[$10],
EXPR$0=[123], EXPR$1=[2020-01-03 10:14:34], EXPR$2=[12.3:DECIMAL(3, 1)])
+ LogicalProject(t1_varchar20=[$0], t1_smallint=[$1], t1_int=[$2],
t1_bigint=[$3], t1_real=[$4], t1_double=[$5], t1_decimal=[$6],
t1_timestamp=[$7], t1_date=[$8], t1_binary=[$9], t1_boolean=[$10],
EXPR$0=[123], EXPR$1=[2020-01-03 10:14:34], EXPR$2=[12.3:DECIMAL(3, 1)])
LogicalTableScan(table=[[CATALOG, SALES, T1]])
]]>
</Resource>
diff --git a/core/src/test/resources/sql/agg.iq
b/core/src/test/resources/sql/agg.iq
index d109a763f5..733ae7a9c0 100644
--- a/core/src/test/resources/sql/agg.iq
+++ b/core/src/test/resources/sql/agg.iq
@@ -3120,7 +3120,7 @@ group by dept.deptno;
!ok
EnumerableAggregate(group=[{0}], S=[COLLECT($1) WITHIN GROUP ([1 DESC])],
S1=[COLLECT($1) WITHIN GROUP ([2])], S2=[COLLECT($1) WITHIN GROUP ([1]) FILTER
$3])
- EnumerableCalc(expr#0..3=[{inputs}], expr#4=[1], expr#5=[2000],
expr#6=[>($t2, $t5)], expr#7=[IS TRUE($t6)], DEPTNO=[$t0], SAL=[$t2],
$f2=[$t4], $f3=[$t7])
+ EnumerableCalc(expr#0..3=[{inputs}], expr#4=[1],
expr#5=[CAST($t2):DECIMAL(10, 2)], expr#6=[2000.00:DECIMAL(10, 2)],
expr#7=[>($t5, $t6)], expr#8=[IS TRUE($t7)], DEPTNO=[$t0], SAL=[$t2],
$f2=[$t4], $f3=[$t8])
EnumerableHashJoin(condition=[=($0, $3)], joinType=[inner])
EnumerableCalc(expr#0..2=[{inputs}], DEPTNO=[$t0])
EnumerableTableScan(table=[[scott, DEPT]])
diff --git a/core/src/test/resources/sql/sub-query.iq
b/core/src/test/resources/sql/sub-query.iq
index 7bfedac3a8..ace96e5ec6 100644
--- a/core/src/test/resources/sql/sub-query.iq
+++ b/core/src/test/resources/sql/sub-query.iq
@@ -2176,8 +2176,8 @@ where sal + 100 not in (
!ok
EnumerableAggregate(group=[{}], C=[COUNT()])
- EnumerableCalc(expr#0..7=[{inputs}], expr#8=[0], expr#9=[=($t1, $t8)],
expr#10=[IS NULL($t0)], expr#11=[IS NOT NULL($t6)], expr#12=[<($t2, $t1)],
expr#13=[OR($t10, $t11, $t12)], expr#14=[IS NOT TRUE($t13)], expr#15=[OR($t9,
$t14)], proj#0..7=[{exprs}], $condition=[$t15])
- EnumerableMergeJoin(condition=[AND(=($3, $5), =($4, $7))], joinType=[left])
+ EnumerableCalc(expr#0..7=[{inputs}], expr#8=[0], expr#9=[=($t1, $t8)],
expr#10=[IS NULL($t0)], expr#11=[IS NOT NULL($t7)], expr#12=[<($t2, $t1)],
expr#13=[OR($t10, $t11, $t12)], expr#14=[IS NOT TRUE($t13)], expr#15=[OR($t9,
$t14)], proj#0..7=[{exprs}], $condition=[$t15])
+ EnumerableMergeJoin(condition=[AND(=($3, $5), =($4, $6))], joinType=[left])
EnumerableSort(sort0=[$3], sort1=[$4], dir0=[ASC], dir1=[ASC])
EnumerableCalc(expr#0..6=[{inputs}], expr#7=[100], expr#8=[+($t2,
$t7)], expr#9=[CAST($t1):VARCHAR(14)], SAL=[$t2], c=[$t4], ck=[$t5], $f5=[$t8],
ENAME0=[$t9])
EnumerableMergeJoin(condition=[=($3, $6)], joinType=[left])
@@ -2187,8 +2187,8 @@ EnumerableAggregate(group=[{}], C=[COUNT()])
EnumerableSort(sort0=[$2], dir0=[ASC])
EnumerableCalc(expr#0..2=[{inputs}], expr#3=[1:BIGINT],
expr#4=[IS NOT NULL($t1)], c=[$t3], ck=[$t3], DNAME=[$t1], $condition=[$t4])
EnumerableTableScan(table=[[scott, DEPT]])
- EnumerableSort(sort0=[$0], sort1=[$2], dir0=[ASC], dir1=[ASC])
- EnumerableCalc(expr#0..2=[{inputs}], expr#3=[true], expr#4=[IS NOT
NULL($t1)], DEPTNO=[$t0], i=[$t3], DNAME=[$t1], $condition=[$t4])
+ EnumerableSort(sort0=[$0], sort1=[$1], dir0=[ASC], dir1=[ASC])
+ EnumerableCalc(expr#0..2=[{inputs}], expr#3=[CAST($t0):DECIMAL(13, 2)
NOT NULL], expr#4=[true], expr#5=[IS NOT NULL($t1)], EXPR$0=[$t3], DNAME=[$t1],
i=[$t4], $condition=[$t5])
EnumerableTableScan(table=[[scott, DEPT]])
!plan
@@ -2894,7 +2894,7 @@ EnumerableCalc(expr#0..1=[{inputs}], expr#2=[IS
NULL($t1)], DEPTNO=[$t0], $condi
EnumerableAggregate(group=[{0}])
EnumerableCalc(expr#0..2=[{inputs}], expr#3=[true], expr#4=[1],
expr#5=[>($t2, $t4)], i=[$t3], $condition=[$t5])
EnumerableAggregate(group=[{5, 7}], c=[COUNT()])
- EnumerableCalc(expr#0..7=[{inputs}], expr#8=[CAST($t5):DECIMAL(12,
2)], expr#9=[3000.00:DECIMAL(12, 2)], expr#10=[=($t8, $t9)], expr#11=[IS NOT
NULL($t7)], expr#12=[AND($t10, $t11)], proj#0..7=[{exprs}], $condition=[$t12])
+ EnumerableCalc(expr#0..7=[{inputs}], expr#8=[CAST($t5):DECIMAL(10,
2)], expr#9=[3000.00:DECIMAL(10, 2)], expr#10=[=($t8, $t9)], expr#11=[IS NOT
NULL($t7)], expr#12=[AND($t10, $t11)], proj#0..7=[{exprs}], $condition=[$t12])
EnumerableTableScan(table=[[scott, EMP]])
!plan
@@ -2920,7 +2920,7 @@ EnumerableCalc(expr#0..1=[{inputs}], expr#2=[IS
NULL($t1)], DEPTNO=[$t0], U=[$t2
EnumerableAggregate(group=[{0}])
EnumerableCalc(expr#0..2=[{inputs}], expr#3=[true], expr#4=[1],
expr#5=[>($t2, $t4)], i=[$t3], $condition=[$t5])
EnumerableAggregate(group=[{5, 7}], c=[COUNT()])
- EnumerableCalc(expr#0..7=[{inputs}], expr#8=[CAST($t5):DECIMAL(12,
2)], expr#9=[3000.00:DECIMAL(12, 2)], expr#10=[=($t8, $t9)], expr#11=[IS NOT
NULL($t7)], expr#12=[AND($t10, $t11)], proj#0..7=[{exprs}], $condition=[$t12])
+ EnumerableCalc(expr#0..7=[{inputs}], expr#8=[CAST($t5):DECIMAL(10,
2)], expr#9=[3000.00:DECIMAL(10, 2)], expr#10=[=($t8, $t9)], expr#11=[IS NOT
NULL($t7)], expr#12=[AND($t10, $t11)], proj#0..7=[{exprs}], $condition=[$t12])
EnumerableTableScan(table=[[scott, EMP]])
!plan
@@ -2946,7 +2946,7 @@ EnumerableCalc(expr#0..1=[{inputs}], expr#2=[IS NOT
NULL($t1)], DEPTNO=[$t0], U=
EnumerableAggregate(group=[{0}])
EnumerableCalc(expr#0..2=[{inputs}], expr#3=[true], expr#4=[1],
expr#5=[>($t2, $t4)], i=[$t3], $condition=[$t5])
EnumerableAggregate(group=[{5, 7}], c=[COUNT()])
- EnumerableCalc(expr#0..7=[{inputs}], expr#8=[CAST($t5):DECIMAL(12,
2)], expr#9=[3000.00:DECIMAL(12, 2)], expr#10=[=($t8, $t9)], expr#11=[IS NOT
NULL($t7)], expr#12=[AND($t10, $t11)], proj#0..7=[{exprs}], $condition=[$t12])
+ EnumerableCalc(expr#0..7=[{inputs}], expr#8=[CAST($t5):DECIMAL(10,
2)], expr#9=[3000.00:DECIMAL(10, 2)], expr#10=[=($t8, $t9)], expr#11=[IS NOT
NULL($t7)], expr#12=[AND($t10, $t11)], proj#0..7=[{exprs}], $condition=[$t12])
EnumerableTableScan(table=[[scott, EMP]])
!plan
@@ -3290,7 +3290,7 @@ select *, (comm <> 300 and comm <> 500 and comm <> null)
as i from "scott".emp;
(14 rows)
!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:BOOLEAN], expr#12=[AND($t10, $t11)],
proj#0..7=[{exprs}], I=[$t12])
+EnumerableCalc(expr#0..7=[{inputs}], expr#8=[CAST($t6):DECIMAL(10, 2)],
expr#9=[Sarg[(-∞..300.00:DECIMAL(10, 2)), (300.00:DECIMAL(10,
2)..500.00:DECIMAL(10, 2)), (500.00:DECIMAL(10, 2)..+∞)]:DECIMAL(10, 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
@@ -3927,16 +3927,23 @@ select comm, comm in (500, 300, 0) from emp;
EnumerableCalc(expr#0..5=[{inputs}], expr#6=[IS NOT NULL($t5)],
expr#7=[0:BIGINT], expr#8=[<>($t1, $t7)], expr#9=[AND($t6, $t8)], expr#10=[IS
NULL($t3)], expr#11=[<($t2, $t1)], expr#12=[OR($t10, $t11)],
expr#13=[null:BOOLEAN], expr#14=[IS NULL($t5)], expr#15=[AND($t12, $t13, $t8,
$t14)], expr#16=[OR($t9, $t15)], COMM=[$t0], EXPR$1=[$t16])
EnumerableMergeJoin(condition=[=($3, $4)], joinType=[left])
EnumerableSort(sort0=[$3], dir0=[ASC])
- EnumerableCalc(expr#0..3=[{inputs}], COMM=[$t1], $f0=[$t2], $f1=[$t3],
COMM0=[$t1])
+ EnumerableCalc(expr#0..3=[{inputs}], expr#4=[CAST($t1):DECIMAL(10, 2)],
COMM=[$t1], $f0=[$t2], $f1=[$t3], COMM0=[$t4])
EnumerableNestedLoopJoin(condition=[true], joinType=[inner])
EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0], COMM=[$t6])
EnumerableTableScan(table=[[scott, EMP]])
EnumerableCalc(expr#0=[{inputs}], $f0=[$t0], $f00=[$t0])
EnumerableAggregate(group=[{}], agg#0=[COUNT()])
- EnumerableValues(tuples=[[{ 500.00 }, { 300.00 }, { 0.00 }]])
+ EnumerableUnion(all=[true])
+ EnumerableValues(tuples=[[{ 500.00 }]])
+ EnumerableValues(tuples=[[{ 300.00 }]])
+ EnumerableValues(tuples=[[{ 0.00 }]])
EnumerableSort(sort0=[$0], dir0=[ASC])
- EnumerableCalc(expr#0=[{inputs}], expr#1=[true], proj#0..1=[{exprs}])
- EnumerableValues(tuples=[[{ 500.00 }, { 300.00 }, { 0.00 }]])
+ EnumerableAggregate(group=[{0}], agg#0=[MIN($1)])
+ EnumerableCalc(expr#0=[{inputs}], expr#1=[true], proj#0..1=[{exprs}])
+ EnumerableUnion(all=[true])
+ EnumerableValues(tuples=[[{ 500.00 }]])
+ EnumerableValues(tuples=[[{ 300.00 }]])
+ EnumerableValues(tuples=[[{ 0.00 }]])
!plan
# Test LHS is nullable and RHS is nullable
@@ -3967,15 +3974,24 @@ select comm, comm in (500, 300, 0, null) from emp;
EnumerableCalc(expr#0..5=[{inputs}], expr#6=[IS NOT NULL($t5)],
expr#7=[0:BIGINT], expr#8=[<>($t1, $t7)], expr#9=[AND($t6, $t8)], expr#10=[IS
NULL($t3)], expr#11=[<($t2, $t1)], expr#12=[OR($t10, $t11)],
expr#13=[null:BOOLEAN], expr#14=[IS NULL($t5)], expr#15=[AND($t12, $t13, $t8,
$t14)], expr#16=[OR($t9, $t15)], COMM=[$t0], EXPR$1=[$t16])
EnumerableMergeJoin(condition=[=($3, $4)], joinType=[left])
EnumerableSort(sort0=[$3], dir0=[ASC])
- EnumerableCalc(expr#0..3=[{inputs}], COMM=[$t1], $f0=[$t2], $f1=[$t3],
COMM0=[$t1])
+ EnumerableCalc(expr#0..3=[{inputs}], expr#4=[CAST($t1):DECIMAL(12, 2)],
COMM=[$t1], $f0=[$t2], $f1=[$t3], COMM0=[$t4])
EnumerableNestedLoopJoin(condition=[true], joinType=[inner])
EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0], COMM=[$t6])
EnumerableTableScan(table=[[scott, EMP]])
EnumerableAggregate(group=[{}], agg#0=[COUNT()], agg#1=[COUNT($0)])
- EnumerableValues(tuples=[[{ 500.00 }, { 300.00 }, { 0.00 }, { null
}]])
+ EnumerableUnion(all=[true])
+ EnumerableValues(tuples=[[{ 500.00 }]])
+ EnumerableValues(tuples=[[{ 300.00 }]])
+ EnumerableValues(tuples=[[{ 0.00 }]])
+ EnumerableValues(tuples=[[{ null }]])
EnumerableSort(sort0=[$0], dir0=[ASC])
- EnumerableCalc(expr#0=[{inputs}], expr#1=[true], proj#0..1=[{exprs}])
- EnumerableValues(tuples=[[{ 500.00 }, { 300.00 }, { 0.00 }, { null }]])
+ EnumerableAggregate(group=[{0}], agg#0=[MIN($1)])
+ EnumerableCalc(expr#0=[{inputs}], expr#1=[true], proj#0..1=[{exprs}])
+ EnumerableUnion(all=[true])
+ EnumerableValues(tuples=[[{ 500.00 }]])
+ EnumerableValues(tuples=[[{ 300.00 }]])
+ EnumerableValues(tuples=[[{ 0.00 }]])
+ EnumerableValues(tuples=[[{ null }]])
!plan
# Test LHS is (not nullable, not nullable) and RHS is (not nullable, not
nullable)
@@ -4046,16 +4062,23 @@ select comm, (comm, comm) in ((500, 500), (300, 300),
(0, 0)) from emp;
EnumerableCalc(expr#0..7=[{inputs}], expr#8=[IS NOT NULL($t7)],
expr#9=[0:BIGINT], expr#10=[<>($t1, $t9)], expr#11=[AND($t8, $t10)],
expr#12=[IS NULL($t3)], expr#13=[IS NULL($t4)], expr#14=[<($t2, $t1)],
expr#15=[OR($t12, $t13, $t14)], expr#16=[null:BOOLEAN], expr#17=[IS NULL($t7)],
expr#18=[AND($t15, $t16, $t10, $t17)], expr#19=[OR($t11, $t18)], COMM=[$t0],
EXPR$1=[$t19])
EnumerableMergeJoin(condition=[AND(=($3, $5), =($4, $6))], joinType=[left])
EnumerableSort(sort0=[$3], sort1=[$4], dir0=[ASC], dir1=[ASC])
- EnumerableCalc(expr#0..3=[{inputs}], COMM=[$t1], $f0=[$t2], $f1=[$t3],
COMM0=[$t1], COMM1=[$t1])
+ EnumerableCalc(expr#0..3=[{inputs}], expr#4=[CAST($t1):DECIMAL(10, 2)],
COMM=[$t1], $f0=[$t2], $f1=[$t3], COMM0=[$t4], COMM1=[$t4])
EnumerableNestedLoopJoin(condition=[true], joinType=[inner])
EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0], COMM=[$t6])
EnumerableTableScan(table=[[scott, EMP]])
EnumerableCalc(expr#0=[{inputs}], $f0=[$t0], $f00=[$t0])
EnumerableAggregate(group=[{}], agg#0=[COUNT()])
- EnumerableValues(tuples=[[{ 500.00, 500.00 }, { 300.00, 300.00
}, { 0.00, 0.00 }]])
+ EnumerableUnion(all=[true])
+ EnumerableValues(tuples=[[{ 500.00, 500.00 }]])
+ EnumerableValues(tuples=[[{ 300.00, 300.00 }]])
+ EnumerableValues(tuples=[[{ 0.00, 0.00 }]])
EnumerableSort(sort0=[$0], sort1=[$1], dir0=[ASC], dir1=[ASC])
- EnumerableCalc(expr#0..1=[{inputs}], expr#2=[true], proj#0..2=[{exprs}])
- EnumerableValues(tuples=[[{ 500.00, 500.00 }, { 300.00, 300.00 }, {
0.00, 0.00 }]])
+ EnumerableAggregate(group=[{0, 1}], agg#0=[MIN($2)])
+ EnumerableCalc(expr#0..1=[{inputs}], expr#2=[true],
proj#0..2=[{exprs}])
+ EnumerableUnion(all=[true])
+ EnumerableValues(tuples=[[{ 500.00, 500.00 }]])
+ EnumerableValues(tuples=[[{ 300.00, 300.00 }]])
+ EnumerableValues(tuples=[[{ 0.00, 0.00 }]])
!plan
# Test LHS is (nullable, nullable) and RHS is (nullable, nullable)
@@ -4086,15 +4109,24 @@ select comm, (comm, comm) in ((500, 500), (300, 300),
(0, 0), (null , null)) fro
EnumerableCalc(expr#0..7=[{inputs}], expr#8=[IS NOT NULL($t7)],
expr#9=[0:BIGINT], expr#10=[<>($t1, $t9)], expr#11=[AND($t8, $t10)],
expr#12=[IS NULL($t3)], expr#13=[IS NULL($t4)], expr#14=[<($t2, $t1)],
expr#15=[OR($t12, $t13, $t14)], expr#16=[null:BOOLEAN], expr#17=[IS NULL($t7)],
expr#18=[AND($t15, $t16, $t10, $t17)], expr#19=[OR($t11, $t18)], COMM=[$t0],
EXPR$1=[$t19])
EnumerableMergeJoin(condition=[AND(=($3, $5), =($4, $6))], joinType=[left])
EnumerableSort(sort0=[$3], sort1=[$4], dir0=[ASC], dir1=[ASC])
- EnumerableCalc(expr#0..3=[{inputs}], COMM=[$t1], $f0=[$t2], $f1=[$t3],
COMM0=[$t1], COMM1=[$t1])
+ EnumerableCalc(expr#0..3=[{inputs}], expr#4=[CAST($t1):DECIMAL(10, 2)],
COMM=[$t1], $f0=[$t2], $f1=[$t3], COMM0=[$t4], COMM1=[$t4])
EnumerableNestedLoopJoin(condition=[true], joinType=[inner])
EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0], COMM=[$t6])
EnumerableTableScan(table=[[scott, EMP]])
EnumerableAggregate(group=[{}], agg#0=[COUNT()], agg#1=[COUNT($0,
$1)])
- EnumerableValues(tuples=[[{ 500.00, 500.00 }, { 300.00, 300.00 },
{ 0.00, 0.00 }, { null, null }]])
+ EnumerableUnion(all=[true])
+ EnumerableValues(tuples=[[{ 500.00, 500.00 }]])
+ EnumerableValues(tuples=[[{ 300.00, 300.00 }]])
+ EnumerableValues(tuples=[[{ 0.00, 0.00 }]])
+ EnumerableValues(tuples=[[{ null, null }]])
EnumerableSort(sort0=[$0], sort1=[$1], dir0=[ASC], dir1=[ASC])
- EnumerableCalc(expr#0..1=[{inputs}], expr#2=[true], proj#0..2=[{exprs}])
- EnumerableValues(tuples=[[{ 500.00, 500.00 }, { 300.00, 300.00 }, {
0.00, 0.00 }, { null, null }]])
+ EnumerableAggregate(group=[{0, 1}], agg#0=[MIN($2)])
+ EnumerableCalc(expr#0..1=[{inputs}], expr#2=[true],
proj#0..2=[{exprs}])
+ EnumerableUnion(all=[true])
+ EnumerableValues(tuples=[[{ 500.00, 500.00 }]])
+ EnumerableValues(tuples=[[{ 300.00, 300.00 }]])
+ EnumerableValues(tuples=[[{ 0.00, 0.00 }]])
+ EnumerableValues(tuples=[[{ null, null }]])
!plan
# End sub-query.iq
diff --git a/druid/src/test/java/org/apache/calcite/test/DruidAdapter2IT.java
b/druid/src/test/java/org/apache/calcite/test/DruidAdapter2IT.java
index a44add8094..384d98409b 100644
--- a/druid/src/test/java/org/apache/calcite/test/DruidAdapter2IT.java
+++ b/druid/src/test/java/org/apache/calcite/test/DruidAdapter2IT.java
@@ -2374,14 +2374,14 @@ public class DruidAdapter2IT {
"PLAN=EnumerableInterpreter\n"
+ " DruidQuery(table=[[foodmart, foodmart]], "
+
"intervals=[[1900-01-09T00:00:00.000Z/2992-01-10T00:00:00.000Z]], filter=[>"
- + "($91, 10)], projects=[[$90, IS TRUE(>($91, 10))]], groups=[{}],
aggs=[[SUM($0)"
+ + "($91, 10.0E0)], projects=[[$90, IS TRUE(>($91, 10.0E0))]],
groups=[{}], aggs=[[SUM($0)"
+ "]])";
sql(sql)
.explainContains(expectedSubExplain)
.queryContains(
new DruidChecker("\"queryType\":\"timeseries\"",
"\"filter\":{\"type\":\"bound\","
- +
"\"dimension\":\"store_cost\",\"lower\":\"10\",\"lowerStrict\":true,"
+ +
"\"dimension\":\"store_cost\",\"lower\":\"10.0\",\"lowerStrict\":true,"
+ "\"ordering\":\"numeric\"}"))
.returnsUnordered("EXPR$0=25.06");
}
@@ -2392,13 +2392,13 @@ public class DruidAdapter2IT {
String expectedSubExplain = "PLAN="
+ "EnumerableCalc(expr#0..1=[{inputs}], EXPR$0=[$t1],
product_id=[$t0])\n"
+ " EnumerableInterpreter\n"
- + " DruidQuery(table=[[foodmart, foodmart]],
intervals=[[1900-01-09T00:00:00.000Z/2992-01-10T00:00:00.000Z]],
filter=[AND(>(CAST($1):INTEGER, 1553), >($91, 5))], projects=[[$1, $90]],
groups=[{0}], aggs=[[SUM($1)]])";
+ + " DruidQuery(table=[[foodmart, foodmart]],
intervals=[[1900-01-09T00:00:00.000Z/2992-01-10T00:00:00.000Z]],
filter=[AND(>(CAST($1):INTEGER, 1553), >($91, 5.0E0))], projects=[[$1, $90]],
groups=[{0}], aggs=[[SUM($1)]])";
CalciteAssert.AssertQuery q = sql(sql)
.explainContains(expectedSubExplain)
.queryContains(
new DruidChecker("\"queryType\":\"groupBy\"",
"{\"type\":\"bound\","
- +
"\"dimension\":\"store_cost\",\"lower\":\"5\",\"lowerStrict\":true,"
+ +
"\"dimension\":\"store_cost\",\"lower\":\"5.0\",\"lowerStrict\":true,"
+ "\"ordering\":\"numeric\"}"));
q.returnsUnordered("EXPR$0=10.16; product_id=1554",
"EXPR$0=45.05; product_id=1556",
@@ -2413,7 +2413,7 @@ public class DruidAdapter2IT {
sql(sql)
.queryContains(
new DruidChecker("\"queryType\":\"groupBy\"",
"{\"type\":\"bound\","
- +
"\"dimension\":\"store_cost\",\"lower\":\"5\",\"lowerStrict\":true,"
+ +
"\"dimension\":\"store_cost\",\"lower\":\"5.0\",\"lowerStrict\":true,"
+ "\"ordering\":\"numeric\"}"))
.returnsUnordered("EXPR$0=10.6; product_id=1556",
"EXPR$0=10.6; product_id=1556",
@@ -3150,7 +3150,7 @@ public class DruidAdapter2IT {
+ "<= ((floor(\\'store_sales\\') * 25) + 2))'}";
final String likeExpressionFilter =
"{'type':'expression','expression':'like(\\'product_id\\'";
final String likeExpressionFilter2 = "1%";
- final String simpleBound =
"{'type':'bound','dimension':'store_cost','lower':'1',"
+ final String simpleBound =
"{'type':'bound','dimension':'store_cost','lower':'1.0',"
+ "'lowerStrict':true,'ordering':'numeric'}";
final String timeSimpleFilter =
"{'type':'bound','dimension':'__time','upper':'1997-01-02T00:00:00.000Z',"
@@ -3169,7 +3169,7 @@ public class DruidAdapter2IT {
final String plan = "PLAN=EnumerableInterpreter\n"
+ " DruidQuery(table=[[foodmart, foodmart]],
intervals=[[1900-01-09T00:00:00.000Z/"
+ "2992-01-10T00:00:00.000Z]], filter=[AND(<=(/(+(CAST($1):INTEGER,
$90), "
- + "-($91, 5)), +(*(FLOOR($90), 25), 2)), >($90, 0), LIKE($1, '1%'),
>($91, 1), "
+ + "-($91, 5)), +(*(FLOOR($90), 25), 2)), >($90, 0.0E0), LIKE($1,
'1%'), >($91, 1.0E0), "
+ "<($0, 1997-01-02 00:00:00), =(EXTRACT(FLAG(MONTH), $0), 1), "
+ "=(EXTRACT(FLAG(DAY), $0), 1), =(+(/(EXTRACT(FLAG(MONTH), $0), 4),
1), 1))], "
+ "groups=[{}], aggs=[[COUNT()]])";
@@ -3194,7 +3194,7 @@ public class DruidAdapter2IT {
+ " AND ceil(\"timestamp\" TO SECOND) > CAST('1997-01-01' AS
TIMESTAMP) ";
final String plan = "PLAN=EnumerableInterpreter\n"
+ " DruidQuery(table=[[foodmart, foodmart]],
intervals=[[1997-01-01T00:00:00.001Z/"
- + "1997-01-04T00:00:00.001Z]], filter=[>(CEIL($90), 1)], groups=[{}],
aggs=[[COUNT()]])";
+ + "1997-01-04T00:00:00.001Z]], filter=[>(CEIL($90), 1.0E0)],
groups=[{}], aggs=[[COUNT()]])";
sql(sql)
.explainContains(plan)
.returnsOrdered("EXPR$0=408");
@@ -3272,7 +3272,7 @@ public class DruidAdapter2IT {
sql(sql)
.queryContains(
new DruidChecker("\"queryType\":\"groupBy\"",
"{\"type\":\"bound\","
- +
"\"dimension\":\"store_cost\",\"lower\":\"5\",\"lowerStrict\":true,"
+ +
"\"dimension\":\"store_cost\",\"lower\":\"5.0\",\"lowerStrict\":true,"
+ "\"ordering\":\"numeric\"}"))
.runs();
}
@@ -3355,7 +3355,7 @@ public class DruidAdapter2IT {
.explainContains("PLAN=EnumerableInterpreter\n"
+ " BindableSort(sort0=[$2], dir0=[ASC], fetch=[3])\n"
+ " DruidQuery(table=[[foodmart, foodmart]],
intervals=[[1900-01-09T00:00:00.000Z/"
- + "2992-01-10T00:00:00.000Z]], filter=[<($90, 20)],
projects=[[+(COS($90), 1), SIN($91),"
+ + "2992-01-10T00:00:00.000Z]], filter=[<($90, 20.0E0)],
projects=[[+(COS($90), 1), SIN($91),"
+ " +(EXTRACT(FLAG(DAY), $0), 1)]])");
}
@@ -3371,7 +3371,7 @@ public class DruidAdapter2IT {
.explainContains("PLAN=EnumerableInterpreter\n"
+ " BindableSort(sort0=[$1], dir0=[ASC], fetch=[3])\n"
+ " DruidQuery(table=[[foodmart, foodmart]],
intervals=[[1900-01-09T00:00:00.000Z/"
- + "2992-01-10T00:00:00.000Z]], filter=[<($90, 20)],
projects=[[+(COS(+($90, $91)), 1), "
+ + "2992-01-10T00:00:00.000Z]], filter=[<($90, 20.0E0)],
projects=[[+(COS(+($90, $91)), 1), "
+ "+(EXTRACT(FLAG(DAY), $0), 1)]])");
}
@@ -3501,7 +3501,7 @@ public class DruidAdapter2IT {
final String query =
"{'queryType':'timeseries','dataSource':'foodmart','descending':false,"
+
"'granularity':'all','aggregations':[{'type':'filtered','filter':{'type':'or','fields':"
+
"[{'type':'selector','dimension':'state_province','value':'CA'},{'type':'and','fields':"
- +
"[{'type':'bound','dimension':'store_sales','lower':'100','lowerStrict':true,"
+ +
"[{'type':'bound','dimension':'store_sales','lower':'100.0','lowerStrict':true,"
+
"'ordering':'numeric'},{'type':'not','field':{'type':'selector','dimension':'product_id',"
+
"'value':'100'}}]}]},'aggregator':{'type':'filtered','filter':{'type':'not',"
+
"'field':{'type':'selector','dimension':'product_id','value':null}},'aggregator':"
@@ -3637,10 +3637,10 @@ public class DruidAdapter2IT {
.explainContains("PLAN=EnumerableInterpreter\n"
+ " DruidQuery(table=[[foodmart, foodmart]],
intervals=[[1900-01-09T00:00:00.000Z/"
+ "2992-01-10T00:00:00.000Z]], projects=[[$1, $90]], groups=[{0}],
aggs=[[SUM($1)]], "
- + "filter=[>($1, 220)], sort0=[0], dir0=[ASC], fetch=[2])")
+ + "filter=[>($1, 220.0E0)], sort0=[0], dir0=[ASC], fetch=[2])")
.queryContains(
new
DruidChecker("'having':{'type':'filter','filter':{'type':'bound',"
- +
"'dimension':'S','lower':'220','lowerStrict':true,'ordering':'numeric'}}"));
+ +
"'dimension':'S','lower':'220.0','lowerStrict':true,'ordering':'numeric'}}"));
q.returnsOrdered("P=1; S=236.55", "P=10; S=230.04");
}
@@ -3652,7 +3652,7 @@ public class DruidAdapter2IT {
.explainContains("PLAN=EnumerableInterpreter\n"
+ " DruidQuery(table=[[foodmart, foodmart]],
intervals=[[1900-01-09T00:00:00.000Z/"
+ "2992-01-10T00:00:00.000Z]], filter=[>($1, '10')],
projects=[[$1, $90]], groups=[{0}],"
- + " aggs=[[SUM($1)]], filter=[>($1, 220)], sort0=[0], dir0=[ASC],
fetch=[2])\n")
+ + " aggs=[[SUM($1)]], filter=[>($1, 220.0E0)], sort0=[0],
dir0=[ASC], fetch=[2])\n")
.queryContains(
new
DruidChecker("{'queryType':'groupBy','dataSource':'foodmart','granularity':'all'"));
q.returnsOrdered("P=100; S=343.2", "P=1000; S=532.62");
diff --git a/druid/src/test/java/org/apache/calcite/test/DruidAdapterIT.java
b/druid/src/test/java/org/apache/calcite/test/DruidAdapterIT.java
index 9e140f3293..e47fe4376e 100644
--- a/druid/src/test/java/org/apache/calcite/test/DruidAdapterIT.java
+++ b/druid/src/test/java/org/apache/calcite/test/DruidAdapterIT.java
@@ -2691,14 +2691,14 @@ public class DruidAdapterIT {
+ "EnumerableInterpreter\n"
+ " DruidQuery(table=[[foodmart, foodmart]], "
+ "intervals=[[1900-01-09T00:00:00.000Z/2992-01-10T00:00:00.000Z]],
filter=[>"
- + "($91, 10)], projects=[[$90, IS TRUE(>($91, 10))]], groups=[{}],
aggs=[[SUM($0)"
+ + "($91, 10.0E0)], projects=[[$90, IS TRUE(>($91, 10.0E0))]],
groups=[{}], aggs=[[SUM($0)"
+ "]])";
sql(sql)
.explainContains(expectedSubExplain)
.queryContains(
new DruidChecker("\"queryType\":\"timeseries\"",
"\"filter\":{\"type\":\"bound\","
- +
"\"dimension\":\"store_cost\",\"lower\":\"10\",\"lowerStrict\":true,"
+ +
"\"dimension\":\"store_cost\",\"lower\":\"10.0\",\"lowerStrict\":true,"
+ "\"ordering\":\"numeric\"}"))
.returnsUnordered("EXPR$0=25.06");
}
@@ -2712,14 +2712,14 @@ public class DruidAdapterIT {
+ "EnumerableCalc(expr#0..1=[{inputs}], EXPR$0=[$t1],
product_id=[$t0])\n"
+ " EnumerableInterpreter\n"
+ " DruidQuery(table=[[foodmart, foodmart]],
intervals=[[1900-01-09T00:00:00"
- + ".000Z/2992-01-10T00:00:00.000Z]], filter=[AND(>(CAST($1):INTEGER,
1553), >($91, 5))], "
+ + ".000Z/2992-01-10T00:00:00.000Z]], filter=[AND(>(CAST($1):INTEGER,
1553), >($91, 5.0E0))], "
+ "projects=[[$1, $90]], groups=[{0}], aggs=[[SUM($1)]])";
CalciteAssert.AssertQuery q = sql(sql)
.explainContains(expectedSubExplain)
.queryContains(
new DruidChecker("\"queryType\":\"groupBy\"",
"{\"type\":\"bound\","
- +
"\"dimension\":\"store_cost\",\"lower\":\"5\",\"lowerStrict\":true,"
+ +
"\"dimension\":\"store_cost\",\"lower\":\"5.0\",\"lowerStrict\":true,"
+ "\"ordering\":\"numeric\"}"));
q.returnsUnordered("EXPR$0=10.16; product_id=1554",
"EXPR$0=45.05; product_id=1556",
@@ -2736,7 +2736,7 @@ public class DruidAdapterIT {
sql(sql)
.queryContains(
new DruidChecker("\"queryType\":\"groupBy\"",
"{\"type\":\"bound\","
- +
"\"dimension\":\"store_cost\",\"lower\":\"5\",\"lowerStrict\":true,"
+ +
"\"dimension\":\"store_cost\",\"lower\":\"5.0\",\"lowerStrict\":true,"
+ "\"ordering\":\"numeric\"}"))
.returnsUnordered("EXPR$0=10.6; product_id=1556",
"EXPR$0=10.6; product_id=1556",
@@ -3792,7 +3792,7 @@ public class DruidAdapterIT {
+ "<= ((floor(\\'store_sales\\') * 25) + 2))'}";
final String likeExpressionFilter =
"{'type':'expression','expression':'like(\\'product_id\\'";
final String likeExpressionFilter2 = "1%";
- final String simpleBound =
"{'type':'bound','dimension':'store_cost','lower':'1',"
+ final String simpleBound =
"{'type':'bound','dimension':'store_cost','lower':'1.0',"
+ "'lowerStrict':true,'ordering':'numeric'}";
final String timeSimpleFilter =
"{'type':'bound','dimension':'__time','upper':'1997-01-02T00:00:00.000Z',"
@@ -3812,12 +3812,12 @@ public class DruidAdapterIT {
// 1. https://issues.apache.org/jira/browse/CALCITE-2590
// 2. https://issues.apache.org/jira/browse/CALCITE-2838
final String booleanAsFilter =
"{\"type\":\"bound\",\"dimension\":\"store_sales\","
- + "\"lower\":\"0\",\"lowerStrict\":true,\"ordering\":\"numeric\"}";
+ + "\"lower\":\"0.0\",\"lowerStrict\":true,\"ordering\":\"numeric\"}";
final String plan = "PLAN=EnumerableInterpreter\n"
+ " DruidQuery(table=[[foodmart, foodmart]], "
+ "intervals=[[1900-01-09T00:00:00.000Z/2992-01-10T00:00:00.000Z]], "
+ "filter=[AND(<=(/(+(CAST($1):INTEGER, $90), -($91, 5)),
+(*(FLOOR($90), 25), 2)), "
- + ">($90, 0), LIKE($1, '1%'), >($91, 1), <($0, 1997-01-02 00:00:00), "
+ + ">($90, 0.0E0), LIKE($1, '1%'), >($91, 1.0E0), <($0, 1997-01-02
00:00:00), "
+ "=(EXTRACT(FLAG(MONTH), $0), 1), =(EXTRACT(FLAG(DAY), $0), 1), "
+ "=(+(/(EXTRACT(FLAG(MONTH), $0), 4), 1), 1))], groups=[{}],
aggs=[[COUNT()]])";
sql(sql, FOODMART)
@@ -3841,7 +3841,7 @@ public class DruidAdapterIT {
+ " AND ceil(\"timestamp\" TO SECOND) > CAST('1997-01-01' AS
TIMESTAMP) ";
final String plan = "PLAN=EnumerableInterpreter\n"
+ " DruidQuery(table=[[foodmart, foodmart]],
intervals=[[1997-01-01T00:00:00.001Z/"
- + "1997-01-04T00:00:00.001Z]], filter=[>(CEIL($90), 1)], groups=[{}],
aggs=[[COUNT()]])";
+ + "1997-01-04T00:00:00.001Z]], filter=[>(CEIL($90), 1.0E0)],
groups=[{}], aggs=[[COUNT()]])";
sql(sql, FOODMART)
.explainContains(plan)
.returnsOrdered("EXPR$0=408");
@@ -3923,7 +3923,7 @@ public class DruidAdapterIT {
sql(sql)
.queryContains(
new DruidChecker("\"queryType\":\"groupBy\"",
"{\"type\":\"bound\","
- +
"\"dimension\":\"store_cost\",\"lower\":\"5\",\"lowerStrict\":true,"
+ +
"\"dimension\":\"store_cost\",\"lower\":\"5.0\",\"lowerStrict\":true,"
+ "\"ordering\":\"numeric\"}"))
.runs();
}
@@ -4010,7 +4010,7 @@ public class DruidAdapterIT {
.explainContains("PLAN=EnumerableInterpreter\n"
+ " BindableSort(sort0=[$2], dir0=[ASC], fetch=[3])\n"
+ " DruidQuery(table=[[foodmart, foodmart]],
intervals=[[1900-01-09T00:00:00.000Z/"
- + "2992-01-10T00:00:00.000Z]], filter=[<($90, 20)],
projects=[[+(COS($90), 1), SIN($91),"
+ + "2992-01-10T00:00:00.000Z]], filter=[<($90, 20.0E0)],
projects=[[+(COS($90), 1), SIN($91),"
+ " +(EXTRACT(FLAG(DAY), $0), 1)]])");
}
@@ -4026,7 +4026,7 @@ public class DruidAdapterIT {
.explainContains("PLAN=EnumerableInterpreter\n"
+ " BindableSort(sort0=[$1], dir0=[ASC], fetch=[3])\n"
+ " DruidQuery(table=[[foodmart, foodmart]],
intervals=[[1900-01-09T00:00:00.000Z/"
- + "2992-01-10T00:00:00.000Z]], filter=[<($90, 20)],
projects=[[+(COS(+($90, $91)), 1), "
+ + "2992-01-10T00:00:00.000Z]], filter=[<($90, 20.0E0)],
projects=[[+(COS(+($90, $91)), 1), "
+ "+(EXTRACT(FLAG(DAY), $0), 1)]])");
}
@@ -4166,7 +4166,7 @@ public class DruidAdapterIT {
final String query =
"{'queryType':'timeseries','dataSource':'foodmart','descending':false,"
+
"'granularity':'all','aggregations':[{'type':'filtered','filter':{'type':'or','fields':"
+
"[{'type':'selector','dimension':'state_province','value':'CA'},{'type':'and','fields':"
- +
"[{'type':'bound','dimension':'store_sales','lower':'100','lowerStrict':true,"
+ +
"[{'type':'bound','dimension':'store_sales','lower':'100.0','lowerStrict':true,"
+
"'ordering':'numeric'},{'type':'not','field':{'type':'selector','dimension':'product_id',"
+
"'value':'100'}}]}]},'aggregator':{'type':'filtered','filter':{'type':'not',"
+
"'field':{'type':'selector','dimension':'product_id','value':null}},'aggregator':"
@@ -4326,10 +4326,10 @@ public class DruidAdapterIT {
.explainContains("PLAN=EnumerableInterpreter\n"
+ " DruidQuery(table=[[foodmart, foodmart]],
intervals=[[1900-01-09T00:00:00.000Z/"
+ "2992-01-10T00:00:00.000Z]], projects=[[$1, $90]], groups=[{0}],
aggs=[[SUM($1)]], "
- + "filter=[>($1, 220)], sort0=[0], dir0=[ASC], fetch=[2])")
+ + "filter=[>($1, 220.0E0)], sort0=[0], dir0=[ASC], fetch=[2])")
.queryContains(
new
DruidChecker("'having':{'type':'filter','filter':{'type':'bound',"
- +
"'dimension':'S','lower':'220','lowerStrict':true,'ordering':'numeric'}}"));
+ +
"'dimension':'S','lower':'220.0','lowerStrict':true,'ordering':'numeric'}}"));
q.returnsOrdered("P=1; S=236.55", "P=10; S=230.04");
}
@@ -4341,7 +4341,7 @@ public class DruidAdapterIT {
.explainContains("PLAN=EnumerableInterpreter\n"
+ " DruidQuery(table=[[foodmart, foodmart]],
intervals=[[1900-01-09T00:00:00.000Z/"
+ "2992-01-10T00:00:00.000Z]], filter=[>($1, '10')],
projects=[[$1, $90]], groups=[{0}],"
- + " aggs=[[SUM($1)]], filter=[>($1, 220)], sort0=[0], dir0=[ASC],
fetch=[2])\n")
+ + " aggs=[[SUM($1)]], filter=[>($1, 220.0E0)], sort0=[0],
dir0=[ASC], fetch=[2])\n")
.queryContains(
new
DruidChecker("{'queryType':'groupBy','dataSource':'foodmart','granularity':'all'"));
q.returnsOrdered("P=100; S=343.2", "P=1000; S=532.62");
