Possible bug in the ORDER BY clause of SQL Server

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Possible bug in the ORDER BY clause of SQL Server

Author

Topic

CraigN
Starting Member

2 Posts

Posted - 2005-11-04 : 18:03:54

Instead of me retyping my entire case, please refer to my blog entry ([url]http://dotnet.org.za/craign/archive/2005/11/04/47987.aspx[/url]).

Any help will be appreciated.

nosepicker
Constraint Violating Yak Guru

366 Posts

Posted - 2005-11-04 : 19:12:08

Interesting. I think SQL Server's behavior can be explained if you compare this statement:


SELECT          f.[Id],
                [Description] = CASE
                    WHEN b.[Description] IS NOT NULL THEN b.[Description]
                    ELSE f.[Description]
                END
FROM            [dbo].[Foo] AS f
LEFT OUTER JOIN [dbo].[Bar] AS b
ON              f.[Id] = b.[Id]
ORDER BY        f.[Description] ASC

with this:


SELECT          f.[Id],
                CASE
                    WHEN b.[Description] IS NOT NULL THEN b.[Description]
                    ELSE f.[Description]
                END
FROM            [dbo].[Foo] AS f
LEFT OUTER JOIN [dbo].[Bar] AS b
ON              f.[Id] = b.[Id]
ORDER BY        f.[Description] ASC

Notice in the second statement, I didn't give the second column a column name of "Description" (left it blank instead). I think the problem is that when you named the column with the CASE statement "Description", SQL Server ordered by it in the ORDER BY clause. I know you told it to order by "f.[Description]", but it seems that it ignored the "f." part and basically did this: ORDER BY [Description] ASC. So it seems SQL Server will order by a column that is not named in the SELECT list, unless that same column name appears in the SELECT list as an aliased column name. This behavior occurs in SP3 too, not just SP4. I wouldn't really call this a bug - just something that has to be understood about how SQL Server will behave.

CraigN
Starting Member

2 Posts

Posted - 2005-11-05 : 03:34:06

Unfortunately by not defining and alias for the column I'm unable to retrieve the column by name from my Data Access Layer.

I have however tested the query on SQL Server 2005 September CTP and it works as designed. So I'm pretty sure its a lesser-known bug in SQL Server 2000.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources