Second try at fixing join alias variables. Instead of attaching miscellaneous

lists to join RTEs, attach a list of Vars and COALESCE expressions that will
replace the join's alias variables during planning.  This simplifies
flatten_join_alias_vars while still making it easy to fix up varno references
when transforming the query tree.  Add regression test cases for interactions
of subqueries with outer joins.

21 files changed, 622 insertions, 651 deletions

diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c
index ac25cd52295..c461f147ef8 100644
--- a/src/backend/nodes/copyfuncs.c
+++ b/src/backend/nodes/copyfuncs.c
@@ -15,7 +15,7 @@
  * Portions Copyright (c) 1994, Regents of the University of California
  *
  * IDENTIFICATION
- *	  $Header: /cvsroot/pgsql/src/backend/nodes/copyfuncs.c,v 1.181 2002/04/24 02:48:54 momjian Exp $
+ *	  $Header: /cvsroot/pgsql/src/backend/nodes/copyfuncs.c,v 1.182 2002/04/28 19:54:28 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -323,7 +323,6 @@ CopyJoinFields(Join *from, Join *newnode)
 {
 	newnode->jointype = from->jointype;
 	Node_Copy(from, newnode, joinqual);
-	newnode->joinrti = from->joinrti;
 	/* subPlan list must point to subplans in the new subtree, not the old */
 	if (from->plan.subPlan != NIL)
 		newnode->plan.subPlan = nconc(newnode->plan.subPlan,
@@ -1475,10 +1474,7 @@ _copyRangeTblEntry(RangeTblEntry *from)
 	newnode->relid = from->relid;
 	Node_Copy(from, newnode, subquery);
 	newnode->jointype = from->jointype;
-	newnode->joincoltypes = listCopy(from->joincoltypes);
-	newnode->joincoltypmods = listCopy(from->joincoltypmods);
-	newnode->joinleftcols = listCopy(from->joinleftcols);
-	newnode->joinrightcols = listCopy(from->joinrightcols);
+	Node_Copy(from, newnode, joinaliasvars);
 	Node_Copy(from, newnode, alias);
 	Node_Copy(from, newnode, eref);
 	newnode->inh = from->inh;
diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c
index 3deb6207c3e..31ee46e0603 100644
--- a/src/backend/nodes/equalfuncs.c
+++ b/src/backend/nodes/equalfuncs.c
@@ -20,7 +20,7 @@
  * Portions Copyright (c) 1994, Regents of the University of California
  *
  * IDENTIFICATION
- *	  $Header: /cvsroot/pgsql/src/backend/nodes/equalfuncs.c,v 1.129 2002/04/24 02:48:54 momjian Exp $
+ *	  $Header: /cvsroot/pgsql/src/backend/nodes/equalfuncs.c,v 1.130 2002/04/28 19:54:28 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -1680,13 +1680,7 @@ _equalRangeTblEntry(RangeTblEntry *a, RangeTblEntry *b)
 		return false;
 	if (a->jointype != b->jointype)
 		return false;
-	if (!equali(a->joincoltypes, b->joincoltypes))
-		return false;
-	if (!equali(a->joincoltypmods, b->joincoltypmods))
-		return false;
-	if (!equali(a->joinleftcols, b->joinleftcols))
-		return false;
-	if (!equali(a->joinrightcols, b->joinrightcols))
+	if (!equal(a->joinaliasvars, b->joinaliasvars))
 		return false;
 	if (!equal(a->alias, b->alias))
 		return false;
diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.c
index 24238565d55..34264db11e9 100644
--- a/src/backend/nodes/outfuncs.c
+++ b/src/backend/nodes/outfuncs.c
@@ -5,7 +5,7 @@
  * Portions Copyright (c) 1996-2001, PostgreSQL Global Development Group
  * Portions Copyright (c) 1994, Regents of the University of California
  *
- *	$Header: /cvsroot/pgsql/src/backend/nodes/outfuncs.c,v 1.156 2002/04/17 20:57:56 tgl Exp $
+ *	$Header: /cvsroot/pgsql/src/backend/nodes/outfuncs.c,v 1.157 2002/04/28 19:54:28 tgl Exp $
  *
  * NOTES
  *	  Every (plan) node in POSTGRES has an associated "out" routine which
@@ -408,8 +408,6 @@ _outJoin(StringInfo str, Join *node)
 	appendStringInfo(str, " :jointype %d :joinqual ",
 					 (int) node->jointype);
 	_outNode(str, node->joinqual);
-	appendStringInfo(str, " :joinrti %d ",
-					 node->joinrti);
 }
 
 /*
@@ -423,8 +421,6 @@ _outNestLoop(StringInfo str, NestLoop *node)
 	appendStringInfo(str, " :jointype %d :joinqual ",
 					 (int) node->join.jointype);
 	_outNode(str, node->join.joinqual);
-	appendStringInfo(str, " :joinrti %d ",
-					 node->join.joinrti);
 }
 
 /*
@@ -438,8 +434,6 @@ _outMergeJoin(StringInfo str, MergeJoin *node)
 	appendStringInfo(str, " :jointype %d :joinqual ",
 					 (int) node->join.jointype);
 	_outNode(str, node->join.joinqual);
-	appendStringInfo(str, " :joinrti %d ",
-					 node->join.joinrti);
 
 	appendStringInfo(str, " :mergeclauses ");
 	_outNode(str, node->mergeclauses);
@@ -456,8 +450,6 @@ _outHashJoin(StringInfo str, HashJoin *node)
 	appendStringInfo(str, " :jointype %d :joinqual ",
 					 (int) node->join.jointype);
 	_outNode(str, node->join.joinqual);
-	appendStringInfo(str, " :joinrti %d ",
-					 node->join.joinrti);
 
 	appendStringInfo(str, " :hashclauses ");
 	_outNode(str, node->hashclauses);
@@ -982,22 +974,16 @@ _outRangeTblEntry(StringInfo str, RangeTblEntry *node)
 	{
 		case RTE_RELATION:
 		case RTE_SPECIAL:
-			appendStringInfo(str, ":relid %u ", node->relid);
+			appendStringInfo(str, ":relid %u", node->relid);
 			break;
 		case RTE_SUBQUERY:
 			appendStringInfo(str, ":subquery ");
 			_outNode(str, node->subquery);
 			break;
 		case RTE_JOIN:
-			appendStringInfo(str, ":jointype %d :joincoltypes ",
+			appendStringInfo(str, ":jointype %d :joinaliasvars ",
 							 (int) node->jointype);
-			_outOidList(str, node->joincoltypes);
-			appendStringInfo(str, " :joincoltypmods ");
-			_outIntList(str, node->joincoltypmods);
-			appendStringInfo(str, " :joinleftcols ");
-			_outIntList(str, node->joinleftcols);
-			appendStringInfo(str, " :joinrightcols ");
-			_outIntList(str, node->joinrightcols);
+			_outNode(str, node->joinaliasvars);
 			break;
 		default:
 			elog(ERROR, "bogus rte kind %d", (int) node->rtekind);
diff --git a/src/backend/nodes/readfuncs.c b/src/backend/nodes/readfuncs.c
index f91ba36e5dc..1d4236fc847 100644
--- a/src/backend/nodes/readfuncs.c
+++ b/src/backend/nodes/readfuncs.c
@@ -8,7 +8,7 @@
  *
  *
  * IDENTIFICATION
- *	  $Header: /cvsroot/pgsql/src/backend/nodes/readfuncs.c,v 1.119 2002/04/11 20:00:00 tgl Exp $
+ *	  $Header: /cvsroot/pgsql/src/backend/nodes/readfuncs.c,v 1.120 2002/04/28 19:54:28 tgl Exp $
  *
  * NOTES
  *	  Most of the read functions for plan nodes are tested. (In fact, they
@@ -421,10 +421,6 @@ _getJoin(Join *node)
 
 	token = pg_strtok(&length); /* skip the :joinqual */
 	node->joinqual = nodeRead(true);	/* get the joinqual */
-
-	token = pg_strtok(&length); /* skip the :joinrti */
-	token = pg_strtok(&length); /* get the joinrti */
-	node->joinrti = atoi(token);
 }
 
 
@@ -1523,17 +1519,8 @@ _readRangeTblEntry(void)
 			token = pg_strtok(&length); /* get jointype */
 			local_node->jointype = (JoinType) atoi(token);
 
-			token = pg_strtok(&length); /* eat :joincoltypes */
-			local_node->joincoltypes = toOidList(nodeRead(true));
-
-			token = pg_strtok(&length); /* eat :joincoltypmods */
-			local_node->joincoltypmods = toIntList(nodeRead(true));
-
-			token = pg_strtok(&length); /* eat :joinleftcols */
-			local_node->joinleftcols = toIntList(nodeRead(true));
-
-			token = pg_strtok(&length); /* eat :joinrightcols */
-			local_node->joinrightcols = toIntList(nodeRead(true));
+			token = pg_strtok(&length); /* eat :joinaliasvars */
+			local_node->joinaliasvars = nodeRead(true);	/* now read it */
 			break;
 
 		default:
diff --git a/src/backend/optimizer/plan/createplan.c b/src/backend/optimizer/plan/createplan.c
index 79d90bf5a3c..c20abacca39 100644
--- a/src/backend/optimizer/plan/createplan.c
+++ b/src/backend/optimizer/plan/createplan.c
@@ -10,7 +10,7 @@
  *
  *
  * IDENTIFICATION
- *	  $Header: /cvsroot/pgsql/src/backend/optimizer/plan/createplan.c,v 1.112 2002/03/12 00:51:45 tgl Exp $
+ *	  $Header: /cvsroot/pgsql/src/backend/optimizer/plan/createplan.c,v 1.113 2002/04/28 19:54:28 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -80,18 +80,18 @@ static TidScan *make_tidscan(List *qptlist, List *qpqual, Index scanrelid,
 static NestLoop *make_nestloop(List *tlist,
 			  List *joinclauses, List *otherclauses,
 			  Plan *lefttree, Plan *righttree,
-			  JoinType jointype, Index joinrti);
+			  JoinType jointype);
 static HashJoin *make_hashjoin(List *tlist,
 			  List *joinclauses, List *otherclauses,
 			  List *hashclauses,
 			  Plan *lefttree, Plan *righttree,
-			  JoinType jointype, Index joinrti);
+			  JoinType jointype);
 static Hash *make_hash(List *tlist, Node *hashkey, Plan *lefttree);
 static MergeJoin *make_mergejoin(List *tlist,
 			   List *joinclauses, List *otherclauses,
 			   List *mergeclauses,
 			   Plan *lefttree, Plan *righttree,
-			   JoinType jointype, Index joinrti);
+			   JoinType jointype);
 
 /*
  * create_plan
@@ -591,7 +591,6 @@ create_nestloop_plan(Query *root,
 					 List *inner_tlist)
 {
 	NestLoop   *join_plan;
-	Index		joinrti = best_path->path.parent->joinrti;
 
 	if (IsA(inner_plan, IndexScan))
 	{
@@ -639,22 +638,19 @@ create_nestloop_plan(Query *root,
 													  root,
 													  outer_tlist,
 													  NIL,
-													  innerrel,
-													  joinrti);
+													  innerrel);
 			innerscan->indxqual = join_references(innerscan->indxqual,
 												  root,
 												  outer_tlist,
 												  NIL,
-												  innerrel,
-												  joinrti);
+												  innerrel);
 			/* fix the inner qpqual too, if it has join clauses */
 			if (NumRelids((Node *) inner_plan->qual) > 1)
 				inner_plan->qual = join_references(inner_plan->qual,
 												   root,
 												   outer_tlist,
 												   NIL,
-												   innerrel,
-												   joinrti);
+												   innerrel);
 		}
 	}
 	else if (IsA(inner_plan, TidScan))
@@ -665,8 +661,7 @@ create_nestloop_plan(Query *root,
 											 root,
 											 outer_tlist,
 											 inner_tlist,
-											 innerscan->scan.scanrelid,
-											 joinrti);
+											 innerscan->scan.scanrelid);
 	}
 	else if (IsA_Join(inner_plan))
 	{
@@ -688,22 +683,19 @@ create_nestloop_plan(Query *root,
 								  root,
 								  outer_tlist,
 								  inner_tlist,
-								  (Index) 0,
-								  joinrti);
+								  (Index) 0);
 	otherclauses = join_references(otherclauses,
 								   root,
 								   outer_tlist,
 								   inner_tlist,
-								   (Index) 0,
-								   joinrti);
+								   (Index) 0);
 
 	join_plan = make_nestloop(tlist,
 							  joinclauses,
 							  otherclauses,
 							  outer_plan,
 							  inner_plan,
-							  best_path->jointype,
-							  joinrti);
+							  best_path->jointype);
 
 	copy_path_costsize(&join_plan->join.plan, &best_path->path);
 
@@ -723,7 +715,6 @@ create_mergejoin_plan(Query *root,
 {
 	List	   *mergeclauses;
 	MergeJoin  *join_plan;
-	Index		joinrti = best_path->jpath.path.parent->joinrti;
 
 	mergeclauses = get_actual_clauses(best_path->path_mergeclauses);
 
@@ -736,8 +727,7 @@ create_mergejoin_plan(Query *root,
 								  root,
 								  outer_tlist,
 								  inner_tlist,
-								  (Index) 0,
-								  joinrti);
+								  (Index) 0);
 
 	/*
 	 * Fix the additional qpquals too.
@@ -746,8 +736,7 @@ create_mergejoin_plan(Query *root,
 								   root,
 								   outer_tlist,
 								   inner_tlist,
-								   (Index) 0,
-								   joinrti);
+								   (Index) 0);
 
 	/*
 	 * Now set the references in the mergeclauses and rearrange them so
@@ -757,8 +746,7 @@ create_mergejoin_plan(Query *root,
 												root,
 												outer_tlist,
 												inner_tlist,
-												(Index) 0,
-												joinrti));
+												(Index) 0));
 
 	/*
 	 * Create explicit sort nodes for the outer and inner join paths if
@@ -824,8 +812,7 @@ create_mergejoin_plan(Query *root,
 							   mergeclauses,
 							   outer_plan,
 							   inner_plan,
-							   best_path->jpath.jointype,
-							   joinrti);
+							   best_path->jpath.jointype);
 
 	copy_path_costsize(&join_plan->join.plan, &best_path->jpath.path);
 
@@ -847,7 +834,6 @@ create_hashjoin_plan(Query *root,
 	HashJoin   *join_plan;
 	Hash	   *hash_plan;
 	Node	   *innerhashkey;
-	Index		joinrti = best_path->jpath.path.parent->joinrti;
 
 	/*
 	 * NOTE: there will always be exactly one hashclause in the list
@@ -866,8 +852,7 @@ create_hashjoin_plan(Query *root,
 								  root,
 								  outer_tlist,
 								  inner_tlist,
-								  (Index) 0,
-								  joinrti);
+								  (Index) 0);
 
 	/*
 	 * Fix the additional qpquals too.
@@ -876,8 +861,7 @@ create_hashjoin_plan(Query *root,
 								   root,
 								   outer_tlist,
 								   inner_tlist,
-								   (Index) 0,
-								   joinrti);
+								   (Index) 0);
 
 	/*
 	 * Now set the references in the hashclauses and rearrange them so
@@ -887,8 +871,7 @@ create_hashjoin_plan(Query *root,
 											   root,
 											   outer_tlist,
 											   inner_tlist,
-											   (Index) 0,
-											   joinrti));
+											   (Index) 0));
 
 	/* Now the righthand op of the sole hashclause is the inner hash key. */
 	innerhashkey = (Node *) get_rightop(lfirst(hashclauses));
@@ -903,8 +886,7 @@ create_hashjoin_plan(Query *root,
 							  hashclauses,
 							  outer_plan,
 							  (Plan *) hash_plan,
-							  best_path->jpath.jointype,
-							  joinrti);
+							  best_path->jpath.jointype);
 
 	copy_path_costsize(&join_plan->join.plan, &best_path->jpath.path);
 
@@ -1363,8 +1345,7 @@ make_nestloop(List *tlist,
 			  List *otherclauses,
 			  Plan *lefttree,
 			  Plan *righttree,
-			  JoinType jointype,
-			  Index joinrti)
+			  JoinType jointype)
 {
 	NestLoop   *node = makeNode(NestLoop);
 	Plan	   *plan = &node->join.plan;
@@ -1377,7 +1358,6 @@ make_nestloop(List *tlist,
 	plan->righttree = righttree;
 	node->join.jointype = jointype;
 	node->join.joinqual = joinclauses;
-	node->join.joinrti = joinrti;
 
 	return node;
 }
@@ -1389,8 +1369,7 @@ make_hashjoin(List *tlist,
 			  List *hashclauses,
 			  Plan *lefttree,
 			  Plan *righttree,
-			  JoinType jointype,
-			  Index joinrti)
+			  JoinType jointype)
 {
 	HashJoin   *node = makeNode(HashJoin);
 	Plan	   *plan = &node->join.plan;
@@ -1404,7 +1383,6 @@ make_hashjoin(List *tlist,
 	node->hashclauses = hashclauses;
 	node->join.jointype = jointype;
 	node->join.joinqual = joinclauses;
-	node->join.joinrti = joinrti;
 
 	return node;
 }
@@ -1439,8 +1417,7 @@ make_mergejoin(List *tlist,
 			   List *mergeclauses,
 			   Plan *lefttree,
 			   Plan *righttree,
-			   JoinType jointype,
-			   Index joinrti)
+			   JoinType jointype)
 {
 	MergeJoin  *node = makeNode(MergeJoin);
 	Plan	   *plan = &node->join.plan;
@@ -1454,7 +1431,6 @@ make_mergejoin(List *tlist,
 	node->mergeclauses = mergeclauses;
 	node->join.jointype = jointype;
 	node->join.joinqual = joinclauses;
-	node->join.joinrti = joinrti;
 
 	return node;
 }
diff --git a/src/backend/optimizer/plan/initsplan.c b/src/backend/optimizer/plan/initsplan.c
index 39ac5ba8866..2410651d683 100644
--- a/src/backend/optimizer/plan/initsplan.c
+++ b/src/backend/optimizer/plan/initsplan.c
@@ -8,7 +8,7 @@
  *
  *
  * IDENTIFICATION
- *	  $Header: /cvsroot/pgsql/src/backend/optimizer/plan/initsplan.c,v 1.68 2002/04/16 23:08:10 tgl Exp $
+ *	  $Header: /cvsroot/pgsql/src/backend/optimizer/plan/initsplan.c,v 1.69 2002/04/28 19:54:28 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -173,16 +173,14 @@ add_vars_to_targetlist(Query *root, List *vars)
 		if (rel->reloptkind == RELOPT_OTHER_JOIN_REL)
 		{
 			/* Var is an alias */
-			Var	   *leftsubvar,
-				   *rightsubvar;
-
-			build_join_alias_subvars(root, var,
-									 &leftsubvar, &rightsubvar);
-
-			rel = find_base_rel(root, leftsubvar->varno);
-			add_var_to_tlist(rel, leftsubvar);
-			rel = find_base_rel(root, rightsubvar->varno);
-			add_var_to_tlist(rel, rightsubvar);
+			Node   *expansion;
+			List   *varsused;
+
+			expansion = flatten_join_alias_vars((Node *) var,
+												root, true);
+			varsused = pull_var_clause(expansion, false);
+			add_vars_to_targetlist(root, varsused);
+			freeList(varsused);
 		}
 	}
 }
diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c
index 1df2cd29402..5eb17f3b05c 100644
--- a/src/backend/optimizer/plan/planner.c
+++ b/src/backend/optimizer/plan/planner.c
@@ -8,7 +8,7 @@
  *
  *
  * IDENTIFICATION
- *	  $Header: /cvsroot/pgsql/src/backend/optimizer/plan/planner.c,v 1.115 2002/03/12 00:51:47 tgl Exp $
+ *	  $Header: /cvsroot/pgsql/src/backend/optimizer/plan/planner.c,v 1.116 2002/04/28 19:54:28 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -41,8 +41,10 @@
 #define EXPRKIND_HAVING 2
 
 
-static Node *pull_up_subqueries(Query *parse, Node *jtnode);
+static Node *pull_up_subqueries(Query *parse, Node *jtnode,
+								bool below_outer_join);
 static bool is_simple_subquery(Query *subquery);
+static bool has_nullable_targetlist(Query *subquery);
 static void resolvenew_in_jointree(Node *jtnode, int varno, List *subtlist);
 static Node *preprocess_jointree(Query *parse, Node *jtnode);
 static Node *preprocess_expression(Query *parse, Node *expr, int kind);
@@ -153,7 +155,7 @@ subquery_planner(Query *parse, double tuple_fraction)
 	 * this query.
 	 */
 	parse->jointree = (FromExpr *)
-		pull_up_subqueries(parse, (Node *) parse->jointree);
+		pull_up_subqueries(parse, (Node *) parse->jointree, false);
 
 	/*
 	 * If so, we may have created opportunities to simplify the jointree.
@@ -264,6 +266,9 @@ subquery_planner(Query *parse, double tuple_fraction)
  *		the parent query.  If the subquery has no special features like
  *		grouping/aggregation then we can merge it into the parent's jointree.
  *
+ * below_outer_join is true if this jointree node is within the nullable
+ * side of an outer join.  This restricts what we can do.
+ *
  * A tricky aspect of this code is that if we pull up a subquery we have
  * to replace Vars that reference the subquery's outputs throughout the
  * parent query, including quals attached to jointree nodes above the one
@@ -274,7 +279,7 @@ subquery_planner(Query *parse, double tuple_fraction)
  * copy of the tree; we have to invoke it just on the quals, instead.
  */
 static Node *
-pull_up_subqueries(Query *parse, Node *jtnode)
+pull_up_subqueries(Query *parse, Node *jtnode, bool below_outer_join)
 {
 	if (jtnode == NULL)
 		return NULL;
@@ -288,16 +293,26 @@ pull_up_subqueries(Query *parse, Node *jtnode)
 		 * Is this a subquery RTE, and if so, is the subquery simple
 		 * enough to pull up?  (If not, do nothing at this node.)
 		 *
+		 * If we are inside an outer join, only pull up subqueries whose
+		 * targetlists are nullable --- otherwise substituting their tlist
+		 * entries for upper Var references would do the wrong thing
+		 * (the results wouldn't become NULL when they're supposed to).
+		 * XXX This could be improved by generating pseudo-variables for
+		 * such expressions; we'd have to figure out how to get the pseudo-
+		 * variables evaluated at the right place in the modified plan tree.
+		 * Fix it someday.
+		 *
 		 * Note: even if the subquery itself is simple enough, we can't pull
-		 * it up if there is a reference to its whole tuple result.
+		 * it up if there is a reference to its whole tuple result.  Perhaps
+		 * a pseudo-variable is the answer here too.
 		 */
-		if (subquery && is_simple_subquery(subquery) &&
+		if (rte->rtekind == RTE_SUBQUERY && is_simple_subquery(subquery) &&
+			(!below_outer_join || has_nullable_targetlist(subquery)) &&
 			!contain_whole_tuple_var((Node *) parse, varno, 0))
 		{
 			int			rtoffset;
-			Node	   *subjointree;
 			List	   *subtlist;
-			List	   *l;
+			List	   *rt;
 
 			/*
 			 * First, recursively pull up the subquery's subqueries, so
@@ -311,50 +326,62 @@ pull_up_subqueries(Query *parse, Node *jtnode)
 			 * having chunks of structure multiply linked.
 			 */
 			subquery->jointree = (FromExpr *)
-				pull_up_subqueries(subquery, (Node *) subquery->jointree);
+				pull_up_subqueries(subquery, (Node *) subquery->jointree,
+								   below_outer_join);
 
 			/*
-			 * Append the subquery's rangetable to mine (currently, no
-			 * adjustments will be needed in the subquery's rtable).
+			 * Now make a modifiable copy of the subquery that we can
+			 * run OffsetVarNodes on.
 			 */
-			rtoffset = length(parse->rtable);
-			parse->rtable = nconc(parse->rtable,
-								  copyObject(subquery->rtable));
+			subquery = copyObject(subquery);
 
 			/*
-			 * Make copies of the subquery's jointree and targetlist with
-			 * varnos adjusted to match the merged rangetable.
+			 * Adjust varnos in subquery so that we can append its
+			 * rangetable to upper query's.
 			 */
-			subjointree = copyObject(subquery->jointree);
-			OffsetVarNodes(subjointree, rtoffset, 0);
-			subtlist = copyObject(subquery->targetList);
-			OffsetVarNodes((Node *) subtlist, rtoffset, 0);
+			rtoffset = length(parse->rtable);
+			OffsetVarNodes((Node *) subquery, rtoffset, 0);
 
 			/*
 			 * Replace all of the top query's references to the subquery's
 			 * outputs with copies of the adjusted subtlist items, being
 			 * careful not to replace any of the jointree structure.
+			 * (This'd be a lot cleaner if we could use query_tree_mutator.)
 			 */
+			subtlist = subquery->targetList;
 			parse->targetList = (List *)
 				ResolveNew((Node *) parse->targetList,
 						   varno, 0, subtlist, CMD_SELECT, 0);
 			resolvenew_in_jointree((Node *) parse->jointree, varno, subtlist);
+			Assert(parse->setOperations == NULL);
 			parse->havingQual =
 				ResolveNew(parse->havingQual,
 						   varno, 0, subtlist, CMD_SELECT, 0);
 
-			/*
-			 * Pull up any FOR UPDATE markers, too.
-			 */
-			foreach(l, subquery->rowMarks)
+			foreach(rt, parse->rtable)
 			{
-				int			submark = lfirsti(l);
+				RangeTblEntry *rte = (RangeTblEntry *) lfirst(rt);
 
-				parse->rowMarks = lappendi(parse->rowMarks,
-										   submark + rtoffset);
+				if (rte->rtekind == RTE_JOIN)
+					rte->joinaliasvars = (List *)
+						ResolveNew((Node *) rte->joinaliasvars,
+								   varno, 0, subtlist, CMD_SELECT, 0);
 			}
 
 			/*
+			 * Now append the adjusted rtable entries to upper query.
+			 * (We hold off until after fixing the upper rtable entries;
+			 * no point in running that code on the subquery ones too.)
+			 */
+			parse->rtable = nconc(parse->rtable, subquery->rtable);
+
+			/*
+			 * Pull up any FOR UPDATE markers, too.  (OffsetVarNodes
+			 * already adjusted the marker values, so just nconc the list.)
+			 */
+			parse->rowMarks = nconc(parse->rowMarks, subquery->rowMarks);
+
+			/*
 			 * Miscellaneous housekeeping.
 			 */
 			parse->hasSubLinks |= subquery->hasSubLinks;
@@ -364,7 +391,7 @@ pull_up_subqueries(Query *parse, Node *jtnode)
 			 * Return the adjusted subquery jointree to replace the
 			 * RangeTblRef entry in my jointree.
 			 */
-			return subjointree;
+			return (Node *) subquery->jointree;
 		}
 	}
 	else if (IsA(jtnode, FromExpr))
@@ -373,35 +400,39 @@ pull_up_subqueries(Query *parse, Node *jtnode)
 		List	   *l;
 
 		foreach(l, f->fromlist)
-			lfirst(l) = pull_up_subqueries(parse, lfirst(l));
+			lfirst(l) = pull_up_subqueries(parse, lfirst(l),
+										   below_outer_join);
 	}
 	else if (IsA(jtnode, JoinExpr))
 	{
 		JoinExpr   *j = (JoinExpr *) jtnode;
 
-		/*
-		 * At the moment, we can't pull up subqueries that are inside the
-		 * nullable side of an outer join, because substituting their
-		 * target list entries for upper Var references wouldn't do the
-		 * right thing (the entries wouldn't go to NULL when they're
-		 * supposed to). Suppressing the pullup is an ugly,
-		 * performance-losing hack, but I see no alternative for now. Find
-		 * a better way to handle this when we redesign query trees ---
-		 * tgl 4/30/01.
-		 */
+		/* Recurse, being careful to tell myself when inside outer join */
 		switch (j->jointype)
 		{
 			case JOIN_INNER:
-				j->larg = pull_up_subqueries(parse, j->larg);
-				j->rarg = pull_up_subqueries(parse, j->rarg);
+				j->larg = pull_up_subqueries(parse, j->larg,
+											 below_outer_join);
+				j->rarg = pull_up_subqueries(parse, j->rarg,
+											 below_outer_join);
 				break;
 			case JOIN_LEFT:
-				j->larg = pull_up_subqueries(parse, j->larg);
+				j->larg = pull_up_subqueries(parse, j->larg,
+											 below_outer_join);
+				j->rarg = pull_up_subqueries(parse, j->rarg,
+											 true);
 				break;
 			case JOIN_FULL:
+				j->larg = pull_up_subqueries(parse, j->larg,
+											 true);
+				j->rarg = pull_up_subqueries(parse, j->rarg,
+											 true);
 				break;
 			case JOIN_RIGHT:
-				j->rarg = pull_up_subqueries(parse, j->rarg);
+				j->larg = pull_up_subqueries(parse, j->larg,
+											 true);
+				j->rarg = pull_up_subqueries(parse, j->rarg,
+											 below_outer_join);
 				break;
 			case JOIN_UNION:
 
@@ -485,6 +516,37 @@ is_simple_subquery(Query *subquery)
 }
 
 /*
+ * has_nullable_targetlist
+ *	  Check a subquery in the range table to see if all the non-junk
+ *	  targetlist items are simple variables (and, hence, will correctly
+ *	  go to NULL when examined above the point of an outer join).
+ *
+ * A possible future extension is to accept strict functions of simple
+ * variables, eg, "x + 1".
+ */
+static bool
+has_nullable_targetlist(Query *subquery)
+{
+	List	   *l;
+
+	foreach(l, subquery->targetList)
+	{
+		TargetEntry *tle = (TargetEntry *) lfirst(l);
+
+		/* ignore resjunk columns */
+		if (tle->resdom->resjunk)
+			continue;
+
+		/* Okay if tlist item is a simple Var */
+		if (tle->expr && IsA(tle->expr, Var))
+			continue;
+
+		return false;
+	}
+	return true;
+}
+
+/*
  * Helper routine for pull_up_subqueries: do ResolveNew on every expression
  * in the jointree, without changing the jointree structure itself.  Ugly,
  * but there's no other way...
@@ -675,7 +737,7 @@ preprocess_expression(Query *parse, Node *expr, int kind)
 		}
 	}
 	if (has_join_rtes)
-		expr = flatten_join_alias_vars(expr, parse, 0);
+		expr = flatten_join_alias_vars(expr, parse, false);
 
 	return expr;
 }
diff --git a/src/backend/optimizer/plan/setrefs.c b/src/backend/optimizer/plan/setrefs.c
index 2f48821ece6..807876e4a72 100644
--- a/src/backend/optimizer/plan/setrefs.c
+++ b/src/backend/optimizer/plan/setrefs.c
@@ -9,7 +9,7 @@
  *
  *
  * IDENTIFICATION
- *	  $Header: /cvsroot/pgsql/src/backend/optimizer/plan/setrefs.c,v 1.74 2002/03/12 00:51:48 tgl Exp $
+ *	  $Header: /cvsroot/pgsql/src/backend/optimizer/plan/setrefs.c,v 1.75 2002/04/28 19:54:28 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -31,7 +31,6 @@ typedef struct
 	List	   *outer_tlist;
 	List	   *inner_tlist;
 	Index		acceptable_rel;
-	Index		join_rti;
 } join_references_context;
 
 typedef struct
@@ -271,8 +270,7 @@ set_join_references(Query *root, Join *join)
 											root,
 											outer_tlist,
 											inner_tlist,
-											(Index) 0,
-											join->joinrti);
+											(Index) 0);
 }
 
 /*
@@ -367,8 +365,6 @@ set_uppernode_references(Plan *plan, Index subvarno)
  * 'inner_tlist' is the target list of the inner join relation, or NIL
  * 'acceptable_rel' is either zero or the rangetable index of a relation
  *		whose Vars may appear in the clause without provoking an error.
- * 'join_rti' is either zero or the join RTE index of join alias variables
- *		that should be expanded.
  *
  * Returns the new expression tree.  The original clause structure is
  * not modified.
@@ -378,8 +374,7 @@ join_references(List *clauses,
 				Query *root,
 				List *outer_tlist,
 				List *inner_tlist,
-				Index acceptable_rel,
-				Index join_rti)
+				Index acceptable_rel)
 {
 	join_references_context context;
 
@@ -387,7 +382,6 @@ join_references(List *clauses,
 	context.outer_tlist = outer_tlist;
 	context.inner_tlist = inner_tlist;
 	context.acceptable_rel = acceptable_rel;
-	context.join_rti = join_rti;
 	return (List *) join_references_mutator((Node *) clauses, &context);
 }
 
@@ -401,6 +395,7 @@ join_references_mutator(Node *node,
 	{
 		Var		   *var = (Var *) node;
 		Resdom	   *resdom;
+		Node	   *newnode;
 
 		/* First look for the var in the input tlists */
 		resdom = tlist_member((Node *) var, context->outer_tlist);
@@ -423,13 +418,11 @@ join_references_mutator(Node *node,
 		}
 
 		/* Perhaps it's a join alias that can be resolved to input vars? */
-		if (var->varno == context->join_rti)
+		newnode = flatten_join_alias_vars((Node *) var,
+										  context->root,
+										  true);
+		if (!equal(newnode, (Node *) var))
 		{
-			Node   *newnode;
-
-			newnode = flatten_join_alias_vars((Node *) var,
-											  context->root,
-											  context->join_rti);
 			/* Must now resolve the input vars... */
 			newnode = join_references_mutator(newnode, context);
 			return newnode;
diff --git a/src/backend/optimizer/util/clauses.c b/src/backend/optimizer/util/clauses.c
index d7f100cb353..1b7cc14e2e7 100644
--- a/src/backend/optimizer/util/clauses.c
+++ b/src/backend/optimizer/util/clauses.c
@@ -8,7 +8,7 @@
  *
  *
  * IDENTIFICATION
- *	  $Header: /cvsroot/pgsql/src/backend/optimizer/util/clauses.c,v 1.96 2002/04/05 00:31:27 tgl Exp $
+ *	  $Header: /cvsroot/pgsql/src/backend/optimizer/util/clauses.c,v 1.97 2002/04/28 19:54:28 tgl Exp $
  *
  * HISTORY
  *	  AUTHOR			DATE			MAJOR EVENT
@@ -1902,6 +1902,8 @@ query_tree_walker(Query *query,
 				  void *context,
 				  bool visitQueryRTEs)
 {
+	List	   *rt;
+
 	Assert(query != NULL && IsA(query, Query));
 
 	if (walker((Node *) query->targetList, context))
@@ -1912,17 +1914,25 @@ query_tree_walker(Query *query,
 		return true;
 	if (walker(query->havingQual, context))
 		return true;
-	if (visitQueryRTEs)
+	foreach(rt, query->rtable)
 	{
-		List	   *rt;
+		RangeTblEntry *rte = (RangeTblEntry *) lfirst(rt);
 
-		foreach(rt, query->rtable)
+		switch (rte->rtekind)
 		{
-			RangeTblEntry *rte = (RangeTblEntry *) lfirst(rt);
-
-			if (rte->subquery)
-				if (walker(rte->subquery, context))
+			case RTE_RELATION:
+			case RTE_SPECIAL:
+				/* nothing to do */
+				break;
+			case RTE_SUBQUERY:
+				if (visitQueryRTEs)
+					if (walker(rte->subquery, context))
+						return true;
+				break;
+			case RTE_JOIN:
+				if (walker(rte->joinaliasvars, context))
 					return true;
+				break;
 		}
 	}
 	return false;
@@ -2281,32 +2291,42 @@ query_tree_mutator(Query *query,
 				   void *context,
 				   bool visitQueryRTEs)
 {
+	List	   *newrt = NIL;
+	List	   *rt;
+
 	Assert(query != NULL && IsA(query, Query));
 
 	MUTATE(query->targetList, query->targetList, List *);
 	MUTATE(query->jointree, query->jointree, FromExpr *);
 	MUTATE(query->setOperations, query->setOperations, Node *);
 	MUTATE(query->havingQual, query->havingQual, Node *);
-	if (visitQueryRTEs)
+	foreach(rt, query->rtable)
 	{
-		List	   *newrt = NIL;
-		List	   *rt;
+		RangeTblEntry *rte = (RangeTblEntry *) lfirst(rt);
+		RangeTblEntry *newrte;
 
-		foreach(rt, query->rtable)
+		switch (rte->rtekind)
 		{
-			RangeTblEntry *rte = (RangeTblEntry *) lfirst(rt);
-
-			if (rte->subquery)
-			{
-				RangeTblEntry *newrte;
-
+			case RTE_RELATION:
+			case RTE_SPECIAL:
+				/* nothing to do, don't bother to make a copy */
+				break;
+			case RTE_SUBQUERY:
+				if (visitQueryRTEs)
+				{
+					FLATCOPY(newrte, rte, RangeTblEntry);
+					CHECKFLATCOPY(newrte->subquery, rte->subquery, Query);
+					MUTATE(newrte->subquery, newrte->subquery, Query *);
+					rte = newrte;
+				}
+				break;
+			case RTE_JOIN:
 				FLATCOPY(newrte, rte, RangeTblEntry);
-				CHECKFLATCOPY(newrte->subquery, rte->subquery, Query);
-				MUTATE(newrte->subquery, newrte->subquery, Query *);
+				MUTATE(newrte->joinaliasvars, rte->joinaliasvars, List *);
 				rte = newrte;
-			}
-			newrt = lappend(newrt, rte);
+				break;
 		}
-		query->rtable = newrt;
+		newrt = lappend(newrt, rte);
 	}
+	query->rtable = newrt;
 }
diff --git a/src/backend/optimizer/util/var.c b/src/backend/optimizer/util/var.c
index 81aad81c0f3..ee861c3557e 100644
--- a/src/backend/optimizer/util/var.c
+++ b/src/backend/optimizer/util/var.c
@@ -8,18 +8,16 @@
  *
  *
  * IDENTIFICATION
- *	  $Header: /cvsroot/pgsql/src/backend/optimizer/util/var.c,v 1.35 2002/04/11 20:00:00 tgl Exp $
+ *	  $Header: /cvsroot/pgsql/src/backend/optimizer/util/var.c,v 1.36 2002/04/28 19:54:28 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
 #include "postgres.h"
 
-#include "nodes/makefuncs.h"
 #include "nodes/plannodes.h"
 #include "optimizer/clauses.h"
 #include "optimizer/var.h"
 #include "parser/parsetree.h"
-#include "parser/parse_coerce.h"
 
 
 typedef struct
@@ -44,7 +42,7 @@ typedef struct
 typedef struct
 {
 	Query	   *root;
-	int			expandRTI;
+	bool		force;
 } flatten_join_alias_vars_context;
 
 static bool pull_varnos_walker(Node *node,
@@ -56,8 +54,6 @@ static bool pull_var_clause_walker(Node *node,
 					   pull_var_clause_context *context);
 static Node *flatten_join_alias_vars_mutator(Node *node,
 						flatten_join_alias_vars_context *context);
-static Node *flatten_join_alias_var(Var *var, Query *root, int expandRTI);
-static Node *find_jointree_item(Node *jtnode, int rtindex);
 
 
 /*
@@ -314,26 +310,30 @@ pull_var_clause_walker(Node *node, pull_var_clause_context *context)
 
 /*
  * flatten_join_alias_vars
- *	  Whereever possible, replace Vars that reference JOIN outputs with
- *	  references to the original relation variables instead.  This allows
- *	  quals involving such vars to be pushed down.  Vars that cannot be
- *	  simplified to non-join Vars are replaced by COALESCE expressions
- *	  if they have varno = expandRTI, and are left as JOIN RTE references
- *	  otherwise.  (Pass expandRTI = 0 to prevent all COALESCE expansion.)
+ *	  Replace Vars that reference JOIN outputs with references to the original
+ *	  relation variables instead.  This allows quals involving such vars to be
+ *	  pushed down.
  *
- *	  Upper-level vars (with varlevelsup > 0) are ignored; normally there
- *	  should not be any by the time this routine is called.
+ * If force is TRUE then we will reduce all JOIN alias Vars to non-alias Vars
+ * or expressions thereof (there may be COALESCE and/or type conversions
+ * involved).  If force is FALSE we will not expand a Var to a non-Var
+ * expression.  This is a hack to avoid confusing mergejoin planning, which
+ * currently cannot cope with non-Var join items --- we leave the join vars
+ * as Vars till after planning is done, then expand them during setrefs.c.
+ *
+ * Upper-level vars (with varlevelsup > 0) are ignored; normally there
+ * should not be any by the time this routine is called.
  *
  * Does not examine subqueries, therefore must only be used after reduction
  * of sublinks to subplans!
  */
 Node *
-flatten_join_alias_vars(Node *node, Query *root, int expandRTI)
+flatten_join_alias_vars(Node *node, Query *root, bool force)
 {
 	flatten_join_alias_vars_context context;
 
 	context.root = root;
-	context.expandRTI = expandRTI;
+	context.force = force;
 
 	return flatten_join_alias_vars_mutator(node, &context);
 }
@@ -347,326 +347,24 @@ flatten_join_alias_vars_mutator(Node *node,
 	if (IsA(node, Var))
 	{
 		Var	   *var = (Var *) node;
+		RangeTblEntry *rte;
+		Node   *newvar;
 
 		if (var->varlevelsup != 0)
 			return node;		/* no need to copy, really */
-		return flatten_join_alias_var(var, context->root, context->expandRTI);
-	}
-	return expression_tree_mutator(node, flatten_join_alias_vars_mutator,
-								   (void *) context);
-}
-
-static Node *
-flatten_join_alias_var(Var *var, Query *root, int expandRTI)
-{
-	Index		varno = var->varno;
-	AttrNumber	varattno = var->varattno;
-	Oid			vartype = var->vartype;
-	int32		vartypmod = var->vartypmod;
-	JoinExpr   *jexpr = NULL;
-
-	/*
-	 * Loop to cope with joins of joins
-	 */
-	for (;;)
-	{
-		RangeTblEntry *rte = rt_fetch(varno, root->rtable);
-		Index		leftrti,
-					rightrti;
-		AttrNumber	leftattno,
-					rightattno;
-		RangeTblEntry *subrte;
-		Oid			subtype;
-		int32		subtypmod;
-
+		rte = rt_fetch(var->varno, context->root->rtable);
 		if (rte->rtekind != RTE_JOIN)
-			break;				/* reached a non-join RTE */
-		/*
-		 * Find the RT indexes of the left and right children of the
-		 * join node.  We have to search the join tree to do this,
-		 * which is a major pain in the neck --- but keeping RT indexes
-		 * in other RT entries is worse, because it makes modifying
-		 * querytrees difficult.  (Perhaps we can improve on the
-		 * rangetable/jointree datastructure someday.)  One thing we
-		 * can do is avoid repeated searches while tracing a single
-		 * variable down to its baserel.
-		 */
-		if (jexpr == NULL)
-			jexpr = (JoinExpr *)
-				find_jointree_item((Node *) root->jointree, varno);
-		if (jexpr == NULL ||
-			!IsA(jexpr, JoinExpr) ||
-			jexpr->rtindex != varno)
-			elog(ERROR, "flatten_join_alias_var: failed to find JoinExpr");
-		if (IsA(jexpr->larg, RangeTblRef))
-			leftrti = ((RangeTblRef *) jexpr->larg)->rtindex;
-		else if (IsA(jexpr->larg, JoinExpr))
-			leftrti = ((JoinExpr *) jexpr->larg)->rtindex;
-		else
-		{
-			elog(ERROR, "flatten_join_alias_var: unexpected subtree type");
-			leftrti = 0;		/* keep compiler quiet */
-		}
-		if (IsA(jexpr->rarg, RangeTblRef))
-			rightrti = ((RangeTblRef *) jexpr->rarg)->rtindex;
-		else if (IsA(jexpr->rarg, JoinExpr))
-			rightrti = ((JoinExpr *) jexpr->rarg)->rtindex;
-		else
-		{
-			elog(ERROR, "flatten_join_alias_var: unexpected subtree type");
-			rightrti = 0;		/* keep compiler quiet */
-		}
-		/*
-		 * See if the join var is from the left side, the right side,
-		 * or both (ie, it is a USING/NATURAL JOIN merger column).
-		 */
-		Assert(varattno > 0);
-		leftattno = (AttrNumber) nthi(varattno-1, rte->joinleftcols);
-		rightattno = (AttrNumber) nthi(varattno-1, rte->joinrightcols);
-		if (leftattno && rightattno)
-		{
-			/*
-			 * Var is a merge var.  If a left or right join, we can replace
-			 * it by the left or right input var respectively; we only need
-			 * a COALESCE for a full join.  However, beware of the possibility
-			 * that there's been a type promotion to make the input vars
-			 * compatible; do not replace a var by one of a different type!
-			 */
-			if (rte->jointype == JOIN_INNER ||
-				rte->jointype == JOIN_LEFT)
-			{
-				subrte = rt_fetch(leftrti, root->rtable);
-				get_rte_attribute_type(subrte, leftattno,
-									   &subtype, &subtypmod);
-				if (vartype == subtype && vartypmod == subtypmod)
-				{
-					varno = leftrti;
-					varattno = leftattno;
-					jexpr = (JoinExpr *) jexpr->larg;
-					continue;
-				}
-			}
-			if (rte->jointype == JOIN_INNER ||
-				rte->jointype == JOIN_RIGHT)
-			{
-				subrte = rt_fetch(rightrti, root->rtable);
-				get_rte_attribute_type(subrte, rightattno,
-									   &subtype, &subtypmod);
-				if (vartype == subtype && vartypmod == subtypmod)
-				{
-					varno = rightrti;
-					varattno = rightattno;
-					jexpr = (JoinExpr *) jexpr->rarg;
-					continue;
-				}
-			}
-			/*
-			 * This var cannot be substituted directly, only with a COALESCE.
-			 * Do so only if it belongs to the particular join indicated by
-			 * the caller.
-			 */
-			if (varno != expandRTI)
-				break;
-			{
-				Node   *l_var,
-					   *r_var;
-				CaseExpr   *c = makeNode(CaseExpr);
-				CaseWhen   *w = makeNode(CaseWhen);
-				NullTest   *n = makeNode(NullTest);
-
-				subrte = rt_fetch(leftrti, root->rtable);
-				get_rte_attribute_type(subrte, leftattno,
-									   &subtype, &subtypmod);
-				l_var = (Node *) makeVar(leftrti,
-										 leftattno,
-										 subtype,
-										 subtypmod,
-										 0);
-				if (subtype != vartype)
-				{
-					l_var = coerce_type(NULL, l_var, subtype,
-										vartype, vartypmod, false);
-					l_var = coerce_type_typmod(NULL, l_var,
-											   vartype, vartypmod);
-				}
-				else if (subtypmod != vartypmod)
-					l_var = coerce_type_typmod(NULL, l_var,
-											   vartype, vartypmod);
-
-				subrte = rt_fetch(rightrti, root->rtable);
-				get_rte_attribute_type(subrte, rightattno,
-									   &subtype, &subtypmod);
-				r_var = (Node *) makeVar(rightrti,
-										 rightattno,
-										 subtype,
-										 subtypmod,
-										 0);
-				if (subtype != vartype)
-				{
-					r_var = coerce_type(NULL, r_var, subtype,
-										vartype, vartypmod, false);
-					r_var = coerce_type_typmod(NULL, r_var,
-											   vartype, vartypmod);
-				}
-				else if (subtypmod != vartypmod)
-					r_var = coerce_type_typmod(NULL, r_var,
-											   vartype, vartypmod);
-
-				n->arg = l_var;
-				n->nulltesttype = IS_NOT_NULL;
-				w->expr = (Node *) n;
-				w->result = l_var;
-				c->casetype = vartype;
-				c->args = makeList1(w);
-				c->defresult = r_var;
-				return (Node *) c;
-			}
-		}
-		else if (leftattno)
-		{
-			/* Here we do not need to check the type */
-			varno = leftrti;
-			varattno = leftattno;
-			jexpr = (JoinExpr *) jexpr->larg;
-		}
-		else
-		{
-			Assert(rightattno);
-			/* Here we do not need to check the type */
-			varno = rightrti;
-			varattno = rightattno;
-			jexpr = (JoinExpr *) jexpr->rarg;
-		}
-	}
-
-	/*
-	 * When we fall out of the loop, we've reached the base Var.
-	 */
-	return (Node *) makeVar(varno,
-							varattno,
-							vartype,
-							vartypmod,
-							0);
-}
-
-/*
- * Given a join alias Var, construct Vars for the two input vars it directly
- * depends on.  Note that this should *only* be called for merger alias Vars.
- * In practice it is only used for Vars that got past flatten_join_alias_vars.
- */
-void
-build_join_alias_subvars(Query *root, Var *aliasvar,
-						 Var **leftsubvar, Var **rightsubvar)
-{
-	Index		varno = aliasvar->varno;
-	AttrNumber	varattno = aliasvar->varattno;
-	RangeTblEntry *rte;
-	JoinExpr   *jexpr;
-	Index		leftrti,
-				rightrti;
-	AttrNumber	leftattno,
-				rightattno;
-	RangeTblEntry *subrte;
-	Oid			subtype;
-	int32		subtypmod;
-
-	Assert(aliasvar->varlevelsup == 0);
-	rte = rt_fetch(varno, root->rtable);
-	Assert(rte->rtekind == RTE_JOIN);
-
-	/*
-	 * Find the RT indexes of the left and right children of the
-	 * join node.
-	 */
-	jexpr = (JoinExpr *) find_jointree_item((Node *) root->jointree, varno);
-	if (jexpr == NULL ||
-		!IsA(jexpr, JoinExpr) ||
-		jexpr->rtindex != varno)
-		elog(ERROR, "build_join_alias_subvars: failed to find JoinExpr");
-	if (IsA(jexpr->larg, RangeTblRef))
-		leftrti = ((RangeTblRef *) jexpr->larg)->rtindex;
-	else if (IsA(jexpr->larg, JoinExpr))
-		leftrti = ((JoinExpr *) jexpr->larg)->rtindex;
-	else
-	{
-		elog(ERROR, "build_join_alias_subvars: unexpected subtree type");
-		leftrti = 0;			/* keep compiler quiet */
-	}
-	if (IsA(jexpr->rarg, RangeTblRef))
-		rightrti = ((RangeTblRef *) jexpr->rarg)->rtindex;
-	else if (IsA(jexpr->rarg, JoinExpr))
-		rightrti = ((JoinExpr *) jexpr->rarg)->rtindex;
-	else
-	{
-		elog(ERROR, "build_join_alias_subvars: unexpected subtree type");
-		rightrti = 0;			/* keep compiler quiet */
-	}
-
-	Assert(varattno > 0);
-	leftattno = (AttrNumber) nthi(varattno-1, rte->joinleftcols);
-	rightattno = (AttrNumber) nthi(varattno-1, rte->joinrightcols);
-	if (!(leftattno && rightattno))
-		elog(ERROR, "build_join_alias_subvars: non-merger variable");
-
-	subrte = rt_fetch(leftrti, root->rtable);
-	get_rte_attribute_type(subrte, leftattno,
-						   &subtype, &subtypmod);
-	*leftsubvar = makeVar(leftrti,
-						  leftattno,
-						  subtype,
-						  subtypmod,
-						  0);
-
-	subrte = rt_fetch(rightrti, root->rtable);
-	get_rte_attribute_type(subrte, rightattno,
-						   &subtype, &subtypmod);
-	*rightsubvar = makeVar(rightrti,
-						   rightattno,
-						   subtype,
-						   subtypmod,
-						   0);
-}
-
-/*
- * Find jointree item matching the specified RT index
- */
-static Node *
-find_jointree_item(Node *jtnode, int rtindex)
-{
-	if (jtnode == NULL)
-		return NULL;
-	if (IsA(jtnode, RangeTblRef))
-	{
-		if (((RangeTblRef *) jtnode)->rtindex == rtindex)
-			return jtnode;
-	}
-	else if (IsA(jtnode, FromExpr))
-	{
-		FromExpr   *f = (FromExpr *) jtnode;
-		List	   *l;
-
-		foreach(l, f->fromlist)
+			return node;
+		Assert(var->varattno > 0);
+		newvar = (Node *) nth(var->varattno - 1, rte->joinaliasvars);
+		if (IsA(newvar, Var) || context->force)
 		{
-			jtnode = find_jointree_item(lfirst(l), rtindex);
-			if (jtnode)
-				return jtnode;
+			/* expand it; recurse in case join input is itself a join */
+			return flatten_join_alias_vars_mutator(newvar, context);
 		}
+		/* we don't want to force expansion of this alias Var */
+		return node;
 	}
-	else if (IsA(jtnode, JoinExpr))
-	{
-		JoinExpr   *j = (JoinExpr *) jtnode;
-
-		if (j->rtindex == rtindex)
-			return jtnode;
-		jtnode = find_jointree_item(j->larg, rtindex);
-		if (jtnode)
-			return jtnode;
-		jtnode = find_jointree_item(j->rarg, rtindex);
-		if (jtnode)
-			return jtnode;
-	}
-	else
-		elog(ERROR, "find_jointree_item: unexpected node type %d",
-			 nodeTag(jtnode));
-	return NULL;
+	return expression_tree_mutator(node, flatten_join_alias_vars_mutator,
+								   (void *) context);
 }
diff --git a/src/backend/parser/analyze.c b/src/backend/parser/analyze.c
index c0fe929e497..a3acf294534 100644
--- a/src/backend/parser/analyze.c
+++ b/src/backend/parser/analyze.c
@@ -6,7 +6,7 @@
  * Portions Copyright (c) 1996-2001, PostgreSQL Global Development Group
  * Portions Copyright (c) 1994, Regents of the University of California
  *
- *	$Header: /cvsroot/pgsql/src/backend/parser/analyze.c,v 1.232 2002/04/24 02:22:54 momjian Exp $
+ *	$Header: /cvsroot/pgsql/src/backend/parser/analyze.c,v 1.233 2002/04/28 19:54:28 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -2075,7 +2075,7 @@ transformSetOperationStmt(ParseState *pstate, SelectStmt *stmt)
 	Node	   *node;
 	List	   *lefttl,
 			   *dtlist,
-			   *colMods,
+			   *targetvars,
 			   *targetnames,
 			   *sv_namespace,
 			   *sv_rtable;
@@ -2145,14 +2145,12 @@ transformSetOperationStmt(ParseState *pstate, SelectStmt *stmt)
 	/*
 	 * Generate dummy targetlist for outer query using column names of
 	 * leftmost select and common datatypes of topmost set operation. Also
-	 * make a list of the column names for use in parsing ORDER BY.
-	 *
-	 * XXX colMods is a hack to provide a dummy typmod list below.  We
-	 * should probably keep track of common typmod instead.
+	 * make lists of the dummy vars and their names for use in parsing
+	 * ORDER BY.
 	 */
 	qry->targetList = NIL;
+	targetvars = NIL;
 	targetnames = NIL;
-	colMods = NIL;
 	lefttl = leftmostQuery->targetList;
 	foreach(dtlist, sostmt->colTypes)
 	{
@@ -2174,8 +2172,8 @@ transformSetOperationStmt(ParseState *pstate, SelectStmt *stmt)
 								0);
 		qry->targetList = lappend(qry->targetList,
 								  makeTargetEntry(resdom, expr));
+		targetvars = lappend(targetvars, expr);
 		targetnames = lappend(targetnames, makeString(colName));
-		colMods = lappendi(colMods, -1);
 		lefttl = lnext(lefttl);
 	}
 
@@ -2232,10 +2230,7 @@ transformSetOperationStmt(ParseState *pstate, SelectStmt *stmt)
 	jrte = addRangeTableEntryForJoin(NULL,
 									 targetnames,
 									 JOIN_INNER,
-									 sostmt->colTypes,
-									 colMods,
-									 NIL,
-									 NIL,
+									 targetvars,
 									 NULL,
 									 true);
 	jrtr = makeNode(RangeTblRef);
diff --git a/src/backend/parser/parse_clause.c b/src/backend/parser/parse_clause.c
index 452f66284d8..6df4a4fd7dc 100644
--- a/src/backend/parser/parse_clause.c
+++ b/src/backend/parser/parse_clause.c
@@ -8,7 +8,7 @@
  *
  *
  * IDENTIFICATION
- *	  $Header: /cvsroot/pgsql/src/backend/parser/parse_clause.c,v 1.89 2002/04/16 23:08:11 tgl Exp $
+ *	  $Header: /cvsroot/pgsql/src/backend/parser/parse_clause.c,v 1.90 2002/04/28 19:54:28 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -39,7 +39,7 @@
 
 static char *clauseText[] = {"ORDER BY", "GROUP BY", "DISTINCT ON"};
 
-static void extractUniqueColumns(List *common_colnames,
+static void extractRemainingColumns(List *common_colnames,
 					 List *src_colnames, List *src_colvars,
 					 List **res_colnames, List **res_colvars);
 static Node *transformJoinUsingClause(ParseState *pstate,
@@ -51,6 +51,8 @@ static RangeTblRef *transformRangeSubselect(ParseState *pstate,
 						RangeSubselect *r);
 static Node *transformFromClauseItem(ParseState *pstate, Node *n,
 						List **containedRels);
+static Node *buildMergedJoinVar(JoinType jointype,
+								Var *l_colvar, Var *r_colvar);
 static TargetEntry *findTargetlistEntry(ParseState *pstate, Node *node,
 					List *tlist, int clause);
 static List *addTargetToSortList(TargetEntry *tle, List *sortlist,
@@ -194,9 +196,9 @@ interpretInhOption(InhOption inhOpt)
  * Extract all not-in-common columns from column lists of a source table
  */
 static void
-extractUniqueColumns(List *common_colnames,
-					 List *src_colnames, List *src_colvars,
-					 List **res_colnames, List **res_colvars)
+extractRemainingColumns(List *common_colnames,
+						List *src_colnames, List *src_colvars,
+						List **res_colnames, List **res_colvars)
 {
 	List	   *new_colnames = NIL;
 	List	   *new_colvars = NIL;
@@ -496,10 +498,7 @@ transformFromClauseItem(ParseState *pstate, Node *n, List **containedRels)
 				   *res_colnames,
 				   *l_colvars,
 				   *r_colvars,
-				   *coltypes,
-				   *coltypmods,
-				   *leftcolnos,
-				   *rightcolnos;
+				   *res_colvars;
 		Index		leftrti,
 					rightrti;
 		RangeTblEntry *rte;
@@ -597,17 +596,14 @@ transformFromClauseItem(ParseState *pstate, Node *n, List **containedRels)
 		 * Now transform the join qualifications, if any.
 		 */
 		res_colnames = NIL;
-		coltypes = NIL;
-		coltypmods = NIL;
-		leftcolnos = NIL;
-		rightcolnos = NIL;
+		res_colvars = NIL;
 
 		if (j->using)
 		{
 			/*
 			 * JOIN/USING (or NATURAL JOIN, as transformed above).
 			 * Transform the list into an explicit ON-condition, and
-			 * generate a list of result columns.
+			 * generate a list of merged result columns.
 			 */
 			List	   *ucols = j->using;
 			List	   *l_usingvars = NIL;
@@ -620,14 +616,22 @@ transformFromClauseItem(ParseState *pstate, Node *n, List **containedRels)
 			{
 				char	   *u_colname = strVal(lfirst(ucol));
 				List	   *col;
-				Var		   *l_colvar,
-						   *r_colvar;
-				Oid			outcoltype;
-				int32		outcoltypmod;
 				int			ndx;
 				int			l_index = -1;
 				int			r_index = -1;
+				Var		   *l_colvar,
+						   *r_colvar;
+
+				/* Check for USING(foo,foo) */
+				foreach(col, res_colnames)
+				{
+					char	   *res_colname = strVal(lfirst(col));
 
+					if (strcmp(res_colname, u_colname) == 0)
+						elog(ERROR, "USING column name \"%s\" appears more than once", u_colname);
+				}
+
+				/* Find it in left input */
 				ndx = 0;
 				foreach(col, l_colnames)
 				{
@@ -645,6 +649,7 @@ transformFromClauseItem(ParseState *pstate, Node *n, List **containedRels)
 					elog(ERROR, "JOIN/USING column \"%s\" not found in left table",
 						 u_colname);
 
+				/* Find it in right input */
 				ndx = 0;
 				foreach(col, r_colnames)
 				{
@@ -667,30 +672,11 @@ transformFromClauseItem(ParseState *pstate, Node *n, List **containedRels)
 				r_colvar = nth(r_index, r_colvars);
 				r_usingvars = lappend(r_usingvars, r_colvar);
 
-				res_colnames = lappend(res_colnames,
-									   nth(l_index, l_colnames));
-				/*
-				 * Choose output type if input types are dissimilar.
-				 */
-				outcoltype = l_colvar->vartype;
-				outcoltypmod = l_colvar->vartypmod;
-				if (outcoltype != r_colvar->vartype)
-				{
-					outcoltype =
-						select_common_type(makeListi2(l_colvar->vartype,
-													  r_colvar->vartype),
-										   "JOIN/USING");
-					outcoltypmod = -1; /* ie, unknown */
-				}
-				else if (outcoltypmod != r_colvar->vartypmod)
-				{
-					/* same type, but not same typmod */
-					outcoltypmod = -1; /* ie, unknown */
-				}
-				coltypes = lappendi(coltypes, outcoltype);
-				coltypmods = lappendi(coltypmods, outcoltypmod);
-				leftcolnos = lappendi(leftcolnos, l_index+1);
-				rightcolnos = lappendi(rightcolnos, r_index+1);
+				res_colnames = lappend(res_colnames, lfirst(ucol));
+				res_colvars = lappend(res_colvars,
+									  buildMergedJoinVar(j->jointype,
+														 l_colvar,
+														 r_colvar));
 			}
 
 			j->quals = transformJoinUsingClause(pstate,
@@ -708,34 +694,16 @@ transformFromClauseItem(ParseState *pstate, Node *n, List **containedRels)
 		}
 
 		/* Add remaining columns from each side to the output columns */
-		extractUniqueColumns(res_colnames,
-							 l_colnames, l_colvars,
-							 &l_colnames, &l_colvars);
-		extractUniqueColumns(res_colnames,
-							 r_colnames, r_colvars,
-							 &r_colnames, &r_colvars);
+		extractRemainingColumns(res_colnames,
+								l_colnames, l_colvars,
+								&l_colnames, &l_colvars);
+		extractRemainingColumns(res_colnames,
+								r_colnames, r_colvars,
+								&r_colnames, &r_colvars);
 		res_colnames = nconc(res_colnames, l_colnames);
-		while (l_colvars)
-		{
-			Var	   *l_var = (Var *) lfirst(l_colvars);
-
-			coltypes = lappendi(coltypes, l_var->vartype);
-			coltypmods = lappendi(coltypmods, l_var->vartypmod);
-			leftcolnos = lappendi(leftcolnos, l_var->varattno);
-			rightcolnos = lappendi(rightcolnos, 0);
-			l_colvars = lnext(l_colvars);
-		}
+		res_colvars = nconc(res_colvars, l_colvars);
 		res_colnames = nconc(res_colnames, r_colnames);
-		while (r_colvars)
-		{
-			Var	   *r_var = (Var *) lfirst(r_colvars);
-
-			coltypes = lappendi(coltypes, r_var->vartype);
-			coltypmods = lappendi(coltypmods, r_var->vartypmod);
-			leftcolnos = lappendi(leftcolnos, 0);
-			rightcolnos = lappendi(rightcolnos, r_var->varattno);
-			r_colvars = lnext(r_colvars);
-		}
+		res_colvars = nconc(res_colvars, r_colvars);
 
 		/*
 		 * Check alias (AS clause), if any.
@@ -753,11 +721,12 @@ transformFromClauseItem(ParseState *pstate, Node *n, List **containedRels)
 		/*
 		 * Now build an RTE for the result of the join
 		 */
-		rte = addRangeTableEntryForJoin(pstate, res_colnames,
+		rte = addRangeTableEntryForJoin(pstate,
+										res_colnames,
 										j->jointype,
-										coltypes, coltypmods,
-										leftcolnos, rightcolnos,
-										j->alias, true);
+										res_colvars,
+										j->alias,
+										true);
 
 		/* assume new rte is at end */
 		j->rtindex = length(pstate->p_rtable);
@@ -777,6 +746,115 @@ transformFromClauseItem(ParseState *pstate, Node *n, List **containedRels)
 								 * quiet */
 }
 
+/*
+ * buildMergedJoinVar -
+ *	  generate a suitable replacement expression for a merged join column
+ */
+static Node *
+buildMergedJoinVar(JoinType jointype, Var *l_colvar, Var *r_colvar)
+{
+	Oid			outcoltype;
+	int32		outcoltypmod;
+	Node	   *l_node,
+			   *r_node,
+			   *res_node;
+
+	/*
+	 * Choose output type if input types are dissimilar.
+	 */
+	outcoltype = l_colvar->vartype;
+	outcoltypmod = l_colvar->vartypmod;
+	if (outcoltype != r_colvar->vartype)
+	{
+		outcoltype = select_common_type(makeListi2(l_colvar->vartype,
+												   r_colvar->vartype),
+										"JOIN/USING");
+		outcoltypmod = -1;		/* ie, unknown */
+	}
+	else if (outcoltypmod != r_colvar->vartypmod)
+	{
+		/* same type, but not same typmod */
+		outcoltypmod = -1;		/* ie, unknown */
+	}
+
+	/*
+	 * Insert coercion functions if needed.  Note that a difference in
+	 * typmod can only happen if input has typmod but outcoltypmod is -1.
+	 * In that case we insert a RelabelType to clearly mark that result's
+	 * typmod is not same as input.
+	 */
+	if (l_colvar->vartype != outcoltype)
+		l_node = coerce_type(NULL, (Node *) l_colvar, l_colvar->vartype,
+							 outcoltype, outcoltypmod, false);
+	else if (l_colvar->vartypmod != outcoltypmod)
+		l_node = (Node *) makeRelabelType((Node *) l_colvar,
+										  outcoltype, outcoltypmod);
+	else
+		l_node = (Node *) l_colvar;
+
+	if (r_colvar->vartype != outcoltype)
+		r_node = coerce_type(NULL, (Node *) r_colvar, r_colvar->vartype,
+							 outcoltype, outcoltypmod, false);
+	else if (r_colvar->vartypmod != outcoltypmod)
+		r_node = (Node *) makeRelabelType((Node *) r_colvar,
+										  outcoltype, outcoltypmod);
+	else
+		r_node = (Node *) r_colvar;
+
+	/*
+	 * Choose what to emit
+	 */
+	switch (jointype)
+	{
+		case JOIN_INNER:
+			/*
+			 * We can use either var; prefer non-coerced one if available.
+			 */
+			if (IsA(l_node, Var))
+				res_node = l_node;
+			else if (IsA(r_node, Var))
+				res_node = r_node;
+			else
+				res_node = l_node;
+			break;
+		case JOIN_LEFT:
+			/* Always use left var */
+			res_node = l_node;
+			break;
+		case JOIN_RIGHT:
+			/* Always use right var */
+			res_node = r_node;
+			break;
+		case JOIN_FULL:
+		{
+			/*
+			 * Here we must build a COALESCE expression to ensure that
+			 * the join output is non-null if either input is.
+			 */
+			CaseExpr   *c = makeNode(CaseExpr);
+			CaseWhen   *w = makeNode(CaseWhen);
+			NullTest   *n = makeNode(NullTest);
+
+			n->arg = l_node;
+			n->nulltesttype = IS_NOT_NULL;
+			w->expr = (Node *) n;
+			w->result = l_node;
+			c->casetype = outcoltype;
+			c->args = makeList1(w);
+			c->defresult = r_node;
+			res_node = (Node *) c;
+			break;
+		}
+		default:
+			elog(ERROR, "buildMergedJoinVar: unexpected jointype %d",
+				 (int) jointype);
+			res_node = NULL;	/* keep compiler quiet */
+			break;
+	}
+
+	return res_node;
+}
+
 
 /*
  * transformWhereClause -
diff --git a/src/backend/parser/parse_relation.c b/src/backend/parser/parse_relation.c
index 9b386cb6366..b822a2378ba 100644
--- a/src/backend/parser/parse_relation.c
+++ b/src/backend/parser/parse_relation.c
@@ -8,7 +8,7 @@
  *
  *
  * IDENTIFICATION
- *	  $Header: /cvsroot/pgsql/src/backend/parser/parse_relation.c,v 1.67 2002/04/02 08:51:51 inoue Exp $
+ *	  $Header: /cvsroot/pgsql/src/backend/parser/parse_relation.c,v 1.68 2002/04/28 19:54:28 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -681,10 +681,7 @@ RangeTblEntry *
 addRangeTableEntryForJoin(ParseState *pstate,
 						  List *colnames,
 						  JoinType jointype,
-						  List *coltypes,
-						  List *coltypmods,
-						  List *leftcols,
-						  List *rightcols,
+						  List *aliasvars,
 						  Alias *alias,
 						  bool inFromCl)
 {
@@ -696,10 +693,7 @@ addRangeTableEntryForJoin(ParseState *pstate,
 	rte->relid = InvalidOid;
 	rte->subquery = NULL;
 	rte->jointype = jointype;
-	rte->joincoltypes = coltypes;
-	rte->joincoltypmods = coltypmods;
-	rte->joinleftcols = leftcols;
-	rte->joinrightcols = rightcols;
+	rte->joinaliasvars = aliasvars;
 	rte->alias = alias;
 
 	eref = alias ? (Alias *) copyObject(alias) : makeAlias("unnamed_join", NIL);
@@ -922,13 +916,12 @@ expandRTE(ParseState *pstate, RangeTblEntry *rte,
 	{
 		/* Join RTE */
 		List	   *aliasp = rte->eref->colnames;
-		List	   *coltypes = rte->joincoltypes;
-		List	   *coltypmods = rte->joincoltypmods;
+		List	   *aliasvars = rte->joinaliasvars;
 
 		varattno = 0;
 		while (aliasp)
 		{
-			Assert(coltypes && coltypmods);
+			Assert(aliasvars);
 			varattno++;
 
 			if (colnames)
@@ -940,21 +933,21 @@ expandRTE(ParseState *pstate, RangeTblEntry *rte,
 
 			if (colvars)
 			{
+				Node	   *aliasvar = (Node *) lfirst(aliasvars);
 				Var		   *varnode;
 
 				varnode = makeVar(rtindex, varattno,
-								  (Oid) lfirsti(coltypes),
-								  (int32) lfirsti(coltypmods),
+								  exprType(aliasvar),
+								  exprTypmod(aliasvar),
 								  sublevels_up);
 
 				*colvars = lappend(*colvars, varnode);
 			}
 
 			aliasp = lnext(aliasp);
-			coltypes = lnext(coltypes);
-			coltypmods = lnext(coltypmods);
+			aliasvars = lnext(aliasvars);
 		}
-		Assert(coltypes == NIL && coltypmods == NIL);
+		Assert(aliasvars == NIL);
 	}
 	else
 		elog(ERROR, "expandRTE: unsupported RTE kind %d",
@@ -1091,10 +1084,13 @@ get_rte_attribute_type(RangeTblEntry *rte, AttrNumber attnum,
 	}
 	else if (rte->rtekind == RTE_JOIN)
 	{
-		/* Join RTE --- get type info directly from join RTE */
-		Assert(attnum > 0 && attnum <= length(rte->joincoltypes));
-		*vartype = (Oid) nthi(attnum-1, rte->joincoltypes);
-		*vartypmod = nthi(attnum-1, rte->joincoltypmods);
+		/* Join RTE --- get type info from join RTE's alias variable */
+		Node   *aliasvar;
+
+		Assert(attnum > 0 && attnum <= length(rte->joinaliasvars));
+		aliasvar = (Node *) nth(attnum-1, rte->joinaliasvars);
+		*vartype = exprType(aliasvar);
+		*vartypmod = exprTypmod(aliasvar);
 	}
 	else
 		elog(ERROR, "get_rte_attribute_type: unsupported RTE kind %d",
diff --git a/src/include/catalog/catversion.h b/src/include/catalog/catversion.h
index 6ebe79cc6a3..e135782bd58 100644
--- a/src/include/catalog/catversion.h
+++ b/src/include/catalog/catversion.h
@@ -37,7 +37,7 @@
  * Portions Copyright (c) 1996-2001, PostgreSQL Global Development Group
  * Portions Copyright (c) 1994, Regents of the University of California
  *
- * $Id: catversion.h,v 1.128 2002/04/27 21:24:34 tgl Exp $
+ * $Id: catversion.h,v 1.129 2002/04/28 19:54:28 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -53,6 +53,6 @@
  */
 
 /*							yyyymmddN */
-#define CATALOG_VERSION_NO	200204271
+#define CATALOG_VERSION_NO	200204281
 
 #endif
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 60703a06dcc..5bd0e89b185 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -7,7 +7,7 @@
  * Portions Copyright (c) 1996-2001, PostgreSQL Global Development Group
  * Portions Copyright (c) 1994, Regents of the University of California
  *
- * $Id: parsenodes.h,v 1.174 2002/04/24 02:48:55 momjian Exp $
+ * $Id: parsenodes.h,v 1.175 2002/04/28 19:54:28 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -510,17 +510,14 @@ typedef struct RangeTblEntry
 	/*
 	 * Fields valid for a join RTE (else NULL/zero):
 	 *
-	 * joincoltypes/joincoltypmods identify the column datatypes of the
-	 * join result.  joinleftcols and joinrightcols identify the source
-	 * columns from the join's inputs: each entry is either a source column
-	 * AttrNumber or zero.  For normal columns exactly one is nonzero,
-	 * but both are nonzero for a column "merged" by USING or NATURAL.
+	 * joinaliasvars is a list of Vars or COALESCE expressions corresponding
+	 * to the columns of the join result.  An alias Var referencing column
+	 * K of the join result can be replaced by the K'th element of
+	 * joinaliasvars --- but to simplify the task of reverse-listing aliases
+	 * correctly, we do not do that until planning time.
 	 */
 	JoinType	jointype;		/* type of join */
-	List	   *joincoltypes;	/* integer list of column type OIDs */
-	List	   *joincoltypmods;	/* integer list of column typmods */
-	List	   *joinleftcols;	/* integer list of left-side column #s */
-	List	   *joinrightcols;	/* integer list of right-side column #s */
+	List	   *joinaliasvars;	/* list of alias-var expansions */
 
 	/*
 	 * Fields valid in all RTEs:
diff --git a/src/include/nodes/plannodes.h b/src/include/nodes/plannodes.h
index 66db9cf3640..9ba1caca327 100644
--- a/src/include/nodes/plannodes.h
+++ b/src/include/nodes/plannodes.h
@@ -7,7 +7,7 @@
  * Portions Copyright (c) 1996-2001, PostgreSQL Global Development Group
  * Portions Copyright (c) 1994, Regents of the University of California
  *
- * $Id: plannodes.h,v 1.54 2002/03/12 00:52:01 tgl Exp $
+ * $Id: plannodes.h,v 1.55 2002/04/28 19:54:28 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -254,7 +254,6 @@ typedef struct SubqueryScan
  * jointype:	rule for joining tuples from left and right subtrees
  * joinqual:	qual conditions that came from JOIN/ON or JOIN/USING
  *				(plan.qual contains conditions that came from WHERE)
- * joinrti:		rtable index of corresponding JOIN RTE, if any (0 if none)
  *
  * When jointype is INNER, joinqual and plan.qual are semantically
  * interchangeable.  For OUTER jointypes, the two are *not* interchangeable;
@@ -263,8 +262,6 @@ typedef struct SubqueryScan
  * (But plan.qual is still applied before actually returning a tuple.)
  * For an outer join, only joinquals are allowed to be used as the merge
  * or hash condition of a merge or hash join.
- *
- * joinrti is for the convenience of setrefs.c; it's not used in execution.
  * ----------------
  */
 typedef struct Join
@@ -272,7 +269,6 @@ typedef struct Join
 	Plan		plan;
 	JoinType	jointype;
 	List	   *joinqual;		/* JOIN quals (in addition to plan.qual) */
-	Index		joinrti;		/* JOIN RTE, if any */
 } Join;
 
 /* ----------------
diff --git a/src/include/optimizer/planmain.h b/src/include/optimizer/planmain.h
index 919f3d23de4..3956555a2e0 100644
--- a/src/include/optimizer/planmain.h
+++ b/src/include/optimizer/planmain.h
@@ -7,7 +7,7 @@
  * Portions Copyright (c) 1996-2001, PostgreSQL Global Development Group
  * Portions Copyright (c) 1994, Regents of the University of California
  *
- * $Id: planmain.h,v 1.55 2002/03/12 00:52:03 tgl Exp $
+ * $Id: planmain.h,v 1.56 2002/04/28 19:54:28 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -59,7 +59,7 @@ extern void process_implied_equality(Query *root, Node *item1, Node *item2,
 extern void set_plan_references(Query *root, Plan *plan);
 extern List *join_references(List *clauses, Query *root,
 							 List *outer_tlist, List *inner_tlist,
-							 Index acceptable_rel, Index join_rti);
+							 Index acceptable_rel);
 extern void fix_opids(Node *node);
 
 /*
diff --git a/src/include/optimizer/var.h b/src/include/optimizer/var.h
index 1153604e48a..c9a22b8f55a 100644
--- a/src/include/optimizer/var.h
+++ b/src/include/optimizer/var.h
@@ -7,7 +7,7 @@
  * Portions Copyright (c) 1996-2001, PostgreSQL Global Development Group
  * Portions Copyright (c) 1994, Regents of the University of California
  *
- * $Id: var.h,v 1.18 2002/03/12 00:52:04 tgl Exp $
+ * $Id: var.h,v 1.19 2002/04/28 19:54:28 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -23,8 +23,6 @@ extern bool contain_var_reference(Node *node, int varno, int varattno,
 extern bool contain_whole_tuple_var(Node *node, int varno, int levelsup);
 extern bool contain_var_clause(Node *node);
 extern List *pull_var_clause(Node *node, bool includeUpperVars);
-extern Node *flatten_join_alias_vars(Node *node, Query *root, int expandRTI);
-extern void build_join_alias_subvars(Query *root, Var *aliasvar,
-									 Var **leftsubvar, Var **rightsubvar);
+extern Node *flatten_join_alias_vars(Node *node, Query *root, bool force);
 
 #endif   /* VAR_H */
diff --git a/src/include/parser/parse_relation.h b/src/include/parser/parse_relation.h
index 038931d9b6d..353ce8957c7 100644
--- a/src/include/parser/parse_relation.h
+++ b/src/include/parser/parse_relation.h
@@ -7,7 +7,7 @@
  * Portions Copyright (c) 1996-2001, PostgreSQL Global Development Group
  * Portions Copyright (c) 1994, Regents of the University of California
  *
- * $Id: parse_relation.h,v 1.31 2002/03/22 02:56:37 tgl Exp $
+ * $Id: parse_relation.h,v 1.32 2002/04/28 19:54:29 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -44,10 +44,7 @@ extern RangeTblEntry *addRangeTableEntryForSubquery(ParseState *pstate,
 extern RangeTblEntry *addRangeTableEntryForJoin(ParseState *pstate,
 						  List *colnames,
 						  JoinType jointype,
-						  List *coltypes,
-						  List *coltypmods,
-						  List *leftcols,
-						  List *rightcols,
+						  List *aliasvars,
 						  Alias *alias,
 						  bool inFromCl);
 extern void addRTEtoQuery(ParseState *pstate, RangeTblEntry *rte,
diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out
index cfe7ded08d9..78b0d89685d 100644
--- a/src/test/regress/expected/join.out
+++ b/src/test/regress/expected/join.out
@@ -1869,6 +1869,138 @@ SELECT * FROM t1 FULL JOIN t2 USING (name) FULL JOIN t3 USING (name);
 (4 rows)
 
 --
+-- Test interactions of join syntax and subqueries
+--
+-- Basic cases (we expect planner to pull up the subquery here)
+SELECT * FROM
+(SELECT * FROM t2) as s2
+INNER JOIN
+(SELECT * FROM t3) s3
+USING (name);
+ name | n  | n  
+------+----+----
+ aa   | 12 | 13
+ bb   | 22 | 23
+(2 rows)
+
+SELECT * FROM
+(SELECT * FROM t2) as s2
+LEFT JOIN
+(SELECT * FROM t3) s3
+USING (name);
+ name | n  | n  
+------+----+----
+ aa   | 12 | 13
+ bb   | 22 | 23
+ dd   | 42 |   
+(3 rows)
+
+SELECT * FROM
+(SELECT * FROM t2) as s2
+FULL JOIN
+(SELECT * FROM t3) s3
+USING (name);
+ name | n  | n  
+------+----+----
+ aa   | 12 | 13
+ bb   | 22 | 23
+ cc   |    | 33
+ dd   | 42 |   
+(4 rows)
+
+-- Cases with non-nullable expressions in subquery results;
+-- make sure these go to null as expected
+SELECT * FROM
+(SELECT name, n as s2_n, 2 as s2_2 FROM t2) as s2
+NATURAL INNER JOIN
+(SELECT name, n as s3_n, 3 as s3_2 FROM t3) s3;
+ name | s2_n | s2_2 | s3_n | s3_2 
+------+------+------+------+------
+ aa   |   12 |    2 |   13 |    3
+ bb   |   22 |    2 |   23 |    3
+(2 rows)
+
+SELECT * FROM
+(SELECT name, n as s2_n, 2 as s2_2 FROM t2) as s2
+NATURAL LEFT JOIN
+(SELECT name, n as s3_n, 3 as s3_2 FROM t3) s3;
+ name | s2_n | s2_2 | s3_n | s3_2 
+------+------+------+------+------
+ aa   |   12 |    2 |   13 |    3
+ bb   |   22 |    2 |   23 |    3
+ dd   |   42 |    2 |      |     
+(3 rows)
+
+SELECT * FROM
+(SELECT name, n as s2_n, 2 as s2_2 FROM t2) as s2
+NATURAL FULL JOIN
+(SELECT name, n as s3_n, 3 as s3_2 FROM t3) s3;
+ name | s2_n | s2_2 | s3_n | s3_2 
+------+------+------+------+------
+ aa   |   12 |    2 |   13 |    3
+ bb   |   22 |    2 |   23 |    3
+ cc   |      |      |   33 |    3
+ dd   |   42 |    2 |      |     
+(4 rows)
+
+SELECT * FROM
+(SELECT name, n as s1_n, 1 as s1_1 FROM t1) as s1
+NATURAL INNER JOIN
+(SELECT name, n as s2_n, 2 as s2_2 FROM t2) as s2
+NATURAL INNER JOIN
+(SELECT name, n as s3_n, 3 as s3_2 FROM t3) s3;
+ name | s1_n | s1_1 | s2_n | s2_2 | s3_n | s3_2 
+------+------+------+------+------+------+------
+ aa   |   11 |    1 |   12 |    2 |   13 |    3
+(1 row)
+
+SELECT * FROM
+(SELECT name, n as s1_n, 1 as s1_1 FROM t1) as s1
+NATURAL FULL JOIN
+(SELECT name, n as s2_n, 2 as s2_2 FROM t2) as s2
+NATURAL FULL JOIN
+(SELECT name, n as s3_n, 3 as s3_2 FROM t3) s3;
+ name | s1_n | s1_1 | s2_n | s2_2 | s3_n | s3_2 
+------+------+------+------+------+------+------
+ aa   |   11 |    1 |   12 |    2 |   13 |    3
+ bb   |      |      |   22 |    2 |   23 |    3
+ cc   |      |      |      |      |   33 |    3
+ dd   |      |      |   42 |    2 |      |     
+(4 rows)
+
+SELECT * FROM
+(SELECT name, n as s1_n FROM t1) as s1
+NATURAL FULL JOIN
+  (SELECT * FROM
+    (SELECT name, n as s2_n FROM t2) as s2
+    NATURAL FULL JOIN
+    (SELECT name, n as s3_n FROM t3) as s3
+  ) ss2;
+ name | s1_n | s2_n | s3_n 
+------+------+------+------
+ aa   |   11 |   12 |   13
+ bb   |      |   22 |   23
+ cc   |      |      |   33
+ dd   |      |   42 |     
+(4 rows)
+
+SELECT * FROM
+(SELECT name, n as s1_n FROM t1) as s1
+NATURAL FULL JOIN
+  (SELECT * FROM
+    (SELECT name, n as s2_n, 2 as s2_2 FROM t2) as s2
+    NATURAL FULL JOIN
+    (SELECT name, n as s3_n FROM t3) as s3
+  ) ss2;
+ name | s1_n | s2_n | s2_2 | s3_n 
+------+------+------+------+------
+ aa   |   11 |   12 |    2 |   13
+ bb   |      |   22 |    2 |   23
+ cc   |      |      |      |   33
+ dd   |      |   42 |    2 |     
+(4 rows)
+
+--
 -- Clean up
 --
 DROP TABLE t1;
diff --git a/src/test/regress/sql/join.sql b/src/test/regress/sql/join.sql
index 91e64adfc94..5baf7fd3cf6 100644
--- a/src/test/regress/sql/join.sql
+++ b/src/test/regress/sql/join.sql
@@ -217,6 +217,78 @@ INSERT INTO t3 VALUES ( 'cc', 33 );
 SELECT * FROM t1 FULL JOIN t2 USING (name) FULL JOIN t3 USING (name);
 
 --
+-- Test interactions of join syntax and subqueries
+--
+
+-- Basic cases (we expect planner to pull up the subquery here)
+SELECT * FROM
+(SELECT * FROM t2) as s2
+INNER JOIN
+(SELECT * FROM t3) s3
+USING (name);
+
+SELECT * FROM
+(SELECT * FROM t2) as s2
+LEFT JOIN
+(SELECT * FROM t3) s3
+USING (name);
+
+SELECT * FROM
+(SELECT * FROM t2) as s2
+FULL JOIN
+(SELECT * FROM t3) s3
+USING (name);
+
+-- Cases with non-nullable expressions in subquery results;
+-- make sure these go to null as expected
+SELECT * FROM
+(SELECT name, n as s2_n, 2 as s2_2 FROM t2) as s2
+NATURAL INNER JOIN
+(SELECT name, n as s3_n, 3 as s3_2 FROM t3) s3;
+
+SELECT * FROM
+(SELECT name, n as s2_n, 2 as s2_2 FROM t2) as s2
+NATURAL LEFT JOIN
+(SELECT name, n as s3_n, 3 as s3_2 FROM t3) s3;
+
+SELECT * FROM
+(SELECT name, n as s2_n, 2 as s2_2 FROM t2) as s2
+NATURAL FULL JOIN
+(SELECT name, n as s3_n, 3 as s3_2 FROM t3) s3;
+
+SELECT * FROM
+(SELECT name, n as s1_n, 1 as s1_1 FROM t1) as s1
+NATURAL INNER JOIN
+(SELECT name, n as s2_n, 2 as s2_2 FROM t2) as s2
+NATURAL INNER JOIN
+(SELECT name, n as s3_n, 3 as s3_2 FROM t3) s3;
+
+SELECT * FROM
+(SELECT name, n as s1_n, 1 as s1_1 FROM t1) as s1
+NATURAL FULL JOIN
+(SELECT name, n as s2_n, 2 as s2_2 FROM t2) as s2
+NATURAL FULL JOIN
+(SELECT name, n as s3_n, 3 as s3_2 FROM t3) s3;
+
+SELECT * FROM
+(SELECT name, n as s1_n FROM t1) as s1
+NATURAL FULL JOIN
+  (SELECT * FROM
+    (SELECT name, n as s2_n FROM t2) as s2
+    NATURAL FULL JOIN
+    (SELECT name, n as s3_n FROM t3) as s3
+  ) ss2;
+
+SELECT * FROM
+(SELECT name, n as s1_n FROM t1) as s1
+NATURAL FULL JOIN
+  (SELECT * FROM
+    (SELECT name, n as s2_n, 2 as s2_2 FROM t2) as s2
+    NATURAL FULL JOIN
+    (SELECT name, n as s3_n FROM t3) as s3
+  ) ss2;
+
+--
 -- Clean up
 --