5

Why does this simple query result in a "division by zero" Error?

select 
case when b > 0 then sum(a / b) end
from (values (1,1),(2,1),(1,0),(2,0)) t (a,b)
group by b

I would expect the output:

case
3
NULL

The only explanation I have is that postgres calculates the sum before doing the grouping and evaluating the case.

Bergi
  • 630,263
  • 148
  • 957
  • 1,375
ArcticLord
  • 3,999
  • 3
  • 27
  • 47
  • ¡Good question! Probably PostgreSQL is trying to be smart and it's pre-calculating things before filtering. – The Impaler Jan 26 '22 at 12:58
  • Using `sum(case when b > 0 then a / b end)` instead [does yield the expected result](https://dbfiddle.uk/?rdbms=postgres_14&fiddle=4aaf283d1f949e18852ef24995d83da0). – Bergi Jan 26 '22 at 14:02
  • I think it kinda makes sense that `a / b` runs on every row before the aggregation, then the `case of` expression runs only once (per group) after the aggregation on the result of the `sum(…)`. The hypothetical future conditionals don't make a difference to that - what if the condition was `avg(b) > 0`? I don't have enough knowledge of the SQL spec to provide a definitive answer though. – Bergi Jan 26 '22 at 14:09

2 Answers2

6

See the documentation:

[…] 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. For example, the following query can cause a division-by-zero error despite seemingly having protected against it:

SELECT CASE WHEN min(employees) > 0
            THEN avg(expenses / employees)
       END
FROM departments;

So it is expected that aggregate expression sum(a/b) are computed before other expressions. This applies not only to Postgresql, but also to Sql Server too.

Bergi
  • 630,263
  • 148
  • 957
  • 1,375
Anton Grig
  • 1,640
  • 7
  • 11
2

Use NULLIF() to avoid the problem, you don't need a CASE for this:

SELECT  
     SUM(a / NULLIF(b,0))
FROM 
    (values (1,1),(2,1),(1,0),(2,0)) t (a,b)
GROUP BY b
ORDER BY 1; -- first (and only) column
Frank Heikens
  • 117,544
  • 24
  • 142
  • 135