This query (minimal reproducible example):
WITH t as (
SELECT 3 id, 2 price, 0 amount
)
SELECT
CASE WHEN amount > 0 THEN
SUM(price / amount)
ELSE
price
END u_price
FROM t
GROUP BY id, price, amount
on PostgreSQL 9.4 throws
division by zero
Without the SUM it works.
How is this possible?
I liked this question and I turned for help to these tough guys :
The planner is guilty:
A CASE cannot prevent evaluation of an aggregate expression contained within it, because aggregate expressions are computed before other expressions in a SELECT list or HAVING clause are considered
More details at https://www.postgresql.org/docs/10/static/sql-expressions.html#SYNTAX-EXPRESS-EVAL
I cannot figure the "why" part out, but here is a workaround...
WITH t as (
SELECT 3 id, 2 price, 0 amount
)
SELECT SUM(price / case when amount = 0 then 1 else amount end) u_cena
FROM t
GROUP BY id, price, amount
OR: you can use the following and avoid the "case"
SELECT SUM(price / power(amount,sign(amount))) u_cena
FROM t
GROUP BY id, price, amount
... FROM ( values (3, 2, 0) ) as t(id, price, amount) ...works correctly. Might be worth logging as a bug