Обсуждение: BUG #14301: function in case expression called when it should not be

VGhlIGZvbGxvd2luZyBidWcgaGFzIGJlZW4gbG9nZ2VkIG9uIHRoZSB3ZWJz
aXRlOgoKQnVnIHJlZmVyZW5jZTogICAgICAxNDMwMQpMb2dnZWQgYnk6ICAg
ICAgICAgIFJpa2FyZCBQYXZlbGljCkVtYWlsIGFkZHJlc3M6ICAgICAgcmlr
YXJkQG5ncy5ocgpQb3N0Z3JlU1FMIHZlcnNpb246IDkuNC45Ck9wZXJhdGlu
ZyBzeXN0ZW06ICAgQ2VudE9TCkRlc2NyaXB0aW9uOiAgICAgICAgCgpJIHdh
cyBpbnZlc3RpZ2F0aW5nIHNvbWUgcGVyZm9ybWFuY2UgaXNzdWVzIGFuZCBz
dHVtYmxlZCB1cG9uIHRoaXMKYmVoYXZpb3I6DQoNCmNyZWF0ZSBmdW5jdGlv
biBzbG93RnVuY3Rpb24oKSByZXR1cm5zIGludCBhcyAkJCANCmJlZ2luDQoJ
cmFpc2Ugbm90aWNlICdjYWxsZWQnOw0KCXJldHVybiAwOw0KZW5kOw0KJCQg
bGFuZ3VhZ2UgcGxwZ3NxbCB2b2xhdGlsZTsNCg0KY3JlYXRlIHRhYmxlIHNv
bWV0aGluZyAoaSBpbnQsIGRvU2xvdyBib29sZWFuKTsNCmluc2VydCBpbnRv
IHNvbWV0aGluZyB2YWx1ZXMoMSwgZmFsc2UpLCgyLGZhbHNlKTsNCg0Kc2Vs
ZWN0IGNhc2Ugd2hlbiBkb1Nsb3cgdGhlbiBzdW0oc2xvd0Z1bmN0aW9uKCkp
IGVsc2Ugc3VtKDEpIGVuZCBhcyB0b3RhbApmcm9tIHNvbWV0aGluZyBncm91
cCBieSBkb1Nsb3cNCg0KUG9zdGdyZXMgd2lsbCBjb3JyZWN0bHkgZXZhbHVh
dGUgdGhlIGxhc3QgcXVlcnksIGJ1dCBpdCB3aWxsIGFsc28gY2FsbCB0aGUK
cGxwZ3NxbCB2b2xhdGlsZSBmdW5jdGlvbi4NCg0KTHVja2x5IGZvciB1cywg
dGhpcyB3YXMgcmVhZCBvbmx5IGZ1bmN0aW9uLCBidXQgUG9zdGdyZXMgd2ls
bCBoYXBwaWx5CmV4ZWN1dGUgRE1MIGNvbW1hbmRzIGluc2lkZSBpdCA7KAoK

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

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

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/