Post reply

TVP Bug

  • October 24, 2012 at 10:45 am

    #1552360

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • October 24, 2012 at 12:27 pm

    #1552390

    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 2 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply