Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.11, 11.4, 11.8, 12.3, 13.1, 12.3.3, 13.0.2, 13.1.1
-
x86_64 Ubuntu24.04
-
Unexpected results
-
A join query without GROUP BY could return fewer rows than expected when its HAVING clause contained a condition OR its negation. The equivalent condition in WHERE returned the correct rows, as did disabling index access on the affected table.
Description
A join without GROUP BY loses rows when the HAVING clause has the form X OR NOT X and X compares a column of each table. Every joined row has non-NULL values, so X OR NOT X is true for every row and HAVING should not remove anything.
In the script the join produces 12 rows. With the HAVING clause the query returns 9. The missing rows are the ones with l.c0 = -3, -2 and -1. These are exactly the values inside BETWEEN -5 AND 0, a range that the ON clause states for r.c0, not for l.c0. This suggests that an incorrect range inference or predicate transformation may be involved, although the exact cause has not been established.
The result is correct (12 rows) when the same condition is written in WHERE, and with IGNORE INDEX on l. EXPLAIN shows range access on the index of l. SET optimizer_switch='condition_pushdown_from_having=off' does not change the wrong result.
How to reproduce
CREATE TABLE t0(c0 INT, UNIQUE KEY(c0)); |
CREATE TABLE t1(c0 INT, UNIQUE KEY(c0)); |
INSERT INTO t0 VALUES (-100),(1),(2),(3),(4),(5),(6),(7),(8),(9); |
INSERT INTO t1 VALUES (-30),(-20),(-3),(-2),(-1),(10),(20),(30),(40),(50),(60),(70); |
|
|
SELECT l.c0 AS f0, r.c0 AS f1 |
FROM t1 l JOIN (SELECT * FROM t0 r WHERE r.c0 <= -50) r ON r.c0 NOT BETWEEN -5 AND 0 |
ORDER BY f0; |
-- 12 rows: every t1 row paired with -100
|
|
|
SELECT l.c0 AS f0, r.c0 AS f1 |
FROM t1 l JOIN (SELECT * FROM t0 r WHERE r.c0 <= -50) r ON r.c0 NOT BETWEEN -5 AND 0 |
WHERE ((r.c0 <> l.c0 OR r.c0 IS NOT NULL) AND l.c0 < -25) OR NOT ((r.c0 <> l.c0 OR r.c0 IS NOT NULL) AND l.c0 < -25) |
ORDER BY f0; |
-- 12 rows (correct)
|
|
|
SELECT l.c0 AS f0, r.c0 AS f1 |
FROM t1 l JOIN (SELECT * FROM t0 r WHERE r.c0 <= -50) r ON r.c0 NOT BETWEEN -5 AND 0 |
HAVING ((f1 <> f0 OR f1 IS NOT NULL) AND f0 < -25) OR NOT ((f1 <> f0 OR f1 IS NOT NULL) AND f0 < -25) |
ORDER BY f0; |
-- expected: 12 rows
|
-- actual: 9 rows; f0 = -3, -2, -1 are missing
|
|
|
SELECT l.c0 AS f0, r.c0 AS f1 |
FROM t1 l IGNORE INDEX (c0) JOIN (SELECT * FROM t0 r WHERE r.c0 <= -50) r ON r.c0 NOT BETWEEN -5 AND 0 |
HAVING ((f1 <> f0 OR f1 IS NOT NULL) AND f0 < -25) OR NOT ((f1 <> f0 OR f1 IS NOT NULL) AND f0 < -25) |
ORDER BY f0; |
-- 12 rows (correct)
|
|
|
EXPLAIN SELECT l.c0 AS f0, r.c0 AS f1 |
FROM t1 l JOIN (SELECT * FROM t0 r WHERE r.c0 <= -50) r ON r.c0 NOT BETWEEN -5 AND 0 |
HAVING ((f1 <> f0 OR f1 IS NOT NULL) AND f0 < -25) OR NOT ((f1 <> f0 OR f1 IS NOT NULL) AND f0 < -25); |
-- r range key=c0 rows=1 Using where; Using index
|
-- l range key=c0 rows=9 Using where; Using index; Using join buffer (flat, BNL join) |
Attachments
Issue Links
- relates to
-
-
- Closed
-