This is an automated email from the ASF dual-hosted git repository.
xiong 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 3a9ee36c11 [CALCITE-4993] Simplify EQUALS or NOT-EQUALS with other
number comparison
3a9ee36c11 is described below
commit 3a9ee36c11a2ade12993a49b6fdf37cb4d0fe1f6
Author: Xiong Duan <[email protected]>
AuthorDate: Tue Jun 17 08:47:39 2025 +0800
[CALCITE-4993] Simplify EQUALS or NOT-EQUALS with other number comparison
---
.../calcite/sql/type/SqlTypeFactoryImpl.java | 2 +
.../calcite/sql2rel/StandardConvertletTable.java | 5 +-
.../java/org/apache/calcite/test/JdbcTest.java | 73 ++++++++++++++++------
.../test/MaterializedViewRelOptRulesTest.java | 56 ++++++++---------
.../MaterializedViewSubstitutionVisitorTest.java | 8 +--
.../test/enumerable/EnumerableCorrelateTest.java | 2 +-
.../test/enumerable/EnumerableHashJoinTest.java | 20 +++---
.../java/org/apache/calcite/tools/PlannerTest.java | 10 +--
core/src/test/resources/sql/misc.iq | 4 +-
.../adapter/geode/rel/GeodeBookstoreTest.java | 8 +--
10 files changed, 115 insertions(+), 73 deletions(-)
diff --git
a/core/src/main/java/org/apache/calcite/sql/type/SqlTypeFactoryImpl.java
b/core/src/main/java/org/apache/calcite/sql/type/SqlTypeFactoryImpl.java
index 82fd14d6f4..3dde2b4309 100644
--- a/core/src/main/java/org/apache/calcite/sql/type/SqlTypeFactoryImpl.java
+++ b/core/src/main/java/org/apache/calcite/sql/type/SqlTypeFactoryImpl.java
@@ -341,6 +341,8 @@ private static void assertBasic(SqlTypeName typeName) {
? createSqlType(typeName, type.getPrecision())
: createSqlType(typeName);
type = createTypeWithNullability(type, originalType.isNullable());
+ // update java type's family
+ family = type.getFamily();
}
if (resultType == null) {
diff --git
a/core/src/main/java/org/apache/calcite/sql2rel/StandardConvertletTable.java
b/core/src/main/java/org/apache/calcite/sql2rel/StandardConvertletTable.java
index fc8bde9a84..48e6a0280f 100644
--- a/core/src/main/java/org/apache/calcite/sql2rel/StandardConvertletTable.java
+++ b/core/src/main/java/org/apache/calcite/sql2rel/StandardConvertletTable.java
@@ -21,6 +21,7 @@
import org.apache.calcite.plan.RelOptUtil;
import org.apache.calcite.rel.type.RelDataType;
import org.apache.calcite.rel.type.RelDataTypeFactory;
+import org.apache.calcite.rel.type.RelDataTypeFactoryImpl;
import org.apache.calcite.rel.type.RelDataTypeFamily;
import org.apache.calcite.rel.type.RelDataTypeField;
import org.apache.calcite.rel.type.TimeFrame;
@@ -1310,7 +1311,9 @@ private static List<RexNode>
convertOperands(SqlRexContext cx,
SqlOperandTypeChecker.Consistency consistency, List<RelDataType> types) {
switch (consistency) {
case COMPARE:
- if (SqlTypeUtil.areSameFamily(types)) {
+ if (SqlTypeUtil.areSameFamily(types)
+ && (types.stream().allMatch(RelDataTypeFactoryImpl::isJavaType)
+ || types.stream().noneMatch(RelDataTypeFactoryImpl::isJavaType))) {
// All arguments are of same family. No need for explicit casts.
return null;
}
diff --git a/core/src/test/java/org/apache/calcite/test/JdbcTest.java
b/core/src/test/java/org/apache/calcite/test/JdbcTest.java
index 129556e2b3..a0101cd649 100644
--- a/core/src/test/java/org/apache/calcite/test/JdbcTest.java
+++ b/core/src/test/java/org/apache/calcite/test/JdbcTest.java
@@ -2904,10 +2904,10 @@ private void
checkNullableTimestamp(CalciteAssert.Config config) {
+ " then 'y' else null end)) T\n"
+ "from \"hr\".\"emps\"";
final String plan = ""
- + " String case_when_value;\n"
+ + " String case_when_value;\n"
+ " final org.apache.calcite.test.schemata.hr.Employee
current = (org.apache"
+ ".calcite.test.schemata.hr.Employee) inputEnumerator.current();\n"
- + " if (current.empid > current.deptno * 10) {\n"
+ + " if
(org.apache.calcite.runtime.SqlFunctions.toInt(org.apache.calcite.linq4j.tree.Primitive.integerCast(org.apache.calcite.linq4j.tree.Primitive.INT,
current.empid, java.math.RoundingMode.DOWN)) > current.deptno * 10) {\n"
+ " case_when_value = \"y\";\n"
+ " } else {\n"
+ " case_when_value = null;\n"
@@ -2928,10 +2928,10 @@ private void
checkNullableTimestamp(CalciteAssert.Config config) {
+ " then \"name\" end)) T\n"
+ "from \"hr\".\"emps\"";
final String plan = ""
- + " String case_when_value;\n"
+ + " String case_when_value;\n"
+ " final org.apache.calcite.test.schemata.hr.Employee
current = (org.apache"
+ ".calcite.test.schemata.hr.Employee) inputEnumerator.current();\n"
- + " if (current.empid > current.deptno * 10) {\n"
+ + " if
(org.apache.calcite.runtime.SqlFunctions.toInt(org.apache.calcite.linq4j.tree.Primitive.integerCast(org.apache.calcite.linq4j.tree.Primitive.INT,
current.empid, java.math.RoundingMode.DOWN)) > current.deptno * 10) {\n"
+ " case_when_value = current.name;\n"
+ " } else {\n"
+ " case_when_value = null;\n"
@@ -3442,15 +3442,15 @@ void testInnerJoinValues(String format) {
+ "LogicalProject(deptno=[$0], name=[$1], employees=[$2],
location=[$3])\n"
+ " LogicalFilter(condition=[IN($0, {\n"
+ "LogicalProject(deptno=[$1])\n"
- + " LogicalFilter(condition=[<($0, 150)])\n"
+ + " LogicalFilter(condition=[<(CAST($0):INTEGER NOT NULL,
150)])\n"
+ " LogicalTableScan(table=[[hr, emps]])\n"
+ "})])\n"
- + " LogicalTableScan(table=[[hr, depts]])")
+ + " LogicalTableScan(table=[[hr, depts]])\n")
.explainContains(""
+ "EnumerableHashJoin(condition=[=($0, $5)], joinType=[semi])\n"
+ " EnumerableTableScan(table=[[hr, depts]])\n"
- + " EnumerableCalc(expr#0..4=[{inputs}], expr#5=[150],
expr#6=[<($t0, $t5)], proj#0..4=[{exprs}], $condition=[$t6])\n"
- + " EnumerableTableScan(table=[[hr, emps]])")
+ + " EnumerableCalc(expr#0..4=[{inputs}],
expr#5=[CAST($t0):INTEGER NOT NULL], expr#6=[150], expr#7=[<($t5, $t6)],
proj#0..4=[{exprs}], $condition=[$t7])\n"
+ + " EnumerableTableScan(table=[[hr, emps]])\n\n")
.returnsUnordered(
"deptno=10; name=Sales; employees=[{100, 10, Bill, 10000.0, 1000},
{150, 10, Sebastian, 7000.0, null}]; location={-122, 38}");
}
@@ -4267,12 +4267,11 @@ public void checkOrderBy(final boolean desc,
+ " EnumerableCalc(expr#0..2=[{inputs}], expr#3=[0],
expr#4=[=($t2, $t3)], "
+ "expr#5=[1], expr#6=[=($t2, $t5)], proj#0..1=[{exprs}],
$f2=[$t4], $f3=[$t6])\n"
+ " EnumerableUnion(all=[true])\n"
- + " EnumerableCalc(expr#0..4=[{inputs}], expr#5=[0],
expr#6=[CAST($t1):INTEGER"
- + " NOT NULL], expr#7=[10], expr#8=[=($t6, $t7)], empid=[$t0],
name=[$t2], $f2=[$t5],"
- + " $condition=[$t8])\n"
+ + " EnumerableCalc(expr#0..4=[{inputs}], expr#5=[0],
expr#6=[CAST($t1):INTEGER NOT NULL], expr#7=[10], expr#8=[=($t6, $t7)],
empid=[$t0], name=[$t2], $f2=[$t5], "
+ + "$condition=[$t8])\n"
+ " EnumerableTableScan(table=[[hr, emps]])\n"
- + " EnumerableCalc(expr#0..4=[{inputs}], expr#5=[1],
expr#6=[150], expr#7=[>="
- + "($t0, $t6)], empid=[$t0], name=[$t2], $f2=[$t5],
$condition=[$t7])\n"
+ + " EnumerableCalc(expr#0..4=[{inputs}], expr#5=[1],
expr#6=[CAST($t0):INTEGER NOT NULL], expr#7=[150], expr#8=[>="
+ + "($t6, $t7)], empid=[$t0], name=[$t2], $f2=[$t5],
$condition=[$t8])\n"
+ " EnumerableTableScan(table=[[hr, emps]])\n")
.returnsUnordered("empid=100; name=Bill",
"empid=110; name=Theodore");
@@ -4327,7 +4326,7 @@ public void checkOrderBy(final boolean desc,
.explainContains(""
+ "PLAN=EnumerableCalc(expr#0..1=[{inputs}], expr#2=[0],
expr#3=[=($t0, $t2)], expr#4=[null:JavaType(class java.lang.Integer)],
expr#5=[CASE($t3, $t4, $t1)], expr#6=[/($t5, $t0)],
expr#7=[CAST($t6):JavaType(class java.lang.Integer)], CS=[$t0], C=[$t0],
S=[$t5], A=[$t7])\n"
+ " EnumerableAggregate(group=[{}], CS=[COUNT()],
S=[$SUM0($1)])\n"
- + " EnumerableCalc(expr#0..4=[{inputs}], expr#5=[0],
expr#6=[<($t1, $t5)], proj#0..4=[{exprs}], $condition=[$t6])\n"
+ + " EnumerableCalc(expr#0..4=[{inputs}],
expr#5=[CAST($t1):INTEGER NOT NULL], expr#6=[0], expr#7=[<($t5, $t6)],
proj#0..4=[{exprs}], $condition=[$t7])\n"
+ " EnumerableTableScan(table=[[hr, emps]])\n")
.returns("CS=0; C=0; S=null; A=null\n");
}
@@ -4343,7 +4342,7 @@ public void checkOrderBy(final boolean desc,
.explainContains(""
+ "PLAN=EnumerableCalc(expr#0=[{inputs}], CS=[$t0], CS2=[$t0])\n"
+ " EnumerableAggregate(group=[{}], CS=[COUNT()])\n"
- + " EnumerableCalc(expr#0..4=[{inputs}], expr#5=[0],
expr#6=[<($t1, $t5)], proj#0..4=[{exprs}], $condition=[$t6])\n"
+ + " EnumerableCalc(expr#0..4=[{inputs}],
expr#5=[CAST($t1):INTEGER NOT NULL], expr#6=[0], expr#7=[<($t5, $t6)],
proj#0..4=[{exprs}], $condition=[$t7])\n"
+ " EnumerableTableScan(table=[[hr, emps]])\n")
.returns("CS=0; CS2=0\n");
}
@@ -5242,7 +5241,7 @@ private void startOfGroupStep3(String startOfGroup) {
+ "where \"empid\" > 10")
.convertContains(""
+ "LogicalProject(name=[$2], EXPR$1=[+(COUNT($3) OVER (PARTITION
BY $1), 1)])\n"
- + " LogicalFilter(condition=[>($0, 10)])\n"
+ + " LogicalFilter(condition=[>(CAST($0):INTEGER NOT NULL,
10)])\n"
+ " LogicalProject(empid=[$0], deptno=[$1], name=[$2],
commission=[$4])\n"
+ " LogicalTableScan(table=[[hr, emps]])\n");
}
@@ -6612,7 +6611,7 @@ private CalciteAssert.AssertThat modelWithView(String
view,
.explainMatches(" without implementation ",
checkResult("PLAN="
+ "LogicalProject(empid=[$0], deptno=[$1])\n"
- + " LogicalFilter(condition=[>($1, 10)])\n"
+ + " LogicalFilter(condition=[>(CAST($1):INTEGER NOT NULL,
10)])\n"
+ " LogicalProject(empid=[$0], deptno=[$1])\n"
+ " LogicalTableScan(table=[[adhoc, EMPLOYEES]])\n\n"));
with.query("select * from \"adhoc\".\"EMPLOYEES\" where exists (select *
from \"adhoc\".V)")
@@ -6668,7 +6667,7 @@ private CalciteAssert.AssertThat modelWithView(String
view,
checkResult("PLAN="
+ "LogicalProject(empid=[$0], deptno=[$1], name=[$2], "
+ "salary=[$3], commission=[$4])\n"
- + " LogicalFilter(condition=[>($1, 10)])\n"
+ + " LogicalFilter(condition=[>(CAST($1):INTEGER NOT NULL,
10)])\n"
+ " LogicalSort(sort0=[$1], dir0=[ASC])\n"
+ " LogicalProject(empid=[$0], deptno=[$1], name=[$2], "
+ "salary=[$3], commission=[$4])\n"
@@ -6681,7 +6680,7 @@ private CalciteAssert.AssertThat modelWithView(String
view,
checkResult("PLAN="
+ "LogicalProject(empid=[$0], deptno=[$1], name=[$2], "
+ "salary=[$3], commission=[$4])\n"
- + " LogicalFilter(condition=[>($1, 10)])\n"
+ + " LogicalFilter(condition=[>(CAST($1):INTEGER NOT NULL,
10)])\n"
+ " LogicalSort(sort0=[$1], dir0=[ASC])\n"
+ " LogicalProject(empid=[$0], deptno=[$1], name=[$2],
salary=[$3], "
+ "commission=[$4])\n"
@@ -8173,6 +8172,42 @@ void checkCalciteSchemaGetSubSchemaMap(boolean cache) {
.returnsUnordered("DID=1", "DID=2");
}
+ /**
+ * Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-4993";>[CALCITE-4993]
+ * Simplify EQUALS or NOT-EQUALS with other number comparison</a>.
+ */
+ @Test void testJavaTypeSimplifyEqWithOtherNumberComparison() {
+ CalciteAssert.hr()
+ .query("select \"empid\", \"deptno\" from \"hr\".\"emps\"\n"
+ + "where \"empid\" <> 5 and \"empid\"> 3 and \"empid\"< 10")
+ .explainContains(""
+ + "PLAN=EnumerableCalc(expr#0..4=[{inputs}],
expr#5=[CAST($t0):INTEGER NOT NULL], expr#6=[Sarg[(3..5), (5..10)]],
expr#7=[SEARCH($t5, $t6)], "
+ + "proj#0..1=[{exprs}], $condition=[$t7])\n"
+ + " EnumerableTableScan(table=[[hr, emps]])\n\n")
+ .returns("");
+ }
+
+ @Test void testJavaTypeSimplifyEqWithOtherNumberComparison2() {
+ CalciteAssert.hr()
+ .query("select \"empid\", \"deptno\" from \"hr\".\"emps\"\n"
+ + "where \"empid\" = 5 and \"empid\"> 3 and \"empid\"< 10")
+ .explainContains(""
+ + "PLAN=EnumerableCalc(expr#0..4=[{inputs}],
expr#5=[CAST($t0):INTEGER NOT NULL], expr#6=[5], expr#7=[=($t5, $t6)], "
+ + "proj#0..1=[{exprs}], $condition=[$t7])\n"
+ + " EnumerableTableScan(table=[[hr, emps]])\n\n")
+ .returns("");
+ }
+
+ @Test void testJavaTypeSimplifyEqWithOtherNumberComparison3() {
+ CalciteAssert.hr()
+ .query("select \"empid\", \"deptno\" from \"hr\".\"emps\"\n"
+ + "where \"empid\" = 5 and \"empid\"> 6 and \"empid\"< 10")
+ .explainContains(""
+ + "PLAN=EnumerableValues(tuples=[[]])\n\n")
+ .returns("");
+ }
+
/**
* Test case for
* <a href="https://issues.apache.org/jira/browse/CALCITE-596";>[CALCITE-596]
diff --git
a/core/src/test/java/org/apache/calcite/test/MaterializedViewRelOptRulesTest.java
b/core/src/test/java/org/apache/calcite/test/MaterializedViewRelOptRulesTest.java
index d15fdbeafa..a7a088081e 100644
---
a/core/src/test/java/org/apache/calcite/test/MaterializedViewRelOptRulesTest.java
+++
b/core/src/test/java/org/apache/calcite/test/MaterializedViewRelOptRulesTest.java
@@ -131,7 +131,7 @@ protected final MaterializedViewFixture sql(String
materialize,
"select \"deptno\" from \"emps\" where \"deptno\" > 10 group by
\"deptno\"")
.checkingThatResultContains(""
+ "EnumerableAggregate(group=[{1}])\n"
- + " EnumerableCalc(expr#0..1=[{inputs}], expr#2=[10],
expr#3=[<($t2, $t1)], proj#0..1=[{exprs}], $condition=[$t3])\n"
+ + " EnumerableCalc(expr#0..1=[{inputs}], expr#2=[10],
expr#3=[CAST($t1):INTEGER NOT NULL], expr#4=[<($t2, $t3)], proj#0..1=[{exprs}],
$condition=[$t4])\n"
+ " EnumerableTableScan(table=[[hr, MV0]])")
.ok();
}
@@ -196,8 +196,8 @@ protected final MaterializedViewFixture sql(String
materialize,
+ "from \"emps\" where \"deptno\" > 10 group by \"deptno\"")
.checkingThatResultContains(""
+ "EnumerableAggregate(group=[{1}], S=[$SUM0($3)])\n"
- + " EnumerableCalc(expr#0..3=[{inputs}], expr#4=[10],
expr#5=[<($t4, $t1)], "
- + "proj#0..3=[{exprs}], $condition=[$t5])\n"
+ + " EnumerableCalc(expr#0..3=[{inputs}], expr#4=[10],
expr#5=[CAST($t1):INTEGER NOT NULL], expr#6=[<($t4, $t5)], "
+ + "proj#0..3=[{exprs}], $condition=[$t6])\n"
+ " EnumerableTableScan(table=[[hr, MV0]])")
.ok();
}
@@ -208,11 +208,11 @@ protected final MaterializedViewFixture sql(String
materialize,
"select \"deptno\", sum(\"empid\") + 1 as s\n"
+ "from \"emps\" where \"deptno\" > 10 group by \"deptno\"")
.checkingThatResultContains(""
- + "EnumerableCalc(expr#0..1=[{inputs}], expr#2=[1], expr#3=[+($t1,
$t2)],"
- + " deptno=[$t0], S=[$t3])\n"
+ + "EnumerableCalc(expr#0..1=[{inputs}], expr#2=[1], expr#3=[+($t1,
$t2)], "
+ + "deptno=[$t0], S=[$t3])\n"
+ " EnumerableAggregate(group=[{1}], agg#0=[$SUM0($3)])\n"
- + " EnumerableCalc(expr#0..3=[{inputs}], expr#4=[10],
expr#5=[<($t4, $t1)], "
- + "proj#0..3=[{exprs}], $condition=[$t5])\n"
+ + " EnumerableCalc(expr#0..3=[{inputs}], expr#4=[10],
expr#5=[CAST($t1):INTEGER NOT NULL], expr#6=[<($t4, $t5)], "
+ + "proj#0..3=[{exprs}], $condition=[$t6])\n"
+ " EnumerableTableScan(table=[[hr, MV0]])")
.ok();
}
@@ -234,8 +234,8 @@ protected final MaterializedViewFixture sql(String
materialize,
+ "EnumerableCalc(expr#0..1=[{inputs}], expr#2=[1], expr#3=[+($t0,
$t2)], "
+ "expr#4=[+($t1, $t2)], EXPR$0=[$t3], S=[$t4])\n"
+ " EnumerableAggregate(group=[{1}], agg#0=[$SUM0($3)])\n"
- + " EnumerableCalc(expr#0..3=[{inputs}], expr#4=[10],
expr#5=[<($t4, $t1)], "
- + "proj#0..3=[{exprs}], $condition=[$t5])\n"
+ + " EnumerableCalc(expr#0..3=[{inputs}], expr#4=[10],
expr#5=[CAST($t1):INTEGER NOT NULL], expr#6=[<($t4, $t5)], "
+ + "proj#0..3=[{exprs}], $condition=[$t6])\n"
+ " EnumerableTableScan(table=[[hr, MV0]])")
.ok();
}
@@ -372,8 +372,8 @@ protected final MaterializedViewFixture sql(String
materialize,
+ "join \"depts\" using (\"deptno\") where \"depts\".\"deptno\" >
20\n"
+ "group by \"empid\", \"depts\".\"deptno\"")
.checkingThatResultContains(""
- + "EnumerableCalc(expr#0..1=[{inputs}], expr#2=[20],
expr#3=[<($t2, $t1)], "
- + "empid=[$t0], $condition=[$t3])\n"
+ + "EnumerableCalc(expr#0..1=[{inputs}], expr#2=[20],
expr#3=[CAST($t1):INTEGER NOT NULL], "
+ + "expr#4=[<($t2, $t3)], empid=[$t0], $condition=[$t4])\n"
+ " EnumerableTableScan(table=[[hr, MV0]])")
.ok();
}
@@ -386,8 +386,8 @@ protected final MaterializedViewFixture sql(String
materialize,
+ "join \"depts\" using (\"deptno\") where \"depts\".\"deptno\" >
20\n"
+ "group by \"empid\", \"depts\".\"deptno\"")
.checkingThatResultContains(""
- + "EnumerableCalc(expr#0..1=[{inputs}], expr#2=[20],
expr#3=[<($t2, $t0)], "
- + "empid=[$t1], $condition=[$t3])\n"
+ + "EnumerableCalc(expr#0..1=[{inputs}], expr#2=[20],
expr#3=[CAST($t0):INTEGER NOT NULL], expr#4=[<($t2, $t3)], "
+ + "empid=[$t1], $condition=[$t4])\n"
+ " EnumerableTableScan(table=[[hr, MV0]])")
.ok();
}
@@ -411,8 +411,8 @@ protected final MaterializedViewFixture sql(String
materialize,
+ "join \"depts\" using (\"deptno\") where \"depts\".\"deptno\" >
20\n"
+ "group by \"empid\", \"depts\".\"deptno\"")
.checkingThatResultContains(""
- + "EnumerableCalc(expr#0..1=[{inputs}], expr#2=[20],
expr#3=[<($t2, $t1)], "
- + "empid=[$t0], $condition=[$t3])\n"
+ + "EnumerableCalc(expr#0..1=[{inputs}], expr#2=[20],
expr#3=[CAST($t1):INTEGER NOT NULL], expr#4=[<($t2, $t3)], "
+ + "empid=[$t0], $condition=[$t4])\n"
+ " EnumerableTableScan(table=[[hr, MV0]])")
.ok();
}
@@ -425,8 +425,8 @@ protected final MaterializedViewFixture sql(String
materialize,
+ "join \"emps\" using (\"deptno\") where \"emps\".\"empid\" >
15\n"
+ "group by \"depts\".\"deptno\", \"emps\".\"empid\"")
.checkingThatResultContains(""
- + "EnumerableCalc(expr#0..1=[{inputs}], expr#2=[15],
expr#3=[<($t2, $t1)], "
- + "deptno=[$t0], $condition=[$t3])\n"
+ + "EnumerableCalc(expr#0..1=[{inputs}], expr#2=[15],
expr#3=[CAST($t1):INTEGER NOT NULL], expr#4=[<($t2, $t3)], "
+ + "deptno=[$t0], $condition=[$t4])\n"
+ " EnumerableTableScan(table=[[hr, MV0]])")
.ok();
}
@@ -440,8 +440,8 @@ protected final MaterializedViewFixture sql(String
materialize,
+ "group by \"depts\".\"deptno\"")
.checkingThatResultContains(""
+ "EnumerableAggregate(group=[{0}])\n"
- + " EnumerableCalc(expr#0..1=[{inputs}], expr#2=[15],
expr#3=[<($t2, $t1)], "
- + "proj#0..1=[{exprs}], $condition=[$t3])\n"
+ + " EnumerableCalc(expr#0..1=[{inputs}], expr#2=[15],
expr#3=[CAST($t1):INTEGER NOT NULL], expr#4=[<($t2, $t3)], "
+ + "proj#0..1=[{exprs}], $condition=[$t4])\n"
+ " EnumerableTableScan(table=[[hr, MV0]])")
.ok();
}
@@ -465,7 +465,7 @@ protected final MaterializedViewFixture sql(String
materialize,
"EnumerableUnion(all=[true])",
"EnumerableAggregate(group=[{2}])",
"EnumerableTableScan(table=[[hr, MV0]])",
- "expr#5=[Sarg[(10..11]]], expr#6=[SEARCH($t0, $t5)]")
+ "expr#6=[Sarg[(10..11]]], expr#7=[SEARCH($t5, $t6)]")
.ok();
}
@@ -506,7 +506,7 @@ protected final MaterializedViewFixture sql(String
materialize,
"EnumerableUnion(all=[true])",
"EnumerableAggregate(group=[{2}])",
"EnumerableTableScan(table=[[hr, MV0]])",
- "expr#5=[Sarg[(10..11], [19..20)]], expr#6=[SEARCH($t0, $t5)]")
+ "expr#6=[Sarg[(10..11], [19..20)]], expr#7=[SEARCH($t5, $t6)]")
.ok();
}
@@ -582,8 +582,8 @@ protected final MaterializedViewFixture sql(String
materialize,
+ "where \"emps\".\"deptno\" > 10 group by \"depts\".\"deptno\"")
.checkingThatResultContains(""
+ "EnumerableAggregate(group=[{1}], S=[$SUM0($3)])\n"
- + " EnumerableCalc(expr#0..3=[{inputs}], expr#4=[10],
expr#5=[<($t4, $t1)], "
- + "proj#0..3=[{exprs}], $condition=[$t5])\n"
+ + " EnumerableCalc(expr#0..3=[{inputs}], expr#4=[10],
expr#5=[CAST($t1):INTEGER NOT NULL], expr#6=[<($t4, $t5)], "
+ + "proj#0..3=[{exprs}], $condition=[$t6])\n"
+ " EnumerableTableScan(table=[[hr, MV0]])")
.ok();
}
@@ -599,8 +599,8 @@ protected final MaterializedViewFixture sql(String
materialize,
+ "EnumerableCalc(expr#0..1=[{inputs}], expr#2=[1], expr#3=[+($t1,
$t2)], "
+ "deptno=[$t0], S=[$t3])\n"
+ " EnumerableAggregate(group=[{1}], agg#0=[$SUM0($3)])\n"
- + " EnumerableCalc(expr#0..3=[{inputs}], expr#4=[10],
expr#5=[<($t4, $t1)], "
- + "proj#0..3=[{exprs}], $condition=[$t5])\n"
+ + " EnumerableCalc(expr#0..3=[{inputs}], expr#4=[10],
expr#5=[CAST($t1):INTEGER NOT NULL], expr#6=[<($t4, $t5)], "
+ + "proj#0..3=[{exprs}], $condition=[$t6])\n"
+ " EnumerableTableScan(table=[[hr, MV0]])")
.ok();
}
@@ -712,7 +712,7 @@ protected final MaterializedViewFixture sql(String
materialize,
"EnumerableAggregate(group=[{2}], agg#0=[COUNT()])",
"EnumerableAggregate(group=[{1}], agg#0=[$SUM0($2)])",
"EnumerableTableScan(table=[[hr, MV0]])",
- "expr#5=[Sarg[(10..11], [19..20)]], expr#6=[SEARCH($t0, $t5)]")
+ "expr#6=[Sarg[(10..11], [19..20)]], expr#7=[SEARCH($t5, $t6)]")
.ok();
}
@@ -837,7 +837,7 @@ protected final MaterializedViewFixture sql(String
materialize,
+ "join \"depts\" using (\"deptno\") where \"empid\" > 1")
.checkingThatResultContains(""
+ "EnumerableCalc(expr#0=[{inputs}],
expr#1=[CAST($t0):JavaType(int) NOT NULL], "
- + "expr#2=[1], expr#3=[<($t2, $t1)], EXPR$0=[$t1],
$condition=[$t3])\n"
+ + "expr#2=[1], expr#3=[CAST($t1):INTEGER NOT NULL], expr#4=[<($t2,
$t3)], EXPR$0=[$t1], $condition=[$t4])\n"
+ " EnumerableTableScan(table=[[hr, MV0]])")
.ok();
}
@@ -916,7 +916,7 @@ protected final MaterializedViewFixture sql(String
materialize,
+ "where \"depts\".\"deptno\" > 10")
.checkingThatResultContains("EnumerableUnion(all=[true])",
"EnumerableTableScan(table=[[hr, MV0]])",
- "expr#5=[Sarg[(10..30]]], expr#6=[SEARCH($t0, $t5)]")
+ "expr#6=[Sarg[(10..30]]], expr#7=[SEARCH($t5, $t6)]")
.ok();
}
diff --git
a/core/src/test/java/org/apache/calcite/test/MaterializedViewSubstitutionVisitorTest.java
b/core/src/test/java/org/apache/calcite/test/MaterializedViewSubstitutionVisitorTest.java
index 8e1419e916..22fa948ed6 100644
---
a/core/src/test/java/org/apache/calcite/test/MaterializedViewSubstitutionVisitorTest.java
+++
b/core/src/test/java/org/apache/calcite/test/MaterializedViewSubstitutionVisitorTest.java
@@ -862,9 +862,9 @@ protected final MaterializedViewFixture sql(String
materialize,
sql(m, q)
.checkingThatResultContains(""
+ "LogicalUnion(all=[true])\n"
- + " LogicalCalc(expr#0..4=[{inputs}], expr#5=[300],
expr#6=[>($t0, $t5)], proj#0..4=[{exprs}], $condition=[$t6])\n"
+ + " LogicalCalc(expr#0..4=[{inputs}], expr#5=[CAST($t0):INTEGER
NOT NULL], expr#6=[300], expr#7=[>($t5, $t6)], proj#0..4=[{exprs}],
$condition=[$t7])\n"
+ " LogicalTableScan(table=[[hr, emps]])\n"
- + " LogicalCalc(expr#0..4=[{inputs}], expr#5=[200],
expr#6=[<($t0, $t5)], proj#0..4=[{exprs}], $condition=[$t6])\n"
+ + " LogicalCalc(expr#0..4=[{inputs}], expr#5=[200],
expr#6=[CAST($t0):INTEGER NOT NULL], expr#7=[>($t5, $t6)], proj#0..4=[{exprs}],
$condition=[$t7])\n"
+ " EnumerableTableScan(table=[[hr, MV0]])")
.ok();
}
@@ -887,9 +887,9 @@ protected final MaterializedViewFixture sql(String
materialize,
.checkingThatResultContains(""
+ "LogicalCalc(expr#0..9=[{inputs}], proj#0..4=[{exprs}],
deptno0=[$t6], name0=[$t7], salary0=[$t8], commission0=[$t9])\n"
+ " LogicalJoin(condition=[=($0, $5)], joinType=[inner])\n"
- + " LogicalCalc(expr#0..4=[{inputs}], expr#5=[300],
expr#6=[<($t0, $t5)], proj#0..4=[{exprs}], $condition=[$t6])\n"
+ + " LogicalCalc(expr#0..4=[{inputs}], expr#5=[300],
expr#6=[CAST($t0):INTEGER NOT NULL], expr#7=[>($t5, $t6)], proj#0..4=[{exprs}],
$condition=[$t7])\n"
+ " EnumerableTableScan(table=[[hr, MV0]])\n"
- + " LogicalCalc(expr#0..4=[{inputs}], expr#5=[200],
expr#6=[<($t0, $t5)], proj#0..4=[{exprs}], $condition=[$t6])\n"
+ + " LogicalCalc(expr#0..4=[{inputs}], expr#5=[200],
expr#6=[CAST($t0):INTEGER NOT NULL], expr#7=[>($t5, $t6)], proj#0..4=[{exprs}],
$condition=[$t7])\n"
+ " EnumerableTableScan(table=[[hr, MV0]])")
.ok();
}
diff --git
a/core/src/test/java/org/apache/calcite/test/enumerable/EnumerableCorrelateTest.java
b/core/src/test/java/org/apache/calcite/test/enumerable/EnumerableCorrelateTest.java
index 19cbdb5b8f..9ea76b84e8 100644
---
a/core/src/test/java/org/apache/calcite/test/enumerable/EnumerableCorrelateTest.java
+++
b/core/src/test/java/org/apache/calcite/test/enumerable/EnumerableCorrelateTest.java
@@ -131,7 +131,7 @@ class EnumerableCorrelateTest {
+ " EnumerableCorrelate(correlation=[$cor1], joinType=[inner],
requiredColumns=[{0}])\n"
+ " EnumerableAggregate(group=[{0}])\n"
+ " EnumerableTableScan(table=[[s, depts]])\n"
- + " EnumerableCalc(expr#0..4=[{inputs}], expr#5=[$cor1],
expr#6=[$t5.deptno], expr#7=[=($t1, $t6)], expr#8=[100], expr#9=[>($t0, $t8)],
expr#10=[AND($t7, $t9)], proj#0..2=[{exprs}], $condition=[$t10])\n"
+ + " EnumerableCalc(expr#0..4=[{inputs}], expr#5=[$cor1],
expr#6=[$t5.deptno], expr#7=[=($t1, $t6)], expr#8=[CAST($t0):INTEGER NOT NULL],
expr#9=[100], expr#10=[>($t8, $t9)], expr#11=[AND($t7, $t10)],
proj#0..2=[{exprs}], $condition=[$t11])\n"
+ " EnumerableTableScan(table=[[s, emps]])")
.returnsUnordered(
"empid=110; name=Theodore",
diff --git
a/core/src/test/java/org/apache/calcite/test/enumerable/EnumerableHashJoinTest.java
b/core/src/test/java/org/apache/calcite/test/enumerable/EnumerableHashJoinTest.java
index 1431d7fc35..614ed98e03 100644
---
a/core/src/test/java/org/apache/calcite/test/enumerable/EnumerableHashJoinTest.java
+++
b/core/src/test/java/org/apache/calcite/test/enumerable/EnumerableHashJoinTest.java
@@ -105,9 +105,10 @@ class EnumerableHashJoinTest {
+ ".empid>d.deptno")
.withHook(Hook.PLANNER, (Consumer<RelOptPlanner>) planner ->
planner.removeRule(EnumerableRules.ENUMERABLE_MERGE_JOIN_RULE))
- .explainContains("EnumerableCalc(expr#0..4=[{inputs}], empid=[$t0], "
+ .explainContains(""
+ + "EnumerableCalc(expr#0..4=[{inputs}], empid=[$t0], "
+ "name=[$t2], dept=[$t4])\n"
- + " EnumerableHashJoin(condition=[AND(=($1, $3), <($0, 150), >"
+ + " EnumerableHashJoin(condition=[AND(=($1, $3),
<(CAST($0):INTEGER NOT NULL, 150), >"
+ "($0, $3))], joinType=[left])\n"
+ " EnumerableCalc(expr#0..4=[{inputs}], proj#0..2=[{exprs}])\n"
+ " EnumerableTableScan(table=[[s, emps]])\n"
@@ -125,11 +126,12 @@ class EnumerableHashJoinTest {
.query(
"select e.empid, e.name, d.name as dept from emps e right outer "
+ "join depts d on e.deptno=d.deptno and e.empid<150")
- .explainContains("EnumerableCalc(expr#0..4=[{inputs}], empid=[$t0], "
+ .explainContains(""
+ + "EnumerableCalc(expr#0..4=[{inputs}], empid=[$t0], "
+ "name=[$t2], dept=[$t4])\n"
+ " EnumerableHashJoin(condition=[=($1, $3)], joinType=[right])\n"
- + " EnumerableCalc(expr#0..4=[{inputs}], expr#5=[150], "
- + "expr#6=[<($t0, $t5)], proj#0..2=[{exprs}], $condition=[$t6])\n"
+ + " EnumerableCalc(expr#0..4=[{inputs}],
expr#5=[CAST($t0):INTEGER NOT NULL], expr#6=[150], "
+ + "expr#7=[<($t5, $t6)], proj#0..2=[{exprs}], $condition=[$t7])\n"
+ " EnumerableTableScan(table=[[s, emps]])\n"
+ " EnumerableCalc(expr#0..3=[{inputs}], proj#0..1=[{exprs}])\n"
+ " EnumerableTableScan(table=[[s, depts]])\n")
@@ -205,11 +207,11 @@ class EnumerableHashJoinTest {
.query(
"select e.empid, e.name, d.name as dept from emps e join depts d"
+ " on e.deptno=d.deptno and e.empid<150 and e.empid>d.deptno")
- .explainContains("EnumerableCalc(expr#0..4=[{inputs}], empid=[$t0],
name=[$t2], "
- + "dept=[$t4])\n"
+ .explainContains(""
+ + "EnumerableCalc(expr#0..4=[{inputs}], empid=[$t0], name=[$t2],
dept=[$t4])\n"
+ " EnumerableHashJoin(condition=[AND(=($1, $3), >($0, $3))],
joinType=[inner])\n"
- + " EnumerableCalc(expr#0..4=[{inputs}], expr#5=[150],
expr#6=[<($t0, $t5)], "
- + "proj#0..2=[{exprs}], $condition=[$t6])\n"
+ + " EnumerableCalc(expr#0..4=[{inputs}],
expr#5=[CAST($t0):INTEGER NOT NULL], expr#6=[150], expr#7=[<($t5, $t6)], "
+ + "proj#0..2=[{exprs}], $condition=[$t7])\n"
+ " EnumerableTableScan(table=[[s, emps]])\n"
+ " EnumerableCalc(expr#0..3=[{inputs}], proj#0..1=[{exprs}])\n"
+ " EnumerableTableScan(table=[[s, depts]])\n")
diff --git a/core/src/test/java/org/apache/calcite/tools/PlannerTest.java
b/core/src/test/java/org/apache/calcite/tools/PlannerTest.java
index 765c65d86c..9a367f37ab 100644
--- a/core/src/test/java/org/apache/calcite/tools/PlannerTest.java
+++ b/core/src/test/java/org/apache/calcite/tools/PlannerTest.java
@@ -314,7 +314,7 @@ private void checkMetadataPredicates(String sql,
"select * from \"emps\" where \"deptno\" < 10\n"
+ "union all\n"
+ "select * from \"emps\" where \"empid\" > 2",
- "[OR(<($1, 10), >($0, 2))]");
+ "[OR(<(CAST($1):INTEGER NOT NULL, 10), >(CAST($0):INTEGER NOT NULL,
2))]");
}
/** Test case for
@@ -333,7 +333,7 @@ private void checkMetadataPredicates(String sql,
"select * from \"emps\" where \"deptno\" < 10\n"
+ "union all\n"
+ "select * from \"emps\" where \"deptno\" < 10 and \"empid\" > 1",
- "[<($1, 10)]");
+ "[<(CAST($1):INTEGER NOT NULL, 10)]");
}
@Test void testMetadataUnionPredicates4() throws Exception {
@@ -341,14 +341,14 @@ private void checkMetadataPredicates(String sql,
"select * from \"emps\" where \"deptno\" < 10\n"
+ "union all\n"
+ "select * from \"emps\" where \"deptno\" < 10 or \"empid\" > 1",
- "[OR(<($1, 10), >($0, 1))]");
+ "[OR(<(CAST($1):INTEGER NOT NULL, 10), >(CAST($0):INTEGER NOT NULL,
1))]");
}
@Test void testMetadataUnionPredicates5() throws Exception {
final String sql = "select * from \"emps\" where \"deptno\" < 10\n"
+ "union all\n"
+ "select * from \"emps\" where \"deptno\" < 10 and false";
- checkMetadataPredicates(sql, "[<($1, 10)]");
+ checkMetadataPredicates(sql, "[<(CAST($1):INTEGER NOT NULL, 10)]");
}
/** Tests predicates that can be pulled-up from an Aggregate with
@@ -374,7 +374,7 @@ private void checkMetadataPredicates(String sql,
final String sql = "select \"deptno\", count(\"deptno\")\n"
+ "from \"emps\" where \"deptno\" > 10\n"
+ "group by \"deptno\"";
- checkMetadataPredicates(sql, "[>($0, 10)]");
+ checkMetadataPredicates(sql, "[>(CAST($0):INTEGER NOT NULL, 10)]");
}
/** Unit test that parses, validates, converts and plans. */
diff --git a/core/src/test/resources/sql/misc.iq
b/core/src/test/resources/sql/misc.iq
index 073d9b4803..5b30a18535 100644
--- a/core/src/test/resources/sql/misc.iq
+++ b/core/src/test/resources/sql/misc.iq
@@ -567,7 +567,7 @@ EnumerableCalc(expr#0..1=[{inputs}], deptno=[$t0])
EnumerableCalc(expr#0..3=[{inputs}], deptno=[$t0])
EnumerableTableScan(table=[[hr, depts]])
EnumerableAggregate(group=[{0}])
- EnumerableCalc(expr#0..4=[{inputs}], expr#5=[true], expr#6=[0],
expr#7=[<($t0, $t6)], i=[$t5], $condition=[$t7])
+ EnumerableCalc(expr#0..4=[{inputs}], expr#5=[true],
expr#6=[CAST($t0):INTEGER NOT NULL], expr#7=[0], expr#8=[<($t6, $t7)], i=[$t5],
$condition=[$t8])
EnumerableTableScan(table=[[hr, emps]])
!plan
@@ -589,7 +589,7 @@ EnumerableCalc(expr#0..1=[{inputs}], expr#2=[IS NULL($t1)],
deptno=[$t0], $condi
EnumerableCalc(expr#0..3=[{inputs}], deptno=[$t0])
EnumerableTableScan(table=[[hr, depts]])
EnumerableAggregate(group=[{0}])
- EnumerableCalc(expr#0..4=[{inputs}], expr#5=[true], expr#6=[0],
expr#7=[<($t0, $t6)], i=[$t5], $condition=[$t7])
+ EnumerableCalc(expr#0..4=[{inputs}], expr#5=[true],
expr#6=[CAST($t0):INTEGER NOT NULL], expr#7=[0], expr#8=[<($t6, $t7)], i=[$t5],
$condition=[$t8])
EnumerableTableScan(table=[[hr, emps]])
!plan
diff --git
a/geode/src/test/java/org/apache/calcite/adapter/geode/rel/GeodeBookstoreTest.java
b/geode/src/test/java/org/apache/calcite/adapter/geode/rel/GeodeBookstoreTest.java
index d6efd09723..92b0c2a33a 100644
---
a/geode/src/test/java/org/apache/calcite/adapter/geode/rel/GeodeBookstoreTest.java
+++
b/geode/src/test/java/org/apache/calcite/adapter/geode/rel/GeodeBookstoreTest.java
@@ -98,7 +98,7 @@ private CalciteAssert.AssertThat calciteAssert() {
+ "retailCost=34.99; yearPublished=2011; author=Daisy Mae West;
title=A Treatise of "
+ "Treatises\n")
.explainContains("PLAN=GeodeToEnumerableConverter\n"
- + " GeodeFilter(condition=[SEARCH($0, Sarg[(122..123]])])\n"
+ + " GeodeFilter(condition=[SEARCH(CAST($0):INTEGER,
Sarg[(122..123]])])\n"
+ " GeodeTableScan(table=[[geode, BookMaster]])")
.queryContains(
GeodeAssertions.query("SELECT * FROM /BookMaster "
@@ -131,13 +131,13 @@ private CalciteAssert.AssertThat calciteAssert() {
.returnsUnordered("author=Jim Heavisides", "author=Daisy Mae West")
.explainContains("PLAN=GeodeToEnumerableConverter\n"
+ " GeodeProject(author=[$4])\n"
- + " GeodeFilter(condition=[OR(AND(>($0, 123),
=(CAST($0):INTEGER, 789)), "
+ + " GeodeFilter(condition=[OR(=(CAST($0):INTEGER, 789), "
+ "=(CAST($4):VARCHAR, 'Daisy Mae West'))])\n"
+ " GeodeTableScan(table=[[geode, BookMaster]])\n"
+ "\n")
.queryContains(
GeodeAssertions.query("SELECT author AS author FROM /BookMaster "
- + "WHERE (itemNumber > 123 AND itemNumber = 789) OR author =
'Daisy Mae West'"));
+ + "WHERE itemNumber = 789 OR author = 'Daisy Mae West'"));
}
// TODO: Not supported YET
@@ -159,7 +159,7 @@ private CalciteAssert.AssertThat calciteAssert() {
+ "author=Jim Heavisides\n")
.explainContains("PLAN=GeodeToEnumerableConverter\n"
+ " GeodeProject(author=[$4])\n"
- + " GeodeFilter(condition=[>($0, 123)])\n"
+ + " GeodeFilter(condition=[>(CAST($0):INTEGER, 123)])\n"
+ " GeodeTableScan(table=[[geode, BookMaster]])")
.queryContains(
GeodeAssertions.query("SELECT author AS author "
