Wrong query results caused by loss of join quals

From: Richard Guo <guofenglinux(at)gmail(dot)com>
To: PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Wrong query results caused by loss of join quals
Date: 2023-02-15 03:31:44
Message-ID: CAMbWs4-DsTBfOvXuw64GdFss2=M5cwtEhY=0DCS7t2gT7P6hSA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

I came across $subject on HEAD and here is the query I'm using.

create table t1 (a int, b int);
create table t2 (a int, b int);
create table t3 (a int, b int);

insert into t1 values (1, 1);
insert into t2 values (2, 200);
insert into t3 values (3, 3);

# select * from t1 left join t2 on true, lateral (select * from t3 where
t2.a = t2.b) ss;
a | b | a | b | a | b
---+---+---+-----+---+---
1 | 1 | 2 | 200 | 3 | 3
(1 row)

# explain (costs off) select * from t1 left join t2 on true, lateral
(select * from t3 where t2.a = t2.b) ss;
QUERY PLAN
----------------------------------
Nested Loop
-> Nested Loop Left Join
-> Seq Scan on t1
-> Materialize
-> Seq Scan on t2
-> Materialize
-> Seq Scan on t3
(7 rows)

As we can see, the join qual 't2.a = t2.b' disappears in the plan, and
that results in the wrong query results.

I did some dig and here is what happened. Firstly both sides of qual
't2.a = t2.b' could be nulled by the OJ t1/t2 and they are marked so in
their varnullingrels. Then we decide that this qual can form a EC, and
the EC's ec_relids is marked as {t2, t1/t2}. Note that t1 is not
included in this ec_relids. So when it comes to building joinrel for
t1/t2, generate_join_implied_equalities fails to generate the join qual
from that EC.

I'm not sure how to fix this problem yet. I'm considering that while
composing eclass_indexes for each base rel, when we come across an
ojrelid in ec->ec_relids, can we instead mark the base rels in the OJ's
min_lefthand/min_righthand that they are 'mentioned' in this EC?
Something like the TODO says.

i = -1;
while ((i = bms_next_member(ec->ec_relids, i)) > 0)
{
RelOptInfo *rel = root->simple_rel_array[i];

if (rel == NULL) /* must be an outer join */
{
Assert(bms_is_member(i, root->outer_join_rels));
+ /*
+ * TODO Mark the base rels in the OJ's min_xxxhand that they
+ * are 'mentioned' in this EC.
+ */
continue;
}

Assert(rel->reloptkind == RELOPT_BASEREL);

rel->eclass_indexes = bms_add_member(rel->eclass_indexes,
ec_index);

if (can_generate_joinclause)
rel->has_eclass_joins = true;
}

Or maybe we can just expand ec->ec_relids to include OJ's min_xxxhand
when we form a new EC?

Thanks
Richard

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message shiy.fnst@fujitsu.com 2023-02-15 03:53:34 RE: [PATCH] Use indexes on the subscriber when REPLICA IDENTITY is full on the publisher
Previous Message Stephen Frost 2023-02-15 03:31:34 Re: REASSIGN OWNED vs ALTER TABLE OWNER TO permission inconsistencies