SQL Temp Table not Throwing Invalid Column Name Error when it should

I have found the following issue in SQL Server 2008 Management Studio. When I run the following script as whole I expect errors (due to a "copy and paste error"), but don't receive them.

IF OBJECT_ID('Foo') IS NOT NULL
BEGIN
    DROP TABLE Foo
END
IF OBJECT_ID('Bar') IS NOT NULL
BEGIN
    DROP TABLE Bar
END
CREATE TABLE Foo (
    FooID int
)
Create Table Bar (
    BarID int
,   FooID int
)
INSERT INTO Foo
SELECT 1 UNION ALL SELECT 2
INSERT INTO Bar
SELECT 1,1 UNION ALL SELECT 2,1 UNION ALL SELECT 3,1
GO

IF OBJECT_ID('tempdb..#temp') IS NOT NULL
BEGIN
      DROP TABLE #TEMP
END
GO
CREATE TABLE #TEMP (
    FooID int
)
INSERT INTO #TEMP
    SELECT FooID FROM Bar
GO
SELECT * FROM Foo WHERE FooID IN (SELECT FooID FROM #TEMP)
GO
SELECT * FROM Bar WHERE BarID IN (SELECT BarID FROM #TEMP)
GO
SELECT * FROM #TEMP
GO

The second last statement containing the where clause filter on "SELECT BarID FROM #TEMP"开发者_JAVA百科 runs, but there is no BarID column in #TEMP. When running the script as a whole I receive no error and the script return all rows in Foo. However when I run the command on its own then I get the error informing me that there is no BarId in #TEMP.

Is there a reason for this? Is it my code?

Your second to last query actually is working correctly.

Other commenters please correct me, but I understand this to be a correlated subquery. Table aliasing will show what is actually going on. Your query is equivalent to this:

SELECT * FROM Bar x WHERE x.BarID IN (SELECT x.BarID FROM #TEMP)

For each row in #TEMP, the nested select is actually returning the current value of BarID from table Bar, so yes, BarID in (BarID) is true, so every row in Bar is matched, so every row is returned.

To show that you're not crazy, try

SELECT * FROM Bar WHERE BarID IN (SELECT NonExistentFieldName FROM #TEMP) 

This raises the error I think you're expecting.

For some background on Neil's answer, please see the following KB article:

http://support.microsoft.com/kb/298674

The example they use (that works though it appears that it shouldn't) is quite similar to your example (though without #temp tables):

CREATE TABLE X1 (ColA INT, ColB INT)
CREATE TABLE X2 (ColC INT, ColD INT)
SELECT ColA FROM X1 WHERE ColA IN (Select ColB FROM X2)

They also show that adding the table alias (which is a good practice for many reasons, but would prevent this problem from getting past compilation) makes the query fail as you originally expected.

This is actually following the ANSI standard for column resolution, and is not going to change (unless they implement Erland's SET STRICT_CHECKS ON).

Some others who have complained about this bug (and a lot more background on why the engine has to work this way):

http://connect.microsoft.com/SQL/feedback/details/542289/

http://connect.microsoft.com/SQL/feedback/details/422252/

http://connect.microsoft.com/SQL/feedback/details/126785/

Have to agree with @BalamBalam's comment: why are you writing invalid code, and then complaining that it's mistakenly taken as valid?