[MDEV-11102] Wrong result (extra rows) on LEFT JOIN with a view and IS NULL in WHERE, condition_pushdown_for_derived=on Created: 2016-10-21  Updated: 2016-11-17  Resolved: 2016-11-17

Status: Closed
Project: MariaDB Server
Component/s: Optimizer, Views
Affects Version/s: 10.2
Fix Version/s: 10.2.3

Type: Bug Priority: Critical
Reporter: Elena Stepanova Assignee: Igor Babaev
Resolution: Fixed Votes: 0
Labels: regression-10.2

Sprint: 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)



 Comments   
Comment by Oleksandr Byelkin [ 2016-11-16 ]

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).

Comment by Igor Babaev [ 2016-11-17 ]

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.

Generated at Thu Feb 08 07:47:20 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.