BUG #8287: select distinct / select group by - Invalid result

The following bug has been logged on the website:

Bug reference: 8287
Logged by: Jacek
Email address: lindebg(at)gmail(dot)com
PostgreSQL version: 9.2.4
Operating system: Debian x64 / Windows 8 x64
Description:

I wanted to report an incorrect execution of the query SELECT DISTINCT... :

Example:

create table machines
(
machineid int primary key,
machinename varchar not null,
editdate timestamp,
deleted boolean
);

insert into machines(machineid, machinename, deleted, editdate) values
(1, 'test', false, null);

create table commands
(
commandid int primary key,
command varchar not null,
machineid int not null references machines(machineid) on delete cascade,
resultdate timestamp
);

insert into commands(commandid, machineid, command, resultdate) values
(1, 1, 'command1', '2011-03-25 13:40:58.430'),
(2, 1, 'command2', null),
(3, 1, 'command3', '2012-04-05 21:22:23.111'),
(4, 1, 'command1', null),
(5, 1, 'command1', '2011-04-11 23:17:09.113');

create table commandsaddit
(
commandid int primary key references commands(commandid) on delete
cascade,
param1 varchar,
param2 varchar
);

create view vmachinesall
as
select
hs.machineid,
hs.machinename,
hs.editdate,
case when hs.deleted then hs.editdate else null::timestamp end as
deleteddate
from machines hs;

create view vmachines
as
select
hs.machineid,
hs.machinename,
hs.editdate
from vmachinesall hs
where (hs.deleteddate is null);

create view vcommands
as
select
t.commandid,

case
when (t.resultdate is null) then 'Processing'::varchar
when (a.commandid is not null) then 'Ok 1'::varchar
else 'Ok 2'::varchar
end as status
from commands t inner join vmachines h on t.machineid = h.machineid
left join commandsaddit a on t.commandid = a.commandid;

-- example:

select distinct status
from vcommands;

-- RESULT:
-- Processing
-- Ok 2

select distinct status
from vcommands
where status = 'xxx'; -- any value

-- or

select status
from vcommands
where status = 'xxx' -- any value
group by status;

-- RESULT:
-- Ok 2
-- Processing
-- Ok 2
-- Processing
-- Ok 2

lindebg(at)gmail(dot)com writes:
> I wanted to report an incorrect execution of the query SELECT DISTINCT... :

FWIW, this example doesn't seem to misbehave for me in HEAD or 9.2
branch tip. I suspect that this is a variant of bug #8049, which was
fixed recently. It could be though that you're reporting some other
problem but there are special circumstances (such as nondefault planner
settings) needed to make it manifest. You could try applying this patch
to see if the problem goes away for you:

http://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=841c9b6ba151ed5a41733ec345bf9bf32a55f4dc

regards, tom lane

W dniu 2013-07-06 23:22, Tom Lane pisze:
> lindebg(at)gmail(dot)com writes:
>> I wanted to report an incorrect execution of the query SELECT DISTINCT... :
> FWIW, this example doesn't seem to misbehave for me in HEAD or 9.2
> branch tip. I suspect that this is a variant of bug #8049, which was
> fixed recently. It could be though that you're reporting some other
> problem but there are special circumstances (such as nondefault planner
> settings) needed to make it manifest. You could try applying this patch
> to see if the problem goes away for you:
>
> http://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=841c9b6ba151ed5a41733ec345bf9bf32a55f4dc
>
> regards, tom lane

Thank you for your response. This problem does not exist in the branch
REL9_2_STABLE.

Regards, Jacek