On 2017-09-28 10:49, 'Daniel Miller' dmiller@...
[firebird-support] wrote:

> It's certainly possible my database is corrupted - I don't think it
> is. But I'm willing to test if someone tells me how. However...
>
> At the moment, after several painful hours, I think I've determined
> the following:
>
> CREATE TABLE NODES
> (
> NODE smallint NOT NULL,
> ROUTE_UPDATED timestamp,
> CONSTRAINT PK_NODES PRIMARY KEY (NODE)
> );
>
> CREATE TABLE ROUTES
> (
> NODE smallint NOT NULL,
> ROUTE_INDEX smallint NOT NULL,
> LINK_NODE smallint NOT NULL,
> QUALITY smallint,
> CONSTRAINT PK_ROUTES PRIMARY KEY (NODE,ROUTE_INDEX)
> );
>
> Entries in table ROUTES are inserted in primary key order - as well as
> sorted by primary key. So via both "raw & natural" order and an active
> ORDER BY it shouldn't be that difficult to have a sorted list of
> routes!

If you assume that Firebird will somehow guarantee that rows are
returned in insertion order (or that they are even stored in insertion
order on disk), than your are mistaken. There is no such guarantee, the
only guarantee is using an ORDER BY.

> if I do:
> select R.NODE, list(R.ROUTE_INDEX||R.LINK_NODE||R.QUALITY)
> LISTED_ROUTE
> from ROUTES R
> group by R.NODE
>
> I get a computed column that is properly sorted. I don't even need to
> specify an ORDER BY. But...
>
> select N.NODE, list(ROUTE_INDEX||LINK_NODE||QUALITY) LISTED_ROUTE
> from NODES N
> join (select R.NODE, R.ROUTE_INDEX, R.LINK_NODE, R.QUALITY from
> ROUTES R order by R.NODE,R.ROUTE_INDEX) using (NODE)
> group by N.NODE
>
> Even though I'm explicitly sorting the source derived table for the
> LIST - I get a set of results that seems almost random. Absolutely
> maddening.

As documented in :
https://www.firebirdsql.org/file/documentation/reference_manuals/fblangref25-en/html/fblangref25-functions-aggfuncs.html#fblangref25-functions-aggfuncs-list

"**The ordering of the list values is undefined**—the order in which the
strings are concatenated is determined by read order from the source set
which, in tables, is not generally defined. If ordering is important,
the source data can be pre-sorted using a derived table or similar."

(emphasis mine)

In your query your ORDER BY in the subquery of the join gets lost (or is
not guaranteed to be maintained) in the join, so you need to push that
ORDER BY up out of the join:

select NODE, list(ROUTE_INDEX||LINK_NODE||QUALITY) LISTED_ROUTE
from (
select N.NODE, R.ROUTE, R.LINK_NODE, R.QUALITY
from NODES N
join ROUTES R using (NODE)
order by N.NODE, R.ROUTE_INDEX
) a
group by NODE

However, this still depends on an implementation artefact and is not
guaranteed to work, nor guaranteed to work in future versions.

Mark