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

Commit30b4955

Browse files
committed
Fix misuse of RelOptInfo.unique_for_rels cache by SJE
When SJE uses RelOptInfo.unique_for_rels cache, it passes filtered quals toinnerrel_is_unique_ext(). That might lead to an invalid match to cache entriesmade by previous non self-join checking calls. Add UniqueRelInfo.self_joinflag to prevent such cases. Also, fix that SJE should require a strict matchof outerrelids to make sure UniqueRelInfo.extra_clauses are valid.Reported-by: Alexander LakhinDiscussion:https://postgr.es/m/4788f781-31bd-9796-d7d6-588a751c8787%40gmail.com
1 parentd3c5f37 commit30b4955

File tree

4 files changed

+59
-9
lines changed

4 files changed

+59
-9
lines changed

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

Lines changed: 21 additions & 9 deletions
Original file line numberDiff line numberDiff line change
@@ -1247,8 +1247,10 @@ innerrel_is_unique(PlannerInfo *root,
12471247

12481248
/*
12491249
* innerrel_is_unique_ext
1250-
* Do the same as innerrel_is_unique(), but also return additional clauses
1251-
* from a baserestrictinfo list that were used to prove uniqueness.
1250+
* Do the same as innerrel_is_unique(), but also set to '*extra_clauses'
1251+
* additional clauses from a baserestrictinfo list that were used to prove
1252+
* uniqueness. A non NULL 'extra_clauses' indicates that we're checking
1253+
* for self-join and correspondingly dealing with filtered clauses.
12521254
*/
12531255
bool
12541256
innerrel_is_unique_ext(PlannerInfo*root,
@@ -1264,6 +1266,7 @@ innerrel_is_unique_ext(PlannerInfo *root,
12641266
ListCell*lc;
12651267
UniqueRelInfo*uniqueRelInfo;
12661268
List*outer_exprs=NIL;
1269+
boolself_join= (extra_clauses!=NULL);
12671270

12681271
/* Certainly can't prove uniqueness when there are no joinclauses */
12691272
if (restrictlist==NIL)
@@ -1278,16 +1281,23 @@ innerrel_is_unique_ext(PlannerInfo *root,
12781281

12791282
/*
12801283
* Query the cache to see if we've managed to prove that innerrel is
1281-
* unique for any subset of this outerrel. We don't need an exact match,
1282-
* as extra outerrels can't make the innerrel any less unique (or more
1283-
* formally, the restrictlist for a join to a superset outerrel must be a
1284-
* superset of the conditions we successfully used before).
1284+
* unique for any subset of this outerrel. For non self-join search, we
1285+
* don't need an exact match, as extra outerrels can't make the innerrel
1286+
* any less unique (or more formally, the restrictlist for a join to a
1287+
* superset outerrel must be a superset of the conditions we successfully
1288+
* used before). For self-join search, we require an exact match of
1289+
* outerrels, because we need extra clauses to be valid for our case.
1290+
* Also, for self-join checking we've filtered the clauses list. Thus,
1291+
* for a self-join search, we can match only the result cached for another
1292+
* self-join check.
12851293
*/
12861294
foreach(lc,innerrel->unique_for_rels)
12871295
{
12881296
uniqueRelInfo= (UniqueRelInfo*)lfirst(lc);
12891297

1290-
if (bms_is_subset(uniqueRelInfo->outerrelids,outerrelids))
1298+
if ((!self_join&&bms_is_subset(uniqueRelInfo->outerrelids,outerrelids))||
1299+
(self_join&&bms_equal(uniqueRelInfo->outerrelids,outerrelids)&&
1300+
uniqueRelInfo->self_join))
12911301
{
12921302
if (extra_clauses)
12931303
*extra_clauses=uniqueRelInfo->extra_clauses;
@@ -1309,7 +1319,8 @@ innerrel_is_unique_ext(PlannerInfo *root,
13091319

13101320
/* No cached information, so try to make the proof. */
13111321
if (is_innerrel_unique_for(root,joinrelids,outerrelids,innerrel,
1312-
jointype,restrictlist,&outer_exprs))
1322+
jointype,restrictlist,
1323+
self_join ?&outer_exprs :NULL))
13131324
{
13141325
/*
13151326
* Cache the positive result for future probes, being sure to keep it
@@ -1323,8 +1334,9 @@ innerrel_is_unique_ext(PlannerInfo *root,
13231334
*/
13241335
old_context=MemoryContextSwitchTo(root->planner_cxt);
13251336
uniqueRelInfo=makeNode(UniqueRelInfo);
1326-
uniqueRelInfo->extra_clauses=outer_exprs;
13271337
uniqueRelInfo->outerrelids=bms_copy(outerrelids);
1338+
uniqueRelInfo->self_join=self_join;
1339+
uniqueRelInfo->extra_clauses=outer_exprs;
13281340
innerrel->unique_for_rels=lappend(innerrel->unique_for_rels,
13291341
uniqueRelInfo);
13301342
MemoryContextSwitchTo(old_context);

‎src/include/nodes/pathnodes.h

Lines changed: 6 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -3407,6 +3407,12 @@ typedef struct UniqueRelInfo
34073407
*/
34083408
Relidsouterrelids;
34093409

3410+
/*
3411+
* The relation in consideration is unique when considering only clauses
3412+
* suitable for self-join (passed split_selfjoin_quals()).
3413+
*/
3414+
boolself_join;
3415+
34103416
/*
34113417
* Additional clauses from a baserestrictinfo list that were used to prove
34123418
* the uniqueness. We cache it for the self-join checking procedure: a

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

Lines changed: 23 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -6905,6 +6905,29 @@ where false;
69056905
----------
69066906
(0 rows)
69076907

6908+
-- Check that SJE does not mistakenly re-use knowledge of relation uniqueness
6909+
-- made with different set of quals
6910+
insert into emp1 values (2, 1);
6911+
explain (costs off)
6912+
select * from emp1 t1 where exists (select * from emp1 t2
6913+
where t2.id = t1.code and t2.code > 0);
6914+
QUERY PLAN
6915+
---------------------------------------------
6916+
Nested Loop
6917+
-> Seq Scan on emp1 t1
6918+
-> Index Scan using emp1_pkey on emp1 t2
6919+
Index Cond: (id = t1.code)
6920+
Filter: (code > 0)
6921+
(5 rows)
6922+
6923+
select * from emp1 t1 where exists (select * from emp1 t2
6924+
where t2.id = t1.code and t2.code > 0);
6925+
id | code
6926+
----+------
6927+
1 | 1
6928+
2 | 1
6929+
(2 rows)
6930+
69086931
-- We can remove the join even if we find the join can't duplicate rows and
69096932
-- the base quals of each side are different. In the following case we end up
69106933
-- moving quals over to s1 to make it so it can't match any rows.

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

Lines changed: 9 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2638,6 +2638,15 @@ select 1 from emp1 full join
26382638
where false) son true
26392639
where false;
26402640

2641+
-- Check that SJE does not mistakenly re-use knowledge of relation uniqueness
2642+
-- made with different set of quals
2643+
insert into emp1values (2,1);
2644+
explain (costs off)
2645+
select*from emp1 t1where exists (select*from emp1 t2
2646+
wheret2.id=t1.codeandt2.code>0);
2647+
select*from emp1 t1where exists (select*from emp1 t2
2648+
wheret2.id=t1.codeandt2.code>0);
2649+
26412650
-- We can remove the join even if we find the join can't duplicate rows and
26422651
-- the base quals of each side are different. In the following case we end up
26432652
-- moving quals over to s1 to make it so it can't match any rows.

0 commit comments

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