Problem appending text inside SELECT

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

SQL Server Development (2000)

Problem appending text inside SELECT

Author

Topic

rockyfan206
Starting Member

2 Posts

Posted - 2006-09-28 : 04:30:09

I think I may have encountered a weird SQL Server bug?
If you look at the the sample code below the line that says "START HERE", the value of @S1 is 'ccc' when it should be 'aaabbbccc'. The other slighly modified sections of code after that work the way I expect. Does anyone have any ideas??

Thanks in advance!

-------------------------------------------------------
IF EXISTS (SELECT * FROM dbo.SYSOBJECTS WHERE id = object_id('dbo.ATest') AND OBJECTPROPERTY(id, 'IsUserTable') = 1)
DROP TABLE dbo.ATest
;

CREATE TABLE [dbo].[ATest]
(
[ID] [bigint] IDENTITY(1,1) NOT NULL,
[StringValue] [nvarchar](4000) NULL,
[DisplayOrder] [bigint] NOT NULL,
[ForeignKeyID1] [bigint] NOT NULL,
CONSTRAINT [PK_ATest] PRIMARY KEY CLUSTERED
(
[ID] ASC
)
)

INSERT INTO ATest (StringValue, DisplayOrder, ForeignKeyID1) VALUES ('aaa', 1, 100)
INSERT INTO ATest (StringValue, DisplayOrder, ForeignKeyID1) VALUES ('bbb', 2, 100)
INSERT INTO ATest (StringValue, DisplayOrder, ForeignKeyID1) VALUES ('ccc', 3, 100)

------------------------------------
--START HERE
------------------------------------
-- Only the string from the last row is appended to s1; WHY??
DECLARE @s1 nvarchar(4000)
set @s1 = ' '

SELECT @s1 = @s1 + StringValue
FROM dbo.ATest AS ATest
WHERE ATest.ForeignKeyID1=100
ORDER BY ATest.DisplayOrder

print 's1: ' + @s1

------------------------------------
-- ORDER BY clause is removed
DECLARE @s2 nvarchar(4000)
set @s2 = ' '

SELECT @s2 = @s2 + StringValue
FROM dbo.ATest AS ATest
WHERE ATest.ForeignKeyID1=100

print 's2: ' + @s2

------------------------------------
-- WHERE clause is removed
DECLARE @s3 nvarchar(4000)
set @s3 = ' '

SELECT @s3 = @s3 + StringValue
FROM dbo.ATest AS ATest
ORDER BY ATest.DisplayOrder

print 's3: ' + @s3

------------------------------------
-- ORDER BY different column
DECLARE @s4 nvarchar(4000)
set @s4 = ' '

SELECT @s4 = @s4 + StringValue
FROM dbo.ATest AS ATest
WHERE ATest.ForeignKeyID1=100
ORDER BY ATest.ID

print 's4: ' + @s4

mr_mist
Grunnio

1870 Posts

Posted - 2006-09-28 : 04:34:48

The last row is added because you are not applying any criteria to the SELECT statement to make it do anything else. To do what you are trying to do in your statment after the start here bit you either need to create some kind of WHILE loop and increment through each row, or use a cursor.

-------
Moo. :)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-28 : 04:36:43

This behaviour has been discussed numerous times.

Try this

DECLARE @s1 nvarchar(4000)
set @s1 = ''

SELECT @s1 = @s1 + z.StringValue 
FROM (select stringvalue from dbo.ATest 
	WHERE ForeignKeyID1=100
	ORDER BY DisplayOrder) z

print 's1: ' + @s1

Peter Larsson
Helsingborg, Sweden

Kristen
Test

22859 Posts

Posted - 2006-09-28 : 05:16:08

Eh? I do this all the time and don't use any workarounds as Moo & Peso describe.

In fact I just ran your test code and got:


(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
s1:  aaabbbccc
s2:  aaabbbccc
s3:  aaabbbccc
s4:  aaabbbccc

am I going barmy?

Kristen

rockyfan206
Starting Member

2 Posts

Posted - 2006-09-28 : 11:04:11

Sorry, perhaps I wasn't completely clear on what the problem was.

I expect s1 to be 'aaabbbccc' but it prints as 'ccc'. The following examples, s2, s3, and s4 all print 'aaabbbccc' but I can't see why they should behave any differently than s1! I just want s1 to behave like the others.

This occurs on SQL Server 2005 developer edition.

Peso, I tried your solution but I got:
Msg 1033, Level 15, State 1, Line 7
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.

When I add "TOP 1000" to the select, it seems to work. I think that should be sufficient because I know there won't be that many rows. Seems kind of kludgy though.

Peso, could you point me out to some of the discussion on this behavior?

Thanks a lot!

Kristen
Test

22859 Posts

Posted - 2006-09-28 : 11:14:25

"I expect s1 to be 'aaabbbccc' but it prints as 'ccc'. "

Not here it doesn't (see above) but:

"This occurs on SQL Server 2005 developer edition."

Ah, only on SQL 2000 here ... this is presumably a known change?

"When I add "TOP 1000" to the select, it seems to work"

TOP 100 PERCENT might work (without the row limit restriction)

Kristen

mr_mist
Grunnio

1870 Posts

Posted - 2006-10-02 : 04:58:27

This is version dependant -

Sql 2000 -

s1: aaabbbccc
s2: aaabbbccc
s3: aaabbbccc
s4: aaabbbccc

Sql 2005 SP1 -

s1: ccc
s2: aaabbbccc
s3: aaabbbccc
s4: aaabbbccc

Dunno if there's maybe some option you can change to influence the behaviour.

-------
Moo. :)

Kristen
Test

22859 Posts

Posted - 2006-10-02 : 06:40:25

Is there a list of changes in behaviour, of this sort (i.e. SQL2000 -> SQL2005), that anyone can recommend pls?

Kristen

mr_mist
Grunnio

1870 Posts

Posted - 2006-10-02 : 06:46:37

There's a list of changes to system tables in BOL, but I think the other changes like this one you probably have to guess when your apps break :)

-------
Moo. :)

Kristen
Test

22859 Posts

Posted - 2006-10-02 : 09:00:57

"other changes like this one you probably have to guess when your apps break"

Surely some sad geek has blog-~~cat~~aloged them?

Kristen

lazerath
Constraint Violating Yak Guru

343 Posts

Posted - 2006-10-05 : 15:50:27

Sounds like something that could be compiled by the moderators of this forum as these things crop up?

As a note, if you look at the execution plans for S1, S2, S3 and S4 in SS2K5, you'll notice that S1 is different than the other two:

S1
Select <--- Sort <--- Compute Scalar <--- Clustered Index Scan

Versus Others

Select <--- Compute Scalar <--- [Sort] <--- Clustered Index Scan

In SS2K, all queries generate plans like:

Select <--- Compute Scalar <--- [Sort] <--- Clustered Index Scan

The question is why is this behavior occuring?

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-10-05 : 16:10:00

quote:
When I add "TOP 1000" to the select, it seems to work

Add TOP 100 PERCENT, so that you wont have to bother about maximum rows.

Harsh Athalye
India.
"Nothing is Impossible"

lazerath
Constraint Violating Yak Guru

343 Posts

Posted - 2006-10-05 : 16:12:32

On my system, TOP 100 PERCENT does not work as I still get ccc instead of aaabbbccc. When using TOP 1000, it functions as expected.

quote:
Originally posted by harsh_athalye

quote:
When I add "TOP 1000" to the select, it seems to work

Add TOP 100 PERCENT, so that you wont have to bother about maximum rows.

Harsh Athalye
India.
"Nothing is Impossible"

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-10-05 : 16:16:03

How come TOP 100 PERCENT not working??

Did you tried this?

DECLARE @s1 nvarchar(4000)
set @s1 = ''

SELECT @s1 = @s1 + z.StringValue 
FROM (select top 100 percent stringvalue from dbo.ATest 
	WHERE ForeignKeyID1=100
	ORDER BY DisplayOrder) z

print 's1: ' + @s1

Harsh Athalye
India.
"Nothing is Impossible"

lazerath
Constraint Violating Yak Guru

343 Posts

Posted - 2006-10-05 : 16:23:14

I found another interesting thing about this bug: if you add either of the following indexes:

create nonclustered index ix_#ATest_1 on #ATest (ForeignKeyID1,DisplayOrder)

or

create nonclustered index ix_#ATest_2 on #ATest (DisplayOrder)

it changes the execution plan enough so that the result is:

s1: aaabbbccc
s2: aaabbbccc
s3: aaabbbccc
s4: aaabbbccc

Therefore, it's possible that if your tables are properly indexed, you won't experience this issue.

lazerath
Constraint Violating Yak Guru

343 Posts

Posted - 2006-10-05 : 16:26:23

Harsh,

I did not use a derived table when testing TOP 100 PERCENT vs TOP n, which would explain the differences. Just using TOP n will work on the original @S1 code block.

quote:
Originally posted by harsh_athalye

How come TOP 100 PERCENT not working??

Did you tried this?

DECLARE @s1 nvarchar(4000)
set @s1 = ''

SELECT @s1 = @s1 + z.StringValue
FROM (select top 100 percent stringvalue from dbo.ATest
WHERE ForeignKeyID1=100
ORDER BY DisplayOrder) z

print 's1: ' + @s1

Harsh Athalye
India.
"Nothing is Impossible"

Kristen
Test

22859 Posts

Posted - 2006-10-06 : 00:16:58

"Therefore, it's possible that if your tables are properly indexed, you won't experience this issue."

That sounds really scary - what about if the statistics on the table / other indexes / etc. change in the future so that the query optimiser chooses a different query plan?

Kristen

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-10-11 : 14:46:34

The
select top 100 percent
in the subquery is removed by the compiler in the 2005.
that's why the order by doesn't work.
This is new behaviour in SS2k5

It's quite logical though. sets aren't supposed to be ordered until the very end anyway.
a workaround for this (if you really must have it) is to use
select top aVeryVeryLargeNumber from...

where aVeryVeryLargeNumber is something like 2^31-1 (max int)

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2006-10-11 : 18:03:57

I have found the same behavior on SQL2000.
One expects to get the whole string, but only part of it is returned.
Removing the ORDER BY results in the whole string being returned.

It's strange, and pretty scary.
The results of the query depend on the execution plan!

Not good, after this thread I may have to revisit that code and see what's happening...

Kristen
Test

22859 Posts

Posted - 2006-10-13 : 04:13:10

OK, now I'm worried Rocky. Have you got an SQL2k examples per-chance?

We've got functions that return "comma delimited lists of child records" all over the place.

Kristen

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2006-10-13 : 08:03:57

Ok, this is the example.
In our code it is of course much more complex with left joins and more tables involved.
(That is why we have the coalesce - left join)
If you don't have the coalesce, it works ok.
If you examine the example, you can see 3 different execution plans.

set nocount on

create table #x(s char(2), y smallint)
create table #y(y smallint)

insert #x select 'a', 10 union select 'b', 20 union select 'c', 10
insert #y select 10 union select 20

select * from #x
select * from #y

declare @string varchar(10)
	,@string2 varchar(10)
	,@string3 varchar(10)

select	@string =  case when #y.y in(10)
			then coalesce(@string + ', ', '') + coalesce(#x.s, #x.s)
			else @string end
from	#x join #y
		on #x.y = #y.y
--order by coalesce(#x.s, #x.s)

select	@string2 =  case when #y.y in(10)
			then coalesce(@string2 + ', ', '') + coalesce(#x.s, #x.s)
			else @string2 end
from	#x join #y
		on #x.y = #y.y
order by coalesce(#x.s, #x.s)

select	@string3 =  case when #y.y in(10)
			then coalesce(@string3 + ', ', '') + coalesce(#x.s, #x.s)
			else @string3 end
from	#x join #y
		on #x.y = #y.y
order by #x.s

select @string as noOrder, @string2 as OrderByCoalesce, @string3 as OrderByNormal

drop table #x,#y

--========================================================================================

s    y      
---- ------ 
a    10
b    20
c    10

y      
------ 
10
20

noOrder    OrderByCoalesce OrderByNormal 
---------- --------------- ------------- 
a , c      c               a , c

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources