v1-0001-Stabilize-a-test-case-in-subselect.patch

From 8cfd48d66440048ceeb99a9c56a809787c3e4074 Mon Sep 17 00:00:00 2001
From: Richard Guo <guofenglinux@gmail.com>
Date: Fri, 29 Mar 2024 14:40:06 +0800
Subject: [PATCH v1] Stabilize a test case in subselect

---
 src/test/regress/expected/subselect.out | 24 +++++++++++-------------
 src/test/regress/sql/subselect.sql      |  4 ++--
 2 files changed, 13 insertions(+), 15 deletions(-)

diff --git a/src/test/regress/expected/subselect.out b/src/test/regress/expected/subselect.out
index 29b11f11aa..8cd9853fba 100644
--- a/src/test/regress/expected/subselect.out
+++ b/src/test/regress/expected/subselect.out
@@ -2063,21 +2063,19 @@ ON B.hundred in (SELECT c.hundred FROM tenk2 C WHERE c.odd = b.odd);
 -- we can pull up the sublink into the inner JoinExpr.
 explain (costs off)
 SELECT * FROM tenk1 A INNER JOIN tenk2 B
-ON A.hundred in (SELECT c.hundred FROM tenk2 C WHERE c.odd = b.odd);
-                   QUERY PLAN                    
--------------------------------------------------
- Hash Join
-   Hash Cond: (c.odd = b.odd)
-   ->  Hash Join
-         Hash Cond: (a.hundred = c.hundred)
+ON A.hundred in (SELECT c.hundred FROM tenk2 C WHERE c.odd > b.odd);
+              QUERY PLAN               
+---------------------------------------
+ Hash Semi Join
+   Hash Cond: (a.hundred = c.hundred)
+   Join Filter: (c.odd > b.odd)
+   ->  Nested Loop
          ->  Seq Scan on tenk1 a
-         ->  Hash
-               ->  HashAggregate
-                     Group Key: c.odd, c.hundred
-                     ->  Seq Scan on tenk2 c
+         ->  Materialize
+               ->  Seq Scan on tenk2 b
    ->  Hash
-         ->  Seq Scan on tenk2 b
-(11 rows)
+         ->  Seq Scan on tenk2 c
+(9 rows)
 
 -- we can pull up the aggregate sublink into RHS of a left join.
 explain (costs off)
diff --git a/src/test/regress/sql/subselect.sql b/src/test/regress/sql/subselect.sql
index 7c42ebc36f..2340512dd1 100644
--- a/src/test/regress/sql/subselect.sql
+++ b/src/test/regress/sql/subselect.sql
@@ -1013,9 +1013,9 @@ ON B.hundred in (SELECT c.hundred FROM tenk2 C WHERE c.odd = b.odd);
 -- we can pull up the sublink into the inner JoinExpr.
 explain (costs off)
 SELECT * FROM tenk1 A INNER JOIN tenk2 B
-ON A.hundred in (SELECT c.hundred FROM tenk2 C WHERE c.odd = b.odd);
+ON A.hundred in (SELECT c.hundred FROM tenk2 C WHERE c.odd > b.odd);
 
 -- we can pull up the aggregate sublink into RHS of a left join.
 explain (costs off)
 SELECT * FROM tenk1 A LEFT JOIN tenk2 B
-ON B.hundred in (SELECT min(c.hundred) FROM tenk2 C WHERE c.odd = b.odd);
\ No newline at end of file
+ON B.hundred in (SELECT min(c.hundred) FROM tenk2 C WHERE c.odd = b.odd);
-- 
2.31.0