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

Question

I 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.

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.

EDIT:

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

Client: Oracle SQL Developer Version 4.2.0.16.356

Server: output of uname -a - 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

To CodeFuller's answer, I would just add that (A) there is a patch available for this bug and (B) there is a work-around SQL that works in 12.1.0.2, though I do not know whether it will serve your purposes.

The workaround is to basically nest your joins, like so:

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's comment on Fred's post      3 Helen                                
   1 Fred         2         1 Fred wrote this and it does not have any comments
-------- End of Data --------
2 row(s) fetched

Reference: table DDL for workaround

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');

Answer 2

There is nothing wrong with your query. You've faced bugs 20356733/21547130, that were introduced in 12.1.0.2, as described here. In order to overcome it, use version prior to 12.1.0.2 or apply latest updates (Linked thread claims that fix is available in 12.1.0.2.160419 patch set update).

This answer was found mostly by Matthew McPeak and Martin Smith. I've just made first try as described below and found out that issue is not reproducible on Oracle 12.1.0.1.

First answer attempt:

I've created test database with your schema and both queries worked OK for me. The first one does not return posts without comments, and the second one returns all account posts, without any ORA-00904 error. I've made this test on Oracle 12c.

To proceed with your question:

1. Try to copy/paste and execute 2nd query from your post. Sometimes little nasty typos sneak into the queries. Exact query from your post works for me as expected.

2. If you're still getting the same error, please provide DDL you're using for accounts, posts and comments tables creation.

3. Please specify what SQL client you're using. The error is surely on server side, but in such strange cases every little detail can make the 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