Re: QUAL Pushdown causes ERROR on syntactically and semantically correct SQL Query - Mailing list pgsql-hackers

Hans Buschmann <buschmann@nidsa.net> writes:
> I have reworked the case of BUG #17842 to include the data and the questions for further investigation.

This wasn't a bug before, and it still isn't.  Postgres doesn't guarantee
anything about the order of execution of a query's WHERE and JOIN clauses,
and we do not intend to offer any such guarantee in future either.  Doing
so would make far more people unhappy than happy, since it'd be
catastrophic for performance in many cases.

If you really need to use error-prone qual clauses, you need an
optimization fence.  There are a couple of ways to do that but
the most recommendable is to use a materialized CTE:

with m as materialized
  (select ..., ('0'||split_part(split_part(nline,'(',2),')',1))::smallint
          as nlen, ...
   from ... where ...)
select * from m where nlen > 0;

The "nlen > 0" condition won't get pushed into the CTE.

            regards, tom lane