IsNull and Coalesce

  • Comments posted to this topic are about the item IsNull and Coalesce

  • Good question thanks.

    ...

  • Good question, and I am glad that the answer I expected to be correct was not listed.

    I do want to point out a few things, though.

    First, on ISNULL - the claim in the explanation that "ISNULL return value is always considered NOT NULLable (assuming the return value is a non-nullable one)" is nonsense. The return value of ISNULL will be nullable if the both arguments to ISNULL are nullable. The sentence in parentheses makes no sense at all. If this is actually about the values, then the statement as a whole becomes a tautology: "the return value is not nullable if it is non-nullable". D'oh. On the other hand, if the parenthetical refers to data types that are non-nullable, then this is a fallacy, because one of the basic rules of the relational model is that every data type always has to be nullable.

    Second, I do not understand the results for the COALESCE test and I cannot find an explanation in any documentation. When I first ran into problems because COALESCE returned a nullable type even when the last parameter was constant, I was told that this is because COALESCE is expanded to "CASE WHEN expr1 IS NOT NULL THEN expr1 WHEN expr2 IS NOT NULL THEN expr2 ELSE NULL END", and the final "ELSE NULL" makes it nullable even if that ELSE part can never be reached. But this questions proves that explanation wrong. I almost settled for the theory that COALESCE bases the nullability of the returned data type on the first[/i] parameter, but that is also not true - using the setup from the question, when I use COALESCE(NAME, CAST(id AS varchar(5))), I get a nullable column again. I tried a few other variations, and the results simply made no sense to me.

    If anyone has a good explanation, or a link to a good explanation, to understand the exact rules for nullability of COALESCE - please post it! Currently this feels like playing lottery to me, and that's something I truly hate in SQL Server. There must be rules, and I want to know and understand them.


    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/

  • This is one of the link I still have in my bookmarks - http://sqlmag.com/t-sql/coalesce-vs-isnull. (Lots of variations to test)

    ww; Raghu
    --
    The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.

  • Hugo Kornelis (2/28/2016)


    Good question, and I am glad that the answer I expected to be correct was not listed.

    I do want to point out a few things, though.

    First, on ISNULL - the claim in the explanation that "ISNULL return value is always considered NOT NULLable (assuming the return value is a non-nullable one)" is nonsense. The return value of ISNULL will be nullable if the both arguments to ISNULL are nullable. The sentence in parentheses makes no sense at all. If this is actually about the values, then the statement as a whole becomes a tautology: "the return value is not nullable if it is non-nullable". D'oh. On the other hand, if the parenthetical refers to data types that are non-nullable, then this is a fallacy, because one of the basic rules of the relational model is that every data type always has to be nullable.

    Second, I do not understand the results for the COALESCE test and I cannot find an explanation in any documentation. When I first ran into problems because COALESCE returned a nullable type even when the last parameter was constant, I was told that this is because COALESCE is expanded to "CASE WHEN expr1 IS NOT NULL THEN expr1 WHEN expr2 IS NOT NULL THEN expr2 ELSE NULL END", and the final "ELSE NULL" makes it nullable even if that ELSE part can never be reached. But this questions proves that explanation wrong. I almost settled for the theory that COALESCE bases the nullability of the returned data type on the first[/i] parameter, but that is also not true - using the setup from the question, when I use COALESCE(NAME, CAST(id AS varchar(5))), I get a nullable column again. I tried a few other variations, and the results simply made no sense to me.

    If anyone has a good explanation, or a link to a good explanation, to understand the exact rules for nullability of COALESCE - please post it! Currently this feels like playing lottery to me, and that's something I truly hate in SQL Server. There must be rules, and I want to know and understand them.

    I don't understand this either. At one time I played with it and thought it might be that the result of coalesce is nullable if and only if the first argument is a not non-null constant and at least one of the arguments is nullable. But that sounds too silly to be correct, and I stopped playing with it on the grounds that if I disproved that one the definition would have to be even more bizzare.

    edit: of course the answer "ought to be" that the expression is not nullable if and only if it is impossible for it to have the value NULL. But it isn't.

    Tom

  • My assumption is that this line from the BOL entry for COALESCE

    If all expressions are nonnullable, the result is typed as nonnullable.

    has the intended meaning of

    If and only if all expressions are nonnullable, the result is typed as nonnullable.

    Obviously those mean different things, but that reading is at least consistent with all the examples I've seen so far.

    Cheers!

    EDIT: That seems confirmed by the later point in that same BOL entry:

    The NULLability of the result expression is different for ISNULL and COALESCE. The ISNULL return value is always considered NOT NULLable (assuming the return value is a non-nullable one) whereas COALESCE with non-null parameters is considered to be NULL. So the expressions ISNULL(NULL, 1) and COALESCE(NULL, 1) although equivalent have different nullability values.

    They still don't explicitly say that the COALESCE expression is considered nullable unless every argument is non-nullable, but between those two pieces of the documentation and the results we've seen I'd say that is pretty clear.

  • TomThomson (2/29/2016)


    Hugo Kornelis (2/28/2016)


    .

    ...

    Second, I do not understand the results for the COALESCE test and I cannot find an explanation in any documentation. When I first ran into problems because COALESCE returned a nullable type even when the last parameter was constant, I was told that this is because COALESCE is expanded to "CASE WHEN expr1 IS NOT NULL THEN expr1 WHEN expr2 IS NOT NULL THEN expr2 ELSE NULL END", and the final "ELSE NULL" makes it nullable even if that ELSE part can never be reached. But this questions proves that explanation wrong. I almost settled for the theory that COALESCE bases the nullability of the returned data type on the first[/i] parameter, but that is also not true - using the setup from the question, when I use COALESCE(NAME, CAST(id AS varchar(5))), I get a nullable column again. I tried a few other variations, and the results simply made no sense to me.

    If anyone has a good explanation, or a link to a good explanation, to understand the exact rules for nullability of COALESCE - please post it! Currently this feels like playing lottery to me, and that's something I truly hate in SQL Server. There must be rules, and I want to know and understand them.

    I don't understand this either. At one time I played with it and thought it might be that the result of coalesce is nullable if and only if the first argument is a not non-null constant and at least one of the arguments is nullable. But that sounds too silly to be correct, and I stopped playing with it on the grounds that if I disproved that one the definition would have to be even more bizzare.

    edit: of course the answer "ought to be" that the expression is not nullable if and only if it is impossible for it to have the value NULL. But it isn't.

    Hmmm. I was wondering the very same thing. But if neither Hugo nor Tom know the answer, I'm guessing I won't find out. 🙂

  • Hmm... The usual out, "but I learned something."

    Thanks, Mayank!

  • Jacob Wilkins (2/29/2016)


    My assumption is that this line from the BOL entry for COALESCE

    If all expressions are nonnullable, the result is typed as nonnullable.

    has the intended meaning of

    If and only if all expressions are nonnullable, the result is typed as nonnullable.

    Obviously those mean different things, but that reading is at least consistent with all the examples I've seen so far.

    Cheers!

    Intended meaning - well, you may be right about that; but if so the writer who intended that meaning didn't know what coalesce actually does.

    Here's an example that delivers a nun-nullable result when at least one f the expressions is nullable:-

    create table wird (x INT null, y int not null)

    select coalesce(2,x) xx INTO X1 FROM wird

    drop table wird

    The resulting table X1 has column xx not nullable, despite the argument list to coalesce including a nullable one. Note that as Hugo (or maybe the original question) pointed out that if that "2" had been a non-nullable column instead of a literal constant the xx column would have been nullable. For me, just these two (one nullable, one nonnullable) examples suggest that the whole horrible thing is just plain stupid, making no sense at all.

    Maybe it's all a bug in the implemetation of coalesce, or in the treatement of coalesce expressions in the context of insert into; or maybe it's a bug in BOL. but it's certainly a bug in one of those if your interpretation of BOL is correct (and I believe that it is probalt correct, although on the face of it your "and only if" is just a guess about the intention).

    edit: if you try that, don't forget to drop table X1 after checking the type of column xx.

    edit again: to get the quote /quote tags right

    Tom

  • Indeed, that is just a guess on my part, trying to make sense of as much of the situation as possible.

    Based on that example it seems (mildly annoyingly) that the nullability of the resulting column depends on how the COALESCE statement ends up being rewritten. With the NOT NULL column as the first argument, when it is treated as a CASE WHEN y IS NOT NULL THEN y ELSE x END, the resulting column is NULLable.

    When the NOT NULL column y is replaced with the constant 2, the whole CASE structure disappears, replaced by the the constant 2. That makes good sense, but it is a tad odd that COALESCE with the initial argument as a constant is treated as NOT NULL, while COALESCE with a constant anywhere else in the argument list, which can also never be NULL, is treated as NULLable.

    The only real difference is that the version with the leading constant gets rewritten as just that constant, while the others retain the CASE/IIF structure.

    So, maybe it's all less a set of rules about COALESCE and more about the rules for what is in the resulting rewrite. For the CASE WHEN...ELSE expression where all potential outputs are NOT NULL, the resulting column is NOT NULL, while otherwise it's NULLable.

    If it gets rewritten as a constant, then well, it's of course NOT NULL.

    A bit strange indeed that it seems to depend not on the function itself but how it gets rewritten. Alternatively, it could be expressed as "COALESCE either with a constant as the first argument or with all NOT NULL arguments is evaluated as NOT NULL. Otherwise it is evaluated as NULLable."

    As before, just some semi-educated guessing based on a few tests. Who knows what's really going on over at MS 🙂

    Cheers!

  • Very interesting question. Thanks!

  • Nice question, thanks.

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

  • Thanks for the question.

Viewing 13 posts - 1 through 12 (of 12 total)

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