Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
5.5(EOL), 10.0(EOL), 10.1(EOL), 10.2(EOL), 10.3(EOL)
Description
In 5.5.32 for the test case
create table t1 (a int) engine=myisam; |
insert into t1 values (3), (1), (7); |
|
|
create table t2 (b int, index idx(b)); |
insert into t2 values (2), (5), (3), (2); |
|
|
explain select * from t1 where (select max(b) from t2) = 10; |
explain select * from t1 where (select max(b) from t2) = 10 and t1.a > 3; |
we had
MariaDB [test]> explain select * from t1 where (select max(b) from t2) = 10;
|
+------+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
|
| 1 | PRIMARY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE |
|
| 2 | SUBQUERY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away |
|
+------+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
|
|
|
MariaDB [test]> explain select * from t1 where (select max(b) from t2) = 10 and t1.a > 3;
|
+------+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
|
| 1 | PRIMARY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE |
|
| 2 | SUBQUERY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away |
|
+------+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
|
Yet after mdev-4817 (commit 04684b7709f55a5a9de9226e834bcfbed05ee5c0) we have
MariaDB [test]> explain select * from t1 where (select max(b) from t2) = 10;
|
+------+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
|
| 1 | PRIMARY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE |
|
| 2 | SUBQUERY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away |
|
+------+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
|
2 rows in set (0.00 sec)
|
|
|
MariaDB [test]> explain select * from t1 where (select max(b) from t2) = 10 and t1.a > 3;
|
+------+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
|
| 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 3 | Using where |
|
| 2 | SUBQUERY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away |
|
+------+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
|
So we observe a performance regression.
A fix for this bug was pushed to 5.5 and merged up to 10.2.
It should merged upstream as it is in 10.2.