pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
postgres 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>
  2022-12-29 14:02 ` Re: postgres reorders expressions when inlining David G. Johnston <david.g.johnston@gmail.com>
  2022-12-29 15:14 ` Re: postgres reorders expressions when inlining Tom Lane <tgl@sss.pgh.pa.us>
  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 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>
  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 10:58 postgres reorders expressions when inlining Floris Van Nee <florisvannee@Optiver.com>
@ 2022-12-29 15:14 ` Tom Lane <tgl@sss.pgh.pa.us>
  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