Bug or incorrect usage?

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?

Responses

Browse pgsql-bugs by date

  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