i have just come across a potential "bug" in the T-SQL Parser(if such a thing exists) of both Oracle and SQL server.
here it is
create a simple table
Normal 0 false false false MicrosoftInternetExplorer4
create table table1
(table1_field1 varchar(255),
table1_field2 varchar(255))
create another simple table
create table table2
(table2_field1 varchar(255),
table2_field2 varchar(255))
technically it shouldn't work but it does. do a simple IN operator using a field in the outer table but not present in the inner table as shown below
select * from table1
where table1_field1 in (select table1_field1 from table2)
running
select table1_field1 from table2
fails and gives
Msg 207, Level 16, State 1, Line 1Invalid column name 'table1_field1'.
but the whole thing runs ok...i think the engine is confused by the fields table1_field1 in and outside the bracket.
does anyone know why?
Before we throw this query into the bug basket, we need to remember a couple of things:
1. Subqueries are not parsed or compiled in ISOLATION from the main (outer) query and cannot therefore be executed in isolation. An independent query plan cannot be generated for them.
2. Subqueries can reference ANY valid columns from sets (tables, Table-valued functions, views) referenced in the main query. Whether the main query and the subquery are correlated or not depends on how you use the referenced columns.
3. Queries containing subqueries are sometimes transformed by the parser before an optimal execution plan is generated.
4. Correlated subqueries can also be decorrelated depending on factors like the comparison operator used (IN, EXISTS, ANY, SOME, BETWEEN ) and the presence of indexes on the tables referenced.
Your query, by virtue of referencing the table1_field1 column is equivalent to:
select * from table1 where
table1_field1 IN (select table1_field1 from table1, table2)
which is a valid subquery even when taken in isolation. Remember that table1 is visible and will be used in the subquery even if you do not explicitly say so.
Your query is eventually decorrelated by the query optimizer using a Left semi join to produce a query plan that would have come from the following simple query:
select table1.* from table1 JOIN table2 ON table1_field1 = table1_field1
In this case, writing the following would have been sufficient:
select * from table1 WHERE table1_field1 = table1_field1
IMHO, this is not a bug but a "feature" that needs to be used carefully. It is a programming/logic/spelling bug if the developer intended to use a column from table2 instead.
Nathan H. Omukwenyi.
i agree, i displayed the execution plan and noticed that the table1 has a predicate in the form
[mydb].[dbo].[table1].[table1_field1] as [ a ].[table1_field1]=[mydb].[dbo].[table1].[table1_field1] as [ a ].[table1_field1]
this will always return to true.