Search Postgresql Archives

Re: Coalesce bug ?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Hi,

Please test this script on a PostgreSQL 9.1.6,

create or replace function ps3(a int) returns int as $$ BEGIN
RAISE WARNING 'Call ps3(%)=%',$1,$1; RETURN $1::int;
END; $$ LANGUAGE plpgsql STRICT IMMUTABLE;
SELECT ps3(1);
SELECT ps3(2);
select coalesce( (select ps3(1)), (SELECT ps3(2)) );
explain (verbose, analyze, buffers) select coalesce( (select ps3(1)), (SELECT ps3(2)) );
select coalesce( ps3(1), ps3(2) );


The result will be

[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 IMMUTABLE;
CREATE FUNCTION
Temps : 13,232 ms
[postgres@]test=# SELECT ps3(1);
WARNING:  Call ps3(1)=1
 ps3 
-----
   1
(1 ligne)

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

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

Temps : 0,681 ms
[postgres@]test=# explain (verbose, analyze, buffers) select coalesce( (select ps3(1)), (SELECT ps3(2)) );
WARNING:  Call ps3(1)=1
WARNING:  Call ps3(2)=2
                                         QUERY PLAN                             
            
--------------------------------------------------------------------------------
------------
 Result  (cost=0.02..0.03 rows=1 width=0) (actual time=0.004..0.004 rows=1 loops
=1)
   Output: COALESCE($0, $1)
   InitPlan 1 (returns $0)
     ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.000 rows
=1 loops=1)
           Output: 1
   InitPlan 2 (returns $1)
     ->  Result  (cost=0.00..0.01 rows=1 width=0) (never executed)
           Output: 2
 Total runtime: 0.022 ms
(9 lignes)

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

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


There is a bug !

Thank you for the documentation link, but it does not help me.

JG


-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux