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

Commitc8c03d7

Browse files
committed
Fix another join removal bug: the check on PlaceHolderVars was wrong.
The previous coding would decide that join removal was unsafe upon findinga PlaceHolderVar that needed to be evaluated at the inner rel and then usedabove the join. However, this fails to cover the case of PlaceHolderVarsthat refer to both the inner rel and some other rels. Per bug report fromAndrus.
1 parent635de83 commitc8c03d7

File tree

3 files changed

+49
-5
lines changed

3 files changed

+49
-5
lines changed

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

Lines changed: 13 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -26,6 +26,7 @@
2626
#include"optimizer/pathnode.h"
2727
#include"optimizer/paths.h"
2828
#include"optimizer/planmain.h"
29+
#include"optimizer/var.h"
2930

3031
/* local functions */
3132
staticbooljoin_is_removable(PlannerInfo*root,SpecialJoinInfo*sjinfo);
@@ -197,16 +198,23 @@ join_is_removable(PlannerInfo *root, SpecialJoinInfo *sjinfo)
197198
}
198199

199200
/*
200-
* Similarly check that the inner rel doesn't produce any PlaceHolderVars
201-
* that will be used above the join.
201+
* Similarly check that the inner rel isn't needed by any PlaceHolderVars
202+
* that will be used above the join. We only need to fail if such a PHV
203+
* actually references some inner-rel attributes; but the correct check
204+
* for that is relatively expensive, so we first check against ph_eval_at,
205+
* which must mention the inner rel if the PHV uses any inner-rel attrs.
202206
*/
203207
foreach(l,root->placeholder_list)
204208
{
205209
PlaceHolderInfo*phinfo= (PlaceHolderInfo*)lfirst(l);
206210

207-
if (bms_is_subset(phinfo->ph_eval_at,innerrel->relids)&&
208-
!bms_is_subset(phinfo->ph_needed,joinrelids))
209-
return false;
211+
if (bms_is_subset(phinfo->ph_needed,joinrelids))
212+
continue;/* PHV is not used above the join */
213+
if (!bms_overlap(phinfo->ph_eval_at,innerrel->relids))
214+
continue;/* it definitely doesn't reference innerrel */
215+
if (bms_overlap(pull_varnos((Node*)phinfo->ph_var),
216+
innerrel->relids))
217+
return false;/* it does reference innerrel */
210218
}
211219

212220
/*

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

Lines changed: 21 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2644,3 +2644,24 @@ SELECT b.* FROM b LEFT JOIN a ON (b.a_id = a.id) WHERE (a.id IS NULL OR a.id > 0
26442644
(1 row)
26452645

26462646
rollback;
2647+
-- another join removal bug: this is not optimizable, either
2648+
begin;
2649+
create temp table innertab (id int8 primary key, dat1 int8);
2650+
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "innertab_pkey" for table "innertab"
2651+
insert into innertab values(123, 42);
2652+
SELECT * FROM
2653+
(SELECT 1 AS x) ss1
2654+
LEFT JOIN
2655+
(SELECT q1, q2, COALESCE(dat1, q1) AS y
2656+
FROM int8_tbl LEFT JOIN innertab ON q2 = id) ss2
2657+
ON true;
2658+
x | q1 | q2 | y
2659+
---+------------------+-------------------+------------------
2660+
1 | 123 | 456 | 123
2661+
1 | 123 | 4567890123456789 | 123
2662+
1 | 4567890123456789 | 123 | 42
2663+
1 | 4567890123456789 | 4567890123456789 | 4567890123456789
2664+
1 | 4567890123456789 | -4567890123456789 | 4567890123456789
2665+
(5 rows)
2666+
2667+
rollback;

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

Lines changed: 15 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -644,3 +644,18 @@ SELECT * FROM b LEFT JOIN a ON (b.a_id = a.id) WHERE (a.id IS NULL OR a.id > 0);
644644
SELECT b.*FROM bLEFT JOIN aON (b.a_id=a.id)WHERE (a.id ISNULLORa.id>0);
645645

646646
rollback;
647+
648+
-- another join removal bug: this is not optimizable, either
649+
begin;
650+
651+
create temp table innertab (id int8primary key, dat1 int8);
652+
insert into innertabvalues(123,42);
653+
654+
SELECT*FROM
655+
(SELECT1AS x) ss1
656+
LEFT JOIN
657+
(SELECT q1, q2, COALESCE(dat1, q1)AS y
658+
FROM int8_tblLEFT JOIN innertabON q2= id) ss2
659+
ON true;
660+
661+
rollback;

0 commit comments

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