SQL基础知识不扎实导致的编程错误 - l1t1/note GitHub Wiki

在测试各种数据库对递归CTE的支持程度时无意发现，如下SQL会导致无限循环或OOM报错，以为是数据库的问题，但不同数据库怎么都这么实现？查阅了SQL国际标准，其实是我的错。

with recursive t(i,lv) as(select generate_series i,1 lv from generate_series(1,6,1)
union all
select max(i),max(lv)+1 from t where lv<2)
select * from t where lv=2;

我以为上述SQL应该返回一行6, 2, 因为一轮递归后的结果集中不含lv<2的行，就会终止递归。

实际上，聚合函数max有个性质，当无满足条件的行时，返回NULL。

SQL国标征求意见稿 中写到：

如果没有行符合条件,则 COUNT 的结果为 0,任何其他聚合函数的结果均为空值。

所以，

第一轮递归select max(i),max(lv)+1 from(select 6 i, 2 lv)t where lv<2的结果是：NULL,NULL。

下一轮递归select max(i),max(lv)+1 from(select NULL i,NULL lv)t where lv<2的结果还是：NULL,NULL，于是造成无限递归。

要避免无限递归，可以这么改写：

with recursive t(i,lv) as(select generate_series i,1 lv from generate_series(1,6,1)
union all
select max(i),max(lv)+1 from t having max(lv)<2)
select * from t where lv=2;

having是对聚合后结果的过滤，因此第2轮递归消除了为NULL的行，结果没有产生新行，递归终止。

在受支持的数据库上可以用union代替union all, 如下：

with recursive t(i,lv) as(select generate_series i,1 lv from generate_series(1,6,1)
union
select max(i),max(lv)+1 from t where lv<2)
select * from t where lv=2;

union 将本轮的结果NULL,NULL和已有结果去重复合并，结果第3轮递归没有产生新行，因此递归终止，如果最后查询select * from t，能看到结果包含一行NULL,NULL。