Cross Apply Ambiguity
- by Dave Ballantyne
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.
