Re: [BUGS] BUG #11661: CASE ELSE is evaluated although condition is true

The following bug has been logged on the website:

Bug reference: 11661
Logged by: Michael Stieler
Email address: michael(at)aplaypowered(dot)com
PostgreSQL version: 9.2.6
Operating system: RHEL
Description:

I am not sure if it is a bug, a lack in documentation or just a
misunderstanding.
This issue refers to a StackOverflow question at
https://stackoverflow.com/questions/26297035/posgtres-case-condition-with-sum-aggregation-evaluates-not-needed-else-part

The following query:
case when sum( column1 ) = 0
then 0
else round( sum( price
* hours
/ column1 ), 2 )
raises a division by zero error, although the condition sum(column1) = 0 is
true. I would expect that the else part would never be evaluated.
Documentation only describes that this happens for immediates.

On Mon, Oct 13, 2014 at 2:05 PM, <michael(at)aplaypowered(dot)com> wrote:
> case when sum( column1 ) = 0
> then 0
> else round( sum( price
> * hours
> / column1 ), 2 )

> I would expect that the else part would never be evaluated.
> Documentation only describes that this happens for immediates.

I explained this behavior in the Stack Overflow answer, it is caused
by way aggregates are evaluated in a SELECT (or HAVING) clause.

Our documentation should be clearer about the fact that all aggregates
are evaluated strictly before their surrounding SELECT expressions,
the attached documentation patch adds a note about this to the
"general processing of SELECT" explanation.

I also created a section "Implementation Notes" in the "Conditional
Expressions" chapter to explain this behavior, since conditionals is
where this behavior is most visible.

This similarly affects the HAVING clause:
db=# select sum(1/i) from unnest(array[0])i having min(i)>0;
ERROR: division by zero

Regards,
Marti

On Tue, Oct 14, 2014 at 7:54 AM, Marti Raudsepp <marti(at)juffo(dot)org> wrote:
> Our documentation should be clearer about the fact that all aggregates
> are evaluated strictly before their surrounding SELECT expressions,
> the attached documentation patch adds a note about this to the
> "general processing of SELECT" explanation.

This patch does not compile properly as a </sect2> is missing, and I
think that you actually want to add this sect2 block at the bottom the
sect1 block for conditional functions. "implementation" is as well a
bit too general to name such a section, something like
"functions-conditional-implementation" would be better. Also, I am a
bit dubious about adding a mention aggregate functions and their
elements being evaluated in a paragraph for GROUP BY and HAVING
clause. All together only mentioning that in the conditional function
block sound enough. All those things grouped together result in the
attached. Marti, feel free to comment and correct what you think is
wrong :)
Regards,
--
Michael

Hi

On Tue, Nov 11, 2014 at 9:14 AM, Michael Paquier
<michael(dot)paquier(at)gmail(dot)com> wrote:
> This patch does not compile properly as a </sect2> is missing, and I
> think that you actually want to add this sect2 block at the bottom the
> sect1 block for conditional functions.

Sorry, I never got around to testing the build because I found a
working DocBook envrionment a PITA to set up. Should have mentioned
that in my email.

> "implementation" is as well a
> bit too general to name such a section, something like
> "functions-conditional-implementation" would be better.

+1

> Also, I am a
> bit dubious about adding a mention aggregate functions and their
> elements being evaluated in a paragraph for GROUP BY and HAVING
> clause.

Why? This page explains in what order the expressions in a SELECT
query are processed. Aggregates can only be found in SELECT queries
and their semantics are non-trivial, so it seems like a big omission
to me that their relation to other clauses is not discussed.

I think that's the most appropriate place for that, because GROUP BY,
HAVING and aggregates are closely related. And their execution occurs
after evaluating GROUP BY keys and before HAVING predicates, where
else would one put it?

Regards,
Marti

On Tue, Nov 11, 2014 at 4:37 PM, Marti Raudsepp <marti(at)juffo(dot)org> wrote:
> Why? This page explains in what order the expressions in a SELECT
> query are processed. Aggregates can only be found in SELECT queries
> and their semantics are non-trivial, so it seems like a big omission
> to me that their relation to other clauses is not discussed.
>
> I think that's the most appropriate place for that, because GROUP BY,
> HAVING and aggregates are closely related. And their execution occurs
> after evaluating GROUP BY keys and before HAVING predicates, where
> else would one put it?
Ah, OK. I wasn't aware of this execution order. Now we could as well
split the description flow into more tiny paragraphs but this does not
seem worth it. So what about the attached then?
--
Michael

On Tue, Nov 11, 2014 at 9:52 AM, Michael Paquier
<michael(dot)paquier(at)gmail(dot)com> wrote:
> Ah, OK. I wasn't aware of this execution order. Now we could as well
> split the description flow into more tiny paragraphs but this does not
> seem worth it.

Agreed.

Actually now that I read it...

+ values. All aggregate functions and their argument expressions
+ in the <command>SELECT</> and <literal>HAVING</> are evaluated.

Perhaps should be:
<command>SELECT</> and <literal>HAVING</> clauses are evaluated.

Other than that, looks good to me.

Regards,
Marti

On Tue, Nov 11, 2014 at 5:26 PM, Marti Raudsepp <marti(at)juffo(dot)org> wrote:
> Perhaps should be:
> <command>SELECT</> and <literal>HAVING</> clauses are evaluated.
>
> Other than that, looks good to me.
Let's do so then and mark that as ready for committer.
Thanks,
--
Michael

On Tue, Nov 11, 2014 at 3:20 PM, Michael Paquier
<michael(dot)paquier(at)gmail(dot)com> wrote:
> On Tue, Nov 11, 2014 at 5:26 PM, Marti Raudsepp <marti(at)juffo(dot)org> wrote:
>> Perhaps should be:
>> <command>SELECT</> and <literal>HAVING</> clauses are evaluated.

> Let's do so then

Oops, that should be my task, but you got there first. :)

> and mark that as ready for committer.

Thanks!

Regards,
Marti

Michael Paquier <michael(dot)paquier(at)gmail(dot)com> writes:
> On Tue, Nov 11, 2014 at 5:26 PM, Marti Raudsepp <marti(at)juffo(dot)org> wrote:
>> Perhaps should be:
>> <command>SELECT</> and <literal>HAVING</> clauses are evaluated.
>>
>> Other than that, looks good to me.

> Let's do so then and mark that as ready for committer.

I rewrote this somewhat and committed it.

regards, tom lane