The following bug has been logged on the website:
Bug reference: 6723
Logged by: Heiko Helmbrecht
Email address: heiko.helmbrecht@xclinical.com
PostgreSQL version: 8.4.12
Operating system: Linux
Description:=20=20=20=20=20=20=20=20
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=20
(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=20
(select CAST(t2.value AS DATE) as myDate=20
from myJoinTable t1 join myTable t2=20
on t1.id =3D t2.ref where t2.is_current =3D TRUE and t1.name =3D 'A') myTm=
pTable
WHERE myDate > '2012-01-01';
