is it a bug in SQL Server 2008?

Question

create table Mytable1
(ID int,
Fname varchar(50)
)

create table Mytable2
(ID int,
Lname varchar(50)
)

insert into Mytable1 (ID,Fname)
values (1,'you')
insert into Mytable1 (ID,Fname)
values (2,'Tou')
insert into Mytable1 (ID,Fname)
values (3,'Nou')

insert into Mytable2 (ID,Lname)
values (1,'you2')

The field Fname does not exist in table Mytable2 But we have a result for the following query :

select * from Mytable1 where Fname in (select Fname from Mytable2)

Note : I use sql server 2008 the result is all rows of table Mytable1

is it a bug in SQL ?

Answer 1

No, it's not a bug.

You can see what's happening a bit clearer if you add table aliases to the fields used throughout the query:

select * from Mytable1 mt1 
where mt1.Fname in (select mt1.Fname from Mytable2 mt2)

- ie. the subquery is referencing (and returning) values from the main query.

If you change the query to:

select * from Mytable1 mt1 
where mt1.Fname in (select mt2.Fname from Mytable2 mt2)

- you get an error.

(SQLFiddle here)

Answer 2

No, this is not a bug: http://bugs.mysql.com/bug.php?id=26801

Apparently, this references Fname from Mytable1:

mysql> select *, (select Lname from Mytable1 limit 1) from Mytable2 where Lname in (select Lname from Mytable1 );
+------+-------+--------------------------------------+
| ID   | Lname | (select Lname from Mytable1 limit 1) |
+------+-------+--------------------------------------+
|    1 | you2  | you2                                 |
+------+-------+--------------------------------------+
1 row in set (0.01 sec)