March 9, 2011 at 6:23 am
One of my developers came to me and said the results of the following type query were different between 2005 and 2008 R2 (build 10.50.1660.1). Does anyone have some information if this is an issue? I recall reading a cummulative update fixed an issue when inserting into a temp table and referencing that temp table in the from clause. However, I can't find that in the fix list of any CU now:( It could be related.
INSERT INTO B (Fields...)
SELECT A.Field1, A.Field2
FROM A
LEFT JOIN B ON (A.id = B.id)
WHERE B.id IS NULL
He said on 2008, this did not return a result, versus 2005 it did.
He had to change it from the query above to something like this:
INSERT INTO B (Fields...)
SELECT A.Field1, A.Field2
FROM A
WHERE B.id IS NOT IN (SELECT id FROM B)
March 9, 2011 at 6:31 am
I don't think there's a difference.
I just tested this in 2008 R2:
CREATE TABLE #A (ID INT PRIMARY KEY) ;
CREATE TABLE #B (ID INT PRIMARY KEY) ;
INSERT INTO #A
(ID)
VALUES (1),
(2) ;
INSERT INTO #B
(ID)
VALUES (1) ;
SELECT *
FROM #A
LEFT JOIN #B
ON #A.ID = #B.ID
WHERE #B.ID IS NULL ;
As expected, it returned the row with ID = 2 from #A.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 9, 2011 at 7:03 am
bpportman 52825 (3/9/2011)
INSERT INTO B (Fields...)
SELECT A.Field1, A.Field2
FROM A
WHERE B.id IS NOT IN (SELECT id FROM B)
This code is not correct (and won't run) as it references B outside of the subquery.
I'm fairly sure this is not any kind of bug - it's such basic functionality that's used every day - can you post the real code that did and did not return results in SQL 2008?
March 9, 2011 at 7:54 am
bpportman 52825 (3/9/2011)
@HowardW - please ignore my typo for the second query. What is important is the developer states the first query does not return data under 2K8 but does return data from 2K5.
Nothing has functionally changed in regards to this between 2005 and 2008, but the two queries you showed (ignoring the typo) can have semantic differences depending on whether NULL's can exist at either side of the query.
If the same query is returning different data against identical schema's between 2005 and 2008, it's because there is different data in the two environments, or potentially because you're running different ANSI_NULLS settings for the queries between the environments (although I don't believe this would effect the LEFT JOIN)
March 9, 2011 at 8:38 am
bpportman 52825 (3/9/2011)
@HowardW - please ignore my typo for the second query. What is important is the developer states the first query does not return data under 2K8 but does return data from 2K5.
The developer is either doing the two queries on different sets of data, or is doing something wrong in the query.
Has he verified that the data is the same in both databases? That's the most likely issue.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 9, 2011 at 10:12 am
Thanks All!
That was my first reaction was the data was different and I still believe it is data. I just wanted to make sure I wasn't missing anything. I did check the ANSI_NULL settings on the tables in question and that is fine.
March 9, 2011 at 3:29 pm
Most likely the issue is with the data - but, there is at least one bug that might apply here:
http://support.microsoft.com/kb/958214
Validate the version you are running, and if it is not CU2 or later you might want to try applying that cumulative update and testing again.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply