July 1, 2009 at 9:32 am
I have found a bug in SQL Server.
Take the following query:
SELECT DISTINCT systemname
FROM Table1
WHERE SystemName NOT IN ( SELECT SystemName
FROM dbo.Table2 )
[SystemName] is a field on Table1, but NOT on Table2. Therefore, this query should fail. If you execute ONLY the NOT IN clause:
SELECT SystemName FROM dbo.Table2
The query fails properly, with a 'column not found' error. However, running the whole query at once does not fail, it simply returns no records.
The problem arises because the column 'SystemName' is on Table1, and apparently the parser doesn't distinguish. This is easily shown by changing to the field in the NOT IN clause to 'SystemNameXXX'. The whole query fails at once.
You are welcome. 🙂 Where do I submit this to Microsoft?
-----------------------------
I enjoy queries!
July 1, 2009 at 11:08 am
It's not a bug. You're allowed to reference columns from the outer query inside a subquery. It has to be allowed, or something like this couldn't be done
select * from sys.columns c where exists
(select 1 from sys.tables t where t.object_id = c.object_id)
It's odd that it's allowed in the select, but I can see reasons, like this rather odd possibility
SELECT * FROM Table1 INNER JOIN Table2 ON < Join clause >
WHERE Table1.Col1 IN (SELECT Col3 + '/' + Table2.Col2 From Table3 WHERE Table2.ColZ = Table3.ColZ)
You're welcome to go and submit it on Connect, but I suspect it will be closed 'By Design'
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
July 1, 2009 at 11:56 am
Thanks for the reply! Sure looks like a bug, but yeah, referencing column inside has to be available. Not a bug, but sure does stink. ><
-----------------------------
I enjoy queries!
July 1, 2009 at 12:16 pm
This is one reason why columns should always be prefixed with the name or alias of the table they're from. It's just good practice and, if you do that, putting the wrong column inside the subquery will throw errors.
SELECT DISTINCT
Table1.systemname
FROM dbo.Table1
WHERE Table1.SystemName NOT IN
( SELECT Table2.SystemName
FROM dbo.Table2 Table2
)
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply