jayzhan211 commented on PR #25386:
URL: https://github.com/apache/datafusion/pull/25386#issuecomment-5716140431
It seems Q1-Q4 are fixed, should we go with Q5-Q8?
The queries the PR's 3x–112x numbers come from have no correctness canary,
and on main they return wrong results, so the "base" column is timing an
incorrect fast path. `x NOT IN (<empty>)` is TRUE, but main drops every outer
row whose scope is empty (`o.z = 0`) once a NULL exists anywhere, and Q08
returns all rows. Measured on main at defaults (NOT IN count / reference count):
| Query | NOT IN | reference |
|---|---|---|
| Q05 | 7450 | 7460 |
| Q06 | 5000 | 5010 |
| Q07 | 0 | 10 |
| Q08 | 10000 | 5530 |
These asserts fail on main, so land them in #25339 (where they should pass)
rather than here, and note in this PR's table that base Q05–Q08 are
wrong-result timings:
```sql
-- q05 / q06 (swap id_n1 for id_n50 in q06)
assert I
SELECT count(*) = (
SELECT count(*) FROM small_outer o
WHERE o.z = 0
OR (o.id_n1 IS NOT NULL AND NOT (o.id % 2 = 0 AND (o.id / 2) % 1000 <
o.z))
)
FROM small_outer o
WHERE o.id_n1 NOT IN (SELECT i.id_n0 FROM small_inner i WHERE i.z < o.z);
----
true
-- q07: inner v = 0 is NULL and has z = 0, so it is in scope for every o.z > 0
assert I
SELECT count(*) = (SELECT count(*) FROM small_outer WHERE z = 0)
FROM small_outer o
WHERE o.id_n0 NOT IN (SELECT i.id_n50 FROM small_inner i WHERE i.z < o.z);
----
true
-- q08
assert I
SELECT count(*) = (
SELECT count(*) FROM small_outer o
WHERE o.z > 900
OR NOT EXISTS (SELECT 1 FROM small_inner i WHERE i.k = o.k AND i.z <
o.z)
OR (o.id_n50 IS NOT NULL
AND NOT (o.id % 2 = 0 AND (o.id / 2) % 16 = o.k AND (o.id / 2) %
1000 < o.z))
)
FROM small_outer o
WHERE o.z > 900
OR o.id_n50 NOT IN (
SELECT i.id_n0 FROM small_inner i WHERE i.k = o.k AND i.z < o.z
);
----
true
```
--
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]
