locked
Incorrect column name give me wrong result RRS feed

  • Question

  • Hi,

    I have the below simple query, aggregating the data to a temp table and when I try to filter the same table with incorrect column name sql server ignore the filter and goes for the another filter which give me a wrong result.

    In the below please note my column name is gpn, but i incorrectly give groupname in IN condition which ignores and goes for the next NOT it predicate. Please explain why this is happening, because I was expecting to give me incorrect column name error.

    SELECT groupname as gpn , (count(DepartmentID)) as DPID
        into #temppp
        FROM [AdventureWorks2012].[HumanResources].[Department] group by groupname having count(groupname) > 1
        select * from [AdventureWorks2012].[HumanResources].[Department]
        where groupname IN (select  groupname from #temppp)
        AND DepartmentID NOT IN (select DPID from #temppp)
        drop table #temppp

    Thanks


    Best Regards Moug

    Thursday, December 17, 2015 5:32 AM

Answers

  • This happens because the query (select DPID from #temppp) is correlated. So when DPID is not found in #tempppp, SQL Server looks in the surrounding query for a column with this name.

    This is nothing unique to SQL Server, but this is right from the standards for SQL - it should be the same on any platform.

    • Marked as answer by Moug45 Friday, December 18, 2015 1:35 AM
    Thursday, December 17, 2015 11:22 PM

All replies

  • This happens because the query (select DPID from #temppp) is correlated. So when DPID is not found in #tempppp, SQL Server looks in the surrounding query for a column with this name.

    This is nothing unique to SQL Server, but this is right from the standards for SQL - it should be the same on any platform.

    • Marked as answer by Moug45 Friday, December 18, 2015 1:35 AM
    Thursday, December 17, 2015 11:22 PM
  • Thanks, Am not aware of this Correlation. 

    Best Regards Moug

    Friday, December 18, 2015 1:35 AM