|
create table ten (a int);
insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
create table t1 (a int, b int, c int);
insert into t1 select a,a,a from ten;
create table five (a int, b int, c int);
insert into five select a,a,a from ten limit 5;
In MariaDB 5.5 the query transformed by IN-EXISTS is shown as a correlated, despite the fact it is all constant:
MariaDB [test]> explain extended select * from t1 where 33 in (select b from five) or c > 11;
-----
| id |
select_type |
table |
type |
possible_keys |
key |
key_len |
ref |
rows |
filtered |
Extra |
-----
| 1 |
PRIMARY |
t1 |
ALL |
NULL |
NULL |
NULL |
NULL |
10 |
100.00 |
Using where |
| 2 |
DEPENDENT SUBQUERY |
five |
ALL |
NULL |
NULL |
NULL |
NULL |
5 |
100.00 |
Using where |
-----
MariaDB [test]> show warnings;
------
------
| Note |
1003 |
select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` where (`test`.`t1`.`c` > 11) |
------
In MariaDB 5.2 the query is correctly not shown as correlated:
MariaDB [test]> explain extended select * from t1 where 33 in (select b from five) or c > 11;
---
| id |
select_type |
table |
type |
possible_keys |
key |
key_len |
ref |
rows |
filtered |
Extra |
---
| 1 |
PRIMARY |
t1 |
ALL |
NULL |
NULL |
NULL |
NULL |
10 |
100.00 |
Using where |
| 2 |
SUBQUERY |
five |
ALL |
NULL |
NULL |
NULL |
NULL |
5 |
100.00 |
Using where |
---
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`.`c` AS `c` from `test`.`t1` where (`test`.`t1`.`c` > 11) |
------
|
LPexportBug1000649.xml