| 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: | |
| 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 | ||
[5 Oct 2012 13:05]
MySQL Verification Team
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.
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.