Rowsヒント句のRULEまたはVIEWの試験(R-2-3-*)を追加した。

diff --git a/expected/R_2-3-1.out b/expected/R_2-3-1.out
new file mode 100644 (file)
index 0000000..151ef06
--- /dev/null
+++ b/expected/R_2-3-1.out
@@ -0,0 +1,92 @@
+                                          QUERY PLAN                                           
+-----------------------------------------------------------------------------------------------
+ Aggregate  (cost=xxx rows=1 width=4)
+   ->  Nested Loop  (cost=xxx rows=1 width=4)
+         Join Filter: (t1.c1 = t4.c1)
+         ->  Nested Loop  (cost=xxx rows=1 width=12)
+               Join Filter: (t1.c1 = t3.c1)
+               ->  Merge Join  (cost=xxx rows=1 width=8)
+                     Merge Cond: (t1.c1 = t2.c1)
+                     ->  Nested Loop  (cost=xxx rows=6 width=4)
+                           ->  Index Scan using t1_i1 on t1  (cost=xxx rows=1 width=4)
+                                 Filter: (ctid = '(1,1)'::tid)
+                           ->  Seq Scan on r1  (cost=xxx rows=6 width=0)
+                                 Filter: (c1 = 1)
+                     ->  Sort  (cost=xxx rows=1 width=4)
+                           Sort Key: t2.c1
+                           ->  Seq Scan on t2  (cost=xxx rows=1 width=4)
+                                 Filter: (ctid = '(1,1)'::tid)
+               ->  Tid Scan on t3  (cost=xxx rows=1 width=4)
+                     TID Cond: (ctid = '(1,1)'::tid)
+         ->  Tid Scan on t4  (cost=xxx rows=1 width=4)
+               TID Cond: (ctid = '(1,1)'::tid)
+(20 rows)
+
+                                   QUERY PLAN                                    
+---------------------------------------------------------------------------------
+ Aggregate  (cost=xxx rows=1 width=4)
+   ->  Nested Loop  (cost=xxx rows=2 width=4)
+         Join Filter: (t1.c1 = t4.c1)
+         ->  Tid Scan on t4  (cost=xxx rows=1 width=4)
+               TID Cond: (ctid = '(1,1)'::tid)
+         ->  Nested Loop  (cost=xxx rows=2 width=12)
+               Join Filter: (t1.c1 = t3.c1)
+               ->  Tid Scan on t3  (cost=xxx rows=1 width=4)
+                     TID Cond: (ctid = '(1,1)'::tid)
+               ->  Nested Loop  (cost=xxx rows=2 width=8)
+                     Join Filter: (t1.c1 = t2.c1)
+                     ->  Seq Scan on t2  (cost=xxx rows=1 width=4)
+                           Filter: (ctid = '(1,1)'::tid)
+                     ->  Nested Loop  (cost=xxx rows=2 width=4)
+                           ->  Tid Scan on t1  (cost=xxx rows=1 width=4)
+                                 TID Cond: (ctid = '(1,1)'::tid)
+                           ->  Seq Scan on r1  (cost=xxx rows=6 width=0)
+                                 Filter: (c1 = 1)
+(18 rows)
+
+                                             QUERY PLAN                                             
+----------------------------------------------------------------------------------------------------
+ Aggregate  (cost=xxx rows=1 width=4)
+   ->  Nested Loop  (cost=xxx rows=1 width=4)
+         Join Filter: (b1t1.c1 = b1t4.c1)
+         ->  Nested Loop  (cost=xxx rows=1 width=12)
+               Join Filter: (b1t1.c1 = b1t3.c1)
+               ->  Merge Join  (cost=xxx rows=1 width=8)
+                     Merge Cond: (b1t1.c1 = b1t2.c1)
+                     ->  Nested Loop  (cost=xxx rows=6 width=4)
+                           ->  Index Scan using t1_i1 on t1 b1t1  (cost=xxx rows=1 width=4)
+                                 Filter: (ctid = '(1,1)'::tid)
+                           ->  Seq Scan on r1_  (cost=xxx rows=6 width=0)
+                                 Filter: (c1 = 1)
+                     ->  Sort  (cost=xxx rows=1 width=4)
+                           Sort Key: b1t2.c1
+                           ->  Seq Scan on t2 b1t2  (cost=xxx rows=1 width=4)
+                                 Filter: (ctid = '(1,1)'::tid)
+               ->  Tid Scan on t3 b1t3  (cost=xxx rows=1 width=4)
+                     TID Cond: (ctid = '(1,1)'::tid)
+         ->  Tid Scan on t4 b1t4  (cost=xxx rows=1 width=4)
+               TID Cond: (ctid = '(1,1)'::tid)
+(20 rows)
+
+                                     QUERY PLAN                                      
+-------------------------------------------------------------------------------------
+ Aggregate  (cost=xxx rows=1 width=4)
+   ->  Nested Loop  (cost=xxx rows=2 width=4)
+         Join Filter: (b1t1.c1 = b1t4.c1)
+         ->  Tid Scan on t4 b1t4  (cost=xxx rows=1 width=4)
+               TID Cond: (ctid = '(1,1)'::tid)
+         ->  Nested Loop  (cost=xxx rows=2 width=12)
+               Join Filter: (b1t1.c1 = b1t3.c1)
+               ->  Tid Scan on t3 b1t3  (cost=xxx rows=1 width=4)
+                     TID Cond: (ctid = '(1,1)'::tid)
+               ->  Nested Loop  (cost=xxx rows=2 width=8)
+                     Join Filter: (b1t1.c1 = b1t2.c1)
+                     ->  Seq Scan on t2 b1t2  (cost=xxx rows=1 width=4)
+                           Filter: (ctid = '(1,1)'::tid)
+                     ->  Nested Loop  (cost=xxx rows=2 width=4)
+                           ->  Tid Scan on t1 b1t1  (cost=xxx rows=1 width=4)
+                                 TID Cond: (ctid = '(1,1)'::tid)
+                           ->  Seq Scan on r1_  (cost=xxx rows=6 width=0)
+                                 Filter: (c1 = 1)
+(18 rows)
+

diff --git a/expected/R_2-3-2.out b/expected/R_2-3-2.out
new file mode 100644 (file)
index 0000000..b6f2fbd
--- /dev/null
+++ b/expected/R_2-3-2.out
@@ -0,0 +1,172 @@
+                                          QUERY PLAN                                           
+-----------------------------------------------------------------------------------------------
+ Aggregate  (cost=xxx rows=1 width=4)
+   ->  Nested Loop  (cost=xxx rows=1 width=4)
+         Join Filter: (t1.c1 = t4.c1)
+         ->  Nested Loop  (cost=xxx rows=1 width=12)
+               Join Filter: (t1.c1 = t3.c1)
+               ->  Merge Join  (cost=xxx rows=1 width=8)
+                     Merge Cond: (t1.c1 = t2.c1)
+                     ->  Nested Loop  (cost=xxx rows=6 width=4)
+                           ->  Index Scan using t1_i1 on t1  (cost=xxx rows=1 width=4)
+                                 Filter: (ctid = '(1,1)'::tid)
+                           ->  Seq Scan on r2  (cost=xxx rows=6 width=0)
+                                 Filter: (c1 = 1)
+                     ->  Sort  (cost=xxx rows=1 width=4)
+                           Sort Key: t2.c1
+                           ->  Seq Scan on t2  (cost=xxx rows=1 width=4)
+                                 Filter: (ctid = '(1,1)'::tid)
+               ->  Tid Scan on t3  (cost=xxx rows=1 width=4)
+                     TID Cond: (ctid = '(1,1)'::tid)
+         ->  Tid Scan on t4  (cost=xxx rows=1 width=4)
+               TID Cond: (ctid = '(1,1)'::tid)
+ 
+ Aggregate  (cost=xxx rows=1 width=4)
+   ->  Nested Loop  (cost=xxx rows=1 width=4)
+         Join Filter: (t1.c1 = t4.c1)
+         ->  Nested Loop  (cost=xxx rows=1 width=12)
+               Join Filter: (t1.c1 = t3.c1)
+               ->  Merge Join  (cost=xxx rows=1 width=8)
+                     Merge Cond: (t1.c1 = t2.c1)
+                     ->  Nested Loop  (cost=xxx rows=6 width=4)
+                           ->  Index Scan using t1_i1 on t1  (cost=xxx rows=1 width=4)
+                                 Filter: (ctid = '(1,1)'::tid)
+                           ->  Seq Scan on r2  (cost=xxx rows=6 width=0)
+                                 Filter: (c1 = 1)
+                     ->  Sort  (cost=xxx rows=1 width=4)
+                           Sort Key: t2.c1
+                           ->  Seq Scan on t2  (cost=xxx rows=1 width=4)
+                                 Filter: (ctid = '(1,1)'::tid)
+               ->  Tid Scan on t3  (cost=xxx rows=1 width=4)
+                     TID Cond: (ctid = '(1,1)'::tid)
+         ->  Tid Scan on t4  (cost=xxx rows=1 width=4)
+               TID Cond: (ctid = '(1,1)'::tid)
+(41 rows)
+
+                                   QUERY PLAN                                    
+---------------------------------------------------------------------------------
+ Aggregate  (cost=xxx rows=1 width=4)
+   ->  Nested Loop  (cost=xxx rows=2 width=4)
+         Join Filter: (t1.c1 = t4.c1)
+         ->  Tid Scan on t4  (cost=xxx rows=1 width=4)
+               TID Cond: (ctid = '(1,1)'::tid)
+         ->  Nested Loop  (cost=xxx rows=2 width=12)
+               Join Filter: (t1.c1 = t3.c1)
+               ->  Tid Scan on t3  (cost=xxx rows=1 width=4)
+                     TID Cond: (ctid = '(1,1)'::tid)
+               ->  Nested Loop  (cost=xxx rows=2 width=8)
+                     Join Filter: (t1.c1 = t2.c1)
+                     ->  Seq Scan on t2  (cost=xxx rows=1 width=4)
+                           Filter: (ctid = '(1,1)'::tid)
+                     ->  Nested Loop  (cost=xxx rows=2 width=4)
+                           ->  Tid Scan on t1  (cost=xxx rows=1 width=4)
+                                 TID Cond: (ctid = '(1,1)'::tid)
+                           ->  Seq Scan on r2  (cost=xxx rows=6 width=0)
+                                 Filter: (c1 = 1)
+ 
+ Aggregate  (cost=xxx rows=1 width=4)
+   ->  Nested Loop  (cost=xxx rows=2 width=4)
+         Join Filter: (t1.c1 = t4.c1)
+         ->  Tid Scan on t4  (cost=xxx rows=1 width=4)
+               TID Cond: (ctid = '(1,1)'::tid)
+         ->  Nested Loop  (cost=xxx rows=2 width=12)
+               Join Filter: (t1.c1 = t3.c1)
+               ->  Tid Scan on t3  (cost=xxx rows=1 width=4)
+                     TID Cond: (ctid = '(1,1)'::tid)
+               ->  Nested Loop  (cost=xxx rows=2 width=8)
+                     Join Filter: (t1.c1 = t2.c1)
+                     ->  Seq Scan on t2  (cost=xxx rows=1 width=4)
+                           Filter: (ctid = '(1,1)'::tid)
+                     ->  Nested Loop  (cost=xxx rows=2 width=4)
+                           ->  Tid Scan on t1  (cost=xxx rows=1 width=4)
+                                 TID Cond: (ctid = '(1,1)'::tid)
+                           ->  Seq Scan on r2  (cost=xxx rows=6 width=0)
+                                 Filter: (c1 = 1)
+(37 rows)
+
+                                             QUERY PLAN                                             
+----------------------------------------------------------------------------------------------------
+ Aggregate  (cost=xxx rows=1 width=4)
+   ->  Nested Loop  (cost=xxx rows=1 width=4)
+         Join Filter: (b1t1.c1 = b1t4.c1)
+         ->  Nested Loop  (cost=xxx rows=1 width=12)
+               Join Filter: (b1t1.c1 = b1t3.c1)
+               ->  Merge Join  (cost=xxx rows=1 width=8)
+                     Merge Cond: (b1t1.c1 = b1t2.c1)
+                     ->  Nested Loop  (cost=xxx rows=6 width=4)
+                           ->  Index Scan using t1_i1 on t1 b1t1  (cost=xxx rows=1 width=4)
+                                 Filter: (ctid = '(1,1)'::tid)
+                           ->  Seq Scan on r2_  (cost=xxx rows=6 width=0)
+                                 Filter: (c1 = 1)
+                     ->  Sort  (cost=xxx rows=1 width=4)
+                           Sort Key: b1t2.c1
+                           ->  Seq Scan on t2 b1t2  (cost=xxx rows=1 width=4)
+                                 Filter: (ctid = '(1,1)'::tid)
+               ->  Tid Scan on t3 b1t3  (cost=xxx rows=1 width=4)
+                     TID Cond: (ctid = '(1,1)'::tid)
+         ->  Tid Scan on t4 b1t4  (cost=xxx rows=1 width=4)
+               TID Cond: (ctid = '(1,1)'::tid)
+ 
+ Aggregate  (cost=xxx rows=1 width=4)
+   ->  Nested Loop  (cost=xxx rows=1 width=4)
+         Join Filter: (b2t1.c1 = b2t4.c1)
+         ->  Nested Loop  (cost=xxx rows=1 width=12)
+               Join Filter: (b2t1.c1 = b2t3.c1)
+               ->  Merge Join  (cost=xxx rows=1 width=8)
+                     Merge Cond: (b2t1.c1 = b2t2.c1)
+                     ->  Nested Loop  (cost=xxx rows=6 width=4)
+                           ->  Index Scan using t1_i1 on t1 b2t1  (cost=xxx rows=1 width=4)
+                                 Filter: (ctid = '(1,1)'::tid)
+                           ->  Seq Scan on r2_  (cost=xxx rows=6 width=0)
+                                 Filter: (c1 = 1)
+                     ->  Sort  (cost=xxx rows=1 width=4)
+                           Sort Key: b2t2.c1
+                           ->  Seq Scan on t2 b2t2  (cost=xxx rows=1 width=4)
+                                 Filter: (ctid = '(1,1)'::tid)
+               ->  Tid Scan on t3 b2t3  (cost=xxx rows=1 width=4)
+                     TID Cond: (ctid = '(1,1)'::tid)
+         ->  Tid Scan on t4 b2t4  (cost=xxx rows=1 width=4)
+               TID Cond: (ctid = '(1,1)'::tid)
+(41 rows)
+
+                                     QUERY PLAN                                      
+-------------------------------------------------------------------------------------
+ Aggregate  (cost=xxx rows=1 width=4)
+   ->  Nested Loop  (cost=xxx rows=2 width=4)
+         Join Filter: (b1t1.c1 = b1t4.c1)
+         ->  Tid Scan on t4 b1t4  (cost=xxx rows=1 width=4)
+               TID Cond: (ctid = '(1,1)'::tid)
+         ->  Nested Loop  (cost=xxx rows=2 width=12)
+               Join Filter: (b1t1.c1 = b1t3.c1)
+               ->  Tid Scan on t3 b1t3  (cost=xxx rows=1 width=4)
+                     TID Cond: (ctid = '(1,1)'::tid)
+               ->  Nested Loop  (cost=xxx rows=2 width=8)
+                     Join Filter: (b1t1.c1 = b1t2.c1)
+                     ->  Seq Scan on t2 b1t2  (cost=xxx rows=1 width=4)
+                           Filter: (ctid = '(1,1)'::tid)
+                     ->  Nested Loop  (cost=xxx rows=2 width=4)
+                           ->  Tid Scan on t1 b1t1  (cost=xxx rows=1 width=4)
+                                 TID Cond: (ctid = '(1,1)'::tid)
+                           ->  Seq Scan on r2_  (cost=xxx rows=6 width=0)
+                                 Filter: (c1 = 1)
+ 
+ Aggregate  (cost=xxx rows=1 width=4)
+   ->  Nested Loop  (cost=xxx rows=2 width=4)
+         Join Filter: (b2t1.c1 = b2t4.c1)
+         ->  Tid Scan on t4 b2t4  (cost=xxx rows=1 width=4)
+               TID Cond: (ctid = '(1,1)'::tid)
+         ->  Nested Loop  (cost=xxx rows=2 width=12)
+               Join Filter: (b2t1.c1 = b2t3.c1)
+               ->  Tid Scan on t3 b2t3  (cost=xxx rows=1 width=4)
+                     TID Cond: (ctid = '(1,1)'::tid)
+               ->  Nested Loop  (cost=xxx rows=2 width=8)
+                     Join Filter: (b2t1.c1 = b2t2.c1)
+                     ->  Seq Scan on t2 b2t2  (cost=xxx rows=1 width=4)
+                           Filter: (ctid = '(1,1)'::tid)
+                     ->  Nested Loop  (cost=xxx rows=2 width=4)
+                           ->  Tid Scan on t1 b2t1  (cost=xxx rows=1 width=4)
+                                 TID Cond: (ctid = '(1,1)'::tid)
+                           ->  Seq Scan on r2_  (cost=xxx rows=6 width=0)
+                                 Filter: (c1 = 1)
+(37 rows)
+

diff --git a/expected/R_2-3-3.out b/expected/R_2-3-3.out
new file mode 100644 (file)
index 0000000..07dda1b
--- /dev/null
+++ b/expected/R_2-3-3.out
@@ -0,0 +1,252 @@
+                                          QUERY PLAN                                           
+-----------------------------------------------------------------------------------------------
+ Aggregate  (cost=xxx rows=1 width=4)
+   ->  Nested Loop  (cost=xxx rows=1 width=4)
+         Join Filter: (t1.c1 = t4.c1)
+         ->  Nested Loop  (cost=xxx rows=1 width=12)
+               Join Filter: (t1.c1 = t3.c1)
+               ->  Merge Join  (cost=xxx rows=1 width=8)
+                     Merge Cond: (t1.c1 = t2.c1)
+                     ->  Nested Loop  (cost=xxx rows=6 width=4)
+                           ->  Index Scan using t1_i1 on t1  (cost=xxx rows=1 width=4)
+                                 Filter: (ctid = '(1,1)'::tid)
+                           ->  Seq Scan on r3  (cost=xxx rows=6 width=0)
+                                 Filter: (c1 = 1)
+                     ->  Sort  (cost=xxx rows=1 width=4)
+                           Sort Key: t2.c1
+                           ->  Seq Scan on t2  (cost=xxx rows=1 width=4)
+                                 Filter: (ctid = '(1,1)'::tid)
+               ->  Tid Scan on t3  (cost=xxx rows=1 width=4)
+                     TID Cond: (ctid = '(1,1)'::tid)
+         ->  Tid Scan on t4  (cost=xxx rows=1 width=4)
+               TID Cond: (ctid = '(1,1)'::tid)
+ 
+ Aggregate  (cost=xxx rows=1 width=4)
+   ->  Nested Loop  (cost=xxx rows=1 width=4)
+         Join Filter: (t1.c1 = t4.c1)
+         ->  Nested Loop  (cost=xxx rows=1 width=12)
+               Join Filter: (t1.c1 = t3.c1)
+               ->  Merge Join  (cost=xxx rows=1 width=8)
+                     Merge Cond: (t1.c1 = t2.c1)
+                     ->  Nested Loop  (cost=xxx rows=6 width=4)
+                           ->  Index Scan using t1_i1 on t1  (cost=xxx rows=1 width=4)
+                                 Filter: (ctid = '(1,1)'::tid)
+                           ->  Seq Scan on r3  (cost=xxx rows=6 width=0)
+                                 Filter: (c1 = 1)
+                     ->  Sort  (cost=xxx rows=1 width=4)
+                           Sort Key: t2.c1
+                           ->  Seq Scan on t2  (cost=xxx rows=1 width=4)
+                                 Filter: (ctid = '(1,1)'::tid)
+               ->  Tid Scan on t3  (cost=xxx rows=1 width=4)
+                     TID Cond: (ctid = '(1,1)'::tid)
+         ->  Tid Scan on t4  (cost=xxx rows=1 width=4)
+               TID Cond: (ctid = '(1,1)'::tid)
+ 
+ Aggregate  (cost=xxx rows=1 width=4)
+   ->  Nested Loop  (cost=xxx rows=1 width=4)
+         Join Filter: (t1.c1 = t4.c1)
+         ->  Nested Loop  (cost=xxx rows=1 width=12)
+               Join Filter: (t1.c1 = t3.c1)
+               ->  Merge Join  (cost=xxx rows=1 width=8)
+                     Merge Cond: (t1.c1 = t2.c1)
+                     ->  Nested Loop  (cost=xxx rows=6 width=4)
+                           ->  Index Scan using t1_i1 on t1  (cost=xxx rows=1 width=4)
+                                 Filter: (ctid = '(1,1)'::tid)
+                           ->  Seq Scan on r3  (cost=xxx rows=6 width=0)
+                                 Filter: (c1 = 1)
+                     ->  Sort  (cost=xxx rows=1 width=4)
+                           Sort Key: t2.c1
+                           ->  Seq Scan on t2  (cost=xxx rows=1 width=4)
+                                 Filter: (ctid = '(1,1)'::tid)
+               ->  Tid Scan on t3  (cost=xxx rows=1 width=4)
+                     TID Cond: (ctid = '(1,1)'::tid)
+         ->  Tid Scan on t4  (cost=xxx rows=1 width=4)
+               TID Cond: (ctid = '(1,1)'::tid)
+(62 rows)
+
+                                   QUERY PLAN                                    
+---------------------------------------------------------------------------------
+ Aggregate  (cost=xxx rows=1 width=4)
+   ->  Nested Loop  (cost=xxx rows=2 width=4)
+         Join Filter: (t1.c1 = t4.c1)
+         ->  Tid Scan on t4  (cost=xxx rows=1 width=4)
+               TID Cond: (ctid = '(1,1)'::tid)
+         ->  Nested Loop  (cost=xxx rows=2 width=12)
+               Join Filter: (t1.c1 = t3.c1)
+               ->  Tid Scan on t3  (cost=xxx rows=1 width=4)
+                     TID Cond: (ctid = '(1,1)'::tid)
+               ->  Nested Loop  (cost=xxx rows=2 width=8)
+                     Join Filter: (t1.c1 = t2.c1)
+                     ->  Seq Scan on t2  (cost=xxx rows=1 width=4)
+                           Filter: (ctid = '(1,1)'::tid)
+                     ->  Nested Loop  (cost=xxx rows=2 width=4)
+                           ->  Tid Scan on t1  (cost=xxx rows=1 width=4)
+                                 TID Cond: (ctid = '(1,1)'::tid)
+                           ->  Seq Scan on r3  (cost=xxx rows=6 width=0)
+                                 Filter: (c1 = 1)
+ 
+ Aggregate  (cost=xxx rows=1 width=4)
+   ->  Nested Loop  (cost=xxx rows=2 width=4)
+         Join Filter: (t1.c1 = t4.c1)
+         ->  Tid Scan on t4  (cost=xxx rows=1 width=4)
+               TID Cond: (ctid = '(1,1)'::tid)
+         ->  Nested Loop  (cost=xxx rows=2 width=12)
+               Join Filter: (t1.c1 = t3.c1)
+               ->  Tid Scan on t3  (cost=xxx rows=1 width=4)
+                     TID Cond: (ctid = '(1,1)'::tid)
+               ->  Nested Loop  (cost=xxx rows=2 width=8)
+                     Join Filter: (t1.c1 = t2.c1)
+                     ->  Seq Scan on t2  (cost=xxx rows=1 width=4)
+                           Filter: (ctid = '(1,1)'::tid)
+                     ->  Nested Loop  (cost=xxx rows=2 width=4)
+                           ->  Tid Scan on t1  (cost=xxx rows=1 width=4)
+                                 TID Cond: (ctid = '(1,1)'::tid)
+                           ->  Seq Scan on r3  (cost=xxx rows=6 width=0)
+                                 Filter: (c1 = 1)
+ 
+ Aggregate  (cost=xxx rows=1 width=4)
+   ->  Nested Loop  (cost=xxx rows=2 width=4)
+         Join Filter: (t1.c1 = t4.c1)
+         ->  Tid Scan on t4  (cost=xxx rows=1 width=4)
+               TID Cond: (ctid = '(1,1)'::tid)
+         ->  Nested Loop  (cost=xxx rows=2 width=12)
+               Join Filter: (t1.c1 = t3.c1)
+               ->  Tid Scan on t3  (cost=xxx rows=1 width=4)
+                     TID Cond: (ctid = '(1,1)'::tid)
+               ->  Nested Loop  (cost=xxx rows=2 width=8)
+                     Join Filter: (t1.c1 = t2.c1)
+                     ->  Seq Scan on t2  (cost=xxx rows=1 width=4)
+                           Filter: (ctid = '(1,1)'::tid)
+                     ->  Nested Loop  (cost=xxx rows=2 width=4)
+                           ->  Tid Scan on t1  (cost=xxx rows=1 width=4)
+                                 TID Cond: (ctid = '(1,1)'::tid)
+                           ->  Seq Scan on r3  (cost=xxx rows=6 width=0)
+                                 Filter: (c1 = 1)
+(56 rows)
+
+                                             QUERY PLAN                                             
+----------------------------------------------------------------------------------------------------
+ Aggregate  (cost=xxx rows=1 width=4)
+   ->  Nested Loop  (cost=xxx rows=1 width=4)
+         Join Filter: (b1t1.c1 = b1t4.c1)
+         ->  Nested Loop  (cost=xxx rows=1 width=12)
+               Join Filter: (b1t1.c1 = b1t3.c1)
+               ->  Merge Join  (cost=xxx rows=1 width=8)
+                     Merge Cond: (b1t1.c1 = b1t2.c1)
+                     ->  Nested Loop  (cost=xxx rows=6 width=4)
+                           ->  Index Scan using t1_i1 on t1 b1t1  (cost=xxx rows=1 width=4)
+                                 Filter: (ctid = '(1,1)'::tid)
+                           ->  Seq Scan on r3_  (cost=xxx rows=6 width=0)
+                                 Filter: (c1 = 1)
+                     ->  Sort  (cost=xxx rows=1 width=4)
+                           Sort Key: b1t2.c1
+                           ->  Seq Scan on t2 b1t2  (cost=xxx rows=1 width=4)
+                                 Filter: (ctid = '(1,1)'::tid)
+               ->  Tid Scan on t3 b1t3  (cost=xxx rows=1 width=4)
+                     TID Cond: (ctid = '(1,1)'::tid)
+         ->  Tid Scan on t4 b1t4  (cost=xxx rows=1 width=4)
+               TID Cond: (ctid = '(1,1)'::tid)
+ 
+ Aggregate  (cost=xxx rows=1 width=4)
+   ->  Nested Loop  (cost=xxx rows=1 width=4)
+         Join Filter: (b2t1.c1 = b2t4.c1)
+         ->  Nested Loop  (cost=xxx rows=1 width=12)
+               Join Filter: (b2t1.c1 = b2t3.c1)
+               ->  Merge Join  (cost=xxx rows=1 width=8)
+                     Merge Cond: (b2t1.c1 = b2t2.c1)
+                     ->  Nested Loop  (cost=xxx rows=6 width=4)
+                           ->  Index Scan using t1_i1 on t1 b2t1  (cost=xxx rows=1 width=4)
+                                 Filter: (ctid = '(1,1)'::tid)
+                           ->  Seq Scan on r3_  (cost=xxx rows=6 width=0)
+                                 Filter: (c1 = 1)
+                     ->  Sort  (cost=xxx rows=1 width=4)
+                           Sort Key: b2t2.c1
+                           ->  Seq Scan on t2 b2t2  (cost=xxx rows=1 width=4)
+                                 Filter: (ctid = '(1,1)'::tid)
+               ->  Tid Scan on t3 b2t3  (cost=xxx rows=1 width=4)
+                     TID Cond: (ctid = '(1,1)'::tid)
+         ->  Tid Scan on t4 b2t4  (cost=xxx rows=1 width=4)
+               TID Cond: (ctid = '(1,1)'::tid)
+ 
+ Aggregate  (cost=xxx rows=1 width=4)
+   ->  Nested Loop  (cost=xxx rows=1 width=4)
+         Join Filter: (b3t1.c1 = b3t4.c1)
+         ->  Nested Loop  (cost=xxx rows=1 width=12)
+               Join Filter: (b3t1.c1 = b3t3.c1)
+               ->  Merge Join  (cost=xxx rows=1 width=8)
+                     Merge Cond: (b3t1.c1 = b3t2.c1)
+                     ->  Nested Loop  (cost=xxx rows=6 width=4)
+                           ->  Index Scan using t1_i1 on t1 b3t1  (cost=xxx rows=1 width=4)
+                                 Filter: (ctid = '(1,1)'::tid)
+                           ->  Seq Scan on r3_  (cost=xxx rows=6 width=0)
+                                 Filter: (c1 = 1)
+                     ->  Sort  (cost=xxx rows=1 width=4)
+                           Sort Key: b3t2.c1
+                           ->  Seq Scan on t2 b3t2  (cost=xxx rows=1 width=4)
+                                 Filter: (ctid = '(1,1)'::tid)
+               ->  Tid Scan on t3 b3t3  (cost=xxx rows=1 width=4)
+                     TID Cond: (ctid = '(1,1)'::tid)
+         ->  Tid Scan on t4 b3t4  (cost=xxx rows=1 width=4)
+               TID Cond: (ctid = '(1,1)'::tid)
+(62 rows)
+
+                                     QUERY PLAN                                      
+-------------------------------------------------------------------------------------
+ Aggregate  (cost=xxx rows=1 width=4)
+   ->  Nested Loop  (cost=xxx rows=2 width=4)
+         Join Filter: (b1t1.c1 = b1t4.c1)
+         ->  Tid Scan on t4 b1t4  (cost=xxx rows=1 width=4)
+               TID Cond: (ctid = '(1,1)'::tid)
+         ->  Nested Loop  (cost=xxx rows=2 width=12)
+               Join Filter: (b1t1.c1 = b1t3.c1)
+               ->  Tid Scan on t3 b1t3  (cost=xxx rows=1 width=4)
+                     TID Cond: (ctid = '(1,1)'::tid)
+               ->  Nested Loop  (cost=xxx rows=2 width=8)
+                     Join Filter: (b1t1.c1 = b1t2.c1)
+                     ->  Seq Scan on t2 b1t2  (cost=xxx rows=1 width=4)
+                           Filter: (ctid = '(1,1)'::tid)
+                     ->  Nested Loop  (cost=xxx rows=2 width=4)
+                           ->  Tid Scan on t1 b1t1  (cost=xxx rows=1 width=4)
+                                 TID Cond: (ctid = '(1,1)'::tid)
+                           ->  Seq Scan on r3_  (cost=xxx rows=6 width=0)
+                                 Filter: (c1 = 1)
+ 
+ Aggregate  (cost=xxx rows=1 width=4)
+   ->  Nested Loop  (cost=xxx rows=2 width=4)
+         Join Filter: (b2t1.c1 = b2t4.c1)
+         ->  Tid Scan on t4 b2t4  (cost=xxx rows=1 width=4)
+               TID Cond: (ctid = '(1,1)'::tid)
+         ->  Nested Loop  (cost=xxx rows=2 width=12)
+               Join Filter: (b2t1.c1 = b2t3.c1)
+               ->  Tid Scan on t3 b2t3  (cost=xxx rows=1 width=4)
+                     TID Cond: (ctid = '(1,1)'::tid)
+               ->  Nested Loop  (cost=xxx rows=2 width=8)
+                     Join Filter: (b2t1.c1 = b2t2.c1)
+                     ->  Seq Scan on t2 b2t2  (cost=xxx rows=1 width=4)
+                           Filter: (ctid = '(1,1)'::tid)
+                     ->  Nested Loop  (cost=xxx rows=2 width=4)
+                           ->  Tid Scan on t1 b2t1  (cost=xxx rows=1 width=4)
+                                 TID Cond: (ctid = '(1,1)'::tid)
+                           ->  Seq Scan on r3_  (cost=xxx rows=6 width=0)
+                                 Filter: (c1 = 1)
+ 
+ Aggregate  (cost=xxx rows=1 width=4)
+   ->  Nested Loop  (cost=xxx rows=2 width=4)
+         Join Filter: (b3t1.c1 = b3t4.c1)
+         ->  Tid Scan on t4 b3t4  (cost=xxx rows=1 width=4)
+               TID Cond: (ctid = '(1,1)'::tid)
+         ->  Nested Loop  (cost=xxx rows=2 width=12)
+               Join Filter: (b3t1.c1 = b3t3.c1)
+               ->  Tid Scan on t3 b3t3  (cost=xxx rows=1 width=4)
+                     TID Cond: (ctid = '(1,1)'::tid)
+               ->  Nested Loop  (cost=xxx rows=2 width=8)
+                     Join Filter: (b3t1.c1 = b3t2.c1)
+                     ->  Seq Scan on t2 b3t2  (cost=xxx rows=1 width=4)
+                           Filter: (ctid = '(1,1)'::tid)
+                     ->  Nested Loop  (cost=xxx rows=2 width=4)
+                           ->  Tid Scan on t1 b3t1  (cost=xxx rows=1 width=4)
+                                 TID Cond: (ctid = '(1,1)'::tid)
+                           ->  Seq Scan on r3_  (cost=xxx rows=6 width=0)
+                                 Filter: (c1 = 1)
+(56 rows)
+

diff --git a/expected/R_2-3-4.out b/expected/R_2-3-4.out
new file mode 100644 (file)
index 0000000..171b55e
--- /dev/null
+++ b/expected/R_2-3-4.out
@@ -0,0 +1,18 @@
+                                QUERY PLAN                                
+--------------------------------------------------------------------------
+ Hash Join  (cost=xxx rows=1000 width=30)
+   Hash Cond: (v1t1.c1 = v1t1_1.c1)
+   ->  Seq Scan on t1 v1t1  (cost=xxx rows=1000 width=15)
+   ->  Hash  (cost=xxx rows=1000 width=15)
+         ->  Seq Scan on t1 v1t1_1  (cost=xxx rows=1000 width=15)
+(5 rows)
+
+                                QUERY PLAN                                
+--------------------------------------------------------------------------
+ Hash Join  (cost=xxx rows=1000 width=30)
+   Hash Cond: (v1t1.c1 = v1t1_1.c1)
+   ->  Seq Scan on t1 v1t1  (cost=xxx rows=1000 width=15)
+   ->  Hash  (cost=xxx rows=1000 width=15)
+         ->  Seq Scan on t1 v1t1_1  (cost=xxx rows=1000 width=15)
+(5 rows)
+

diff --git a/expected/R_2-3-5.out b/expected/R_2-3-5.out
new file mode 100644 (file)
index 0000000..01a379f
--- /dev/null
+++ b/expected/R_2-3-5.out
@@ -0,0 +1,16 @@
+                                 QUERY PLAN                                  
+-----------------------------------------------------------------------------
+ Nested Loop  (cost=xxx rows=1000 width=30)
+   ->  Seq Scan on t1 v1t1  (cost=xxx rows=1000 width=15)
+   ->  Index Scan using t1_i1 on t1 v1t1_  (cost=xxx rows=1 width=15)
+         Index Cond: (c1 = v1t1.c1)
+(4 rows)
+
+                                 QUERY PLAN                                  
+-----------------------------------------------------------------------------
+ Nested Loop  (cost=xxx rows=1 width=30)
+   ->  Seq Scan on t1 v1t1  (cost=xxx rows=1000 width=15)
+   ->  Index Scan using t1_i1 on t1 v1t1_  (cost=xxx rows=1 width=15)
+         Index Cond: (c1 = v1t1.c1)
+(4 rows)
+

diff --git a/expected/R_2-3-6.out b/expected/R_2-3-6.out
new file mode 100644 (file)
index 0000000..c3f9bc8
--- /dev/null
+++ b/expected/R_2-3-6.out
@@ -0,0 +1,18 @@
+                                QUERY PLAN                                
+--------------------------------------------------------------------------
+ Hash Join  (cost=xxx rows=1000 width=30)
+   Hash Cond: (r4t1.c1 = r4t1_1.c1)
+   ->  Seq Scan on t1 r4t1  (cost=xxx rows=1000 width=15)
+   ->  Hash  (cost=xxx rows=1000 width=15)
+         ->  Seq Scan on t1 r4t1_1  (cost=xxx rows=1000 width=15)
+(5 rows)
+
+                                QUERY PLAN                                
+--------------------------------------------------------------------------
+ Hash Join  (cost=xxx rows=1000 width=30)
+   Hash Cond: (r4t1.c1 = r4t1_1.c1)
+   ->  Seq Scan on t1 r4t1  (cost=xxx rows=1000 width=15)
+   ->  Hash  (cost=xxx rows=1000 width=15)
+         ->  Seq Scan on t1 r4t1_1  (cost=xxx rows=1000 width=15)
+(5 rows)
+

diff --git a/expected/R_2-3-7.out b/expected/R_2-3-7.out
new file mode 100644 (file)
index 0000000..7b5aaf4
--- /dev/null
+++ b/expected/R_2-3-7.out
@@ -0,0 +1,16 @@
+                                 QUERY PLAN                                 
+----------------------------------------------------------------------------
+ Nested Loop  (cost=xxx rows=1000 width=30)
+   ->  Seq Scan on t1 r4t1  (cost=xxx rows=1000 width=15)
+   ->  Index Scan using t1_i1 on t1 r5t1  (cost=xxx rows=1 width=15)
+         Index Cond: (c1 = r4t1.c1)
+(4 rows)
+
+                                 QUERY PLAN                                 
+----------------------------------------------------------------------------
+ Nested Loop  (cost=xxx rows=1 width=30)
+   ->  Seq Scan on t1 r4t1  (cost=xxx rows=1000 width=15)
+   ->  Index Scan using t1_i1 on t1 r5t1  (cost=xxx rows=1 width=15)
+         Index Cond: (c1 = r4t1.c1)
+(4 rows)
+

diff --git a/expected/ut-R.out b/expected/ut-R.out
index 71cc929..1c8c2ca 100644 (file)
--- a/expected/ut-R.out
+++ b/expected/ut-R.out
@@ -1585,3 +1585,466 @@ error hint:
 \o
 \! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_2-2-4.out.log > results/R_2-2-4.out
 \! diff expected/R_2-2-4.out results/R_2-2-4.out
+----
+---- No. R-2-3 RULE or VIEW
+----
+-- No. R-2-3-1
+\o results/R_2-3-1.out.log
+/*+
+Leading(r1 t1 t2 t3 t4)
+*/
+EXPLAIN UPDATE s1.r1 SET c1 = c1 WHERE c1 = 1;
+LOG:  pg_hint_plan:
+used hint:
+Leading(r1 t1 t2 t3 t4)
+not used hint:
+duplication hint:
+error hint:
+
+/*+
+Leading(r1 t1 t2 t3 t4)
+Rows(r1 t1 t2 t3 t4 #2)
+Rows(r1 t1 t2 t3 #2)
+Rows(r1 t1 t2 #2)
+Rows(r1 t1 #2)
+*/
+EXPLAIN UPDATE s1.r1 SET c1 = c1 WHERE c1 = 1;
+LOG:  pg_hint_plan:
+used hint:
+Leading(r1 t1 t2 t3 t4)
+Rows(r1 t1 #2)
+Rows(r1 t1 t2 #2)
+Rows(r1 t1 t2 t3 #2)
+Rows(r1 t1 t2 t3 t4 #2)
+not used hint:
+duplication hint:
+error hint:
+
+/*+
+Leading(r1_ b1t1 b1t2 b1t3 b1t4)
+*/
+EXPLAIN UPDATE s1.r1_ SET c1 = c1 WHERE c1 = 1;
+LOG:  pg_hint_plan:
+used hint:
+Leading(r1_ b1t1 b1t2 b1t3 b1t4)
+not used hint:
+duplication hint:
+error hint:
+
+/*+
+Leading(r1_ b1t1 b1t2 b1t3 b1t4)
+Rows(r1_ b1t1 b1t2 b1t3 b1t4 #2)
+Rows(r1_ b1t1 b1t2 b1t3 #2)
+Rows(r1_ b1t1 b1t2 #2)
+Rows(r1_ b1t1 #2)
+*/
+EXPLAIN UPDATE s1.r1_ SET c1 = c1 WHERE c1 = 1;
+LOG:  pg_hint_plan:
+used hint:
+Leading(r1_ b1t1 b1t2 b1t3 b1t4)
+Rows(b1t1 r1_ #2)
+Rows(b1t1 b1t2 r1_ #2)
+Rows(b1t1 b1t2 b1t3 r1_ #2)
+Rows(b1t1 b1t2 b1t3 b1t4 r1_ #2)
+not used hint:
+duplication hint:
+error hint:
+
+\o
+\! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_2-3-1.out.log > results/R_2-3-1.out
+\! diff expected/R_2-3-1.out results/R_2-3-1.out
+-- No. R-2-3-2
+\o results/R_2-3-2.out.log
+/*+
+Leading(r2 t1 t2 t3 t4)
+*/
+EXPLAIN UPDATE s1.r2 SET c1 = c1 WHERE c1 = 1;
+LOG:  pg_hint_plan:
+used hint:
+Leading(r2 t1 t2 t3 t4)
+not used hint:
+duplication hint:
+error hint:
+
+LOG:  pg_hint_plan:
+used hint:
+Leading(r2 t1 t2 t3 t4)
+not used hint:
+duplication hint:
+error hint:
+
+/*+
+Leading(r2 t1 t2 t3 t4)
+Rows(r2 t1 t2 t3 t4 #2)
+Rows(r2 t1 t2 t3 #2)
+Rows(r2 t1 t2 #2)
+Rows(r2 t1 #2)
+*/
+EXPLAIN UPDATE s1.r2 SET c1 = c1 WHERE c1 = 1;
+LOG:  pg_hint_plan:
+used hint:
+Leading(r2 t1 t2 t3 t4)
+Rows(r2 t1 #2)
+Rows(r2 t1 t2 #2)
+Rows(r2 t1 t2 t3 #2)
+Rows(r2 t1 t2 t3 t4 #2)
+not used hint:
+duplication hint:
+error hint:
+
+LOG:  pg_hint_plan:
+used hint:
+Leading(r2 t1 t2 t3 t4)
+Rows(r2 t1 #2)
+Rows(r2 t1 t2 #2)
+Rows(r2 t1 t2 t3 #2)
+Rows(r2 t1 t2 t3 t4 #2)
+not used hint:
+duplication hint:
+error hint:
+
+/*+
+Leading(r2_ b1t1 b1t2 b1t3 b1t4)
+Leading(r2_ b2t1 b2t2 b2t3 b2t4)
+*/
+EXPLAIN UPDATE s1.r2_ SET c1 = c1 WHERE c1 = 1;
+LOG:  pg_hint_plan:
+used hint:
+Leading(r2_ b1t1 b1t2 b1t3 b1t4)
+not used hint:
+Leading(r2_ b2t1 b2t2 b2t3 b2t4)
+duplication hint:
+error hint:
+
+LOG:  pg_hint_plan:
+used hint:
+Leading(r2_ b2t1 b2t2 b2t3 b2t4)
+not used hint:
+Leading(r2_ b1t1 b1t2 b1t3 b1t4)
+duplication hint:
+error hint:
+
+/*+
+Leading(r2_ b1t1 b1t2 b1t3 b1t4)
+Leading(r2_ b2t1 b2t2 b2t3 b2t4)
+Rows(r2_ b1t1 #2)
+Rows(r2_ b1t1 b1t2 #2)
+Rows(r2_ b1t1 b1t2 b1t3 #2)
+Rows(r2_ b1t1 b1t2 b1t3 b1t4 #2)
+Rows(r2_ b2t1 #2)
+Rows(r2_ b2t1 b2t2 #2)
+Rows(r2_ b2t1 b2t2 b2t3  #2)
+Rows(r2_ b2t1 b2t2 b2t3 b2t4 #2)
+*/
+EXPLAIN UPDATE s1.r2_ SET c1 = c1 WHERE c1 = 1;
+LOG:  pg_hint_plan:
+used hint:
+Leading(r2_ b1t1 b1t2 b1t3 b1t4)
+Rows(b1t1 r2_ #2)
+Rows(b1t1 b1t2 r2_ #2)
+Rows(b1t1 b1t2 b1t3 r2_ #2)
+Rows(b1t1 b1t2 b1t3 b1t4 r2_ #2)
+not used hint:
+Leading(r2_ b2t1 b2t2 b2t3 b2t4)
+Rows(b2t1 r2_ #2)
+Rows(b2t1 b2t2 r2_ #2)
+Rows(b2t1 b2t2 b2t3 r2_ #2)
+Rows(b2t1 b2t2 b2t3 b2t4 r2_ #2)
+duplication hint:
+error hint:
+
+LOG:  pg_hint_plan:
+used hint:
+Leading(r2_ b2t1 b2t2 b2t3 b2t4)
+Rows(b2t1 r2_ #2)
+Rows(b2t1 b2t2 r2_ #2)
+Rows(b2t1 b2t2 b2t3 r2_ #2)
+Rows(b2t1 b2t2 b2t3 b2t4 r2_ #2)
+not used hint:
+Leading(r2_ b1t1 b1t2 b1t3 b1t4)
+Rows(b1t1 r2_ #2)
+Rows(b1t1 b1t2 r2_ #2)
+Rows(b1t1 b1t2 b1t3 r2_ #2)
+Rows(b1t1 b1t2 b1t3 b1t4 r2_ #2)
+duplication hint:
+error hint:
+
+\o
+\! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_2-3-2.out.log > results/R_2-3-2.out
+\! diff expected/R_2-3-2.out results/R_2-3-2.out
+-- No. R-2-3-3
+\o results/R_2-3-3.out.log
+/*+
+Leading(r3 t1 t2 t3 t4)
+*/
+EXPLAIN UPDATE s1.r3 SET c1 = c1 WHERE c1 = 1;
+LOG:  pg_hint_plan:
+used hint:
+Leading(r3 t1 t2 t3 t4)
+not used hint:
+duplication hint:
+error hint:
+
+LOG:  pg_hint_plan:
+used hint:
+Leading(r3 t1 t2 t3 t4)
+not used hint:
+duplication hint:
+error hint:
+
+LOG:  pg_hint_plan:
+used hint:
+Leading(r3 t1 t2 t3 t4)
+not used hint:
+duplication hint:
+error hint:
+
+/*+
+Leading(r3 t1 t2 t3 t4)
+Rows(r3 t1 t2 t3 t4 #2)
+Rows(r3 t1 t2 t3 #2)
+Rows(r3 t1 t2 #2)
+Rows(r3 t1 #2)
+*/
+EXPLAIN UPDATE s1.r3 SET c1 = c1 WHERE c1 = 1;
+LOG:  pg_hint_plan:
+used hint:
+Leading(r3 t1 t2 t3 t4)
+Rows(r3 t1 #2)
+Rows(r3 t1 t2 #2)
+Rows(r3 t1 t2 t3 #2)
+Rows(r3 t1 t2 t3 t4 #2)
+not used hint:
+duplication hint:
+error hint:
+
+LOG:  pg_hint_plan:
+used hint:
+Leading(r3 t1 t2 t3 t4)
+Rows(r3 t1 #2)
+Rows(r3 t1 t2 #2)
+Rows(r3 t1 t2 t3 #2)
+Rows(r3 t1 t2 t3 t4 #2)
+not used hint:
+duplication hint:
+error hint:
+
+LOG:  pg_hint_plan:
+used hint:
+Leading(r3 t1 t2 t3 t4)
+Rows(r3 t1 #2)
+Rows(r3 t1 t2 #2)
+Rows(r3 t1 t2 t3 #2)
+Rows(r3 t1 t2 t3 t4 #2)
+not used hint:
+duplication hint:
+error hint:
+
+/*+
+Leading(r3_ b1t1 b1t2 b1t3 b1t4)
+Leading(r3_ b2t1 b2t2 b2t3 b2t4)
+Leading(r3_ b3t1 b3t2 b3t3 b3t4)
+*/
+EXPLAIN UPDATE s1.r3_ SET c1 = c1 WHERE c1 = 1;
+LOG:  pg_hint_plan:
+used hint:
+Leading(r3_ b1t1 b1t2 b1t3 b1t4)
+not used hint:
+Leading(r3_ b2t1 b2t2 b2t3 b2t4)
+Leading(r3_ b3t1 b3t2 b3t3 b3t4)
+duplication hint:
+error hint:
+
+LOG:  pg_hint_plan:
+used hint:
+Leading(r3_ b2t1 b2t2 b2t3 b2t4)
+not used hint:
+Leading(r3_ b1t1 b1t2 b1t3 b1t4)
+Leading(r3_ b3t1 b3t2 b3t3 b3t4)
+duplication hint:
+error hint:
+
+LOG:  pg_hint_plan:
+used hint:
+Leading(r3_ b3t1 b3t2 b3t3 b3t4)
+not used hint:
+Leading(r3_ b1t1 b1t2 b1t3 b1t4)
+Leading(r3_ b2t1 b2t2 b2t3 b2t4)
+duplication hint:
+error hint:
+
+/*+
+Leading(r3_ b1t1 b1t2 b1t3 b1t4)
+Leading(r3_ b2t1 b2t2 b2t3 b2t4)
+Leading(r3_ b3t1 b3t2 b3t3 b3t4)
+Rows(r3_ b1t1 #2)
+Rows(r3_ b1t1 b1t2 #2)
+Rows(r3_ b1t1 b1t2 b1t3 #2)
+Rows(r3_ b1t1 b1t2 b1t3 b1t4 #2)
+Rows(r3_ b2t1 #2)
+Rows(r3_ b2t1 b2t2 #2)
+Rows(r3_ b2t1 b2t2 b2t3 #2)
+Rows(r3_ b2t1 b2t2 b2t3 b2t4 #2)
+Rows(r3_ b3t1 #2)
+Rows(r3_ b3t1 b3t2 #2)
+Rows(r3_ b3t1 b3t2 b3t3 #2)
+Rows(r3_ b3t1 b3t2 b3t3 b3t4 #2)
+*/
+EXPLAIN UPDATE s1.r3_ SET c1 = c1 WHERE c1 = 1;
+LOG:  pg_hint_plan:
+used hint:
+Leading(r3_ b1t1 b1t2 b1t3 b1t4)
+Rows(b1t1 r3_ #2)
+Rows(b1t1 b1t2 r3_ #2)
+Rows(b1t1 b1t2 b1t3 r3_ #2)
+Rows(b1t1 b1t2 b1t3 b1t4 r3_ #2)
+not used hint:
+Leading(r3_ b2t1 b2t2 b2t3 b2t4)
+Leading(r3_ b3t1 b3t2 b3t3 b3t4)
+Rows(b2t1 r3_ #2)
+Rows(b3t1 r3_ #2)
+Rows(b2t1 b2t2 r3_ #2)
+Rows(b3t1 b3t2 r3_ #2)
+Rows(b2t1 b2t2 b2t3 r3_ #2)
+Rows(b3t1 b3t2 b3t3 r3_ #2)
+Rows(b2t1 b2t2 b2t3 b2t4 r3_ #2)
+Rows(b3t1 b3t2 b3t3 b3t4 r3_ #2)
+duplication hint:
+error hint:
+
+LOG:  pg_hint_plan:
+used hint:
+Leading(r3_ b2t1 b2t2 b2t3 b2t4)
+Rows(b2t1 r3_ #2)
+Rows(b2t1 b2t2 r3_ #2)
+Rows(b2t1 b2t2 b2t3 r3_ #2)
+Rows(b2t1 b2t2 b2t3 b2t4 r3_ #2)
+not used hint:
+Leading(r3_ b1t1 b1t2 b1t3 b1t4)
+Leading(r3_ b3t1 b3t2 b3t3 b3t4)
+Rows(b1t1 r3_ #2)
+Rows(b3t1 r3_ #2)
+Rows(b1t1 b1t2 r3_ #2)
+Rows(b3t1 b3t2 r3_ #2)
+Rows(b1t1 b1t2 b1t3 r3_ #2)
+Rows(b3t1 b3t2 b3t3 r3_ #2)
+Rows(b1t1 b1t2 b1t3 b1t4 r3_ #2)
+Rows(b3t1 b3t2 b3t3 b3t4 r3_ #2)
+duplication hint:
+error hint:
+
+LOG:  pg_hint_plan:
+used hint:
+Leading(r3_ b3t1 b3t2 b3t3 b3t4)
+Rows(b3t1 r3_ #2)
+Rows(b3t1 b3t2 r3_ #2)
+Rows(b3t1 b3t2 b3t3 r3_ #2)
+Rows(b3t1 b3t2 b3t3 b3t4 r3_ #2)
+not used hint:
+Leading(r3_ b1t1 b1t2 b1t3 b1t4)
+Leading(r3_ b2t1 b2t2 b2t3 b2t4)
+Rows(b1t1 r3_ #2)
+Rows(b2t1 r3_ #2)
+Rows(b1t1 b1t2 r3_ #2)
+Rows(b2t1 b2t2 r3_ #2)
+Rows(b1t1 b1t2 b1t3 r3_ #2)
+Rows(b2t1 b2t2 b2t3 r3_ #2)
+Rows(b1t1 b1t2 b1t3 b1t4 r3_ #2)
+Rows(b2t1 b2t2 b2t3 b2t4 r3_ #2)
+duplication hint:
+error hint:
+
+\o
+\! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_2-3-3.out.log > results/R_2-3-3.out
+\! diff expected/R_2-3-3.out results/R_2-3-3.out
+-- No. R-2-3-4
+\o results/R_2-3-4.out.log
+/*+HashJoin(v1t1 v1t1)*/
+EXPLAIN SELECT * FROM s1.v1 v1, s1.v1 v2 WHERE v1.c1 = v2.c1;
+INFO:  hint syntax error at or near "HashJoin(v1t1 v1t1)"
+DETAIL:  Relation name "v1t1" is ambiguous.
+LOG:  pg_hint_plan:
+used hint:
+not used hint:
+duplication hint:
+error hint:
+HashJoin(v1t1 v1t1)
+
+/*+HashJoin(v1t1 v1t1)Rows(v1t1 v1t1 #1)*/
+EXPLAIN SELECT * FROM s1.v1 v1, s1.v1 v2 WHERE v1.c1 = v2.c1;
+INFO:  hint syntax error at or near "HashJoin(v1t1 v1t1)Rows(v1t1 v1t1 #1)"
+DETAIL:  Relation name "v1t1" is ambiguous.
+INFO:  hint syntax error at or near "Rows(v1t1 v1t1 #1)"
+DETAIL:  Relation name "v1t1" is ambiguous.
+LOG:  pg_hint_plan:
+used hint:
+not used hint:
+duplication hint:
+error hint:
+HashJoin(v1t1 v1t1)
+Rows(v1t1 v1t1 #1)
+
+\o
+\! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_2-3-4.out.log > results/R_2-3-4.out
+\! diff expected/R_2-3-4.out results/R_2-3-4.out
+-- No. R-2-3-5
+\o results/R_2-3-5.out.log
+/*+NestLoop(v1t1 v1t1_)*/
+EXPLAIN SELECT * FROM s1.v1 v1, s1.v1_ v2 WHERE v1.c1 = v2.c1;
+LOG:  pg_hint_plan:
+used hint:
+NestLoop(v1t1 v1t1_)
+not used hint:
+duplication hint:
+error hint:
+
+/*+NestLoop(v1t1 v1t1_)Rows(v1t1 v1t1_ #1)*/
+EXPLAIN SELECT * FROM s1.v1 v1, s1.v1_ v2 WHERE v1.c1 = v2.c1;
+LOG:  pg_hint_plan:
+used hint:
+NestLoop(v1t1 v1t1_)
+Rows(v1t1 v1t1_ #1)
+not used hint:
+duplication hint:
+error hint:
+
+\o
+\! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_2-3-5.out.log > results/R_2-3-5.out
+\! diff expected/R_2-3-5.out results/R_2-3-5.out
+-- No. R-2-3-6
+\o results/R_2-3-6.out.log
+/*+RowsHashJoin(r4t1 r4t1)*/
+EXPLAIN SELECT * FROM s1.r4 t1, s1.r4 t2 WHERE t1.c1 = t2.c1;
+INFO:  hint syntax error at or near "RowsHashJoin(r4t1 r4t1)"
+DETAIL:  Unrecognized hint keyword "RowsHashJoin".
+/*+RowsHashJoin(r4t1 r4t1)Rows(r4t1 r4t1 #1)*/
+EXPLAIN SELECT * FROM s1.r4 t1, s1.r4 t2 WHERE t1.c1 = t2.c1;
+INFO:  hint syntax error at or near "RowsHashJoin(r4t1 r4t1)Rows(r4t1 r4t1 #1)"
+DETAIL:  Unrecognized hint keyword "RowsHashJoin".
+\o
+\! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_2-3-6.out.log > results/R_2-3-6.out
+\! diff expected/R_2-3-6.out results/R_2-3-6.out
+-- No. R-2-3-7
+\o results/R_2-3-7.out.log
+/*+NestLoop(r4t1 r5t1)*/
+EXPLAIN SELECT * FROM s1.r4 t1, s1.r5 t2 WHERE t1.c1 = t2.c1;
+LOG:  pg_hint_plan:
+used hint:
+NestLoop(r4t1 r5t1)
+not used hint:
+duplication hint:
+error hint:
+
+/*+NestLoop(r4t1 r5t1)Rows(r4t1 r5t1 #1)*/
+EXPLAIN SELECT * FROM s1.r4 t1, s1.r5 t2 WHERE t1.c1 = t2.c1;
+LOG:  pg_hint_plan:
+used hint:
+NestLoop(r4t1 r5t1)
+Rows(r4t1 r5t1 #1)
+not used hint:
+duplication hint:
+error hint:
+
+\o
+\! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_2-3-7.out.log > results/R_2-3-7.out
+\! diff expected/R_2-3-7.out results/R_2-3-7.out

diff --git a/sql/ut-R.sql b/sql/ut-R.sql
index 6327496..40dd65e 100644 (file)
--- a/sql/ut-R.sql
+++ b/sql/ut-R.sql
@@ -861,3 +861,155 @@ SELECT b3t1.c1 FROM s1.t1 b3t1
 \o
 \! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_2-2-4.out.log > results/R_2-2-4.out
 \! diff expected/R_2-2-4.out results/R_2-2-4.out
+
+----
+---- No. R-2-3 RULE or VIEW
+----
+
+-- No. R-2-3-1
+\o results/R_2-3-1.out.log
+/*+
+Leading(r1 t1 t2 t3 t4)
+*/
+EXPLAIN UPDATE s1.r1 SET c1 = c1 WHERE c1 = 1;
+/*+
+Leading(r1 t1 t2 t3 t4)
+Rows(r1 t1 t2 t3 t4 #2)
+Rows(r1 t1 t2 t3 #2)
+Rows(r1 t1 t2 #2)
+Rows(r1 t1 #2)
+*/
+EXPLAIN UPDATE s1.r1 SET c1 = c1 WHERE c1 = 1;
+/*+
+Leading(r1_ b1t1 b1t2 b1t3 b1t4)
+*/
+EXPLAIN UPDATE s1.r1_ SET c1 = c1 WHERE c1 = 1;
+/*+
+Leading(r1_ b1t1 b1t2 b1t3 b1t4)
+Rows(r1_ b1t1 b1t2 b1t3 b1t4 #2)
+Rows(r1_ b1t1 b1t2 b1t3 #2)
+Rows(r1_ b1t1 b1t2 #2)
+Rows(r1_ b1t1 #2)
+*/
+EXPLAIN UPDATE s1.r1_ SET c1 = c1 WHERE c1 = 1;
+\o
+\! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_2-3-1.out.log > results/R_2-3-1.out
+\! diff expected/R_2-3-1.out results/R_2-3-1.out
+
+-- No. R-2-3-2
+\o results/R_2-3-2.out.log
+/*+
+Leading(r2 t1 t2 t3 t4)
+*/
+EXPLAIN UPDATE s1.r2 SET c1 = c1 WHERE c1 = 1;
+/*+
+Leading(r2 t1 t2 t3 t4)
+Rows(r2 t1 t2 t3 t4 #2)
+Rows(r2 t1 t2 t3 #2)
+Rows(r2 t1 t2 #2)
+Rows(r2 t1 #2)
+*/
+EXPLAIN UPDATE s1.r2 SET c1 = c1 WHERE c1 = 1;
+/*+
+Leading(r2_ b1t1 b1t2 b1t3 b1t4)
+Leading(r2_ b2t1 b2t2 b2t3 b2t4)
+*/
+EXPLAIN UPDATE s1.r2_ SET c1 = c1 WHERE c1 = 1;
+/*+
+Leading(r2_ b1t1 b1t2 b1t3 b1t4)
+Leading(r2_ b2t1 b2t2 b2t3 b2t4)
+Rows(r2_ b1t1 #2)
+Rows(r2_ b1t1 b1t2 #2)
+Rows(r2_ b1t1 b1t2 b1t3 #2)
+Rows(r2_ b1t1 b1t2 b1t3 b1t4 #2)
+Rows(r2_ b2t1 #2)
+Rows(r2_ b2t1 b2t2 #2)
+Rows(r2_ b2t1 b2t2 b2t3  #2)
+Rows(r2_ b2t1 b2t2 b2t3 b2t4 #2)
+*/
+EXPLAIN UPDATE s1.r2_ SET c1 = c1 WHERE c1 = 1;
+\o
+\! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_2-3-2.out.log > results/R_2-3-2.out
+\! diff expected/R_2-3-2.out results/R_2-3-2.out
+
+-- No. R-2-3-3
+\o results/R_2-3-3.out.log
+/*+
+Leading(r3 t1 t2 t3 t4)
+*/
+EXPLAIN UPDATE s1.r3 SET c1 = c1 WHERE c1 = 1;
+/*+
+Leading(r3 t1 t2 t3 t4)
+Rows(r3 t1 t2 t3 t4 #2)
+Rows(r3 t1 t2 t3 #2)
+Rows(r3 t1 t2 #2)
+Rows(r3 t1 #2)
+*/
+EXPLAIN UPDATE s1.r3 SET c1 = c1 WHERE c1 = 1;
+/*+
+Leading(r3_ b1t1 b1t2 b1t3 b1t4)
+Leading(r3_ b2t1 b2t2 b2t3 b2t4)
+Leading(r3_ b3t1 b3t2 b3t3 b3t4)
+*/
+EXPLAIN UPDATE s1.r3_ SET c1 = c1 WHERE c1 = 1;
+/*+
+Leading(r3_ b1t1 b1t2 b1t3 b1t4)
+Leading(r3_ b2t1 b2t2 b2t3 b2t4)
+Leading(r3_ b3t1 b3t2 b3t3 b3t4)
+Rows(r3_ b1t1 #2)
+Rows(r3_ b1t1 b1t2 #2)
+Rows(r3_ b1t1 b1t2 b1t3 #2)
+Rows(r3_ b1t1 b1t2 b1t3 b1t4 #2)
+Rows(r3_ b2t1 #2)
+Rows(r3_ b2t1 b2t2 #2)
+Rows(r3_ b2t1 b2t2 b2t3 #2)
+Rows(r3_ b2t1 b2t2 b2t3 b2t4 #2)
+Rows(r3_ b3t1 #2)
+Rows(r3_ b3t1 b3t2 #2)
+Rows(r3_ b3t1 b3t2 b3t3 #2)
+Rows(r3_ b3t1 b3t2 b3t3 b3t4 #2)
+*/
+EXPLAIN UPDATE s1.r3_ SET c1 = c1 WHERE c1 = 1;
+\o
+\! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_2-3-3.out.log > results/R_2-3-3.out
+\! diff expected/R_2-3-3.out results/R_2-3-3.out
+
+-- No. R-2-3-4
+\o results/R_2-3-4.out.log
+/*+HashJoin(v1t1 v1t1)*/
+EXPLAIN SELECT * FROM s1.v1 v1, s1.v1 v2 WHERE v1.c1 = v2.c1;
+/*+HashJoin(v1t1 v1t1)Rows(v1t1 v1t1 #1)*/
+EXPLAIN SELECT * FROM s1.v1 v1, s1.v1 v2 WHERE v1.c1 = v2.c1;
+\o
+\! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_2-3-4.out.log > results/R_2-3-4.out
+\! diff expected/R_2-3-4.out results/R_2-3-4.out
+
+-- No. R-2-3-5
+\o results/R_2-3-5.out.log
+/*+NestLoop(v1t1 v1t1_)*/
+EXPLAIN SELECT * FROM s1.v1 v1, s1.v1_ v2 WHERE v1.c1 = v2.c1;
+/*+NestLoop(v1t1 v1t1_)Rows(v1t1 v1t1_ #1)*/
+EXPLAIN SELECT * FROM s1.v1 v1, s1.v1_ v2 WHERE v1.c1 = v2.c1;
+\o
+\! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_2-3-5.out.log > results/R_2-3-5.out
+\! diff expected/R_2-3-5.out results/R_2-3-5.out
+
+-- No. R-2-3-6
+\o results/R_2-3-6.out.log
+/*+RowsHashJoin(r4t1 r4t1)*/
+EXPLAIN SELECT * FROM s1.r4 t1, s1.r4 t2 WHERE t1.c1 = t2.c1;
+/*+RowsHashJoin(r4t1 r4t1)Rows(r4t1 r4t1 #1)*/
+EXPLAIN SELECT * FROM s1.r4 t1, s1.r4 t2 WHERE t1.c1 = t2.c1;
+\o
+\! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_2-3-6.out.log > results/R_2-3-6.out
+\! diff expected/R_2-3-6.out results/R_2-3-6.out
+
+-- No. R-2-3-7
+\o results/R_2-3-7.out.log
+/*+NestLoop(r4t1 r5t1)*/
+EXPLAIN SELECT * FROM s1.r4 t1, s1.r5 t2 WHERE t1.c1 = t2.c1;
+/*+NestLoop(r4t1 r5t1)Rows(r4t1 r5t1 #1)*/
+EXPLAIN SELECT * FROM s1.r4 t1, s1.r5 t2 WHERE t1.c1 = t2.c1;
+\o
+\! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_2-3-7.out.log > results/R_2-3-7.out
+\! diff expected/R_2-3-7.out results/R_2-3-7.out