March 11, 2009 at 1:22 pm
Check this out please
-- Wacky Sort Order Test
-- Create a table of data
create table #fiscal(fldYear int, fldStage int)
-- Populate the data
declare @C int
set @C = 0
while @C < 400
begin
insert into #fiscal(fldYear, fldStage)
select 2005, case when @C > 200 then 4 else 2 end
end
select cast(P.fldYear as varchar) + case when P.fldStage > 2 then ' Final' else ' Initial' end as fldYear
FROM #fiscal P order by P.fldYear, P.fldStage
select cast(P.fldYear as varchar) + case when P.fldStage > 2 then ' Final' else ' Initial' end as fldFoo
FROM #fiscal P order by P.fldYear, P.fldStage
When you run the first query you would expect the output to be sorted as 2005 Initial, 2005 Final. Not the case. It seems that since I have aliased the field with the same field name as the main table the sort mechanism is looking at the calculated field. This can be seen when looking at the execution plan.
Interesting huh?
March 11, 2009 at 11:10 pm
Yes. It is interesting.
I have also tested the query plan.
#1 query ordering is done based on the expression.
#2 query ordering is done based on the column.
karthik
March 12, 2009 at 3:49 am
Another interesting point, if you modify the query like this:
select P.fldYear as JIMMY, cast(P.fldYear as varchar) +
case when P.fldStage > 2 then ' Final' else ' Initial' end as fldYear
FROM #fiscal P order by P.fldYear, P.fldStage
It sorts correctly based on P.fldYear.
This issue was discovered when a report was modified to use calculated data in a field. After the change the report no longer was ordered correctly. This was a classic case of, "It couldn't have been the change I implemented because I didn't change the ordering clause...".
Is this a bug? I couldn't find anything in BOL regarding this.
Thanks
ST
March 12, 2009 at 4:44 am
I'd say it's a bug.
3 or 4 years ago it was a big discussion here. At that time current version of SQL Server was doing OK by following ANSI standard requirements.
But since then SP4 was introduced...
Sadly, it's not the only bug brought to life by SP4.
_____________
Code for TallyGenerator
March 13, 2009 at 12:27 am
I have tested the same query in sybase 12.5.3 and oracle 10g.
SYBASE is sorting the result based on the expression.
ORACLE is not sorting the result based on the expression.
is the same bug continuing in SQL 2005 & SQL 2008 ?
Sergiy,
Can you paste the URL of the discussion happened 4 or 5 years back? Just i wanted to take a look.
karthik
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply