Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2004
    Posts
    492

    Unanswered: invalid query runs anyway

    I would not expect this to run, since the column "uid" does not exist in table1. Yet it does, without errors or anything, even worse: somewhere it matches all values.

    What's going on here? Is this new in SQL2005?

    Code:
    use monkey
    go
    
    set nocount on
    go
    
    create table table1 (column1 int)
    go
    create table table2 (uid int)
    go
    
    insert into table1 values (1)
    insert into table2 values (1)
    insert into table2 values (2)
    go
    
    /*
    uid
    -----------
    1
    2
    */
    select * from table2 where uid in (select uid from table1)
    go
    
    go
    drop table table1
    drop table table2
    go

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I can explain, but also not.

    I changed your script since uid was coming up blue in SSMS (might indicate a keyword). Makes no difference. If you fully qualify the subquery, it fails.

    Code:
    use test
    go
    
    set nocount on
    go
    
    create table no_uidx (column1 int)
    go
    create table has_uidx (uidx int)
    go
    
    insert into no_uidx values (1)
    insert into has_uidx values (1)
    insert into has_uidx values (2)
    go
    
    /*
    uidx
    -----------
    1
    2
    */
    
    SET STATISTICS PROFILE ON
    
    select * from has_uidx where uidx in (select uidx from no_uidx)
    
    SET STATISTICS PROFILE OFF
    
    go
    drop table no_uidx
    drop table has_uidx
    GO
    Result (significant bit highlighted in red)
    Code:
    Rows,Executes,StmtText,StmtId,NodeId,Parent,PhysicalOp,LogicalOp,Argument,DefinedValues,EstimateRows,EstimateIO,EstimateCPU,AvgRowSize,TotalSubtreeCost,OutputList,Warnings,Type,Parallel,EstimateExecutions
    2,1,select * from has_uidx where uidx in (select uidx from no_uidx),1,1,0,NULL,NULL,NULL,NULL,2,NULL,NULL,NULL,0.00665622,NULL,NULL,SELECT,0,NULL
    2,1,  |--Nested Loops(Left Semi Join),1,2,1,Nested Loops,Left Semi Join,NULL,NULL,2,0,8.36E-06,11,0.00665622,[test].[dbo].[has_uidx].[uidx],NULL,PLAN_ROW,0,1
    2,1,       |--Table Scan(OBJECT:([test].[dbo].[has_uidx]), WHERE:([test].[dbo].[has_uidx].[uidx]=[test].[dbo].[has_uidx].[uidx])),1,3,2,Table Scan,Table Scan,OBJECT:([test].[dbo].[has_uidx]), WHERE:([test].[dbo].[has_uidx].[uidx]=[test].[dbo].[has_uidx].[uidx]),[test].[dbo].[has_uidx].[uidx],2,0.003125,0.0001592,11,0.0032842,[test].[dbo].[has_uidx].[uidx],NULL,PLAN_ROW,0,1
    2,2,       |--Table Scan(OBJECT:([test].[dbo].[no_uidx])),1,4,2,Table Scan,Table Scan,OBJECT:([test].[dbo].[no_uidx]),NULL,1,0.0032035,7.96E-05,9,0.0033627,NULL,NULL,PLAN_ROW,0,2
    I don't know if this is SQL Server specific or ISO SQL compliant.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Feb 2004
    Posts
    492
    AFAIK it has to do with the 'uid'-keyword.

    If I change it to use another keyword (select date from table1) it fails indicating 'date' is invalid. Same goes for a non-keyword such as column88.


    Also, the statistics turn out quite different when comparing a valid query to a 'uid'-query:
    Code:
    SET STATISTICS PROFILE ON
    select * from table2 where uid in (select column1 from table1)
    select * from table2 where uid in (select uid from table1)
    SET STATISTICS PROFILE OFF
    Thing is, we occasionally use the "uid" as a columnname...

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Kaiowas View Post
    AFAIK it has to do with the 'uid'-keyword.
    It has nothing to do with uid - it works for me with all column names. You also said the execution plan is different.

    Please can you post complete scripts for everything you stated in your last post?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    The problem is that in a subquery all columns from the main query are available. In effect, you have wrriten a query that evaluates to this:
    Code:
    select * from #table2 where uid = uid
    I think what you may have meant was something along the lines of
    Code:
    select * 
    from #table2 
    where uid in (select column1 
                   from #table1 
                   where column1 = uid)
    The moral of the story is always qualify your columns.
    Last edited by MCrowley; 08-18-10 at 10:32. Reason: EDIT: Prettiferized the seconmd code bit.

  6. #6
    Join Date
    Feb 2004
    Posts
    492
    I see, thanks!

    Horror this, as it also gives the correct results:
    Code:
    select * from table2 where uid in (select uid from table1 where column1 = uid)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •