COALESCE two dates returns binary?

Question

Why does COALESCE(date1,date2) return Blob(binary)? Both columns are of type DATETIME.

Here's the complete sql query:

SELECT COALESCE( last_modified, date_purchased )As LastModifiedOrPurchased
FROM Orders
ORDER BY COALESCE( last_modified, date_purchased )DESC
LIMIT 1;

I'm normally using SQL-Server as rdbms but need to write a synchronization tool. Now i'm trying to select one of two dates. If last_modified is null date_purchased should be returned.

But even if there are multiple records where both dates are not null, the query returns just Blob. How to get the correct last date?

Edit: on SQL-Fiddle it is not reproducable with MySql 5.1.61 (i'm on 5.0.51).

Have you tried it on command-line? Or what do you use to fire the query? And what's the datatype of those columns? — fancyPants, Dec 07 '12 at 11:20
@tombom: I have tried it from MySql workbench 5.2.34 and from Visual Studio. Btw, i'm on windows 2008. — Tim Schmelter, Dec 07 '12 at 11:28
Yes, I would have wondered, if it were reproducable. A lot of clients out there have problems displaying data correctly or think they do the user a favour, i.e. I'm using Toad, which will not display `tinyint` correctly (it uses just checkboxes then) and `NULL` values won't be displayed at all, so you can't tell if it's NULL or empty string. Nonetheless I can still recommend Toad, though. The problem is your client! — fancyPants, Dec 07 '12 at 11:32

score 2 · Answer 1 · answered Dec 07 '12 at 12:48

This is probably a bug that has been fixed: bug-35662

[28 Mar 2008 22:44] Patrick Crews

Description:
COALESCE(date, date) returns varbinary on 5.0.51a for Windows XP 32bit (only tested this flavor)

COALESCE(date,date) returns date on Mac OS

...
...
...

[31 Mar 2008 17:41] Omer BarNir
The bug was fixed in 5.0.52 and was not observed when testing 5.0.56 - closing

Tim Schmelter · Answer 2 · 2012-12-07T12:06:45.330

I still don't know why this happens in MySql Workbench (and also MS Visual Studio). But i have found a workaround. I just cast the result to DATETIME and it works as expected:

SELECT CAST(COALESCE( last_modified, date_purchased )AS DATETIME)As LastModifiedOrPurchased
FROM Orders
ORDER BY COALESCE( last_modified, date_purchased )DESC
LIMIT 1;

Edit: as Jack has commented, IFNULL works also:

SELECT IFNULL( last_modified, date_purchased )As LastModifiedOrPurchased
FROM Orders
ORDER BY IFNULL( last_modified, date_purchased )DESC
LIMIT 1;

COALESCE two dates returns binary?

2 Answers2

Linked