From: Tom Lane <tgl@sss.pgh.pa.us>
To: Floris Van Nee <florisvannee@Optiver.com>
cc: "pgsql-bugs@lists.postgresql.org" <pgsql-bugs@lists.postgresql.org>
Subject: Re: postgres reorders expressions when inlining
In-reply-to: <fe6d0843072a480c82fa48b15eea00b1@opammb0562.comp.optiver.com>
References: <fe6d0843072a480c82fa48b15eea00b1@opammb0562.comp.optiver.com>
Comments: In-reply-to Floris Van Nee <florisvannee@Optiver.com>
	message dated "Thu, 29 Dec 2022 10:58:35 +0000"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <553277.1672326872.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Thu, 29 Dec 2022 10:14:32 -0500
Message-ID: <553278.1672326872@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/553278.1672326872%40sss.pgh.pa.us>
Precedence: bulk

Floris Van Nee <florisvannee@Optiver.com> writes:
> I recently ran into some behavior with inlining that seemed strange to m=
e. 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 su=
bqueries 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_se=
ries(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