Re: Coalesce bug ?

Hi,

Interesting idea.
With VOLATILE, the bug disappears.
With IMMUTABLE, the EXPLAIN and the execution does not match !!!!
That is a bug. Even if the behavior has to be different in VOLATILE and IMMUTABLE, the EXPLAIN and the execution MUST
becoherent. 
JG

[postgres@]test=# create or replace function ps3(a int) returns int as $$ BEGIN
test$# RAISE WARNING 'Call ps3(%)=%',$1,$1; RETURN $1::int;
test$# END; $$ LANGUAGE plpgsql STRICT VOLATILE;
CREATE FUNCTION
Temps : 127,417 ms
[postgres@]test=# SELECT ps3(1);
WARNING:  Call ps3(1)=1
 ps3
-----
   1
(1 ligne)

Temps : 0,941 ms
[postgres@]test=# SELECT ps3(2);
WARNING:  Call ps3(2)=2
 ps3
-----
   2
(1 ligne)

Temps : 0,413 ms
[postgres@]test=# select coalesce( (select ps3(1)), (SELECT ps3(2)) );
WARNING:  Call ps3(1)=1
 coalesce
----------
        1
(1 ligne)

Temps : 0,501 ms
[postgres@]test=# explain (verbose, analyze, buffers) select coalesce( (select ps3(1)), (SELECT ps3(2)) );
WARNING:  Call ps3(1)=1
                                         QUERY PLAN

--------------------------------------------------------------------------------
------------
 Result  (cost=0.52..0.53 rows=1 width=0) (actual time=0.072..0.072 rows=1 loops
=1)
   Output: COALESCE($0, $1)
   InitPlan 1 (returns $0)
     ->  Result  (cost=0.00..0.26 rows=1 width=0) (actual time=0.067..0.067 rows
=1 loops=1)
           Output: ps3(1)
   InitPlan 2 (returns $1)
     ->  Result  (cost=0.00..0.26 rows=1 width=0) (never executed)
           Output: ps3(2)
 Total runtime: 0.095 ms
(9 lignes)

Temps : 0,630 ms
[postgres@]test=# select coalesce( ps3(1), ps3(2) );
WARNING:  Call ps3(1)=1
 coalesce
----------
        1
(1 ligne)

Temps : 0,451 ms
[postgres@]test=#