From: | Richard Guo <guofenglinux(at)gmail(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | zuming(dot)jiang(at)inf(dot)ethz(dot)ch, pgsql-bugs(at)lists(dot)postgresql(dot)org |
Subject: | Re: BUG #17978: Unexpected error: "wrong varnullingrels (b) (expected (b 5)) for Var 6/2" triggered by JOIN |
Date: | 2023-06-19 10:21:43 |
Message-ID: | CAMbWs48pMV0m6UathaqkUtrC=93TsikSPxQnKYun1+SoJxt0Yw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
On Sun, Jun 18, 2023 at 3:57 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Temporarily disabling the setrefs.c crosscheck shows that it's
> trying to create this plan:
>
> which is the wrong join order: the filter condition can't be
> applied at that join level. So the nullingrel cross-check
> has caught a real bug, but why the bug?
FWIW, I found that we have an existing test query in sql/join.sql that
almost exposes this issue.
explain (costs off)
select * from int4_tbl t1
left join ((select t2.f1 from int4_tbl t2
left join int4_tbl t3 on t2.f1 > 0
where t3.f1 is null) s
left join tenk1 t4 on s.f1 > 1)
on s.f1 = t1.f1;
If we change the WHERE clause to 't2.f1 != coalesce(t3.f1, 1)', we will
see this issue.
explain (costs off)
select * from int4_tbl t1
left join ((select t2.f1 from int4_tbl t2
left join int4_tbl t3 on t2.f1 > 0
where t2.f1 != coalesce(t3.f1, 1)) s
left join tenk1 t4 on s.f1 > 1)
on s.f1 = t1.f1;
ERROR: wrong varnullingrels (b) (expected (b 5)) for Var 6/1
Thanks
Richard
From | Date | Subject | |
---|---|---|---|
Next Message | PG Bug reporting form | 2023-06-19 11:52:16 | BUG #17981: HY000 server closed the connection unexpectedly |
Previous Message | Thomas Munro | 2023-06-19 09:30:12 | Re: BUG #17949: Adding an index introduces serialisation anomalies. |