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:

--------------

--------------

--------------

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

explain:

-------------------------------------------------------------------+

-------------------------------------------------------------------+

-------------------------------------------------------------------+

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;
-------------------------------------------------------------------+

-------------------------------------------------------------------+

-------------------------------------------------------------------+

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