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.
Attachments
Activity
| Field | Original Value | New Value |
|---|---|---|
| Status | Open [ 1 ] | Confirmed [ 10101 ] |
| Assignee | Igor Babaev [ igor ] |
| Status | Confirmed [ 10101 ] | In Progress [ 3 ] |
| Labels | regression |
| Fix Version/s | 5.5.61 [ 22914 ] | |
| Fix Version/s | 10.0.36 [ 22916 ] | |
| Fix Version/s | 10.1.35 [ 23116 ] | |
| Fix Version/s | 10.2.17 [ 23111 ] | |
| Resolution | Fixed [ 1 ] | |
| Status | In Progress [ 3 ] | Closed [ 6 ] |
| Workflow | MariaDB v3 [ 88571 ] | MariaDB v4 [ 154707 ] |
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.