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

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.

[MDEV-16820] Lost 'Impossible where' from query with inexpensive subquery Created: 2018-07-25 Updated: 2018-07-31 Resolved: 2018-07-31
Status:	Closed
Project:	MariaDB Server
Component/s:	Optimizer
Affects Version/s:	5.5, 10.0, 10.1, 10.2, 10.3
Fix Version/s:	5.5.61, 10.0.36, 10.1.35, 10.2.17