Somebody please explain this to me

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Somebody please explain this to me

Author

Topic

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-08-14 : 10:30:53

what i'm trying to do is to map old ItemsTree guids to new ItemsTree guids. so same values have to be mapped to new same values.
but i'm at a loss why do these queries work this way. by my logic they shouldn't and all joins should return the same result.
it seems that the newid is always evaluated last which doesn't make sense. what am i missing here??
if i add the t2 select in a temp table first all is fine.


create table test (ItemsTree uniqueidentifier, id int)
insert into test (ItemsTree, id)
SELECT '23D1FA9F-E1D2-4B0B-AF6C-0051B82C8002' ,'37819' union all
SELECT '23D1FA9F-E1D2-4B0B-AF6C-0051B82C8002' ,'39471' union all 
SELECT '80DD39B9-E0E2-4D0E-8E0B-00824CA307FE' ,'39003'  union all
SELECT 'FF9B905C-0AB4-472E-963A-00A4F9A8DCB4' ,'38567'  union all
SELECT '394332F5-E833-42E8-99C4-00C488351AE0' ,'39245'  union all
SELECT '60336A22-9C64-4707-96CE-00C6368A63FD' ,'39869'  union all
SELECT '60336A22-9C64-4707-96CE-00C6368A63FD' ,'39870'  union all
SELECT '60336A22-9C64-4707-96CE-00C6368A63FD' ,'38419'  union all
SELECT '93EB004B-7A53-4354-90CC-0137BE4EAF4D' ,'39174' union all
SELECT '2256D380-16C9-4F82-8B8F-01577B9CF9F9' ,'38354'  union all
SELECT '4C1F41FB-D2FA-4763-B8D5-015E71F903F9' ,'38547'  union all
SELECT '4C1F41FB-D2FA-4763-B8D5-015E71F903F9' ,'39968'  union all
SELECT '4C1F41FB-D2FA-4763-B8D5-015E71F903F9' ,'39969' union all
SELECT '4C1F41FB-D2FA-4763-B8D5-015E71F903F9' ,'39970'  union all
SELECT '4C1F41FB-D2FA-4763-B8D5-015E71F903F9' ,'39971'  union all
SELECT '8494E236-D718-4D49-A991-01646B665D3C' ,'38151'  union all
SELECT '94A1241F-B982-45D9-9276-0176D767D650' ,'38416'  union all
SELECT '3ED92D71-D70E-41C6-8C00-019E77C1F700' ,'39176'  union all
SELECT '91CF26C1-77AC-45FB-B3B1-01A7FFBF5335' ,'37854'  union all
SELECT '386671D0-DBCD-4FC8-BC5A-01B99E048AB5' ,'38947'  union all
SELECT 'AC20D64F-7E01-4CCF-9147-01E6E7AB3194' ,'38851'  union all 
SELECT '5672F600-ACA8-42CC-A512-01F7623448B4' ,'39189' union all
SELECT '5672F600-ACA8-42CC-A512-01F7623448B4' ,'40261' union all
SELECT '5672F600-ACA8-42CC-A512-01F7623448B4' ,'40262' union all
SELECT '97D75A19-A2D8-4BD9-A8DE-0252FBD81161' ,'38059' union all
SELECT 'E1DDF951-B4A0-4E91-B279-026BC26D3805' ,'39204' union all
SELECT '5F29FF9D-0CA7-4F8E-94DB-02802D5CFA05' ,'38443' union all
SELECT '4D990464-FA9E-44E3-ADE0-02A4B10DD3B0' ,'38802' union all
SELECT 'A5285FDB-5052-448D-9DD8-02C970C8B1CA' ,'37642' union all
SELECT '15C87ED4-E3F3-43D3-8D8A-02E76FE71E82' ,'38795' union all
SELECT '15C87ED4-E3F3-43D3-8D8A-02E76FE71E82' ,'40089' 

-- here the mapping is wrong
select t2.*, t1.*
from test t1
		inner join (select ItemsTree, NEWID() as newItemsTree from (select distinct ItemsTree from test) t) t2 on t1.ItemsTree = t2.ItemsTree

-- here the mapping is wrong
select t2.*, t1.*
from test t1
		left join (select ItemsTree, NEWID() as newItemsTree from (select distinct ItemsTree from test) t) t2 on t1.ItemsTree = t2.ItemsTree

-- here the mapping is wrong
select t2.*, t1.*
from test t1
		inner join (select * from (select ItemsTree, NEWID() as newItemsTree from (select distinct ItemsTree from test) t) tt) t2 on t1.ItemsTree = t2.ItemsTree

-- here the mapping is correct
select t2.*, t1.*
from test t1
		left join (select * from (select ItemsTree, NEWID() as newItemsTree from (select distinct ItemsTree from test) t) tt) t2 on t1.ItemsTree = t2.ItemsTree

drop table test

server version: SQL Server 2005 9.0.3054

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.0 out!

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-08-14 : 10:49:05

interesting...
for me the first 3 queries return this:


23D1FA9F-E1D2-4B0B-AF6C-0051B82C8002	BA89925D-19E3-45DE-B796-95337809FBCD	23D1FA9F-E1D2-4B0B-AF6C-0051B82C8002	37819
23D1FA9F-E1D2-4B0B-AF6C-0051B82C8002	15156B67-D651-4CC9-BD14-4CC3983A84DB	23D1FA9F-E1D2-4B0B-AF6C-0051B82C8002	39471
80DD39B9-E0E2-4D0E-8E0B-00824CA307FE	37514746-94D6-4F52-B864-1CCFB64512BA	80DD39B9-E0E2-4D0E-8E0B-00824CA307FE	39003
FF9B905C-0AB4-472E-963A-00A4F9A8DCB4	47E96E08-7C6F-487F-BACF-727441E34E5A	FF9B905C-0AB4-472E-963A-00A4F9A8DCB4	38567
394332F5-E833-42E8-99C4-00C488351AE0	B0F63703-63C8-47D3-B9D8-5873F98CBC0F	394332F5-E833-42E8-99C4-00C488351AE0	39245
60336A22-9C64-4707-96CE-00C6368A63FD	2F908ECA-4102-439A-B055-37010C89ED3D	60336A22-9C64-4707-96CE-00C6368A63FD	39869
60336A22-9C64-4707-96CE-00C6368A63FD	CCE49605-54F3-4154-B201-B211DC32BE0D	60336A22-9C64-4707-96CE-00C6368A63FD	39870
60336A22-9C64-4707-96CE-00C6368A63FD	BA447CCF-1DF7-4034-85A9-20415EB15ED4	60336A22-9C64-4707-96CE-00C6368A63FD	38419
93EB004B-7A53-4354-90CC-0137BE4EAF4D	81305C99-C69D-4CB3-902D-A779FF580C9B	93EB004B-7A53-4354-90CC-0137BE4EAF4D	39174
2256D380-16C9-4F82-8B8F-01577B9CF9F9	C1AEF5FA-B4A7-4528-B10F-EB5C7EEA82CE	2256D380-16C9-4F82-8B8F-01577B9CF9F9	38354
4C1F41FB-D2FA-4763-B8D5-015E71F903F9	FCFDCDE6-1162-47B5-A34A-5A4EF60D7038	4C1F41FB-D2FA-4763-B8D5-015E71F903F9	38547
4C1F41FB-D2FA-4763-B8D5-015E71F903F9	B01EF1DB-F6C6-4BE5-B70A-B242AFFCBB77	4C1F41FB-D2FA-4763-B8D5-015E71F903F9	39968
4C1F41FB-D2FA-4763-B8D5-015E71F903F9	F9719A78-DB48-457B-863F-9B9284CB8D9E	4C1F41FB-D2FA-4763-B8D5-015E71F903F9	39969
4C1F41FB-D2FA-4763-B8D5-015E71F903F9	302C51A7-2C68-4732-8684-14982DEBB739	4C1F41FB-D2FA-4763-B8D5-015E71F903F9	39970
4C1F41FB-D2FA-4763-B8D5-015E71F903F9	3AA254EF-AF85-4F3C-A77F-02AFEAEEA355	4C1F41FB-D2FA-4763-B8D5-015E71F903F9	39971
8494E236-D718-4D49-A991-01646B665D3C	0CF56570-53AA-43F3-A719-1B10447C7BFF	8494E236-D718-4D49-A991-01646B665D3C	38151
94A1241F-B982-45D9-9276-0176D767D650	18FF0478-D489-4538-8279-55910DE07427	94A1241F-B982-45D9-9276-0176D767D650	38416
3ED92D71-D70E-41C6-8C00-019E77C1F700	F02646B3-5E71-4139-BE8A-C321C9B0CE30	3ED92D71-D70E-41C6-8C00-019E77C1F700	39176
91CF26C1-77AC-45FB-B3B1-01A7FFBF5335	5589D132-241E-4D50-B45D-F32FD4D245B5	91CF26C1-77AC-45FB-B3B1-01A7FFBF5335	37854
386671D0-DBCD-4FC8-BC5A-01B99E048AB5	A51E66B6-FDF7-41A6-80B3-15EB6E1A9368	386671D0-DBCD-4FC8-BC5A-01B99E048AB5	38947
AC20D64F-7E01-4CCF-9147-01E6E7AB3194	AAC187BD-ACCC-45C3-A729-7F06DEDFF85C	AC20D64F-7E01-4CCF-9147-01E6E7AB3194	38851
5672F600-ACA8-42CC-A512-01F7623448B4	16D1D95E-68FD-4BC5-B111-048E408F89A3	5672F600-ACA8-42CC-A512-01F7623448B4	39189
5672F600-ACA8-42CC-A512-01F7623448B4	954641F3-D33D-46C6-9B19-80256E553D38	5672F600-ACA8-42CC-A512-01F7623448B4	40261
5672F600-ACA8-42CC-A512-01F7623448B4	30E8CAEF-C740-45F8-977E-3CE3944B38BB	5672F600-ACA8-42CC-A512-01F7623448B4	40262
97D75A19-A2D8-4BD9-A8DE-0252FBD81161	196E78DF-A868-42BC-A4A1-6E1751C6C22E	97D75A19-A2D8-4BD9-A8DE-0252FBD81161	38059
E1DDF951-B4A0-4E91-B279-026BC26D3805	0E2E9150-D2F6-4976-9871-C2AA4F277C8D	E1DDF951-B4A0-4E91-B279-026BC26D3805	39204
5F29FF9D-0CA7-4F8E-94DB-02802D5CFA05	7DEB11B4-14C5-40A2-AE0C-745476B7F256	5F29FF9D-0CA7-4F8E-94DB-02802D5CFA05	38443
4D990464-FA9E-44E3-ADE0-02A4B10DD3B0	88E944A1-847A-430F-A032-3D8A18C90783	4D990464-FA9E-44E3-ADE0-02A4B10DD3B0	38802
A5285FDB-5052-448D-9DD8-02C970C8B1CA	80BB4C91-D2C0-4B8B-91FA-7C1040E1980D	A5285FDB-5052-448D-9DD8-02C970C8B1CA	37642
15C87ED4-E3F3-43D3-8D8A-02E76FE71E82	03B7ED08-FFDA-40F0-9CBC-DDC3E55E6070	15C87ED4-E3F3-43D3-8D8A-02E76FE71E82	38795
15C87ED4-E3F3-43D3-8D8A-02E76FE71E82	B4EEBE26-5020-43EF-A318-43230A698306	15C87ED4-E3F3-43D3-8D8A-02E76FE71E82	40089

and the 4th query returns this:


23D1FA9F-E1D2-4B0B-AF6C-0051B82C8002	53DEB492-D725-4230-8277-DE066AA65065	23D1FA9F-E1D2-4B0B-AF6C-0051B82C8002	37819
23D1FA9F-E1D2-4B0B-AF6C-0051B82C8002	53DEB492-D725-4230-8277-DE066AA65065	23D1FA9F-E1D2-4B0B-AF6C-0051B82C8002	39471
80DD39B9-E0E2-4D0E-8E0B-00824CA307FE	117A9536-497B-4FA0-8C8A-8D59B850EB5C	80DD39B9-E0E2-4D0E-8E0B-00824CA307FE	39003
FF9B905C-0AB4-472E-963A-00A4F9A8DCB4	7720F8F6-D4F7-4280-9160-628063625FA3	FF9B905C-0AB4-472E-963A-00A4F9A8DCB4	38567
394332F5-E833-42E8-99C4-00C488351AE0	0B4AD4C0-4007-4222-99B6-B70F8387370C	394332F5-E833-42E8-99C4-00C488351AE0	39245
60336A22-9C64-4707-96CE-00C6368A63FD	C8D9FE37-20E8-4288-826C-D4556ADB0A45	60336A22-9C64-4707-96CE-00C6368A63FD	39869
60336A22-9C64-4707-96CE-00C6368A63FD	C8D9FE37-20E8-4288-826C-D4556ADB0A45	60336A22-9C64-4707-96CE-00C6368A63FD	39870
60336A22-9C64-4707-96CE-00C6368A63FD	C8D9FE37-20E8-4288-826C-D4556ADB0A45	60336A22-9C64-4707-96CE-00C6368A63FD	38419
93EB004B-7A53-4354-90CC-0137BE4EAF4D	B9037B25-DFF3-43AD-B40A-024F42F0BE53	93EB004B-7A53-4354-90CC-0137BE4EAF4D	39174
2256D380-16C9-4F82-8B8F-01577B9CF9F9	CCEA5210-A65B-46F1-8D88-2B4E3A510449	2256D380-16C9-4F82-8B8F-01577B9CF9F9	38354
4C1F41FB-D2FA-4763-B8D5-015E71F903F9	A777421B-BF4C-4F5B-910B-2C36809B55A2	4C1F41FB-D2FA-4763-B8D5-015E71F903F9	38547
4C1F41FB-D2FA-4763-B8D5-015E71F903F9	A777421B-BF4C-4F5B-910B-2C36809B55A2	4C1F41FB-D2FA-4763-B8D5-015E71F903F9	39968
4C1F41FB-D2FA-4763-B8D5-015E71F903F9	A777421B-BF4C-4F5B-910B-2C36809B55A2	4C1F41FB-D2FA-4763-B8D5-015E71F903F9	39969
4C1F41FB-D2FA-4763-B8D5-015E71F903F9	A777421B-BF4C-4F5B-910B-2C36809B55A2	4C1F41FB-D2FA-4763-B8D5-015E71F903F9	39970
4C1F41FB-D2FA-4763-B8D5-015E71F903F9	A777421B-BF4C-4F5B-910B-2C36809B55A2	4C1F41FB-D2FA-4763-B8D5-015E71F903F9	39971
8494E236-D718-4D49-A991-01646B665D3C	83013350-35F2-4730-9534-5BFA533A0B36	8494E236-D718-4D49-A991-01646B665D3C	38151
94A1241F-B982-45D9-9276-0176D767D650	E5B9B6BD-48E8-4926-9B79-7A993D9D61E1	94A1241F-B982-45D9-9276-0176D767D650	38416
3ED92D71-D70E-41C6-8C00-019E77C1F700	E1431886-9AFA-4CEB-8A3F-2CFE2192411A	3ED92D71-D70E-41C6-8C00-019E77C1F700	39176
91CF26C1-77AC-45FB-B3B1-01A7FFBF5335	6F0573D6-3319-41DF-8B3E-7159BDD5425A	91CF26C1-77AC-45FB-B3B1-01A7FFBF5335	37854
386671D0-DBCD-4FC8-BC5A-01B99E048AB5	1FD3B152-0F9E-40BA-B0CA-56F6733ADC48	386671D0-DBCD-4FC8-BC5A-01B99E048AB5	38947
AC20D64F-7E01-4CCF-9147-01E6E7AB3194	F22E76B3-1F3A-471D-A64C-C3B8BFDC2622	AC20D64F-7E01-4CCF-9147-01E6E7AB3194	38851
5672F600-ACA8-42CC-A512-01F7623448B4	6E73FED5-6632-4B9B-95EE-A9BE118FF204	5672F600-ACA8-42CC-A512-01F7623448B4	39189
5672F600-ACA8-42CC-A512-01F7623448B4	6E73FED5-6632-4B9B-95EE-A9BE118FF204	5672F600-ACA8-42CC-A512-01F7623448B4	40261
5672F600-ACA8-42CC-A512-01F7623448B4	6E73FED5-6632-4B9B-95EE-A9BE118FF204	5672F600-ACA8-42CC-A512-01F7623448B4	40262
97D75A19-A2D8-4BD9-A8DE-0252FBD81161	7592623C-D358-4AE4-9EE4-E9720F6DEBFC	97D75A19-A2D8-4BD9-A8DE-0252FBD81161	38059
E1DDF951-B4A0-4E91-B279-026BC26D3805	2C7D80BB-01E7-4C85-A631-A472F4D293F9	E1DDF951-B4A0-4E91-B279-026BC26D3805	39204
5F29FF9D-0CA7-4F8E-94DB-02802D5CFA05	8A30DCAF-BFB0-4250-9816-BE7F7F87FCE8	5F29FF9D-0CA7-4F8E-94DB-02802D5CFA05	38443
4D990464-FA9E-44E3-ADE0-02A4B10DD3B0	F2BD200E-13FB-4CE9-92CE-03B8228B1856	4D990464-FA9E-44E3-ADE0-02A4B10DD3B0	38802
A5285FDB-5052-448D-9DD8-02C970C8B1CA	BA27B0A3-FCBA-4C8C-97D0-C5EBDA9CC4F5	A5285FDB-5052-448D-9DD8-02C970C8B1CA	37642
15C87ED4-E3F3-43D3-8D8A-02E76FE71E82	A09DA3D5-3D0E-4F82-B374-BC0A1112BC95	15C87ED4-E3F3-43D3-8D8A-02E76FE71E82	38795
15C87ED4-E3F3-43D3-8D8A-02E76FE71E82	A09DA3D5-3D0E-4F82-B374-BC0A1112BC95	15C87ED4-E3F3-43D3-8D8A-02E76FE71E82	40089

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.0 out!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-14 : 10:50:35

Yes, I noticed that now.
However I had the same problem last week and I posted a working solution.
But I don't remember if it was here or on SSC.

E 12°55'05.25"
N 56°04'39.16"

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-08-14 : 10:51:30

the problem is not getting the working solution, but understanding why it does this

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.0 out!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-14 : 11:01:17

If I remember correctly, it has do to with the new SQL Server 2005 enhancements,
where the query engine can look down into more nested levels of derived tables in order to make better decisions
such as which indexes to use and so one.

I'll see if I can find the part in Books Online again.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-14 : 11:20:01

I found it here.
http://msdn.microsoft.com/en-us/library/ms143359(SQL.90).aspx

quote:
Books Online

SQL Server 2005 sometimes evaluates expressions in queries sooner than when they are evaluated in SQL Server 2000. This behavior provides the following important benefits:

The ability to match indexes on computed columns to expressions in a query that are the same as the computed column expression.

The prevention of redundant computation of expression results.

However, depending on the nature of the query and the data in the database, run-time exceptions may occur in SQL Server 2005 if the query contains an existing unsafe expression. These run-time exceptions include the following:

Arithmetic exceptions: zero-divide, overflow, and underflow.

Conversion failures such as loss of precision and an attempt to convert a nonnumeric string to a number.

Aggregation over a set of values that are not all guaranteed to be non-null.

These exceptions may not occur in SQL Server 2000 in a specific application that uses specific data. However, a query-plan that is changed because of changing statistics might potentially lead to an exception in SQL Server 2000. These run-time exceptions can be prevented by modifying the query to include conditional expressions such as NULLIF or CASE. For more information, see Troubleshooting Errors and Warnings on Query Expressions.

E 12°55'05.25"
N 56°04'39.16"

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-08-14 : 11:27:45

but then it shouldn't behave differently for left join and inner join... this is all just weird.

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.0 out!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-14 : 11:32:19

The first and third query has the same execution plan.
The second and fourth execution are not the same.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-14 : 11:34:04

Execution plans on SQL Server 2005.

* First query - Fail


  |--Compute Scalar(DEFINE:([Expr1007]=newid()))
       |--Merge Join(Inner Join, MERGE:([Test].[dbo].[test].[ItemsTree])=([t1].[ItemsTree]), RESIDUAL:([Test].[dbo].[test].[ItemsTree]=[Test].[dbo].[test].[ItemsTree] as [t1].[ItemsTree]))
            |--Sort(DISTINCT ORDER BY:([Test].[dbo].[test].[ItemsTree] ASC))
            |    |--Table Scan(OBJECT:([Test].[dbo].[test]))
            |--Sort(ORDER BY:([t1].[ItemsTree] ASC))
                 |--Table Scan(OBJECT:([Test].[dbo].[test] AS [t1]))

* Second query - Fail


  |--Nested Loops(Left Outer Join, OUTER REFERENCES:([t1].[ItemsTree]))
       |--Table Scan(OBJECT:([Test].[dbo].[test] AS [t1]))
       |--Compute Scalar(DEFINE:([Test].[dbo].[test].[ItemsTree]=[Test].[dbo].[test].[ItemsTree], [Expr1008]=newid()))
            |--Stream Aggregate(DEFINE:([Test].[dbo].[test].[ItemsTree]=ANY([Test].[dbo].[test].[ItemsTree])))
                 |--Table Scan(OBJECT:([Test].[dbo].[test]), WHERE:([Test].[dbo].[test].[ItemsTree] as [t1].[ItemsTree]=[Test].[dbo].[test].[ItemsTree]))

* Third query - Fail


  |--Compute Scalar(DEFINE:([Expr1007]=newid()))
       |--Merge Join(Inner Join, MERGE:([Test].[dbo].[test].[ItemsTree])=([t1].[ItemsTree]), RESIDUAL:([Test].[dbo].[test].[ItemsTree]=[Test].[dbo].[test].[ItemsTree] as [t1].[ItemsTree]))
            |--Sort(DISTINCT ORDER BY:([Test].[dbo].[test].[ItemsTree] ASC))
            |    |--Table Scan(OBJECT:([Test].[dbo].[test]))
            |--Sort(ORDER BY:([t1].[ItemsTree] ASC))
                 |--Table Scan(OBJECT:([Test].[dbo].[test] AS [t1]))

* Fourth query - Success


  |--Nested Loops(Left Outer Join, WHERE:([Test].[dbo].[test].[ItemsTree] as [t1].[ItemsTree]=[Test].[dbo].[test].[ItemsTree]))
       |--Table Scan(OBJECT:([Test].[dbo].[test] AS [t1]))
       |--Table Spool
            |--Compute Scalar(DEFINE:([Expr1007]=[Expr1007]))
                 |--Compute Scalar(DEFINE:([Expr1007]=newid()))
                      |--Compute Scalar(DEFINE:([Test].[dbo].[test].[ItemsTree]=[Test].[dbo].[test].[ItemsTree]))
                           |--Sort(DISTINCT ORDER BY:([Test].[dbo].[test].[ItemsTree] ASC))
                                |--Table Scan(OBJECT:([Test].[dbo].[test]))

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-14 : 11:35:56

Maybe this can sched some more light?
http://blogs.msdn.com/craigfr/archive/2008/05/06/conversion-and-arithmetic-errors-change-between-sql-server-2000-and-2005.aspx

E 12°55'05.25"
N 56°04'39.16"

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-08-14 : 11:36:10

exactly my point.
the 1st and 3rd should have the same plans just as they do.
but so should 2nd and 4th.

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.0 out!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-14 : 11:40:46

I just run your queries on a SQL Server 2000.
There query 2 is ok and the others are not!

E 12°55'05.25"
N 56°04'39.16"

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-08-14 : 11:42:09

ok that post has shed some light but newid and rand shouldn't be treated like normal scalar operations that in my opinion.
if you think about the set theory and expression evaluation order set by ( and ) it simply does not compute

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.0 out!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-14 : 11:42:55

Execution plans on SQL Server 2000.

* First query - Fail


  |--Compute Scalar(DEFINE:([Expr1003]=newid()))
       |--Merge Join(Inner Join, MERGE:([test].[ItemsTree])=([t1].[ItemsTree]), RESIDUAL:([test].[ItemsTree]=[t1].[ItemsTree]))
            |--Sort(DISTINCT ORDER BY:([test].[ItemsTree] ASC))
            |    |--Table Scan(OBJECT:([tempdb].[dbo].[test]))
            |--Sort(ORDER BY:([t1].[ItemsTree] ASC))
                 |--Table Scan(OBJECT:([tempdb].[dbo].[test] AS [t1]))

* Second query - Success


  |--Nested Loops(Left Outer Join, OUTER REFERENCES:([t1].[ItemsTree]))
       |--Table Scan(OBJECT:([tempdb].[dbo].[test] AS [t1]))
       |--Hash Match(Cache, HASH:([t1].[ItemsTree]), RESIDUAL:([t1].[ItemsTree]=[t1].[ItemsTree]))
            |--Compute Scalar(DEFINE:([Expr1003]=newid()))
                 |--Stream Aggregate(DEFINE:([test].[ItemsTree]=ANY([test].[ItemsTree])))
                      |--Table Scan(OBJECT:([tempdb].[dbo].[test]), WHERE:([t1].[ItemsTree]=[test].[ItemsTree]))

* Third query - Fail


  |--Compute Scalar(DEFINE:([Expr1003]=newid()))
       |--Merge Join(Inner Join, MERGE:([test].[ItemsTree])=([t1].[ItemsTree]), RESIDUAL:([test].[ItemsTree]=[t1].[ItemsTree]))
            |--Sort(DISTINCT ORDER BY:([test].[ItemsTree] ASC))
            |    |--Table Scan(OBJECT:([tempdb].[dbo].[test]))
            |--Sort(ORDER BY:([t1].[ItemsTree] ASC))
                 |--Table Scan(OBJECT:([tempdb].[dbo].[test] AS [t1]))

* Fourth query - Fail


  |--Compute Scalar(DEFINE:([Expr1003]=newid()))
       |--Nested Loops(Left Outer Join, OUTER REFERENCES:([t1].[ItemsTree]))
            |--Table Scan(OBJECT:([tempdb].[dbo].[test] AS [t1]))
            |--Hash Match(Cache, HASH:([t1].[ItemsTree]), RESIDUAL:([t1].[ItemsTree]=[t1].[ItemsTree]))
                 |--Stream Aggregate(DEFINE:([test].[ItemsTree]=ANY([test].[ItemsTree])))
                      |--Table Scan(OBJECT:([tempdb].[dbo].[test]), WHERE:([t1].[ItemsTree]=[test].[ItemsTree]))

E 12°55'05.25"
N 56°04'39.16"

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-08-14 : 11:43:02

quote:
Originally posted by Peso

I just run your queries on a SQL Server 2000.
There query 2 is ok and the others are not!

E 12°55'05.25"
N 56°04'39.16"

that's even weirder!!

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.0 out!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-14 : 11:50:01

It easy to see why the INNER JOIN fails.
The "Compute Scalar" is forced to execute last.

In SQL Server 2000, the success comes when "Compute Scalar" occurs before HASH MATCH.
In SQL Server 2005, the success comes when "Compute Scalar" occurs before NESTED LOOP step.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-14 : 11:55:04

Run your four queries again, with this small change for query 2!

left merge join

Now both query 2 and query 4 produces the wanted result!

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-14 : 11:56:51

With LEFT MERGE JOIN, the query plan for query two changed from


  |--Nested Loops(Left Outer Join, OUTER REFERENCES:([t1].[ItemsTree]))
       |--Table Scan(OBJECT:([Test].[dbo].[test] AS [t1]))
       |--Compute Scalar(DEFINE:([Test].[dbo].[test].[ItemsTree]=[Test].[dbo].[test].[ItemsTree], [Expr1008]=newid()))
            |--Stream Aggregate(DEFINE:([Test].[dbo].[test].[ItemsTree]=ANY([Test].[dbo].[test].[ItemsTree])))
                 |--Table Scan(OBJECT:([Test].[dbo].[test]), WHERE:([Test].[dbo].[test].[ItemsTree] as [t1].[ItemsTree]=[Test].[dbo].[test].[ItemsTree]))


  |--Merge Join(Left Outer Join, MANY-TO-MANY MERGE:([t1].[ItemsTree])=([Test].[dbo].[test].[ItemsTree]), RESIDUAL:([Test].[dbo].[test].[ItemsTree] as [t1].[ItemsTree]=[Test].[dbo].[test].[ItemsTree]))
       |--Sort(ORDER BY:([t1].[ItemsTree] ASC))
       |    |--Table Scan(OBJECT:([Test].[dbo].[test] AS [t1]))
       |--Compute Scalar(DEFINE:([Test].[dbo].[test].[ItemsTree]=[Test].[dbo].[test].[ItemsTree], [Expr1008]=newid()))
            |--Sort(DISTINCT ORDER BY:([Test].[dbo].[test].[ItemsTree] ASC))
                 |--Table Scan(OBJECT:([Test].[dbo].[test]))

E 12°55'05.25"
N 56°04'39.16"

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-08-14 : 11:58:09

yes... but if you ask me the join hint shouln't change the results of a join, no?

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.0 out!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-14 : 12:00:57

With SQL Server 2005, yes, because of the new changes in query engine.
These changes were made for a number of reasons as mentioned above.

E 12°55'05.25"
N 56°04'39.16"

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-08-14 : 12:02:17

I think Peso hit the nail on the head about the computre scalar.

I had a similar issue which I though was a bug, basically using NEWID to update a predetermined number of rows randomly. However, as I was told by Microsoft after submitting a bug, that it was expected behavor because you cannot assume when the NEWID function is going to executed. <shrug>

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources