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:
None 
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:23] Peter Gulutzan
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)
[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.