Bug in SQL2k5?

Hi
My coworker found out a bug in SQL2k5.
Try this simple code:

CREATE TABLE #TAB (MY_FIELD varchar(10))
INSERT INTO #TAB VALUES ('test1')
INSERT INTO #TAB VALUES ('test2')

DECLARE @val VARCHAR(2000)
SELECT MY_FIELD FROM #TAB
--1
SET @val=''
SELECT @val=@val+LTRIM(RTRIM(MY_FIELD))
FROM #TAB
SELECT @val
--2
SET @val=''
SELECT @val=@val+LTRIM(RTRIM(MY_FIELD))
FROM #TAB
ORDER BY 1 --!!!!
SELECT @val

DROP TABLE #TAB

Results of 1 and 2 are different!
1: test1test2
2: test2

Tested on:
SQL2000 - no bug
SQL2k5 (9.00.3042.00 (Intel X86) Feb 9 2007 22:47:07) - bug exists
SQL2k5 (9.00.3239.00 (Intel X86) Apr 9 2008 22:56:02) - bug exists

I don't know whether the bug is already known.
Is there a patch for it?
--
Regards
Miroo

Miroo
I avoid doing such things by using T-SQL especially using ORDER BY clause as
it is unreliabe.
In your example change 1 to MY_FIELD and see what is going on.

"Miroo" wrote in message
news:g966ke$4tp$1-at-nemesis.news.neostrada.pl...
> Hi
> My coworker found out a bug in SQL2k5.
> Try this simple code:
>
> CREATE TABLE #TAB (MY_FIELD varchar(10))
> INSERT INTO #TAB VALUES ('test1')
> INSERT INTO #TAB VALUES ('test2')
>
> DECLARE @val VARCHAR(2000)
> SELECT MY_FIELD FROM #TAB
> --1
> SET @val=''
> SELECT @val=@val+LTRIM(RTRIM(MY_FIELD))
> FROM #TAB
> SELECT @val
> --2
> SET @val=''
> SELECT @val=@val+LTRIM(RTRIM(MY_FIELD))
> FROM #TAB
> ORDER BY 1 --!!!!
> SELECT @val
>
> DROP TABLE #TAB
>
> Results of 1 and 2 are different!
> 1: test1test2
> 2: test2
>
> Tested on:
> SQL2000 - no bug
> SQL2k5 (9.00.3042.00 (Intel X86) Feb 9 2007 22:47:07) - bug exists
> SQL2k5 (9.00.3239.00 (Intel X86) Apr 9 2008 22:56:02) - bug exists
>
> I don't know whether the bug is already known.
> Is there a patch for it?
> --
> Regards
> Miroo

Isn't SELECT @var = @var + ... an unsupported syntax?

--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net


"Miroo" wrote in message
news:g966ke$4tp$1-at-nemesis.news.neostrada.pl...
> Hi
> My coworker found out a bug in SQL2k5.
> Try this simple code:
>
> CREATE TABLE #TAB (MY_FIELD varchar(10))
> INSERT INTO #TAB VALUES ('test1')
> INSERT INTO #TAB VALUES ('test2')
>
> DECLARE @val VARCHAR(2000)
> SELECT MY_FIELD FROM #TAB
> --1
> SET @val=''
> SELECT @val=@val+LTRIM(RTRIM(MY_FIELD))
> FROM #TAB
> SELECT @val
> --2
> SET @val=''
> SELECT @val=@val+LTRIM(RTRIM(MY_FIELD))
> FROM #TAB
> ORDER BY 1 --!!!!
> SELECT @val
>
> DROP TABLE #TAB
>
> Results of 1 and 2 are different!
> 1: test1test2
> 2: test2
>
> Tested on:
> SQL2000 - no bug
> SQL2k5 (9.00.3042.00 (Intel X86) Feb 9 2007 22:47:07) - bug exists
> SQL2k5 (9.00.3239.00 (Intel X86) Apr 9 2008 22:56:02) - bug exists
>
> I don't know whether the bug is already known.
> Is there a patch for it?
> --
> Regards
> Miroo

There is even a more curious issue here in that it only seems to be ordering
by a NUMBER that causes the 'data loss':


CREATE TABLE #TAB (MY_FIELD varchar(10), a int)
INSERT INTO #TAB VALUES ('test1', 1)
INSERT INTO #TAB VALUES ('test2',2)
INSERT INTO #TAB VALUES ('test3',3)

DECLARE @val VARCHAR(2000)
SELECT MY_FIELD FROM #TAB
--1
SET @val=''
SELECT @val=@val+LTRIM(RTRIM(MY_FIELD))
FROM #TAB
SELECT @val --correct
--2
SET @val=''
SELECT @val=@val+LTRIM(RTRIM(MY_FIELD))
FROM #TAB
ORDER BY 1 --WRONG
SELECT @val
--3
SET @val=''
SELECT @val=@val+LTRIM(RTRIM(MY_FIELD))
FROM #TAB
ORDER BY my_field --correct
SELECT @val
--4
SET @val=''
SELECT @val=@val+LTRIM(RTRIM(MY_FIELD))
FROM #TAB
ORDER BY a --correct
SELECT @val

DROP TABLE #TAB

very curious



--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net


"Miroo" wrote in message
news:g966ke$4tp$1-at-nemesis.news.neostrada.pl...
> Hi
> My coworker found out a bug in SQL2k5.
> Try this simple code:
>
> CREATE TABLE #TAB (MY_FIELD varchar(10))
> INSERT INTO #TAB VALUES ('test1')
> INSERT INTO #TAB VALUES ('test2')
>
> DECLARE @val VARCHAR(2000)
> SELECT MY_FIELD FROM #TAB
> --1
> SET @val=''
> SELECT @val=@val+LTRIM(RTRIM(MY_FIELD))
> FROM #TAB
> SELECT @val
> --2
> SET @val=''
> SELECT @val=@val+LTRIM(RTRIM(MY_FIELD))
> FROM #TAB
> ORDER BY 1 --!!!!
> SELECT @val
>
> DROP TABLE #TAB
>
> Results of 1 and 2 are different!
> 1: test1test2
> 2: test2
>
> Tested on:
> SQL2000 - no bug
> SQL2k5 (9.00.3042.00 (Intel X86) Feb 9 2007 22:47:07) - bug exists
> SQL2k5 (9.00.3239.00 (Intel X86) Apr 9 2008 22:56:02) - bug exists
>
> I don't know whether the bug is already known.
> Is there a patch for it?
> --
> Regards
> Miroo

Kevin (or anyone else),

Can you please give me a link to an article that confirms that its
unsupported? I was unaware that there was anything wrong with that
statement.

String concatenation in SQL clearly isn't a GOOD thing... but I didn't
realize it was "unsupported"....

Thanks,

Jason




"TheSQLGuru" wrote in message
news:NtydnaHpq5dKOSvVnZ2dnUVZ_s_inZ2d-at-earthlink.co m...
> Isn't SELECT @var = @var + ... an unsupported syntax?
>
> --
> Kevin G. Boles
> Indicium Resources, Inc.
> SQL Server MVP
> kgboles a earthlink dt net
>
>
> "Miroo" wrote in message
> news:g966ke$4tp$1-at-nemesis.news.neostrada.pl...
>> Hi
>> My coworker found out a bug in SQL2k5.
>> Try this simple code:
>>
>> CREATE TABLE #TAB (MY_FIELD varchar(10))
>> INSERT INTO #TAB VALUES ('test1')
>> INSERT INTO #TAB VALUES ('test2')
>>
>> DECLARE @val VARCHAR(2000)
>> SELECT MY_FIELD FROM #TAB
>> --1
>> SET @val=''
>> SELECT @val=@val+LTRIM(RTRIM(MY_FIELD))
>> FROM #TAB
>> SELECT @val
>> --2
>> SET @val=''
>> SELECT @val=@val+LTRIM(RTRIM(MY_FIELD))
>> FROM #TAB
>> ORDER BY 1 --!!!!
>> SELECT @val
>>
>> DROP TABLE #TAB
>>
>> Results of 1 and 2 are different!
>> 1: test1test2
>> 2: test2
>>
>> Tested on:
>> SQL2000 - no bug
>> SQL2k5 (9.00.3042.00 (Intel X86) Feb 9 2007 22:47:07) - bug exists
>> SQL2k5 (9.00.3239.00 (Intel X86) Apr 9 2008 22:56:02) - bug exists
>>
>> I don't know whether the bug is already known.
>> Is there a patch for it?
>> --
>> Regards
>> Miroo
>
>

This is not a bug, but rather using a non reliable approach to
concatenate using ORDER BY. See the following KB article which relates
to the problem:
http://support.microsoft.com/default.aspx/kb/287515

Also, when you specify an integer order by expression, it represents the
position of the name or alias in the select list, and in your example
you do not have a column, but a variable.

--
Plamen Ratchev
http://www.SQLStudio.com

> Can you please give me a link to an article that confirms that its
> unsupported?

This is like asking for proof that Bigfoot doesn't exist. Usually for this
kind of thing you only rely on behavior which is documented, so I'll counter
with, can you show documentation that confirms it IS supported (e.g. from
Microsoft documentation)? Particularly the part about ordering. It is
fairly common knowledge that this ordering is unsupported and unreliable,
but I don't think you will find a Microsoft document that states, "don't do
this, it is unsupported!" There are plenty of better solutions than what
you are using anyway... personally I like the XML method shown here:

http://www.aspfaq.com/2529

Maybe have a look at this article for a better variety of options:

http://www.projectdmx.com/tsql/rowconcatenate.aspx

"TheSQLGuru" wrote in message
news:u5idnSHJq_mxNSvVnZ2dnUVZ_jmdnZ2d-at-earthlink.co m...
>. very curious
>

This should clear things up:

DECLARE @val VARCHAR(2000)
SET @val=''
SELECT @val=@val+LTRIM(RTRIM(MY_FIELD))
FROM #TAB
ORDER BY @val --!!!!
SELECT @val

..Net SqlClient Data Provider: Msg 1008, Level 16, State 1, Line 3
The SELECT item identified by the ORDER BY number 1 contains a variable as
part of the expression identifying a column position. Variables are only
allowed when ordering by an expression referencing a column name.

This is all a game of tickle, to get the optimizer to sort before computing
the scalar. Think of it as xbox sql

www.beyondsql.blogspot.com

Jason Folkens wrote:
> Can you please give me a link to an article that confirms that its
> unsupported? I was unaware that there was anything wrong with that
> statement.

See: http://support.microsoft.com/kb/287515
The KB article says:
"The correct behavior for an aggregate concatenation query is undefined."

--
Razvan Socol
SQL Server MVP

Regarding kb/287515 I think you'll find these two threads very interesting
reads:

microsoft.public.sqlserver.programming
Date: 2003-03-07
Subject: transpose problem
http://tinyurl.com/69lzz
Especially the replies by Hollinshead [MSFT].

microsoft.public.sqlserver.programming
Jan 23 2003
Undocumented operation
http://tinyurl.com/6jprz9
Especially the reply by Hal Berenson

I think they're still working on updating that kb (perhaps in 2011.

best,
steve aka pele

www.beyondsql.blogspot.com

"Razvan Socol" wrote in message
news:%23RBXu3ZCJHA.3668-at-TK2MSFTNGP05.phx.gbl...
> Jason Folkens wrote:
>> Can you please give me a link to an article that confirms that its
>> unsupported? I was unaware that there was anything wrong with that
>> statement.
>
> See: http://support.microsoft.com/kb/287515
> The KB article says:
> "The correct behavior for an aggregate concatenation query is undefined."
>