Re: BUG #6723: Exception for correct query

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: heiko(dot)helmbrecht(at)xclinical(dot)com
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #6723: Exception for correct query
Date: 2012-07-10 04:08:03
Message-ID: 1768.1341893283@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

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

In response to

Browse pgsql-bugs by date

  From Date Subject
Next Message Maciek Sakrejda 2012-07-10 20:03:18 Re: BUG #6706: pg_upgrade fails when plpgsql dropped/re-created
Previous Message Pavel Stehule 2012-07-10 04:02:16 Re: BUG #6724: EXCEPTION syntax not recgonised