From 8d3c4aa614e20375daeff0bb1b9f640b115f363e Mon Sep 17 00:00:00 2001
From: Tom Lane
Date: Mon, 22 Mar 2010 13:57:16 +0000
Subject: Fix an oversight in join-removal optimization: we have to check not
 only for plain Vars that are generated in the inner rel and used above the
 join, but also for PlaceHolderVars.  Per report from Oleg K.

---
 src/test/regress/expected/join.out | 49 ++++++++++++++++++++++++++++++++++++++
 src/test/regress/sql/join.sql      | 23 ++++++++++++++++++
 2 files changed, 72 insertions(+)

(limited to 'src/test')

diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out
index f2b346e5084..ad164e1c02b 100644
--- a/src/test/regress/expected/join.out
+++ b/src/test/regress/expected/join.out
@@ -2491,3 +2491,52 @@ select * from int4_tbl a full join int4_tbl b on false;
  -2147483647 |            
 (10 rows)
 
+--
+-- test join removal
+--
+create temp table parent (k int primary key, pd int);
+NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "parent_pkey" for table "parent"
+create temp table child (k int unique, cd int);
+NOTICE:  CREATE TABLE / UNIQUE will create implicit index "child_k_key" for table "child"
+insert into parent values (1, 10), (2, 20), (3, 30);
+insert into child values (1, 100), (4, 400);
+-- this case is optimizable
+select p.* from parent p left join child c on (p.k = c.k);
+ k | pd 
+---+----
+ 1 | 10
+ 2 | 20
+ 3 | 30
+(3 rows)
+
+explain (costs off)
+  select p.* from parent p left join child c on (p.k = c.k);
+      QUERY PLAN      
+----------------------
+ Seq Scan on parent p
+(1 row)
+
+-- this case is not
+select p.*, linked from parent p
+  left join (select c.*, true as linked from child c) as ss
+  on (p.k = ss.k);
+ k | pd | linked 
+---+----+--------
+ 1 | 10 | t
+ 2 | 20 | 
+ 3 | 30 | 
+(3 rows)
+
+explain (costs off)
+  select p.*, linked from parent p
+    left join (select c.*, true as linked from child c) as ss
+    on (p.k = ss.k);
+           QUERY PLAN            
+---------------------------------
+ Hash Left Join
+   Hash Cond: (p.k = c.k)
+   ->  Seq Scan on parent p
+   ->  Hash
+         ->  Seq Scan on child c
+(5 rows)
+
diff --git a/src/test/regress/sql/join.sql b/src/test/regress/sql/join.sql
index 977765551d5..b0a4ceccf0b 100644
--- a/src/test/regress/sql/join.sql
+++ b/src/test/regress/sql/join.sql
@@ -567,3 +567,26 @@ group by t1.q2 order by 1;
 --
 select * from int4_tbl a full join int4_tbl b on true;
 select * from int4_tbl a full join int4_tbl b on false;
+
+--
+-- test join removal
+--
+
+create temp table parent (k int primary key, pd int);
+create temp table child (k int unique, cd int);
+insert into parent values (1, 10), (2, 20), (3, 30);
+insert into child values (1, 100), (4, 400);
+
+-- this case is optimizable
+select p.* from parent p left join child c on (p.k = c.k);
+explain (costs off)
+  select p.* from parent p left join child c on (p.k = c.k);
+
+-- this case is not
+select p.*, linked from parent p
+  left join (select c.*, true as linked from child c) as ss
+  on (p.k = ss.k);
+explain (costs off)
+  select p.*, linked from parent p
+    left join (select c.*, true as linked from child c) as ss
+    on (p.k = ss.k);
-- 
cgit v1.2.3