I'm using Oracle Free 23.7.0.25.01 from here: https://hub.docker.com/r/gvenzl/oracle-free
This is the most minimal reproducer that I could make for this bug:
create table t (t int);
insert into t values (1),(2);
create table u (u int, t int);
insert into u values (1, 1), (2, 1), (3, 2), (4, 2);
create table v (v int);
insert into v values (1),(2),(3);
create table vu (v int, u int);
insert into vu values (1, 1),(2, 1),(3, 1),(2, 2),(2, 3);
select
t.t,
(
select listagg(x, ', ') within group (order by x)
from (
select v.v x
from v
where v.v in (
select vu.v
from vu
join u
on vu.u = u.u
where u.t = t.t
)
group by v.v
) t
) as l
from t;
The result is this:
|T |L |
|---|-------|
|1 |1, 2, 3|
|2 |1, 2, 3|
When it should be this:
|T |L |
|---|-------|
|1 |1, 2, 3|
|2 |2 |
The correct result can be produced for example when removing the (unnecessary, in this case) GROUP BY
clause:
select
t.t,
(
select listagg(x, ', ') within group (order by x)
from (
select v.v x
from v
where v.v in (
select vu.v
from vu
join u
on vu.u = u.u
where u.t = t.t
)
) t
) as l
from t;
Alternatively, using DISTINCT
also exposes the bug:
select
t.t,
(
select listagg(x, ', ') within group (order by x)
from (
select distinct v.v x
from v
where v.v in (
select vu.v
from vu
join u
on vu.u = u.u
where u.t = t.t
)
) t
) as l
from t;