[
https://issues.apache.org/jira/browse/CALCITE-7437?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18075690#comment-18075690
]
Silun Dong commented on CALCITE-7437:
-------------------------------------
There is another related issue. As far as I know, when performing >,<,>=,<=
comparisons on ROW elements, the comparison proceeds from left to right,
stopping as soon as an unequal or null pair of elements is found. For example:
{code:java}
// query
with
t1(id, age) as (values(2, 20)),
t2(id, age) as (values(2, 10))
select id, age, (id, age) > SOME(select id, age from t2) from t1;
// correct result
+----+-----+--------+
| ID | AGE | EXPR$2 |
+----+-----+--------+
| 2 | 20 | true |
+----+-----+--------+
(1 row)
// but now
+----+-----+--------+
| ID | AGE | EXPR$2 |
+----+-----+--------+
| 2 | 20 | false |
+----+-----+--------+
(1 row){code}
I found {{SqlToRelConverter}} expands the ROW into individual operands in the
RexSubQuery:
{code:java}
> SOME($0, $1, {
LogicalProject(ID=[$0], AGE=[$1])
LogicalValues(tuples=[[{ 2, 10 }]])
}){code}
This seems to cause the comparison semantics of ROW to be broken during
subquery removal. Should we replace it with the following form:
{code:java}
> SOME(ROW($0, $1), {
LogicalProject(ROW($0, $1))
LogicalValues(tuples=[[{ 2, 10 }]])
}){code}
> 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
> Assignee: krooswu
> Priority: Major
> Labels: pull-request-available
>
> 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)
