Bug or incorrect usage?
От | Jordan Gigov |
---|---|
Тема | Bug or incorrect usage? |
Дата | |
Msg-id | CA+nBocAXe+DJOuOzsCJRxNuimF=0dxoxkGXL3bBM+c7UVueHqA@mail.gmail.com обсуждение исходный текст |
Ответы |
Re: Bug or incorrect usage?
|
Список | 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?
В списке pgsql-bugs по дате отправления: