From: | Jordan Gigov <coladict(at)gmail(dot)com> |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | Bug or incorrect usage? |
Date: | 2016-03-01 12:29:11 |
Message-ID: | CA+nBocAXe+DJOuOzsCJRxNuimF=0dxoxkGXL3bBM+c7UVueHqA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
So, I was trying to use left joins to get bot the total number of
corresponding rows and the number in a specific subset, but it seems I
can't do that in 9.4.6 (the changelogs after that don't suggest this has
changed).
This my small-scale test for it:
CREATE TABLE somedata (id bigserial, something text, PRIMARY KEY (id));
CREATE TABLE moredata (id bigserial, otherid bigint, status varchar,
PRIMARY KEY (id), FOREIGN KEY (otherid) REFERENCES somedata(id));
INSERT INTO somedata(something) VALUES ('Example 1'),('Example
2'),('Example 3');
INSERT INTO moredata(otherid,status)
VALUES(1,'NEW'),(3,'NEW'),(3,'OLD'),(3,'DEPRECATED');
SELECT somedata.id, somedata.something, count(md1.id), count(md2.id)
FROM somedata
LEFT JOIN moredata md1 ON (md1.otherid = somedata.id)
LEFT JOIN moredata md2 ON (md2.otherid = somedata.id AND md2.status NOT IN
('OLD', 'DEPRECATED'))
GROUP BY somedata.id, somedata.something;
What I expected the output to be is:
id | something | count | count
----+-----------+-------+-------
2 | Example 2 | 0 | 0
3 | Example 3 | 3 | 1
1 | Example 1 | 1 | 1
(3 rows)
Instead I got:
id | something | count | count
----+-----------+-------+-------
2 | Example 2 | 0 | 0
3 | Example 3 | 3 | 3
1 | Example 1 | 1 | 1
(3 rows)
Running the searches with individual joins:
SELECT somedata.id, somedata.something, count(md2.id)
FROM somedata
LEFT JOIN moredata md2 ON (md2.otherid = somedata.id AND md2.status NOT IN
('OLD', 'DEPRECATED'))
GROUP BY somedata.id, somedata.something;
id | something | count
----+-----------+-------
2 | Example 2 | 0
3 | Example 3 | 1
1 | Example 1 | 1
SELECT somedata.id, somedata.something, count(md1.id)
FROM somedata
LEFT JOIN moredata md1 ON (md1.otherid = somedata.id)
GROUP BY somedata.id, somedata.something;
id | something | count
----+-----------+-------
2 | Example 2 | 0
3 | Example 3 | 3
1 | Example 1 | 1
Am I misunderstanding something, or is some optimization messing-up my data?
From | Date | Subject | |
---|---|---|---|
Next Message | Vitaly Burovoy | 2016-03-01 13:31:09 | Re: Bug or incorrect usage? |
Previous Message | John R Pierce | 2016-03-01 05:33:06 | Re: could not migrate 8.0.13 database with large object data to 9.5.1 |