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