多表之间使用oracle(+)的语法，替换成左、右外连接的形式等价测试

问题描述

目前对于单表的oracle(+)语法转换成postgres是支持的。下面两个sql等价：

1. select host.id from itr_host_v host, itr_ci_ref r where host.id = r. to_ci(+)
   and r.from_template_id =
   ‘0301010101’;
2. select host.id from itr_host_v host left join itr_ci_ref r on host.id = r .to_ci
   where r.from_template_id =
   ‘0301010101’ ;
   （+）标注的位置即为内表。
   目前问题：多表之间使用oracle(+)的语法，替换成左、右外连接的形式等价测试。

实验

目前使用统一sql脚本，oracle和lightdb两套数据库执行，结果进行比较。（有时间的话，我们也会测试gp数据库结果情况）

实验准备

准备建表语句

create table hs_lightDB(Pro_lightdb_version_number bigint, Pro_lightdb_release_date date, Pro_lightdb_publisher varchar(100), Pro_lightdb_sales bigint);
create table hs_em(Pro_em_version_number bigint, Pro_em_release_date date, Pro_em_publisher varchar(100), Pro_em_sales bigint);
create table hs_O45(Pro_O45_version_number bigint, Pro_O45_release_date date, Pro_O45_publisher varchar(100), Pro_O45_sales bigint);

INSERT INTO hs_lightDB 	VALUES (202301, TO_DATE('2023-1-31 18:00:00','YY-MM-DD HH24:MI:SS'), 'zjh', '100000000');
INSERT INTO hs_lightDB 	VALUES (202302, TO_DATE('2023-2-28 18:00:00','YY-MM-DD HH24:MI:SS'), 'zjh', '200000000');
INSERT INTO hs_lightDB 	VALUES (202303, TO_DATE('2023-3-31 18:00:00','YY-MM-DD HH24:MI:SS'), 'zjh', '300000000');

INSERT INTO hs_em 	VALUES (202301, TO_DATE('2023-1-31 18:00:00','YY-MM-DD HH24:MI:SS'), 'zjh', '100000000');
INSERT INTO hs_em 	VALUES (202302, TO_DATE('2023-2-28 18:00:00','YY-MM-DD HH24:MI:SS'), 'zjh', '200000000');
INSERT INTO hs_em 	VALUES (202303, TO_DATE('2023-3-31 18:00:00','YY-MM-DD HH24:MI:SS'), 'zjh', '300000000');

INSERT INTO hs_O45 	VALUES (202301, TO_DATE('2023-1-31 18:00:00','YY-MM-DD HH24:MI:SS'), 'zjh', '100000000');
INSERT INTO hs_O45 	VALUES (202302, TO_DATE('2023-2-28 18:00:00','YY-MM-DD HH24:MI:SS'), 'zjh', '200000000');
INSERT INTO hs_O45 	VALUES (202303, TO_DATE('2023-3-31 18:00:00','YY-MM-DD HH24:MI:SS'), 'zjh', '300000000');

准备外连接语句
oracle：

statement1:
select a.Pro_lightdb_version_number, b.Pro_em_release_date, c.Pro_O45_publisher from hs_lightDB a, hs_em b, hs_O45 c where 1 = 1 and a.Pro_lightdb_version_number(+) = c.Pro_O45_version_number
and b.Pro_em_publisher = c. Pro_O45_publisher(+);

statement2:
select a.Pro_lightdb_version_number, b.Pro_em_release_date, c.Pro_O45_publisher from hs_lightDB a right join hs_O45 c on a.Pro_lightdb_version_number = c.Pro_O45_version_number right join 
hs_em b on b.Pro_em_publisher = c.Pro_O45_publisher where 1 = 1;

postgres：

select a.Pro_lightdb_version_number, b.Pro_em_release_date, c.Pro_O45_publisher from hs_lightDB a right join hs_O45 c on a.Pro_lightdb_version_number = c.Pro_O45_version_number right join 
hs_em b on b.Pro_em_publisher = c.Pro_O45_publisher where 1 = 1 order by a.Pro_lightdb_version_number asc;

执行结果

oracle:
statement:

statement2:

postgres:
不加order by

不加order by扫描结果与oracle存在排序的差异。
加order by

总结

对比postgres使用order by 排序与不排序存在的差异的执行计划。

问题

oracle在执行过程发现表存在封闭的情况下，会报错。
封闭指的是a->b->c->a：