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

Commit9a2dbc6

Browse files
committed
Fix oversight in outer join removal.
A placeholder that references the outer join's relid in ph_eval_atis logically "above" the join, and therefore we can't remove itsPlaceHolderInfo: it might still be used somewhere in the query.This was not an issue pre-v16 because we failed to remove the joinat all in such cases. The new outer-join-aware-Var infrastructurepermits deducing that it's okay to remove the join, but then wehave to clean up correctly afterwards.Report and fix by Richard GuoDiscussion:https://postgr.es/m/CAMbWs4_tuVn9EwwMcggGiZJWWstdXX_ci8FeEU17vs+4nLgw3w@mail.gmail.com
1 parentfbf8042 commit9a2dbc6

File tree

3 files changed

+93
-1
lines changed

3 files changed

+93
-1
lines changed

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

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -425,7 +425,8 @@ remove_rel_from_query(PlannerInfo *root, int relid, SpecialJoinInfo *sjinfo)
425425

426426
Assert(!bms_is_member(relid,phinfo->ph_lateral));
427427
if (bms_is_subset(phinfo->ph_needed,joinrelids)&&
428-
bms_is_member(relid,phinfo->ph_eval_at))
428+
bms_is_member(relid,phinfo->ph_eval_at)&&
429+
!bms_is_member(ojrelid,phinfo->ph_eval_at))
429430
{
430431
root->placeholder_list=foreach_delete_current(root->placeholder_list,
431432
l);

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

Lines changed: 57 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -5842,6 +5842,63 @@ where ss.stringu2 !~* ss.case1;
58425842
doh!
58435843
(1 row)
58445844

5845+
rollback;
5846+
-- test cases where we can remove a join, but not a PHV computed at it
5847+
begin;
5848+
create temp table t (a int unique, b int);
5849+
insert into t values (1,1), (2,2);
5850+
explain (costs off)
5851+
select 1
5852+
from t t1
5853+
left join (select t2.a, 1 as c
5854+
from t t2 left join t t3 on t2.a = t3.a) s
5855+
on true
5856+
left join t t4 on true
5857+
where s.a < s.c;
5858+
QUERY PLAN
5859+
-------------------------------------
5860+
Nested Loop Left Join
5861+
-> Nested Loop
5862+
-> Seq Scan on t t1
5863+
-> Materialize
5864+
-> Seq Scan on t t2
5865+
Filter: (a < 1)
5866+
-> Materialize
5867+
-> Seq Scan on t t4
5868+
(8 rows)
5869+
5870+
explain (costs off)
5871+
select t1.a, s.*
5872+
from t t1
5873+
left join lateral (select t2.a, coalesce(t1.a, 1) as c
5874+
from t t2 left join t t3 on t2.a = t3.a) s
5875+
on true
5876+
left join t t4 on true
5877+
where s.a < s.c;
5878+
QUERY PLAN
5879+
-----------------------------------------------
5880+
Nested Loop Left Join
5881+
-> Nested Loop
5882+
-> Seq Scan on t t1
5883+
-> Seq Scan on t t2
5884+
Filter: (a < COALESCE(t1.a, 1))
5885+
-> Materialize
5886+
-> Seq Scan on t t4
5887+
(7 rows)
5888+
5889+
select t1.a, s.*
5890+
from t t1
5891+
left join lateral (select t2.a, coalesce(t1.a, 1) as c
5892+
from t t2 left join t t3 on t2.a = t3.a) s
5893+
on true
5894+
left join t t4 on true
5895+
where s.a < s.c;
5896+
a | a | c
5897+
---+---+---
5898+
2 | 1 | 2
5899+
2 | 1 | 2
5900+
(2 rows)
5901+
58455902
rollback;
58465903
-- test case to expose miscomputation of required relid set for a PHV
58475904
explain (verbose, costs off)

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

Lines changed: 34 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2153,6 +2153,40 @@ where ss.stringu2 !~* ss.case1;
21532153

21542154
rollback;
21552155

2156+
-- test cases where we can remove a join, but not a PHV computed at it
2157+
begin;
2158+
2159+
create temp table t (aint unique, bint);
2160+
insert into tvalues (1,1), (2,2);
2161+
2162+
explain (costs off)
2163+
select1
2164+
from t t1
2165+
left join (selectt2.a,1as c
2166+
from t t2left join t t3ont2.a=t3.a) s
2167+
on true
2168+
left join t t4on true
2169+
wheres.a<s.c;
2170+
2171+
explain (costs off)
2172+
selectt1.a, s.*
2173+
from t t1
2174+
left join lateral (selectt2.a, coalesce(t1.a,1)as c
2175+
from t t2left join t t3ont2.a=t3.a) s
2176+
on true
2177+
left join t t4on true
2178+
wheres.a<s.c;
2179+
2180+
selectt1.a, s.*
2181+
from t t1
2182+
left join lateral (selectt2.a, coalesce(t1.a,1)as c
2183+
from t t2left join t t3ont2.a=t3.a) s
2184+
on true
2185+
left join t t4on true
2186+
wheres.a<s.c;
2187+
2188+
rollback;
2189+
21562190
-- test case to expose miscomputation of required relid set for a PHV
21572191
explain (verbose, costs off)
21582192
select i8.*,ss.v,t.unique2

0 commit comments

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