Details
-
Bug
-
Status: Closed (View Workflow)
-
Minor
-
Resolution: Fixed
-
None
-
None
-
None
Description
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;
----------------------------------------------------------------------------------------------------------------------
| Level | Code | Message |
----------------------------------------------------------------------------------------------------------------------
| 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;
------------------------------------------------------------------------------------------------------------------------------------
| Level | Code | Message |
------------------------------------------------------------------------------------------------------------------------------------
| 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) |
------------------------------------------------------------------------------------------------------------------------------------