Is this a bug?
-
create table tTst
(
id int not null,
tst varchar(20)
)
insert into tTst values(1, 'bob')
insert into tTst values(1, 'fred')
insert into tTst values(2, 'Jane')
GO
Declare @tst varchar(20)
-- Statement 1 this works Just picks one from bob and fred
SELECT @tst = tst from tTst where id =1
-- Statement 2 I would expect same error as where I have to use MAX/MIN()
SET @tst = (SELECT tst from tTst where id =1)
Statement 1 works in 2005/2008 I was expecting to get same error as in Statement 2.
But it just picks the last one.
-
Nope... not a bug. The variable in example 1 will contain the last value produced by the query. Although not a good example of how to calculate a Factorial, calculating Factorials make a good example for how the code can work.
DECLARE @Factorial FLOAT,@I INT;
SELECT @Factorial = 1,
@I = 1;
WHILE @I < = 170
SELECT @Factorial = @Factorial * @I,
@I = @I + 1;
SELECT @Factorial;
Of course, there's a much simpler way to calculate Factorials but I wanted to show you one possible application of what you were thinking was a bug. Every decent language I know of allows you to overwrite a variable and make self assignments in a loop.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Thanks but I stil lthink its wrong/misleading and can hide errors.
The factorial example works as I would expect it to. Its procedural code working on variables. Just doing a one value assignment for each iteration.
I came across this code in a UDF where the author was selecting on a non-PK field and expecting only one value to come back. I thought
SELECT @tst = tst from tTst where id =1
would fail just like this would:
SET @tst = (SELECT tst from tTst where id =1)
I think it is correct behaviour to fail when assigning a set of values to a single variable. Otherwise SQL server is just picking one of them and the Author of the UDF intended it to bring back only one record.
Update tbl1 set tbl.Field1 = tbl2.field2
from tbl2
where tbl1.id =tbl2.id
Would fail when there are multiples. The Server refuses to pick one value for tbl2.field2 at random.
when tbl1.id =tbl2.id is in a one to many relationship.
-
terryshamir (3/14/2012)
Update tbl1 set tbl.Field1 = tbl2.field2from tbl2
where tbl1.id =tbl2.id
Would fail when there are multiples. The Server refuses to pick one value for tbl2.field2 at random.
when tbl1.id =tbl2.id is in a one to many relationship.
That will work fine no matter how many duplicates there are. Whether you get what you expect is another matter
CREATE TABLE tbl1 (Id INT,
Field1 varchar(10)
)
CREATE TABLE tbl2 (
Id INT,
Field1 varchar(10)
)
INSERT INTO tbl1
VALUES (1,'abc'),(2,'def')
INSERT INTO tbl2
VALUES (1,'a'),(1,'b'), (1,'c')
Update tbl1 set tbl1.Field1 = tbl2.field1
from tbl2
where tbl1.id =tbl2.id
SELECT * FROM tbl1
1a2def
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
terryshamir (3/14/2012)
Thanks but I stil lthink its wrong/misleading and can hide errors.Heh... so can an improperly written join. It's the same as driving a car... if you don't pay attention, you will get into a wreck. 😉
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Thanks Gila
I tried the update and it did work. I'm certain I have had to change queries before to do
set tbl1.field1 = MAX(tbl2.field1)
I'm more confused now..
-
This will give you an error:
Update tbl1set tbl1.Field1 = (SELECT tbl2.field1 from tbl2 where tbl1.id = tbl2.id)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
CELKO (3/14/2012)
This is a historical bug that goes back to the original Sybase days. Basically, the first versions of T-SQL was built on contiguous physical storage files and loops; not Set-Oriented at all.SQL has always used SELECT.. FROM for a query, but since T-SQL was semi-procedural, they needed an assignment. They overloaded SELECT! Unfortunately, the loop was still there. The final physical read loads the target of the assignment. That is pretty random! Change an index, add & delete a row, change a query, etc and you get a different row at the end of the loop.
This same design flaw is also hidden in the proprietary UPDATE..FROM statement, too. Good programmers use the MERGE statement instead because it catch the cardinality errors.
The SET statement is ANSI Standard and as you found out, it catches the cardinality bug. But as usual MS is years behind the specs. The full version allows row assignment:
SET (<target list>) = (<row constructor>);
for example:
SET x,y,z = (1,2,3);
SET x,y,z = (SELECT MAX(a), MAX(b), MAX(c) FROM Foo);
There's a lot that can be done incorrectly. It's up to the developer to learn how to use the tools properly. Some of the things you're calling "faults" are actually "features" to me. Yes, I know your famous thoughts on protecting people who don't know the tools they're being paid to use. If you make something "idiot proof", only idiots will use it. 😉
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
A badly written join will produce more results normally than expected, a cartesian product it behaves correctly. Set based stuff thats what SQL is about.
This is a bug, that could be quite hard to track down.
Thanks Gila - I must of being using a subquery.
I've worked with file DB's (cannot remember VFP or paradox) that allowed you to do
select fname, lname
from tblpeople
group by lname
For me its a bug, the guys who worked with it saw nothing wrong with it.
-
It's not a bug, in that it is expected and documented behaviour. A bug would be where SQL does something other than what it is supposed to do.
Could it cause a bug in your code if you aren't familiar with how the update will behave, sure, but there are tonnes of things that trip people up just because they're not familiar with the details.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Good programmers use the MERGE statement instead because it catch the cardinality errors.
-
This is a historical bug that goes back to the original Sybase days
-
evelinel1f4r (3/14/2012)
Good programmers use the MERGE statement instead because it catch the cardinality errors.Why do you people say such insulting things? The are a ton of great programmers that have never used MERGE.
Also, if you're going to judge, are you a good programmer? Do you know about the bug that MERGE has with OUTPUT? Do you know which versions won't be fixed for the fault? Do you know how to get around the fault?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
evelinel1f4r (3/14/2012)
This is a historical bug that goes back to the original Sybase daysIt's not a "bug". It's a bloody feature! 😉 Ya just gotta know how to use it and when.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
terryshamir (3/14/2012)
For me its a bug, the guys who worked with it saw nothing wrong with it.--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply