[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.sqlserver.programming

BUG 2005 ?

Mario

3/21/2007 7:34:00 PM

Hi ,
I need same help. I have this query....


declare @local_variable varchar (MAX)
-- BAD !!!
Set @local_variable = ''
Select @local_variable = @local_variable + V.type
from master.dbo.spt_values V
order by 1
select Len ( @local_variable )


-- Fine !!
Set @local_variable = ''
Select @local_variable = @local_variable + V.type
from master.dbo.spt_values V
order by V.type
select Len ( @local_variable )

Why in BAD, kill the @local_variable ?
Thxs.
Mario
8 Answers

David Portas

3/21/2007 7:59:00 PM

0

On 21 Mar, 19:33, "Mario" <sux...@hotmail.com> wrote:
> Hi ,
> I need same help. I have this query....
>
> declare @local_variable varchar (MAX)
> -- BAD !!!
> Set @local_variable = ''
> Select @local_variable = @local_variable + V.type
> from master.dbo.spt_values V
> order by 1
> select Len ( @local_variable )
>
> -- Fine !!
> Set @local_variable = ''
> Select @local_variable = @local_variable + V.type
> from master.dbo.spt_values V
> order by V.type
> select Len ( @local_variable )
>
> Why in BAD, kill the @local_variable ?
> Thxs.
> Mario

Don't do that. From Books Online:

"SELECT @local_variable is typically used to return a single value
into the variable. However, when expression is the name of a column,
it can return multiple values. If the SELECT statement returns more
than one value, the variable is assigned the last value that is
returned."

So arguably only one single assignment should take place and
effectively the result of your queries is undefinded. So this is not
really a bug, it's just not a good idea. In my view this multiple-row
assignment syntax ought to be illegal.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/librar...(en-US,SQL.90).aspx
--

Alejandro Mesa

3/21/2007 8:05:00 PM

0

Hi Mario,

What is the meaning of "order by 1" in the statement yielding the wrong
results?

We use a number in the "order by" clause (not a good practice) to indicate
to SQL Server to sort the result based on the column in that position on the
resultset and the statement you are talking about is used for assignment and
not to give a resultset.

May be this applies to this case.

PRB: Execution Plan and Results of Aggregate Concatenation Queries Depend
Upon Expression Location
http://support.microsoft.com/kb/28...

Also see,

Concatenating row values in Transact-SQL
http://www.projectdmx.com/tsql/rowconcat...


AMB


"Mario" wrote:

> Hi ,
> I need same help. I have this query....
>
>
> declare @local_variable varchar (MAX)
> -- BAD !!!
> Set @local_variable = ''
> Select @local_variable = @local_variable + V.type
> from master.dbo.spt_values V
> order by 1
> select Len ( @local_variable )
>
>
> -- Fine !!
> Set @local_variable = ''
> Select @local_variable = @local_variable + V.type
> from master.dbo.spt_values V
> order by V.type
> select Len ( @local_variable )
>
> Why in BAD, kill the @local_variable ?
> Thxs

Hugo Kornelis

3/21/2007 8:20:00 PM

0

On Wed, 21 Mar 2007 16:33:47 -0300, Mario wrote:

> Hi ,
> I need same help. I have this query....
>
>
>declare @local_variable varchar (MAX)
>-- BAD !!!
>Set @local_variable = ''
>Select @local_variable = @local_variable + V.type
>from master.dbo.spt_values V
>order by 1
>select Len ( @local_variable )
>
>
>-- Fine !!
>Set @local_variable = ''
>Select @local_variable = @local_variable + V.type
>from master.dbo.spt_values V
>order by V.type
>select Len ( @local_variable )
>
> Why in BAD, kill the @local_variable ?

Hi Mario,

This construction is undocumented, unsupported and unreliable. As your
example proves :-)

The result of this query is highly dependant on the execution plan.
Obviously, the different ORDER BY in the queries cause different
execution plans to be used.

Especially the ORDER BY 1 is of course intrigueing. It says to first
concatenate the strings, then order by the concatenated string - like a
cat trying to bite it's own tail :-) I'm not at all surprised to see
SQL Server getting confused by this - I am!!

Checking the execution plan, I _think_ that SQL Server first computes
@local_variable + V.type for each row, without assigning it yet; then
sorts all these intermediate results, and ends with the assignment, so
@local_variable ends up as '' (the original value of @local_variable) +
'V' (the alphabetically last V.type in the input table).

And to come back to your question: since this construction is not
documented or supported, the result you got can't possibly be a bug.

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hug...

Steve Dassin

3/21/2007 11:13:00 PM

0

Why don't you put someone in the know from MS, under oath, and ask
them if they use this 'undocumented, unsupported and unreliable' construct
anywhere?

Hugo Kornelis

3/21/2007 11:30:00 PM

0

On Wed, 21 Mar 2007 16:12:32 -0700, Steve Dassin wrote:

>Why don't you put someone in the know from MS, under oath, and ask
>them if they use this 'undocumented, unsupported and unreliable' construct
>anywhere?
>

Hi Steve,

With the hundreds of thousands MS employees out there, I'd be very
surprised if none of them would use this. I just hope it's not someone
from the SQL team, and not in any serious, production software.

But if I'm wrong ... it's there problem, not mine! I'll just file a bug
report if I'm the unlucky customer that gets bitten.

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hug...

Steve Dassin

3/21/2007 11:52:00 PM

0

Hello Hugo,

Okay, something a little more realistic.
Ask them why kb articles are unsigned. Perhaps you can usher in an age
of accountability.
And I want to know 'who' wrote the original Q287515.
And who reviewed it (kb/287515).
Think of it as playing truth or dare! -:)

best,
\steve
"Hugo Kornelis" <hugo@perFact.REMOVETHIS.info.INVALID> wrote in message
news:fsf303t5gqv6fjsm9j6v47ocp7tvmfmjch@4ax.com...
> On Wed, 21 Mar 2007 16:12:32 -0700, Steve Dassin wrote:
>
>>Why don't you put someone in the know from MS, under oath, and ask
>>them if they use this 'undocumented, unsupported and unreliable' construct
>>anywhere?
>>
>
> Hi Steve,
>
> With the hundreds of thousands MS employees out there, I'd be very
> surprised if none of them would use this. I just hope it's not someone
> from the SQL team, and not in any serious, production software.
>
> But if I'm wrong ... it's there problem, not mine! I'll just file a bug
> report if I'm the unlucky customer that gets bitten.
>
> --
> Hugo Kornelis, SQL Server MVP
> My SQL Server blog: http://sqlblog.com/blogs/hug...


Hugo Kornelis

3/23/2007 11:31:00 PM

0

On Wed, 21 Mar 2007 16:51:46 -0700, Steve Dassin wrote:

>Hello Hugo,
>
>Okay, something a little more realistic.
>Ask them why kb articles are unsigned. Perhaps you can usher in an age
>of accountability.
>And I want to know 'who' wrote the original Q287515.
>And who reviewed it (kb/287515).
>Think of it as playing truth or dare! -:)

Hi Steve,

"The correct behavior for an aggregate concatenation query is undefined"

What exactly is your problem with this KB article?

As to signing KB articles - once MS decides to publish an article, it is
a corporate responsibility, not that of the individual author writing
it. That's how companies work.

(Just think aboout it - if an individual employee would have to take the
blame for things that go wrong, shouldn't they also have to get the
profits from things done right? Not a good option for MS shareholders!)

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hug...

Steve Dassin

3/24/2007 3:14:00 AM

0

Hello Hugo,

"Hugo Kornelis" <hugo@perFact.REMOVETHIS.info.INVALID> wrote in message
news:plo803tvc0nkk79e50klemojqtvfg7t5j8@4ax.com...
> Hi Steve,
>
> "The correct behavior for an aggregate concatenation query is undefined"
> What exactly is your problem with this KB article?

Just a pet peeve of mine. The kb is complete jibberish, a
figment of someone's imagination.

> As to signing KB articles - once MS decides to publish an article, it is
> a corporate responsibility, not that of the individual author writing
> it. That's how companies work.
>
> (Just think aboout it - if an individual employee would have to take the
> blame for things that go wrong, shouldn't they also have to get the
> profits from things done right? ....)

That's interesting, IBM has theirs signed. MS vs. IBM..
seems MS is a day late and a letter short.

And I know that people at MS do take responsibility. But
just as you find out the who you no longer know the where.

> Not a good option for MS shareholders!

Sorry, as of Jan 31 they are shamholders!

best,
steve