locked
IN clause ignores error in subquery ¿possible bug? RRS feed

  • Question

  • Hi all,

    I tried a query that delete some records by using "in" clause. I´m very surprised when see that entire table was deleted !!
    After read the wuery I noticed that the subquery used in "in" was using a non existent column so ¿why don´t get an error?
    ex:
    delete from TableA
    where Id in
    (
    select Id_non-existent_Column from TableB
    )

    It´s like queries inside in is not being checked by SQL server and always return some kind of value that causes "in" clause was true at any moment... deleting all records


    Thursday, October 10, 2013 3:39 PM

Answers

  •  "in" was using a non existent column so ¿why don´t get an error?

    Hello,

    A non-existing column in TableB, but existing in TableA, as I guess? The column of the outer query can be reference in the inner query, therefore you don't get an error.

    Example with sys.tables and sys.columns, the column "object_id" exists in both views and the query works fine:

    select *
    from sys.columns
    where object_id in
        (select object_id
         from sys.tables)

    The column "column_id" exists only in sys.columns, but is used in the inner query:

    select *
    from sys.columns
    where object_id in
        (select column_id
         from sys.tables)

    Better written with alias an 2 part qualifiers make it may more understandable:

    select *
    from sys.columns AS COL
    where COL.object_id in
        (select COL.column_id
         from sys.tables AS TBL)

    So not a bug in SQL Server, "only" in your script.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]


    • Edited by Olaf HelperMVP Thursday, October 10, 2013 3:52 PM
    • Marked as answer by DPR77 Thursday, October 10, 2013 4:02 PM
    Thursday, October 10, 2013 3:52 PM

All replies

  • Witch version are you using? In 2012 SQL returns me an error: the column doesn't exixt

    Thursday, October 10, 2013 3:48 PM
  •  "in" was using a non existent column so ¿why don´t get an error?

    Hello,

    A non-existing column in TableB, but existing in TableA, as I guess? The column of the outer query can be reference in the inner query, therefore you don't get an error.

    Example with sys.tables and sys.columns, the column "object_id" exists in both views and the query works fine:

    select *
    from sys.columns
    where object_id in
        (select object_id
         from sys.tables)

    The column "column_id" exists only in sys.columns, but is used in the inner query:

    select *
    from sys.columns
    where object_id in
        (select column_id
         from sys.tables)

    Better written with alias an 2 part qualifiers make it may more understandable:

    select *
    from sys.columns AS COL
    where COL.object_id in
        (select COL.column_id
         from sys.tables AS TBL)

    So not a bug in SQL Server, "only" in your script.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]


    • Edited by Olaf HelperMVP Thursday, October 10, 2013 3:52 PM
    • Marked as answer by DPR77 Thursday, October 10, 2013 4:02 PM
    Thursday, October 10, 2013 3:52 PM
  • Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)   Apr  2 2010 15:48:46   Copyright (c) Microsoft Corporation  Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600: ) (Hypervisor) 
    Thursday, October 10, 2013 3:57 PM
  • "A non-existing column in TableB, but existing in TableA, as I guess?"

    Yes, that´s my mistake, the column not exists in B table but yes in A table. The SQL server checks ok because the column exists but in A table. I was blind by TableB columns.

    Thanks Olaf!

    Thursday, October 10, 2013 4:02 PM
  • That's one of those traps you have to know; I learned it also the hard way.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Thursday, October 10, 2013 4:06 PM
  • You're right it's a trap. Even we can cosider this a mistake or something similar. I mean

    delete...

    where colA in (select colA from tableB)

    I can't see how colA is IN select colA from tableB

    Is not IN


    • Edited by DIEGOCTN Thursday, October 10, 2013 4:16 PM
    Thursday, October 10, 2013 4:16 PM
  • You're right it's a trap. Even we can cosider this a mistake or something similar. I mean

    delete...

    where colA in (select colA from tableB)

    I can't see how colA is IN select colA from tableB

    Is not IN


    Because when you do

    delete from TableA
    where Id in
    (
    select SomeColumnInTableAButNotInTableB from TableB
    )

    you are not asking if Id in TableB, you are asking is Id in the result of that Select.  And when you have a correlated subquery you want the subquery to be able to reference columns in the outer query.  For example, you might want to do

    delete from A
    where A.UserID in
    (
    select B.UserID from B Where A.Id = B.id
    )

    And that query is not possible unless the subquery can reference the value in the correlated row of table A. 

    IMO, the best way to avoid this sort of problem is to always use the table qualifier (either the table name or the table alias you have assigned) whenerver you reference a column.  So for example

    delete from TableA
    where TableA.Id in
    (
    select TableB.Id from TableB
    )

    If you code that way, you will get an error if TableB does not have a column named Id.

    Tom

    Thursday, October 10, 2013 5:23 PM
  • IMO, the best way to avoid this sort of problem is to always use the table qualifier (either the table name or the table alias you have assigned) whenerver you reference a column. 


    Agree; that's really the best way.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Thursday, October 10, 2013 5:30 PM
  • I totally agree with the last statement: IMO, the best way to avoid this sort of problem is to always use the table qualifier (either the table name or the table alias you have assigned) whenerver you reference a column.  So for example

    but I'm not so sure about your first statement. Take for example the order by in a view. It's forbidden even if the logic could be accepted. Why not? A view works like a query. In a query you can order in a view not. Ok, he developers decided in this way and never mind. I think the same should be done in this case. You are asking to delete something WHERE...bear in mind that there is a condition WHERE. Do you see that the condition is satisfied? I don't see. If the data satisfy the condition go on, otherwise don't. It's an achademical question but I'm not sure about the correctness of this behaviour.  Of course, you know and you can avoid it but...as logic...

    PS I hate this editor..eh eh


    Thursday, October 10, 2013 9:23 PM