But that doesn't make sense. Right from the docs you quoted - "If
ordering is important,
the source data can be pre-sorted using a derived table or similar." So
if I create the derived table using ORDER BY - why is the LIST not
sorted?
But...if somehow the join is messing up the LIST processing I can work
around that - generate the LIST first and then join after. Here's the
next issue:
Having generated a table with LIST results - why can I not sort or
compare on the LISTS? I first create a view:
create view TEMP_ROUTE_HISTORY_LISTED (NODE, ROUTE_UPDATED,
LISTED_ROUTE)
as
select H.NODE, H.ROUTE_UPDATED,
list(H.ROUTE_INDEX||H.LINK_NODE||H.QUALITY) LISTED_ROUTE
from ROUTE_HISTORY H
group by H.NODE, H.ROUTE_UPDATED;
This (seems) to work - I get exactly what I think I want now.
Except...the sort order of the rows. So...
select TRHL.LISTED_ROUTE
from TEMP_ROUTE_HISTORY_LISTED TRHL
where NODE=5003
order by TRHL.LISTED_ROUTE
This does...exactly the same as the view. LISTED_ROUTE is obviously not
sorted. What am I doing wrong now?
--
Daniel
On 9/28/2017 5:40:51 AM, "Mark Rotteveel m...@lawinegevaar.nl
[firebird-support]" <firebird-support@yahoogroups.com> wrote:
>On 2017-09-28 10:49, 'Daniel Miller' dmil...@amfes.com
>[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
>
>
>------------------------------------
>Posted by: Mark Rotteveel <m...@lawinegevaar.nl>
>------------------------------------
>
>++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
>Visit http://www.firebirdsql.org and click the Documentation item
>on the main (top) menu. Try FAQ and other links from the left-side
>menu there.
>
>Also search the knowledgebases at
>http://www.ibphoenix.com/resources/documents/
>
>++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>------------------------------------
>
>Yahoo Groups Links
>
>
>
