Silun Dong created CALCITE-7437:
-----------------------------------
Summary: 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
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)
