|
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;
-----
-----
-----
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;
-----
-----
-----
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;
------
------
| 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;
------
------
| 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;
------
------
| 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
|
LPexportBug717577.xml