fornwall opened a new issue, #25283:
URL: https://github.com/apache/datafusion/issues/25283
### Describe the bug
A correlated `EXISTS` subquery that has an `OFFSET` returns rows for which
the subquery is actually empty.
The `OFFSET` is silently dropped when the subquery is rewritten into a semi
join, so the query behaves as if the `OFFSET` was not there.
An `OFFSET` can change whether a subquery is empty: `SELECT ... OFFSET 1` is
empty when the input has one row. `EXISTS` must respect that, but DataFusion
does not.
### To Reproduce
With `datafusion-cli`:
```sql
CREATE TABLE t1(k INT) AS VALUES (1), (2), (3);
CREATE TABLE t2(v INT) AS VALUES (1), (1), (3);
SELECT k FROM t1 WHERE EXISTS (SELECT * FROM t2 WHERE t2.v = t1.k OFFSET 1);
```
Actual output:
```
+---+
| k |
+---+
| 1 |
| 3 |
+---+
```
Only `k = 1` has two matching rows in `t2`, so only for `k = 1` is there a
row left after skipping one. `k = 3` has a single match, so the subquery is
empty for it and it must not be returned.
The equivalent query written with `count(*)` gives the correct answer:
```sql
SELECT k FROM t1 WHERE (SELECT count(*) FROM t2 WHERE t2.v = t1.k) > 1;
```
```
+---+
| k |
+---+
| 1 |
+---+
```
`EXPLAIN` shows that the `OFFSET` does not survive planning. The subquery
becomes a plain `LeftSemi` join on `k = v` with no `Limit` node at all:
```
LeftSemi Join: t1.k = __correlated_sq_1.v
TableScan: t1 projection=[k]
SubqueryAlias: __correlated_sq_1
TableScan: t2 projection=[v]
```
`NOT EXISTS` with an `OFFSET` is affected in the same way (it returns `2`
only, but `2` and `3` are expected).
### Expected behavior
```
+---+
| k |
+---+
| 1 |
+---+
```
### Additional context
Reproduced on `main` at 15f32dd7a.
--
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]
