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: | |
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
[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.