alexy opened a new issue, #26058:
URL: https://github.com/apache/datafusion/issues/26058
### Describe the bug
An inner join whose `ON` has an ordinary equi-key plus a second equality
whose one side spans two relations of a cross join loses the second equality.
The query returns rows that do not satisfy the join condition.
`extract_equijoin_predicate` turns `b.id = CASE k.k WHEN 0 THEN a.x ELSE a.y
END` into an equi-join key whose left expression references both `a` and `k`.
`eliminate_cross_join` then flattens `(a CROSS JOIN k) JOIN b` into one join
graph and rebuilds it as `(a JOIN b ON a.t = b.t) CROSS JOIN k`. The key that
references both `a` and `k` is not attached to any join and is not kept as a
filter.
Reproduced on 55.1.0 and on `main` (8248a57969). Running the query with each
logical optimizer rule removed in turn, it is correct without
`extract_equijoin_predicate` or without `eliminate_cross_join`, and wrong
without any other single rule.
### To Reproduce
```sql
SELECT k.k, b.id
FROM (SELECT 1 AS t, 17 AS x, 18 AS y) a
CROSS JOIN (SELECT 0 AS k UNION ALL SELECT 1) k
JOIN (SELECT 1 AS t, CAST(value AS INT) AS id FROM generate_series(0, 20)) b
ON b.t = a.t AND b.id = CASE k.k WHEN 0 THEN a.x ELSE a.y END
ORDER BY k.k, b.id;
```
returns 42 rows: every `b.id` from 0 to 20, once for each `k`.
The optimized logical plan has no trace of the `CASE` predicate:
```
Sort: k.k ASC NULLS LAST, b.id ASC NULLS LAST
Projection: k.k, b.id
Cross Join:
Projection: b.id
Inner Join: a.t = b.t
SubqueryAlias: a
Projection: Int64(1) AS t
EmptyRelation: rows=1
SubqueryAlias: b
Projection: Int64(1) AS t, CAST(generate_series().value AS
Int32) AS id
TableScan: generate_series() projection=[value]
SubqueryAlias: k
Union
...
```
### Expected behavior
Two rows, `(0, 17)` and `(1, 18)`.
Without `b.t = a.t` in the `ON` clause the result is correct, as it is when
the `CASE` predicate is written in a `WHERE` instead of the `ON` clause.
### Additional context
Found through [Sail](https://github.com/lakehq/sail), where a query joining
a table on `s.tic = rt.tic AND s.id = CASE k.k WHEN 0 THEN rt.sector_id ELSE
COALESCE(rt.spawn_sector_id, rt.sector_id) END` over a cross join with `k`
returned every row for that `tic`.
--
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]
