Details
-
Bug
-
Status: Closed (View Workflow)
-
Minor
-
Resolution: Fixed
-
None
-
None
Description
If we have a query which has
- regular ("cheap") constant condition that evaluates to FALSE
- an expensive constant condition (doesn't matter what it evaluates to)
then "Impossible WHERE" will not be generated.
Example:
create table t1 (a int); |
insert into t1 values (1),(2),(3); |
|
create table t2 as select * from t1; |
|
create table t_pk1 (a int primary key); |
create table t_pk2 (a int primary key); |
|
insert into t_pk1 select a from t1; |
insert into t_pk2 select a from t1; |
|
alter table t_pk1 add b int; |
alter table t_pk2 add b int; |
## Here t_pk1 is a constant table, and "t_pk1.b> 3" is a cheap constant condition that evaluates to FALSE: |
|
MariaDB [j10]> explain select * from t_pk1, t2 where t_pk1.a=2 and t_pk1.b> 3 ; |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+ |
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+ |
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE noticed after reading const tables | |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+ |
1 row in set (0.00 sec) |
# Now, let's add an expensive constant condition, and observe that we won't be getting "Impossible WHERE" anymore: |
|
MariaDB [j10]> explain select * from t_pk1, t2 where t_pk1.a=2 and t_pk1.b> 3 and 2 > (select max(a) from t1); |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+ |
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+ |
| 1 | PRIMARY | t_pk1 | const | PRIMARY | PRIMARY | 4 | const | 1 | | |
| 1 | PRIMARY | t2 | ALL | NULL | NULL | NULL | NULL | 3 | | |
| 2 | SUBQUERY | t1 | ALL | NULL | NULL | NULL | NULL | 3 | | |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+ |
3 rows in set (0.00 sec) |