LISTAGG oracle function incorrect results-CodePudding

I would like to consult on this problem with aggregation function LISTAGG:

select 
r.id, 
(
select
    LISTAGG(
        r.id || '##' || cco.id  
            , 
        ' '
    )
    from 
    (
        SELECT
            co.id
       FROM conditions co
       START WITH ID = (select cons.id from conditions cons where cons.role_id = r.id)
       CONNECT BY PRIOR co.id = co.parent_condition_id
    ) cco
) conditions_export

from roles r
where r.id in (570, 571, 569)
--r.id between 569 and 571



-- table roles has 2 fields: id (number), name (varchar2)
-- table conditions has 4 fields: id, parent_condition_id, role_id (number), rule (varchar2)

When I use single id (or explicit ID list) in where condition r.id in (570, 571, 569), result is as expected.

When I don't use where clause, or use some dynamic range (r.id between 569 and 571, r.id > 500, r.id in (select rr.id from roles rr)), result contains the same aggegated value for each row - result is wrong (not as expected).

example from my DB:

values in table roles:

id   name
---  -----
569  ROLE1
570  ROLE2
571  ROLE3

values in table conditions:

id    parent_condition_id   role_id  rule
------------------------------------------
1657  NULL                  569      deny
1659  NULL                  570      allow
1667  NULL                  571      and
1674  1668                  NULL     match
1673  1670                  NULL     allow
1672  1671                  NULL     allow
1671  1670                  NULL     and
1670  1669                  NULL     and
1669  1668                  NULL     and
1668  1667                  NULL     and

query: ... r.id in (570, 571, 569) result:

569 569##1657
570 570##1659
571 571##1667 571##1668 571##1669 571##1670 571##1671 571##1672 571##1673 571##1674

query: ... r.id between 569 and 571 result:

569 569##1657
570 570##1657
571 571##1657

Reason of using this aggregation is exporting current configuration from database to text file.

Question: do you have any idea, how to work this issue around?

Database version is Oracle 19c

CodePudding user response：

You could use recursive subquery factoring instead of a hierarchical query:

with rcte (role_id, condition_id) as (
  select r.id, c.id
  from roles r
  left join conditions c on c.role_id = r.id
  where r.id in (570, 571, 569)
  union all
  select rcte.role_id, c.id
  from rcte
  join conditions c on c.parent_condition_id = rcte.condition_id
)
select rcte.role_id,
  listagg(rcte.role_id || '##' || rcte.condition_id, ' ')
from rcte
group by rcte.role_id
order by rcte.role_id;

ROLE_ID	LISTAGG(RCTE.ROLE_ID\|\|'##'\|\|RCTE.CONDITION_ID,'')
569	569##1657
570	570##1659
571	571##1667 571##1668 571##1674 571##1669 571##1670 571##1673 571##1671 571##1672

fiddle including your various initial queries and their results.

The anchor member gets the role and initial condition; the recursive member then looks for any child conditions.

1st problem is different order of resulting conditions

Your original code didn't have any ordering of the conditions, so the result is non-deterministic.

In your example output they appear to be ordered by condition ID, so you could just do that explicitly:

select rcte.role_id,
  listagg(rcte.role_id || '##' || rcte.condition_id, ' ')
    within group (order by rcte.condition_id) as conditions
from rcte

ROLE_ID	CONDITIONS
569	569##1657
570	570##1659
571	571##1667 571##1668 571##1669 571##1670 571##1671 571##1672 571##1673 571##1674

As you mention the level you might want to order by that, though it gives a different result to your example. You can get the level by tracking a dummy column in the CTE (which I've called lvl because level is reserved for hierarchical syntax):

with rcte (role_id, lvl, condition_id) as (
  select r.id, 1, c.id
  from roles r
  left join conditions c on c.role_id = r.id
  where r.id in (570, 571, 569)
  union all
  select rcte.role_id, rcte.lvl   1, c.id
  from rcte
  join conditions c on c.parent_condition_id = rcte.condition_id
)
select rcte.role_id,
  listagg(rcte.role_id || '##' || rcte.condition_id, ' ')
    within group (order by rcte.lvl) as conditions
from rcte
group by rcte.role_id
order by rcte.role_id;

ROLE_ID	CONDITIONS
569	569##1657
570	570##1659
571	571##1667 571##1668 571##1669 571##1674 571##1670 571##1671 571##1673 571##1672

Or you could order by level and then condition, where there are multiple conditions at the same level (i.e. several with the same parent); with this data it doesn't make a difference but if there were multiples it would keep it deterministic:

select rcte.role_id,
  listagg(rcte.role_id || '##' || rcte.condition_id, ' ')
    within group (order by rcte.lvl, rcte.condition_id) as conditions
from rcte

ROLE_ID	CONDITIONS
569	569##1657
570	570##1659
571	571##1667 571##1668 571##1669 571##1674 571##1670 571##1671 571##1673 571##1672

fiddle

2nd problem is I cannot access "LEVEL" variable

The code in your question doesn't refer to it, but as shown above, you can generate it as a lvl dummy column. It's unclear how you would use it though - if you want it in the select list then you would have to group by it, which would get a very different result. You could include max(lvl) instead; or concatenate the lvl into the condition (e.g. 571#1#1667). It's unclear what you want to do with it.