IsNull and Coalesce

Question

Post reply

IsNull and Coalesce

er.mayankshukla

SSCarpal Tunnel

Points: 4190
More actions
February 27, 2016 at 7:18 pm

#325192

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

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

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

HappyGeek SSCoach Points: 18729 More actions · Answer 1

HappyGeek

SSCoach

Points: 18729

February 27, 2016 at 10:57 pm

#1861671

Good question thanks.

...

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 2

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/

Raghavendra Mudugal SSChampion Points: 10658 More actions · Answer 3

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.

TomThomson SSC Guru Points: 104773 More actions · Answer 4

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

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 5

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.

Marcia J SSCertifiable Points: 5649 More actions · Answer 6

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. 🙂

Revenant SSC-Forever Points: 42467 More actions · Answer 7

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

Thanks, Mayank!

TomThomson SSC Guru Points: 104773 More actions · Answer 8

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

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 9

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!