Wrong results due to missing quals

Поиск
Список
Период
Сортировка
От Richard Guo
Тема Wrong results due to missing quals
Дата
Msg-id CAMbWs48EYi_9-pSd0ORes1kTmTeAjT4Q3gu49hJtYCbSn2JyeA@mail.gmail.com
обсуждение исходный текст
Ответы Re: Wrong results due to missing quals  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-hackers
Testing with SQLancer reports a wrong results issue on master and I
reduced it to the repro query below.

create table t (a int, b int);

explain (costs off)
select * from t t1 left join
    (t t2 left join t t3 full join t t4 on false on false)
    left join t t5 on t2.a = t5.a
on t2.b = 1;
                    QUERY PLAN
--------------------------------------------------
 Nested Loop Left Join
   ->  Seq Scan on t t1
   ->  Materialize
         ->  Nested Loop Left Join
               ->  Nested Loop Left Join
                     Join Filter: false
                     ->  Seq Scan on t t2
                           Filter: (b = 1)
                     ->  Result
                           One-Time Filter: false
               ->  Materialize
                     ->  Seq Scan on t t5
(12 rows)

So the qual 't2.a = t5.a' is missing.

I looked into it and found that both clones of this joinqual are
rejected by clause_is_computable_at, because their required_relids do
not include the outer join of t2/(t3/t4), and meanwhile include nullable
rels of this outer join.

I think the root cause is that, as Tom pointed out in [1], we're not
maintaining required_relids very accurately.  In b9c755a2, we make
clause_is_computable_at test required_relids for clone clauses.  I think
this is how this issue sneaks in.

To fix it, it seems to me that the ideal way would be to always compute
accurate required_relids.  But I'm not sure how difficult it is.

[1] https://www.postgresql.org/message-id/395264.1684698283%40sss.pgh.pa.us

Thanks
Richard

В списке pgsql-hackers по дате отправления:

Предыдущее
От: Daniel Gustafsson
Дата:
Сообщение: Re: Cutting support for OpenSSL 1.0.1 and 1.0.2 in 17~?
Следующее
От: "Drouvot, Bertrand"
Дата:
Сообщение: Re: pgsql: TAP test for logical decoding on standby