Re: Coalesce bug ?

"David Johnston" <polobo(at)yahoo(dot)com> writes:
>> Indeed, COALESCE will not execute the second sub-select at runtime, but
>> that doesn't particularly matter here. What matters is that "ps3(2)"
>> qualifies to be pre-evaluated (folded to a constant) at plan time.

> Understood (I'm guessing there is no "global" cache but simply the
> plan-level cache that gets populated each time?)

There's no cache. Either the function gets evaluated, or it doesn't.

> However, in the following example the ps3(2) expression should also qualify
> for this "folding" and thus the RAISE NOTICE should also appear during plan
> time for the same reason; which, per the OP, it does not.

> pgb=# select coalesce( ps3(1), ps3(2) );

Ah. The reason for that is a bit subtle: constant-folding of immutable
functions happens in the same pass over the query tree as simplification
of simplifiable constructs --- including COALESCE. So what's happening
is that eval_const_expressions, working on the COALESCE construct, first
calls itself recursively to simplify the first argument. That leads to
evaluation of ps3(1), and we get back a constant 1. Now we reach a
block of code with this comment:

/*
* We can remove null constants from the list. For a
* non-null constant, if it has not been preceded by any
* other non-null-constant expressions then it is the
* result. Otherwise, it's the next argument, but we can
* drop following arguments since they will never be
* reached.
*/

So at this point we realize that the result of the COALESCE() is 1, and
we don't bother to do const-simplification of its remaining arguments.
They're just thrown away, and the final command for execution is nothing
more than "SELECT 1" (as you can see if you do EXPLAIN VERBOSE).

The other example with sub-SELECTs acts differently because the
sub-SELECT is something of an optimization fence --- "(SELECT 1)" does
not look like a simple Const to eval_const_expressions.

As you noted upthread, none of this is a bug. Labeling a function
immutable is an explicit statement that it has no side-effects of
interest and can be evaluated whenever the system chooses. If you stick
in side-effects like a RAISE statement, then that lets you peer into
some inner workings of the optimizer, but it's you that's breaking the
rules not the optimizer.

regards, tom lane