acbokade opened a new issue, #26285:
URL: https://github.com/apache/datafusion/issues/26285
### Describe the bug
A correlated `EXISTS` subquery fails at execution time with
```
Arrow error: Invalid argument error: Can't compare arrays of different types
```
when the outer column it references only gets its final type during type
coercion, for example `CASE WHEN ... THEN int_col ELSE 3 END` (an `INT` branch
first and a `BIGINT` one later) or a `UNION ALL` of an `INT` and a `BIGINT`
column.
The subquery's filter never gets a cast: after `type_coercion` it is still
`u.c0 = outer_ref(s.k)` although `s.k` has become `Int64`, so after
decorrelation the join compares an `Int64` key with an `Int32` key.
### To Reproduce
```sql
CREATE TABLE t(id INT, c1 INT) AS VALUES (1, 0), (100000, 0), (3, 5);
CREATE TABLE u(c0 INT) AS VALUES (1), (3), (100000);
SELECT s.k
FROM (SELECT CASE WHEN c1 = 0 THEN id ELSE 3 END AS k FROM t) s
WHERE EXISTS (SELECT 1 FROM u WHERE u.c0 = s.k);
```
```
Arrow error: Invalid argument error: Can't compare arrays of different types
```
The same happens with `UNION ALL`:
```sql
CREATE TABLE a(k INT) AS VALUES (1), (100000);
CREATE TABLE b(k BIGINT) AS VALUES (3);
-- u as above
SELECT s.k
FROM (SELECT k FROM a UNION ALL SELECT k FROM b) s
WHERE EXISTS (SELECT 1 FROM u WHERE u.c0 = s.k);
```
`NOT EXISTS` and a correlated scalar subquery such as `(SELECT count(*) FROM
u WHERE u.c0 = s.k)` fail the same way. Putting the `BIGINT` branch first
(`CASE WHEN c1 <> 0 THEN 3 ELSE id END`), or using a join instead of `EXISTS`,
works. With small, dense keys such as `1, 2, 3` the default plan happens to
return the right rows, but it still fails with `SET
datafusion.optimizer.prefer_hash_join = false`.
Tested on `main` at 0566b55a4.
### Expected behavior
Both queries return `1`, `3` and `100000`, as in PostgreSQL.
### Additional context
For the `CASE` query, the subquery sees the outer column with its type from
before coercion: `arrow_typeof(s.k)` is `Int64` in the outer query, but inside
the subquery `arrow_typeof(s.k) = 'Int32'` is true. (`Expr::Case::get_type`
returns the type of the first non-null `THEN` branch.) The final plan joins `k:
Int64` with `c0: Int32`:
```
HashJoinExec: mode=CollectLeft, join_type=LeftSemi, on=[(k@0, c0@0)],
schema=[k:Int64;N]
ProjectionExec: expr=[CASE WHEN c1@1 = 0 THEN CAST(id@0 AS Int64) ELSE 3
END as k], schema=[k:Int64;N]
DataSourceExec: partitions=1, partition_sizes=[1], schema=[id:Int32;N,
c1:Int32;N]
DataSourceExec: partitions=1, partition_sizes=[1], schema=[c0:Int32;N]
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
