| Bug #10963 | LEFT/RIGHT/SUBSTR/.. string functions returns wrong result on large length | ||
|---|---|---|---|
| Submitted: | 30 May 2005 14:23 | Modified: | 10 Apr 2007 16:16 |
| Reporter: | Peter Gulutzan | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | 5.0.7-beta-debug | OS: | Linux (SUSE 9.2) |
| Assigned to: | CPU Architecture: | Any | |
[30 May 2005 14:29]
MySQL Verification Team
Thank you for the bug report.
[4 Jul 2005 11:39]
Matthias Leich
I detected the same problem today and made the following
observations:
1. There seems to be a "magic" border around +1e+18,
where the problems could begin to start.
2. There are also sometimes surprising correct results
when len is greater than +1e18
SELECT LEFT('a', 9999999999999999999999.0);
LEFT('a', 9999999999999999999999.0)
a
Warnings:
Error 1292 Truncated incorrect DECIMAL value: ''
Error 1292 Truncated incorrect DECIMAL value: ''
3. The manual is incomplete, because it does not tell, that
non integer values of len are silently rounded to the
nearest integer value. This server behaviour is reasonable,
but somebody might expect, that the server sends an
error or a warning instead. That's why it should be
documented.
SELECT LEFT('a', 0.6);
LEFT('a', 0.6)
'a'
4. Within server mode 'traditional', we get the same results,
even if such very suspicious warnings like in 2. occur.
[5 Jul 2005 14:04]
Magnus Blåudd
I think I have isolated the first part of the problem to the following SQL statement: mysql> select CAST(11111111111111111111111 as unsigned) 11111111111111 Warnings: Error 1292 Truncated incorrect DECIMAL value: '' I.e the value is saved as a Item_decimal and when we read it as an int to get the length to output, the warning above is output.
[19 Sep 2005 15:06]
Matthias Leich
There is also an interesting difference between 32 and 64 Bit releases.
Example:
t1.f1 is of data type BIGINT
32 bit (my box, Pentium M SuSE 9.3)
SELECT f1, LEFT('AaBbCcDdEeFfGgHhIiJjÄäÖöÜüß', f1) FROM t1;
f1 LEFT('AaBbCcDdEeFfGgHhIiJjÄäÖöÜüß', f1)
-100
100 AaBbCcDdEeFfGgHhIiJjÄäÖöÜüß
-9223372036854775808
9223372036854775807
9000000000000000000
1000000000000000000
100000000000000000 AaBbCcDdEeFfGgHhIiJjÄäÖöÜüß
10000000000000000 AaBbCcDdEeFfGgHhIiJjÄäÖöÜüß
1000000000000000
100000000000000 AaBbCcDdEeFfGgHhIiJjÄäÖöÜüß
10000000000000 AaBbCcDdEeFfGgHhIiJjÄäÖöÜüß
1000000000000
100000000000 AaBbCcDdEeFfGgHhIiJjÄäÖöÜüß
10000000000 AaBbCcDdEeFfGgHhIiJjÄäÖöÜüß
1000000000 AaBbCcDdEeFfGgHhIiJjÄäÖöÜüß
100000000 AaBbCcDdEeFfGgHhIiJjÄäÖöÜüß
64 Bit (hammer, AMD Athlon64,
Red Hat Linux Advanced Server 3 (linux-gnu-x86_64))
SELECT f1, LEFT('AaBbCcDdEeFfGgHhIiJjÄäÖöÜüß', f1) FROM t1;
f1 LEFT('AaBbCcDdEeFfGgHhIiJjÄäÖöÜüß', f1)
-100
100 AaBbCcDdEeFfGgHhIiJjÄäÖöÜüß
-9223372036854775808
9223372036854775807 AaBbCcDdEeFfGgHhIiJjÄäÖöÜüß
9000000000000000000 AaBbCcDdEeFfGgHhIiJjÄäÖöÜüß
1000000000000000000 AaBbCcDdEeFfGgHhIiJjÄäÖöÜüß
100000000000000000 AaBbCcDdEeFfGgHhIiJjÄäÖöÜüß
10000000000000000 AaBbCcDdEeFfGgHhIiJjÄäÖöÜüß
1000000000000000 AaBbCcDdEeFfGgHhIiJjÄäÖöÜüß
100000000000000 AaBbCcDdEeFfGgHhIiJjÄäÖöÜüß
10000000000000 AaBbCcDdEeFfGgHhIiJjÄäÖöÜüß
1000000000000 AaBbCcDdEeFfGgHhIiJjÄäÖöÜüß
100000000000 AaBbCcDdEeFfGgHhIiJjÄäÖöÜüß
10000000000 AaBbCcDdEeFfGgHhIiJjÄäÖöÜüß
1000000000 AaBbCcDdEeFfGgHhIiJjÄäÖöÜüß
100000000 AaBbCcDdEeFfGgHhIiJjÄäÖöÜüß
Why does the 64 Bit release not show this bug ?
[17 Feb 2006 16:03]
Gunnar von Boehn
I have verified that the following string functions were affected by this problem LEFT RIGHT SUBSTR INSERT RPAD LPAD LOCATE REPEAT SPACE
[17 Feb 2006 18:40]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/2815
[13 Apr 2006 2:30]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/4894
[18 Apr 2006 2:23]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/5039
[20 Jun 2006 3:19]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/7888
[23 Jun 2006 1:45]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/8116
[15 Aug 2006 0:26]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/10393 ChangeSet@1.2250, 2006-08-14 17:25:53-07:00, igreenhoe@anubis.greendragongames.com +4 -0 Patch for bug #10963, (LEFT/RIGHT/SUBSTR/.. string functions returns wrong result on large length) Problem: Most (all) of the numeric inputs were being coerced into int (32 bit) sized variables. Works OK for sane inputs; any input larger than 2^32 (or 2^31 for signed vars) exihibited predictable wrapping behavior (up to about 10^18) and then started having really strange behaviour past that point (since the conversion to 64 bit int from the DECIMAL type can do weird things on out of range numbers). Solution: 1) Add many tests. 2) Convert input from (u)long type to (u)longlong. 3) Do (sometimes multiple) sanity checks on input, keeping in mind that sometimes a negative longlong is not a negative longlong (if the unsigned_flag is set). 4) Emulate existing behavior w/rt negative and "small" out-of-bounds values.
[8 Nov 2006 15:20]
Chad MILLER
Pushed patch for Ian.
[10 Apr 2007 16:16]
Paul DuBois
Noted in 5.0.30, 5.1.13 changelogs. Several string functions could return incorrect results when given very large length arguments. Pointed out in string function section intro that length arguments are rounded to nearest integer.
Description: FOR "LEFT(str,len)", if len > LENGTH(str), MySQL returns as much as it can, i.e. str. But if len is large, LEFT(str,extremely-large-len) returns errors or returns nothing. In my examples, I am using a 24-digit number and an 18-digit number. How to repeat: mysql> create table t9 (s1 int); Query OK, 0 rows affected (0.00 sec) mysql> insert into t9 values (1); Query OK, 1 row affected (0.01 sec) mysql> select left('a',111111111111111111111111) from t9; +------------------------------------+ | left('a',111111111111111111111111) | +------------------------------------+ | a | +------------------------------------+ 1 row in set, 2 warnings (0.00 sec) mysql> select left('a',111111111111111111) from t9; +------------------------------+ | left('a',111111111111111111) | +------------------------------+ | | +------------------------------+ 1 row in set (0.00 sec)