Query With XMLAGG And "connect By" Clause Returning Unexpected Results After 19.15 To 19.16 Patching (Doc ID 2905611.1)

Last updated on APRIL 17, 2023

Applies to:

Symptoms

A query with XMLAGG and "connect by" clause is returning unexpected results after 19.15 to 19.16 patching

In 19.15 PROD

Query

SELECT acg_id as context_id,
replace_quote(acg_name) as context_name,
parent_acg_id as parent_context_id,
(select XMLAGG(XMLELEMENT(E,acg_id||'|')).extract('//text()').getClobVal()
  from acg cc start with parent_acg_id = ch.acg_id connect by prior acg_id = parent_acg_id) AS child_contexts,
'ACG' as context_type FROM acg ch
where ch.acg_id='C-1C000QW';

Returns following output:

CONTEXT_ID CONTEXT_NAME PARENT_CONTEXT_ID CHILD_CONTEXTS CONTEXT_TYPE
------------------------------ ------------------------------ ------------------------------ ------------------------------------------------------------ ------------------------------
C-1C000QW Top-Level Group: C-1C000QW C-1C000QW.G117380|C-1C000QW.G160024| ACG


In 19.16 QA It returns following output instead:

CONTEXT_ID CONTEXT_NAME PARENT_CONTEXT_ID CHILD_CONTEXTS CONTEXT_TYPE
------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------
C-1C000QW Top-Level Group: C-1C000QW || ACG


CHILD_CONTEXTS return empty list.

There are no errors in the alert log upon execution of this query.

Changes

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.