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

Commitf502849

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 parent4b0e5d6 commitf502849

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
@@ -1465,6 +1465,21 @@ create_join_clause(PlannerInfo *root,
14651465
rightem->em_nullable_relids),
14661466
ec->ec_min_security);
14671467

1468+
/*
1469+
* If either EM is a child, force the clause's clause_relids to include
1470+
* the relid(s) of the child rel. In normal cases it would already, but
1471+
* not if we are considering appendrel child relations with pseudoconstant
1472+
* translated variables (i.e., UNION ALL sub-selects with constant output
1473+
* items). We must do this so that join_clause_is_movable_into() will
1474+
* think that the clause should be evaluated at the correct place.
1475+
*/
1476+
if (leftem->em_is_child)
1477+
rinfo->clause_relids=bms_add_members(rinfo->clause_relids,
1478+
leftem->em_relids);
1479+
if (rightem->em_is_child)
1480+
rinfo->clause_relids=bms_add_members(rinfo->clause_relids,
1481+
rightem->em_relids);
1482+
14681483
/* Mark the clause as redundant, or not */
14691484
rinfo->parent_ec=parent_ec;
14701485

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

Lines changed: 18 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -1264,6 +1264,7 @@ get_baserel_parampathinfo(PlannerInfo *root, RelOptInfo *baserel,
12641264
ParamPathInfo*ppi;
12651265
Relidsjoinrelids;
12661266
List*pclauses;
1267+
List*eqclauses;
12671268
doublerows;
12681269
ListCell*lc;
12691270

@@ -1297,14 +1298,24 @@ get_baserel_parampathinfo(PlannerInfo *root, RelOptInfo *baserel,
12971298
}
12981299

12991300
/*
1300-
* Add in joinclauses generated by EquivalenceClasses, too. (These
1301-
* necessarily satisfy join_clause_is_movable_into.)
1301+
* Add in joinclauses generated by EquivalenceClasses, too. In principle
1302+
* these should always satisfy join_clause_is_movable_into; but if we are
1303+
* below an outer join the clauses might contain Vars that should only be
1304+
* evaluated above the join, so we have to check.
13021305
*/
1303-
pclauses=list_concat(pclauses,
1304-
generate_join_implied_equalities(root,
1305-
joinrelids,
1306-
required_outer,
1307-
baserel));
1306+
eqclauses=generate_join_implied_equalities(root,
1307+
joinrelids,
1308+
required_outer,
1309+
baserel);
1310+
foreach(lc,eqclauses)
1311+
{
1312+
RestrictInfo*rinfo= (RestrictInfo*)lfirst(lc);
1313+
1314+
if (join_clause_is_movable_into(rinfo,
1315+
baserel->relids,
1316+
joinrelids))
1317+
pclauses=lappend(pclauses,rinfo);
1318+
}
13081319

13091320
/* Estimate the number of rows returned by the parameterized scan */
13101321
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
@@ -5672,6 +5672,37 @@ select * from
56725672
3 | 3
56735673
(6 rows)
56745674

5675+
-- check for generation of join EC conditions at wrong level (bug #18429)
5676+
explain (costs off)
5677+
select * from (
5678+
select arrayd.ad, coalesce(c.hundred, 0) as h
5679+
from unnest(array[1]) as arrayd(ad)
5680+
left join lateral (
5681+
select hundred from tenk1 where unique2 = arrayd.ad
5682+
) c on true
5683+
) c2
5684+
where c2.h * c2.ad = c2.h * (c2.ad + 1);
5685+
QUERY PLAN
5686+
-------------------------------------------------------------------------------------------------------
5687+
Nested Loop Left Join
5688+
Filter: ((COALESCE(tenk1.hundred, 0) * arrayd.ad) = (COALESCE(tenk1.hundred, 0) * (arrayd.ad + 1)))
5689+
-> Function Scan on unnest arrayd
5690+
-> Index Scan using tenk1_unique2 on tenk1
5691+
Index Cond: (unique2 = arrayd.ad)
5692+
(5 rows)
5693+
5694+
select * from (
5695+
select arrayd.ad, coalesce(c.hundred, 0) as h
5696+
from unnest(array[1]) as arrayd(ad)
5697+
left join lateral (
5698+
select hundred from tenk1 where unique2 = arrayd.ad
5699+
) c on true
5700+
) c2
5701+
where c2.h * c2.ad = c2.h * (c2.ad + 1);
5702+
ad | h
5703+
----+---
5704+
(0 rows)
5705+
56755706
-- check the number of columns specified
56765707
SELECT * FROM (int8_tbl i cross join int4_tbl j) ss(a,b,c,d);
56775708
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
@@ -1920,6 +1920,25 @@ select * from
19201920
(selectq1.v)
19211921
)as q2;
19221922

1923+
-- check for generation of join EC conditions at wrong level (bug #18429)
1924+
explain (costs off)
1925+
select*from (
1926+
selectarrayd.ad, coalesce(c.hundred,0)as h
1927+
from unnest(array[1])as arrayd(ad)
1928+
left join lateral (
1929+
select hundredfrom tenk1where unique2=arrayd.ad
1930+
) con true
1931+
) c2
1932+
wherec2.h*c2.ad=c2.h* (c2.ad+1);
1933+
select*from (
1934+
selectarrayd.ad, coalesce(c.hundred,0)as h
1935+
from unnest(array[1])as arrayd(ad)
1936+
left join lateral (
1937+
select hundredfrom tenk1where unique2=arrayd.ad
1938+
) con true
1939+
) c2
1940+
wherec2.h*c2.ad=c2.h* (c2.ad+1);
1941+
19231942
-- check the number of columns specified
19241943
SELECT*FROM (int8_tbl icross join int4_tbl j) ss(a,b,c,d);
19251944

0 commit comments

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