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

Commit58ea074

Browse files
author
Richard Guo
committed
Fix semijoin unique-ification for child relations
For a child relation, we should not assume that its parent'sunique-ified relation (or unique-ified path in v18) always exists. Incases where all RHS columns that need to be unique-ified are equatedto constants, the unique-ified relation/path for the parent table isnot built, as there are no columns left to unique-ify. Failing toaccount for this can result in a SIGSEGV crash during planning.This patch checks whether the parent's unique-ified relation or pathexists and skips unique-ification of the child relation if it doesnot.Author: Richard Guo <guofenglinux@gmail.com>Discussion:https://postgr.es/m/CAMbWs49MOdLW2c+qbLHHBt8VBu=4ONpM91D19=AWeW93eFUF6A@mail.gmail.comBackpatch-through: 18
1 parent5146886 commit58ea074

File tree

3 files changed

+106
-0
lines changed

3 files changed

+106
-0
lines changed

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

Lines changed: 9 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1753,6 +1753,15 @@ create_unique_path(PlannerInfo *root, RelOptInfo *rel, Path *subpath,
17531753
if (!(sjinfo->semi_can_btree||sjinfo->semi_can_hash))
17541754
returnNULL;
17551755

1756+
/*
1757+
* Punt if this is a child relation and we failed to build a unique-ified
1758+
* path for its parent. This can happen if all the RHS columns were found
1759+
* to be equated to constants when unique-ifying the parent table, leaving
1760+
* no columns to unique-ify.
1761+
*/
1762+
if (IS_OTHER_REL(rel)&&rel->top_parent->cheapest_unique_path==NULL)
1763+
returnNULL;
1764+
17561765
/*
17571766
* When called during GEQO join planning, we are in a short-lived memory
17581767
* context. We must make sure that the path and any subsidiary data

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

Lines changed: 60 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -6580,6 +6580,66 @@ where exists (select 1 from t t4
65806580
1
65816581
(1 row)
65826582

6583+
rollback;
6584+
-- check handling of semijoins if all RHS columns are equated to constants: we
6585+
-- should suppress unique-ification in this case.
6586+
begin;
6587+
create temp table t (a int, b int);
6588+
insert into t values (1, 2);
6589+
explain (costs off)
6590+
select * from t t1, t t2 where exists
6591+
(select 1 from t t3 where t1.a = t3.a and t2.b = t3.b and t3.a = 1 and t3.b = 2);
6592+
QUERY PLAN
6593+
---------------------------------------------
6594+
Nested Loop Semi Join
6595+
-> Nested Loop
6596+
-> Seq Scan on t t1
6597+
Filter: (a = 1)
6598+
-> Materialize
6599+
-> Seq Scan on t t2
6600+
Filter: (b = 2)
6601+
-> Materialize
6602+
-> Seq Scan on t t3
6603+
Filter: ((a = 1) AND (b = 2))
6604+
(10 rows)
6605+
6606+
select * from t t1, t t2 where exists
6607+
(select 1 from t t3 where t1.a = t3.a and t2.b = t3.b and t3.a = 1 and t3.b = 2);
6608+
a | b | a | b
6609+
---+---+---+---
6610+
1 | 2 | 1 | 2
6611+
(1 row)
6612+
6613+
rollback;
6614+
-- check handling of semijoin unique-ification for child relations if all RHS
6615+
-- columns are equated to constants.
6616+
begin;
6617+
create temp table p (a int, b int) partition by range (a);
6618+
create temp table p1 partition of p for values from (0) to (10);
6619+
create temp table p2 partition of p for values from (10) to (20);
6620+
insert into p values (1, 2);
6621+
insert into p values (10, 20);
6622+
set enable_partitionwise_join to on;
6623+
explain (costs off)
6624+
select * from p t1 where exists
6625+
(select 1 from p t2 where t1.a = t2.a and t1.a = 1);
6626+
QUERY PLAN
6627+
-------------------------------
6628+
Nested Loop Semi Join
6629+
-> Seq Scan on p1 t1
6630+
Filter: (a = 1)
6631+
-> Materialize
6632+
-> Seq Scan on p1 t2
6633+
Filter: (a = 1)
6634+
(6 rows)
6635+
6636+
select * from p t1 where exists
6637+
(select 1 from p t2 where t1.a = t2.a and t1.a = 1);
6638+
a | b
6639+
---+---
6640+
1 | 2
6641+
(1 row)
6642+
65836643
rollback;
65846644
-- test cases where we can remove a join, but not a PHV computed at it
65856645
begin;

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

Lines changed: 37 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2453,6 +2453,43 @@ where exists (select 1 from t t4
24532453

24542454
rollback;
24552455

2456+
-- check handling of semijoins if all RHS columns are equated to constants: we
2457+
-- should suppress unique-ification in this case.
2458+
begin;
2459+
2460+
create temp table t (aint, bint);
2461+
insert into tvalues (1,2);
2462+
2463+
explain (costs off)
2464+
select*from t t1, t t2where exists
2465+
(select1from t t3wheret1.a=t3.aandt2.b=t3.bandt3.a=1andt3.b=2);
2466+
2467+
select*from t t1, t t2where exists
2468+
(select1from t t3wheret1.a=t3.aandt2.b=t3.bandt3.a=1andt3.b=2);
2469+
2470+
rollback;
2471+
2472+
-- check handling of semijoin unique-ification for child relations if all RHS
2473+
-- columns are equated to constants.
2474+
begin;
2475+
2476+
create temp table p (aint, bint) partition by range (a);
2477+
create temp table p1 partition of p forvaluesfrom (0) to (10);
2478+
create temp table p2 partition of p forvaluesfrom (10) to (20);
2479+
insert into pvalues (1,2);
2480+
insert into pvalues (10,20);
2481+
2482+
set enable_partitionwise_join toon;
2483+
2484+
explain (costs off)
2485+
select*from p t1where exists
2486+
(select1from p t2wheret1.a=t2.aandt1.a=1);
2487+
2488+
select*from p t1where exists
2489+
(select1from p t2wheret1.a=t2.aandt1.a=1);
2490+
2491+
rollback;
2492+
24562493
-- test cases where we can remove a join, but not a PHV computed at it
24572494
begin;
24582495

0 commit comments

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