Re: 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.

* OR arguments are handled as follows:
* non constant: keep
* FALSE: drop (does not affect result)
* TRUE: force result to TRUE
* NULL: keep only one
* We keep one NULL input because ExecEvalOr returns NULL
* when no input is TRUE and at least one is NULL.

* AND arguments are handled as follows:
* non constant: keep
* TRUE: drop (does not affect result)
* FALSE: force result to FALSE
* NULL: keep only one
* We keep one NULL input because ExecEvalAnd returns NULL
* when no input is FALSE and at least one is NULL.

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.)

regards, tom lane