Thread: BUG #6723: Exception for correct query

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';

heiko.helmbrecht@xclinical.com writes:
> 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:

This is not a bug.  The optimizer is allowed to push conditions down
into subqueries; many people would be exceedingly unhappy if it failed
to do that.

There is a workaround you can use if you need it to not work like that,
though: add an "OFFSET 0" to the subquery.  LIMIT and OFFSET clauses
on subqueries serve as optimization fences, because the planner can't
push a WHERE condition down through one for fear of changing the set of
rows selected.

            regards, tom lane