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:	8 Dec 2005 23:40	Modified:	30 May 2007 19:45
Reporter:	Daniel Grace	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.0.16-standard-log	OS:	Linux (Linux 2.4.21 (RHAS3))
Assigned to:	Paul DuBois	CPU Architecture:	Any

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?

Using MySQL 5.5, it seems in a query on multiple column comparison to find least value, NULL continues to show up as a Resultant instead of least value
SELECT IF(col1 IS NULL OR col2 IS NULL 
OR col3 IS NULL OR col4 IS NULL OR col5 IS NULL,
COALESCE(col1,col2,col3,col4,col5), 
LEAST(col1,col2,col3,col4,col5)) 
As Resultant 
from db.tablename
Group by Id;

It would have been the best if comparing columns GREATEST_NONNULL( in col1, col2, col3, col4...) OR Least_NONNULL(col1, col2, col3, col4.) would have produced the least and/or greatest value with no null value returned as a resultant of all compared column values.

Not sure what alternative to Least_NONNULL or Greatest_NONNULL can do the columns comparisons. Thanks

+1 Vote for NLEAST!
Current workflow are very messy, and I can not imagine a case where such a form can be useful