Bug #42677 MySQL Optimizer does not work right with truncation
Submitted: 8 Feb 2009 3:26 Modified: 1 Sep 2022 6:54
Reporter: Peter Zaitsev (Basic Quality Contributor) Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.0 and 5.1 OS:Any
Assigned to: Assigned Account CPU Architecture:Any
Tags: qc

[8 Feb 2009 3:26] Peter Zaitsev
Description:
MySQL Optimizer does not use right strategy for values which do not fit in the range of the column.  Instead of  evaluating such predicate to FALSE it does data truncation for the value, often does index lookup and only when evaluates data to false which can cause serve performance problems.

How to repeat:
mysql> CREATE TABLE article_comment(article_id int UNSIGNED NOT NULL, comment_id int UNSIGNED NOT NULL, KEY(article_id));
Query OK, 0 rows affected (0.08 sec)

mysql> INSERT INTO article_comment VALUES(4300000000,1);
Query OK, 1 row affected, 1 warning (0.02 sec)

mysql> INSERT INTO article_comment VALUES(4300000000,1);
Query OK, 1 row affected, 1 warning (0.04 sec)

mysql> INSERT INTO article_comment VALUES(4300000000,1);
Query OK, 1 row affected, 1 warning (0.03 sec)

mysql> INSERT INTO article_comment VALUES(4300000000,1);
Query OK, 1 row affected, 1 warning (0.03 sec)

mysql> EXPLAIN SELECT count(*) FROM article_comment WHERE article_id=4300000000;
+----+-------------+-----------------+------+---------------+------------+---------+-------+------+--------------------------+
| id | select_type | table           | type | possible_keys | key        | key_len | ref   | rows | Extra                    |
+----+-------------+-----------------+------+---------------+------------+---------+-------+------+--------------------------+
|  1 | SIMPLE      | article_comment | ref  | article_id    | article_id | 4       | const |    3 | Using where; Using index |
+----+-------------+-----------------+------+---------------+------------+---------+-------+------+--------------------------+
1 row in set, 1 warning (0.00 sec)
[8 Feb 2009 10:14] Sergey Petrunya
Same happens with [VAR]CHAR(N) columns and joins. If provided with an index lookup value with length > N (which can't have any matches), it will cut off the prefix of the value, do the lookup, and then filter the result out.
[8 Feb 2009 10:19] Sergey Petrunya
Peter, could you elaborate on how this causes severe performance problems?  My idea was that this is not a frequent case. What's the point of running queries like 
 
  SELECT count(*) FROM article_comment WHERE article_id=4 300 000 000;

which are guaranteed to produce no results? Where do the non-existant article ids (4.3G in this case) come from?
[8 Feb 2009 16:59] Peter Zaitsev
Sergey,

Indeed it comes from the bad design (people not checking warnings on inserts) but it can lead to very bad troubles.  I've posted more details on the blog

http://www.mysqlperformanceblog.com/2009/02/07/beware-of-mysql-data-truncation/#comment-46...
[9 Feb 2009 10:39] Sveta Smirnova
Thank you for the feedback.

Verified as described.
[1 Sep 2022 6:54] Roy Lyseng
Posted by developer:
 
Not reproducible in 8.0.
When strict mode was enabled as the default mode, these insert statements are no longer accepted, unless a more relaxed mode is enabled.
The SELECT statement's WHERE clause is transformed to an "impossible WHERE", due to const value elimination, which takes limits of column types into account.