Wrong result with derived_with_keys=on and multiple WHERE conditions on the same field
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
MariaDB |
Fix Released
|
High
|
Igor Babaev |
Bug Description
The following query
SELECT * FROM t1 AS a1 JOIN v1 AS a2 WHERE a1.f2 = a2.f4 AND a1.f4 = a2.f4;
does not return all matching rows when executed with derived_
test case:
CREATE TABLE t1 (f2 int, f4 int);
INSERT INTO t1 VALUES (0,0),(
CREATE ALGORITHM=TEMPTABLE VIEW v1 AS SELECT f2, f4 FROM t1 ;
SET SESSION optimizer_
SELECT * FROM t1 AS a1 JOIN v1 AS a2 WHERE a1.f2 = a2.f4 AND a1.f4 = a2.f4;
SET SESSION optimizer_
SELECT * FROM t1 AS a1 JOIN v1 AS a2 WHERE a1.f2 = a2.f4 AND a1.f4 = a2.f4;
explain:
+----+-
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-
| 1 | PRIMARY | a1 | ALL | NULL | NULL | NULL | NULL | 4 | Using where |
| 1 | PRIMARY | <derived2> | ref | key0 | key0 | 5 | v_b.a1.f2 | 2 | |
| 2 | DERIVED | t1 | ALL | NULL | NULL | NULL | NULL | 4 | |
minimal optimizer switch: derived_
total optimizer_switch:
index_
bzr version-info
revision-id: <email address hidden>
date: 2011-07-06 10:30:51 +0400
build-date: 2011-07-06 14:24:26 +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 |