WHERE condition not observed with views in maria-5.3 after MWL#106

[Philip Stoev]

Repeatable in maria-5.3. Not repeatable on mysql-5.5 or maria-5.3 before WL#106. Not repeatable if a base table is vused instead of a view. influenced by optimizer_switch. Repeatable if the view is inlined as a derived table.

The following query:

SELECT t3.f2, v1.f3, v1.f2 FROM t3 LEFT JOIN (t2,v1) ON t3.f1 WHERE v1.f3 = v1.f3 OR t3.f2 != 0;

returns 1 row which does not match the WHERE predicate:

+------+------+------+
| f2 | f3 | f2 |
+------+------+------+
| 2 | NULL | NULL |
| NULL | NULL | NULL |
+------+------+------+

for the second row, nether side of the OR expression is TRUE, therefore the entire WHERE predicate must be FALSE.

explain:

+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | t3 | ALL | NULL | NULL | NULL | NULL | 2 | |
| 1 | SIMPLE | t2 | ALL | NULL | NULL | NULL | NULL | 0 | Using where |
| 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 5 | |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

explain in before WL#106:

mysql> explain SELECT t3.f2, v1.f3, v1.f2 FROM t3 LEFT JOIN (t2,v1) ON t3.f1 WHERE v1.f3 = v1.f3 OR t3.f2 != 0;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | t3 | ALL | NULL | NULL | NULL | NULL | 2 | |
| 1 | SIMPLE | t2 | ALL | NULL | NULL | NULL | NULL | 0 | Using where |
| 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 5 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

test case:

CREATE TABLE t1 (f1 int(11) NOT NULL ,f2 int(11) NOT NULL ,f3 int(11) NOT NULL ,f4 int);
INSERT INTO t1 VALUES (1,0,1,NULL),(50,0,2,122),(8,0,0,220),(51,238,169,0),(139,145,151,0);

CREATE TABLE t2 ( f1 int NOT NULL, f2 int, f3 int, f4 int );

CREATE TABLE t3 ( f1 int, f2 int, f3 int, f4 int );
INSERT INTO t3 VALUES (76,2,169,0),(1,NULL,0,0);

CREATE ALGORITHM=MERGE VIEW v1 AS select * FROM t1;

SELECT t3.f2, v1.f3, v1.f2 FROM t3 LEFT JOIN (t2,v1) ON t3.f1 WHERE v1.f3 = v1.f3;

SELECT t3.f2, v1.f3, v1.f2 FROM t3 LEFT JOIN (t2,v1) ON t3.f1 WHERE v1.f3 = v1.f3 OR t3.f2 != 0;

bzr version-info:

revision-id: <email address hidden>
date: 2011-07-06 10:30:51 +0400
build-date: 2011-07-06 16:11:10 +0300
revno: 3085
branch-nick: maria-5.3