Mysql Coalesce treats field differently than where

Question

I got a strange condition in Mysql coalesce function:

select start_date, end_date, COALESCE(end_date, start_date) 
from log where log_id = 8999134

returns: 2011-02-09 23:00:38, NULL, NULL

Anyone has any idea why coalsce is not working as it should ?

Edit:

After some research I decided to try the query in MYSQL workbench and saw that the tool I was using to access the database was incorrectly returning null when the field had end_date = 0000-00-00 00:00:00. Yet the problem got even more strange. The result above is already a debug. The first query was:

select id, start_date, COALESCE(end_date, start_date) 
from log where end_date is null

from where I debugged:

 select id, start_date, end_date, end_date is null 
 from log where end_date is null

and the result was: 2011-02-09 23:00:38, 0000-00-00 00:00:00, 0

This is result's first line, but got equal results to the other lines. Why the end_date is NULL for where and not to coalesce ?

the datatype for both fields is datetime

If your tool is mucking around with `NULL` values, then one might suspect that it also mucks around with `is null`. — Gordon Linoff, May 14 '14 at 20:54
no because I tested it in SQL Workbench and the result was the same — Nelson Teixeira, May 14 '14 at 21:25
That last query is a real problem. If you are selecting where `end_date is null` in the `where` clause, then that value has to be true in the `select`. If all you say is true exactly as you have written this, it could be a bug in MySQL. That said, this seems unlikely given the many applications and people who use the software. — Gordon Linoff, May 14 '14 at 23:36

Gordon Linoff · Answer 1 · 2014-05-14T11:42:40.900

3

This would occur if the value NULL were really a string 'NULL'. This would suggest that fields that should be dates are really being stored as strings.

Check the data types. If they are varchar() or char(), then this is probably the problem. Then, fix the problem by fixing the data structure. Date/times should be stored using native database formats.

EDIT:

A date value of 0 is not the same as NULL.

edited May 14 '14 at 11:42

answered May 14 '14 at 11:17

Gordon Linoff

1,242,037
58
646
786

The datatypes of both fields is datetime. See my edit. – Nelson Teixeira May 14 '14 at 11:38
about your edit: then why where clause treats 0000-00-00 00:00:00 as NULL ? – Nelson Teixeira May 14 '14 at 11:45
@NelsonTeixeira . . . I see no evidence that is the case. In fact, `end_date is null` is returning `0`, which is "false". – Gordon Linoff May 14 '14 at 12:07
as I told in the other answer to @echo_Me: chech my post. what started this problem was that I used the condition "where end_date is NULL" to get that results. And where end_date is '0000-00-00 00:00:00' mysql is returing the line as if end_date was null – Nelson Teixeira May 14 '14 at 12:11
Still as you said end_date is null is returning 0. But the line is still included if I use it in "where end_date is null" – Nelson Teixeira May 14 '14 at 12:13
1

@NelsonTeixeira . . . Can you replicate this on SQL Fiddle? – Gordon Linoff May 14 '14 at 12:52
Actually no. The results are diffent in it and my server. link to my test: http://sqlfiddle.com/#!2/e9b4a6/4/0 – Nelson Teixeira May 14 '14 at 13:30
In my server line 3 would also be returned – Nelson Teixeira May 14 '14 at 13:31
It's not a sql_mode related problem because I checked SQLFiddle sql_mode and set my server accordingly. – Nelson Teixeira May 14 '14 at 13:36
My server version: 5.5.37-0ubuntu0.12.04.1 – Nelson Teixeira May 14 '14 at 13:49
@NelsonTeixeira . . . can you check if `NO_ZERO_DATE` mode is set? I don't think it would have this effect, but it conceivably could. – Gordon Linoff May 14 '14 at 16:23
There were no modes set when I first posted. Then I tried STRICT_TRANS_TABLES in order to be consistent with SQL Fiddle. but removed it after the test. – Nelson Teixeira May 14 '14 at 18:30

echo_Me · Answer 2 · 2014-05-14T12:03:30.657

1

if you have your dates stored like that 0000-00-00 00:00:00 then Coalesce will not work because its not NULL . try this with CASE :

    select id, start_date, CASE WHEN end_date = '0000-00-00 00:00:00' 
                                THEN start_date
                                ELSE end_date END as coalesced_date 
    from log where log_id = 8999134

Or this:

SELECT id, start_date, 
CASE WHEN end_date ='0000-00-00 00:00:00' AND start_date != '0000-00-00 00:00:00'
         THEN start_date
     WHEN start_date ='0000-00-00 00:00:00' AND end_date != '0000-00-00 00:00:00'
          THEN end_date
     WHEN start_date ='0000-00-00 00:00:00' AND end_date = '0000-00-00 00:00:00'
          THEN 'They are nulled'
     ELSE end_date END as coalesced_date 
FROM log 
WHERE log_id = 8999134

EDIT2:

you dont need to use NULL in your where clause since you dont have null values , you have '0000-00-00 00:00:00' and this is not null.

so instead of

  where end_date is null

use

  where end_date != '0000-00-00 00:00:00'

edited May 14 '14 at 12:03

answered May 14 '14 at 11:46

echo_Me

37,078
5
58
78

OK.. actually I had alredy used same idea only using if instead: IF(end_date = '0000-00-00 00:00:00', start_date, end_date). But the question remains: why where clause treats 0000-00-00 00:00:00 as NULL ? – Nelson Teixeira May 14 '14 at 11:57
never 0000-00-00 00:00:00 are treated as null, what you mean ? – echo_Me May 14 '14 at 12:00
chech my post. what started this problem was thet I used the condition "where end_date is NULL" to get that results. And where end_date is '0000-00-00 00:00:00' mysql is returing the line as if end_date was null. – Nelson Teixeira May 14 '14 at 12:09
Null dates are usually more work. but they much MUCH MUCH faster than zero dates for queries – echo_Me May 14 '14 at 12:24
my seggest transform your zero dates to null and use coalesce and null condition and all will be faster and eaiser. – echo_Me May 14 '14 at 12:25

Mysql Coalesce treats field differently than where

2 Answers2