August 1, 2012 at 9:15 am
We encountered very strange behavior when using temp tables.
Steps are:
Create a temp table 1 and add data (you can use an "into" clause to dynamically create the temp table)
Then create a temp table 2 derived from the first temp table 1, minus a column
Finally, execute a select (or an update) statement where you reference the missing column
using an "in" clause, and select the missing column from temp table 2
Logically, this column doesn't exist in the temp table 2 so an error should occur...right?
However, instead, the script executes as if the "in" clause simply doesn't exist!
Here is a sample script:
--create a table temp1
select 'test1' as test1, 'test2' as test2, 'test3' as test3
into #temp1
--select 2 fields into temp2
select test2, test3
into #temp2
from #temp1;
--select from temp1 with field that's in temp2
--works as expected
select * from #temp1
where test2 in (select test2 from #temp2)
--select from temp1 with field that doesn't exist anywhere
--select from temp1 with field that's not in temp2, but IS in temp1
--DOESN'T CAUSE ERROR?
select * from #temp1
where test1 in (select test1 from #temp2)
--causes error as expected
select * from #temp1
where test2 in (select foo from #temp2)
Is this normal behavior?
The worst thing about uncovering this is instead of a select, I was executing an update script, which inadvertently caused the entire table to update as opposed to select records...
Also, one last thing...if you execute the statement "select test1 from #temp2" outside of the parenthesis, it renders an error!!
Is this normal behavior, or a bug in SQL Server?
August 1, 2012 at 9:22 am
Learn to alias your tables.
SELECT *
FROM #temp1
WHERE test1 IN (
SELECT test1 --refers to #temp1
FROM #temp2
);
SELECT *
FROM #temp1
WHERE test1 IN (
SELECT a.test1 --refers to #temp2 and will error
FROM #temp2 a
);
What you actually wrote was this: -
SELECT *
FROM #temp1
WHERE test1 = test1;
August 1, 2012 at 9:28 am
Nothing to do with temp tables and not a bug.
A subquery (like with an IN) can reference any column in the tables inside the subquery or outside the subquery. Otherwise we couldn't have correlated subqueries.
Your query resolves to
select * from #temp1 t1
where t1.test1 in (select t1.test1 from #temp2 t2)
Perfectly valid. Probably not what you meant, but perfectly valid. Qualify your column names, always. If you do that, the query would have given the expected error
select * from #temp1 t1
where t1.test1 in (select t2.test1 from #temp2 t2) -- error
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 1, 2012 at 9:38 am
Lesson learned...
I thought by separating the temp table 2 statement in parenthesis, it was separated from the temp table 1 statement, and no alias would be needed...
Won't make that error again...
Thanks!
August 1, 2012 at 10:12 am
Robert Heynen-454800 (8/1/2012)
Lesson learned...I thought by separating the temp table 2 statement in parenthesis, it was separated from the temp table 1 statement, and no alias would be needed...
If that were true, then this wouldn't work:
SELECT * FROM #temp1 t1 WHERE EXISTS (SELECT 1 FROM #temp2 t2 WHERE t1.SomeColumn = t2.SomeOtherColumn)
Nor would any other correlated subquery.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 1, 2012 at 2:23 pm
GilaMonster (8/1/2012)
Robert Heynen-454800 (8/1/2012)
Lesson learned...I thought by separating the temp table 2 statement in parenthesis, it was separated from the temp table 1 statement, and no alias would be needed...
If that were true, then this wouldn't work:
SELECT * FROM #temp1 t1 WHERE EXISTS (SELECT 1 FROM #temp2 t2 WHERE t1.SomeColumn = t2.SomeOtherColumn)Nor would any other correlated subquery.
Thanks!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply