July 30, 2010 at 6:36 am
Testing for new features of SQL2008 R2 I stumbled on a problem with merge.
When I use only table variable objects with the merge statement, the statement fails.
Print 'This one will succeed.'
CREATE TABLE #Stock (StockName VARCHAR(10) PRIMARY KEY, Qty INT CHECK (Qty > 0));
DECLARE @Trades table (StockName VARCHAR(10) PRIMARY KEY, Delta INT CHECK (Delta <> 0));
DECLARE @AuditChanges table (Action varchar(6), StockName VARCHAR(6), Qty INT, OLD_Qty INT);
INSERT #Stock VALUES('MSFT', 10), ('BOEING', 5);
INSERT @Trades VALUES('MSFT', 5), ('BOEING', -5), ('GE', 3);
MERGE #Stock S
USING @Trades T
ON S.StockName = T.StockName
WHEN MATCHED AND (Qty + T.Delta = 0) THEN
DELETE
WHEN MATCHED THEN
UPDATE SET Qty += T.Delta
WHEN NOT MATCHED THEN
INSERT VALUES(StockName, T.Delta)
OUTPUT $action, T.StockName, inserted.Qty , deleted.Qty
INTO @AuditChanges
;
select *
from @AuditChanges
order by 1;
drop table #Stock ;
go
Print 'This one will fail !' ;
DECLARE @Stock table (StockName VARCHAR(10) PRIMARY KEY, Qty INT CHECK (Qty > 0));
DECLARE @Trades table (StockName VARCHAR(10) PRIMARY KEY, Delta INT CHECK (Delta <> 0));
DECLARE @AuditChanges table (Action varchar(6), StockName VARCHAR(6), Qty INT, OLD_Qty INT);
INSERT @Stock VALUES('MSFT', 10), ('BOEING', 5);
INSERT @Trades VALUES('MSFT', 5), ('BOEING', -5), ('GE', 3);
MERGE @Stock S
USING @Trades T
ON S.StockName = T.StockName
WHEN MATCHED AND (S.Qty + T.Delta = 0) THEN
DELETE
WHEN MATCHED THEN
UPDATE SET Qty += T.Delta
WHEN NOT MATCHED THEN
INSERT VALUES(StockName, T.Delta)
OUTPUT $action, T.StockName, inserted.Qty , deleted.Qty
INTO @AuditChanges
;
select *
from @AuditChanges
order by 1;
Can anyone confirm this bug ?
I filed this at connect : https://connect.microsoft.com/SQLServer/feedback/details/581548/sql2008-r2-merge-statement-with-only-table-variables-fails
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
July 30, 2010 at 9:36 am
Confirmed:
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
Oddly if you remove the primary key constraint from either declared table it works fine...
Edit: Actually if you specify PRIMARY KEY NONCLUSTERED it works as well
August 2, 2010 at 12:46 am
Thank you for your feedback.
Strange indeed that removing the pk would "solve" the issue.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
August 4, 2010 at 12:01 am
Very interesting. Blogged about it.
http://sqlblog.com/blogs/paul_white/archive/2010/08/04/another-interesting-merge-bug.aspx
August 4, 2010 at 3:35 am
I couldn't have done that better 😎
Very, very nice plan and bug analysis.
Who am I ? Sometimes this is me[/url] :alien: and most of the time this is me :hehe:
Johan Bijnens
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
November 5, 2010 at 12:58 am
I received feedback from MS ....
...
The problem is that we were trying to apply a particular optimization that doesn't work with table variables. The bug has been fixed for the next release.
You can work around the issue by disabling the unique key constraint on your table variable; that will disable the faulty optimization.
If that is not acceptable, and you need the full fix now, please contact customer support.
...
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
November 5, 2010 at 4:56 am
ALZDBA (11/5/2010)
I received feedback from MS ....
Wouldn't it be nice if MSFT put a bit more effort into their replies on Connect?
Just confirming the bug is nice of course, but a bit more information would have been great.
November 5, 2010 at 4:59 am
" and you need the full fix now, please contact customer support".
Sounds like it is fixed, but in a hotfix. Might be worth taking them up on this offer 😉
November 5, 2010 at 5:37 am
Paul White NZ (11/5/2010)
ALZDBA (11/5/2010)
I received feedback from MS ....Wouldn't it be nice if MSFT put a bit more effort into their replies on Connect?
Just confirming the bug is nice of course, but a bit more information would have been great.
Maybe next week .... @ Pass 😀
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply