Bug creating CSV with IsNull and ORDER BY? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

Bug creating CSV with IsNull and ORDER BY?

Author

Topic

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-10-13 : 02:09:12

Hi all

A colleague of mine showed me this strange problem while trying to produce a CSV. It works fine as is, but add the ORDER BY and only the first or the last entry shows up. I checked the MS Knowledge base but I couldn't find anything related to this.


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[EmployerOrders]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[EmployerOrders]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[EmployerLogin]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[EmployerLogin]
GO

CREATE TABLE [dbo].[EmployerOrders] (
	[OrderNo] [int] IDENTITY (1, 1) NOT NULL ,
	[EmployerId] [int] NOT NULL ,
	[OrderDate] [smalldatetime] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[EmployerLogin] (
	[EmployerId] [int] IDENTITY (1000, 1) NOT NULL ,
	[LoginId] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Password] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[RegisterDate] [smalldatetime] NOT NULL ,
	[CompanyName] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
) ON [PRIMARY]
GO

INSERT INTO EmployerLogin 
	SELECT 'a', 'a', GETDATE(), 'Company A'
	UNION 
	SELECT 'b', 'b', GETDATE(), 'Company B'
	UNION 
	SELECT 'c', 'c', GETDATE(), 'Company C'
	UNION 
	SELECT 'd', 'd', GETDATE(), 'Company D'
	UNION 
	SELECT 'e', 'e', GETDATE(), 'Company E'
GO

--SELECT * FROM EmployerLogin
--GO

INSERT INTO EmployerOrders
	SELECT 1000, GETDATE()
	UNION ALL
	SELECT 1001, GETDATE()
	UNION ALL
	SELECT 1001, GETDATE()
	UNION ALL
	SELECT 1002, GETDATE()
	UNION ALL
	SELECT 1002, GETDATE()
	UNION ALL
	SELECT 1003, GETDATE()
GO

--SELECT * FROM EmployerOrders
--GO

--The problem is here
DECLARE @str varchar(1000)

SELECT @str = IsNull(@str + ',','') + CompanyName
	FROM EmployerLogin a LEFT JOIN EmployerOrders b
	ON a.EmployerId = b.EmployerId
GROUP BY CompanyName
--ORDER BY COUNT(b.OrderNo)

PRINT @str

Run this code and it should show a comma-separated list of 4 companies. Uncomment the ORDER BY in the last batch and it returns only one company.

Any ideas?

Owais

Make it idiot proof and someone will make a better idiot

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2003-10-13 : 02:18:48

Works perfectly on my local machine..

SELECT @@Version

Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 1)

DavidM

"SQL-3 is an abomination.."

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-10-13 : 03:38:05

Thanks David.

That's funny...it suddenly started working correctly on my machine too (I ran sp_updatestats). This was in the Northwind database, so I ran this code from the pubs database, and I encountered this problem again! I did a showplan_text on both of them and the difference was obvious:

pubs (doesn't work right)
==========================


  |--Sort(ORDER BY:([Expr1002] ASC))
       |--Compute Scalar(DEFINE:([Expr1003]=Convert(isnull([@str]+',', '')+[a].[CompanyName])))
            |--Compute Scalar(DEFINE:([Expr1002]=Convert([Expr1006])))
                 |--Stream Aggregate(GROUP BY:([a].[CompanyName]) DEFINE:([Expr1006]=COUNT_BIG([ b ].[OrderNo])))
                      |--Nested Loops(Left Outer Join, WHERE:([a].[EmployerId]=[ b ].[EmployerId]))
                           |--Sort(ORDER BY:([a].[CompanyName] ASC))
                           |    |--Table Scan(OBJECT:([Northwind].[dbo].[EmployerLogin] AS [a]))
                           |--Table Scan(OBJECT:([Northwind].[dbo].[EmployerOrders] AS [ b ]))

Northwind (works as expected)
============================


  |--Compute Scalar(DEFINE:([Expr1003]=Convert(isnull([@str]+',', '')+[a].[CompanyName])))
       |--Sort(ORDER BY:([Expr1002] ASC))
            |--Compute Scalar(DEFINE:([Expr1002]=Convert([Expr1006])))
                 |--Stream Aggregate(GROUP BY:([a].[CompanyName]) DEFINE:([Expr1006]=COUNT_BIG([ b ].[EmployerId])))
                      |--Nested Loops(Left Outer Join, WHERE:([a].[EmployerId]=[ b ].[EmployerId]))
                           |--Sort(ORDER BY:([a].[CompanyName] ASC))
                           |    |--Table Scan(OBJECT:([Northwind].[dbo].[EmployerLogin] AS [a]))
                           |--Table Scan(OBJECT:([Northwind].[dbo].[EmployerOrders] AS [ b ]))

See the difference in the first two lines? This is getting intriguing...

Owais

Make it idiot proof and someone will make a better idiot

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-10-13 : 10:38:26

i saw the same bug at one point, i forget where ... sp3 seemed to fix it. which version of SQL Server 2000 are you running ?

- Jeff

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-10-13 : 12:49:22

aha! SQL Server 2000 Service Pack 1.

Time to begin downloading Service Pack 3a....la la la la

Btw, I managed to make it work now by wrapping the query in a derived table, but it's just not perfect!!

Owais

Make it idiot proof and someone will make a better idiot

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources