WHERE condition not observed with views in maria-5.3 after MWL#106
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
MariaDB |
Fix Released
|
High
|
Igor Babaev |
Bug Description
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,
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,
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
Changed in maria: | |
milestone: | none → 5.3 |
assignee: | nobody → Igor Babaev (igorb-seattle) |
Changed in maria: | |
status: | New → Confirmed |
importance: | Undecided → High |
Changed in maria: | |
status: | Confirmed → In Progress |
Changed in maria: | |
status: | In Progress → Fix Committed |
Changed in maria: | |
status: | Fix Committed → Fix Released |