Обсуждение: postgres reorders expressions when inlining
Hi hackers, I recently ran into some behavior with inlining that seemed strange to me. I managed to break it down into a small reproducibleexample that I've added. I originally ran into it in more complicated queries that involved inlining of SQL functionsinstead 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, thisobviously leads to division by 0. In many other cases (for example not using left join lateral but instead putting thecalculation straight into the SELECT of the subquery), Postgres does the right thing and checks for 0 first. Also, forcefullydisabling 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
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.
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 reproducibleexample that I've added. I originally ran into it in more complicated queries that involved inlining of SQL functionsinstead 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