| Subject | LIST gives inconsistent results |
|---|---|
| Author | Daniel Miller |
| Post date | 2017-09-28T08:49:33Z |
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)
);
(
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)
);
(
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
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
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