Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
10.2(EOL)
-
10.2.4-2
Description
CREATE TABLE t1 (a INT); |
INSERT INTO t1 VALUES (0),(2); |
|
|
CREATE TABLE t2 (b INT); |
INSERT INTO t2 VALUES (1),(2); |
|
|
CREATE OR REPLACE ALGORITHM=TEMPTABLE VIEW v2 AS SELECT * FROM t2; |
|
|
SELECT * FROM t1 LEFT JOIN v2 ON a = b WHERE b IS NULL; |
|
Actual result, 10.2 82b974a1b6 |
MariaDB [test]> SELECT * FROM t1 LEFT JOIN v2 ON a = b WHERE b IS NULL;
|
+------+------+
|
| a | b |
|
+------+------+
|
| 0 | NULL |
|
| 2 | NULL |
|
+------+------+
|
2 rows in set (0.00 sec)
|
|
Expected result |
MariaDB [test]> SELECT * FROM t1 LEFT JOIN v2 ON a = b WHERE b IS NULL;
|
+------+------+
|
| a | b |
|
+------+------+
|
| 0 | NULL |
|
+------+------+
|
1 row in set (0.00 sec)
|
Attachments
Activity
Two patches were pushed into the 10.2 tree to resolve this problem. The first patch contained an incorrect fix, while the other one finally fixed the bug.
The problem is that condition "b IS NULL" was pushed into the view. So it checked field not after LEFT JOIN where it should filter only mismatched in LEFT JOIN records but instead it filtered all records from the view (they are all not null).
IMHO condition pushdown should be prohibited in LEFT table of LEFT JOIN (as well as RIGHT part of RIGHT JOIN, but we have it the same in your implementation).