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

Bug #1000649 reported by Timour Katchaounov on 2012-05-17
6
This bug affects 1 person
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),(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) |
+-------+------+---------------------------------------------------------------------------------------------------------------------------+

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
To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers