Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit5ef34a8

Browse files
committed
Fix the issue that SJE mistakenly omits qual clauses
When the SJE code handles the transfer of qual clauses from the removedrelation to the remaining one, it replaces the Vars of the removedrelation with the Vars of the remaining relation for each clause, andthen reintegrates these clauses into the appropriate restriction or joinclause lists, while attempting to avoid duplicates.However, the code compares RestrictInfo->clause to determine if twoclauses are duplicates. This is just flat wrong. Two RestrictInfoswith the same clause can have different required_relids,incompatible_relids, is_pushed_down, and so on. This can cause qualclauses to be mistakenly omitted, leading to wrong results.This patch fixes it by comparing the entire RestrictInfos not just theirclauses ignoring 'rinfo_serial' field (otherwise almost all RestrictInfos willbe unique). Making 'rinfo_serial' equal_ignore would break other code. Thisis why this commit implements our own comparison function for checking theequality of RestrictInfos.Reported-by: Zuming JiangBug: #18261Discussion:https://postgr.es/m/18261-2a75d748c928609b%40postgresql.orgAuthor: Richard Guo
1 parent43b46aa commit5ef34a8

File tree

3 files changed

+65
-4
lines changed

3 files changed

+65
-4
lines changed

‎src/backend/optimizer/plan/analyzejoins.c

Lines changed: 24 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1657,6 +1657,28 @@ update_eclasses(EquivalenceClass *ec, int from, int to)
16571657
ec->ec_relids=replace_relid(ec->ec_relids,from,to);
16581658
}
16591659

1660+
/*
1661+
* "Logically" compares two RestrictInfo's ignoring the 'rinfo_serial' field,
1662+
* which makes almost every RestrictInfo unique. This type of comparison is
1663+
* useful when removing duplicates while moving RestrictInfo's from removed
1664+
* relation to remaining relation during self-join elimination.
1665+
*
1666+
* XXX: In the future, we might remove the 'rinfo_serial' field completely and
1667+
* get rid of this function.
1668+
*/
1669+
staticbool
1670+
restrict_infos_logically_equal(RestrictInfo*a,RestrictInfo*b)
1671+
{
1672+
intsaved_rinfo_serial=a->rinfo_serial;
1673+
boolresult;
1674+
1675+
a->rinfo_serial=b->rinfo_serial;
1676+
result=equal(a,b);
1677+
a->rinfo_serial=saved_rinfo_serial;
1678+
1679+
returnresult;
1680+
}
1681+
16601682
/*
16611683
* Remove a relation after we have proven that it participates only in an
16621684
* unneeded unique self join.
@@ -1760,7 +1782,7 @@ remove_self_join_rel(PlannerInfo *root, PlanRowMark *kmark, PlanRowMark *rmark,
17601782
if (src==rinfo||
17611783
(rinfo->parent_ec!=NULL
17621784
&&src->parent_ec==rinfo->parent_ec)
1763-
||equal(rinfo->clause,src->clause))
1785+
||restrict_infos_logically_equal(rinfo,src))
17641786
{
17651787
is_redundant= true;
17661788
break;
@@ -1788,7 +1810,7 @@ remove_self_join_rel(PlannerInfo *root, PlanRowMark *kmark, PlanRowMark *rmark,
17881810
if (src==rinfo||
17891811
(rinfo->parent_ec!=NULL
17901812
&&src->parent_ec==rinfo->parent_ec)
1791-
||equal(rinfo->clause,src->clause))
1813+
||restrict_infos_logically_equal(rinfo,src))
17921814
{
17931815
is_redundant= true;
17941816
break;

‎src/test/regress/expected/join.out

Lines changed: 25 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -6742,7 +6742,7 @@ explain (costs off) select 1 from
67426742
reset join_collapse_limit;
67436743
reset enable_seqscan;
67446744
-- Check that clauses from the join filter list is not lost on the self-join removal
6745-
CREATE TABLE emp1 (id SERIAL PRIMARY KEY NOT NULL, code int);
6745+
CREATE TABLE emp1 (id SERIAL PRIMARY KEY NOT NULL, code int);
67466746
explain (verbose, costs off)
67476747
SELECT * FROM emp1 e1, emp1 e2 WHERE e1.id = e2.id AND e2.code <> e1.code;
67486748
QUERY PLAN
@@ -6880,6 +6880,30 @@ WHERE t1.id = emp1.id RETURNING emp1.id, emp1.code;
68806880
Filter: (id IS NOT NULL)
68816881
(3 rows)
68826882

6883+
-- Check that SJE does not mistakenly omit qual clauses (bug #18187)
6884+
explain (costs off)
6885+
select 1 from emp1 full join
6886+
(select * from emp1 t1 join
6887+
emp1 t2 join emp1 t3 on t2.id = t3.id
6888+
on true
6889+
where false) s on true
6890+
where false;
6891+
QUERY PLAN
6892+
--------------------------
6893+
Result
6894+
One-Time Filter: false
6895+
(2 rows)
6896+
6897+
select 1 from emp1 full join
6898+
(select * from emp1 t1 join
6899+
emp1 t2 join emp1 t3 on t2.id = t3.id
6900+
on true
6901+
where false) s on true
6902+
where false;
6903+
?column?
6904+
----------
6905+
(0 rows)
6906+
68836907
-- We can remove the join even if we find the join can't duplicate rows and
68846908
-- the base quals of each side are different. In the following case we end up
68856909
-- moving quals over to s1 to make it so it can't match any rows.

‎src/test/regress/sql/join.sql

Lines changed: 16 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -2568,7 +2568,7 @@ reset join_collapse_limit;
25682568
reset enable_seqscan;
25692569

25702570
-- Check that clauses from the join filter list is not lost on the self-join removal
2571-
CREATETABLEemp1 (idSERIALPRIMARY KEYNOT NULL, codeint);
2571+
CREATETABLEemp1 (idSERIALPRIMARY KEYNOT NULL, codeint);
25722572
explain (verbose, costs off)
25732573
SELECT*FROM emp1 e1, emp1 e2WHEREe1.id=e2.idANDe2.code<>e1.code;
25742574

@@ -2622,6 +2622,21 @@ WITH t1 AS (SELECT * FROM emp1)
26222622
UPDATE emp1SET code=t1.code+1FROM t1
26232623
WHEREt1.id=emp1.id RETURNINGemp1.id,emp1.code;
26242624

2625+
-- Check that SJE does not mistakenly omit qual clauses (bug #18187)
2626+
explain (costs off)
2627+
select1from emp1 fulljoin
2628+
(select*from emp1 t1join
2629+
emp1 t2join emp1 t3ont2.id=t3.id
2630+
on true
2631+
where false) son true
2632+
where false;
2633+
select1from emp1 fulljoin
2634+
(select*from emp1 t1join
2635+
emp1 t2join emp1 t3ont2.id=t3.id
2636+
on true
2637+
where false) son true
2638+
where false;
2639+
26252640
-- We can remove the join even if we find the join can't duplicate rows and
26262641
-- the base quals of each side are different. In the following case we end up
26272642
-- moving quals over to s1 to make it so it can't match any rows.

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp