Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Critical
-
Resolution: Fixed
-
Affects Version/s: None
-
Fix Version/s: None
-
Component/s: None
-
Labels:
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
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Launchpad bug id: 717577