Wrong result for a join with OR in the WHERE condition when using a view
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
MariaDB |
Fix Released
|
Critical
|
Igor Babaev |
Bug Description
If in maria-db 5.3 one creates and populates tables t1 and t2 with the following commands:
create table t1 (a int, b int);
insert into t1 values (2,4), (1,3);
create table t2 (c int);
insert into t2 values (6), (4), (1), (3), (8), (3), (4), (2);
and then runs the query:
select * from t1,t2 where t2.c=t1.a and t2.c < 3 or t2.c=t1.b and t2.c >=4;
one gets the result:
MariaDB [test]> select * from t1,t2 where t2.c=t1.a and t2.c < 3 or t2.c=t1.b and t2.c >=4;
+------
| a | b | c |
+------
| 2 | 4 | 4 |
| 1 | 3 | 1 |
| 2 | 4 | 4 |
| 2 | 4 | 2 |
+------
This result is correct.
If now one create the view
create view v as select * from t2;
and executes an equivalent query
select * from t1,v where v.c=t1.a and v.c < 3 or v.c=t1.b and v.c >=4;
one gets:
MariaDB [test]> select * from t1,v where v.c=t1.a and v.c < 3 or v.c=t1.b and v.c >=4;
+------
| a | b | c |
+------
| 2 | 4 | 4 |
| 2 | 4 | 4 |
+------
This result is incorrect.
Why does it happen? EXPLAIN EXTENDED for these queries give us an answer:
MariaDB [test]> explain extended select * from t1,t2 where t2.c=t1.a and t2.c < 3 or t2.c=t1.b and t2.c >=4;
+----+-
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-
| 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | Using where |
| 1 | SIMPLE | t2 | ALL | NULL | NULL | NULL | NULL | 8 | 100.00 | Using where; Using join buffer (flat, BNL join) |
+----+-
2 rows in set, 1 warning (0.00 sec)
MariaDB [test]> show warnings;
+------
| Level | Code | Message |
+------
| Note | 1003 | select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t1` join `test`.`t2` where (((`test`.`t2`.`c` = `test`.`t1`.`a`) and (`test`.`t1`.`a` < 3)) or ((`test`.`t2`.`c` = `test`.`t1`.`b`) and (`test`.`t1`.`b` >= 4))) |
+------
1 row in set (0.00 sec)
MariaDB [test]> explain extended select * from t1,v where v.c=t1.a and v.c < 3 or v.c=t1.b and v.c >=4;
+----+-
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-
| 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | Using where |
| 1 | SIMPLE | t2 | ALL | NULL | NULL | NULL | NULL | 8 | 100.00 | Using where; Using join buffer (flat, BNL join) |
+----+-
2 rows in set, 1 warning (0.00 sec)
MariaDB [test]> show warnings;
+------
| Level | Code | Message |
+------
| Note | 1003 | select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`b` AS `c` from `test`.`t1` join `test`.`t2` where (((`test`.`t2`.`c` = `test`.`t1`.`a`) and (`test`.`t1`.`b` < 3)) or ((`test`.`t2`.`c` = `test`.`t1`.`b`) and (`test`.`t1`.`b` >= 4))) |
+------
1 row in set (0.00 sec)
As we can see the optimizer performs an invalid transformation of the second query.
mariadb-5.1/5.2 are not affected because there the optimizer does not perform any optimizations for the second query at all:
MariaDB [test]> explain extended select * from t1,v where v.c=t1.a and v.c < 3 or v.c=t1.b and v.c >=4;
+----+-
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-
| 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | |
| 1 | SIMPLE | t2 | ALL | NULL | NULL | NULL | NULL | 8 | 100.00 | Using where; Using join buffer |
+----+-
2 rows in set, 1 warning (0.00 sec)
MariaDB [test]> show warnings;
+------
| Level | Code | Message |
+------
| Note | 1003 | select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t1` join `test`.`t2` where (((`test`.`t2`.`c` = `test`.`t1`.`a`) and (`test`.`t2`.`c` < 3)) or ((`test`.`t2`.`c` = `test`.`t1`.`b`) and (`test`.`t2`.`c` >= 4))) |
+------
1 row in set (0.00 sec)
4
Changed in maria: | |
status: | New → Confirmed |
importance: | Undecided → Critical |
assignee: | nobody → Igor Babaev (igorb-seattle) |
milestone: | none → 5.3 |
Changed in maria: | |
status: | Confirmed → In Progress |
Changed in maria: | |
status: | In Progress → Fix Committed |
Changed in maria: | |
status: | Fix Committed → Fix Released |