Hi,

what about this query
It looks strange but is really simple

SELECT
R5.SUMING, COUNT(*)
FROM
(
SELECT
(SELECT CAST(LIST(R3.ROUTE_INDEX || '_' || R3.LINK_NODE) AS VARCHAR(1000)) AS SUMING FROM (SELECT * FROM ROUTES R4 WHERE R4.NODE=R2.NODE ORDER BY R4.ROUTE_INDEX ASC) R3), R2.NODE
FROM
(SELECT DISTINCT R.NODE from ROUTES R) R2
) R5
WHERE
EXISTS(
SELECT
*
FROM
(
SELECT
(SELECT CAST(LIST(R3.ROUTE_INDEX || '_' || R3.LINK_NODE) AS VARCHAR(1000)) AS SUMING FROM (SELECT * FROM ROUTES R4 WHERE R4.NODE=R2.NODE ORDER BY R4.ROUTE_INDEX ASC) R3)
FROM
(SELECT DISTINCT R.NODE from ROUTES R) R2
) R6
WHERE
R6.SUMING=CAST((SELECT LIST(R3.ROUTE_INDEX || '_' || R3.LINK_NODE) FROM (SELECT * FROM ROUTES R4 WHERE R4.NODE=R5.NODE ORDER BY R4.ROUTE_INDEX ASC) R3) AS VARCHAR(1000))
)
GROUP BY 1
HAVING COUNT(*)>1


for me it found all duplicates in sets

regards,
Karol Bieniaszewski


From: 'Daniel Miller' dmiller@... [firebird-support]
Sent: Thursday, September 28, 2017 10:49 AM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] LIST gives inconsistent results



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


[Non-text portions of this message have been removed]