March 29, 2010 at 7:07 am
Hi
I am experiencing something peculiar with a query that orders the result on a column enclosed by IsNull and the output is selected in a local @variable. Consider the following query:
--- Query Starts --
Declare @t1 Table (SeqNo Int, ID Varchar(50))
Insert Into @t1
Select 1, 'One' Union
Select 2, 'Two' Union
Select Null, 'Unknown'
Declare @t2 Table (SeqNo Int, ColName Varchar(50))
Insert Into @t2
Select 1, 'First' Union
Select 2, 'Second' Union
Select 3, 'Third'
Select IsNull(t1.SeqNo,t2.SeqNo),t2.ColName From
@t2 t2
Left Outer Join @t1 t1 On t2.SeqNo = t1.SeqNo
Order By IsNull(t1.SeqNo,t2.SeqNo)
Declare @csv Varchar(1000)
Set @csv = ''
Select @csv = @csv + ',' + t2.ColName From
@t2 t2
Left Outer Join @t1 t1 On t2.SeqNo = t1.SeqNo
Order By IsNull(t1.SeqNo,t2.SeqNo)
Select @csv
Set @csv = ''
Select @csv = @csv + ',' + t2.ColName From
@t2 t2
Left Outer Join @t1 t1 On t2.SeqNo = t1.SeqNo
Order By t1.SeqNo
Select @csv
-- Query Ends --
The above query produces three resultsets as:
1First
2Second
3Third
------------------------------
,Third
------------------------------
,Third,First,Second
------------------------------
The output produced in second result is ',Third' whereas I expect it to be ',First,Second,Third'. The table output produced in first result is OK but when the same is selected in a @var only the last value is saved. To me it should be a straightforward output, but I don't know if I am missing something here !!
March 29, 2010 at 7:19 am
Still looking into the "WHY" but remove the Order by from the second query and that gives you the expected results.
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgMarch 29, 2010 at 7:27 am
Thanks Jason, you are right removing the Order By will produce the result but not the required/correct result in every case. The query I provided is part of the issue I faced in a project. However I progressed through a workaround but I am still eager to know the reason behind this.
March 29, 2010 at 7:28 am
Jason Selburg (3/29/2010)
Still looking into the "WHY" but remove the Order by from the second query and that gives you the expected results.
DUH! f I would have read the title of this post I would have known that...LOL
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgMarch 29, 2010 at 9:43 am
It's an order of operations thing. If you look at the execution plans, the sort doesn't come until after the compute scalar. Removing the order by, removing the join, changing the order by to just t2.SeqNo OR t1.seqNo will all fix the order and make the sort come before the compute scalar. I think it has to do with the function in the order by changing the order it has to process the query.
That's only half an explanation though. Still not quite seeing what ends up giving you only the last one. (If you change the sort order to DESC, you'll get only ,First instead of ,Third.
-- Your Way
|--Sort(ORDER BY:([Expr1003] ASC))
|--Compute Scalar(DEFINE:([Expr1002]=Convert([@csv]+','+[t2].[ColName]), [Expr1003]=isnull([t1].[SeqNo], [t2].[SeqNo])))
|--Nested Loops(Left Outer Join, WHERE:([t2].[SeqNo]=[t1].[SeqNo]))
|--Table Scan(OBJECT:(@t2 AS [t2]))
|--Table Scan(OBJECT:(@t1 AS [t1]))
-- Removing Isnull from the Sort
|--Compute Scalar(DEFINE:([Expr1002]=Convert([@csv]+','+[t2].[ColName])))
|--Sort(ORDER BY:([t2].[SeqNo] ASC))
|--Nested Loops(Left Outer Join, WHERE:([t2].[SeqNo]=[t1].[SeqNo]))
|--Table Scan(OBJECT:(@t2 AS [t2]))
|--Table Scan(OBJECT:(@t1 AS [t1]))
March 29, 2010 at 12:29 pm
Thanks Phelabaum, this makes sense.
Can one write a similar query through which a query plan can be achieved that ensures the same output being assigned to @variable that is being returned as table. (Please consider the query I provided as an example, this is not the actual query I wanted to execute.)
March 29, 2010 at 6:27 pm
Jason Selburg (3/29/2010)
Still looking into the "WHY"
It's a bug in SQL Server.
Instead of applying ORDER BY condition to the outcome of OUTER join (as it should be according to ANSI) it applies it to each participant in he join.
Same about WHERE clause.
That causes unexpected results returned by views with OUTER JOIN inside.
It was fixed in SP3 but introduced back in SP4.
_____________
Code for TallyGenerator
March 29, 2010 at 10:39 pm
March 30, 2010 at 6:38 am
Sergiy (3/29/2010)
Jason Selburg (3/29/2010)
Still looking into the "WHY"It's a bug in SQL Server.
Instead of applying ORDER BY condition to the outcome of OUTER join (as it should be according to ANSI) it applies it to each participant in he join.
Same about WHERE clause.
That causes unexpected results returned by views with OUTER JOIN inside.
It was fixed in SP3 but introduced back in SP4.
Just when I thought I was going crazy. Sergiy had to come along and prove it.. :w00t:
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgViewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply