October 24, 2012 at 10:38 am
This is a strange. I had a TVP field name that was very close to the table field name. It took me a bit to figure out what the issue was. It did not error. Intellisense wanted me to pick the wrong field. this is on a SQL 2008 R2 X64. Here is a code snippet. Has anyone ran into this? MS going to correct this?
CREATE TABLE TEST (ThePKsId uniqueidentifier)
Insert into TEST
Select 'F35D5E07-716D-4BF0-B6FD-D1182F761CB2' UNION ALL
Select '6CD13452-53E0-459A-A00D-422372799747' UNION ALL
Select 'FF8B5A03-0779-4C03-9AA5-4315D4042737' UNION ALL
Select 'E17FD7DA-781A-46B7-83A0-F2C82B175074' UNION ALL
Select '0A0F209F-B4BD-4343-8ECA-B87A4A15FDF0'
DECLARE @RemoveData Table (ThePKId uniqueidentifier)
Insert INTO @RemoveData
Values ('F35D5E07-716D-4BF0-B6FD-D1182F761CB2')
--Wrong field - Intellisense has no issue with this and it does not error
--Basically it does Where 1=1
Select * FROM TEST
WHERE ThePKsId IN (Select ThePKsId from @RemoveData)
--Right response
Select * FROM TEST
WHERE ThePKsId IN (Select ThePKId from @RemoveData)
October 24, 2012 at 10:45 am
Not a bug. Expected and documented behaviour.
The scope for column resolution within a subquery is first to the tables inside the subquery, then to the tables outside the subquery. If it wasn't, we couldn't write correlated subqueries.
This is why you should always qualify your column names. If you had, you'd have got the expected error
-- throws an error
Select * FROM TEST t
WHERE t.ThePKsId IN (Select rd.ThePKsId from @RemoveData rd)
--Right response
Select * FROM TEST t
WHERE t.ThePKsId IN (Select rd.ThePKId from @RemoveData rd)
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
October 24, 2012 at 12:27 pm
I have known to always qualify in Joins but never ran into this in Where clauses. Lesson learned. I don't like it but I understand it now.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply