Not so obvious COALESCE behaviour

  • Comments posted to this topic are about the item Not so obvious COALESCE behaviour

  • Now the answer is NEVER.

    Without the RTRIM then the ansewer : IF NULL is right.

    It surprished me a bit when I use this code :

    DECLARE @anyDateTime date = null

    IF (COALESCE(@anyDateTime,'')) = ''

    print 'true'

    else

    print 'false'

    print '*' + cast(@anydatetime as varchar) + '*'

    print COALESCE(@anyDateTime,'')

    print rtrim(COALESCE(@anyDateTime,''))

    MessagesPane:

    true

    1900-01-01

    1900-01-01

    Why true ? The COALESCE(@anyDateTime,'') returns 1900-01-01 which is not ''

  • ArnoKwetters (6/17/2015)


    Now the answer is NEVER.

    Without the RTRIM then the ansewer : IF NULL is right.

    It surprished me a bit when I use this code :

    DECLARE @anyDateTime date = null

    IF (COALESCE(@anyDateTime,'')) = ''

    print 'true'

    else

    print 'false'

    print '*' + cast(@anydatetime as varchar) + '*'

    print COALESCE(@anyDateTime,'')

    print rtrim(COALESCE(@anyDateTime,''))

    MessagesPane:

    true

    1900-01-01

    1900-01-01

    Why true ? The COALESCE(@anyDateTime,'') returns 1900-01-01 which is not ''

    There is already a fairly good explanation in the answer of the QotD. COALESCE (like many functions and operators in SQL Server), when confronted with mixed data types in the input, will implicitly convert values to be of the same data type and then return that data type. To determine which data type is used, the rules of data type precedence (https://msdn.microsoft.com/en-us/library/ms190309.aspx) are used. In the case of this example, the input data types are date and varchar(1). Date has a higher precedence, so the empty string is implicitly converted to date. This conversion results in Jan 1st, 1900, so the COALESCE returns a date value that is either @anyDateTime or, if that is NULL, Jan 1st, 1900.

    In the QotD, the next step is RTRIM. This is a specific string function, so it will always return a string and convert its input to string. Jan 1st 1900 gets casted as string, resulting in '1900-01-01' or (depending on whether the data type used is date or datetime, and on the language settings) some other representation of that date. If @anyDateTime is not null, it will return a similar representation of whatever date is in that variable. Bottom line is that this is never equal to the empty string.

    In your example, you omit the RTRIM. So the result of COALESCE (which was a date value) is compared to '' (a varchar(1) value). SQL Server again consults the rules of data type precedence and then implicitly converts the empty string to a date - again resulting in Jan 1st, 1900. Which makes the comparison true.

    Interesting other queries to run:

    SET LANGUAGE English;

    DECLARE @anyDateTime date = NULL;

    IF COALESCE(@anyDateTime, '1/1/1900') = '' PRINT 'Equal'; ELSE PRINT 'Different';

    IF COALESCE(@anyDateTime, '') = '19000101' PRINT 'Equal'; ELSE PRINT 'Different';

    IF COALESCE(@anyDateTime, 'Jan 1, 1900') = '1-1-1900' PRINT 'Equal'; ELSE PRINT 'Different';

    (If you run with a different language setting, some of the implicit string-to-date conversioins might fail)


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • Thanks Hugo,

    Now it's clear to me.

  • Excellent question. It was unambiguous and the explanation was good. Thanks.

  • This was removed by the editor as SPAM

  • Nice question.

    Disturbing to see that 49.7% still either don't know that coalesce picks the type of its highest type precedence argument or don't know that RTRIM requires conversion of any non-string first argument to a string type.

    Tom

  • now i'm just wondering how a column could be both Date and DateTime 🙂

    "...a column of Date and/or DateTime..."

  • Got it right by accident. Long day and logic was off for result from RTRIM - for some reason I was thinking it would return nothing not '' (empty string), which was wrong for two reasons. 🙂

  • Interesting question. The question is not about COALESCE, rather it is about RTRIM. COALESCE would have returned a DateTime that WOULD have been equal to the blank string (hence my answer). I made a slight assumption that the question (based on the title) was about the COALESCE not the RTRIM and so mentally omitted the RTRIM, which would yield the first answer. This question is about RTRIM behavior with a non-string data type. Don't really care about points, but the title is very misleading.

  • Not quite, it's still the COALESCE. In a comparison such as "select case when coalesce(cast (null as date),'')='' then 'true' else 'false' end" the result is "true" because of the zero date (from the COALESCE) coerced into an empty string, but when the COALESCE is further used in an expression it continues as a DATE type. As an example "select coalesce(cast (null as date),'')" returns a date rather than the often assumed empty string.

  • pparsons (6/17/2015)


    Not quite, it's still the COALESCE. In a comparison such as "select case when coalesce(cast (null as date),'')='' then 'true' else 'false' end" the result is "true" because of the zero date (from the COALESCE) coerced into an empty string, but when the COALESCE is further used in an expression it continues as a DATE type. As an example "select coalesce(cast (null as date),'')" returns a date rather than the often assumed empty string.

    No, this is not entirely correct. In a comparison such as "select case when coalesce(cast (null as date),'')='' then 'true' else 'false' end" the result is indeed "true". However, that is NOT because of zero date (from the COALESCE) is coerced into an empty string, but because the comparison between a zero date and an empty string prompts an implicit conversion of the empty string into date.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • Thanks to Phil for an interesting question and to everyone for interesting discussion!

  • good question. thanks for sharing

  • Excellent question. Thanks.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply