MySQL Bugs: #15610: GREATEST and LEAST(value, NULL) return NULL on MySQL5.0 but not MySQL4.x

Bug #15610	GREATEST and LEAST(value, NULL) return NULL on MySQL5.0 but not MySQL4.x
Submitted:	9 Dec 2005 0:40	Modified:	30 May 2007 21:45
Reporter:	Daniel Grace
Status:	Closed
Category:	Server: Docs	Severity:	S3 (Non-critical)
Version:	5.0.16-standard-log	OS:	Linux (Linux 2.4.21 (RHAS3))
Assigned to:	Paul DuBois	Target Version:
Triage:	D4 (Minor)

Description:
Under MySQL 4.1.17:

LEAST(nonnull, NULL) and GREATEST(nonnull, NULL) both return 'nonnull'

But in 5.0.16, they both return NULL.

I noticed the documentation changed to state:
<< Before MySQL 5.0.13, LEAST() returns NULL only if all arguments are NULL. As of 5.0.13,
it returns NULL if any argument is NULL. >>

However, this is not listed as an 'incompatible change' in the "Upgrading from Version 4.1
to 5.0" documentation at http://dev.mysql.com/doc/refman/5.0/en/upgrading-from-4-1.html

This massively breaks some applications that may depend on the older behavior and the
workaround is somewhat messy in the 2-argument version and much more messy if there are 3+
arguments... 

2 argument version: IF(a IS NULL OR B IS NULL, COALESCE(a, b), LEAST(a,b))

Ideally, this would be solved by either
a) Restoring the previous behavior, or
b) Adding an SQL Mode option that restores the previous behavior, or
c) Adding NLEAST() and NGREATEST() ("N" for "NULL") functions that implement either the
older or the newer behavior.

At the very least, it should probably be mentioned as an incompatible change in the docs.

How to repeat:
SELECT LEAST(42, NULL);

Thank you for a problem report. Sorry, but previous behaviour of LEAST and GREATEST was
simply incorrect. Current correct behaviour (compare to Oracle, where these fucntions came
from) is documented. So, I argee with the following you suggestion:

"At the very least, it should probably be mentioned as an incompatible change in the
docs."

This is surely should be mentioned in
http://dev.mysql.com/doc/refman/5.0/en/upgrading-from-4-1.html. So, I'll mark this report
as a verified documentation request.

You may add a separate feature request for adding functions like NLEAST, but I am not sure
that this feature will be added really.

Thank you for your bug report. This issue has been addressed in the
documentation. The updated documentation will appear on our website
shortly, and will be included in the next release of the relevant
product(s).

Additional info:

I'll add a note in the upgrade section to point out this problem.

Incompatible change: Before MySQL 5.0.13, GREATEST(x,NULL) and
LEAST(x,NULL) return x when x is a non-NULL value. As of 5.0.3,
both functions return NULL if any argument is NULL, the same as 
Oracle.  This change can cause problems for applications that rely 
on the old behavior.

OUCH!!

My 4.1 code is no longer working correctly due to this 'correction'. I understand the for
compatibility, but this change has created major problems for many Mysql users.

Rather than simply leave users who need the old behavior 'high and dry', may I suggest
that the 'powers that be' at Mysql create suitable replacement functions that have the
same behavior as the 'non-corrected' functions. 

For example GREATEST_OF_ALL(...) and LEAST_OF_ALL(...) where any NULLs would be ignored. 

That's my $0.02! Thanks for listening!

A (messy) workaround is to do:

(for 2 arguments to GREATEST()/LEAST())

LEAST(COALESCE(a,b), COALESCE(b,a))

(for 3 (or more) arguments to GREATEST()/LEAST())

LEAST(COALESCE(a,b,c), COALESCE(b,c,a), COALESCE(c,a,b))

I'm still a proponent of adding a null-ignoring LEAST with behavior like 4.1 however.

I contemplated adding it as a SQL-language UDF, but:
1) Function syntax doesn't allow for functions with a variable amount of arguments
2) Nor does it allow variable typing, or date arguments.
3) There's not a way to add UDFs globally that I saw in the documentation (only on a
per-database context)

We have noticed this quite frequently with programs that are producing web pages with
"Last updated ..." dates produced by SQL with a left outer join.  Since the update
TIMESTAMP column is NULL for rows from the LOJ'd columns (ex:
greatest(DrivingTable.UpdateTime, LOJTable.UpdateTime), the result of the function is now
NULL, which fouls up the output for the the "Last Updated ..." info on the web page.

So here's another vote for functions that work the old way, such as:

LEAST_NONNULL(...);
GREATEST_NONNULL(...);

Thanks.

To Robert: you can use IFNULL in this kind of query:
 
SELECT GREATEST(IFNULL(DrivingTable.UpdateTime,''), IFNULL(LOJTable.UpdateTime,''))...

Hope that helps.

I need an easy way to do:
LEAST(timestamp1,timestamp2,timestamp3,timestamp4)
returning the maximum timestamp even if some of them are NULL, just like MySQL Before
5.0.13 did.

I tried doing:
TIMESTAMP(LEAST(IFNULL(timestamp1,''),IFNULL(timestamp2,''),IFNULL(timestamp3,'')))
as Trung-Kien Dao suggests for GREATEST(), but it returns '' for LEAST()

Is there an easy way to get MySQL Before 5.0.13 LEAST() behavior again?

What about adding:
LEAST_NONNULL(...);
GREATEST_NONNULL(...);
as Robert Simpson suggests?