BUG or not?

I have a case when the same query returns two different results, depending
on the generated exec plan. I managed to reproduce it on different servers
and databases.
Heres the code, the trigger should generate new number max(Broj)+1 but the
result here is 1 because join in the selec max() part returns empty result
set.
If you substitute part in the join on n.prefix = i.prefix with n.prefix =
convert(varchar(10), i.prefix) then the trigger works.
Notice that the convert function doesnt actually do anything, its there as
an example, feel free to use any method to force different exec plan.
if exists(select * from sysobjects where xtype='U' and name='tbl')
drop table tbl
go
create table tbl (
[id] int identity(1,1) not null primary key clustered,
prefix varchar(10) not null,
broj int null
)
go
insert tbl (prefix, broj) values( '2006/01-', 54)
insert tbl (prefix, broj) values( '2006/01-', 53)
insert tbl (prefix, broj) values( '2006/01-', 52)
insert tbl (prefix, broj) values( '2006/01-', 51)
insert tbl (prefix, broj) values( '2006/01-', 50)
insert tbl (prefix, broj) values( '2006/01-', 49)
insert tbl (prefix, broj) values( '2006/01-', 48)
insert tbl (prefix, broj) values( '2006/01-', 47)
insert tbl (prefix, broj) values( '2006/01-', 46)
insert tbl (prefix, broj) values( '2006/01-', 45)
insert tbl (prefix, broj) values( '2006/01-', 44)
insert tbl (prefix, broj) values( '2006/01-', 43)
insert tbl (prefix, broj) values( '2006/01-', 42)
insert tbl (prefix, broj) values( '2006/01-', 41)
insert tbl (prefix, broj) values( '2006/01-', 40)
insert tbl (prefix, broj) values( '2006/01-', 39)
insert tbl (prefix, broj) values( '2006/01-', 38)
insert tbl (prefix, broj) values( '2006/01-', 37)
insert tbl (prefix, broj) values( '2006/01-', 36)
go
if exists(select * from sysobjects where xtype='TR' and name='tr_tbl_ins')
drop trigger tr_tbl_ins
go
create trigger tr_tbl_ins on tbl
for insert
as
update tbl
set
broj = coalesce((select max(n.broj) from tbl n inner join inserted i on
n.prefix = i.prefix),0)+1
where
[id] = (select [id] from inserted)
go
insert into tbl (prefix)
values ('2006/01-')
select * from tbl order by broj
-- New broj should be max(Broj)+1 but its 1 since the select max(n.broj)
returns NULL valueForgot to add, its for SQL Server 2005, SQL Server 2000 works as
expected...
Marko
"MC" <marko_culo#@.#yahoo#.#com#> wrote in message
news:uqJ6IuJVGHA.736@.TK2MSFTNGP12.phx.gbl...
>I have a case when the same query returns two different results, depending
>on the generated exec plan. I managed to reproduce it on different servers
>and databases.
> Heres the code, the trigger should generate new number max(Broj)+1 but the
> result here is 1 because join in the selec max() part returns empty result
> set.
> If you substitute part in the join on n.prefix = i.prefix with n.prefix =
> convert(varchar(10), i.prefix) then the trigger works.
> Notice that the convert function doesnt actually do anything, its there as
> an example, feel free to use any method to force different exec plan.
>
> if exists(select * from sysobjects where xtype='U' and name='tbl')
> drop table tbl
> go
> create table tbl (
> [id] int identity(1,1) not null primary key clustered,
> prefix varchar(10) not null,
> broj int null
> )
> go
> insert tbl (prefix, broj) values( '2006/01-', 54)
> insert tbl (prefix, broj) values( '2006/01-', 53)
> insert tbl (prefix, broj) values( '2006/01-', 52)
> insert tbl (prefix, broj) values( '2006/01-', 51)
> insert tbl (prefix, broj) values( '2006/01-', 50)
> insert tbl (prefix, broj) values( '2006/01-', 49)
> insert tbl (prefix, broj) values( '2006/01-', 48)
> insert tbl (prefix, broj) values( '2006/01-', 47)
> insert tbl (prefix, broj) values( '2006/01-', 46)
> insert tbl (prefix, broj) values( '2006/01-', 45)
> insert tbl (prefix, broj) values( '2006/01-', 44)
> insert tbl (prefix, broj) values( '2006/01-', 43)
> insert tbl (prefix, broj) values( '2006/01-', 42)
> insert tbl (prefix, broj) values( '2006/01-', 41)
> insert tbl (prefix, broj) values( '2006/01-', 40)
> insert tbl (prefix, broj) values( '2006/01-', 39)
> insert tbl (prefix, broj) values( '2006/01-', 38)
> insert tbl (prefix, broj) values( '2006/01-', 37)
> insert tbl (prefix, broj) values( '2006/01-', 36)
> go
> if exists(select * from sysobjects where xtype='TR' and name='tr_tbl_ins')
> drop trigger tr_tbl_ins
> go
> create trigger tr_tbl_ins on tbl
> for insert
> as
> update tbl
> set
> broj = coalesce((select max(n.broj) from tbl n inner join inserted i on
> n.prefix = i.prefix),0)+1
> where
> [id] = (select [id] from inserted)
> go
>
> insert into tbl (prefix)
> values ('2006/01-')
> select * from tbl order by broj
> -- New broj should be max(Broj)+1 but its 1 since the select max(n.broj)
> returns NULL value
>|||It sure looks like a bug to me.
The UPDATE in the trigger can be simplified somewhat. There is no
need to include another instance of tbl in the subquery, it can be
written as a corellated subquery. This version did not have the
problem:
update tbl
set
broj = coalesce((select max(n.broj) from tbl n
where n.prefix = tbl.prefix),0)+1
where
[id] = (select [id] from inserted)
Of course you must already realize that the trigger will provide
duplicate values if more than one row is INSERTED at a time. This is
considered a vulnerability, and I think the trigger should have a test
that enforces that there is only one row in INSERTED.
Roy Harvey
Beacon Falls, CT
On Fri, 31 Mar 2006 10:31:01 +0200, "MC" <marko_culo#@.#yahoo#.#com#>
wrote:

>I have a case when the same query returns two different results, depending
>on the generated exec plan. I managed to reproduce it on different servers
>and databases.
>Heres the code, the trigger should generate new number max(Broj)+1 but the
>result here is 1 because join in the selec max() part returns empty result
>set.
>If you substitute part in the join on n.prefix = i.prefix with n.prefix =
>convert(varchar(10), i.prefix) then the trigger works.
>Notice that the convert function doesnt actually do anything, its there as
>an example, feel free to use any method to force different exec plan.
>
>if exists(select * from sysobjects where xtype='U' and name='tbl')
> drop table tbl
>go
>create table tbl (
> [id] int identity(1,1) not null primary key clustered,
> prefix varchar(10) not null,
> broj int null
> )
>go
>insert tbl (prefix, broj) values( '2006/01-', 54)
>insert tbl (prefix, broj) values( '2006/01-', 53)
>insert tbl (prefix, broj) values( '2006/01-', 52)
>insert tbl (prefix, broj) values( '2006/01-', 51)
>insert tbl (prefix, broj) values( '2006/01-', 50)
>insert tbl (prefix, broj) values( '2006/01-', 49)
>insert tbl (prefix, broj) values( '2006/01-', 48)
>insert tbl (prefix, broj) values( '2006/01-', 47)
>insert tbl (prefix, broj) values( '2006/01-', 46)
>insert tbl (prefix, broj) values( '2006/01-', 45)
>insert tbl (prefix, broj) values( '2006/01-', 44)
>insert tbl (prefix, broj) values( '2006/01-', 43)
>insert tbl (prefix, broj) values( '2006/01-', 42)
>insert tbl (prefix, broj) values( '2006/01-', 41)
>insert tbl (prefix, broj) values( '2006/01-', 40)
>insert tbl (prefix, broj) values( '2006/01-', 39)
>insert tbl (prefix, broj) values( '2006/01-', 38)
>insert tbl (prefix, broj) values( '2006/01-', 37)
>insert tbl (prefix, broj) values( '2006/01-', 36)
>go
>if exists(select * from sysobjects where xtype='TR' and name='tr_tbl_ins')
>drop trigger tr_tbl_ins
>go
>create trigger tr_tbl_ins on tbl
>for insert
>as
>update tbl
>set
> broj = coalesce((select max(n.broj) from tbl n inner join inserted i on
>n.prefix = i.prefix),0)+1
>where
> [id] = (select [id] from inserted)
>go
>
>insert into tbl (prefix)
>values ('2006/01-')
>select * from tbl order by broj
>-- New broj should be max(Broj)+1 but its 1 since the select max(n.broj)
>returns NULL value
>|||This is not the real code, it is simplified as musch as possible to show a
point. And, yes, there are ways to fix this but thats not the point. Now,
where should I report this?
MC
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:db6q22529ujosandv3442g81j72e4i2j6q@.
4ax.com...
> It sure looks like a bug to me.
> The UPDATE in the trigger can be simplified somewhat. There is no
> need to include another instance of tbl in the subquery, it can be
> written as a corellated subquery. This version did not have the
> problem:
> update tbl
> set
> broj = coalesce((select max(n.broj) from tbl n
> where n.prefix = tbl.prefix),0)+1
> where
> [id] = (select [id] from inserted)
> Of course you must already realize that the trigger will provide
> duplicate values if more than one row is INSERTED at a time. This is
> considered a vulnerability, and I think the trigger should have a test
> that enforces that there is only one row in INSERTED.
> Roy Harvey
> Beacon Falls, CT
> On Fri, 31 Mar 2006 10:31:01 +0200, "MC" <marko_culo#@.#yahoo#.#com#>
> wrote:
>|||I could see that it was a simplified example.
The way I always did this was to open a case with Microsoft. This
cost some $$$ up front, but it was always refunded in the long run
when it proved to be a bug. Haven't had to do that in some years
though.
Roy
On Fri, 31 Mar 2006 14:21:39 +0200, "MC" <marko_culo#@.#yahoo#.#com#>
wrote:

>This is not the real code, it is simplified as musch as possible to show a
>point. And, yes, there are ways to fix this but thats not the point. Now,
>where should I report this?
>
>MC|||> Now,
> where should I report this?
http://lab.msdn.microsoft.com/productfeedback/
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"MC" <marko_culo#@.#yahoo#.#com#> wrote in message news:uiyBBvLVGHA.2156@.tk2msftngp13.phx.gb
l...
> This is not the real code, it is simplified as musch as possible to show a
> point. And, yes, there are ways to fix this but thats not the point. Now,
> where should I report this?
>
> MC
>
> "Roy Harvey" <roy_harvey@.snet.net> wrote in message
> news:db6q22529ujosandv3442g81j72e4i2j6q@.
4ax.com...
>|||Maybe you should report a bug:
http://lab.msdn.microsoft.com/produ...ck/Default.aspx
ML
http://milambda.blogspot.com/|||MC (marko_culo#@.#yahoo#.#com#) writes:
> I have a case when the same query returns two different results, depending
> on the generated exec plan. I managed to reproduce it on different servers
> and databases.
> Heres the code, the trigger should generate new number max(Broj)+1 but the
> result here is 1 because join in the selec max() part returns empty result
> set.
> If you substitute part in the join on n.prefix = i.prefix with n.prefix =
> convert(varchar(10), i.prefix) then the trigger works.
> Notice that the convert function doesnt actually do anything, its there as
> an example, feel free to use any method to force different exec plan.
Definitely a bug, and you should report it on
http://lab.msdn.microsoft.com/ProductFeedback/ if you haven't already
done so. I tested it on the CTP of SP1 for SQL 2005, and the bug
exists there as well.
The bug appears to related to the virtual inserted table. I rewrote
the MAX query in a way which is more palatable to me:
select max(n.broj)
from tbl n
where exists(select * from inserted i where n.prefix = i.prefix)
However, it still returns NULL.
If I do:
SELECT * INTO #inserted FROM tbl
and then use #inserted in the trigger, I get the correct result.
Note: while the use if SELECT INTO is palatable, since you don't
have to copy the table definition in the trigger, my recommendation
is to use a table variable instead (which only should the columns
actually referenced the trigger). I did use SELECT INTO for a while,
but I found that it gave us performance problems.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||I've reported the bug, however I cant seem to check the status. It seems
that part of the site is down at the moment. If you want to look at it, i
created it under 'mculo'
MC
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9797F2B151D23Yazorman@.127.0.0.1...
> MC (marko_culo#@.#yahoo#.#com#) writes:
> Definitely a bug, and you should report it on
> http://lab.msdn.microsoft.com/ProductFeedback/ if you haven't already
> done so. I tested it on the CTP of SP1 for SQL 2005, and the bug
> exists there as well.
> The bug appears to related to the virtual inserted table. I rewrote
> the MAX query in a way which is more palatable to me:
> select max(n.broj)
> from tbl n
> where exists(select * from inserted i where n.prefix = i.prefix)
> However, it still returns NULL.
> If I do:
> SELECT * INTO #inserted FROM tbl
> and then use #inserted in the trigger, I get the correct result.
> Note: while the use if SELECT INTO is palatable, since you don't
> have to copy the table definition in the trigger, my recommendation
> is to use a table variable instead (which only should the columns
> actually referenced the trigger). I did use SELECT INTO for a while,
> but I found that it gave us performance problems.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||MC (marko_culo#@.#yahoo#.#com#) writes:
> I've reported the bug, however I cant seem to check the status. It seems
> that part of the site is down at the moment. If you want to look at it, i
> created it under 'mculo'
Yeah, I also had problems with the site yesterday. I guess it will come back
later today. Keep in mind that it is still only six o'clock in the morning
in Redmond.
But I did see your bug Saturday. As you had failed to attach(*) the repro,
I augmented the bug with the information from the newsgroup.
(*) It's very easy to go wrong, due to the poor inteface on the site. First
browse the file path, and then press Add Attachment. Took me some time to
figure that out the first time.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx