Re: wierd AND condition evaluation for plpgsql

> -----Original Message-----
> From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
> Sent: Thursday, May 30, 2002 10:44 AM
> To: Joel Burton
> Cc: Alessio Bragadini; PostgreSQL Hackers
> Subject: Re: [HACKERS] wierd AND condition evaluation for plpgsql
>
>
> "Joel Burton" <joel(at)joelburton(dot)com> writes:
> >>> Actually, at least in some cases, PG does short-circuit logic:
> >>> joel(at)joel=# select false and seeme();
> >>> joel(at)joel=# select true and seeme();
>
> >> If seeme() returns NULL, shouldn't both SELECTs return NULL, and
> >> therefore not be short-circuit-able?
>
> > In my example, seeme() returns true, not NULL. However, the
> short-circuiting
> > came from the other part (the simple true or false) being
> evaluated first.
> > So, regardless of the returned value of seeme(), "SELECT FALSE
> AND seeme()"
> > would short-circuit, since "FALSE AND ___" can never be true.
>
> Yes. Per the SQL standard, some cases involving AND and OR can be
> simplified without evaluating all the arguments, and PG uses this
> flexibility to the hilt. You might care to read eval_const_expressions()
> in src/backend/optimizer/util/clauses.c. Some relevant tidbits:
>
> * Reduce any recognizably constant subexpressions of the given
> * expression tree, for example "2 + 2" => "4". More interestingly,
> * we can reduce certain boolean expressions even when they contain
> * non-constant subexpressions: "x OR true" => "true" no matter what
> * the subexpression x is. (XXX We assume that no such subexpression
> * will have important side-effects, which is not necessarily a good
> * assumption in the presence of user-defined functions; do we need a
> * pg_proc flag that prevents discarding the execution of a function?)
>
> * We do understand that certain functions may deliver non-constant
> * results even with constant inputs, "nextval()" being the classic
> * example. Functions that are not marked "immutable" in pg_proc
> * will not be pre-evaluated here, although we will reduce their
> * arguments as far as possible.
>
> ...
>
> Other relevant manipulations include canonicalize_qual() in
> src/backend/optimizer/prep/prepqual.c (tries to convert boolean
> WHERE expressions to normal form by application of DeMorgan's laws)
> and for that matter the entire planner --- the fact that we have
> a choice of execution plans at all really comes from the fact that
> we are allowed to evaluate WHERE clauses in any order. So there's
> not likely to be much support for any proposal that we constrain the
> evaluation order or guarantee the evaluation or non-evaluation of
> specific clauses in WHERE. (The XXX comment above is an idle aside,
> not something that is likely to really happen.)

Thanks, Tom, for the pointers to the full story.

Is there any generalizable help would could offer to people who write
functions that have side effects? Don't use them in WHERE (or ON or HAVING)
clauses? Evaluate the function in a earlier db call, then plug the resolved
results into the SQL WHERE statement?

I've lived without having this bite me; I'd think that side-effect functions
would be unusual in a WHERE clause. I'm just wondering if we should work
this into the docs somewhere. (Or is it? I took a look, but didn't see
anything).

- J.

Joel BURTON | joel(at)joelburton(dot)com | joelburton.com | aim: wjoelburton
Knowledge Management & Technology Consultant