From: Floris Van Nee <florisvannee@Optiver.com>
To: "pgsql-bugs@lists.postgresql.org" <pgsql-bugs@lists.postgresql.org>
Subject: postgres reorders expressions when inlining
Thread-Topic: postgres reorders expressions when inlining
Thread-Index: AdkbcKQk26ojlcUsTtKsiI8Z2XOueQ==
Date: Thu, 29 Dec 2022 10:58:35 +0000
Message-ID: <fe6d0843072a480c82fa48b15eea00b1@opammb0562.comp.optiver.com>
Accept-Language: en-US
Content-Language: en-US
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
MIME-Version: 1.0
Archived-At: 
 <https://www.postgresql.org/message-id/fe6d0843072a480c82fa48b15eea00b1%40opammb0562.comp.optiver.com>
Precedence: bulk

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 adde=
d. I originally ran into it in more complicated queries that involved inlin=
ing of SQL functions instead of subqueries, but it reproduces with subqueri=
es 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_serie=
s(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" c=
heck before checking if b is 0. However, this obviously leads to division b=
y 0. In many other cases (for example not using left join lateral but inste=
ad putting the calculation straight into the SELECT of the subquery), Postg=
res does the right thing and checks for 0 first. Also, forcefully disabling=
 inlining (by adding "offset 0" to the subquery for example) makes the quer=
y execute correctly.

Is it expected that Postgres reorders these expressions? I'd think that, ev=
en though Postgres decides to inline a query, it should at least evaluate t=
he expressions of the inner-part before the outer part to avoid the divisio=
n by zero.

-Floris