November 9, 2012 at 7:42 am
Great noncontroversial question to end the week!
November 9, 2012 at 7:51 am
It's a good question, with the right answer.
I don't like the explanation, though. Admittedly those words occur in BoL, but I don't like them there either (although MS would probably claim that their position on the page makes it clear that they only apply in the context where all arguments are (typed or untyped) null, I think that's a lazy and careless argument). they are also in the error message (where they are just as wrong). It's simply not true that coalesce requires at least one of its arguments to be a typed null.
The correct statement is "At least one argument of coalesce must be typed". This allows for literal consonants (which are implicitly typed), variables and column names which are explicitly typed by declaration, expressions which are typed by type inference from function, operator, and subexpression types, and expressions explicitly typed as a resut of casting or conversion. As long as there is at least one typed argument (whether null or not), coalesce can derive a type for its result. If there isn't one, it can't - and this causes an error, since coalesce is specified to deliver a typed result.
Tom
November 9, 2012 at 7:52 am
I had to read twice to realize that there was a COALESCE(NULL, NULL).
It's good to know how this is wrong, but it shouldn't be in real code. Why would someone use this? the only possible answer I can think of is debugging.
November 9, 2012 at 7:53 am
Mike Dougherty-384281 (11/9/2012)
While it is an interesting exploitation of an edge case, when does this situation happen in real code?Does this example illustrate a Fail in the implementation/execution of coalesce()? I would expect the function to return null if all of the arguments supplied are null. ..
It does... try the below... when used with the columns if all the supplied arguments are null, then it returns null. (but what I guess here, is that, the usage of hard NULL, where it expects atleast one argument to be non null, even if the usage of such null, needs to return null then i cant think of any cases where and when all the arguments will be passed as hard null.... and if someone does... why?)
create table TestNull
(
id int,
Fname varchar(10),
Lname varchar(10)
)
insert TestNull
select 1, 'F1', 'L1'
union all
select 2, null, null
union all
select 3, null, 'L3'
select id, coalesce(Fname, Lname), isnull(fname, lname) from TestNull
ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.
November 9, 2012 at 8:04 am
A nice and simple one to end the week with - thanks!
November 9, 2012 at 8:39 am
L' Eomot Inversé (11/9/2012)
It's a good question, with the right answer.I don't like the explanation, though. Admittedly those words occur in BoL, but I don't like them there either (although MS would probably claim that their position on the page makes it clear that they only apply in the context where all arguments are (typed or untyped) null, I think that's a lazy and careless argument). they are also in the error message (where they are just as wrong). It's simply not true that coalesce requires at least one of its arguments to be a typed null.
The correct statement is "At least one argument of coalesce must be typed". This allows for literal consonants (which are implicitly typed), variables and column names which are explicitly typed by declaration, expressions which are typed by type inference from function, operator, and subexpression types, and expressions explicitly typed as a resut of casting or conversion. As long as there is at least one typed argument (whether null or not), coalesce can derive a type for its result. If there isn't one, it can't - and this causes an error, since coalesce is specified to deliver a typed result.
Thanks for the explanaition. I was wondering why one of the agruments had to be typed and your explanation makes perfect sense.
So this works:
DECLARE @a varchar(100) = NULL;
SELECT coalesce(NULL,@a);
But this doesn't:
SELECT coalesce(NULL,NULL);
November 9, 2012 at 9:43 am
It's difficult to think of a situation where this would arise...is it actually possible to get an untyped NULL via any means other than just typing NULL into the function?
November 9, 2012 at 9:45 am
paul.knibbs (11/9/2012)
It's difficult to think of a situation where this would arise...is it actually possible to get an untyped NULL via any means other than just typing NULL into the function?
If you're doing dynamic SQL you could end up with this depending on how the statement was compiled.
November 9, 2012 at 7:57 pm
cfradenburg (11/9/2012)
paul.knibbs (11/9/2012)
It's difficult to think of a situation where this would arise...is it actually possible to get an untyped NULL via any means other than just typing NULL into the function?If you're doing dynamic SQL you could end up with this depending on how the statement was compiled.
That's the best answer to how/why this would ever happen in real code.
I realize this was only a QotD, but an article on an environment using dynamic SQL encountering this problem would be an interesting read (to know who might be at-risk for such a pitfall)
November 10, 2012 at 9:47 am
Mike Dougherty-384281 (11/9/2012)
I feel like the concept of a "typed null" is wrong. I'd say type is a property (metadata) of a variable rather than the value itself.
Type isn't a property of the value. It's a property of the expression. ("Two" is text; 2 is numeric; the value is the same (or convertible) but the expression and hence the type differ.)
The dirty secret is, SQL Server doesn't let us work with values. When you type NULL into a statement, that's technically not a value, but an expression. Same is true with @a, 2.5, [Col1], or 2.5*@a+[Col1].
If you think of it that way, you'll see why a NULL in a column or variable can (must) have a type associated with it, while a NULL constant cannot.
November 12, 2012 at 7:51 pm
sknox (11/10/2012)
If you think of it that way, you'll see why a NULL in a column or variable can (must) have a type associated with it, while a NULL constant cannot.
Then there's the case of a NULL consonant cast to a specific type: that's not a variable, not a column; but it still has a type.
Tom
November 13, 2012 at 7:26 am
L' Eomot Inversé (11/12/2012)
sknox (11/10/2012)
If you think of it that way, you'll see why a NULL in a column or variable can (must) have a type associated with it, while a NULL constant cannot.Then there's the case of a NULL consonant cast to a specific type: that's not a variable, not a column; but it still has a type.
In that case, CAST(NULL as {type}) is an expression that SQL Server resolves and stores in an internal variable. It's not the same thing as the NULL constant -- in fact the NULL constant in that expression has no type associated with it, but the resulting NULL expression does.
November 13, 2012 at 2:37 pm
Thanks for the question
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
June 5, 2013 at 11:51 pm
Thanx for the question..
Another difference between coalesce and Isnull
Select coalesce(Null,Null)
Msg 4127, Level 16, State 1, Line 1
At least one of the arguments to COALESCE must be a typed NULL.
Select ISnull(Null,Null)
returns Null
Declare @a varchar(100)
Select coalesce(Null,@a)
select @a
returns Null
Because here @a is typed varchar(100) so it does`t return error .
Neeraj Prasad Sharma
Sql Server Tutorials
June 6, 2013 at 2:47 am
sknox (11/13/2012)
L' Eomot Inversé (11/12/2012)
sknox (11/10/2012)
If you think of it that way, you'll see why a NULL in a column or variable can (must) have a type associated with it, while a NULL constant cannot.Then there's the case of a NULL consonant cast to a specific type: that's not a variable, not a column; but it still has a type.
In that case, CAST(NULL as {type}) is an expression that SQL Server resolves and stores in an internal variable. It's not the same thing as the NULL constant -- in fact the NULL constant in that expression has no type associated with it, but the resulting NULL expression does.
Apparently you use the word constant in a manner rather different from the way I use it. The value of cast(NULL as INT) certainly can not vary, so it is a constant expression.
And even if you mean (as I think you must ) "a literal representing a constant" there are cases when NULL has type, for example in the expression @C+NULL varchar(1), if @C has type varchar(6) then NULL has type varchar(1), if @C has type INT in that expression then NULL also has type INT. At least that's the way type inference seems to treat NULL since SQLS 2005 (it was different in SQLS 2000).
Tom
Viewing 15 posts - 16 through 29 (of 29 total)
You must be logged in to reply to this topic. Login to reply