Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Cross Apply Ambiguity

Cross apply (and outer apply)  are a very welcome addition to the TSQL language.  However, today after a few hours of head scratching, I have found an simple issue which could cause big big problems.

What would you expect from this statement ?

select * 
  from sys.objects b    
  join sys.objects a
    on a.object_id = object_id

No prizes for guessing SQL server errors with “Ambiguous column name 'object_id'”.

What would you expect from this statement ?

Select * 
  from sys.objects a
  cross apply( Select * 
                from sys.objects b where b.object_id = object_id) as c

Surprisingly, perhaps, the result is a cross join of sys.objects.  Well, what happened there ?

If you look at the apply statement, within the where clause, only one of the conditions is qualified with a table name.  This meant that is has be interpreted as “b.object_id = b.object_id” causing the cross apply to have no join the the parent sys.objects table and causing the cross join.

The fix is , obviously, simple

Select * 
  from sys.objects a
  cross apply( Select * 
                from sys.objects b where b.object_id = a.object_id) as c

So why no “Ambiguous column name ” error ?  I’ve raised a connect item on this issue here.

Comments

Posted by trieuluongquang on 17 September 2013

Why don't you use?

select *

 from sys.objects b    

 join sys.objects a

   on a.object_id = b.object_id

I think it very simple to know.

Leave a Comment

Please register or log in to leave a comment.