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)
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S4 (Feature request)
• Version: 4.1.10
• OS: Any (*)
• CPU Architecture: Any

[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