[CALCITE-5930] When sqlToRel is converted from in to join, condition '=' on both sides will have inconsistent data types.

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Duplicate
• Labels: pull-request-available
• Assignee: Unassigned
• Reporter: libopeng
• Created: 15/Aug/23 13:36
• Updated: 06/Jun/24 00:46
• Resolved: 18/Aug/23 02:52

Description

Wrong calculation result

select *
from dept
where deptno in (
  select count(*)
  from emp
  where comm is null);
+--------+------------+----------+
| DEPTNO |   DNAME    |   LOC    |
+--------+------------+----------+
+--------+------------+----------+ 

Correct calculation results

select *
from dept
where cast(deptno as bigint) in (
  select count(*)
  from emp
  where comm is null); 

+--------+------------+----------+
| DEPTNO | DNAME      | LOC      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
+--------+------------+----------+

The above two cases can be reproduced in agg.iq.

Or look at this submission https://github.com/apache/calcite/commit/6cef259f29884fbda3f165d579232285998d1b46.

 
 
 
 
 

Issue Links

• duplicates: CALCITE-5156 Support implicit integer type cast for IN Sub-query
• links to: GitHub Pull Request #3371