join-missing-partition.diff.txt

commit 0a3adf1387662f76a921510731097ebcf3964547
Author: Jacob Champion <jacob.champion@enterprisedb.com>
Date:   Tue Jan 13 10:27:10 2026

    review: try JOIN_ORDER with nonexistent partition
    
    Fails with
        ERROR:  cannot determine RTI for advice target

diff --git a/contrib/pg_plan_advice/expected/join_order.out b/contrib/pg_plan_advice/expected/join_order.out
index f3dd7810484..abec728ddc9 100644
--- a/contrib/pg_plan_advice/expected/join_order.out
+++ b/contrib/pg_plan_advice/expected/join_order.out
@@ -165,6 +165,69 @@ SELECT * FROM jo_fact f
    NO_GATHER(f d1 d2)
 (21 rows)
 
+COMMIT;
+BEGIN;
+SET LOCAL pg_plan_advice.advice = 'join_order(f/d1 d1 d2)';
+EXPLAIN (COSTS OFF, PLAN_ADVICE)
+SELECT * FROM jo_fact f
+	LEFT JOIN jo_dim1 d1 ON f.dim1_id = d1.id
+	LEFT JOIN jo_dim2 d2 ON f.dim2_id = d2.id
+	WHERE val1 = 1 AND val2 = 1;
+                         QUERY PLAN                          
+-------------------------------------------------------------
+ Nested Loop
+   Disabled: true
+   ->  Nested Loop
+         Disabled: true
+         ->  Seq Scan on jo_fact f
+         ->  Index Scan using jo_dim1_pkey on jo_dim1 d1
+               Index Cond: (id = f.dim1_id)
+               Filter: (val1 = 1)
+   ->  Index Scan using jo_dim2_pkey on jo_dim2 d2
+         Index Cond: (id = f.dim2_id)
+         Filter: (val2 = 1)
+ Supplied Plan Advice:
+   JOIN_ORDER(f/d1 d1 d2) /* partially matched */
+ Generated Plan Advice:
+   JOIN_ORDER(f d1 d2)
+   NESTED_LOOP_PLAIN(d1 d2)
+   SEQ_SCAN(f)
+   INDEX_SCAN(d1 public.jo_dim1_pkey d2 public.jo_dim2_pkey)
+   NO_GATHER(f d1 d2)
+(19 rows)
+
+SET LOCAL pg_plan_advice.advice = 'join_order(f/d1 (d1 d2))';
+EXPLAIN (COSTS OFF, PLAN_ADVICE)
+SELECT * FROM jo_fact f
+	LEFT JOIN jo_dim1 d1 ON f.dim1_id = d1.id
+	LEFT JOIN jo_dim2 d2 ON f.dim2_id = d2.id
+	WHERE val1 = 1 AND val2 = 1;
+                         QUERY PLAN                          
+-------------------------------------------------------------
+-- XXX: this is just a guess
+ Merge Join
+   Merge Cond: ((d2.id = f.dim2_id) AND (d1.id = f.dim1_id))
+   ->  Sort
+         Sort Key: d2.id, d1.id
+         ->  Nested Loop
+               ->  Seq Scan on jo_dim1 d1
+                     Filter: (val1 = 1)
+               ->  Materialize
+                     ->  Seq Scan on jo_dim2 d2
+                           Filter: (val2 = 1)
+   ->  Sort
+         Sort Key: f.dim2_id, f.dim1_id
+         ->  Seq Scan on jo_fact f
+ Supplied Plan Advice:
+   JOIN_ORDER(f/d1 (d1 d2)) /* partially matched */
+ Generated Plan Advice:
+   JOIN_ORDER(d1 d2 f)
+   MERGE_JOIN_PLAIN(f)
+   NESTED_LOOP_MATERIALIZE(d2)
+   SEQ_SCAN(d1 d2 f)
+   NO_GATHER(f d1 d2)
+(21 rows)
+
 COMMIT;
 -- The unusual formulation of this query is intended to prevent the query
 -- planner from reducing the FULL JOIN to some other join type, so that we
diff --git a/contrib/pg_plan_advice/sql/join_order.sql b/contrib/pg_plan_advice/sql/join_order.sql
index 5aa2fc62d34..48b8dfe6f74 100644
--- a/contrib/pg_plan_advice/sql/join_order.sql
+++ b/contrib/pg_plan_advice/sql/join_order.sql
@@ -60,6 +60,21 @@ SELECT * FROM jo_fact f
 	WHERE val1 = 1 AND val2 = 1;
 COMMIT;
 
+BEGIN;
+SET LOCAL pg_plan_advice.advice = 'join_order(f/d1 d1 d2)';
+EXPLAIN (COSTS OFF, PLAN_ADVICE)
+SELECT * FROM jo_fact f
+	LEFT JOIN jo_dim1 d1 ON f.dim1_id = d1.id
+	LEFT JOIN jo_dim2 d2 ON f.dim2_id = d2.id
+	WHERE val1 = 1 AND val2 = 1;
+SET LOCAL pg_plan_advice.advice = 'join_order(f/d1 (d1 d2))';
+EXPLAIN (COSTS OFF, PLAN_ADVICE)
+SELECT * FROM jo_fact f
+	LEFT JOIN jo_dim1 d1 ON f.dim1_id = d1.id
+	LEFT JOIN jo_dim2 d2 ON f.dim2_id = d2.id
+	WHERE val1 = 1 AND val2 = 1;
+COMMIT;
+
 -- The unusual formulation of this query is intended to prevent the query
 -- planner from reducing the FULL JOIN to some other join type, so that we
 -- can test what happens with a join type that cannot be reordered.