Hi,
> 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
Thank you for the explanation.
It was tricky to get it, and I got this question as a side effect of some other optimisation works.
--
Cordialement,
Jean-Gérard Pailloncy
