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.