[
https://issues.apache.org/jira/browse/CALCITE-7437?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Silun Dong updated CALCITE-7437:
--------------------------------
Description:
The follow sql will throw an error at runtime:
{code:java}
SELECT deptno, dname > SOME(SELECT empno FROM emp) AS b FROM dept;
// the plan in CoreQuidemTest
EnumerableCalc(expr#0..4=[{inputs}], expr#5=[>($t4, $t0)], expr#6=[IS
TRUE($t5)], expr#7=[0], expr#8=[<>($t1, $t7)], expr#9=[AND($t6, $t8)],
expr#10=[>($t1, $t2)], expr#11=[null:BOOLEAN], expr#12=[IS NOT TRUE($t5)],
expr#13=[AND($t10, $t11, $t8, $t12)], expr#14=[<=($t1, $t2)], expr#15=[AND($t5,
$t8, $t12, $t14)], expr#16=[OR($t9, $t13, $t15)], DEPTNO=[$t3], B=[$t16])
EnumerableNestedLoopJoin(condition=[true], joinType=[inner])
EnumerableCalc(expr#0..1=[{inputs}], proj#0..1=[{exprs}], d=[$t1])
EnumerableAggregate(group=[{}], m=[MIN($0)], c=[COUNT()])
EnumerableTableScan(table=[[scott, EMP]])
EnumerableCalc(expr#0..2=[{inputs}], proj#0..1=[{exprs}])
EnumerableTableScan(table=[[scott, DEPT]])
// the plan in CoreQuidemTest2
EnumerableCalc(expr#0..2=[{inputs}], DEPTNO=[$t0], B=[$t2])
EnumerableNestedLoopJoin(condition=[>($1, $2)], joinType=[left_mark])
EnumerableCalc(expr#0..2=[{inputs}], proj#0..1=[{exprs}])
EnumerableTableScan(table=[[scott, DEPT]])
EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0])
EnumerableTableScan(table=[[scott, EMP]])
// both will throw an error at runtime
java.lang.RuntimeException: while resolving method 'gt[class java.lang.String,
short]' in class class org.apache.calcite.runtime.SqlFunctions{code}
The type of {{dname}} is VARCHAR, while the type of {{empno}} is SMALLINT.
There is no type coercion being applied here, is that expected? The current
implementation in Calcite seems to perform type coercion by default. It's just
that the quantifier operators only handle the case where the right operand is a
[collection
expression|https://github.com/apache/calcite/blob/c628e6876bc28f268a69dece649c172869118607/core/src/main/java/org/apache/calcite/sql/fun/SqlQuantifyOperator.java#L109]
during type coercion, ignoring subqueries.
Should we consider this a bug and improve the subqueries case?
was:
The follow sql will throw an error at runtime:
{code:java}
SELECT deptno, dname > SOME(SELECT empno FROM emp) AS b FROM dept;
// the plan in CoreQuidemTest
EnumerableCalc(expr#0..4=[{inputs}], expr#5=[>($t4, $t0)], expr#6=[IS
TRUE($t5)], expr#7=[0], expr#8=[<>($t1, $t7)], expr#9=[AND($t6, $t8)],
expr#10=[>($t1, $t2)], expr#11=[null:BOOLEAN], expr#12=[IS NOT TRUE($t5)],
expr#13=[AND($t10, $t11, $t8, $t12)], expr#14=[<=($t1, $t2)], expr#15=[AND($t5,
$t8, $t12, $t14)], expr#16=[OR($t9, $t13, $t15)], DEPTNO=[$t3], B=[$t16])
EnumerableNestedLoopJoin(condition=[true], joinType=[inner])
EnumerableCalc(expr#0..1=[{inputs}], proj#0..1=[{exprs}], d=[$t1])
EnumerableAggregate(group=[{}], m=[MIN($0)], c=[COUNT()])
EnumerableTableScan(table=[[scott, EMP]])
EnumerableCalc(expr#0..2=[{inputs}], proj#0..1=[{exprs}])
EnumerableTableScan(table=[[scott, DEPT]])
// the plan in CoreQuidemTest2
EnumerableCalc(expr#0..2=[{inputs}], DEPTNO=[$t0], B=[$t2])
EnumerableNestedLoopJoin(condition=[>($1, $2)], joinType=[left_mark])
EnumerableCalc(expr#0..2=[{inputs}], proj#0..1=[{exprs}])
EnumerableTableScan(table=[[scott, DEPT]])
EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0])
EnumerableTableScan(table=[[scott, EMP]])
// both will throw an error at runtime
java.lang.RuntimeException: while resolving method 'gt[class java.lang.String,
short]' in class class org.apache.calcite.runtime.SqlFunctions{code}
The type of {{dname}} is VARCHAR, while the type of {{empno}} is SMALLINT.
There is no type coercion being applied here, is that expected? The current
implementation in Calcite seems to perform type coercion by default. It's just
that the quantifier operators only handle the case where the right operand is a
[collection
expression|https://github.com/apache/calcite/blob/c628e6876bc28f268a69dece649c172869118607/core/src/main/java/org/apache/calcite/sql/fun/SqlQuantifyOperator.java#L109]
during type coercion, ignoring subqueries.
Should we consider this a bug and improve the subqueries case?
> Type coercion for quantifier operators is incomplete
> ----------------------------------------------------
>
> Key: CALCITE-7437
> URL: https://issues.apache.org/jira/browse/CALCITE-7437
> Project: Calcite
> Issue Type: Bug
> Components: core
> Reporter: Silun Dong
> Priority: Major
>
> The follow sql will throw an error at runtime:
> {code:java}
> SELECT deptno, dname > SOME(SELECT empno FROM emp) AS b FROM dept;
> // the plan in CoreQuidemTest
> EnumerableCalc(expr#0..4=[{inputs}], expr#5=[>($t4, $t0)], expr#6=[IS
> TRUE($t5)], expr#7=[0], expr#8=[<>($t1, $t7)], expr#9=[AND($t6, $t8)],
> expr#10=[>($t1, $t2)], expr#11=[null:BOOLEAN], expr#12=[IS NOT TRUE($t5)],
> expr#13=[AND($t10, $t11, $t8, $t12)], expr#14=[<=($t1, $t2)],
> expr#15=[AND($t5, $t8, $t12, $t14)], expr#16=[OR($t9, $t13, $t15)],
> DEPTNO=[$t3], B=[$t16])
> EnumerableNestedLoopJoin(condition=[true], joinType=[inner])
> EnumerableCalc(expr#0..1=[{inputs}], proj#0..1=[{exprs}], d=[$t1])
> EnumerableAggregate(group=[{}], m=[MIN($0)], c=[COUNT()])
> EnumerableTableScan(table=[[scott, EMP]])
> EnumerableCalc(expr#0..2=[{inputs}], proj#0..1=[{exprs}])
> EnumerableTableScan(table=[[scott, DEPT]])
> // the plan in CoreQuidemTest2
> EnumerableCalc(expr#0..2=[{inputs}], DEPTNO=[$t0], B=[$t2])
> EnumerableNestedLoopJoin(condition=[>($1, $2)], joinType=[left_mark])
> EnumerableCalc(expr#0..2=[{inputs}], proj#0..1=[{exprs}])
> EnumerableTableScan(table=[[scott, DEPT]])
> EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0])
> EnumerableTableScan(table=[[scott, EMP]])
> // both will throw an error at runtime
> java.lang.RuntimeException: while resolving method 'gt[class
> java.lang.String, short]' in class class
> org.apache.calcite.runtime.SqlFunctions{code}
> The type of {{dname}} is VARCHAR, while the type of {{empno}} is SMALLINT.
> There is no type coercion being applied here, is that expected? The current
> implementation in Calcite seems to perform type coercion by default. It's
> just that the quantifier operators only handle the case where the right
> operand is a [collection
> expression|https://github.com/apache/calcite/blob/c628e6876bc28f268a69dece649c172869118607/core/src/main/java/org/apache/calcite/sql/fun/SqlQuantifyOperator.java#L109]
> during type coercion, ignoring subqueries.
> Should we consider this a bug and improve the subqueries case?
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
