MySQL Bugs: #12791: LEAST() Ignores NULLs (Undocumented Behaviour)

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:	None
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

Description:
LEAST() seems to ignore null value arguments. When NULL value arguments are passed to LEAST(), the function behaves as if the NULL arguments were not passed to the function at all.

It was expected that LEAST would use the equivalent of the less than operator (<) to decide what value to return. If that would be the case, NULL should be returned in case there's at least one NULL argument. However, LEAST performs the comparison on all arguments as if the NULL arguments were never passed.

I'm uncertain wheter this is the intended behaviour, as the documentation on LEAST() does not mention what LEAST() is supposed to do in case there are NULL arguments.

How to repeat:
mysql> select least(1,null), 1<null;
+---------------+--------+
| least(1,null) | 1<null |
+---------------+--------+
|             1 |   NULL |
+---------------+--------+
1 row in set (0.00 sec)

Suggested fix:
Document that the comparison performed by LEAST() is not the same as the comparision performed by <, at least not when it comes to NULL values.

Alternatively, if this is not the intended behaviour, make least compare NULL when there's at least one NULL argument. Of course, that should be documented too. 

(All this applies to GREATEST() too, of course)

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.

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

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.

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!

Noted in 5.0.13 changelog, and in descriptions
for LEAST()/GREATEST().