【LightDB-A】 23.2 oracle connect by兼容

connect by

Connect by是oracle常用语法，用于层次查询。LightDB-A为了方便用户迁移，支持了这一语法特性。目前已支持一下场景：

connect by level

connect by level常用于批量生成数据。示例如下：

select  level as id, level + 1000  from dual connect by level < 10;

运行结果如下：

oracle=# select  level as id, level + 1000  from dual connect by level < 10;
 id | ?column? 
----+----------
  1 |     1001
  2 |     1002
  3 |     1003
  4 |     1004
  5 |     1005
  6 |     1006
  7 |     1007
  8 |     1008
  9 |     1009
(9 rows)

树查询

树形查询也是比较常见的查询，如：公司的组织架构。示例如下：

--建表
CREATE TABLE emp_ (
empno NUMERIC(4) NOT NULL CONSTRAINT emp_pk PRIMARY KEY,
ename VARCHAR(10),
job VARCHAR(9),
mgr NUMERIC(4),
hiredate DATE,
sal NUMERIC(7,2),
comm NUMERIC(7,2),
deptno NUMERIC(2)
);
-- 设置日期格式
set DateStyle to "ISO, MDY";
-- 插入数据
INSERT INTO emp_ VALUES (7369,'SMITH','CLERK',7902,'17-DEC-80',800,NULL,20);
INSERT INTO emp_ VALUES (7499,'ALLEN','SALESMAN',7698,'20-FEB-81',1600,300,30);
INSERT INTO emp_ VALUES (7521,'WARD','SALESMAN',7698,'22-FEB-81',1250,500,30);
INSERT INTO emp_ VALUES (7566,'JONES','MANAGER',7839,'02-APR-81',2975,NULL,20);
INSERT INTO emp_ VALUES (7654,'MARTIN','SALESMAN',7698,'28-SEP-81',1250,1400,30);
INSERT INTO emp_ VALUES (7698,'BLAKE','MANAGER',7839,'01-MAY-81',2850,NULL,30);
INSERT INTO emp_ VALUES (7782,'CLARK','MANAGER',7839,'09-JUN-81',2450,NULL,10);
INSERT INTO emp_ VALUES (7788,'SCOTT','ANALYST',7566,'19-APR-87',3000,NULL,20);
INSERT INTO emp_ VALUES (7839,'KING','PRESIDENT',NULL,'17-NOV-81',5000,NULL,10);
INSERT INTO emp_ VALUES (7844,'TURNER','SALESMAN',7698,'08-SEP-81',1500,0,30);
INSERT INTO emp_ VALUES (7876,'ADAMS','CLERK',7788,'23-MAY-87',1100,NULL,20);
INSERT INTO emp_ VALUES (7900,'JAMES','CLERK',7698,'03-DEC-81',950,NULL,30);
INSERT INTO emp_ VALUES (7902,'FORD','ANALYST',7566,'03-DEC-81',3000,NULL,20);
-- 查询公司BLAKE和其下属
SELECT LEVEL, empno, ename, mgr, sal
FROM emp_
CONNECT BY PRIOR empno = mgr
START WITH ename = 'BLAKE';

查询结果：

 level | empno | ename  | mgr  |   sal   
-------+-------+--------+------+---------
     1 |  7698 | BLAKE  | 7839 | 2850.00
     2 |  7654 | MARTIN | 7698 | 1250.00
     2 |  7499 | ALLEN  | 7698 | 1600.00
     2 |  7900 | JAMES  | 7698 |  950.00
     2 |  7844 | TURNER | 7698 | 1500.00
     2 |  7521 | WARD   | 7698 | 1250.00

connect by的限制

from子句不支持多表源

select * from a,b where a.id = b.id connect by prior id = pid;

使用join改写

select * from a join b on a.id = b.id connect by prior id = pid; 

from中join子句，不支持using子句

select a.*,b.* from a join b using(id) connect by prior id = pid;

使用on改写

select a.*,b.* from a join b on a.id = b.id connect by prior id = pid;

不支持与with query连用

with c as (select * from a)
select * from c connect by c.id = c.pid

使用子查询改写

select * from (select * from a) as c connect by c.id = c.pid

不支持和oracle外连接(+)一起使用

select a.*,b.* from a,b where a.id(+) = b.id connect by prior id = pid;

使用join改写

select a.*,b.* from a right join b on a.id = b.id connect by prior id = pid;

level,prior,connect_by_root,sys_connect_by_path不支持嵌套

prior,connect_by_root,sys_connect_by_path不支持嵌套求值，即：connect_by_root,sys_connect_by_path，prior的参数不能是prior,connect_by_root,sys_connect_by_path。
以下sql会报错：

select prior connect_by_root(a.id),connect_by_root(prior a.id) from a right join b on a.id = b.id connect by prior id = pid;

不支持connect by rownum

以下sql 报错：

select * from a connect by rownum <10

暂无改写方式

结果顺序有差别

LightDB-A使用的是广度优先遍历（层次遍历），Oracle是深度优先遍历（先序遍历），而结果集的顺序，取决于遍历顺序。举例如下：
LightDB-A中以下sql

SELECT LEVEL, empno, ename, mgr, sal
FROM emp_
CONNECT BY PRIOR empno = mgr
START WITH ename = 'BLAKE';

等同于oracle

SELECT LEVEL, empno, ename, mgr, sal
FROM emp_
CONNECT BY PRIOR empno = mgr
START WITH ename = 'BLAKE' order by level;