What are the challenges you face when working across database platforms? Take the survey
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

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
  • Options
    Was this ever fixed?
Sign In or Register to comment.