The following bug has been logged on the website:
Bug reference: 6723
Logged by: Heiko Helmbrecht
Email address: [email protected]
PostgreSQL version: 8.4.12
Operating system: Linux
Description:
The optimizer is using a where condition for a full table, not to the
results of a join/subselect result, that's why it is tried to use casts,
that cannot work on the whole table, here are the easy steps to reproduce
the problems:
create table myTable(id integer, is_current boolean, value varchar(100), ref
integer);
create table myJoinTable(id integer, name varchar(10));
insert into myTable(id, is_current, value, ref) values
(1,true,'2012-01-15',1),
(2,true,'no date',2),
(3,false,'2012-01-07',3),
(4,false,'2012-01-07',1),
(5,false,'2012-01-07',1),
(6,false,'2012-01-07',1),
(7,false,'2012-01-07',1),
(8,false,'2012-01-07',3),
(9,true,'2012-01-07',3);
insert into myJoinTable(id, name) values (1, 'A'), (2, 'B'), (3, 'A');
select myDate from
(select CAST(t2.value AS DATE) as myDate
from myJoinTable t1 join myTable t2
on t1.id = t2.ref where t2.is_current = TRUE and t1.name = 'A') myTmpTable
WHERE myDate > '2012-01-01';
--
Sent via pgsql-bugs mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
