Jerry Nixon @Work: SQL 2005 TSQL bug with NEWID() in DERIVED tables

Jerry Nixon on Windows

Tuesday, April 1, 2008

SQL 2005 TSQL bug with NEWID() in DERIVED tables

I can't verify that this IS a bug, but I know what a bug is (I have created plenty in my career) and I think I can recognize one when I see it.

Explained

When you have a newid() in a derived table that joins to another table (or derived table, as my example), it appears that newid() executes again for each JOINED row even though it should execute once for each SOURCE row. Look at my example and see the pain:

create table x (col1 int, col2 int)
GO

insert into x select 1, 1
insert into x select 2, 1
insert into x select 3, 1
insert into x select 4, 1
insert into x select 5, 1
GO

select
sub1.newid
,sub2.col1
,sub2.col2
from
(select
newid() as newid
,1 as jointothis) sub1
join (select
col1
,col2 from x) sub2
on sub1.jointothis = sub2.col2
GO

drop table x

/*

expected output

newid col1 col2
------------------------------------ ----------- -----------
E9F667EB-B9FD-435D-BF1D-B2CA1E572499 1 1
E9F667EB-B9FD-435D-BF1D-B2CA1E572499 2 1
E9F667EB-B9FD-435D-BF1D-B2CA1E572499 3 1
E9F667EB-B9FD-435D-BF1D-B2CA1E572499 4 1
E9F667EB-B9FD-435D-BF1D-B2CA1E572499 5 1

actual output

newid col1 col2
------------------------------------ ----------- -----------
E9F667EB-B9FD-435D-BF1D-B2CA1E572499 1 1
A9D8E98B-8531-40F4-8D29-0BCEBBD066B0 2 1
88F36612-CED9-4847-B5B5-EFEBC6DF6EE8 3 1
15839554-E804-43D2-87D7-6A6C88D15A71 4 1
504E0E63-F7BC-4307-8131-0375F7149714 5 1

*/