BUG #8287: select distinct / select group by - Invalid result - Mailing list pgsql-bugs

The following bug has been logged on the website:

Bug reference:      8287
Logged by:          Jacek
Email address:      lindebg@gmail.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