-- Consider this simple query:

select 'Q' || y as y2

from ( select 'Y' as y from dual where 0=1 ) x

-- result: 0 rows

-- This makes sense, because x returns 0 rows, therefore any "select y from x" also returns 0 rows.

​

-- Now change the "select y from x" to "select listagg(y) from x":

select 'Q' || listagg(y, ':') within group(order by y) as y2

from ( select 'Y' as y from dual where 0=1 ) x

-- result: 1 row: 'Q'

-- This is strange, because x still returns 0 rows, so why do we get a result ?

​

-- And now add an extra "where 0=1" to the whole thing:

select 'Q' || listagg(y, ':') within group(order by y) as y2

from ( select 'Y' as y from dual where 0=1 ) x

where 0=1

-- result: 1 row: 'Q'

-- Even this does not stop the listagg from somehow producing a row...

​