Wrong result / WHERE not observed with derived_merge and inner join in maria-5.3
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
MariaDB |
Fix Released
|
Critical
|
Igor Babaev |
Bug Description
The following query:
SELECT * FROM ( SELECT t2.* FROM t1 , t2 ) AS alias1 WHERE b IS NULL;
returns rows for which b IS NOT NULL
MariaDB [test]> explain SELECT * FROM ( SELECT t2.* FROM t1 , t2 ) AS alias1 WHERE b IS NULL ;
+----+-
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-
| 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 2 | Using where |
| 1 | SIMPLE | t2 | ALL | NULL | NULL | NULL | NULL | 2 | Using join buffer (flat, BNL join) |
+----+-
minimal optimizer switch:
full optimizer_switch:
index_merge=
bzr version-info
revision-id: <email address hidden>
date: 2011-08-09 18:34:26 +0300
build-date: 2011-08-10 13:04:33 +0300
revno: 3148
branch-nick: maria-5.3
test case:
CREATE TABLE t1 (a int) ;
INSERT INTO t1 VALUES (1),(1);
CREATE TABLE t2 (b int) ;
INSERT INTO t2 VALUES (NULL),(6);
SET SESSION optimizer_
SELECT * FROM ( SELECT t2.* FROM t1 , t2 ) AS alias1 WHERE b IS NULL;
Changed in maria: | |
milestone: | none → 5.3 |
assignee: | nobody → Igor Babaev (igorb-seattle) |
summary: |
- WHERE not observed with derived_merge and inner join in maria-5.3 + Wrong result / WHERE not observed with derived_merge and inner join in + maria-5.3 |
Changed in maria: | |
importance: | Undecided → Critical |
status: | Confirmed → In Progress |
Changed in maria: | |
status: | In Progress → Fix Committed |
Changed in maria: | |
status: | Fix Committed → Fix Released |
The same problem can be reproduced with a view:
MariaDB [test]> create view v2 as select * from t1,t2;
Query OK, 0 rows affected (0.01 sec)
MariaDB [test]> select * from v2;
+------+------+
| a | b |
+------+------+
| 1 | NULL |
| 1 | NULL |
| 1 | 6 |
| 1 | 6 |
+------+------+
4 rows in set (0.00 sec)
MariaDB [test]> select * from v2 where b is null;
+------+------+
| a | b |
+------+------+
| 1 | NULL |
| 1 | NULL |
| 1 | 6 |
| 1 | 6 |
+------+------+
4 rows in set (0.00 sec)