Post reply

Query fails with invalid column name but succeeds as a sub-query with unexpected results.

  • September 22, 2015 at 10:39 am

    #300772

    -- The 3rd query uses an incorrect column name in a sub-query and succeeds but rows are incorrectly qualified. This is very DANGEROUS!!!

    -- The issue exists is in 2008 R2, 2012 and 2014 and is "By Design"

    set nocount on

    go

    if object_id('tempdb.dbo.#t1') IS NOT NULL drop table #t1

    if object_id('tempdb.dbo.#t2') IS NOT NULL drop table #t2

    GO

    create table #t1 (c1 int)

    insert into #t1 values (1)

    create table #t2 (c2 int)

    insert into #t2 values (2)

    select * from #t1

    select * from #t2

    GO

    select count(*) as [Valid Join] from #t1 t1 inner join #t2 t2 on t1.c1 = t2.c2 -- corrected column name - no rows returned - GOOD

    go

    select count(*) as [Valid Inner Query] from #t1 where c1 in (select c2 from #t2) -- corrected column name - no rows returned - GOOD

    go

    select count(*) as [Invalid Column Name in Inner Query Works instead of failing and returns an incorrect count of 1 instead of 0] from #t1 where c1 in (select c1 from #t2) -- invalid column name c1 - WORKS and returns all rows which is incorrect - BAD !!!

    go

    select count(*) as [Invalid Column Name in Join Fails] from #t1 t1 inner join #t2 t2 on t1.c1 = t2.c1 -- invalid column name c1 - FAILS - GOOD

    go

    This was addressed by Microsoft as "By Design" in 2010 as per :

    https://connect.microsoft.com/sqlserver/feedback/details/542289/subquery-with-error-does-not-cause-outer-select-to-fail

    This succeeds when the invalid column name is a valid column name in the outer query. So in this situation the sub-query would fail when run by itself but succeed with an incorrectly applied filter when run as a sub-query. as Per KB article https://support.microsoft.com/en-ca/kb/298674

    The danger here is that if a SQL Server user runs DML in a production database with such a sub-query which then the results are likely not the expected results with potentially unintended actions applied against the data.

    Wondering how many SQL Server users have had incorrectly applied DML or incorrect query results and don't even know it....?

  • September 22, 2015 at 12:44 pm

    #1828540

    this is because you have a correlated subquery, mostly due to bad aliasing. it's not a bug, but a behavior that occurs when you don't code correctly. properly aliasing columns might make it obvious that [c1] doesn't belong to the inner table.

    the column c1 exists...it's in the outer table.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • September 22, 2015 at 3:09 pm

    #1828599

    Agreed and the KB article does explain this. I've reminded our developers of the best practice to always qualify column names but we have a lot of SQL, a lot of developers and developer turn over to complicate matters. So the concern is that many SQL developers would not be aware of this correlated sub-query assumption and instead expect the sub-query to fail. Likely leading to incorrect results.

    Thanks

Viewing 3 posts - 1 through 2 (of 2 total)

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