EXPLAIN shows incorrectly a non-correlated constant IN subquery is correlated.
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
MariaDB |
Fix Released
|
Medium
|
Timour Katchaounov |
Bug Description
create table ten (a int);
insert into ten values (0),(1)
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) |
+------
Related branches
Changed in maria: | |
status: | New → Confirmed |
assignee: | nobody → Timour Katchaounov (timour) |
importance: | Undecided → Medium |
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 |