[MDEV-16820] Lost 'Impossible where' from query with inexpensive subquery - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: 5.5(EOL), 10.0(EOL), 10.1(EOL), 10.2(EOL), 10.3(EOL)
Fix Version/s: 5.5.61, 10.0.36, 10.1.35, 10.2.17
Component/s: Optimizer
Labels:
- regression

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

Igor Babaev (Inactive) added a comment - 2018-07-31 06:28

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.

Igor Babaev (Inactive) added a comment - 2018-07-31 06:28 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.

People

Assignee:: Igor Babaev (Inactive)

Reporter:: Igor Babaev (Inactive)

Votes:: 0 Vote for this issue

Watchers:: 1 Start watching this issue

Dates

Created:: 2018-07-25 00:01

Updated:: 2018-07-31 06:28

Resolved:: 2018-07-31 06:28

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server