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