|
|
|
Grasshopper
 
Group: General Forum Members
Last Login: Tuesday, July 21, 2009 10:36 AM
Points: 14,
Visits: 43
|
|
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!
|
|
|
|
|
SSChampion
Group: General Forum Members
Last Login: Yesterday @ 9:28 AM
Points: 14,526,
Visits: 10,415
|
|
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
We walk in the dark places no others will enter
We stand on the bridge and none may pass
|
|
|
|
|
Grasshopper
Group: General Forum Members
Last Login: Tuesday, July 21, 2009 10:36 AM
Points: 14,
Visits: 43
|
|
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!
|
|
|
|
|
SSChampion
Group: General Forum Members
Last Login: Yesterday @ 9:28 AM
Points: 14,526,
Visits: 10,415
|
|
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
We walk in the dark places no others will enter
We stand on the bridge and none may pass
|
|
|
|
