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

Commit5aacfa6

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 parent689ba4f commit5aacfa6

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
@@ -1851,6 +1851,21 @@ create_join_clause(PlannerInfo *root,
18511851
rightem->em_nullable_relids),
18521852
ec->ec_min_security);
18531853

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

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

Lines changed: 18 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -1300,6 +1300,7 @@ get_baserel_parampathinfo(PlannerInfo *root, RelOptInfo *baserel,
13001300
ParamPathInfo*ppi;
13011301
Relidsjoinrelids;
13021302
List*pclauses;
1303+
List*eqclauses;
13031304
doublerows;
13041305
ListCell*lc;
13051306

@@ -1333,14 +1334,24 @@ get_baserel_parampathinfo(PlannerInfo *root, RelOptInfo *baserel,
13331334
}
13341335

13351336
/*
1336-
* Add in joinclauses generated by EquivalenceClasses, too. (These
1337-
* necessarily satisfy join_clause_is_movable_into.)
1337+
* Add in joinclauses generated by EquivalenceClasses, too. In principle
1338+
* these should always satisfy join_clause_is_movable_into; but if we are
1339+
* below an outer join the clauses might contain Vars that should only be
1340+
* evaluated above the join, so we have to check.
13381341
*/
1339-
pclauses=list_concat(pclauses,
1340-
generate_join_implied_equalities(root,
1341-
joinrelids,
1342-
required_outer,
1343-
baserel));
1342+
eqclauses=generate_join_implied_equalities(root,
1343+
joinrelids,
1344+
required_outer,
1345+
baserel);
1346+
foreach(lc,eqclauses)
1347+
{
1348+
RestrictInfo*rinfo= (RestrictInfo*)lfirst(lc);
1349+
1350+
if (join_clause_is_movable_into(rinfo,
1351+
baserel->relids,
1352+
joinrelids))
1353+
pclauses=lappend(pclauses,rinfo);
1354+
}
13441355

13451356
/* Estimate the number of rows returned by the parameterized scan */
13461357
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
@@ -5949,6 +5949,37 @@ select * from
59495949
3 | 3
59505950
(6 rows)
59515951

5952+
-- check for generation of join EC conditions at wrong level (bug #18429)
5953+
explain (costs off)
5954+
select * from (
5955+
select arrayd.ad, coalesce(c.hundred, 0) as h
5956+
from unnest(array[1]) as arrayd(ad)
5957+
left join lateral (
5958+
select hundred from tenk1 where unique2 = arrayd.ad
5959+
) c on true
5960+
) c2
5961+
where c2.h * c2.ad = c2.h * (c2.ad + 1);
5962+
QUERY PLAN
5963+
-------------------------------------------------------------------------------------------------------
5964+
Nested Loop Left Join
5965+
Filter: ((COALESCE(tenk1.hundred, 0) * arrayd.ad) = (COALESCE(tenk1.hundred, 0) * (arrayd.ad + 1)))
5966+
-> Function Scan on unnest arrayd
5967+
-> Index Scan using tenk1_unique2 on tenk1
5968+
Index Cond: (unique2 = arrayd.ad)
5969+
(5 rows)
5970+
5971+
select * from (
5972+
select arrayd.ad, coalesce(c.hundred, 0) as h
5973+
from unnest(array[1]) as arrayd(ad)
5974+
left join lateral (
5975+
select hundred from tenk1 where unique2 = arrayd.ad
5976+
) c on true
5977+
) c2
5978+
where c2.h * c2.ad = c2.h * (c2.ad + 1);
5979+
ad | h
5980+
----+---
5981+
(0 rows)
5982+
59525983
-- check the number of columns specified
59535984
SELECT * FROM (int8_tbl i cross join int4_tbl j) ss(a,b,c,d);
59545985
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
@@ -2029,6 +2029,25 @@ select * from
20292029
(selectq1.v)
20302030
)as q2;
20312031

2032+
-- check for generation of join EC conditions at wrong level (bug #18429)
2033+
explain (costs off)
2034+
select*from (
2035+
selectarrayd.ad, coalesce(c.hundred,0)as h
2036+
from unnest(array[1])as arrayd(ad)
2037+
left join lateral (
2038+
select hundredfrom tenk1where unique2=arrayd.ad
2039+
) con true
2040+
) c2
2041+
wherec2.h*c2.ad=c2.h* (c2.ad+1);
2042+
select*from (
2043+
selectarrayd.ad, coalesce(c.hundred,0)as h
2044+
from unnest(array[1])as arrayd(ad)
2045+
left join lateral (
2046+
select hundredfrom tenk1where unique2=arrayd.ad
2047+
) con true
2048+
) c2
2049+
wherec2.h*c2.ad=c2.h* (c2.ad+1);
2050+
20322051
-- check the number of columns specified
20332052
SELECT*FROM (int8_tbl icross join int4_tbl j) ss(a,b,c,d);
20342053

0 commit comments

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