奇怪的Oracle行为-递归CTE中的交叉联接使用逗号，但不能与交叉联接子句一起使用 [英] Weird Oracle behaviour - cross join in recursive CTE works with comma but not with cross join clause

问题描述

假设以下简单的递归查询 r 列出几个数字.当查询的递归部分使用逗号分隔的旧方法与不必要的1行表交叉连接时，一切正常(

Suppose following simple recursive query r to list several numbers. When recursive part of query is cross joined with unnecessary 1-row table using old way with comma separation, everything works fine (dbfiddle):

with r (x) as (
  select 1 as x from dual
  union all
  select x + 1 from r,dual where x < 5
)
select * from r

如果我将逗号更改为 cross join 子句，则会得到 ORA-32044:执行递归WITH查询时检测到周期错误(

If I change comma to cross join clause, I get ORA-32044: cycle detected while executing recursive WITH query error (dbfiddle):

with r (x) as (
  select 1 as x from dual
  union all
  select x + 1 from r cross join dual where x < 5
)
select * from r;

转载于Oracle 11g(我的项目db)和Oracle 18(dbfiddle)上.这种情况被最小化了，最初是对图数据的分层查询，其中以这种方式交叉连接了代表边缘端点的两行辅助关系.

Reproduced on Oracle 11g (my project db) and Oracle 18 (dbfiddle). The case is minimized, originally it is hierarchical query on graph data where auxiliary relation with 2 rows representing edge endpoints was cross joined in such a way.

我可以(不愿意)使用第一种语法，尽管我对黑暗的未公开文档或建立查询的Oracle感到不好.我没有发现这种行为的任何原因，也没有发现错误.有人知道吗?谢谢.

I can (unwillingly) live with first syntax though I have bad feeling of dark undocumented corner or Oracle to build query upon. I didn't find any reasoning for such behaviour nor track of bug. Do anybody knows any? Thanks.

推荐答案

到目前为止，我发现的最接近的解释是在此线程在MOS上.在线程中，OP通过 where 子句中的联接条件有效地联接具有内部联接的表.在 from 子句中，只有两个表通过 cross join 或逗号连接.我的示例的不同之处在于双重表不涉及任何联接条件，这削弱了线程中的某些推理，例如涉及2个不同表的任何事物都被视为联接条件.

The closest explanation I've found until now is in this thread on MOS. In the thread, OP effectively joins tables with inner join with join condition in where clause. In from clause there are just two tables connected with either cross join or comma. My example differs in aspect the dual table is not involved in any join condition, which weakens some reasonings in thread such as anything involving 2 different tables is recognized as a join condition.

无论如何，Oracle似乎实际上将逗号分隔的表评估为内部联接，由于某些未知原因，递归循环的评估顺序不同.如果我将连接条件更改为true-从r.x = r.x上的r inner join dual进行内部编码-查询也将成功，这支持此猜测.

Anyway, it seems Oracle actually evaluates comma-separated tables as inner join, which - for some unknown reason - orders differently in evaluation of recursion loop. If I change join condition to inner join on true - from r inner join dual on r.x = r.x - the query also succeeds, which supports this guess.

我为此在MOS上设置了自己的线程问题.也有怀疑是错误的答案，但没有更明确的解释.

I set up own thread on MOS for this problem. There is also answer suspecting bug but without clearer explanation.

摘要:目前尚不清楚它是错误还是某些未记录的功能.在这种情况下，使用 where 子句中的逗号和联接条件进行分隔似乎是最安全的方法.

Summary: currently it's not clear whether it is a bug or some undocumented feature. Separation with comma and join condition in where clause seem to be the safest way in this situation.

这篇关于奇怪的Oracle行为-递归CTE中的交叉联接使用逗号，但不能与交叉联接子句一起使用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！