Re: Coalesce bug ?

> -----Original Message-----
> From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
> Sent: Friday, December 21, 2012 11:16 AM
> To: David Johnston
> Cc: 'Adrian Klaver'; 'jg'; pgsql-general(at)postgresql(dot)org
> Subject: Re: [GENERAL] Coalesce bug ?
>
> "David Johnston" <polobo(at)yahoo(dot)com> writes:
> > The first case is:
>
> > SELECT COALESCE( (SELECT), (SELECT) ); I am not sure exactly what the
> parentheses surrounding the scalar-sub-SELECTs do (turn them into
> anonymously typed rows?) but if the first scalar-sub-select results in a
non-
> null result then the second one should not be executed.
>
> 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. So
that
> happens, and the RAISE message comes out, at plan time. What's left at
run
> time is
>
> SELECT COALESCE( (SELECT 1), (SELECT 2) );
>
> and indeed the "SELECT 2" is skipped at that point, as is visible in the
EXPLAIN
> ANALYZE measurements.
>
> regards, tom lane

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

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) );
WARNING: Call ps3(1)=1
coalesce
----------
1
(1 row)

It would seem the addition of the sub-select messes with the COALESCE logic.
If the function call is directly a part of the COALESCE statement it can be
optimized away by the COALESCE logic but if it is buried within a SELECT
statement the planner does not know that the function is indirectly part of
a COALESCE input set and so it goes ahead and performs its optimization but
pre-executing the function and caching its results.

David J.