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 637047900f [CALCITE-5156] Support implicit integer types cast for IN
Sub-query
637047900f is described below
commit 637047900ff06425b70a515d290666b3db9a0dc3
Author: Xiong Duan <[email protected]>
AuthorDate: Mon Oct 21 16:14:11 2024 +0800
[CALCITE-5156] Support implicit integer types cast for IN Sub-query
---
babel/src/test/resources/sql/redshift.iq | 2 +-
.../java/org/apache/calcite/plan/RelOptRules.java | 1 +
.../validate/implicit/AbstractTypeCoercion.java | 9 -
.../calcite/rel/rel2sql/RelToSqlConverterTest.java | 18 +-
.../org/apache/calcite/test/JdbcAdapterTest.java | 20 +-
.../java/org/apache/calcite/test/JdbcTest.java | 2 +-
.../apache/calcite/test/SqlToRelConverterTest.java | 19 +-
.../java/org/apache/calcite/test/StreamTest.java | 2 +-
.../calcite/test/TypeCoercionConverterTest.java | 20 ++
.../apache/calcite/test/SqlToRelConverterTest.xml | 51 ++++-
.../calcite/test/TypeCoercionConverterTest.xml | 42 ++++
core/src/test/resources/sql/agg.iq | 6 +-
core/src/test/resources/sql/conditions.iq | 2 +-
core/src/test/resources/sql/sub-query.iq | 255 +++++++++++++++------
.../calcite/adapter/kafka/KafkaAdapterTest.java | 2 +-
.../org/apache/calcite/test/SparkAdapterTest.java | 10 +-
16 files changed, 341 insertions(+), 120 deletions(-)
diff --git a/babel/src/test/resources/sql/redshift.iq
b/babel/src/test/resources/sql/redshift.iq
index d5d9f255f3..f706ecb164 100755
--- a/babel/src/test/resources/sql/redshift.iq
+++ b/babel/src/test/resources/sql/redshift.iq
@@ -921,7 +921,7 @@ EXPR$0
select nvl2(comm, sal, sal + 10) from emp where deptno = 30;
SELECT "NVL2"("EMP"."COMM", "EMP"."SAL", "EMP"."SAL" + 10)
FROM "scott"."EMP" AS "EMP"
-WHERE "EMP"."DEPTNO" = 30
+WHERE CAST("EMP"."DEPTNO" AS INTEGER) = 30
!explain-validated-on calcite
# NULLIF
diff --git a/core/src/main/java/org/apache/calcite/plan/RelOptRules.java
b/core/src/main/java/org/apache/calcite/plan/RelOptRules.java
index 8b513de0fb..65b9e54897 100644
--- a/core/src/main/java/org/apache/calcite/plan/RelOptRules.java
+++ b/core/src/main/java/org/apache/calcite/plan/RelOptRules.java
@@ -131,6 +131,7 @@ public class RelOptRules {
CoreRules.JOIN_TO_SEMI_JOIN,
CoreRules.AGGREGATE_REMOVE,
CoreRules.UNION_TO_DISTINCT,
+ CoreRules.UNION_TO_VALUES,
CoreRules.PROJECT_REMOVE,
CoreRules.PROJECT_AGGREGATE_MERGE,
CoreRules.AGGREGATE_JOIN_TRANSPOSE,
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 f80c6eb9b7..6d8d6f0415 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
@@ -280,15 +280,6 @@ public abstract class AbstractTypeCoercion implements
TypeCoercion {
return false;
}
- // No need to cast if the source type precedence list
- // contains target type. i.e. do not cast from
- // tinyint to int or int to bigint.
- if (fromType.getPrecedenceList().containsType(toType)
- && SqlTypeUtil.isIntType(fromType)
- && SqlTypeUtil.isIntType(toType)) {
- return false;
- }
-
// No casts to binary except from strings
if (SqlTypeUtil.isBinary(fromType) && !SqlTypeUtil.isString(toType)) {
return false;
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 259af4b156..85a39de8fd 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
@@ -3814,7 +3814,7 @@ class RelToSqlConverterTest {
+ "group by \"product_id\", \"units_per_case\" order by
\"units_per_case\" desc";
final String expected = "SELECT COUNT(*), \"units_per_case\"\n"
+ "FROM \"foodmart\".\"product\"\n"
- + "WHERE \"cases_per_pallet\" > 100\n"
+ + "WHERE CAST(\"cases_per_pallet\" AS INTEGER) > 100\n"
+ "GROUP BY \"product_id\", \"units_per_case\"\n"
+ "ORDER BY \"units_per_case\" DESC";
sql(query).ok(expected);
@@ -4160,7 +4160,7 @@ class RelToSqlConverterTest {
+ "order by \"units_per_case\" desc";
final String expected = "SELECT COUNT(*), product.units_per_case\n"
+ "FROM foodmart.product AS product\n"
- + "WHERE product.cases_per_pallet > 100\n"
+ + "WHERE CAST(product.cases_per_pallet AS INTEGER) > 100\n"
+ "GROUP BY product.product_id, product.units_per_case\n"
+ "ORDER BY product.units_per_case DESC";
sql(query).withDb2().ok(expected);
@@ -4181,7 +4181,7 @@ class RelToSqlConverterTest {
+ "FROM (SELECT product.units_per_case, product.cases_per_pallet, "
+ "product.product_id, 1 AS FOO\n"
+ "FROM foodmart.product AS product) AS t\n"
- + "WHERE t.cases_per_pallet > 100\n"
+ + "WHERE CAST(t.cases_per_pallet AS INTEGER) > 100\n"
+ "GROUP BY t.product_id, t.units_per_case\n"
+ "ORDER BY t.units_per_case DESC";
sql(query).withDb2().ok(expected);
@@ -4205,13 +4205,13 @@ class RelToSqlConverterTest {
+ "FROM (SELECT product.units_per_case, product.cases_per_pallet, "
+ "product.product_id, 1 AS FOO\n"
+ "FROM foodmart.product AS product\n"
- + "WHERE product.cases_per_pallet > 100\n"
+ + "WHERE CAST(product.cases_per_pallet AS INTEGER) > 100\n"
+ "UNION ALL\n"
+ "SELECT product0.units_per_case, product0.cases_per_pallet, "
+ "product0.product_id, 1 AS FOO\n"
+ "FROM foodmart.product AS product0\n"
- + "WHERE product0.cases_per_pallet < 100) AS t3\n"
- + "WHERE t3.cases_per_pallet > 100\n"
+ + "WHERE CAST(product0.cases_per_pallet AS INTEGER) < 100) AS t3\n"
+ + "WHERE CAST(t3.cases_per_pallet AS INTEGER) > 100\n"
+ "GROUP BY t3.product_id, t3.units_per_case\n"
+ "ORDER BY t3.units_per_case DESC";
sql(query).withDb2().ok(expected);
@@ -4308,8 +4308,8 @@ class RelToSqlConverterTest {
+ "FROM \"SCOTT\".\"DEPT\"\n"
+ "LEFT JOIN \"SCOTT\".\"EMP\" "
+ "ON \"DEPT\".\"DEPTNO\" = \"EMP\".\"DEPTNO\" "
- + "AND (\"DEPT\".\"DEPTNO\" > 10"
- + " AND \"DEPT\".\"DEPTNO\" < 15)\n"
+ + "AND (CAST(\"DEPT\".\"DEPTNO\" AS INTEGER) > 10"
+ + " AND CAST(\"DEPT\".\"DEPTNO\" AS INTEGER) < 15)\n"
+ "WHERE \"EMP\".\"JOB\" LIKE 'PRESIDENT'";
sql(sql)
.schema(CalciteAssert.SchemaSpec.JDBC_SCOTT)
@@ -8263,7 +8263,7 @@ class RelToSqlConverterTest {
+ "FROM (SELECT DEPTNO\n"
+ "FROM SCOTT.EMP\n"
+ "GROUP BY DEPTNO\n"
- + "HAVING DEPTNO > 0) AS t1";
+ + "HAVING CAST(DEPTNO AS INT64) > 0) AS t1";
// Parse the input SQL with PostgreSQL dialect,
// in which "isHavingAlias" is false.
diff --git a/core/src/test/java/org/apache/calcite/test/JdbcAdapterTest.java
b/core/src/test/java/org/apache/calcite/test/JdbcAdapterTest.java
index cfa3e0c443..49b30e832b 100644
--- a/core/src/test/java/org/apache/calcite/test/JdbcAdapterTest.java
+++ b/core/src/test/java/org/apache/calcite/test/JdbcAdapterTest.java
@@ -122,22 +122,22 @@ class JdbcAdapterTest {
+ "union all\n"
+ "select ename from SCOTT.emp where empno > 10")
.explainContains("PLAN=EnumerableUnion(all=[true])\n"
- + " JdbcToEnumerableConverter\n"
- + " JdbcProject(store_name=[$3])\n"
- + " JdbcFilter(condition=[<($0, 10)])\n"
- + " JdbcTableScan(table=[[foodmart, store]])\n"
- + " JdbcToEnumerableConverter\n"
- + " JdbcProject(ENAME=[$1])\n"
- + " JdbcFilter(condition=[>($0, 10)])\n"
- + " JdbcTableScan(table=[[SCOTT, EMP]])")
+ + " JdbcToEnumerableConverter\n"
+ + " JdbcProject(store_name=[$3])\n"
+ + " JdbcFilter(condition=[<($0, 10)])\n"
+ + " JdbcTableScan(table=[[foodmart, store]])\n"
+ + " JdbcToEnumerableConverter\n"
+ + " JdbcProject(ENAME=[$1])\n"
+ + " JdbcFilter(condition=[>(CAST($0):INTEGER NOT NULL,
10)])\n"
+ + " JdbcTableScan(table=[[SCOTT, EMP]])")
.runs()
.enable(CalciteAssert.DB == CalciteAssert.DatabaseInstance.HSQLDB)
.planHasSql("SELECT \"store_name\"\n"
+ "FROM \"foodmart\".\"store\"\n"
+ "WHERE \"store_id\" < 10")
.planHasSql("SELECT \"ENAME\"\n"
- + "FROM \"SCOTT\".\"EMP\"\n"
- + "WHERE \"EMPNO\" > 10");
+ + "FROM \"SCOTT\".\"EMP\"\n"
+ + "WHERE CAST(\"EMPNO\" AS INTEGER) > 10");
}
/** Test case for
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 7aaef4cf77..ec681ef601 100644
--- a/core/src/test/java/org/apache/calcite/test/JdbcTest.java
+++ b/core/src/test/java/org/apache/calcite/test/JdbcTest.java
@@ -5017,7 +5017,7 @@ public class JdbcTest {
+ "group by grouping sets (deptno, deptno, deptno, (), ())\n"
+ "having group_id() > 0")
.explainContains("EnumerableCalc(expr#0..2=[{inputs}], expr#3=[1],
expr#4=[+($t1, $t3)], "
- + "expr#5=[0], expr#6=[>($t1, $t5)], DEPTNO=[$t0], G=[$t4],
C=[$t2], $condition=[$t6])\n"
+ + "expr#5=[0:BIGINT], expr#6=[>($t1, $t5)], DEPTNO=[$t0], G=[$t4],
C=[$t2], $condition=[$t6])\n"
+ " EnumerableUnion(all=[true])\n"
+ " EnumerableCalc(expr#0..1=[{inputs}], expr#2=[0:BIGINT],
DEPTNO=[$t0], $f1=[$t2], C=[$t1])\n"
+ " EnumerableAggregate(group=[{7}], groups=[[{7}, {}]],
C=[COUNT()])\n"
diff --git
a/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
b/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
index 345db9eb62..a568964de8 100644
--- a/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
+++ b/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
@@ -290,7 +290,7 @@ class SqlToRelConverterTest extends SqlToRelTestBase {
*/
@Test void testAsOfCast() {
final String sql = "SELECT * "
- + "FROM (SELECT deptno % 10 as m, CAST(deptno AS BIGINT) as deptno
FROM dept) D\n"
+ + "FROM (SELECT CAST(deptno % 10 AS BIGINT) as m, CAST(deptno AS
BIGINT) as deptno FROM dept) D\n"
+ "LEFT ASOF JOIN (SELECT CAST(empno as BIGINT) as empno, CAST(deptno
AS BIGINT) AS deptno FROM emp) E\n"
+ "MATCH_CONDITION D.deptno >= E.deptno\n"
+ "ON D.m = E.empno";
@@ -4142,6 +4142,23 @@ class SqlToRelConverterTest extends SqlToRelTestBase {
sql(sql).withDynamicTable().ok();
}
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-5156";>[CALCITE-5156]
+ * Support implicit integer types cast for IN Sub-query</a>. */
+ @Test void testInSubQueryWithTypeCast() {
+ final String sql = "select *\n"
+ + "from dept\n"
+ + "where cast(deptno + 20 as bigint) in (select deptno from dept)";
+ sql(sql).withExpand(false).ok();
+ }
+
+ @Test void testInSubQueryWithTypeCast2() {
+ final String sql = "select *\n"
+ + "from dept\n"
+ + "where cast(deptno as bigint) in (select deptno + 20 from dept)";
+ sql(sql).withExpand(false).ok();
+ }
+
/** Test case for
* <a
href="https://issues.apache.org/jira/browse/CALCITE-1321";>[CALCITE-1321]
* Configurable IN list size when converting IN clause to join</a>. */
diff --git a/core/src/test/java/org/apache/calcite/test/StreamTest.java
b/core/src/test/java/org/apache/calcite/test/StreamTest.java
index d2bf27f372..cbff9d6c13 100644
--- a/core/src/test/java/org/apache/calcite/test/StreamTest.java
+++ b/core/src/test/java/org/apache/calcite/test/StreamTest.java
@@ -142,7 +142,7 @@ public class StreamTest {
+ " LogicalProject(ROWTIME=[FLOOR($0, FLAG(HOUR))],
PRODUCT=[$2])\n"
+ " LogicalTableScan(table=[[STREAMS, ORDERS]])\n")
.explainContains(
- "EnumerableCalc(expr#0..2=[{inputs}], expr#3=[1], expr#4=[>($t2,
$t3)], proj#0..2=[{exprs}], $condition=[$t4])\n"
+ "EnumerableCalc(expr#0..2=[{inputs}], expr#3=[1:BIGINT],
expr#4=[>($t2, $t3)], proj#0..2=[{exprs}], $condition=[$t4])\n"
+ " EnumerableAggregate(group=[{0, 1}], C=[COUNT()])\n"
+ " EnumerableCalc(expr#0..3=[{inputs}],
expr#4=[FLAG(HOUR)], expr#5=[FLOOR($t0, $t4)], ROWTIME=[$t5], PRODUCT=[$t2])\n"
+ " EnumerableInterpreter\n"
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 228b569062..538588ec9e 100644
--- a/core/src/test/java/org/apache/calcite/test/TypeCoercionConverterTest.java
+++ b/core/src/test/java/org/apache/calcite/test/TypeCoercionConverterTest.java
@@ -155,6 +155,26 @@ class TypeCoercionConverterTest extends SqlToRelTestBase {
.ok();
}
+ @Test void testIntegerImplicitTypeCast1() {
+ sql("with\n"
+ + "t1(x) as (select * from (values (cast(1 as bigint)),(cast(2 as
bigint))) as t1),\n"
+ + "t2(x) as (select * from (values (3),(4)) as t2)\n"
+ + "select *\n"
+ + "from t1\n"
+ + "where t1.x in (select t2.x from t2)")
+ .ok();
+ }
+
+ @Test void testIntegerImplicitTypeCast2() {
+ sql("with\n"
+ + "t1(x) as (select * from (values (cast(1 as tinyint)),(cast(2 as
tinyint))) as t1),\n"
+ + "t2(x) as (select * from (values (3),(4)) as t2)\n"
+ + "select *\n"
+ + "from t1\n"
+ + "where t1.x in (select t2.x from t2)")
+ .ok();
+ }
+
@Test void testSetOperation() {
// int decimal smallint double
// char decimal float bigint
diff --git
a/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
b/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
index 3feb6be58a..80ec5f83ad 100644
--- a/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
+++ b/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
@@ -204,7 +204,7 @@ having e > 10 or count(empno) < 5]]>
</Resource>
<Resource name="plan">
<![CDATA[
-LogicalFilter(condition=[SEARCH($0, Sarg[(-∞..5), (10..+∞)])])
+LogicalFilter(condition=[SEARCH($0, Sarg[(-∞..5L:BIGINT),
(10L:BIGINT..+∞)]:BIGINT)])
LogicalAggregate(group=[{}], E=[COUNT()])
LogicalProject(EMPNO=[$0])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
@@ -526,16 +526,16 @@ LogicalProject(EXPR$0=[ITEM(ITEM($3, 1).DETAIL.SKILLS,
+(2, 3)).DESC])
</TestCase>
<TestCase name="testAsOfCast">
<Resource name="sql">
- <![CDATA[SELECT * FROM (SELECT deptno % 10 as m, CAST(deptno AS BIGINT)
as deptno FROM dept) D
+ <![CDATA[SELECT * FROM (SELECT CAST(deptno % 10 AS BIGINT) as m,
CAST(deptno AS BIGINT) as deptno FROM dept) D
LEFT ASOF JOIN (SELECT CAST(empno as BIGINT) as empno, CAST(deptno AS BIGINT)
AS deptno FROM emp) E
MATCH_CONDITION D.deptno >= E.deptno
ON D.m = E.empno]]>
</Resource>
<Resource name="plan">
<![CDATA[
-LogicalProject(M=[$0], DEPTNO=[$1], EMPNO=[$3], DEPTNO0=[$4])
- LogicalAsofJoin(condition=[=($2, $3)], joinType=[left_asof],
matchCondition=[>=($1, $4)])
- LogicalProject(M=[MOD($0, 10)], DEPTNO=[CAST($0):BIGINT NOT NULL],
$f2=[CAST(MOD($0, 10)):BIGINT NOT NULL])
+LogicalProject(M=[$0], DEPTNO=[$1], EMPNO=[$2], DEPTNO0=[$3])
+ LogicalAsofJoin(condition=[=($0, $2)], joinType=[left_asof],
matchCondition=[>=($1, $3)])
+ LogicalProject(M=[CAST(MOD($0, 10)):BIGINT NOT NULL],
DEPTNO=[CAST($0):BIGINT NOT NULL])
LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
LogicalProject(EMPNO=[CAST($0):BIGINT NOT NULL], DEPTNO=[CAST($7):BIGINT
NOT NULL])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
@@ -2905,6 +2905,40 @@ FROM dept, emp WHERE emp.deptno = dept.deptno AND
emp.sal < (
)]]>
</Resource>
</TestCase>
+ <TestCase name="testInSubQueryWithTypeCast">
+ <Resource name="sql">
+ <![CDATA[select *
+from dept
+where cast(deptno + 20 as bigint) in (select deptno from dept)]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalProject(DEPTNO=[$0], NAME=[$1])
+ LogicalFilter(condition=[IN(CAST(+($0, 20)):BIGINT NOT NULL, {
+LogicalProject(EXPR$0=[CAST($0):BIGINT NOT NULL])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+})])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testInSubQueryWithTypeCast2">
+ <Resource name="sql">
+ <![CDATA[select *
+from dept
+where cast(deptno as bigint) in (select deptno + 20 from dept)]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalProject(DEPTNO=[$0], NAME=[$1])
+ LogicalFilter(condition=[IN(CAST($0):BIGINT NOT NULL, {
+LogicalProject(EXPR$0=[CAST(+($0, 20)):BIGINT NOT NULL])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+})])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+]]>
+ </Resource>
+ </TestCase>
<TestCase name="testInToSemiJoin">
<Resource name="sql">
<![CDATA[SELECT empno
@@ -2925,7 +2959,12 @@ LogicalProject(EMPNO=[$0])
LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4],
SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], EMPNO0=[CAST($0):BIGINT NOT
NULL])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
LogicalAggregate(group=[{0}])
- LogicalValues(tuples=[[{ 130 }, { 131 }, { 132 }, { 133 }, { 134 }]])
+ LogicalUnion(all=[true])
+ LogicalValues(tuples=[[{ 130 }]])
+ LogicalValues(tuples=[[{ 131 }]])
+ LogicalValues(tuples=[[{ 132 }]])
+ LogicalValues(tuples=[[{ 133 }]])
+ LogicalValues(tuples=[[{ 134 }]])
]]>
</Resource>
</TestCase>
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 4253f0b7e1..37f707b8db 100644
---
a/core/src/test/resources/org/apache/calcite/test/TypeCoercionConverterTest.xml
+++
b/core/src/test/resources/org/apache/calcite/test/TypeCoercionConverterTest.xml
@@ -174,6 +174,48 @@ LogicalTableModify(table=[[CATALOG, SALES, T1]],
operation=[INSERT], flattened=[
<![CDATA[
LogicalTableModify(table=[[CATALOG, SALES, T1]], operation=[INSERT],
flattened=[false])
LogicalValues(tuples=[[{ 'a', 1, 1, 0, 0.0E0, 0.0E0, 0, 2021-11-28 00:00:00,
2021-11-28, X'0a', false }, { 'b', 2, 2, 0, 0.0E0, 0.0E0, 0, 2021-11-28
00:00:00, 2021-11-28, X'0a', false }, { 'c', 3, 3, 0, 0.0E0, 0.0E0, 0,
2021-11-28 00:00:00, 2021-11-28, X'0a', false }, { 'd', 4, 4, 0, 0.0E0, 0.0E0,
0, 2021-11-28 00:00:00, 2021-11-28, X'0a', false }, { 'e', 5, 5, 0, 0.0E0,
0.0E0, 0, 2021-11-28 00:00:00, 2021-11-28, X'0a', false }]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testIntegerImplicitTypeCast1">
+ <Resource name="sql">
+ <![CDATA[with
+t1(x) as (select * from (values (cast(1 as bigint)),(cast(2 as bigint))) as
t1),
+t2(x) as (select * from (values (3),(4)) as t2)
+select *
+from t1
+where t1.x in (select t2.x from t2)]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalProject(X=[$0])
+ LogicalJoin(condition=[=($0, $1)], joinType=[inner])
+ LogicalProject(T1=[$0])
+ LogicalValues(tuples=[[{ 1 }, { 2 }]])
+ LogicalAggregate(group=[{0}])
+ LogicalProject(EXPR$0=[CAST($0):BIGINT NOT NULL])
+ LogicalValues(tuples=[[{ 3 }, { 4 }]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testIntegerImplicitTypeCast2">
+ <Resource name="sql">
+ <![CDATA[with
+t1(x) as (select * from (values (cast(1 as tinyint)),(cast(2 as tinyint))) as
t1),
+t2(x) as (select * from (values (3),(4)) as t2)
+select *
+from t1
+where t1.x in (select t2.x from t2)]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalProject(X=[$0])
+ LogicalJoin(condition=[=($1, $2)], joinType=[inner])
+ LogicalProject(T1=[$0], EXPR$0=[CAST($0):INTEGER NOT NULL])
+ LogicalValues(tuples=[[{ 1 }, { 2 }]])
+ LogicalAggregate(group=[{0}])
+ LogicalProject(X=[$0])
+ LogicalValues(tuples=[[{ 3 }, { 4 }]])
]]>
</Resource>
</TestCase>
diff --git a/core/src/test/resources/sql/agg.iq
b/core/src/test/resources/sql/agg.iq
index 411cbe38f8..d459e62e74 100644
--- a/core/src/test/resources/sql/agg.iq
+++ b/core/src/test/resources/sql/agg.iq
@@ -1934,9 +1934,9 @@ group by deptno;
EnumerableAggregate(group=[{0}], CF=[COUNT() FILTER $1], C=[COUNT()])
EnumerableCalc(expr#0..1=[{inputs}], expr#2=['CLERK':VARCHAR(9)],
expr#3=[=($t0, $t2)], expr#4=[IS TRUE($t3)], DEPTNO=[$t1], $f1=[$t4])
EnumerableUnion(all=[true])
- EnumerableCalc(expr#0..7=[{inputs}], expr#8=[20], expr#9=[<($t7, $t8)],
JOB=[$t2], DEPTNO=[$t7], $condition=[$t9])
+ EnumerableCalc(expr#0..7=[{inputs}], expr#8=[CAST($t7):INTEGER],
expr#9=[20], expr#10=[<($t8, $t9)], JOB=[$t2], DEPTNO=[$t7], $condition=[$t10])
EnumerableTableScan(table=[[scott, EMP]])
- EnumerableCalc(expr#0..7=[{inputs}], expr#8=[20], expr#9=[>($t7, $t8)],
JOB=[$t2], DEPTNO=[$t7], $condition=[$t9])
+ EnumerableCalc(expr#0..7=[{inputs}], expr#8=[CAST($t7):INTEGER],
expr#9=[20], expr#10=[>($t8, $t9)], JOB=[$t2], DEPTNO=[$t7], $condition=[$t10])
EnumerableTableScan(table=[[scott, EMP]])
!plan
@@ -3079,7 +3079,7 @@ group by deptno;
!ok
EnumerableAggregate(group=[{0}], EMPNOS=[COLLECT($1) WITHIN GROUP ([1 DESC])
FILTER $2])
- EnumerableCalc(expr#0..7=[{inputs}], expr#8=[7500], expr#9=[>($t0, $t8)],
DEPTNO=[$t7], EMPNO=[$t0], $f2=[$t9])
+ EnumerableCalc(expr#0..7=[{inputs}], expr#8=[CAST($t0):INTEGER NOT NULL],
expr#9=[7500], expr#10=[>($t8, $t9)], DEPTNO=[$t7], EMPNO=[$t0], $f2=[$t10])
EnumerableTableScan(table=[[scott, EMP]])
!plan
diff --git a/core/src/test/resources/sql/conditions.iq
b/core/src/test/resources/sql/conditions.iq
index 1acf5ecd4b..a9fa54b529 100644
--- a/core/src/test/resources/sql/conditions.iq
+++ b/core/src/test/resources/sql/conditions.iq
@@ -395,7 +395,7 @@ EnumerableCalc(expr#0..2=[{inputs}], EMPNO=[$t0])
EnumerableNestedLoopJoin(condition=[true], joinType=[left])
EnumerableCalc(expr#0..2=[{inputs}], DEPTNO=[$t0])
EnumerableTableScan(table=[[scott, DEPT]])
- EnumerableCalc(expr#0=[{inputs}], expr#1=[0], expr#2=[>($t0, $t1)],
EXPR$0=[$t2])
+ EnumerableCalc(expr#0=[{inputs}], expr#1=[0:BIGINT], expr#2=[>($t0,
$t1)], EXPR$0=[$t2])
EnumerableAggregate(group=[{}], agg#0=[COUNT()])
EnumerableTableScan(table=[[scott, EMP]])
!plan
diff --git a/core/src/test/resources/sql/sub-query.iq
b/core/src/test/resources/sql/sub-query.iq
index ace96e5ec6..c32362f778 100644
--- a/core/src/test/resources/sql/sub-query.iq
+++ b/core/src/test/resources/sql/sub-query.iq
@@ -492,8 +492,10 @@ EnumerableCalc(expr#0..2=[{inputs}], proj#0..1=[{exprs}])
# Uncorrelated
with t (a, b) as (select * from (values (60, 'b')))
select * from t where a in (select deptno from "scott".dept);
-EnumerableCalc(expr#0..2=[{inputs}], expr#3=[60], expr#4=['b'], expr#5=[=($t3,
$t0)], A=[$t3], B=[$t4], $condition=[$t5])
- EnumerableTableScan(table=[[scott, DEPT]])
+EnumerableCalc(expr#0=[{inputs}], expr#1=[60], expr#2=['b'], A=[$t1], B=[$t2])
+ EnumerableAggregate(group=[{0}])
+ EnumerableCalc(expr#0..2=[{inputs}], expr#3=[CAST($t0):INTEGER NOT NULL],
expr#4=[60], expr#5=[=($t4, $t3)], EXPR$0=[$t3], $condition=[$t5])
+ EnumerableTableScan(table=[[scott, DEPT]])
!plan
+---+---+
| A | B |
@@ -505,8 +507,10 @@ EnumerableCalc(expr#0..2=[{inputs}], expr#3=[60],
expr#4=['b'], expr#5=[=($t3, $
with t (a, b) as (select * from (values (30, 'b')))
select * from t where a in (select deptno from "scott".dept);
-EnumerableCalc(expr#0..2=[{inputs}], expr#3=[30], expr#4=['b'], expr#5=[=($t3,
$t0)], A=[$t3], B=[$t4], $condition=[$t5])
- EnumerableTableScan(table=[[scott, DEPT]])
+EnumerableCalc(expr#0=[{inputs}], expr#1=[30], expr#2=['b'], A=[$t1], B=[$t2])
+ EnumerableAggregate(group=[{0}])
+ EnumerableCalc(expr#0..2=[{inputs}], expr#3=[CAST($t0):INTEGER NOT NULL],
expr#4=[30], expr#5=[=($t4, $t3)], EXPR$0=[$t3], $condition=[$t5])
+ EnumerableTableScan(table=[[scott, DEPT]])
!plan
+----+---+
| A | B |
@@ -734,7 +738,7 @@ EnumerableCalc(expr#0..2=[{inputs}], SAL=[$t1])
EnumerableHashJoin(condition=[AND(=($2, $4), =($0, $3))], joinType=[semi])
EnumerableCalc(expr#0..7=[{inputs}], expr#8=[CAST($t2):VARCHAR(14)],
EMPNO=[$t0], SAL=[$t5], JOB0=[$t8])
EnumerableTableScan(table=[[scott, EMP]])
- EnumerableCalc(expr#0..2=[{inputs}], expr#3=[IS NOT NULL($t1)],
proj#0..1=[{exprs}], $condition=[$t3])
+ EnumerableCalc(expr#0..2=[{inputs}], expr#3=[CAST($t0):SMALLINT NOT NULL],
expr#4=[IS NOT NULL($t1)], EXPR$0=[$t3], DNAME=[$t1], $condition=[$t4])
EnumerableTableScan(table=[[scott, DEPT]])
!plan
@@ -1101,8 +1105,9 @@ EnumerableCalc(expr#0..2=[{inputs}], expr#3=[IS NOT
NULL($t2)], SAL=[$t1], EXPR$
EnumerableNestedLoopJoin(condition=[true], joinType=[left])
EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0], SAL=[$t5])
EnumerableTableScan(table=[[scott, EMP]])
- EnumerableCalc(expr#0..2=[{inputs}], expr#3=[true], expr#4=[10],
expr#5=[=($t4, $t0)], cs=[$t3], $condition=[$t5])
- EnumerableTableScan(table=[[scott, DEPT]])
+ EnumerableAggregate(group=[{0}])
+ EnumerableCalc(expr#0..2=[{inputs}], expr#3=[true], expr#4=[10],
expr#5=[CAST($t0):INTEGER NOT NULL], expr#6=[=($t4, $t5)], cs=[$t3],
$condition=[$t6])
+ EnumerableTableScan(table=[[scott, DEPT]])
!plan
# Test project literal IN nullable
@@ -1137,7 +1142,7 @@ EnumerableCalc(expr#0..3=[{inputs}], expr#4=[IS
FALSE($t2)], expr#5=[null:BOOLEA
EnumerableLimit(fetch=[1])
EnumerableSort(sort0=[$0], dir0=[DESC])
EnumerableAggregate(group=[{0}], c=[COUNT()])
- EnumerableCalc(expr#0..2=[{inputs}], expr#3=[true], expr#4=[10],
expr#5=[=($t0, $t4)], cs=[$t3], $condition=[$t5])
+ EnumerableCalc(expr#0..2=[{inputs}], expr#3=[IS NOT NULL($t0)],
expr#4=[CAST($t0):INTEGER], expr#5=[10], expr#6=[=($t4, $t5)], cs=[$t3],
$condition=[$t6])
EnumerableTableScan(table=[[scott, DEPT]])
!plan
@@ -1350,8 +1355,9 @@ EnumerableCalc(expr#0..2=[{inputs}], expr#3=[IS
NULL($t2)], SAL=[$t1], EXPR$1=[$
EnumerableNestedLoopJoin(condition=[true], joinType=[left])
EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0], SAL=[$t5])
EnumerableTableScan(table=[[scott, EMP]])
- EnumerableCalc(expr#0..2=[{inputs}], expr#3=[true], expr#4=[10],
expr#5=[=($t4, $t0)], cs=[$t3], $condition=[$t5])
- EnumerableTableScan(table=[[scott, DEPT]])
+ EnumerableAggregate(group=[{0}])
+ EnumerableCalc(expr#0..2=[{inputs}], expr#3=[true], expr#4=[10],
expr#5=[CAST($t0):INTEGER NOT NULL], expr#6=[=($t4, $t5)], cs=[$t3],
$condition=[$t6])
+ EnumerableTableScan(table=[[scott, DEPT]])
!plan
# Test project literal NOT IN nullable
@@ -1386,7 +1392,7 @@ EnumerableCalc(expr#0..3=[{inputs}], expr#4=[IS
NULL($t3)], expr#5=[IS FALSE($t2
EnumerableLimit(fetch=[1])
EnumerableSort(sort0=[$0], dir0=[DESC])
EnumerableAggregate(group=[{0}], c=[COUNT()])
- EnumerableCalc(expr#0..2=[{inputs}], expr#3=[true], expr#4=[10],
expr#5=[=($t0, $t4)], cs=[$t3], $condition=[$t5])
+ EnumerableCalc(expr#0..2=[{inputs}], expr#3=[IS NOT NULL($t0)],
expr#4=[CAST($t0):INTEGER], expr#5=[10], expr#6=[=($t4, $t5)], cs=[$t3],
$condition=[$t6])
EnumerableTableScan(table=[[scott, DEPT]])
!plan
@@ -1519,8 +1525,9 @@ EnumerableCalc(expr#0..2=[{inputs}], SAL=[$t1])
EnumerableNestedLoopJoin(condition=[true], joinType=[inner])
EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0], SAL=[$t5])
EnumerableTableScan(table=[[scott, EMP]])
- EnumerableCalc(expr#0..2=[{inputs}], expr#3=[10], expr#4=[=($t3, $t0)],
DEPTNO=[$t0], $condition=[$t4])
- EnumerableTableScan(table=[[scott, DEPT]])
+ EnumerableAggregate(group=[{0}])
+ EnumerableCalc(expr#0..2=[{inputs}], expr#3=[true], expr#4=[10],
expr#5=[CAST($t0):INTEGER NOT NULL], expr#6=[=($t4, $t5)], cs=[$t3],
$condition=[$t6])
+ EnumerableTableScan(table=[[scott, DEPT]])
!plan
# Test filter literal IN nullable
@@ -1552,7 +1559,7 @@ EnumerableCalc(expr#0..2=[{inputs}], SAL=[$t1])
EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0], SAL=[$t5])
EnumerableTableScan(table=[[scott, EMP]])
EnumerableAggregate(group=[{0}])
- EnumerableCalc(expr#0..2=[{inputs}], expr#3=[true], expr#4=[10],
expr#5=[=($t4, $t0)], cs=[$t3], $condition=[$t5])
+ EnumerableCalc(expr#0..2=[{inputs}], expr#3=[true], expr#4=[10],
expr#5=[CAST($t0):INTEGER], expr#6=[=($t4, $t5)], cs=[$t3], $condition=[$t6])
EnumerableTableScan(table=[[scott, DEPT]])
!plan
@@ -1678,7 +1685,7 @@ EnumerableCalc(expr#0..3=[{inputs}], expr#4=[IS
NULL($t3)], expr#5=[NOT($t2)], e
EnumerableLimit(fetch=[1])
EnumerableSort(sort0=[$0], dir0=[DESC])
EnumerableAggregate(group=[{0}], c=[COUNT()])
- EnumerableCalc(expr#0..2=[{inputs}], expr#3=[true], expr#4=[10],
expr#5=[=($t4, $t0)], cs=[$t3], $condition=[$t5])
+ EnumerableCalc(expr#0..2=[{inputs}], expr#3=[true], expr#4=[10],
expr#5=[CAST($t0):INTEGER NOT NULL], expr#6=[=($t4, $t5)], cs=[$t3],
$condition=[$t6])
EnumerableTableScan(table=[[scott, DEPT]])
!plan
@@ -1699,7 +1706,7 @@ EnumerableCalc(expr#0..3=[{inputs}], expr#4=[IS
NULL($t3)], expr#5=[NOT($t2)], e
EnumerableLimit(fetch=[1])
EnumerableSort(sort0=[$0], dir0=[DESC])
EnumerableAggregate(group=[{0}], c=[COUNT()])
- EnumerableCalc(expr#0..2=[{inputs}], expr#3=[true], expr#4=[10],
expr#5=[=($t0, $t4)], cs=[$t3], $condition=[$t5])
+ EnumerableCalc(expr#0..2=[{inputs}], expr#3=[IS NOT NULL($t0)],
expr#4=[CAST($t0):INTEGER], expr#5=[10], expr#6=[=($t4, $t5)], cs=[$t3],
$condition=[$t6])
EnumerableTableScan(table=[[scott, DEPT]])
!plan
@@ -1841,7 +1848,7 @@ EnumerableCalc(expr#0..2=[{inputs}], SAL=[$t1])
EnumerableHashJoin(condition=[=($2, $3)], joinType=[semi])
EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0], SAL=[$t5], DEPTNO=[$t7])
EnumerableTableScan(table=[[scott, EMP]])
- EnumerableCalc(expr#0..2=[{inputs}], expr#3=[10], expr#4=[=($t3, $t0)],
DEPTNO=[$t0], $condition=[$t4])
+ EnumerableCalc(expr#0..2=[{inputs}], expr#3=[10],
expr#4=[CAST($t0):INTEGER NOT NULL], expr#5=[=($t3, $t4)], DEPTNO=[$t0],
$condition=[$t5])
EnumerableTableScan(table=[[scott, DEPT]])
!plan
@@ -1862,7 +1869,7 @@ EnumerableCalc(expr#0..2=[{inputs}], SAL=[$t1])
EnumerableHashJoin(condition=[=($2, $3)], joinType=[semi])
EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0], SAL=[$t5], DEPTNO=[$t7])
EnumerableTableScan(table=[[scott, EMP]])
- EnumerableCalc(expr#0..2=[{inputs}], expr#3=[10], expr#4=[=($t3, $t0)],
DEPTNO=[$t0], $condition=[$t4])
+ EnumerableCalc(expr#0..2=[{inputs}], expr#3=[10],
expr#4=[CAST($t0):INTEGER], expr#5=[=($t3, $t4)], DEPTNO=[$t0],
$condition=[$t5])
EnumerableTableScan(table=[[scott, DEPT]])
!plan
@@ -1958,12 +1965,12 @@ select sal from "scott".emp e
(11 rows)
!ok
-EnumerableCalc(expr#0..4=[{inputs}], expr#5=[NOT($t3)], expr#6=[IS NOT
NULL($t3)], expr#7=[OR($t5, $t6)], expr#8=[IS NOT TRUE($t7)], SAL=[$t1],
$condition=[$t8])
- EnumerableMergeJoin(condition=[=($2, $4)], joinType=[left])
+EnumerableCalc(expr#0..4=[{inputs}], expr#5=[NOT($t4)], expr#6=[IS NOT
NULL($t4)], expr#7=[OR($t5, $t6)], expr#8=[IS NOT TRUE($t7)], SAL=[$t1],
$condition=[$t8])
+ EnumerableMergeJoin(condition=[=($2, $3)], joinType=[left])
EnumerableSort(sort0=[$2], dir0=[ASC])
EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0], SAL=[$t5],
DEPTNO=[$t7])
EnumerableTableScan(table=[[scott, EMP]])
- EnumerableCalc(expr#0..2=[{inputs}], expr#3=[true], expr#4=[10],
expr#5=[=($t4, $t0)], cs=[$t3], DEPTNO1=[$t0], $condition=[$t5])
+ EnumerableCalc(expr#0..2=[{inputs}], expr#3=[true], expr#4=[10],
expr#5=[CAST($t0):INTEGER NOT NULL], expr#6=[=($t4, $t5)], DEPTNO=[$t0],
$f1=[$t3], $condition=[$t6])
EnumerableTableScan(table=[[scott, DEPT]])
!plan
@@ -1988,12 +1995,12 @@ select sal from "scott".emp e
(11 rows)
!ok
-EnumerableCalc(expr#0..4=[{inputs}], expr#5=[NOT($t4)], expr#6=[IS NOT
NULL($t4)], expr#7=[OR($t5, $t6)], expr#8=[IS NOT TRUE($t7)], SAL=[$t1],
$condition=[$t8])
- EnumerableMergeJoin(condition=[=($2, $3)], joinType=[left])
+EnumerableCalc(expr#0..4=[{inputs}], expr#5=[NOT($t3)], expr#6=[IS NOT
NULL($t3)], expr#7=[OR($t5, $t6)], expr#8=[IS NOT TRUE($t7)], SAL=[$t1],
$condition=[$t8])
+ EnumerableMergeJoin(condition=[=($2, $4)], joinType=[left])
EnumerableSort(sort0=[$2], dir0=[ASC])
EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0], SAL=[$t5],
DEPTNO=[$t7])
EnumerableTableScan(table=[[scott, EMP]])
- EnumerableCalc(expr#0..2=[{inputs}], expr#3=[true], expr#4=[10],
expr#5=[=($t0, $t4)], DEPTNO=[$t0], $f1=[$t3], $condition=[$t5])
+ EnumerableCalc(expr#0..2=[{inputs}], expr#3=[true],
expr#4=[CAST($t0):INTEGER], expr#5=[10], expr#6=[=($t4, $t5)], cs=[$t3],
DEPTNO=[$t0], $condition=[$t6])
EnumerableTableScan(table=[[scott, DEPT]])
!plan
@@ -3439,14 +3446,15 @@ select * from "scott".emp where empno not in (null,
7782);
EnumerableCalc(expr#0..12=[{inputs}], expr#13=[0:BIGINT], expr#14=[=($t8,
$t13)], expr#15=[IS NULL($t12)], expr#16=[>=($t9, $t8)], expr#17=[AND($t15,
$t16)], expr#18=[OR($t14, $t17)], proj#0..7=[{exprs}], $condition=[$t18])
EnumerableMergeJoin(condition=[=($10, $11)], joinType=[left])
EnumerableSort(sort0=[$10], dir0=[ASC])
- EnumerableCalc(expr#0..9=[{inputs}], proj#0..9=[{exprs}], EMPNO0=[$t0])
+ EnumerableCalc(expr#0..9=[{inputs}], expr#10=[CAST($t0):INTEGER NOT
NULL], proj#0..10=[{exprs}])
EnumerableNestedLoopJoin(condition=[true], joinType=[inner])
EnumerableTableScan(table=[[scott, EMP]])
EnumerableAggregate(group=[{}], agg#0=[COUNT()], agg#1=[COUNT($0)])
EnumerableValues(tuples=[[{ null }, { 7782 }]])
EnumerableSort(sort0=[$0], dir0=[ASC])
- EnumerableCalc(expr#0=[{inputs}], expr#1=[true], proj#0..1=[{exprs}])
- EnumerableValues(tuples=[[{ null }, { 7782 }]])
+ EnumerableAggregate(group=[{0}], agg#0=[MIN($1)])
+ EnumerableCalc(expr#0=[{inputs}], expr#1=[true], proj#0..1=[{exprs}])
+ EnumerableValues(tuples=[[{ null }, { 7782 }]])
!plan
select * from "scott".emp where (empno, deptno) not in ((1, 2), (3, null));
@@ -3461,14 +3469,15 @@ select * from "scott".emp where (empno, deptno) not in
((1, 2), (3, null));
EnumerableCalc(expr#0..14=[{inputs}], expr#15=[0:BIGINT], expr#16=[=($t8,
$t15)], expr#17=[IS NULL($t14)], expr#18=[>=($t9, $t8)], expr#19=[IS NOT
NULL($t11)], expr#20=[AND($t17, $t18, $t19)], expr#21=[OR($t16, $t20)],
proj#0..7=[{exprs}], $condition=[$t21])
EnumerableMergeJoin(condition=[AND(=($10, $12), =($11, $13))],
joinType=[left])
EnumerableSort(sort0=[$10], sort1=[$11], dir0=[ASC], dir1=[ASC])
- EnumerableCalc(expr#0..9=[{inputs}], proj#0..9=[{exprs}], EMPNO0=[$t0],
DEPTNO0=[$t7])
+ EnumerableCalc(expr#0..9=[{inputs}], expr#10=[CAST($t0):INTEGER NOT
NULL], expr#11=[CAST($t7):INTEGER], proj#0..11=[{exprs}])
EnumerableNestedLoopJoin(condition=[true], joinType=[inner])
EnumerableTableScan(table=[[scott, EMP]])
EnumerableAggregate(group=[{}], agg#0=[COUNT()], agg#1=[COUNT($1)])
- EnumerableValues(tuples=[[{ 1, 2 }, { 3, null }]])
+ EnumerableValues(tuples=[[{ 3, null }, { 1, 2 }]])
EnumerableSort(sort0=[$0], sort1=[$1], dir0=[ASC], dir1=[ASC])
- EnumerableCalc(expr#0..1=[{inputs}], expr#2=[true], proj#0..2=[{exprs}])
- EnumerableValues(tuples=[[{ 1, 2 }, { 3, null }]])
+ EnumerableAggregate(group=[{0, 1}], agg#0=[MIN($2)])
+ EnumerableCalc(expr#0..1=[{inputs}], expr#2=[true],
proj#0..2=[{exprs}])
+ EnumerableValues(tuples=[[{ 3, null }, { 1, 2 }]])
!plan
select * from "scott".emp where (empno, deptno) not in ((7369, 20), (7499,
30));
@@ -3495,7 +3504,7 @@ select * from "scott".emp where (empno, deptno) not in
((7369, 20), (7499, 30));
EnumerableCalc(expr#0..12=[{inputs}], expr#13=[IS NOT TRUE($t12)], expr#14=[IS
NULL($t9)], expr#15=[OR($t13, $t14)], proj#0..7=[{exprs}], $condition=[$t15])
EnumerableMergeJoin(condition=[AND(=($8, $10), =($9, $11))], joinType=[left])
EnumerableSort(sort0=[$8], sort1=[$9], dir0=[ASC], dir1=[ASC])
- EnumerableCalc(expr#0..7=[{inputs}], proj#0..7=[{exprs}], EMPNO0=[$t0],
DEPTNO0=[$t7])
+ EnumerableCalc(expr#0..7=[{inputs}], expr#8=[CAST($t0):INTEGER NOT
NULL], expr#9=[CAST($t7):INTEGER], proj#0..9=[{exprs}])
EnumerableTableScan(table=[[scott, EMP]])
EnumerableSort(sort0=[$0], sort1=[$1], dir0=[ASC], dir1=[ASC])
EnumerableCalc(expr#0..1=[{inputs}], expr#2=[true], proj#0..2=[{exprs}])
@@ -3889,7 +3898,7 @@ select empno, empno in (7369, 7499, 7521) 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=[<($t2, $t1)], expr#11=[null:BOOLEAN], expr#12=[IS NULL($t5)],
expr#13=[AND($t10, $t11, $t8, $t12)], expr#14=[OR($t9, $t13)],
expr#15=[CAST($t14):BOOLEAN NOT NULL], EMPNO=[$t0], EXPR$1=[$t15])
EnumerableMergeJoin(condition=[=($3, $4)], joinType=[left])
EnumerableSort(sort0=[$3], dir0=[ASC])
- EnumerableCalc(expr#0..2=[{inputs}], EMPNO=[$t2], $f0=[$t0], $f1=[$t1],
EMPNO0=[$t2])
+ EnumerableCalc(expr#0..2=[{inputs}], expr#3=[CAST($t2):INTEGER NOT
NULL], EMPNO=[$t2], $f0=[$t0], $f1=[$t1], EMPNO0=[$t3])
EnumerableNestedLoopJoin(condition=[true], joinType=[inner])
EnumerableCalc(expr#0=[{inputs}], $f0=[$t0], $f00=[$t0])
EnumerableAggregate(group=[{}], agg#0=[COUNT()])
@@ -3933,17 +3942,11 @@ EnumerableCalc(expr#0..5=[{inputs}], expr#6=[IS NOT
NULL($t5)], expr#7=[0:BIGINT
EnumerableTableScan(table=[[scott, EMP]])
EnumerableCalc(expr#0=[{inputs}], $f0=[$t0], $f00=[$t0])
EnumerableAggregate(group=[{}], agg#0=[COUNT()])
- EnumerableUnion(all=[true])
- EnumerableValues(tuples=[[{ 500.00 }]])
- EnumerableValues(tuples=[[{ 300.00 }]])
- EnumerableValues(tuples=[[{ 0.00 }]])
+ EnumerableValues(tuples=[[{ 500.00 }, { 300.00 }, { 0.00 }]])
EnumerableSort(sort0=[$0], dir0=[ASC])
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=[[{ 500.00 }, { 300.00 }, { 0.00 }]])
!plan
# Test LHS is nullable and RHS is nullable
@@ -3979,19 +3982,11 @@ EnumerableCalc(expr#0..5=[{inputs}], expr#6=[IS NOT
NULL($t5)], expr#7=[0:BIGINT
EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0], COMM=[$t6])
EnumerableTableScan(table=[[scott, EMP]])
EnumerableAggregate(group=[{}], agg#0=[COUNT()], agg#1=[COUNT($0)])
- EnumerableUnion(all=[true])
- EnumerableValues(tuples=[[{ 500.00 }]])
- EnumerableValues(tuples=[[{ 300.00 }]])
- EnumerableValues(tuples=[[{ 0.00 }]])
- EnumerableValues(tuples=[[{ null }]])
+ EnumerableValues(tuples=[[{ 500.00 }, { 300.00 }, { 0.00 }, { null
}]])
EnumerableSort(sort0=[$0], dir0=[ASC])
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 }]])
+ EnumerableValues(tuples=[[{ 500.00 }, { 300.00 }, { 0.00 }, { null
}]])
!plan
# Test LHS is (not nullable, not nullable) and RHS is (not nullable, not
nullable)
@@ -4022,7 +4017,7 @@ select empno, (empno, empno) in ((7369, 7369), (7499,
7499), (7521, 7521)) from
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=[<($t2, $t1)], expr#13=[null:BOOLEAN], expr#14=[IS NULL($t7)],
expr#15=[AND($t12, $t13, $t10, $t14)], expr#16=[OR($t11, $t15)],
expr#17=[CAST($t16):BOOLEAN NOT NULL], EMPNO=[$t0], EXPR$1=[$t17])
EnumerableMergeJoin(condition=[AND(=($3, $5), =($4, $6))], joinType=[left])
EnumerableSort(sort0=[$3], sort1=[$4], dir0=[ASC], dir1=[ASC])
- EnumerableCalc(expr#0..2=[{inputs}], EMPNO=[$t2], $f0=[$t0], $f1=[$t1],
EMPNO0=[$t2], EMPNO1=[$t2])
+ EnumerableCalc(expr#0..2=[{inputs}], expr#3=[CAST($t2):INTEGER NOT
NULL], EMPNO=[$t2], $f0=[$t0], $f1=[$t1], EMPNO0=[$t3], EMPNO1=[$t3])
EnumerableNestedLoopJoin(condition=[true], joinType=[inner])
EnumerableCalc(expr#0=[{inputs}], $f0=[$t0], $f00=[$t0])
EnumerableAggregate(group=[{}], agg#0=[COUNT()])
@@ -4068,17 +4063,11 @@ EnumerableCalc(expr#0..7=[{inputs}], expr#8=[IS NOT
NULL($t7)], expr#9=[0:BIGINT
EnumerableTableScan(table=[[scott, EMP]])
EnumerableCalc(expr#0=[{inputs}], $f0=[$t0], $f00=[$t0])
EnumerableAggregate(group=[{}], agg#0=[COUNT()])
- EnumerableUnion(all=[true])
- EnumerableValues(tuples=[[{ 500.00, 500.00 }]])
- EnumerableValues(tuples=[[{ 300.00, 300.00 }]])
- EnumerableValues(tuples=[[{ 0.00, 0.00 }]])
+ EnumerableValues(tuples=[[{ 500.00, 500.00 }, { 300.00, 300.00
}, { 0.00, 0.00 }]])
EnumerableSort(sort0=[$0], sort1=[$1], dir0=[ASC], dir1=[ASC])
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=[[{ 500.00, 500.00 }, { 300.00, 300.00 }, {
0.00, 0.00 }]])
!plan
# Test LHS is (nullable, nullable) and RHS is (nullable, nullable)
@@ -4114,19 +4103,145 @@ EnumerableCalc(expr#0..7=[{inputs}], expr#8=[IS NOT
NULL($t7)], expr#9=[0:BIGINT
EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0], COMM=[$t6])
EnumerableTableScan(table=[[scott, EMP]])
EnumerableAggregate(group=[{}], agg#0=[COUNT()], agg#1=[COUNT($0,
$1)])
- 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 }]])
+ EnumerableValues(tuples=[[{ 500.00, 500.00 }, { 300.00, 300.00 },
{ 0.00, 0.00 }, { null, null }]])
EnumerableSort(sort0=[$0], sort1=[$1], dir0=[ASC], dir1=[ASC])
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 }]])
+ EnumerableValues(tuples=[[{ 500.00, 500.00 }, { 300.00, 300.00 }, {
0.00, 0.00 }, { null, null }]])
+!plan
+
+# [CALCITE-5156] Support implicit integer types cast for IN Sub-query
+
+# Test case about the IN sub-query left operand type is INTEGER and right
operand type is TINYINT
+select *
+from dept
+where deptno + 20 in (select deptno from dept);
++--------+------------+----------+
+| DEPTNO | DNAME | LOC |
++--------+------------+----------+
+| 10 | ACCOUNTING | NEW YORK |
+| 20 | RESEARCH | DALLAS |
++--------+------------+----------+
+(2 rows)
+
+!ok
+
+EnumerableCalc(expr#0..3=[{inputs}], proj#0..2=[{exprs}])
+ EnumerableHashJoin(condition=[=($3, $4)], joinType=[semi])
+ EnumerableCalc(expr#0..2=[{inputs}], expr#3=[20], expr#4=[+($t0, $t3)],
proj#0..2=[{exprs}], $f3=[$t4])
+ EnumerableTableScan(table=[[scott, DEPT]])
+ EnumerableCalc(expr#0..2=[{inputs}], expr#3=[CAST($t0):INTEGER NOT NULL],
EXPR$0=[$t3])
+ EnumerableTableScan(table=[[scott, DEPT]])
+!plan
+
+# Test case about the IN sub-query left operand type is BIGINT and right
operand type is TINYINT
+select *
+from dept
+where cast(deptno as bigint) in (select deptno from dept);
++--------+------------+----------+
+| DEPTNO | DNAME | LOC |
++--------+------------+----------+
+| 10 | ACCOUNTING | NEW YORK |
+| 20 | RESEARCH | DALLAS |
+| 30 | SALES | CHICAGO |
+| 40 | OPERATIONS | BOSTON |
++--------+------------+----------+
+(4 rows)
+
+!ok
+
+EnumerableCalc(expr#0..3=[{inputs}], proj#0..2=[{exprs}])
+ EnumerableHashJoin(condition=[=($3, $4)], joinType=[semi])
+ EnumerableCalc(expr#0..2=[{inputs}], expr#3=[CAST($t0):BIGINT NOT NULL],
proj#0..3=[{exprs}])
+ EnumerableTableScan(table=[[scott, DEPT]])
+ EnumerableCalc(expr#0..2=[{inputs}], expr#3=[CAST($t0):BIGINT NOT NULL],
EXPR$0=[$t3])
+ EnumerableTableScan(table=[[scott, DEPT]])
+!plan
+
+# Test case about the IN sub-query left operand type is INTEGER and right
operand type is BIGINT
+select *
+from dept
+where deptno + 10 in (select count(*) + 10 from emp where comm is null);
++--------+------------+----------+
+| DEPTNO | DNAME | LOC |
++--------+------------+----------+
+| 10 | ACCOUNTING | NEW YORK |
++--------+------------+----------+
+(1 row)
+
+!ok
+
+EnumerableCalc(expr#0..3=[{inputs}], proj#0..2=[{exprs}])
+ EnumerableHashJoin(condition=[=($3, $4)], joinType=[semi])
+ EnumerableCalc(expr#0..2=[{inputs}], expr#3=[10], expr#4=[+($t0, $t3)],
expr#5=[CAST($t4):BIGINT NOT NULL], proj#0..2=[{exprs}], $f3=[$t5])
+ EnumerableTableScan(table=[[scott, DEPT]])
+ EnumerableCalc(expr#0=[{inputs}], expr#1=[10], expr#2=[+($t0, $t1)],
EXPR$0=[$t2])
+ EnumerableAggregate(group=[{}], agg#0=[COUNT()])
+ EnumerableCalc(expr#0..7=[{inputs}], expr#8=[IS NULL($t6)],
proj#0..7=[{exprs}], $condition=[$t8])
+ EnumerableTableScan(table=[[scott, EMP]])
+!plan
+
+# Test case about the IN sub-query left operand type is SMALLINT and right
operand type is TINYINT
+select *
+from emp
+where cast(empno - 7349 as smallint) in (select deptno from emp) and ename =
'SMITH';
++-------+-------+-------+------+------------+--------+------+--------+
+| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
++-------+-------+-------+------+------------+--------+------+--------+
+| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | | 20 |
++-------+-------+-------+------+------------+--------+------+--------+
+(1 row)
+
+!ok
+
+EnumerableCalc(expr#0..8=[{inputs}], proj#0..7=[{exprs}])
+ EnumerableHashJoin(condition=[=($8, $9)], joinType=[semi])
+ EnumerableCalc(expr#0..7=[{inputs}], expr#8=[7349], expr#9=[-($t0, $t8)],
expr#10=[CAST($t9):SMALLINT NOT NULL], expr#11=['SMITH':VARCHAR(10)],
expr#12=[=($t1, $t11)], proj#0..7=[{exprs}], $f8=[$t10], $condition=[$t12])
+ EnumerableTableScan(table=[[scott, EMP]])
+ EnumerableCalc(expr#0..7=[{inputs}], expr#8=[CAST($t7):SMALLINT],
EXPR$0=[$t8])
+ EnumerableTableScan(table=[[scott, EMP]])
+!plan
+
+# Test case about the IN sub-query left operand type is SMALLINT and right
operand type is INTEGER
+select *
+from emp
+where empno in (select deptno + 7349 from emp);
++-------+-------+-------+------+------------+--------+------+--------+
+| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
++-------+-------+-------+------+------------+--------+------+--------+
+| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | | 20 |
++-------+-------+-------+------+------------+--------+------+--------+
+(1 row)
+
+!ok
+
+EnumerableCalc(expr#0..8=[{inputs}], proj#0..7=[{exprs}])
+ EnumerableHashJoin(condition=[=($8, $9)], joinType=[semi])
+ EnumerableCalc(expr#0..7=[{inputs}], expr#8=[CAST($t0):INTEGER NOT NULL],
proj#0..8=[{exprs}])
+ EnumerableTableScan(table=[[scott, EMP]])
+ EnumerableCalc(expr#0..7=[{inputs}], expr#8=[7349], expr#9=[+($t7, $t8)],
EXPR$0=[$t9])
+ EnumerableTableScan(table=[[scott, EMP]])
+!plan
+
+# Test case about the IN sub-query left operand type is SMALLINT and right
operand type is BIGINT
+select *
+from emp
+where empno in (select cast(deptno + 7349 as bigint) from emp);
++-------+-------+-------+------+------------+--------+------+--------+
+| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
++-------+-------+-------+------+------------+--------+------+--------+
+| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | | 20 |
++-------+-------+-------+------+------------+--------+------+--------+
+(1 row)
+
+!ok
+
+EnumerableCalc(expr#0..8=[{inputs}], proj#0..7=[{exprs}])
+ EnumerableHashJoin(condition=[=($8, $9)], joinType=[semi])
+ EnumerableCalc(expr#0..7=[{inputs}], expr#8=[CAST($t0):BIGINT NOT NULL],
proj#0..8=[{exprs}])
+ EnumerableTableScan(table=[[scott, EMP]])
+ EnumerableCalc(expr#0..7=[{inputs}], expr#8=[7349], expr#9=[+($t7, $t8)],
expr#10=[CAST($t9):BIGINT], EXPR$0=[$t10])
+ EnumerableTableScan(table=[[scott, EMP]])
!plan
# End sub-query.iq
diff --git
a/kafka/src/test/java/org/apache/calcite/adapter/kafka/KafkaAdapterTest.java
b/kafka/src/test/java/org/apache/calcite/adapter/kafka/KafkaAdapterTest.java
index 13088474cc..7ed08f0ab0 100644
--- a/kafka/src/test/java/org/apache/calcite/adapter/kafka/KafkaAdapterTest.java
+++ b/kafka/src/test/java/org/apache/calcite/adapter/kafka/KafkaAdapterTest.java
@@ -84,7 +84,7 @@ class KafkaAdapterTest {
.returnsUnordered(
"MSG_PARTITION=0; MSG_OFFSET=1; MSG_VALUE_BYTES=myvalue1")
.explainContains(
- "PLAN=EnumerableCalc(expr#0..4=[{inputs}], expr#5=[0],
expr#6=[>($t2, $t5)], MSG_PARTITION=[$t0], MSG_OFFSET=[$t2],
MSG_VALUE_BYTES=[$t4], $condition=[$t6])\n"
+ "PLAN=EnumerableCalc(expr#0..4=[{inputs}], expr#5=[0:BIGINT],
expr#6=[>($t2, $t5)], MSG_PARTITION=[$t0], MSG_OFFSET=[$t2],
MSG_VALUE_BYTES=[$t4], $condition=[$t6])\n"
+ " EnumerableInterpreter\n"
+ " BindableTableScan(table=[[KAFKA, MOCKTABLE,
(STREAM)]])");
}
diff --git a/spark/src/test/java/org/apache/calcite/test/SparkAdapterTest.java
b/spark/src/test/java/org/apache/calcite/test/SparkAdapterTest.java
index 10448b118f..1f57407683 100644
--- a/spark/src/test/java/org/apache/calcite/test/SparkAdapterTest.java
+++ b/spark/src/test/java/org/apache/calcite/test/SparkAdapterTest.java
@@ -220,7 +220,7 @@ class SparkAdapterTest {
+ "having count(*) > 2";
final String plan = "PLAN="
- + "EnumerableCalc(expr#0..1=[{inputs}], expr#2=[2], expr#3=[>($t1,
$t2)], X=[$t0], $condition=[$t3])\n"
+ + "EnumerableCalc(expr#0..1=[{inputs}], expr#2=[2:BIGINT],
expr#3=[>($t1, $t2)], X=[$t0], $condition=[$t3])\n"
+ " EnumerableAggregate(group=[{0}], agg#0=[COUNT()])\n"
+ " EnumerableValues(tuples=[[{ 1, 'a' }, { 2, 'b' }, { 1, 'b' }, {
2, 'c' }, { 2, 'c' }]])\n\n";
@@ -240,9 +240,7 @@ class SparkAdapterTest {
+ "from " + VALUES2;
final String plan = "PLAN="
- + "EnumerableUnion(all=[true])\n"
- + " EnumerableValues(tuples=[[{ 1, 'a' }, { 2, 'b' }]])\n"
- + " EnumerableValues(tuples=[[{ 1, 'a' }, { 2, 'b' }, { 1, 'b' }, {
2, 'c' }, { 2, 'c' }]])\n";
+ + "EnumerableValues(tuples=[[{ 1, 'a' }, { 2, 'b' }, { 1, 'a' }, { 2,
'b' }, { 1, 'b' }, { 2, 'c' }, { 2, 'c' }]])\n\n";
final String[] expectedResult = {
"X=1; Y=a",
@@ -266,9 +264,7 @@ class SparkAdapterTest {
+ "from " + VALUES2;
final String plan = "PLAN="
- + "EnumerableUnion(all=[false])\n"
- + " EnumerableValues(tuples=[[{ 1, 'a' }, { 2, 'b' }]])\n"
- + " EnumerableValues(tuples=[[{ 1, 'a' }, { 2, 'b' }, { 1, 'b' }, {
2, 'c' }, { 2, 'c' }]])\n";
+ + "EnumerableValues(tuples=[[{ 1, 'a' }, { 2, 'b' }, { 1, 'b' }, { 2,
'c' }]])\n\n";
final String[] expectedResult = {
"X=1; Y=a",
