EXPLAIN shows incorrectly a non-correlated constant IN subquery is correlated.

[Timour Katchaounov]

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) |
+-------+------+---------------------------------------------------------------------------------------------------------------------------+