The following bug has been logged on the website: Bug reference: 8287 Logged by: Jacek Email address: lind...@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 -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs