summaryrefslogtreecommitdiff
path: root/src/test
diff options
context:
space:
mode:
Diffstat (limited to 'src/test')
-rw-r--r--src/test/regress/expected/insert_conflict.out6
-rw-r--r--src/test/regress/expected/subselect.out47
-rw-r--r--src/test/regress/expected/updatable_views.out48
-rw-r--r--src/test/regress/sql/subselect.sql17
4 files changed, 82 insertions, 36 deletions
diff --git a/src/test/regress/expected/insert_conflict.out b/src/test/regress/expected/insert_conflict.out
index 1338b2b23e1..ff157ceb1c1 100644
--- a/src/test/regress/expected/insert_conflict.out
+++ b/src/test/regress/expected/insert_conflict.out
@@ -50,14 +50,12 @@ explain (costs off) insert into insertconflicttest values(0, 'Crowberry') on con
Insert on insertconflicttest
Conflict Resolution: UPDATE
Conflict Arbiter Indexes: op_index_key, collation_index_key, both_index_key
- Conflict Filter: (alternatives: SubPlan 1 or hashed SubPlan 2)
+ Conflict Filter: (SubPlan 1)
-> Result
SubPlan 1
-> Index Only Scan using both_index_expr_key on insertconflicttest ii
Index Cond: (key = excluded.key)
- SubPlan 2
- -> Seq Scan on insertconflicttest ii_1
-(10 rows)
+(8 rows)
-- Neither collation nor operator class specifications are required --
-- supplying them merely *limits* matches to indexes with matching opclasses
diff --git a/src/test/regress/expected/subselect.out b/src/test/regress/expected/subselect.out
index b81923f2e74..9d56cdacf37 100644
--- a/src/test/regress/expected/subselect.out
+++ b/src/test/regress/expected/subselect.out
@@ -875,6 +875,53 @@ select * from int8_tbl where q1 in (select c1 from inner_text);
rollback; -- to get rid of the bogus operator
--
+-- Test resolution of hashed vs non-hashed implementation of EXISTS subplan
+--
+explain (costs off)
+select count(*) from tenk1 t
+where (exists(select 1 from tenk1 k where k.unique1 = t.unique2) or ten < 0);
+ QUERY PLAN
+--------------------------------------------------------------
+ Aggregate
+ -> Seq Scan on tenk1 t
+ Filter: ((hashed SubPlan 2) OR (ten < 0))
+ SubPlan 2
+ -> Index Only Scan using tenk1_unique1 on tenk1 k
+(5 rows)
+
+select count(*) from tenk1 t
+where (exists(select 1 from tenk1 k where k.unique1 = t.unique2) or ten < 0);
+ count
+-------
+ 10000
+(1 row)
+
+explain (costs off)
+select count(*) from tenk1 t
+where (exists(select 1 from tenk1 k where k.unique1 = t.unique2) or ten < 0)
+ and thousand = 1;
+ QUERY PLAN
+--------------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on tenk1 t
+ Recheck Cond: (thousand = 1)
+ Filter: ((SubPlan 1) OR (ten < 0))
+ -> Bitmap Index Scan on tenk1_thous_tenthous
+ Index Cond: (thousand = 1)
+ SubPlan 1
+ -> Index Only Scan using tenk1_unique1 on tenk1 k
+ Index Cond: (unique1 = t.unique2)
+(9 rows)
+
+select count(*) from tenk1 t
+where (exists(select 1 from tenk1 k where k.unique1 = t.unique2) or ten < 0)
+ and thousand = 1;
+ count
+-------
+ 10
+(1 row)
+
+--
-- Test case for planner bug with nested EXISTS handling
--
select a.thousand from tenk1 a, tenk1 b
diff --git a/src/test/regress/expected/updatable_views.out b/src/test/regress/expected/updatable_views.out
index 5de53f2782a..caed1c19ec7 100644
--- a/src/test/regress/expected/updatable_views.out
+++ b/src/test/regress/expected/updatable_views.out
@@ -1869,9 +1869,7 @@ EXPLAIN (costs off) INSERT INTO rw_view1 VALUES (5);
SubPlan 1
-> Index Only Scan using ref_tbl_pkey on ref_tbl r
Index Cond: (a = b.a)
- SubPlan 2
- -> Seq Scan on ref_tbl r_1
-(7 rows)
+(5 rows)
EXPLAIN (costs off) UPDATE rw_view1 SET a = a + 5;
QUERY PLAN
@@ -1885,9 +1883,7 @@ EXPLAIN (costs off) UPDATE rw_view1 SET a = a + 5;
SubPlan 1
-> Index Only Scan using ref_tbl_pkey on ref_tbl r_1
Index Cond: (a = b.a)
- SubPlan 2
- -> Seq Scan on ref_tbl r_2
-(11 rows)
+(9 rows)
DROP TABLE base_tbl, ref_tbl CASCADE;
NOTICE: drop cascades to view rw_view1
@@ -2301,8 +2297,8 @@ SELECT * FROM v1 WHERE a=8;
EXPLAIN (VERBOSE, COSTS OFF)
UPDATE v1 SET a=100 WHERE snoop(a) AND leakproof(a) AND a < 7 AND a != 6;
- QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------------------------------
Update on public.t1
Update on public.t1
Update on public.t11 t1_1
@@ -2311,32 +2307,26 @@ UPDATE v1 SET a=100 WHERE snoop(a) AND leakproof(a) AND a < 7 AND a != 6;
-> Index Scan using t1_a_idx on public.t1
Output: 100, t1.b, t1.c, t1.ctid
Index Cond: ((t1.a > 5) AND (t1.a < 7))
- Filter: ((t1.a <> 6) AND (alternatives: SubPlan 1 or hashed SubPlan 2) AND snoop(t1.a) AND leakproof(t1.a))
+ Filter: ((t1.a <> 6) AND (SubPlan 1) AND snoop(t1.a) AND leakproof(t1.a))
SubPlan 1
-> Append
-> Seq Scan on public.t12 t12_1
Filter: (t12_1.a = t1.a)
-> Seq Scan on public.t111 t12_2
Filter: (t12_2.a = t1.a)
- SubPlan 2
- -> Append
- -> Seq Scan on public.t12 t12_4
- Output: t12_4.a
- -> Seq Scan on public.t111 t12_5
- Output: t12_5.a
-> Index Scan using t11_a_idx on public.t11 t1_1
Output: 100, t1_1.b, t1_1.c, t1_1.d, t1_1.ctid
Index Cond: ((t1_1.a > 5) AND (t1_1.a < 7))
- Filter: ((t1_1.a <> 6) AND (alternatives: SubPlan 1 or hashed SubPlan 2) AND snoop(t1_1.a) AND leakproof(t1_1.a))
+ Filter: ((t1_1.a <> 6) AND (SubPlan 1) AND snoop(t1_1.a) AND leakproof(t1_1.a))
-> Index Scan using t12_a_idx on public.t12 t1_2
Output: 100, t1_2.b, t1_2.c, t1_2.e, t1_2.ctid
Index Cond: ((t1_2.a > 5) AND (t1_2.a < 7))
- Filter: ((t1_2.a <> 6) AND (alternatives: SubPlan 1 or hashed SubPlan 2) AND snoop(t1_2.a) AND leakproof(t1_2.a))
+ Filter: ((t1_2.a <> 6) AND (SubPlan 1) AND snoop(t1_2.a) AND leakproof(t1_2.a))
-> Index Scan using t111_a_idx on public.t111 t1_3
Output: 100, t1_3.b, t1_3.c, t1_3.d, t1_3.e, t1_3.ctid
Index Cond: ((t1_3.a > 5) AND (t1_3.a < 7))
- Filter: ((t1_3.a <> 6) AND (alternatives: SubPlan 1 or hashed SubPlan 2) AND snoop(t1_3.a) AND leakproof(t1_3.a))
-(33 rows)
+ Filter: ((t1_3.a <> 6) AND (SubPlan 1) AND snoop(t1_3.a) AND leakproof(t1_3.a))
+(27 rows)
UPDATE v1 SET a=100 WHERE snoop(a) AND leakproof(a) AND a < 7 AND a != 6;
SELECT * FROM v1 WHERE a=100; -- Nothing should have been changed to 100
@@ -2351,8 +2341,8 @@ SELECT * FROM t1 WHERE a=100; -- Nothing should have been changed to 100
EXPLAIN (VERBOSE, COSTS OFF)
UPDATE v1 SET a=a+1 WHERE snoop(a) AND leakproof(a) AND a = 8;
- QUERY PLAN
----------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------------------------
Update on public.t1
Update on public.t1
Update on public.t11 t1_1
@@ -2361,32 +2351,26 @@ UPDATE v1 SET a=a+1 WHERE snoop(a) AND leakproof(a) AND a = 8;
-> Index Scan using t1_a_idx on public.t1
Output: (t1.a + 1), t1.b, t1.c, t1.ctid
Index Cond: ((t1.a > 5) AND (t1.a = 8))
- Filter: ((alternatives: SubPlan 1 or hashed SubPlan 2) AND snoop(t1.a) AND leakproof(t1.a))
+ Filter: ((SubPlan 1) AND snoop(t1.a) AND leakproof(t1.a))
SubPlan 1
-> Append
-> Seq Scan on public.t12 t12_1
Filter: (t12_1.a = t1.a)
-> Seq Scan on public.t111 t12_2
Filter: (t12_2.a = t1.a)
- SubPlan 2
- -> Append
- -> Seq Scan on public.t12 t12_4
- Output: t12_4.a
- -> Seq Scan on public.t111 t12_5
- Output: t12_5.a
-> Index Scan using t11_a_idx on public.t11 t1_1
Output: (t1_1.a + 1), t1_1.b, t1_1.c, t1_1.d, t1_1.ctid
Index Cond: ((t1_1.a > 5) AND (t1_1.a = 8))
- Filter: ((alternatives: SubPlan 1 or hashed SubPlan 2) AND snoop(t1_1.a) AND leakproof(t1_1.a))
+ Filter: ((SubPlan 1) AND snoop(t1_1.a) AND leakproof(t1_1.a))
-> Index Scan using t12_a_idx on public.t12 t1_2
Output: (t1_2.a + 1), t1_2.b, t1_2.c, t1_2.e, t1_2.ctid
Index Cond: ((t1_2.a > 5) AND (t1_2.a = 8))
- Filter: ((alternatives: SubPlan 1 or hashed SubPlan 2) AND snoop(t1_2.a) AND leakproof(t1_2.a))
+ Filter: ((SubPlan 1) AND snoop(t1_2.a) AND leakproof(t1_2.a))
-> Index Scan using t111_a_idx on public.t111 t1_3
Output: (t1_3.a + 1), t1_3.b, t1_3.c, t1_3.d, t1_3.e, t1_3.ctid
Index Cond: ((t1_3.a > 5) AND (t1_3.a = 8))
- Filter: ((alternatives: SubPlan 1 or hashed SubPlan 2) AND snoop(t1_3.a) AND leakproof(t1_3.a))
-(33 rows)
+ Filter: ((SubPlan 1) AND snoop(t1_3.a) AND leakproof(t1_3.a))
+(27 rows)
UPDATE v1 SET a=a+1 WHERE snoop(a) AND leakproof(a) AND a = 8;
NOTICE: snooped value: 8
diff --git a/src/test/regress/sql/subselect.sql b/src/test/regress/sql/subselect.sql
index cce8ebdb3d9..a25cb6fc5c5 100644
--- a/src/test/regress/sql/subselect.sql
+++ b/src/test/regress/sql/subselect.sql
@@ -510,6 +510,23 @@ select * from int8_tbl where q1 in (select c1 from inner_text);
rollback; -- to get rid of the bogus operator
--
+-- Test resolution of hashed vs non-hashed implementation of EXISTS subplan
+--
+explain (costs off)
+select count(*) from tenk1 t
+where (exists(select 1 from tenk1 k where k.unique1 = t.unique2) or ten < 0);
+select count(*) from tenk1 t
+where (exists(select 1 from tenk1 k where k.unique1 = t.unique2) or ten < 0);
+
+explain (costs off)
+select count(*) from tenk1 t
+where (exists(select 1 from tenk1 k where k.unique1 = t.unique2) or ten < 0)
+ and thousand = 1;
+select count(*) from tenk1 t
+where (exists(select 1 from tenk1 k where k.unique1 = t.unique2) or ten < 0)
+ and thousand = 1;
+
+--
-- Test case for planner bug with nested EXISTS handling
--
select a.thousand from tenk1 a, tenk1 b
generated by cgit v1.2.3 (git 2.46.0) at 2025-09-30 12:20:24 +0000