This question is also asked on
http://stackoverflow.com/questions/42593148/inconsistent-behavior-with-cross-apply-and-outer-apply by a Graphql collaborator who is developing pagination for supporting Oracle db.
have a schema in Oracle-12c similar to a typical forum with accounts, posts, comments. I am writing a single query to get...
1. one user
2. all that user's posts
3. the comments on each of those posts
4. and the author of each comment.
5. The query looks like this:
select "accounts".*, "p".*, "c".*, "author".*
from "accounts"
cross apply (
select * from "posts"
where "posts"."author_id" = "accounts"."id"
) "p"
cross apply (
select * from "comments"
where "comments"."post_id" = "p"."id"
) "c"
left join "accounts" "author" on "author"."id" = "c"."author_id"
where "accounts"."id" = 1
This query works as expected. I'm using CROSS APPLY instead of a typical JOIN because I'll be adding OFFSET and FETCH to paginate later on. However, the problem is that CROSS APPLY omits the posts that have no comments, which I don't want. I want to keep the posts in the results even if they have no comments.
So I tried changing CROSS APPLY to OUTER APPLY.
select "accounts".*, "p".*, "c".*, "author".*
from "accounts"
outer apply (
select * from "posts"
where "posts"."author_id" = "accounts"."id"
) "p"
outer apply (
select * from "comments"
where "comments"."post_id" = "p"."id"
) "c"
left join "accounts" "author" on "author"."id" = "c"."author_id"
where "accounts"."id" = 1
But now I get this error:
ORA-00904: "p"."id": invalid identifier
00904. 00000 - "%s: invalid identifier"
*Cause:
*Action:
Error at Line: 9 Column: 34
For some reason, my second OUTER APPLY join is complaining about me referencing "p"."id" from the results of the first one. But its fine when I was using CROSS APPLY.
What is going on? Why is there this difference in behavior between these?
EDIT: OUTER APPLY may not seem necessary in this basic example. This has been distilled from a more complex scenario in which I must insist that OUTER APPLY is indeed necessary, but the details of that scenario are irrelevant to the actual question I'm asking—which is about this seemingly undocumented difference in behavior between CROSS and OUTER APPLY.
SO not good enough for you, eh? ;)
As the answers there state, this is due to a bug. If you're not able to access the 12.1 patch, it's also fixed in 12.2 which you can download now! :)
http://www.oracle.com/technetwork/database/enterprise-edition/downloads/index.html So the difference is not "documented" because it's not supposed to work that way!
But I'm not sure why you think you have to use outer apply as opposed to an outer join. Using the test case in the SO answer, they give the same result:
CREATE TABLE accounts
(
id NUMBER PRIMARY KEY,
name VARCHAR2 (30)
);
CREATE TABLE posts
(
id NUMBER PRIMARY KEY,
author_id NUMBER,
text VARCHAR2 (240)
);
CREATE TABLE comments
(
id NUMBER PRIMARY KEY,
post_id NUMBER,
author_id NUMBER,
text VARCHAR2 (240)
);
INSERT INTO accounts (id, name)
VALUES (1, 'Fred');
INSERT INTO accounts (id, name)
VALUES (2, 'Mary');
INSERT INTO accounts (id, name)
VALUES (3, 'Helen');
INSERT INTO accounts (id, name)
VALUES (4, 'Iqbal');
INSERT INTO posts (id, author_id, text)
VALUES (1, 1, 'Fred wrote this and it has comments');
INSERT INTO posts (id, author_id, text)
VALUES (2, 1, 'Fred wrote this and it does not have any comments');
INSERT INTO posts (id, author_id, text)
VALUES (3, 4, 'Iqbal wrote this and it does not have any comments');
INSERT INTO comments (id,
post_id,
author_id,
text)
VALUES (1,
1,
3,
'This is Helen''s comment on Fred''s post');
select accounts.*, p.*, c.*,
author.*
from accounts
outer apply ( select * from posts where posts.author_id = accounts.id
) p
outer apply ( select * from comments where comments.post_id = p.id
) c
left join accounts author
on author.id = c.author_id
where accounts.id = 1;
ID NAME ID AUTHOR_ID TEXT ID POST_ID AUTHOR_ID TEXT ID NAME
1 Fred 1 1 Fred wrote this and it has comments 1 1 3 This is Helen's comment on Fred's post 3 Helen
1 Fred 2 1 Fred wrote this and it does not have any comments
select accounts.*, p.*, c.*,
author.*
from accounts
left join posts p
on p.author_id = accounts.id
left join comments c
on c.post_id = p.id
left join accounts author
on author.id = c.author_id
where accounts.id = 1;
ID NAME ID AUTHOR_ID TEXT ID POST_ID AUTHOR_ID TEXT ID NAME
1 Fred 1 1 Fred wrote this and it has comments 1 1 3 This is Helen's comment on Fred's post 3 Helen
1 Fred 2 1 Fred wrote this and it does not have any comments
And Oracle Database converts outer apply to outer joins. Looking at the 10053 trace, you can see the outer apply becomes:
select "ACCOUNTS"."ID" "ID","ACCOUNTS"."NAME" "NAME","POSTS"."ID" "ID",
"POSTS"."AUTHOR_ID" "AUTHOR_ID","POSTS"."TEXT" "TEXT","COMMENTS"."ID" "ID",
"COMMENTS"."POST_ID" "POST_ID","COMMENTS"."AUTHOR_ID" "AUTHOR_ID","COMMENTS"."TEXT" "TEXT",
"AUTHOR"."ID" "ID","AUTHOR"."NAME" "NAME"
from "CHRIS"."ACCOUNTS" "ACCOUNTS","CHRIS"."POSTS" "POSTS","CHRIS"."COMMENTS" "COMMENTS",
"CHRIS"."ACCOUNTS" "AUTHOR"
where "ACCOUNTS"."ID" =1
and "AUTHOR"."ID"(+) ="COMMENTS"."AUTHOR_ID"
and "COMMENTS"."POST_ID"(+)="POSTS"."ID"
and "POSTS"."AUTHOR_ID"(+) ="ACCOUNTS"."ID"
and "POSTS"."AUTHOR_ID"(+) =1
So if you think what you're trying to do
requires outer apply, we'll need to see a test case showing this. This
must include:
- Create table statements
- Insert statements
- The output you expect from your query