September 22, 2014 at 9:51 am
Nice question. Thanks!
September 22, 2014 at 10:36 am
marcia.j.wilson (9/22/2014)
JustMarie (9/22/2014)
Made my guess, ran the code, got back NULL. Not an empty record set - NULL.So, did I. Even though that's what I answered, that's not what I was expecting since the explanation made sense to me.
Can anyone explain why I would get something back?Running 2008 R2.
Never mind. I see why. There are two select statements. The second one is doing a select on the variable @test-2 which is null. That's the one that is returning a null value.
+1 Thanks for the great question.
September 22, 2014 at 11:06 am
Thank you Don for this good question.
😎
September 22, 2014 at 11:52 am
Good question, mediocre explanation.
This has nothinng to do with the ISNULL being executed or not. The correct explanation would be that the SELECT assigns a value once for each row returned. If a query returns multiple rows, the last assignment executed will be the one that sticks. And if, as in this case, no rows are returned, no assignment is executed.
September 22, 2014 at 12:17 pm
stephen.long.1 (9/22/2014)
Nice, simple question - good way to get the brain working after the weekend. Thanks! 🙂
+:-)
Not all gray hairs are Dinosaurs!
September 23, 2014 at 3:00 am
Hugo Kornelis (9/22/2014)
Good question, mediocre explanation.This has nothinng to do with the ISNULL being executed or not. The correct explanation would be that the SELECT assigns a value once for each row returned. If a query returns multiple rows, the last assignment executed will be the one that sticks. And if, as in this case, no rows are returned, no assignment is executed.
And I may add: In fact, the trickery lies entirely elsewhere.
If the same script is run without assigning the result to a temp variable the answer would be "blank (no rows)".
The reason for the NULL being returned is the fact that the last SELECT is calling a temp variable which has never been assigned anything (since the queried table is empty, and thus the returned recordset is empty as well).
Finally, just to comment on the quoted explanation:
the last assignment executed will be the one that sticks
is correct - but a very dangerous way to go! If you try to insert something into table @testA, you will quickly realize a main feature of SQL Server: There is not concept of "first", "next", or "last"! (Btw, that's exactly why Windowing Functions - much enhanced in version 2012 - is so beneficial a tool!).
The result given (when @testA has content) is the result of the ISNULL function applied to the last row of the table - not in the creation order, but in the primary key, join, or sort order... Which for anything but the simplest of tables/queries is very hard to keep track of. In short: This type of coding ought to be avoided at all cost! - if you assign value to a scalar variable, make sure you have a uniquely defined and consistent result, instead of going around mapping tables to scalars...
September 23, 2014 at 8:00 am
Hugo Kornelis (9/22/2014)
Good question, mediocre explanation.This has nothinng to do with the ISNULL being executed or not. The correct explanation would be that the SELECT assigns a value once for each row returned. If a query returns multiple rows, the last assignment executed will be the one that sticks. And if, as in this case, no rows are returned, no assignment is executed.
[/quote]
That sounds excessively pedantic even to this hardened pedant. If that ISNULL call were executed, it would return a value and since the ISNILL call is in a select list the select statement would retyurn at least one row, and the value in the row wouln't be NULL. The reason the ISNULL isn't executed is that there are no rows in the table named, which is the table in the FROM clause. So what is stated in the explanation is pretty clearly what is going on. The thing which is utterly irrelevant is what would happen if the table contained multiple rows, which you introduced, not the explanation.
hjp (9/23/2014)
And I may add: In fact, the trickery lies entirely elsewhere.If the same script is run without assigning the result to a temp variable the answer would be "blank (no rows)".
I see no trickery. It is generally true that two different pieces of SQL may have two different results, which is what you appear to be complaining about.
hjp
Finally, just to comment on the quoted explanation:the last assignment executed will be the one that sticks
is correct - but a very dangerous way to go! If you try to insert something into table @testA, you will quickly realize a main feature of SQL Server: There is not concept of "first", "next", or "last"! (Btw, that's exactly why Windowing Functions - much enhanced in version 2012 - is so beneficial a tool!).
The result given (when @testA has content) is the result of the ISNULL function applied to the last row of the table - not in the creation order, but in the primary key, join, or sort order... Which for anything but the simplest of tables/queries is very hard to keep track of. In short: This type of coding ought to be avoided at all cost! - if you assign value to a scalar variable, make sure you have a uniquely defined and consistent result, instead of going around mapping tables to scalars...
If Hugo had said "that last row in the table" instead of "the last assignment executed" Hugo would have been wrong as well as irrelevant, but that's not what he said. Mapping tables to scalars is precisely what aggregate functions do in the absence of grouping, so hardly something to be avoided when assigning a value to a scalar variable. It's certainly true that mapping to a scalar in a way whose result depends on what the optimiser decides to do, which in turn depends not only on (i) the physical structure of the tables involved, (ii) how much RAM is available, and (iii) how many processing cores are available as well as on (iv) aspects of the tables involved which are determined by their relational properties as but also on what the OS's scheduler decides to do which itself depends in turn on what else is going on on the server is indeed somewhat dangerous, but I don't think Hugo's explanation was intended to encourage people to do that.
Tom
September 23, 2014 at 9:02 am
TomThomson (9/23/2014) ...which is what you appear to be complaining about.
I am certainly not complaining about anything 😉 And I am sorry if it appears that way.
In fact, I am actually agreeing with Hugo that it is a good question with a mediocre explanation, and seeks to further improve on the explanation.
Yes, veteran T-SQL programmers won't fall into a pit like the one given in the question. But I believe rookies might get excited with inspiration and miss what I consider important details hiding within the question. And yes, I do consider "assignment to a scalar variable by returning an entire table" as bad/lazy programming - but it is certainly a matter with the question and not Hugo's explanation!
But maybe I just misunderstood the point of this thread...
September 23, 2014 at 9:26 am
hjp (9/23/2014)
But maybe I just misunderstood the point of this thread...
No you did well, and added a lot to the conversation. Without it neither your comments, which were valuable, and Tom's response would have been left out, which would have caused the thread to be less comprehensive.
M.
Not all gray hairs are Dinosaurs!
September 23, 2014 at 9:34 am
Yikes. Methinks we've wrung a thunderstorm from a washcloth.
The point of the question was to illustrate that ISNULL (or COALESCE) do not prevent nulls when used in this manner (because they never get executed). I have several junior programmers and converted VB programers working for me that have fallen into this trap on several occasions. It is critical to understand that when you are retrieving data into some variable, whether sql or a language calling sql, if your query returns no rows, the ISNULL logic will not protect against null values.
Sorry if the explanation was too terse. The explanations that I've read for other questions are rarely very verbose.
Don
Don Simpson
September 30, 2014 at 9:10 am
hjp (9/23/2014)
In short: This type of coding ought to be avoided at all cost! - if you assign value to a scalar variable, make sure you have a uniquely defined and consistent result, instead of going around mapping tables to scalars...
This sort of code does have some uses though. For example:
declare @test-2 varchar(20) = '';
create table #testtable (c1 varchar(5));
insert into #testtable values('1'),('2'),('3');
select @test-2 = @test-2 + c1 from #testtable;
select @test-2;
September 30, 2014 at 10:13 am
Toreador (9/30/2014)
hjp (9/23/2014)
In short: This type of coding ought to be avoided at all cost! - if you assign value to a scalar variable, make sure you have a uniquely defined and consistent result, instead of going around mapping tables to scalars...This sort of code does have some uses though. For example:
declare @test-2 varchar(20) = '';create table #testtable (c1 varchar(5));
insert into #testtable values('1'),('2'),('3');
select @test-2 = @test-2 + c1 from #testtable;
select @test-2;
If you don't care whether the final returned value is 123, 321, 312, or even just 1 or 2 or 3, then yeah, it does have uses.
Me, when I concatenate strings I prefer to use code that ensures that all the input is included in the concatenated result - or if I want only some of the input, that I can specify in the WHERE filter exactly what part to include.
September 30, 2014 at 10:18 am
Hugo Kornelis (9/30/2014)
If you don't care whether the final returned value is 123, 321, 312, or even just 1 or 2 or 3, then yeah, it does have uses.Me, when I concatenate strings I prefer to use code that ensures that all the input is included in the concatenated result - or if I want only some of the input, that I can specify in the WHERE filter exactly what part to include.
Fair point about the lack of an Order By, I just knocked the code up quickly and forgot that bit.
But I'm interested in how it could return just 1 or 2 or 3?
September 30, 2014 at 10:27 am
Toreador (9/30/2014)
Hugo Kornelis (9/30/2014)
If you don't care whether the final returned value is 123, 321, 312, or even just 1 or 2 or 3, then yeah, it does have uses.Me, when I concatenate strings I prefer to use code that ensures that all the input is included in the concatenated result - or if I want only some of the input, that I can specify in the WHERE filter exactly what part to include.
Fair point about the lack of an Order By, I just knocked the code up quickly and forgot that bit.
But I'm interested in how it could return just 1 or 2 or 3?
See for instance http://support2.microsoft.com/kb/287515/en-us.
That's just a single example. I have seen more. The root cause is that, though the syntax is valid, this is not a documented or supported use of the language. This syntax is supposed to be used for queries with single-row results. With multiple rows, the result is undefined.
October 1, 2014 at 2:25 am
Interesting. Though the issue appears to be with applying functions to the Order By, which I don't think I've tried. I've certainly used this method successfully for many years - but it sounds like I should maybe have a rethink!
Viewing 15 posts - 16 through 30 (of 32 total)
You must be logged in to reply to this topic. Login to reply