Why I get division by zero for PostgreSQL v13? I suppose rows should be filtered out

user_4632019 0
Viewed 89

Running same query on different versions of PostgreSQL gives different result.

WITH t(a, b) AS (VALUES (1, 0))
SELECT a / b
  FROM t
 WHERE b <> 0;

On v11.10 I get no rows as expected fiddle
On v13.1 I get ERROR: division by zero fiddle

Why I get division by zero for PostgreSQL v13.1? I suppose rows should be filtered out as it done for v11.10

UPD
I think when we join, optimizer should not call lateral function sometimes (when it may) and save time: fiddle

where-clause postgresql sql
2 Answers

Newer versions of PostgreSQL (starting in 12) look through your strange use of the constant single row VALUES list to see the constant-folding opportunity. But it folds the division constant before it folds the WHERE constant, and so trips over the error.

In 11, you would still get the error in other similar places, like:

select 1/0 from pgbench_accounts where aid < -100;

This can be annoying if you are trying to come up with a very simple example not useful in its own right, but just to test something else. But I just chalk it up to "Play stupid games, win stupid prizes", and find some other way to test what I want to test. In your case, you can just make the CTE be materialized.

WITH t(a, b) AS materialized (VALUES (1, 0))
SELECT a / b            
  FROM t
 WHERE b <> 0;
user_17212390

It looks the order of evaluation is not what one would expect it to be: that is, one would expect that 0 denominators would be filtered out by the where clause before the computation in the select clause happens. Obviously the database chooses to do things differently.

You can, however, easily work around this with nullif():

with t(a, b) as (values (1, 0))
select a / nullif(b, 0)
from t
where b <> 0;

This properly produce no rows.

I would tend qualifying this as a bug, or at least a regression. Playing around in db<>fiddle, your original code works fine in versions 9.4, 9.5, 9.6, 10 and 11, but fails in versions 12 and 13.

user_106767160
Related