ORA-00932, если коллекция используется в рекурсивном CTE в предложении where

У меня есть рекурсивный CTE со столбцом типа коллекции (здесь используется sys.ku$_vcnt, потому что он встроен, проблема может быть воспроизведена для любого типа коллекции). Когда столбец коллекции используется в рекурсивной части CTE в предложении where, запрос завершается с ошибкой ORA-00932: inconsistent datatypes: expected UDT got SYS.KU$_VCNT.

Это минимизированный пример, в реальном случае содержимое коллекции проверяется в предложении where. Любого появления коллекции кажется достаточно для сбоя запроса - например, не нулевая проверка, как показано в следующем примере:

with r (l, dummy_coll, b) as (
  select 1 as l, sys.ku$_vcnt(), null from dual
  union all
  select l + 1
       , r.dummy_coll
       , case when r.dummy_coll is not null then 'not null' else 'null' end as b
  from r
  where l < 5 and r.dummy_coll is not null
)
select * from r;

Если and r.dummy_coll is not null удалить из предложения where, запрос будет успешным. Появление коллекции в предложении select не является проблемой (столбец b показывает, что коллекция на самом деле не является нулевой).

Почему это не работает и как заставить Oracle видеть столбец коллекции из предыдущего уровня рекурсии в предложении where?

Воспроизведено в Oracle 11 и Oracle 18 (dbfiddle).

Спасибо!


collections oracle recursive-query oracle11g ora-00932
person Tomáš Záluský    schedule 04.06.2020    source источник

Ответы (1)


arrow_upward
1
arrow_downward

Да, это похоже на ошибку для меня. Скалярный выбор кажется обходным путем. Будет ли это работать в вашем случае?

SQL> with r (l, dummy_coll, b) as (
  2    select 1 as l, sys.ku$_vcnt(), null from dual
  3    union all
  4    select l + 1
  5         , r.dummy_coll
  6         , case when r.dummy_coll is not null then 'not null' else 'null' end as b
  7    from r
  8    where l < 5 and ( select r.dummy_coll from dual ) is not null
  9  )
 10  select * from r;

L,DUMMY_COLL,B
1,KU$_VCNT(),
2,KU$_VCNT(),not null
3,KU$_VCNT(),not null
4,KU$_VCNT(),not null
5,KU$_VCNT(),not null
person Connor McDonald    schedule 06.06.2020
comment
Отлично работает, спасибо! Тем временем я придумал обходной путь where l < 5 and (cast(multiset(select * from table(r.dummy_coll)) as sys.ku$_vcnt) is not null, который тоже работает, ваш намного проще. - person Tomáš Záluský; 06.06.2020
comment
Поскольку фактическое условие было something not member of r.dummy_coll (которое было успешно заменено на something not member of (select r.dummy_coll from dual) благодаря принятому ответу), альтернативным решением также является something not in (select column_value from table(r.dummy_coll)). Я обнаружил это через пару недель. - person Tomáš Záluský; 10.07.2020