| 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: | |
| Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
| Version: | 4.1, 5.0, 5.1 | OS: | |
| Assigned to: | Alexander Barkov | CPU Architecture: | Any |
[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.
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.