BEGIN;

CREATE SCHEMA duptest;


CREATE TABLE documents (
	docid SERIAL NOT NULL,
	title text,
	PRIMARY KEY (docid)
);

CREATE TABLE duplicates (
	docid   int NOT NULL REFERENCES documents ON DELETE CASCADE,
	dup_set SERIAL NOT NULL,
	PRIMARY KEY (docid, dup_set)
);

-- Five documents
INSERT INTO documents (docid, title)
SELECT i, 'document number ' || i FROM generate_series(1, 6) i;

-- duplicates are (1,3) and (2,4) - 5,6 are not
INSERT INTO duplicates (docid, dup_set)
SELECT i, (i % 2)+1 FROM generate_series(1, 4) i;

SELECT setval('documents_docid_seq', (SELECT max(docid)+1 FROM documents));
SELECT setval('duplicates_dup_set_seq', (SELECT max(dup_set)+1 FROM duplicates));

-- This is a list of all documents with how many duplicates they have
SELECT dup_set, d.docid, d.title, COALESCE(ds.num_dups, 0) AS num_dups
FROM
	documents d
LEFT JOIN
	(
		SELECT dup1.docid, dup1.dup_set, count(*) - 1 AS num_dups
		FROM duplicates dup1
		JOIN duplicates dup2 USING (dup_set)
		GROUP BY dup1.docid, dup1.dup_set
	) ds
USING (docid)
ORDER BY dup_set, docid
;

-- This DOESN'T work because nulls are considered equal by DISTINCT ON
SELECT DISTINCT ON (dup_set) 
	dup_set, d.docid, d.title, COALESCE(ds.num_dups, 0) AS num_dups
FROM
	documents d
LEFT JOIN
	(
		SELECT dup1.docid, dup1.dup_set, count(*) - 1 AS num_dups
		FROM duplicates dup1
		JOIN duplicates dup2 USING (dup_set)
		GROUP BY dup1.docid, dup1.dup_set
	) ds
USING (docid)
ORDER BY dup_set, docid
;

-- Work around is to fake a unique id for non-duplicates
CREATE SEQUENCE not_duplicate_seq MINVALUE -999999 MAXVALUE -1 CYCLE;

SELECT DISTINCT ON (dup_set) 
	COALESCE(dup_set, nextval('not_duplicate_seq')) AS dup_set,
	d.docid, d.title, COALESCE(ds.num_dups, 0) AS num_dups
FROM
	documents d
LEFT JOIN
	(
		SELECT dup1.docid, dup1.dup_set, count(*) - 1 AS num_dups
		FROM duplicates dup1
		JOIN duplicates dup2 USING (dup_set)
		GROUP BY dup1.docid, dup1.dup_set
	) ds
USING (docid)
ORDER BY dup_set, docid
;

-- Second alternative - UNION duplicated and non-duplicated documents
SELECT DISTINCT ON (dup_set) 
	ds.dup_set, d.docid, d.title, num_dups
FROM
	documents d
LEFT JOIN
	(
		SELECT dup1.docid, dup1.dup_set, count(*) - 1 AS num_dups
		FROM duplicates dup1
		JOIN duplicates dup2 USING (dup_set)
		GROUP BY dup1.docid, dup1.dup_set
	) ds
USING (docid)
WHERE num_dups > 0
UNION ALL
SELECT
	ds.dup_set, d.docid, d.title, COALESCE(num_dups,0) AS num_dups
FROM
	documents d
LEFT JOIN
	(
		SELECT dup1.docid, dup1.dup_set, count(*) - 1 AS num_dups
		FROM duplicates dup1
		JOIN duplicates dup2 USING (dup_set)
		GROUP BY dup1.docid, dup1.dup_set
	) ds
USING (docid)
WHERE num_dups IS NULL
ORDER BY dup_set, docid
;

ROLLBACK;
