From: | Marti Raudsepp <marti(at)juffo(dot)org> |
---|---|
To: | michael(at)aplaypowered(dot)com |
Cc: | pgsql-bugs(at)postgresql(dot)org, pgsql-docs <pgsql-docs(at)postgresql(dot)org> |
Subject: | Re: [BUGS] BUG #11661: CASE ELSE is evaluated although condition is true |
Date: | 2014-10-13 22:54:12 |
Message-ID: | CABRT9RDMWvoAW-NASCvO08AMZqED5N0jytYHPsoeor=O0VdmMg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs pgsql-docs |
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
Attachment | Content-Type | Size |
---|---|---|
0001-doc-Clarify-evaluation-order-of-aggregates-wrt-condi.patch | binary/octet-stream | 2.3 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Wang, Gang-RSC | 2014-10-14 03:02:29 | Question |
Previous Message | Bruce Momjian | 2014-10-13 21:37:14 | Re: BUG #11474: [Documentation] Add a note about to_date() not accepting TM modifier |
From | Date | Subject | |
---|---|---|---|
Next Message | Josh Kupershmidt | 2014-10-14 16:22:06 | Re: Documentation for CREATE USER |
Previous Message | Bruce Momjian | 2014-10-13 21:24:00 | Re: Huge pages section needs to describe hugetlb_shm_group, memlock limit |