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).
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
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;