11e22e486-additional-test.patc
text/plain
Filename: 11e22e486-additional-test.patc
Type: text/plain
Part: 0
diff --git a/src/test/regress/expected/partition_prune.out b/src/test/regress/expected/partition_prune.out index 693c348185..61457862a9 100644 --- a/src/test/regress/expected/partition_prune.out +++ b/src/test/regress/expected/partition_prune.out @@ -2478,6 +2478,8 @@ deallocate ab_q3; deallocate ab_q4; deallocate ab_q5; -- UPDATE on a partition subtree has been seen to have problems. +set enable_hashjoin to off; +set enable_mergejoin to off; insert into ab values (1,2); explain (analyze, costs off, summary off, timing off) update ab_a1 set b = 3 from ab where ab.a = 1 and ab.a = ab_a1.a; @@ -2556,6 +2558,69 @@ table ab; 1 | 3 (1 row) +truncate ab; +insert into ab values (1, 1), (1, 2), (1, 3); +explain (analyze, costs off, summary off, timing off) +update ab_a1 set b = 3 from (select * from (select * from ab_a2 union all select 1, 2) s where s.b = (select 2)) ss where ss.a = ab_a1.a; + QUERY PLAN +--------------------------------------------------------------------------------- + Update on ab_a1 (actual rows=0 loops=1) + Update on ab_a1_b1 + Update on ab_a1_b2 + Update on ab_a1_b3 + InitPlan 1 (returns $0) + -> Result (actual rows=1 loops=1) + -> Nested Loop (actual rows=1 loops=1) + -> Append (actual rows=1 loops=1) + -> Seq Scan on ab_a2_b1 (never executed) + Filter: (b = $0) + -> Seq Scan on ab_a2_b2 (actual rows=0 loops=1) + Filter: (b = $0) + -> Seq Scan on ab_a2_b3 (never executed) + Filter: (b = $0) + -> Subquery Scan on "*SELECT* 2" (actual rows=1 loops=1) + -> Result (actual rows=1 loops=1) + One-Time Filter: (2 = $0) + -> Index Scan using ab_a1_b1_a_idx on ab_a1_b1 (actual rows=1 loops=1) + Index Cond: (a = ab_a2_b1.a) + -> Nested Loop (actual rows=1 loops=1) + -> Append (actual rows=1 loops=1) + -> Seq Scan on ab_a2_b1 (never executed) + Filter: (b = $0) + -> Seq Scan on ab_a2_b2 (actual rows=0 loops=1) + Filter: (b = $0) + -> Seq Scan on ab_a2_b3 (never executed) + Filter: (b = $0) + -> Subquery Scan on "*SELECT* 2_1" (actual rows=1 loops=1) + -> Result (actual rows=1 loops=1) + One-Time Filter: (2 = $0) + -> Index Scan using ab_a1_b2_a_idx on ab_a1_b2 (actual rows=1 loops=1) + Index Cond: (a = ab_a2_b1.a) + -> Nested Loop (actual rows=1 loops=1) + -> Append (actual rows=1 loops=1) + -> Seq Scan on ab_a2_b1 (never executed) + Filter: (b = $0) + -> Seq Scan on ab_a2_b2 (actual rows=0 loops=1) + Filter: (b = $0) + -> Seq Scan on ab_a2_b3 (never executed) + Filter: (b = $0) + -> Subquery Scan on "*SELECT* 2_2" (actual rows=1 loops=1) + -> Result (actual rows=1 loops=1) + One-Time Filter: (2 = $0) + -> Index Scan using ab_a1_b3_a_idx on ab_a1_b3 (actual rows=1 loops=1) + Index Cond: (a = ab_a2_b1.a) +(45 rows) + +table ab; + a | b +---+--- + 1 | 3 + 1 | 3 + 1 | 3 +(3 rows) + +reset enable_hashjoin; +reset enable_mergejoin; drop table ab, lprt_a; -- Join create table tbl1(col1 int); diff --git a/src/test/regress/sql/partition_prune.sql b/src/test/regress/sql/partition_prune.sql index 935c509b29..e78220a3e5 100644 --- a/src/test/regress/sql/partition_prune.sql +++ b/src/test/regress/sql/partition_prune.sql @@ -555,10 +555,19 @@ deallocate ab_q4; deallocate ab_q5; -- UPDATE on a partition subtree has been seen to have problems. +set enable_hashjoin to off; +set enable_mergejoin to off; insert into ab values (1,2); explain (analyze, costs off, summary off, timing off) update ab_a1 set b = 3 from ab where ab.a = 1 and ab.a = ab_a1.a; table ab; +truncate ab; +insert into ab values (1, 1), (1, 2), (1, 3); +explain (analyze, costs off, summary off, timing off) +update ab_a1 set b = 3 from (select * from (select * from ab_a2 union all select 1, 2) s where s.b = (select 2)) ss where ss.a = ab_a1.a; +table ab; +reset enable_hashjoin; +reset enable_mergejoin; drop table ab, lprt_a;