sql – 使用`CROSS APPLY`和`OUTER APPLY`的行为不一致

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

>一个用户
>所有用户的帖子
>对每个帖子的评论
>和每个评论的作者.

查询如下所示：

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

此查询按预期工作.我正在使用CROSS APPLY而不是典型的JOIN,因为稍后我将添加OFFSET和FETCH以进行分页.但是,问题是CROSS APPLY省略了没有评论的帖子,这是我不想要的.我想在结果中保留帖子,即使他们没有评论.

所以我尝试将CROSS APPLY更改为OUTER APPLY.

select "accounts".*,"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

但是现在我收到了这个错误：

ORA-00904: "p"."id": invalid identifier
00904. 00000 -  "%s: invalid identifier"
*Cause:    
*Action:
Error at Line: 9 Column: 34

出于某种原因,我的第二个OUTER APPLY join抱怨我引用第一个结果中的“p”.“id”.但是当我使用CROSS APPLY时它很好.

到底是怎么回事？为什么这些之间的行为存在差异？

编辑：在这个基本示例中似乎没有必要使用OUTER APPLY.这是从一个更复杂的场景中提炼出来的,我必须坚持认为外部应用确实是必要的,但是这个场景的细节与我所问的实际问题无关 – 这与CROSS和CROSS之间看似无证的行为差异有关.外部申请.

编辑：

Oracle版本：Database 12c标准版12.1.0.2.0版 – 64位生产

客户端：Oracle sql Developer版本4.2.0.16.356

服务器：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

解决方法

至于 CodeFuller’s回答,我只想补充一点(A)有一个可用于此bug的补丁和(B)有一个可在12.1.0.2中运行的解决方法,但我不知道它是否能满足您的需求.

解决方法是基本嵌套连接,如下所示：

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

参考：表DDL的变通方法

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,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,text)
VALUES (2,'Fred wrote this and it does not have any comments');
 
INSERT INTO posts (id,text)
VALUES (3,4,'Iqbal wrote this and it does not have any comments');
 
INSERT INTO comments (id,post_id,3,'This is Helen''s comment on Fred''s post');

sql – 使用`CROSS APPLY`和`OUTER APPLY`的行为不一致

解决方法

猜你在找的MsSQL相关文章