Undo mistaken tightening in join_is_legal().

One of the changes I made in commit 8703059c6b55c427 turns out not to have
been such a good idea: we still need the exception in join_is_legal() that
allows a join if both inputs already overlap the RHS of the special join
we're checking.  Otherwise we can miss valid plans, and might indeed fail
to find a plan at all, as in recent report from Andreas Seltenreich.

That code was added way back in commit c17117649b9ae23d, but I failed to
include a regression test case then; my bad.  Put it back with a better
explanation, and a test this time.  The logic does end up a bit different
than before though: I now believe it's appropriate to make this check
first, thereby allowing such a case whether or not we'd consider the
previous SJ(s) to commute with this one.  (Presumably, we already decided
they did; but it was confusing to have this consideration in the middle
of the code that was handling the other case.)

Back-patch to all active branches, like the previous patch.

2 files changed, 65 insertions, 0 deletions

diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out
index cd4713f5e17..eafcd406dc0 100644
--- a/src/test/regress/expected/join.out
+++ b/src/test/regress/expected/join.out
@@ -3563,6 +3563,52 @@ select t1.* from
  hi de ho neighbor
 (2 rows)
 
+explain (verbose, costs off)
+select * from
+  text_tbl t1
+  inner join int8_tbl i8
+  on i8.q2 = 456
+  right join text_tbl t2
+  on t1.f1 = 'doh!'
+  left join int4_tbl i4
+  on i8.q1 = i4.f1;
+                       QUERY PLAN                       
+--------------------------------------------------------
+ Nested Loop Left Join
+   Output: t1.f1, i8.q1, i8.q2, t2.f1, i4.f1
+   ->  Seq Scan on public.text_tbl t2
+         Output: t2.f1
+   ->  Materialize
+         Output: i8.q1, i8.q2, i4.f1, t1.f1
+         ->  Nested Loop
+               Output: i8.q1, i8.q2, i4.f1, t1.f1
+               ->  Nested Loop Left Join
+                     Output: i8.q1, i8.q2, i4.f1
+                     Join Filter: (i8.q1 = i4.f1)
+                     ->  Seq Scan on public.int8_tbl i8
+                           Output: i8.q1, i8.q2
+                           Filter: (i8.q2 = 456)
+                     ->  Seq Scan on public.int4_tbl i4
+                           Output: i4.f1
+               ->  Seq Scan on public.text_tbl t1
+                     Output: t1.f1
+                     Filter: (t1.f1 = 'doh!'::text)
+(19 rows)
+
+select * from
+  text_tbl t1
+  inner join int8_tbl i8
+  on i8.q2 = 456
+  right join text_tbl t2
+  on t1.f1 = 'doh!'
+  left join int4_tbl i4
+  on i8.q1 = i4.f1;
+  f1  | q1  | q2  |        f1         | f1 
+------+-----+-----+-------------------+----
+ doh! | 123 | 456 | doh!              |   
+ doh! | 123 | 456 | hi de ho neighbor |   
+(2 rows)
+
 --
 -- test ability to push constants through outer join clauses
 --
diff --git a/src/test/regress/sql/join.sql b/src/test/regress/sql/join.sql
index 2b9bd20bc6a..c9f34aa4e39 100644
--- a/src/test/regress/sql/join.sql
+++ b/src/test/regress/sql/join.sql
@@ -1108,6 +1108,25 @@ select t1.* from
   left join int4_tbl i4
   on (i8.q2 = i4.f1);
 
+explain (verbose, costs off)
+select * from
+  text_tbl t1
+  inner join int8_tbl i8
+  on i8.q2 = 456
+  right join text_tbl t2
+  on t1.f1 = 'doh!'
+  left join int4_tbl i4
+  on i8.q1 = i4.f1;
+
+select * from
+  text_tbl t1
+  inner join int8_tbl i8
+  on i8.q2 = 456
+  right join text_tbl t2
+  on t1.f1 = 'doh!'
+  left join int4_tbl i4
+  on i8.q1 = i4.f1;
+
 --
 -- test ability to push constants through outer join clauses
 --