From: | lindebg(at)gmail(dot)com |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | BUG #8287: select distinct / select group by - Invalid result |
Date: | 2013-07-06 20:41:39 |
Message-ID: | E1UvZIZ-0003LO-DK@wrigleys.postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2013-07-06 21:22:14 | Re: BUG #8287: select distinct / select group by - Invalid result |
Previous Message | Pavel Stehule | 2013-07-05 16:16:09 | Re: [BUGS] BUG #7873: pg_restore --clean tries to drop tables that don't exist |