Skip to Main Content

Oracle Database Free

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Wrong result generated when correlated derived table contains GROUP BY clause

Lukas EderMar 17 2025

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
     )
     -- group by v.v
   ) 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;

Comments

Processing

Post Details

Added on Mar 17 2025
1 comment
64 views