Re: Assert failure of the cross-check for nullingrels - Mailing list pgsql-hackers
| From | Richard Guo |
|---|---|
| Subject | Re: Assert failure of the cross-check for nullingrels |
| Date | |
| Msg-id | CAMbWs48VKcu-eYnqXTpFsbSqPve1aqcGv=mmYaJ4qNC-qUWTdQ@mail.gmail.com Whole thread Raw |
| In response to | Re: Assert failure of the cross-check for nullingrels (Tom Lane <tgl@sss.pgh.pa.us>) |
| Responses |
Re: Assert failure of the cross-check for nullingrels
Re: Assert failure of the cross-check for nullingrels |
| List | pgsql-hackers |
On Fri, May 19, 2023 at 12:33 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Bleah. The other solution I'd been poking at involved adding an
extra check for clone clauses, as attached (note this requires
8a2523ff3). This survives your example, but I wonder if it might
reject all the clones in some cases. It seems a bit expensive
too, although as I said before, I don't think the clone cases get
traversed all that often.
I tried with v4 patch and find that, as you predicted, it might reject
all the clones in some cases. Check the query below
explain (costs off)
select * from t t1
left join t t2 on t1.a = t2.a
left join t t3 on t2.a = t3.a
left join t t4 on t3.a = t4.a and t2.b = t4.b;
QUERY PLAN
------------------------------------------
Hash Left Join
Hash Cond: (t2.b = t4.b)
-> Hash Left Join
Hash Cond: (t2.a = t3.a)
-> Hash Left Join
Hash Cond: (t1.a = t2.a)
-> Seq Scan on t t1
-> Hash
-> Seq Scan on t t2
-> Hash
-> Seq Scan on t t3
-> Hash
-> Seq Scan on t t4
(13 rows)
So the qual 't3.a = t4.a' is missing in this plan shape.
all the clones in some cases. Check the query below
explain (costs off)
select * from t t1
left join t t2 on t1.a = t2.a
left join t t3 on t2.a = t3.a
left join t t4 on t3.a = t4.a and t2.b = t4.b;
QUERY PLAN
------------------------------------------
Hash Left Join
Hash Cond: (t2.b = t4.b)
-> Hash Left Join
Hash Cond: (t2.a = t3.a)
-> Hash Left Join
Hash Cond: (t1.a = t2.a)
-> Seq Scan on t t1
-> Hash
-> Seq Scan on t t2
-> Hash
-> Seq Scan on t t3
-> Hash
-> Seq Scan on t t4
(13 rows)
So the qual 't3.a = t4.a' is missing in this plan shape.
Perhaps another answer could be to compare against syn_righthand
for clone clauses and min_righthand for non-clones? That seems
mighty unprincipled though.
I also checked this solution with the same query.
explain (costs off)
select * from t t1
left join t t2 on t1.a = t2.a
left join t t3 on t2.a = t3.a
left join t t4 on t3.a = t4.a and t2.b = t4.b;
QUERY PLAN
------------------------------------------------------------------
Hash Left Join
Hash Cond: ((t3.a = t4.a) AND (t3.a = t4.a) AND (t2.b = t4.b))
-> Hash Left Join
Hash Cond: (t2.a = t3.a)
-> Hash Left Join
Hash Cond: (t1.a = t2.a)
-> Seq Scan on t t1
-> Hash
-> Seq Scan on t t2
-> Hash
-> Seq Scan on t t3
-> Hash
-> Seq Scan on t t4
(13 rows)
This time the qual 't3.a = t4.a' is back, but twice.
I keep thinking about my proposal in v2 patch. It seems more natural to
me to fix this issue, because an outer join's quals are always treated
as a whole when we check if identity 3 applies in make_outerjoininfo, as
well as when we adjust the outer join's quals for commutation in
deconstruct_distribute_oj_quals. So when it comes to check if quals are
computable at a join level, they should be still treated as a whole.
This should have the same effect regarding qual placement if the quals
of an outer join are in form of 'qual1 OR qual2 OR ...' rather than
'qual1 AND qual2 AND ...'.
Thanks
Richard
explain (costs off)
select * from t t1
left join t t2 on t1.a = t2.a
left join t t3 on t2.a = t3.a
left join t t4 on t3.a = t4.a and t2.b = t4.b;
QUERY PLAN
------------------------------------------------------------------
Hash Left Join
Hash Cond: ((t3.a = t4.a) AND (t3.a = t4.a) AND (t2.b = t4.b))
-> Hash Left Join
Hash Cond: (t2.a = t3.a)
-> Hash Left Join
Hash Cond: (t1.a = t2.a)
-> Seq Scan on t t1
-> Hash
-> Seq Scan on t t2
-> Hash
-> Seq Scan on t t3
-> Hash
-> Seq Scan on t t4
(13 rows)
This time the qual 't3.a = t4.a' is back, but twice.
I keep thinking about my proposal in v2 patch. It seems more natural to
me to fix this issue, because an outer join's quals are always treated
as a whole when we check if identity 3 applies in make_outerjoininfo, as
well as when we adjust the outer join's quals for commutation in
deconstruct_distribute_oj_quals. So when it comes to check if quals are
computable at a join level, they should be still treated as a whole.
This should have the same effect regarding qual placement if the quals
of an outer join are in form of 'qual1 OR qual2 OR ...' rather than
'qual1 AND qual2 AND ...'.
Thanks
Richard
pgsql-hackers by date: