Re: BUG #6723: Exception for correct query

heiko(dot)helmbrecht(at)xclinical(dot)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