Re: Do not scan index in right table if condition for left join evaluates to false using columns in left table

On 14/09/2026 18:26, Bryan Green wrote:
> On 9/12/2026 7:02 AM, Andrei Lepikhov wrote:
>> On 01/09/2026 11:16, Andrei Lepikhov wrote:
>> So the real question is whether the cost model can account for these two terms
>> well enough to choose between a gated and a non-gated NestLoop, assuming we turn
>> this into a cost-based optimisation.
>>
> I've continued doing security audits of commitfest patches, and as part
> of that I occasionally turn up differences in output that aren't
> security issues but seemed worth passing along. v3 has a few. They all
> show up on the gated nestloop plan, so I forced it with:
>
> set enable_hashjoin = off;
> set enable_mergejoin = off;
>
> 1) A left join that returns a row on HEAD raises an error with the patch:
>
> create table o (k int, x int); insert into o values (1, 0);
> create table i (k int); -- empty
> select * from o left join i on o.k = i.k and 1/o.x = 1;
> -- HEAD: (1, 0, NULL)
> -- v3: ERROR: division by zero

That’s a false positive. I believe I addressed this topic thoroughly in my blog
post [1] to prevent such comments ;). Please take a look and let me know if you
have any further questions.

>
> 2) A volatile condition returns a different number of rows:
>
> create table a (k int, x int); insert into a values (1, 2);
> create table b (k int, y int); insert into b values
> (1,1),(1,2),(1,3),(1,4);
> create sequence s;
> select a.k, b.y from a left join b on a.k = b.k and a.x > nextval('s');
> -- HEAD: 1 row
> -- v3: 4 rows

This is an actual catch. Postgres has a convention on pushdown safety,
implemented in qual_is_pushdown_safe, and I was wrong to ignore it. I believe
the new version of the first patch (attached) contains the necessary fixes.

[1]
https://www.pgedge.com/blog/binary-ternary-or-is-it-actually-quaternary-logic-in-postgres-functions

--
regards, Andrei Lepikhov,
pgEdge