oracle - 当列值中不存在 sys_connect_by_path 分隔符时，为什么会出现 Ora-30004

Question

我已经在 Oracle 版本上对此进行了测试:
11.2.0.3.0
12.1.0.2.0

以下查询引发 ORA-30004 错误，但我的分隔符 ' -> ' 未在任何列值中使用:

with temptable as (
  select '2624' as id, 'ninechars' as label, '' as parentid from dual union 
  select '2625' as id, 'erewrettt' as label, '2624' as parentid from dual union 
  select '2626' as id, 'Im stumped' as label, '' as parentid from dual union 
  select '2627' as id, '- Unknown -' as label, '' as parentid from dual
)
select sys_connect_by_path(label, ' -> ' ) 
from temptable
start with parentid is null 
connect by prior id = parentid;

一些观察:

• Changing the value "ninechars" to "ninecharsx" allows the query to work
• Changing the value "ninechars" to "abcdefghi" also breaks the query
  • It seems like all nine character values here break the query
    
    
• Leaving the value as "ninechars" and removing the last union statement, which is not connected to any of the other records, allows the query to work
• Changing the delimiter from ' -> ' to ' *> ' allows the query to work

问题
ORA-30004 错误的根源是什么？为什么 Oracle 认为分隔符作为列值的一部分出现？

编辑:感谢 bobdylan pastebin.com/Ad1edFcJ 评论中留下的链接有助于说明问题

Answer 1

这闻起来像个 bug 。如果您需要解决它并实现您的逻辑，或者您可以使用递归子查询分解(递归与)，它在 11.2.0.4 中工作正常:

SQL> with t (id, label, parentid, reportlevel, fake_connect_by_path) as (
  2  select id, label, parentid, 0 as reportlevel, ' -> ' || label as fake_connect_by_path
  3    from temptable
  4   where parentid is null
  5   union all
  6  select tt.id, tt.label, tt.parentid, reportlevel + 1, t.fake_connect_by_path || ' -> ' || tt.label as fake_connect_by_path
  7    from temptable tt
  8    join t on t.id = tt.parentid
  9  )
 10  select fake_connect_by_path
 11    from t;
FAKE_CONNECT_BY_PATH
--------------------------------------------------------------------------------
 -> ninechars
 -> Im stumped
 -> - Unknown -
 -> ninechars -> erewrettt