Bug #16555 Comparison of constant which is out of range leads to key lookup
Submitted: 16 Jan 2006 23:48 Modified: 30 Aug 2012 11:47
Reporter: Peter Zaitsev (Basic Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S4 (Feature request)
Version:4.1 OS:Linux (linux)
Assigned to: Jørgen Løland CPU Architecture:Any
Tags: qc
Triage: D5 (Feature request)

[16 Jan 2006 23:48] Peter Zaitsev
Description:
If you perform  match of constant which is too large  to the column instead of simply responding with empty set MySQL   truncates the constant, performs the lookup  and only when  discards results:

CREATE TABLE `trunc` (
  `i` int(11) NOT NULL default '0',
  KEY `i` (`i`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

mysql> select i,count(*) cnt from trunc  group by i order by cnt desc limit 10;
+------------+-------+
| i          | cnt   |
+------------+-------+
| 2147483647 | 76047 |
| 1421638051 |     3 |
|  985505567 |     3 |
| 1046160975 |     2 |
|  141017389 |     2 |
|  848130626 |     2 |
|  888665819 |     2 |
| 1001437915 |     2 |
|  118824892 |     2 |
| 2104712727 |     2 |
+------------+-------+
10 rows in set (0.34 sec)

(Just some ranfom data. The only row we really need is with 2147483647)

mysql> explain select count(*) from trunc where i=4147483647;
+----+-------------+-------+------+---------------+------+---------+-------+-------+--------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref   | rows  | Extra                    |
+----+-------------+-------+------+---------------+------+---------+-------+-------+--------------------------+
|  1 | SIMPLE      | trunc | ref  | i             | i    |       4 | const | 81602 | Using where; Using index |
+----+-------------+-------+------+---------------+------+---------+-------+-------+--------------------------+
1 row in set, 1 warning (0.01 sec)

4bil is out of range for unsigned column and I would expect "Impossible Where clause" here 

Lets look at query execution:

| Handler_read_next     | 1305742982 |

mysql> select  count(*) from trunc where i=4147483647;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set, 1 warning (0.04 sec)

| Handler_read_next     | 1305819030 |

As you  can see  there were over 70000 row reads performed 

How to repeat:
See above
[17 Jan 2006 12:14] Aleksey Kishkin
Your MySQL connection id is 2 to server version: 4.1.16

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> explain select count(*) from trunc where i=4147483647;
+----+-------------+-------+------+---------------+------+---------+-------+-------+--------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref   | rows  | Extra                    |
+----+-------------+-------+------+---------------+------+---------+-------+-------+--------------------------+
|  1 | SIMPLE      | trunc | ref  | i             | i    |       4 | const | 76039 | Using where; Using index |
+----+-------------+-------+------+---------------+------+---------+-------+-------+--------------------------+
1 row in set, 1 warning (0.04 sec)

mysql> show warnings;
+---------+------+------------------------------------------------------+
| Level   | Code | Message                                              |
+---------+------+------------------------------------------------------+
| Warning | 1264 | Data truncated; out of range for column 'i' at row 1 |
+---------+------+------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show status like 'Hand%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
....
| Handler_read_next     | 0     |
....
+-----------------------+-------+
12 rows in set (0.00 sec)

mysql> select  count(*) from trunc where i=4147483647;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set, 1 warning (0.10 sec)

mysql> show status like 'Hand%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
...
| Handler_read_next     | 76047 |
....
+-----------------------+-------+
12 rows in set (0.00 sec)
[17 Jan 2006 12:15] Aleksey Kishkin
script for fill testcase tables

Attachment: bug16555.pl (application/x-perl, text), 533 bytes.

[16 Oct 2006 22:45] Igor Babaev
To evaluate statically predicates using implicit type constrains is definitely a feature request.
[30 Aug 2012 11:47] Jørgen Løland
Fixed in 5.6.6