最近处理了一个connect by的case，分享给大家一下。
首先介绍一下connect by作用：对于数据有着严密的层级关系的表，我们有时候希望能够把有着
父子关系或者叫上下级关系的数据一次性展现出来，这个时候传统的sql 语法并不能就解决问题，
例如一个部门有一个总经理，多个副经理，每个下面又有多个总监，总监下面是员工，
我们设计表的时候，肯定只有一个字段来记录员工的上级，并不会记录他的上上级，那么我们
想把某个副经理的下面的所有员工都列出来的时候，就存在递归查找底层员工的情况，这种
就需要用到递归遍历，不同的DB给出了不同的解决办法，如DB2可以使用with+内嵌递归逻辑的
sql实现，oracle 提供了connect by的语法来实现。下面简单的看个例子：
drop table emp;
create table emp( emp_no number , manager_no number, name varchar2(100));
insert into emp values(1,,’总经理’);
insert into emp values(2,1,’副经理1′);
insert into emp values(3,1,’副经理2′);
insert into emp values(4,2,’总监1′);
insert into emp values(5,3,’总监2′);
insert into emp values(6,4,’员工6′);
insert into emp values(7,4,’员工7′);
insert into emp values(8,5,’员工8′);
insert into emp values(9,5,’员工9′); 
commit;
 
1个总经理两个副经理，每个副经理都有一个总监，每个总监都有两个员工。
下面列出副总1的所有员工情况:
select * from emp   start with emp_no=2
connect by prior emp_no=manager_no
 
2 1 副经理1  <<起始行
4 2 总监1
6 4 员工6
7 4 员工7
这就sql的原理就是，start with标识递归遍历的起点，从emp_no=2这条记录开始遍历，prior 标识子节点和父节点关系，
可以理解成这个是一个函数，传入参数字段求值就可以得到他的父节点，根据emp_no父亲是在manager_no里面记录这种父子关系
向下遍历，那么就找第二条总监1，进而递归遍历找到员工6，7两条记录。
我们都知掉，任何编程语言递归遍历可能就存在死循环的情况，类似C语言中的goto滥用就会出现死循环一样，oracle在解决
这种递归的时候一旦发现死循环就会报错，例如下面的例子：
 
insert into emp values(2,7,’副经理1′);  <<我们在插入一条让其副经理1是下面总监1的员工7的下属（这显然是矛盾的)
 
SQL>insert into emp values(2,7,’副经理1′);
 
select * from emp   start with emp_no=2
connect by prior emp_no=manager_no
1 row created.
 
SQL> SQL>   2  ;
ERROR:
ORA-01436: CONNECT BY loop in user data 
no rows selected
 
从上面看，的确发现了死循环，因为按照上面的逻辑，先列出前4条记录
2 1 副经理1  <<起始行
4 2 总监1
6 4 员工6
7 4 员工7   <<前4行记录
然后记录遍历：发现7的下级记录
2 7 副经理1′
然后在找2的下级记录，又发现
4 2 总监1 
进而找到6，7，这样就无限的循环了。
那么若是因为突然插入这一条违背逻辑的数据就造成SQL报错，程序异常就没有办法补救了么，例如很显然，
我们就在死循环前停止，前面正常的数据正常输出即可，其实oracle的确已经帮我们想到了这个问题，我们可以
通过nocycle关键字来规避，这个参数的意思就是不要进入死循环，进入前停止。下面看看添加后的效果
select * from emp   start with emp_no=2
connect by nocycle prior emp_no=manager_no
 
2 1 副经理1  
4 2 总监1
6 4 员工6
7 4 员工7  结果和我们期望的一致，显示正常的结果。
 
有了上述基础知识之后，我们再来看一下另外一个真实的case
drop table t1;
create table t1 (id int, fst_child int, snd_child int);
 
–base data
insert into t1 values (1, 2, NULL);
insert into t1 values (2, 3, 4);
insert into t1 values (3, 5, NULL);
insert into t1 values (4, 5, NULL);
insert into t1 values (5, 6, NULL);
insert into t1 values (6, 2, NULL);
 
SELECT id, fst_child, nvl(snd_child,99999) snd_child, connect_by_iscycle, SYS_CONNECT_BY_PATH(id, ‘/’) “Path”
from t1
start with id = 1
connect by nocycle ((id = prior fst_child) or (id = prior snd_child));
这个结果返回如下：
 
id  fst_child   snd_child connect_by_iscycle   Path
1    2         99999     0        /1  
2    3         4              0        /1/2  
3    5         99999     0        /1/2/3  
5    6         99999     1        /1/2/3/5 
4    5         99999     0        /1/2/4  
5    6         99999     1        /1/2/4/5  
 
客户发现当然把最后一条记录 由2变成4 之后
insert into t1 values (6, 4, NULL);
数据变成如下：
id  fst_child   snd_child connect_by_iscycle   Path
1    2   99999     0          /1 
2    3       4     0              /1/2 
3    5   99999     0          /1/2/3  
5    6   99999     0          /1/2/3/5 
6    4   99999     1          /1/2/3/5/6  <<新多出记录
4    5   99999     0          /1/2/4 
5    6   99999     0          /1/2/4/5 
6    4   99999     1          /1/2/4/5/6 <<新多出记录
 
结果多出两条记录出来，从上述知识来看最后一条无论是2还是4都是出现了死循环所以，造成了connect_by_iscycle返回1，
遍历停止，那么为啥后者能遍历出6，4这条记录，而前者不能遍历出6，2呢？因为2是6的祖先，4也是6的祖先，为啥
停止位置不同呢？难道是遇到了bug？经过在11204和12.2两个最稳定版本上测试，效果都是一样，
客户版本是11201，所以初步排除了bug的可能性,因为wrong result这种高优先级bug是不太可能跨越这两个版本仍然没有fix的。
那原因只能是一个，也就是这个结果是合理的。
再来重温一下connect_by_iscycle的解释：
The CONNECT_BY_ISCYCLE pseudocolumn returns 1 if the current row has a child which is also its ancestor. Otherwise it returns 0.
如果当前行含有一个child，而且这个child又是他的祖先，那么就返回1，否则返回0.
 
我们来看一下第一个场景，
insert into t1 values (1, 2, NULL);
insert into t1 values (2, 3, 4);
insert into t1 values (3, 5, NULL);
insert into t1 values (4, 5, NULL);
insert into t1 values (5, 6, NULL);
insert into t1 values (6, 2, NULL);
当走到（5，6） 的时候 也就是child节点6的时候,发现有child 行（6，2），而这行表名2是6的子节点，但是根据
之前的遍历，2是3的祖先，3是5的祖先，5是6的祖先，自然2是6的祖先，所以connect_by_iscycle返回1，这个是合情合理。
再来看一下第二个场景
insert into t1 values (1, 2, NULL);
insert into t1 values (2, 3, 4);
insert into t1 values (3, 5, NULL);
insert into t1 values (4, 5, NULL);
insert into t1 values (5, 6, NULL);
insert into t1 values (6, 4, NULL);
当走到（5，6） 的时候 也就是child节点6的时候,发现有child 行（6，4），而这行表名4是6的子节点，但是根据第四行（4，5）
5是4的子节点，6是5的子节点，显然6也是4的子节点，也违反逻辑，也应该connect_by_iscycle返回1才对，这个是为什么？
其实这里面存在一个误导，我们在考虑这个递归逻辑的时候是看到了所有数据，但是计算机是严格的一步一步递归的，也就是
当走到（5，6）的时候会，的确会进一步check下一行（6，4）情况，发现4是6的子节点，但是这个时候他是看不到（4，5）这一行的
所以他不知道5是4的子节点，所以他认为（6，4）是有效的，所以进行了输出，然后check他的子节点（4，5）发现5是4的子节点，
这个就有问题了，因为之前遍历的时候知道，5是6的祖先，6是4的祖先，所以5一定是4的祖先，所以进入时候死循环，那么本记录
标记成connect_by_iscycle=1，遍历停止，所以本场景会多遍历一层，这也就是为啥和场景1不同的原因。