From ecf1a03257e804715fb6e028e878c97f140b7518 Mon Sep 17 00:00:00 2001
From: Kyotaro Horiguchi <horikyota.ntt@gmail.com>
Date: Fri, 14 Feb 2020 13:44:27 +0900
Subject: [PATCH] Fix behavior of nested planning

Issue: https://github.com/ossc-db/pg_hint_plan/issues/39

When planning is nested by function call, the hints in the function
should affect the corresponding query. Maybe 9599067 introduced
that. Fix the nested planning behavior.
---
 expected/ut-A.out | 19 ++++++++++---------
 sql/ut-A.sql      | 19 ++++++++++---------
 2 files changed, 20 insertions(+), 18 deletions(-)

diff --git a/expected/ut-A.out b/expected/ut-A.out
index 268a268..86134a9 100644
--- a/expected/ut-A.out
+++ b/expected/ut-A.out
@@ -4429,6 +4429,11 @@ CREATE OR REPLACE FUNCTION recall_planner() RETURNS int AS $$
 	 ORDER BY t_1.c1 LIMIT 1;
 $$ LANGUAGE SQL IMMUTABLE;
 --No.13-4-1
+-- recall_planner() is reduced to constant while planning using the
+-- hint defined in the function. Then the outer query is planned based
+-- on the following hint. pg_hint_plan shows the log for the function
+-- but the resulting explain output doesn't contain the corresponding
+-- plan.
 /*+HashJoin(t_1 t_2)*/
 EXPLAIN (COSTS false)
  SELECT recall_planner() FROM s1.t1 t_1
@@ -4460,11 +4465,7 @@ error hint:
 (7 rows)
 
 --No.13-4-2
--- recall_planner() is reduced to constant while planning using the
--- hint defined in the function. Then the outer query is planned based
--- on the following hint. pg_hint_plan shows the log for the function
--- but the resulting explain output doesn't contain the corresponding
--- plan.
+--See description for No.13-4-1
 /*+HashJoin(st_1 st_2)*/
 EXPLAIN (COSTS false)
  SELECT recall_planner() FROM s1.t1 st_1
@@ -4496,7 +4497,7 @@ error hint:
 (7 rows)
 
 --No.13-4-3
---See description for No.13-4-2
+--See description for No.13-4-1
 /*+HashJoin(t_1 t_2)*/
 EXPLAIN (COSTS false)
  SELECT recall_planner() FROM s1.t1 st_1
@@ -4527,7 +4528,7 @@ error hint:
 (6 rows)
 
 --No.13-4-4
---See description for No.13-4-2
+--See description for No.13-4-1
 /*+HashJoin(st_1 st_2)*/
 EXPLAIN (COSTS false)
  SELECT recall_planner() FROM s1.t1 t_1
@@ -4558,7 +4559,7 @@ error hint:
 (6 rows)
 
 --No.13-4-5
--- See description for No.13-4-2. No joins in ths plan, so
+-- See description for No.13-4-1. No joins in ths plan, so
 -- pg_hint_plan doesn't complain on the wrongly written error hint.
 /*+HashJoin(t_1 t_1)*/
 EXPLAIN (COSTS false)
@@ -4644,7 +4645,7 @@ HashJoin(t_1 t_1)
 DROP FUNCTION recall_planner_one_t(int);
 ERROR:  function recall_planner_one_t(integer) does not exist
 --No.13-4-7
--- See description for No.13-4-2. Complains on the wrongly wrtten hit.
+-- See description for No.13-4-1. Complains on the wrongly written hint.
 /*+HashJoin(t_1 t_1)*/
 EXPLAIN (COSTS false)
  SELECT recall_planner() FROM s1.t1 t_1
diff --git a/sql/ut-A.sql b/sql/ut-A.sql
index 69f3167..2b601d7 100644
--- a/sql/ut-A.sql
+++ b/sql/ut-A.sql
@@ -1152,6 +1152,11 @@ CREATE OR REPLACE FUNCTION recall_planner() RETURNS int AS $$
 $$ LANGUAGE SQL IMMUTABLE;
 
 --No.13-4-1
+-- recall_planner() is reduced to constant while planning using the
+-- hint defined in the function. Then the outer query is planned based
+-- on the following hint. pg_hint_plan shows the log for the function
+-- but the resulting explain output doesn't contain the corresponding
+-- plan.
 /*+HashJoin(t_1 t_2)*/
 EXPLAIN (COSTS false)
  SELECT recall_planner() FROM s1.t1 t_1
@@ -1159,11 +1164,7 @@ EXPLAIN (COSTS false)
   ORDER BY t_1.c1;
 
 --No.13-4-2
--- recall_planner() is reduced to constant while planning using the
--- hint defined in the function. Then the outer query is planned based
--- on the following hint. pg_hint_plan shows the log for the function
--- but the resulting explain output doesn't contain the corresponding
--- plan.
+--See description for No.13-4-1
 /*+HashJoin(st_1 st_2)*/
 EXPLAIN (COSTS false)
  SELECT recall_planner() FROM s1.t1 st_1
@@ -1171,7 +1172,7 @@ EXPLAIN (COSTS false)
   ORDER BY st_1.c1;
 
 --No.13-4-3
---See description for No.13-4-2
+--See description for No.13-4-1
 /*+HashJoin(t_1 t_2)*/
 EXPLAIN (COSTS false)
  SELECT recall_planner() FROM s1.t1 st_1
@@ -1179,7 +1180,7 @@ EXPLAIN (COSTS false)
   ORDER BY st_1.c1;
 
 --No.13-4-4
---See description for No.13-4-2
+--See description for No.13-4-1
 /*+HashJoin(st_1 st_2)*/
 EXPLAIN (COSTS false)
  SELECT recall_planner() FROM s1.t1 t_1
@@ -1187,7 +1188,7 @@ EXPLAIN (COSTS false)
   ORDER BY t_1.c1;
 
 --No.13-4-5
--- See description for No.13-4-2. No joins in ths plan, so
+-- See description for No.13-4-1. No joins in ths plan, so
 -- pg_hint_plan doesn't complain on the wrongly written error hint.
 /*+HashJoin(t_1 t_1)*/
 EXPLAIN (COSTS false)
@@ -1214,7 +1215,7 @@ EXPLAIN (COSTS false)
 DROP FUNCTION recall_planner_one_t(int);
 
 --No.13-4-7
--- See description for No.13-4-2. Complains on the wrongly wrtten hit.
+-- See description for No.13-4-1. Complains on the wrongly written hint.
 /*+HashJoin(t_1 t_1)*/
 EXPLAIN (COSTS false)
  SELECT recall_planner() FROM s1.t1 t_1
-- 
2.11.0