mySQL "greatest" with two columns that can be null

Question

Why does GREATEST(createdTS, modifiedTS) with one of each column "null" return "null"?

If one of both is null, I simply need the value of the other. If both are null, then null.

How can I do that?

As I noticed I can use COALESCE like GREATEST(COALESCE(createdTS,0), COALESCE(modifiedTS,0)) - is that the best solution?

Answer 1

Yes, that's how null is, so you may do it using ifnull.
https://dev.mysql.com/doc/refman/5.0/en/working-with-null.html

You may do something like this:

mysql> select GREATEST(ifnull(1234,0),ifnull(null,0)) as g;
+------+
| g    |
+------+
| 1234 |
+------+
1 row in set (0.00 sec)

If you are doing the comparison for non fractional data, you can do it as above.

You can also use the COALESCE function for that.

Answer 2

Before MySQL 5.0.13, GREATEST() returns NULL only if all arguments are NULL. As of 5.0.13, it returns NULL if any argument is NULL.