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

Commitab24022

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 parent6fa5e67 commitab24022

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
@@ -1880,6 +1880,21 @@ create_join_clause(PlannerInfo *root,
18801880
rightem->em_nullable_relids),
18811881
ec->ec_min_security);
18821882

1883+
/*
1884+
* If either EM is a child, force the clause's clause_relids to include
1885+
* the relid(s) of the child rel. In normal cases it would already, but
1886+
* not if we are considering appendrel child relations with pseudoconstant
1887+
* translated variables (i.e., UNION ALL sub-selects with constant output
1888+
* items). We must do this so that join_clause_is_movable_into() will
1889+
* think that the clause should be evaluated at the correct place.
1890+
*/
1891+
if (leftem->em_is_child)
1892+
rinfo->clause_relids=bms_add_members(rinfo->clause_relids,
1893+
leftem->em_relids);
1894+
if (rightem->em_is_child)
1895+
rinfo->clause_relids=bms_add_members(rinfo->clause_relids,
1896+
rightem->em_relids);
1897+
18831898
/* Mark the clause as redundant, or not */
18841899
rinfo->parent_ec=parent_ec;
18851900

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

Lines changed: 18 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -1297,6 +1297,7 @@ get_baserel_parampathinfo(PlannerInfo *root, RelOptInfo *baserel,
12971297
ParamPathInfo*ppi;
12981298
Relidsjoinrelids;
12991299
List*pclauses;
1300+
List*eqclauses;
13001301
doublerows;
13011302
ListCell*lc;
13021303

@@ -1330,14 +1331,24 @@ get_baserel_parampathinfo(PlannerInfo *root, RelOptInfo *baserel,
13301331
}
13311332

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

13421353
/* Estimate the number of rows returned by the parameterized scan */
13431354
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