0

I made a mistake when updating code and ended up with this situation (I renamed the column in a table variable and missed renaming where it was used)...

    CREATE TABLE MyTable (Id INT, OtherCol VARCHAR(100));
    -- populate with lots of data, just a demo here
    INSERT MyTable(Id) VALUES (1, 'bla'), (2, 'bla'), (3, 'bla'), (4, 'bla');

    DECLARE @TableVar TABLE (MyCol INT);
    -- populate the table variable somehow, e.g. ...
    INSERT @TableVar VALUES (1);
    
    UPDATE MyTable
       SET OtherCol = 'Some value'
     WHERE Id IN (SELECT Id FROM @TableVar);

The mistake is that the Id column in the IN (SELECT...) should have been MyCol of course, but I didn't expect the following behaviour...

  • If @TableVar contains no rows, no update is done at all - I expected that
  • If @TableVar contains any rows, every row in MyTable is updated I would have expected that if @TableVar included one row, that the (SELECT...) would return one row, though I'm not sure what value I'd expect in the Id column.

The code isn't great anyway for various reasons, but that behaviour of an IN (SELECT...) where a column name from the outer query is referenced acting as if it matched every row surprised me (and made it a very nasty error).

Am I just dumb to be so surprised by this? What does SQL Server actually make out of that statement?

Keith Fearnley
  • 125
  • 6
  • 5
    Welcome to T-SQL name resolution. The `Id` resolves to `MyTable.Id`, so effectively this is the same thing as `WHERE Id = Id` if `@TableVar` has any rows. This is why you always, always prefix columns with table names in subqueries, even if it seems unnecessary. – Jeroen Mostert May 25 '22 at 12:48
  • 4
    [No, this is not a bug in T-SQL](https://www.sqlinthewild.co.za/index.php/2019/04/23/no-this-is-not-a-bug-in-t-sql/). This is one reason why whenever dealing with 2+ objects, you should **always** qualify your columns. `WHERE Id IN (SELECT Id FROM @TableVar);` would (effectively) resolve to `WHERE MyTable.Id IN (SELECT MyTable.Id FROM @TableVar);` and so as long as `@TableVar` has *at least* 1 row, the `IN` will *always* be `TRUE`. – Thom A May 25 '22 at 12:49
  • 1
    Does this help? [Correlated Subqueries: resolving ambiguous names](https://dba.stackexchange.com/q/164628/7257) – GarethD May 25 '22 at 12:50
  • Thanks guys - good advice about table aliases that I do try to stick to, it's the simple little queries where we sometimes forget or cut corners I guess. Much appreciated comments and useful links. – Keith Fearnley May 25 '22 at 13:45
  • 1
    The really bad aspect of this is that it can cause queries that at one time worked correctly to start failing silently, if the underlying tables have changed. So no cutting corners there, ever! It's just more expensive in the long run. :P – Jeroen Mostert May 25 '22 at 13:48

0 Answers0