Cross apply Ambiguity

Question

Post reply

Cross apply Ambiguity

Dave Ballantyne

SSC-Dedicated

Points: 33566
More actions
March 26, 2010 at 8:04 am

#146233

3 hours of my time wasted by this stupid problem http://sqlblogcasts.com/blogs/sqlandthelike/archive/2010/03/26/cross-apply-ambiguity.aspx

Clear Sky SQL
My Blog[/url]
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

mister.magoo SSC-Forever Points: 47068 More actions · Answer 1

I have also come across this peculiarity, it is easier to see in this example

declare @a table(object_id int)

declare @b-2 table(object_id int)

Select a.object_id ,c.object_id

from @a as a

cross apply( Select *

from @b-2 as b where b.object_id = object_id) as c

The execution plan for this shows the problem very nicely:

StmtText

|--Nested Loops(Inner Join)

|--Table Scan(OBJECT : (@a AS [a]))

|--Table Scan(OBJECT : (@b AS ),

WHERE : (@b.[object_id] as .[object_id]=@b.[object_id] as .[object_id]))

Just look at the WHERE clause in the plan... it clearly shows that it will use the "local" object_id without complaint.

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

Garadin One Orange Chip Points: 29613 More actions · Answer 2

That one can be nasty. There's a very similar version that can bite you as well.

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 3

mister.magoo (3/26/2010)
I have also come across this peculiarity, it is easier to see in this example
declare @a table(object_id int)
declare @b-2 table(object_id int)
Select a.object_id ,c.object_id
from @a as a
cross apply( Select *
from @b-2 as b where b.object_id = object_id) as c
The execution plan for this shows the problem very nicely:
StmtText
|--Nested Loops(Inner Join)
|--Table Scan(OBJECT : (@a AS [a]))
|--Table Scan(OBJECT : (@b AS ),
WHERE : (@b.[object_id] as .[object_id]=@b.[object_id] as .[object_id]))
Just look at the WHERE clause in the plan... it clearly shows that it will use the "local" object_id without complaint.

While I do see the problem - isn't this a problem with any subquery (i.e. not specific to CROSS APPLY) ? Without fully qualifying every instance of object_id you will always run into a problem.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

mister.magoo SSC-Forever Points: 47068 More actions · Answer 4

Matt Miller (#4) (3/26/2010)
While I do see the problem - isn't this a problem with any subquery (i.e. not specific to CROSS APPLY) ? Without fully qualifying every instance of object_id you will always run into a problem.

Yes and no...

Yes you could run into the problem, and no, it is not limited to CROSS APPLY.

This particular problem is "nasty" because it does not consider the non-qualified column name to be ambiguous, and people might not realise what it is doing - as happened here to initiate the warning post!

I agree with the sentiment - always qualify the names - and you won't have the problem.

🙂

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

Paul White SSC Guru Points: 150467 More actions · Answer 5

Dave Ballantyne (3/26/2010)
3 hours of my time wasted by this stupid problem http://sqlblogcasts.com/blogs/sqlandthelike/archive/2010/03/26/cross-apply-ambiguity.aspx

Brad Schultz's comment on your blog entry summarises my views on this nicely.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Jeff Moden SSC Guru Points: 1003872 More actions · Answer 6

And now we know why I've gotten in the habit of aliasing all column names. Out of habit, it's even bled over into queries that only have one table.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Paul White SSC Guru Points: 150467 More actions · Answer 7

Jeff Moden (3/27/2010)
And now we know why I've gotten in the habit of aliasing all column names. Out of habit, it's even bled over into queries that only have one table.

Me too. The other advantage to doing it in 2008 is that typing the alias, and then a dot (or period) brings up the column list for the table (IntelliSense). If you don't alias the table, and just start typing column names, the list produced contains lots of non-column entities...again, using the alias just makes things easier, and encourages good practices like fully listing column names.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Garadin One Orange Chip Points: 29613 More actions · Answer 8

Paul White NZ (3/27/2010)
Jeff Moden (3/27/2010)
And now we know why I've gotten in the habit of aliasing all column names. Out of habit, it's even bled over into queries that only have one table.
Me too. The other advantage to doing it in 2008 is that typing the alias, and then a dot (or period) brings up the column list for the table (IntelliSense). If you don't alias the table, and just start typing column names, the list produced contains lots of non-column entities...again, using the alias just makes things easier, and encourages good practices like fully listing column names.

Darn it... now I just have to link it.

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

Dave Ballantyne SSC-Dedicated Points: 33566 More actions · Answer 9

Brad is obviously spot on in his comment. I cant deny that this is down to anything else other that 'user' error. 🙂 It is a rookie mistake , and i hang my head in shame ;-).

Fully qualifying names would of saved a good deal of trouble and maybe it is expecting to much of the engine to save me from my own stupidity at each and every turn. I do think though that an ansi warning style message would be fairly easy to implement within the parser, perhaps an error would be to much. Lots of perfectly good code broken.

Clear Sky SQL
My Blog[/url]