BUG #14301: function in case expression called when it should not be

rikard@ngs.hr writes:

select case when doSlow then sum(slowFunction()) else sum(1) end as total
from something group by doSlow

You are misunderstanding how aggregates work. The aggregates are
evaluated first, then the surrounding expressions are done once at
the end of the query (or group). The fact that the CASE might choose
the other branch at the end doesn't eliminate the necessity to run
both aggregates across all the rows.

regards, tom lane

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

On Mon, Aug 29, 2016 at 03:14:33PM +0000, rikard@ngs.hr wrote:

The following bug has been logged on the website:

Bug reference: 14301
Logged by: Rikard Pavelic
Email address: rikard@ngs.hr
PostgreSQL version: 9.4.9
Operating system: CentOS
Description:

I was investigating some performance issues and stumbled upon this
behavior:

create function slowFunction() returns int as $$
begin
raise notice 'called';
return 0;
end;
$$ language plpgsql volatile;

create table something (i int, doSlow boolean);
insert into something values(1, false),(2,false);

select case when doSlow then sum(slowFunction()) else sum(1) end as total
from something group by doSlow

Postgres will correctly evaluate the last query, but it will also call the
plpgsql volatile function.

Luckly for us, this was read only function, but Postgres will happily
execute DML commands inside it ;(

This is the expected behavior. You might look into FILTER clauses in
your queries, which might be closer to the behavior you want.

Best,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

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

On Mon, 29 Aug 2016 13:44:19 -0700
David Fetter <david@fetter.org> wrote:

This is the expected behavior. You might look into FILTER clauses in
your queries, which might be closer to the behavior you want.

Yeah, I realized why it's behaving that way after Tom's answer.
I did not write such horrible code, but I worked around it by repeating
case when within the sum :)

Regards,
Rikard

--
Rikard Pavelic
https://dsl-platform.com/
http://templater.info/

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