具有递归 SQL 查询的无限循环

Question

我似乎找不到此查询中无限循环背后的原因，也找不到如何纠正它。

这是上下文： 我有一个名为 mergesWith 的表带有以下描述： 合并：有关邻近海域的信息。请注意，在这种关系中，对于每对 相邻的海域(A，B(，只给出一个元组 - 因此，关系不对称。 海1：海 海2：一片海。

我想知道通过导航从地中海进入的每个海域。我选择了使用"with"的递归查询：

With 
acces(p,d) as (
select sea1 as p, sea2 as d
from MERGESWITH
UNION ALL
select a.p, case when mw.sea1=a.d 
then mw.sea2 
else mw.sea1 
end as d
from acces a, MERGESWITH mw
where a.d=mw.sea1 or a.d=mw.sea2)
select d
from acces
where p= 'Mediterranean Sea';

我认为原因要么是限制不够case when，要么是a.d=mw.sea1 or a.d=mw.sea2，但我似乎无法确定原因。

我收到此错误消息：32044. 00000 - "执行递归 WITH 查询时检测到周期" *原因：递归 WITH 子句查询生成循环并已停止 以避免无限循环。 *操作：重写递归 WITH 查询以停止递归或使用 周期条款。

Answer 1

周期是由查询的结构引起的，而不是由数据中的周期引起的。你问骑自行车的原因。这应该是显而易见的：在第一次迭代时，一行输出具有d = 'Aegean Sea'。在第二次迭代中，您会发现一行带有d = 'Mediterranean Sea'，对吧？您现在能看到这将如何导致周期吗？

递归查询有一个cycle子句，专门用于此类问题。出于某种原因，即使是许多很好地学习了递归with子句并一直使用它的用户，似乎也不知道cycle子句(以及用于排序输出的不相关但同样有用的search子句(。

在代码中，需要进行两项更改。添加cycle子句，并在外部查询筛选器中仅针对非周期行添加子句。在cycle子句中，您可以决定如何称呼"cycle"列，以及为其提供哪些值。为了使这看起来尽可能类似于connect by查询，我喜欢调用新列IS_CYCLE并为其提供值 0(表示无周期(和 1(表示循环(。在下面的外部查询中，将is_cycle添加到select列表中，以查看它向递归查询中添加的内容。

请注意cycle子句的位置：它紧跟在递归with子句之后(特别是递归分解子查询末尾的右括号之后(。

with 
acces(p,d) as (
select sea1 as p, sea2 as d
from   MERGESWITH
UNION ALL
select  a.p, case when mw.sea1=a.d 
then mw.sea2 
else mw.sea1 
end  as d
from   acces a, MERGESWITH mw
where  a.d=mw.sea1 or a.d=mw.sea2)
cycle  d set is_cycle to 1 default 0           -- add this line
select d
from   acces
where  p= 'Mediterranean Sea'
and  is_cycle = 0                                   -- and this line
;

Answer 2

显然，由于数据中的循环，这将依赖于数据。 在开发递归 CTE 时，我通常会包含一个lev值。 这使得调试它们变得更加简单。

因此，请尝试以下操作：

with acces(p, d, lev) as (
select sea1 as p, sea2 as d, 1 as lev
from MERGESWITH
union all
select a.p,
(case when mw.sea1 = a.d then mw.sea2 else mw.sea1 end) as d,
lev + 1
from acces a join
MERGESWITH mw
on a.d in (mw.sea1, mw.sea2)
where lev < 5)
select d
from acces
where p = 'Mediterranean Sea';

如果找到原因但无法修复代码，请提出包含示例数据和所需结果的新问题。 某种DB小提琴也很有帮助。