From: Floris Van Nee <florisvannee@Optiver.com>
To: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
Subject: postgres reorders expressions when inlining
Date: Thu, 29 Dec 2022 10:58:35 +0000
Message-ID: <fe6d0843072a480c82fa48b15eea00b1@opammb0562.comp.optiver.com> (raw)
Hi hackers,
I recently ran into some behavior with inlining that seemed strange to me. I managed to break it down into a small reproducible example that I've added. I originally ran into it in more complicated queries that involved inlining of SQL functions instead of subqueries, but it reproduces with subqueries too so that's the example I've added. Tested on v15.
create table t1 as select a, b from generate_series(0, 5) a, generate_series(0, 10) b;
select * from (
select a, b, c
from t1
left join lateral
(
select 1 / b as c
) c on true
where b <> 0
) as o
where o.c is not null
The SELECT query leads to a division by zero. Plan:
Seq Scan on t1
Filter: (((1 / b) IS NOT NULL) AND (b <> 0))
Postgres seems to decide that it's smart to execute the "o.c is not null" check before checking if b is 0. However, this obviously leads to division by 0. In many other cases (for example not using left join lateral but instead putting the calculation straight into the SELECT of the subquery), Postgres does the right thing and checks for 0 first. Also, forcefully disabling inlining (by adding "offset 0" to the subquery for example) makes the query execute correctly.
Is it expected that Postgres reorders these expressions? I'd think that, even though Postgres decides to inline a query, it should at least evaluate the expressions of the inner-part before the outer part to avoid the division by zero.
-Floris
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: florisvannee@Optiver.com, pgsql-bugs@lists.postgresql.org
Subject: Re: postgres reorders expressions when inlining
In-Reply-To: <fe6d0843072a480c82fa48b15eea00b1@opammb0562.comp.optiver.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox