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

Commita43b190

Browse files
committed
Fix a thinko in join_is_legal: when we decide we can implement a semijoin
by unique-ifying the RHS and then inner-joining to some other relation,that is not grounds for violating the RHS of some other outer join.Noticed while regression-testing new GEQO code, which will blindly followany path that join_is_legal says is legal, and then complain later if thatleads to a dead end.I'm not certain that this can result in any visible failure in 8.4: themistake may always be masked by the fact that subsequent attempts to jointhe rest of the RHS of the other join will fail. But I'm not certain itcan't, either, and it's definitely not operating as intended. So back-patch.The added regression test depends on the new no-failures-allowed logicthat I'm about to commit in GEQO, so no point back-patching that.
1 parent011eae6 commita43b190

File tree

3 files changed

+36
-3
lines changed

3 files changed

+36
-3
lines changed

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

Lines changed: 12 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/optimizer/path/joinrels.c,v 1.100 2009/06/11 14:48:59 momjian Exp $
11+
* $PostgreSQL: pgsql/src/backend/optimizer/path/joinrels.c,v 1.101 2009/07/19 20:32:48 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -349,6 +349,7 @@ join_is_legal(PlannerInfo *root, RelOptInfo *rel1, RelOptInfo *rel2,
349349
{
350350
SpecialJoinInfo*match_sjinfo;
351351
boolreversed;
352+
boolunique_ified;
352353
boolis_valid_inner;
353354
ListCell*l;
354355

@@ -366,6 +367,7 @@ join_is_legal(PlannerInfo *root, RelOptInfo *rel1, RelOptInfo *rel2,
366367
*/
367368
match_sjinfo=NULL;
368369
reversed= false;
370+
unique_ified= false;
369371
is_valid_inner= true;
370372

371373
foreach(l,root->join_info_list)
@@ -450,6 +452,7 @@ join_is_legal(PlannerInfo *root, RelOptInfo *rel1, RelOptInfo *rel2,
450452
return false;/* invalid join path */
451453
match_sjinfo=sjinfo;
452454
reversed= false;
455+
unique_ified= true;
453456
}
454457
elseif (sjinfo->jointype==JOIN_SEMI&&
455458
bms_equal(sjinfo->syn_righthand,rel1->relids)&&
@@ -461,6 +464,7 @@ join_is_legal(PlannerInfo *root, RelOptInfo *rel1, RelOptInfo *rel2,
461464
return false;/* invalid join path */
462465
match_sjinfo=sjinfo;
463466
reversed= true;
467+
unique_ified= true;
464468
}
465469
else
466470
{
@@ -510,8 +514,13 @@ join_is_legal(PlannerInfo *root, RelOptInfo *rel1, RelOptInfo *rel2,
510514
}
511515
}
512516

513-
/* Fail if violated some SJ's RHS and didn't match to another SJ */
514-
if (match_sjinfo==NULL&& !is_valid_inner)
517+
/*
518+
* Fail if violated some SJ's RHS and didn't match to another SJ.
519+
* However, "matching" to a semijoin we are implementing by
520+
* unique-ification doesn't count (think: it's really an inner join).
521+
*/
522+
if (!is_valid_inner&&
523+
(match_sjinfo==NULL||unique_ified))
515524
return false;/* invalid join path */
516525

517526
/* Otherwise, it's a valid join */

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

Lines changed: 14 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2150,6 +2150,20 @@ select count(*) from tenk1 x where
21502150
1
21512151
(1 row)
21522152

2153+
-- try that with GEQO too
2154+
begin;
2155+
set geqo = on;
2156+
set geqo_threshold = 2;
2157+
select count(*) from tenk1 x where
2158+
x.unique1 in (select a.f1 from int4_tbl a,float8_tbl b where a.f1=b.f1) and
2159+
x.unique1 = 0 and
2160+
x.unique1 in (select aa.f1 from int4_tbl aa,float8_tbl bb where aa.f1=bb.f1);
2161+
count
2162+
-------
2163+
1
2164+
(1 row)
2165+
2166+
rollback;
21532167
--
21542168
-- Clean up
21552169
--

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

Lines changed: 10 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -343,6 +343,16 @@ select count(*) from tenk1 x where
343343
x.unique1=0and
344344
x.unique1in (selectaa.f1from int4_tbl aa,float8_tbl bbwhereaa.f1=bb.f1);
345345

346+
-- try that with GEQO too
347+
begin;
348+
set geqo=on;
349+
set geqo_threshold=2;
350+
selectcount(*)from tenk1 xwhere
351+
x.unique1in (selecta.f1from int4_tbl a,float8_tbl bwherea.f1=b.f1)and
352+
x.unique1=0and
353+
x.unique1in (selectaa.f1from int4_tbl aa,float8_tbl bbwhereaa.f1=bb.f1);
354+
rollback;
355+
346356

347357
--
348358
-- Clean up

0 commit comments

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