From: | "jg" <jg(at)rilk(dot)com> |
---|---|
To: | "Chris Angelico" <rosuav(at)gmail(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Coalesce bug ? |
Date: | 2012-12-21 15:40:00 |
Message-ID: | 4974-50d48300-13-6b8b4580@118860038 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(at)]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(at)]test=# SELECT ps3(1);
WARNING: Call ps3(1)=1
ps3
-----
1
(1 ligne)
Temps : 0,975 ms
[postgres(at)]test=# SELECT ps3(2);
WARNING: Call ps3(2)=2
ps3
-----
2
(1 ligne)
Temps : 0,473 ms
[postgres(at)]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(at)]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(at)]test=# select coalesce( ps3(1), ps3(2) );
WARNING: Call ps3(1)=1
coalesce
----------
1
(1 ligne)
Temps : 0,562 ms
[postgres(at)]test=#
There is a bug !
Thank you for the documentation link, but it does not help me.
JG
From | Date | Subject | |
---|---|---|---|
Next Message | Denis Papathanasiou | 2012-12-21 15:42:50 | Re: Using POSIX Regular Expressions on xml type fields gives inconsistent results |
Previous Message | David Johnston | 2012-12-21 15:35:44 | Re: Using POSIX Regular Expressions on xml type fields gives inconsistent results |