Inconsistent behavior with `CROSS APPLY` and` OUTER APPLY`

Question

I have a schema in Oracle-12c similar to a regular forum with accounts , posts , comments . I am writing one request to get ...

• single user
• all user posts
• comments on each of these posts
• and the author of each comment.

The request is as follows:

 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 use CROSS APPLY instead of the usual JOIN, because I will add OFFSET and FETCH for further pagination. However, the problem is that CROSS APPLY omits posts that have no comments, which I don't want. I want to keep 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 union complains that I refer to "p"."id" the results of the first. But this is great when I used CROSS APPLY .

What's happening? Why is there such a difference in behavior between them?

EDIT: OUTER APPLY may not seem necessary in this basic example. This is distilled from a more complex scenario in which I must insist that OUTER APPLY really necessary, but the details of this scenario are not related to the real question that I am asking - regarding this seemingly undocumented difference in behavior between CROSS and OUTER APPLY .

EDIT:

Oracle Version: Database 12c Standard Version Release 12.1.0.2.0 - 64bit Production

Client: Oracle SQL Developer version 4.2.0.16.356

Server: uname -a output - Linux ubuntu-1gb-sfo2-01 4.4.0-64-generic #85-Ubuntu SMP Mon Feb 20 11:50:30 UTC 2017 x86_64 x86_64 x86_64 GNU/Linux

DDL: link

Answer 1

In response to CodeFuller, I just add that (A) there is an available patch for this error, and (B) there is a working SQL file that works in 12.1.0.2, although I do not know if this will serve your purpose.

The workaround is to basically nest your connections, for example:

 SELECT accounts.*, p.*, author.* FROM accounts CROSS APPLY (SELECT posts.id, posts.author_id, posts.text, comments.comment_author_id, comments.comment_text FROM posts OUTER APPLY (SELECT comments.author_id comment_author_id, comments.text comment_text FROM comments WHERE comments.post_id = posts.id) comments WHERE posts.author_id = accounts.id) p LEFT JOIN accounts author ON author.id = p.comment_author_id WHERE accounts.id = 1; ID NAME ID_1 AUTHOR_ID TEXT COMMENT_AUTHOR_ID COMMENT_TEXT ID_2 NAME_1 ---- --------- ---- --------- ------------------------------------------------- ----------------- --------------------------------------- ----- ------------------- 1 Fred 1 1 Fred wrote this and it has comments 3 This is Helen comment on Fred post 3 Helen 1 Fred 2 1 Fred wrote this and it does not have any comments -------- End of Data -------- 2 row(s) fetched 

Ref: DDL table to crawl

 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' comment on Fred' post'); 

Answer 2

There is nothing wrong with your request. You have encountered errors 20356733/21547130, which were introduced in 12.1.0.2, as described here . To overcome this, use the version up to 12.1.0.2 or apply the latest updates (a link to fix the patch is available in patch update 12.1.0.2.160419).

This answer was found mainly by Matthew McPeak and Martin Smith . First I tried, as described below, and found out that the problem is not reproducible on Oracle 12.1.0.1.

First attempt to answer:

I created a test database with your schema and both queries worked fine for me. The first of them does not return messages without comments, and the second returns all account entries without any ORA-00904 error. I did this test on Oracle 12c.

To continue your question:

• Try to copy / paste and execute the second request from your message. Sometimes in queries small unpleasant typos make their way. The exact request from your message works for me, as expected.

• If you still get the same error, specify the DDL that you use to create the accounts , posts and comments tables.

• Indicate which SQL client you are using. The error is certainly on the server side, but in such strange cases, every little detail can make a difference.

My test database:

 CREATE TABLE "accounts" ( "id" NUMBER(11) NOT NULL, "name" NVARCHAR2(256), CONSTRAINT ACCOUNTS_PK PRIMARY KEY ("id") ) / CREATE TABLE "posts" ( "id" NUMBER(11) NOT NULL, "author_id" NUMBER(11) NOT NULL, "post_text" NVARCHAR2(1024), CONSTRAINT POSTS_PK PRIMARY KEY ("id"), CONSTRAINT POST_ACCOUNT_FK FOREIGN KEY ("author_id") REFERENCES "accounts" ("id") ON DELETE CASCADE ) / CREATE TABLE "comments" ( "id" NUMBER(11) NOT NULL, "author_id" NUMBER(11) NOT NULL, "post_id" NUMBER(11) NOT NULL, "comment_text" NVARCHAR2(1024), CONSTRAINT COMMENTS_PK PRIMARY KEY ("id"), CONSTRAINT COMMENT_ACCOUNT_FK FOREIGN KEY ("author_id") REFERENCES "accounts" ("id") ON DELETE CASCADE, CONSTRAINT COMMENT_POST_FK FOREIGN KEY ("post_id") REFERENCES "posts" ("id") ON DELETE CASCADE ) / INSERT INTO "accounts"("id", "name") VALUES(1, 'testuser') / INSERT INTO "posts"("id", "author_id", "post_text") VALUES(1, 1, 'First test post') / INSERT INTO "posts"("id", "author_id", "post_text") VALUES(2, 1, 'Second test post') / INSERT INTO "comments"("id", "author_id", "post_id", "comment_text") VALUES(1, 1, 2, 'It is a very cool post') / COMMIT