Bug #8571 impossible where not regocnized
Submitted: 17 Feb 2005 15:18 Modified: 10 Oct 2005 0:02
Reporter: Martin Friebe (Gold Quality Contributor) (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S4 (Feature request)
Version:4.1.10 OS:Any (*)
Assigned to: CPU Architecture:Any
Triage: Triaged: D5 (Feature request)

[17 Feb 2005 15:18] Martin Friebe
Description:
see below. the where condition is impossible. but it will only be recognized so, if an index exists.

It could be recognized before even reding the table, because an integer can never fullfill those conditions.

How to repeat:
create table tb1 (a int, b int, c varchar(5));
insert into tb1 values (1,2,'foo'),  (2,3,'foo'), (3,2,'foo'), (4,4,'foo'), (5,2,'foo'), (6,5,'foo'), (10,2,'foo'),  (12,3,'foo'), (13,2,'foo'), (14,4,'foo'), (15,2,'foo'), (16,5,'foo');
analyze table tb1;

mysql> explain select * from tb1 where a > 2 and a < 1;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | tb1   | ALL  | NULL          | NULL |    NULL | NULL |   12 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

alter table tb1 add unique (a);

explain select * from tb1 where a > 2 and a < 1;
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
| 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)
[17 Feb 2005 18:44] Martin Friebe
# the following goes WITHOUT index

explain select * from tb1 where a > 2 and a = 1;
+----+-------------+-------+------+---------------+------+---------+------+------+------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra            |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL |    NULL | NULL | NULL | Impossible WHERE |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------+
[10 Oct 2005 0:02] Hartmut Holzgraefe
Verified with 4.1.14 and 5.0bk