Oracle 交叉应用和外部应用的不一致行为

这个问题也是在http://stackoverflow.com/questions/42593148/inconsistent-behavior-with-cross-apply-and-outer-apply由Graphql合作者开发分页以支持Oracle db。

在Oracle-12c中具有类似于典型论坛的模式，其中包含帐户，帖子和评论。我正在编写一个查询来获取...

1.一个用户
2.所有那些用户的帖子
3.对每个员额的评论
4.和每个评论的作者。
5.查询看起来像这样:

选择 “帐户”。*，“p”。*，“c”。*，“作者”。*
来自 “账户”
交叉应用 (
从 “帖子” 中选择 *
其中 “帖子”。“author_id” = “帐户”。“id”
) “p”
交叉应用 (
从 “评论” 中选择 *
其中 “注释”。“post_id” = “p”。“id”
) “c”
在 “作者” 上左加入 “帐户” “作者”。“id” = “c”。“author_id”
其中 “帐户”。“id” = 1
此查询按预期工作。我使用交叉应用而不是典型的联接，因为我将在以后添加偏移量并提取到paginate。但是，问题是CROSS APPLY省略了没有评论的帖子，这是我不想要的。即使没有评论，我也希望将帖子保留在结果中。

所以我尝试将交叉应用更改为外部应用。

选择 “帐户”。*，“p”。*，“c”。*，“作者”。*
来自 “账户”
外部应用 (
从 “帖子” 中选择 *
其中 “帖子”。“author_id” = “帐户”。“id”
) “p”
外部应用 (
从 “评论” 中选择 *
其中 “注释”。“post_id” = “p”。“id”
) “c”
在 “作者” 上左加入 “帐户” “作者”。“id” = “c”。“author_id”
其中 “帐户”。“id” = 1
但现在我得到这个错误:

ORA-00904: "p"。"id": 无效标识符
00904. 00000-“% s: 无效标识符”
* 原因:
* 行动:
行错误: 9列: 34
由于某种原因，我的第二个外部APPLY join抱怨我从第一个结果引用 “p”.“id”。但是当我使用交叉应用的时候很好。

这是怎么回事？为什么这些之间的行为会有这种差异？

编辑: 在这个基本示例中，外部应用可能似乎没有必要。这是从一个更复杂的场景中提炼出来的，在这个场景中，我必须坚持认为外部应用确实是必要的，但该场景的细节与我要问的实际问题无关-这是关于交叉和外部应用之间这种看似没有记录的行为差异。

所以对你来说还不够好，嗯？;)

正如那里的答案所指出的那样，这是由于错误造成的。如果您无法访问12.1补丁，则它也已修复，您可以立即下载该12.2!:)

http://www.oracle.com/technetwork/database/enterprise-edition/downloads/index.html

所以区别不是 “记录在案的”，因为它不应该那样工作!

但是我不确定为什么您认为必须使用外部应用而不是外部连接。使用SO答案中的测试用例，他们给出了相同的结果:

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                                                                             
复制

和Oracle数据库将外部应用转换为外部联接。查看10053跟踪，您可以看到外部应用变为:

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
复制

所以如果你认为你想做什么requires外部应用，我们需要看到一个测试用例显示这一点。这个must包括:

-创建表语句
-插入语句
-您期望从查询中获得的输出