Friday, March 17, 2006

SQL Bug (or feature?) when using sub-queries

I have been using SQL sub-queries for quite a while and had never seen this until today, I was trying to delete records from a table that were found in another table

*luckily* I created a backup before deleting anything, the query to delete ended up deleting every single record from the first table, I knew that not all the records from one were in the other one, so I investigated a little bit, and was able to reproduce the behavior

try this:

CREATE TABLE [temp1] (
[Field1] [nvarchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Field2] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Field3] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [temp2] (
[Field2] [nvarchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Field3] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Field4] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

--Execute both inserts a few times
insert into temp1
values ('v1', null, null)

insert into temp2
values ('v2', null, null)

--now run this query
select * from temp1
where field1 in (select field1 from temp2)

you get all the records from temp1

notice that the field field1 doesn't exist in temp2, but it does exist in temp1, if I try a field that doesn't exist in neither table I will get the "Invalid column name 'fieldx'." error

weird... I wonder if this is a bug/known bug... or a feature!

2 comments:

BlackTigerX said...

if I write something like:

select * from temp1 t1
where t1.field1 in (select t2.field1 from temp2 t2)

it works fine, but it shouldn't get confused with both tables in that case, should it?

BlackTigerX said...

it seems this is a known bug (or a feature, they call it co-related query), so you just need to be aware of the workaround

http://groups.google.com/group/microsoft.public.sqlserver.server/browse_frm/thread/48f2ecffb8be2eca

http://groups.google.com/group/comp.databases.oracle.server/browse_frm/thread/ade67bee5e65fbc3