Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-bugs by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group