Why do two different route pairs count as one?

Prepare the report input

Original report counts distinct source/destination pairs. A→BC and AB→C are different, but both concatenate to ABC. Count rows of a DISTINCT two-column subquery; input event IDs are not the route-pair identity.

Every record and report rule below is original and fictional. Reproduce the setup in a fresh SQLite test database; input previews include every table in this small fixture.

CREATE TABLE routes(id INTEGER PRIMARY KEY,source TEXT NOT NULL,destination TEXT NOT NULL); INSERT INTO routes VALUES(1,'A','BC'),(2,'AB','C');

Compare misleading and corrected results

SELECT COUNT(DISTINCT source||destination) AS route_count FROM routes;

SELECT COUNT(*) AS route_count FROM (SELECT DISTINCT source,destination FROM routes);

The misleading scalar key counts one route; the corrected tuple count is two. A separator alone also requires an escaping contract if labels may contain that separator.

Check a fresh boundary case

Delete all route events in the fresh boundary fixture. The distinct-pair count is zero.

Recreate the original setup in another fresh database, then apply the change and corrected query below. For an empty result, the table displays a labelled empty-result marker rather than a data record.

DELETE FROM routes;
SELECT COUNT(*) AS route_count FROM (SELECT DISTINCT source,destination FROM routes);