使用CONNECT BY PRIOR出现异常，是否属于Oracle Bug？

问题场景 #

在Oracle 12c/19c中执行拆分车型油漆选项的SQL时，返回4行冗余结果（期望仅3行）；添加ep.car_model = prior ep.car_model约束限制同车型关联后，触发ORA-01436: 用户数据中的CONNECT BY循环错误。

初始SQL #

WITH car_paint_options AS (
   SELECT 'Escort'  car_model, 'red,blue' paint_opts FROM dual UNION
   SELECT 'Puma'    car_model, 'black'    paint_opts FROM dual
)
SELECT row_number() over(order by level) rn, level, ep.car_model,
       regexp_substr(ep.paint_opts, '[^,]+', 1, level) paint_opt
FROM   car_paint_options ep
CONNECT BY regexp_substr (ep.paint_opts, '[^,]+', 1, level) is not null

错误返回结果 #

rn level car_model paint_opt
--- ----- --------- ---------
  1     1 Puma      black
  2     1 Focus     red
  3     2 Focus     blue
  4     2 Focus     blue

期望正确结果 #

rn level car_model paint_opt
--- ----- --------- ---------
  1     1 Puma      black
  2     1 Focus     red
  3     2 Focus     blue

添加约束后的SQL #

WITH car_paint_options AS (
   SELECT 'Escort'  car_model, 'red,blue' paint_opts FROM dual UNION
   SELECT 'Puma'    car_model, 'black'    paint_opts FROM dual
)
SELECT row_number() over(order by level) rn, level, ep.car_model,
       regexp_substr(ep.paint_opts, '[^,]+', 1, level) paint_opt
FROM   car_paint_options ep
CONNECT BY regexp_substr (ep.paint_opts, '[^,]+', 1, level) is not null
       AND ep.car_model = prior ep.car_model

触发的错误信息 #

ORA-01436: 用户数据中的CONNECT BY循环

问题原因 #

1. 初始SQL冗余行原因：未限制CONNECT BY的层级关联范围，导致不同车型的行之间产生交叉关联，生成重复的拆分结果。
2. 循环错误原因：仅添加ep.car_model = prior ep.car_model时，Oracle无法区分父行与子行的唯一关联关系，同一行会被反复关联，判定为循环。

正确解法 #

需要在CONNECT BY中同时指定层级数量限制、同车型约束，并通过唯一标识避免循环。以下是两种可行写法：

写法一：用SYS_GUID()避免循环 #

WITH car_paint_options AS (
   SELECT 'Escort'  car_model, 'red,blue' paint_opts FROM dual UNION
   SELECT 'Puma'    car_model, 'black'    paint_opts FROM dual
)
SELECT row_number() over(order by car_model, level) rn, level, ep.car_model,
       regexp_substr(ep.paint_opts, '[^,]+', 1, level) paint_opt
FROM   car_paint_options ep
CONNECT BY level <= regexp_count(ep.paint_opts, '[^,]+')
       AND ep.car_model = prior ep.car_model
       AND PRIOR SYS_GUID() IS NOT NULL
ORDER BY rn;

写法二：用rowid避免循环 #

WITH car_paint_options AS (
   SELECT 'Escort'  car_model, 'red,blue' paint_opts FROM dual UNION
   SELECT 'Puma'    car_model, 'black'    paint_opts FROM dual
)
SELECT row_number() over(order by car_model, level) rn, level, ep.car_model,
       regexp_substr(ep.paint_opts, '[^,]+', 1, level) paint_opt
FROM   car_paint_options ep
CONNECT BY level <= regexp_count(ep.paint_opts, '[^,]+')
       AND ep.car_model = prior ep.car_model
       AND PRIOR rowid = ep.rowid
ORDER BY rn;

代码说明 #

• level <= regexp_count(ep.paint_opts, '[^,]+')：明确层级数不超过字符串拆分后的元素总数，避免生成多余层级。
• ep.car_model = prior ep.car_model：限制仅在同一车型的行内进行层级关联，避免跨车型交叉。
• PRIOR SYS_GUID() IS NOT NULL / PRIOR rowid = ep.rowid：通过唯一标识让Oracle识别父行与子行的唯一关联，防止循环判定。

内容的提问来源于stack exchange，提问作者cartbeforehorse