Options

Bug in "Qualify Object Names" introduced bug in my code!

dmckinneydmckinney Posts: 12
edited April 14, 2015 3:10PM in SQL Prompt Previous Versions 
SELECT  onfile.DealReference  
                    dbo.DCA_Detail AS onfile 
                    WHERE   onfile.DealReference NOT IN (  
                            SELECT  DealReference  
                            FROM    #tmpAllocatedDealReferences )

When I chose to "Qualify Object Names", it changed the subselect as follows.
NOT IN (  
                            SELECT  onfile.DealReference  
                            FROM    #tmpAllocatedDealReferences )

This introduced a nasty little bug, quite tricky to spot.

The DealReference field in the subselect should refer to the temp table.

I'd advise to all that you shouldn't consider the "Qualify Object Names" as purely cosmetic i.e. that if you're going to use it, make sure that you do so BEFORE testing.

Regards,

David McKinney.
David McKinney
· Share on Twitter

Comments

  • Options
    bauerjubauerju Posts: 33 Bronze 3
    Hi David

    the same occurs with table variables:
    DECLARE @PP TABLE ( PPi VARCHAR(15) )
SELECT PPi FROM @PP
    after qualifying: 
    DECLARE @PP TABLE ( PPi VARCHAR(15) )
SELECT @PP.PPi FROM @PP

    Qualify inserts @PP. into the select. This creates the error.

    Jürgen
    · Share on Twitter
  • Options
    rlivelyrlively Posts: 5
    Was this ever fixed?
    · Share on Twitter
Sign In or Register to comment.