AdamGS commented on PR #25008:
URL: https://github.com/apache/datafusion/pull/25008#issuecomment-5666349461
I did a pass with claude, and it spit out the following tests that all pass
on `main` and in DuckDB, but fail on this branch:
```sql
statement ok
CREATE TABLE outer_rows(k INT);
statement ok
INSERT INTO outer_rows VALUES (1);
statement ok
CREATE TABLE inner_rows(k INT, v INT);
statement ok
INSERT INTO inner_rows VALUES (1, NULL);
statement ok
CREATE TABLE text_keys(k VARCHAR);
statement ok
INSERT INTO text_keys VALUES ('1'), ('01');
# HAVING evaluates to NULL for the matching group and must discard its row.
query I
SELECT o.k
FROM outer_rows o
WHERE EXISTS (
SELECT count(*) FROM inner_rows i WHERE i.k = o.k
HAVING count(*) = 0 OR sum(i.v) > 0
);
----
# The same NULL-valued HAVING makes the subquery empty, so NOT IN is true.
query I
SELECT o.k
FROM outer_rows o
WHERE 1 NOT IN (
SELECT count(*) FROM inner_rows i WHERE i.k = o.k
HAVING count(*) = 0 OR sum(i.v) > 0
);
----
1
# LIMIT 0 removes the aggregate row, so EXISTS is false.
query I
SELECT o.k
FROM outer_rows o
WHERE EXISTS (
SELECT count(*) FROM inner_rows i WHERE i.k = o.k LIMIT 0
);
----
# Both text keys cast to 1, but EXISTS must return the outer row only once.
query I
SELECT o.k
FROM outer_rows o
WHERE EXISTS (
SELECT count(*) FROM text_keys i WHERE CAST(i.k AS INT) = o.k
);
----
1
```
I didn't have the time to try and dive into the individual causes here, but
might be worth it to try and split this PR into smaller ones that are more
specific (and potentially stacked) as each one seems to be pretty hard to review
--
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]
