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 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.
--
Daniel
