Bug #11728 string function LEFT, strange undocumented behaviour, strict mode
Submitted: 4 Jul 2005 16:59 Modified: 9 Aug 2006 19:45
Reporter: Matthias Leich Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:4.1, 5.0, 5.1 OS:
Assigned to: Alexander Barkov CPU Architecture:Any

[4 Jul 2005 16:59] Matthias Leich
Description:
MySQL gives a strange result, when working in SQL mode
'traditional'.
SELECT LEFT('a', NULL);
LEFT('a', NULL)
                     <- empty string as result
1. The manual is incomplete, because is does not tell what
    will happen if the length is NULL.
    (MySQL 4.1 and 5.0, any SQL mode)
2. I think the result of "Give me an unknown number of
    the leftmost characters of a known string" should be NULL,
    especially when working in SQL mode 'traditional'.
    

My environment:
   - Intel PC with Linux(SuSE 9.3)
   - MySQL compiled from source
        Version 5.0 ChangeSet@1.1867.1.1, 2005-07-01

How to repeat:
Please execute:
SET sql_mode = 'traditional';

SELECT LEFT('a', NULL);

Suggested fix:
The current "answer" on "Give me a fixed number of
the leftmost characters of an unknown string" in
non strict SQL mode is:
SET sql_mode= '';
SELECT LEFT(NULL, 1);
LEFT(NULL, 1)
NULL 

Therefore the result of SELECT LEFT('a', NULL); should
be NULL in all SQL modes.
[21 Feb 2006 14:58] Gunnar von Boehn
a) IMHO LEFT() and RIGHT() should behave like the corresponting version of SUBSTR()
LEFT('MYSQL',2) == SUBSTR('MYSQL',1,2) and
LEFT('MYSQL',NULL) should return NULL like SUBSTR(MYSQL',1,NULL) does

b) SUBSTR('MYSQL',-10,1) retuns '' empty string other DMBS (Oracle) return NULL in this case.
IMHO it would make sense to behave the same here.
[6 Jun 2006 17: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/7331
[20 Jun 2006 13:21] Sergei Glukhov
ok to push
[7 Jul 2006 12:08] 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/8900
[7 Jul 2006 12:13] Alexander Barkov
Pushed into 5.0-kt, marked as 5.0.24
TODO: merge to 5.0 common and 5.1.
[7 Aug 2006 6:33] Alexander Barkov
Merged into 5.0.25 and 5.1.12
[9 Aug 2006 19:45] Paul DuBois
Noted in 5.0.24, 5.1.12 changelogs.

The LEFT() and RIGHT() functions return NULL if any argument is NULL.