2 files changed, 111 insertions, 0 deletions

diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out
index d14ab18eda1..edab441d20f 100644
--- a/src/test/regress/expected/join.out
+++ b/src/test/regress/expected/join.out
@@ -2235,6 +2235,86 @@ where b.f1 = t.thousand and a.f1 = b.f1 and (a.f1+b.f1+999) = t.tenthous;
 (0 rows)
 
 --
+-- check a case where we formerly got confused by conflicting sort orders
+-- in redundant merge join path keys
+--
+explain (costs off)
+select * from
+  j1_tbl full join
+  (select * from j2_tbl order by j2_tbl.i desc, j2_tbl.k asc) j2_tbl
+  on j1_tbl.i = j2_tbl.i and j1_tbl.i = j2_tbl.k;
+                           QUERY PLAN                            
+-----------------------------------------------------------------
+ Merge Full Join
+   Merge Cond: ((j2_tbl.i = j1_tbl.i) AND (j2_tbl.k = j1_tbl.i))
+   ->  Sort
+         Sort Key: j2_tbl.i, j2_tbl.k
+         ->  Seq Scan on j2_tbl
+   ->  Sort
+         Sort Key: j1_tbl.i
+         ->  Seq Scan on j1_tbl
+(8 rows)
+
+select * from
+  j1_tbl full join
+  (select * from j2_tbl order by j2_tbl.i desc, j2_tbl.k asc) j2_tbl
+  on j1_tbl.i = j2_tbl.i and j1_tbl.i = j2_tbl.k;
+ i | j |   t   | i | k  
+---+---+-------+---+----
+   |   |       |   |  0
+   |   |       |   |   
+   | 0 | zero  |   |   
+   |   | null  |   |   
+ 8 | 8 | eight |   |   
+ 7 | 7 | seven |   |   
+ 6 | 6 | six   |   |   
+   |   |       | 5 | -5
+   |   |       | 5 | -5
+ 5 | 0 | five  |   |   
+ 4 | 1 | four  |   |   
+   |   |       | 3 | -3
+ 3 | 2 | three |   |   
+ 2 | 3 | two   | 2 |  2
+   |   |       | 2 |  4
+   |   |       | 1 | -1
+   |   |       | 0 |   
+ 1 | 4 | one   |   |   
+ 0 |   | zero  |   |   
+(19 rows)
+
+--
+-- a different check for handling of redundant sort keys in merge joins
+--
+explain (costs off)
+select count(*) from
+  (select * from tenk1 x order by x.thousand, x.twothousand, x.fivethous) x
+  left join
+  (select * from tenk1 y order by y.unique2) y
+  on x.thousand = y.unique2 and x.twothousand = y.hundred and x.fivethous = y.unique2;
+                                    QUERY PLAN                                    
+----------------------------------------------------------------------------------
+ Aggregate
+   ->  Merge Left Join
+         Merge Cond: (x.thousand = y.unique2)
+         Join Filter: ((x.twothousand = y.hundred) AND (x.fivethous = y.unique2))
+         ->  Sort
+               Sort Key: x.thousand, x.twothousand, x.fivethous
+               ->  Seq Scan on tenk1 x
+         ->  Materialize
+               ->  Index Scan using tenk1_unique2 on tenk1 y
+(9 rows)
+
+select count(*) from
+  (select * from tenk1 x order by x.thousand, x.twothousand, x.fivethous) x
+  left join
+  (select * from tenk1 y order by y.unique2) y
+  on x.thousand = y.unique2 and x.twothousand = y.hundred and x.fivethous = y.unique2;
+ count 
+-------
+ 10000
+(1 row)
+
+--
 -- Clean up
 --
 DROP TABLE t1;
diff --git a/src/test/regress/sql/join.sql b/src/test/regress/sql/join.sql
index 5e69a478508..e9ec0e29e42 100644
--- a/src/test/regress/sql/join.sql
+++ b/src/test/regress/sql/join.sql
@@ -396,6 +396,37 @@ select a.f1, b.f1, t.thousand, t.tenthous from
   (select sum(f1) as f1 from int4_tbl i4b) b
 where b.f1 = t.thousand and a.f1 = b.f1 and (a.f1+b.f1+999) = t.tenthous;
 
+--
+-- check a case where we formerly got confused by conflicting sort orders
+-- in redundant merge join path keys
+--
+explain (costs off)
+select * from
+  j1_tbl full join
+  (select * from j2_tbl order by j2_tbl.i desc, j2_tbl.k asc) j2_tbl
+  on j1_tbl.i = j2_tbl.i and j1_tbl.i = j2_tbl.k;
+
+select * from
+  j1_tbl full join
+  (select * from j2_tbl order by j2_tbl.i desc, j2_tbl.k asc) j2_tbl
+  on j1_tbl.i = j2_tbl.i and j1_tbl.i = j2_tbl.k;
+
+--
+-- a different check for handling of redundant sort keys in merge joins
+--
+explain (costs off)
+select count(*) from
+  (select * from tenk1 x order by x.thousand, x.twothousand, x.fivethous) x
+  left join
+  (select * from tenk1 y order by y.unique2) y
+  on x.thousand = y.unique2 and x.twothousand = y.hundred and x.fivethous = y.unique2;
+
+select count(*) from
+  (select * from tenk1 x order by x.thousand, x.twothousand, x.fivethous) x
+  left join
+  (select * from tenk1 y order by y.unique2) y
+  on x.thousand = y.unique2 and x.twothousand = y.hundred and x.fivethous = y.unique2;
+
 
 --
 -- Clean up