Bug #12791 | LEAST() Ignores NULLs (Undocumented Behaviour) | ||
---|---|---|---|
Submitted: | 24 Aug 2005 20:30 | Modified: | 30 Aug 2005 20:35 |
Reporter: | Roland Bouman | Email Updates: | |
Status: | Closed | Impact on me: | |
Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
Version: | 5.0.10, 5.0.11, 4.x.x | OS: | Any (any) |
Assigned to: | Igor Babaev | CPU Architecture: | Any |
[24 Aug 2005 20:30]
Roland Bouman
[25 Aug 2005 15:13]
Valeriy Kravchuk
Thank you for your bug report. LEAST and GREATEST are not standard functions - they are SQL extensions introduced by Oracle. So, it's hard to say how to implement them correctly... NULL's are not completely ignored in MySQL: mysql> select least(1,NULL); +---------------+ | least(1,NULL) | +---------------+ | 1 | +---------------+ 1 row in set (0.08 sec) mysql> select least(NULL,NULL); +------------------+ | least(NULL,NULL) | +------------------+ | NULL | +------------------+ 1 row in set (0.00 sec) mysql> select greatest(NULL,NULL); +---------------------+ | greatest(NULL,NULL) | +---------------------+ | NULL | +---------------------+ 1 row in set (0.00 sec) mysql> select greatest(1,NULL); +------------------+ | greatest(1,NULL) | +------------------+ | 1 | +------------------+ 1 row in set (0.00 sec) mysql> select version(); +----------------+ | version() | +----------------+ | 5.0.11-beta-nt | +----------------+ 1 row in set (0.00 sec) As you can see, LEAST and GREATEST of NULLs is NULL (and it should be NULL in any case when NULL is used in the list, because any comparison with NULL should have NULL result, logically). Moreover, that is the case in original Oracle's implementation: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.1.0 - Production SQL> select least(null, null) from dual; L - SQL> select least (1, null) from dual; LEAST(1,NULL) ------------- (empty result means NULL in Oracle in this case...) So, I think, it's not a documentation request - it's a bug in our implementation of LEAST and GREATEST functions.
[27 Aug 2005 5: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/internals/28922
[28 Aug 2005 16:12]
Igor Babaev
ChangeSet 1.1903 05/08/26 22:25:45 igor@rurik.mysql.com +6 -0 func_str.result, null.result: Corrected results after the fix for bug #12791. func_test.result, func_test.test: Added test cases for bug #12791. item_func.h, item_func.cc: Fixed bug #12791. Made LEAST/GREATES fully Oracle compliant. LEAST/GREATEST did not return NULL if only some arguments were NULLs. This did not comply with Oracle. The fix will appear in 5.0.13. For 4.1 LEAST/GREATEST still returns a non-null value if not all arguments are nulls. It was decided to leave it as it is there.
[28 Aug 2005 17:02]
Roland Bouman
Thanks for your quick response Igor. I was not a big thing for me, I would have been happy just to have the behaviour documented. Good Luck!
[30 Aug 2005 20:35]
Paul DuBois
Noted in 5.0.13 changelog, and in descriptions for LEAST()/GREATEST().