Bug #67097 InnoDB optimizer fails to detect impossible WHEREs on unsigned columns
Submitted: 5 Oct 2012 7:34 Modified: 19 Oct 2012 13:05
Reporter: Suren Karapetyan Email Updates:
Status: Won't fix Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical)
Version:5.5.28 OS:Linux
Assigned to: CPU Architecture:Any
Tags: impossible where, innodb unsigned columns
Triage: Needs Triage: D4 (Minor)

[5 Oct 2012 7:34] Suren Karapetyan
Description:
Mysql optimizer usually detects impossible WHERE clauses like "`a` < 5 AND `a` > 10" or when `a` is unsigned int and "`a` < -4". These type of queries are not very common but happen from time to time.
The problem is the optimizer fails to detect impossible WHEREs in some cases when quoted negative values are compared against unsigned int columns.

How to repeat:
mysql> CREATE TABLE `test` (
    ->   `id` int(10) unsigned NOT NULL,
    ->   PRIMARY KEY (`ID`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8
    -> ;
mysql> EXPLAIN SELECT COUNT(*) FROM `test` WHERE `id` < '-10' ;
+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
|  1 | SIMPLE      | test  | index | PRIMARY       | PRIMARY | 4       | NULL |    1 | Using where; Using index |
+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+

It's doing index scan.

If -10 is not quoted, it works as expected:
mysql> EXPLAIN SELECT COUNT(*) FROM `test` WHERE `id` < -10 ;
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
| 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 |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+

The problem is *not* present with MyISAM storage engine.
[5 Oct 2012 13:05] Miguel Solorzano
Thank you for the bug report.

d:\dbs>d:\dbs\5.5\bin\mysql -uroot --port=3541 --prompt="mysql 5.5 >"
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.29 Source distribution

Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.5 >create database d1;
Query OK, 1 row affected (0.00 sec)

mysql 5.5 >use d1
Database changed
mysql 5.5 >CREATE TABLE `test` (`id` int(10) unsigned NOT NULL,PRIMARY KEY (`ID`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.05 sec)

mysql 5.5 >EXPLAIN SELECT COUNT(*) FROM `test` WHERE `id` < '-10' ;
+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
|  1 | SIMPLE      | test  | index | PRIMARY       | PRIMARY | 4       | NULL |    1 | Using where; Using index |
+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
1 row in set (0.00 sec)

mysql 5.5 >EXPLAIN SELECT COUNT(*) FROM `test` WHERE `id` < -10 ;
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
| 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)

mysql 5.5 >ALTER TABLE test ENGINE=MyISAM;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql 5.5 >EXPLAIN SELECT COUNT(*) FROM `test` WHERE `id` < '-10' ;
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
| 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.01 sec)

mysql 5.5 >
[19 Oct 2012 13:05] Jørgen Løland
Already fixed in 5.6.