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

Commitd9e4ee7

Browse files
committed
Fix generation of EC join conditions at the wrong plan level.
get_baserel_parampathinfo previously assumed without checking thatthe results of generate_join_implied_equalities "necessarily satisfyjoin_clause_is_movable_into". This turns out to be wrong in thepresence of outer joins, because the generated clauses could includeVars that mustn't be evaluated below a relevant outer join. Thatled to applying clauses at the wrong plan level and possibly gettingincorrect query results. We must check each clause's nullable_relids,and really the right thing to do is test join_clause_is_movable_into.However, trying to fix it that way exposes an oversight inequivclass.c: it wasn't careful about marking join clauses forappendrel children with the correct clause_relids. That caused themodified get_baserel_parampathinfo code to reject some clauses itstill needs to accept. (See parallel commit for HEAD/v16 for morecommentary about that.)Per bug #18429 from Benoît Ryder. This misbehavior existed fora long time before commit2489d76, so patch v12-v15 this way.Discussion:https://postgr.es/m/18429-8982d4a348cc86c6@postgresql.org
1 parentbb418ae commitd9e4ee7

File tree

4 files changed

+83
-7
lines changed

4 files changed

+83
-7
lines changed

‎src/backend/optimizer/path/equivclass.c‎

Lines changed: 15 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1846,6 +1846,21 @@ create_join_clause(PlannerInfo *root,
18461846
rightem->em_nullable_relids),
18471847
ec->ec_min_security);
18481848

1849+
/*
1850+
* If either EM is a child, force the clause's clause_relids to include
1851+
* the relid(s) of the child rel. In normal cases it would already, but
1852+
* not if we are considering appendrel child relations with pseudoconstant
1853+
* translated variables (i.e., UNION ALL sub-selects with constant output
1854+
* items). We must do this so that join_clause_is_movable_into() will
1855+
* think that the clause should be evaluated at the correct place.
1856+
*/
1857+
if (leftem->em_is_child)
1858+
rinfo->clause_relids=bms_add_members(rinfo->clause_relids,
1859+
leftem->em_relids);
1860+
if (rightem->em_is_child)
1861+
rinfo->clause_relids=bms_add_members(rinfo->clause_relids,
1862+
rightem->em_relids);
1863+
18491864
/* Mark the clause as redundant, or not */
18501865
rinfo->parent_ec=parent_ec;
18511866

‎src/backend/optimizer/util/relnode.c‎

Lines changed: 18 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -1282,6 +1282,7 @@ get_baserel_parampathinfo(PlannerInfo *root, RelOptInfo *baserel,
12821282
ParamPathInfo*ppi;
12831283
Relidsjoinrelids;
12841284
List*pclauses;
1285+
List*eqclauses;
12851286
doublerows;
12861287
ListCell*lc;
12871288

@@ -1315,14 +1316,24 @@ get_baserel_parampathinfo(PlannerInfo *root, RelOptInfo *baserel,
13151316
}
13161317

13171318
/*
1318-
* Add in joinclauses generated by EquivalenceClasses, too. (These
1319-
* necessarily satisfy join_clause_is_movable_into.)
1319+
* Add in joinclauses generated by EquivalenceClasses, too. In principle
1320+
* these should always satisfy join_clause_is_movable_into; but if we are
1321+
* below an outer join the clauses might contain Vars that should only be
1322+
* evaluated above the join, so we have to check.
13201323
*/
1321-
pclauses=list_concat(pclauses,
1322-
generate_join_implied_equalities(root,
1323-
joinrelids,
1324-
required_outer,
1325-
baserel));
1324+
eqclauses=generate_join_implied_equalities(root,
1325+
joinrelids,
1326+
required_outer,
1327+
baserel);
1328+
foreach(lc,eqclauses)
1329+
{
1330+
RestrictInfo*rinfo= (RestrictInfo*)lfirst(lc);
1331+
1332+
if (join_clause_is_movable_into(rinfo,
1333+
baserel->relids,
1334+
joinrelids))
1335+
pclauses=lappend(pclauses,rinfo);
1336+
}
13261337

13271338
/* Estimate the number of rows returned by the parameterized scan */
13281339
rows=get_parameterized_baserel_size(root,baserel,pclauses);

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

Lines changed: 31 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -5842,6 +5842,37 @@ select * from
58425842
3 | 3
58435843
(6 rows)
58445844

5845+
-- check for generation of join EC conditions at wrong level (bug #18429)
5846+
explain (costs off)
5847+
select * from (
5848+
select arrayd.ad, coalesce(c.hundred, 0) as h
5849+
from unnest(array[1]) as arrayd(ad)
5850+
left join lateral (
5851+
select hundred from tenk1 where unique2 = arrayd.ad
5852+
) c on true
5853+
) c2
5854+
where c2.h * c2.ad = c2.h * (c2.ad + 1);
5855+
QUERY PLAN
5856+
-------------------------------------------------------------------------------------------------------
5857+
Nested Loop Left Join
5858+
Filter: ((COALESCE(tenk1.hundred, 0) * arrayd.ad) = (COALESCE(tenk1.hundred, 0) * (arrayd.ad + 1)))
5859+
-> Function Scan on unnest arrayd
5860+
-> Index Scan using tenk1_unique2 on tenk1
5861+
Index Cond: (unique2 = arrayd.ad)
5862+
(5 rows)
5863+
5864+
select * from (
5865+
select arrayd.ad, coalesce(c.hundred, 0) as h
5866+
from unnest(array[1]) as arrayd(ad)
5867+
left join lateral (
5868+
select hundred from tenk1 where unique2 = arrayd.ad
5869+
) c on true
5870+
) c2
5871+
where c2.h * c2.ad = c2.h * (c2.ad + 1);
5872+
ad | h
5873+
----+---
5874+
(0 rows)
5875+
58455876
-- check the number of columns specified
58465877
SELECT * FROM (int8_tbl i cross join int4_tbl j) ss(a,b,c,d);
58475878
ERROR: join expression "ss" has 3 columns available but 4 columns specified

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

Lines changed: 19 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1999,6 +1999,25 @@ select * from
19991999
(selectq1.v)
20002000
)as q2;
20012001

2002+
-- check for generation of join EC conditions at wrong level (bug #18429)
2003+
explain (costs off)
2004+
select*from (
2005+
selectarrayd.ad, coalesce(c.hundred,0)as h
2006+
from unnest(array[1])as arrayd(ad)
2007+
left join lateral (
2008+
select hundredfrom tenk1where unique2=arrayd.ad
2009+
) con true
2010+
) c2
2011+
wherec2.h*c2.ad=c2.h* (c2.ad+1);
2012+
select*from (
2013+
selectarrayd.ad, coalesce(c.hundred,0)as h
2014+
from unnest(array[1])as arrayd(ad)
2015+
left join lateral (
2016+
select hundredfrom tenk1where unique2=arrayd.ad
2017+
) con true
2018+
) c2
2019+
wherec2.h*c2.ad=c2.h* (c2.ad+1);
2020+
20022021
-- check the number of columns specified
20032022
SELECT*FROM (int8_tbl icross join int4_tbl j) ss(a,b,c,d);
20042023

0 commit comments

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