pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedpostgres reorders expressions when inlining
3+ messages / 3 participants
[nested] [flat]
* postgres reorders expressions when inlining
@ 2022-12-29 10:58 Floris Van Nee <florisvannee@Optiver.com>
0 siblings, 2 replies; 3+ messages in thread
From: Floris Van Nee @ 2022-12-29 10:58 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: postgres reorders expressions when inlining
@ 2022-12-29 14:02 David G. Johnston <david.g.johnston@gmail.com>
parent: Floris Van Nee <florisvannee@Optiver.com>
1 sibling, 0 replies; 3+ messages in thread
From: David G. Johnston @ 2022-12-29 14:02 UTC (permalink / raw)
To: Floris Van Nee <florisvannee@optiver.com>; +Cc: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
On Thursday, December 29, 2022, Floris Van Nee <florisvannee@optiver.com>
wrote:
>
>
> 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.
>
>
Yes, this is working as intended, expression order is not something that is
recognized by the system . In this case protect your division with a case
expression.
David J.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: postgres reorders expressions when inlining
@ 2022-12-29 15:14 Tom Lane <tgl@sss.pgh.pa.us>
parent: Floris Van Nee <florisvannee@Optiver.com>
1 sibling, 0 replies; 3+ messages in thread
From: Tom Lane @ 2022-12-29 15:14 UTC (permalink / raw)
To: Floris Van Nee <florisvannee@Optiver.com>; +Cc: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
Floris Van Nee <florisvannee@Optiver.com> writes:
> 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:
This is not a bug. We make no ordering guarantees about WHERE clause
execution; if we did, it would cripple the planner's ability to
optimize.
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2022-12-29 15:14 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-12-29 10:58 postgres reorders expressions when inlining Floris Van Nee <florisvannee@Optiver.com>
2022-12-29 14:02 ` David G. Johnston <david.g.johnston@gmail.com>
2022-12-29 15:14 ` Tom Lane <tgl@sss.pgh.pa.us>
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