Thread: 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@aplaypowered.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@aplaypowered.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
Attachment
On Tue, Oct 14, 2014 at 7:54 AM, Marti Raudsepp <marti@juffo.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
Attachment
Hi On Tue, Nov 11, 2014 at 9:14 AM, Michael Paquier <michael.paquier@gmail.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@juffo.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
Attachment
On Tue, Nov 11, 2014 at 9:52 AM, Michael Paquier <michael.paquier@gmail.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@juffo.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
Attachment
On Tue, Nov 11, 2014 at 3:20 PM, Michael Paquier <michael.paquier@gmail.com> wrote: > On Tue, Nov 11, 2014 at 5:26 PM, Marti Raudsepp <marti@juffo.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.paquier@gmail.com> writes:
> On Tue, Nov 11, 2014 at 5:26 PM, Marti Raudsepp <marti@juffo.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