Strange LIST result

[Gabor Boros]

Hi All,

See the example below. Why the order by not have an effect in this case
and how to get same result with the three different versions? (A simple
select * from tbl1 order by 1, 2 or select * from tbl1 order by 2, 1
give same result for all versions.)

CREATE TABLE TBL1 (F1 INTEGER, F2 VARCHAR(10), F3 VARCHAR(10), F4
VARCHAR(10));

INSERT INTO TBL1 (F1, F2, F3, F4) VALUES (1, '27', 'Y', 'X');

INSERT INTO TBL1 (F1, F2, F3, F4) VALUES (25, '12', 'Y', 'X');


with list1 as (select * from tbl1 order by 1, 2)
select cast(list(f1) as varchar(10)) f1, cast(list(f2) as varchar(10))
f2, f3, f4
from list1
group by 3, 4;


2.5

F1 F2 F3 F4
========== ========== ========== ==========
25,1 12,27 Y X


3.0

F1 F2 F3 F4
========== ========== ========== ==========
25,1 12,27 Y X


4.0

F1 F2 F3 F4
========== ========== ========== ==========
1,25 27,12 Y X


with list1 as (select * from tbl1 order by 2, 1)
select cast(list(f1) as varchar(10)) f1, cast(list(f2) as varchar(10))
f2, f3, f4
from list1
group by 3, 4;


2.5

F1 F2 F3 F4
========== ========== ========== ==========
25,1 12,27 Y X


3.0

F1 F2 F3 F4
========== ========== ========== ==========
25,1 12,27 Y X


4.0

F1 F2 F3 F4
========== ========== ========== ==========
1,25 27,12 Y X

Gabor

[Mark Rotteveel]

Using a nested query with an ORDER BY was a trick that exploited a
specific optimizer behaviour, and this was in no way defined behaviour.
It looks like the Firebird 4.0 optimizer has a different behaviour. You
could try and report it as a bug, but I'm not sure this will be
considered as a bug, so this might not get fixed.

Unless Firebird implements the SQL standard WITHIN GROUP clause (defined
for SQL standard LISTAGG, which is similar to Firebird's LIST), there is
no way to get any guaranteed order here. In the SQL standard, the WITHIN
GROUP clause allows you to specify a specific order for concatenating
values with LISTAGG.

Mark