ORDER BY VIRTUAL TABLE CASE END bug?

18 Aug 2006 9:15 AM

kurt sune

Hi, is this a bug in SQL server 2000?

Create a table:

create table dbo.Numbers (
Number1            integer          not null,
Number2            integer          not null,
) go

Populate it:

INSERT INTO dbo.Numbers VALUES (2, 3)
INSERT INTO dbo.Numbers VALUES (4, 8)
INSERT INTO dbo.Numbers VALUES (7, 5)
go

This statement works:

DECLARE @OrderBy varchar(33)
SET @OrderBy = 'Number2'
SELECT * FROM (SELECT
N1      = A.Number1
, N2      = A.Number2
FROM dbo.Numbers A
) X ORDER BY CASE
WHEN @OrderBy = 'Number1' THEN CAST(N1 as varchar)
WHEN @OrderBy = 'Number2' THEN CAST(N2 as varchar)
ELSE 2 END

This doesnt, bug?:

DECLARE @OrderBy varchar(33)
SET @OrderBy = ''
SELECT * FROM (SELECT
N1      = A.Number1
, N2      = A.Number2
FROM dbo.Numbers A
) X ORDER BY CASE
WHEN @OrderBy = 'Number1' THEN N1
WHEN @OrderBy = 'Number2' THEN N2
ELSE 2 END

/k

18 Aug 2006 12:28 PM

Alejandro Mesa

kurt sune,

> Hi, is this a bug in SQL server 2000?

No, it is not a bug in SQL Server. Seems that you forgot to assign a value
to variable @OrderBy in the second example.

Show quote

> This doesnt, bug?:
>
> DECLARE @OrderBy varchar(33)

> SET @OrderBy = ''

> SELECT * FROM (SELECT
> N1 = A.Number1
> , N2 = A.Number2
> FROM dbo.Numbers A
> ) X ORDER BY CASE
> WHEN @OrderBy = 'Number1' THEN N1
> WHEN @OrderBy = 'Number2' THEN N2
> ELSE 2 END

AMB

Show quote

"kurt sune" wrote:

> Hi, is this a bug in SQL server 2000?
>
> Create a table:
>
> create table dbo.Numbers (
> Number1            integer          not null,
> Number2            integer          not null,
> ) go
>
> Populate it:
>
> INSERT INTO dbo.Numbers VALUES (2, 3)
> INSERT INTO dbo.Numbers VALUES (4, 8)
> INSERT INTO dbo.Numbers VALUES (7, 5)
> go
>
> This statement works:
>
> DECLARE @OrderBy varchar(33)
> SET @OrderBy = 'Number2'
> SELECT * FROM (SELECT
>   N1      = A.Number1
> , N2      = A.Number2
> FROM dbo.Numbers A
> ) X ORDER BY CASE
> WHEN @OrderBy = 'Number1' THEN CAST(N1 as varchar)
> WHEN @OrderBy = 'Number2' THEN CAST(N2 as varchar)
> ELSE 2 END
>
>
> This doesnt, bug?:
>
> DECLARE @OrderBy varchar(33)
> SET @OrderBy = ''
> SELECT * FROM (SELECT
>   N1      = A.Number1
> , N2      = A.Number2
> FROM dbo.Numbers A
> ) X ORDER BY CASE
> WHEN @OrderBy = 'Number1' THEN N1
> WHEN @OrderBy = 'Number2' THEN N2
> ELSE 2 END
>
>
> /k
>
>
>

18 Aug 2006 12:32 PM

Tracy McKibben

kurt sune wrote:
Show quote

> Hi, is this a bug in SQL server 2000?
>
> Create a table:
>
> create table dbo.Numbers (
> Number1            integer          not null,
> Number2            integer          not null,
> ) go
>
> Populate it:
>
> INSERT INTO dbo.Numbers VALUES (2, 3)
> INSERT INTO dbo.Numbers VALUES (4, 8)
> INSERT INTO dbo.Numbers VALUES (7, 5)
> go
>
> This statement works:
>
> DECLARE @OrderBy varchar(33)
> SET @OrderBy = 'Number2'
> SELECT * FROM (SELECT
>   N1      = A.Number1
> , N2      = A.Number2
> FROM dbo.Numbers A
> ) X ORDER BY CASE
> WHEN @OrderBy = 'Number1' THEN CAST(N1 as varchar)
> WHEN @OrderBy = 'Number2' THEN CAST(N2 as varchar)
> ELSE 2 END
>
>
> This doesnt, bug?:
>
> DECLARE @OrderBy varchar(33)
> SET @OrderBy = ''
> SELECT * FROM (SELECT
>   N1      = A.Number1
> , N2      = A.Number2
> FROM dbo.Numbers A
> ) X ORDER BY CASE
> WHEN @OrderBy = 'Number1' THEN N1
> WHEN @OrderBy = 'Number2' THEN N2
> ELSE 2 END
>
>
> /k
>
>

Define "doesn't work" - do you get an error, or just unexpected results?

--
Tracy McKibben
MCDBA
http://www.realsqlguy.com

18 Aug 2006 12:35 PM

Roy Harvey

When the CASE's ELSE condition resolves to 2, it resolves to the digit
2, not the column in position 2. That value 2 is the same for every
row - it is not sorting at all.

Roy Harvey
Beacon Falls, CT

Show quote

On Fri, 18 Aug 2006 11:15:58 +0200, "kurt sune" <a**@apa.com> wrote:

>Hi, is this a bug in SQL server 2000?
>
>Create a table:
>
>create table dbo.Numbers (
>Number1            integer          not null,
>Number2            integer          not null,
>) go
>
>Populate it:
>
>INSERT INTO dbo.Numbers VALUES (2, 3)
>INSERT INTO dbo.Numbers VALUES (4, 8)
>INSERT INTO dbo.Numbers VALUES (7, 5)
>go
>
>This statement works:
>
>DECLARE @OrderBy varchar(33)
>SET @OrderBy = 'Number2'
>SELECT * FROM (SELECT
> N1      = A.Number1
>, N2      = A.Number2
>FROM dbo.Numbers A
>) X ORDER BY CASE
>WHEN @OrderBy = 'Number1' THEN CAST(N1 as varchar)
>WHEN @OrderBy = 'Number2' THEN CAST(N2 as varchar)
>ELSE 2 END
>
>
>This doesnt, bug?:
>
>DECLARE @OrderBy varchar(33)
>SET @OrderBy = ''
>SELECT * FROM (SELECT
> N1      = A.Number1
>, N2      = A.Number2
>FROM dbo.Numbers A
>) X ORDER BY CASE
>WHEN @OrderBy = 'Number1' THEN N1
>WHEN @OrderBy = 'Number2' THEN N2
>ELSE 2 END
>
>
>/k
>