The result of a FULL or RIGHT join can't be assumed to be sorted by the

left input's sorting, because null rows may be inserted at various points.
Per report from Ferenc Lutischá¸n.

1 files changed, 10 insertions, 1 deletions

diff --git a/src/backend/optimizer/path/pathkeys.c b/src/backend/optimizer/path/pathkeys.c
index e10a41b9518..d2c6de54dd7 100644
--- a/src/backend/optimizer/path/pathkeys.c
+++ b/src/backend/optimizer/path/pathkeys.c
@@ -11,7 +11,7 @@
  * Portions Copyright (c) 1994, Regents of the University of California
  *
  * IDENTIFICATION
- *	  $PostgreSQL: pgsql/src/backend/optimizer/path/pathkeys.c,v 1.63 2004/12/31 22:00:04 pgsql Exp $
+ *	  $PostgreSQL: pgsql/src/backend/optimizer/path/pathkeys.c,v 1.64 2005/01/23 02:21:26 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -858,7 +858,12 @@ build_subquery_pathkeys(Query *root, RelOptInfo *rel, Query *subquery)
  *	  vars they were joined with; furthermore, it doesn't matter what kind
  *	  of join algorithm is actually used.
  *
+ *	  EXCEPTION: in a FULL or RIGHT join, we cannot treat the result as
+ *	  having the outer path's path keys, because null lefthand rows may be
+ *	  inserted at random points.  It must be treated as unsorted.
+ *
  * 'joinrel' is the join relation that paths are being formed for
+ * 'jointype' is the join type (inner, left, full, etc)
  * 'outer_pathkeys' is the list of the current outer path's path keys
  *
  * Returns the list of new path keys.
@@ -866,8 +871,12 @@ build_subquery_pathkeys(Query *root, RelOptInfo *rel, Query *subquery)
 List *
 build_join_pathkeys(Query *root,
 					RelOptInfo *joinrel,
+					JoinType jointype,
 					List *outer_pathkeys)
 {
+	if (jointype == JOIN_FULL || jointype == JOIN_RIGHT)
+		return NIL;
+
 	/*
 	 * This used to be quite a complex bit of code, but now that all
 	 * pathkey sublists start out life canonicalized, we don't have to do