Reimplementation of UNION/INTERSECT/EXCEPT. INTERSECT/EXCEPT now meet the

SQL92 semantics, including support for ALL option.  All three can be used
in subqueries and views.  DISTINCT and ORDER BY work now in views, too.
This rewrite fixes many problems with cross-datatype UNIONs and INSERT/SELECT
where the SELECT yields different datatypes than the INSERT needs.  I did
that by making UNION subqueries and SELECT in INSERT be treated like
subselects-in-FROM, thereby allowing an extra level of targetlist where the
datatype conversions can be inserted safely.
INITDB NEEDED!

5 files changed, 718 insertions, 673 deletions

diff --git a/src/backend/parser/analyze.c b/src/backend/parser/analyze.c
index 169075c47e2..ffedca05ed9 100644
--- a/src/backend/parser/analyze.c
+++ b/src/backend/parser/analyze.c
@@ -6,7 +6,7 @@
  * Portions Copyright (c) 1996-2000, PostgreSQL, Inc
  * Portions Copyright (c) 1994, Regents of the University of California
  *
- *	$Id: analyze.c,v 1.159 2000/09/29 18:21:36 tgl Exp $
+ *	$Id: analyze.c,v 1.160 2000/10/05 19:11:33 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -20,8 +20,10 @@
 #include "nodes/makefuncs.h"
 #include "parser/analyze.h"
 #include "parser/parse.h"
+#include "parser/parsetree.h"
 #include "parser/parse_agg.h"
 #include "parser/parse_clause.h"
+#include "parser/parse_coerce.h"
 #include "parser/parse_relation.h"
 #include "parser/parse_target.h"
 #include "parser/parse_type.h"
@@ -44,11 +46,13 @@ static Query *transformIndexStmt(ParseState *pstate, IndexStmt *stmt);
 static Query *transformExtendStmt(ParseState *pstate, ExtendStmt *stmt);
 static Query *transformRuleStmt(ParseState *query, RuleStmt *stmt);
 static Query *transformSelectStmt(ParseState *pstate, SelectStmt *stmt);
+static Query *transformSetOperationStmt(ParseState *pstate, SetOperationStmt *stmt);
+static Node *transformSetOperationTree(ParseState *pstate, Node *node);
 static Query *transformUpdateStmt(ParseState *pstate, UpdateStmt *stmt);
-static Query *transformCursorStmt(ParseState *pstate, SelectStmt *stmt);
 static Query *transformCreateStmt(ParseState *pstate, CreateStmt *stmt);
 static Query *transformAlterTableStmt(ParseState *pstate, AlterTableStmt *stmt);
 
+static List *getSetColTypes(ParseState *pstate, Node *node);
 static void transformForUpdate(Query *qry, List *forUpdate);
 static void transformFkeyGetPrimaryKey(FkConstraint *fkconstraint);
 static void transformConstraintAttrs(List *constraintList);
@@ -156,7 +160,7 @@ transformStmt(ParseState *pstate, Node *parseTree)
 			{
 				ViewStmt   *n = (ViewStmt *) parseTree;
 
-				n->query = (Query *) transformStmt(pstate, (Node *) n->query);
+				n->query = transformStmt(pstate, (Node *) n->query);
 
 				/*
 				 * If a list of column names was given, run through and
@@ -258,20 +262,17 @@ transformStmt(ParseState *pstate, Node *parseTree)
 			break;
 
 		case T_SelectStmt:
-			if (!((SelectStmt *) parseTree)->portalname)
-			{
-				result = transformSelectStmt(pstate, (SelectStmt *) parseTree);
-				result->limitOffset = ((SelectStmt *) parseTree)->limitOffset;
-				result->limitCount = ((SelectStmt *) parseTree)->limitCount;
-			}
-			else
-				result = transformCursorStmt(pstate, (SelectStmt *) parseTree);
+			result = transformSelectStmt(pstate, (SelectStmt *) parseTree);
+			break;
+
+		case T_SetOperationStmt:
+			result = transformSetOperationStmt(pstate, (SetOperationStmt *) parseTree);
 			break;
 
 		default:
 
 			/*
-			 * other statments don't require any transformation-- just
+			 * other statements don't require any transformation-- just
 			 * return the original parsetree, yea!
 			 */
 			result = makeNode(Query);
@@ -313,7 +314,7 @@ transformDeleteStmt(ParseState *pstate, DeleteStmt *stmt)
 	if (pstate->p_hasAggs)
 		parseCheckAggregates(pstate, qry, qual);
 
-	return (Query *) qry;
+	return qry;
 }
 
 /*
@@ -324,7 +325,6 @@ static Query *
 transformInsertStmt(ParseState *pstate, InsertStmt *stmt)
 {
 	Query	   *qry = makeNode(Query);
-	Node	   *qual;
 	List	   *icolumns;
 	List	   *attrnos;
 	List	   *attnos;
@@ -335,59 +335,89 @@ transformInsertStmt(ParseState *pstate, InsertStmt *stmt)
 	qry->commandType = CMD_INSERT;
 	pstate->p_is_insert = true;
 
-	/*----------
-	 * Initial processing steps are just like SELECT, which should not
-	 * be surprising, since we may be handling an INSERT ... SELECT.
-	 * It is important that we finish processing all the SELECT subclauses
-	 * before we start doing any INSERT-specific processing; otherwise
-	 * the behavior of SELECT within INSERT might be different from a
-	 * stand-alone SELECT.	(Indeed, Postgres up through 6.5 had bugs of
-	 * just that nature...)
-	 *----------
-	 */
-
-	/* set up a range table --- note INSERT target is not in it yet */
-	makeRangeTable(pstate, stmt->fromClause);
-
-	qry->targetList = transformTargetList(pstate, stmt->targetList);
-
-	qual = transformWhereClause(pstate, stmt->whereClause);
-
-	/*
-	 * Initial processing of HAVING clause is just like WHERE clause.
-	 * Additional work will be done in optimizer/plan/planner.c.
-	 */
-	qry->havingQual = transformWhereClause(pstate, stmt->havingClause);
-
-	qry->groupClause = transformGroupClause(pstate,
-											stmt->groupClause,
-											qry->targetList);
-
-	/* An InsertStmt has no sortClause */
-	qry->sortClause = NIL;
-
-	qry->distinctClause = transformDistinctClause(pstate,
-												  stmt->distinctClause,
-												  qry->targetList,
-												  &qry->sortClause);
-
-	qry->hasSubLinks = pstate->p_hasSubLinks;
-	qry->hasAggs = pstate->p_hasAggs;
-	if (pstate->p_hasAggs || qry->groupClause || qry->havingQual)
-		parseCheckAggregates(pstate, qry, qual);
-
 	/*
-	 * The INSERT INTO ... SELECT ... could have a UNION in child, so
-	 * unionClause may be false
+	 * Is it INSERT ... SELECT or INSERT ... VALUES?
 	 */
-	qry->unionall = stmt->unionall;
+	if (stmt->selectStmt)
+	{
+		List   *selectList;
+		Query  *selectQuery;
+		RangeTblEntry *rte;
+		RangeTblRef *rtr;
 
-	/*
-	 * Just hand through the unionClause and intersectClause. We will
-	 * handle it in the function Except_Intersect_Rewrite()
-	 */
-	qry->unionClause = stmt->unionClause;
-	qry->intersectClause = stmt->intersectClause;
+		/*
+		 * Process the source SELECT.
+		 *
+		 * It is important that this be handled just like a standalone SELECT;
+		 * otherwise the behavior of SELECT within INSERT might be different
+		 * from a stand-alone SELECT. (Indeed, Postgres up through 6.5 had
+		 * bugs of just that nature...)
+		 */
+		selectList = parse_analyze(makeList1(stmt->selectStmt), pstate);
+		Assert(length(selectList) == 1);
+
+		selectQuery = (Query *) lfirst(selectList);
+		Assert(IsA(selectQuery, Query));
+		Assert(selectQuery->commandType == CMD_SELECT);
+		if (selectQuery->into || selectQuery->isPortal)
+			elog(ERROR, "INSERT ... SELECT may not specify INTO");
+		/*
+		 * Make the source be a subquery in the INSERT's rangetable,
+		 * and add it to the joinlist.
+		 */
+		rte = addRangeTableEntryForSubquery(pstate,
+											selectQuery,
+											makeAttr("*SELECT*", NULL),
+											true);
+		rtr = makeNode(RangeTblRef);
+		/* assume new rte is at end */
+		rtr->rtindex = length(pstate->p_rtable);
+		Assert(rte == rt_fetch(rtr->rtindex, pstate->p_rtable));
+		pstate->p_joinlist = lappend(pstate->p_joinlist, rtr);
+		/*
+		 * Generate a targetlist for the INSERT that selects all
+		 * the non-resjunk columns from the subquery.  (We need this to
+		 * be separate from the subquery's tlist because we may add
+		 * columns, insert datatype coercions, etc.)
+		 *
+		 * HACK: constants in the INSERT's targetlist are copied up as-is
+		 * rather than being referenced as subquery outputs.  This is mainly
+		 * to ensure that when we try to coerce them to the target column's
+		 * datatype, the right things happen for UNKNOWN constants.
+		 * Otherwise this fails:
+		 *		INSERT INTO foo SELECT 'bar', ... FROM baz
+		 */
+		qry->targetList = NIL;
+		foreach(tl, selectQuery->targetList)
+		{
+			TargetEntry *tle = (TargetEntry *) lfirst(tl);
+			Resdom	   *resnode = tle->resdom;
+			Node	   *expr;
+
+			if (resnode->resjunk)
+				continue;
+			if (tle->expr && IsA(tle->expr, Const))
+				expr = tle->expr;
+			else
+				expr = (Node *) makeVar(rtr->rtindex,
+										resnode->resno,
+										resnode->restype,
+										resnode->restypmod,
+										0);
+			resnode = copyObject(resnode);
+			resnode->resno = (AttrNumber) pstate->p_last_resno++;
+			qry->targetList = lappend(qry->targetList,
+									  makeTargetEntry(resnode, expr));
+		}
+	}
+	else
+	{
+		/*
+		 * For INSERT ... VALUES, transform the given list of values
+		 * to form a targetlist for the INSERT.
+		 */
+		qry->targetList = transformTargetList(pstate, stmt->targetList);
+	}
 
 	/*
 	 * Now we are done with SELECT-like processing, and can get on with
@@ -400,11 +430,6 @@ transformInsertStmt(ParseState *pstate, InsertStmt *stmt)
 	 */
 	setTargetTable(pstate, stmt->relname, false, false);
 
-	/* now the range table and jointree will not change */
-	qry->rtable = pstate->p_rtable;
-	qry->jointree = makeFromExpr(pstate->p_joinlist, qual);
-	qry->resultRelation = refnameRangeTablePosn(pstate, stmt->relname, NULL);
-
 	/* Prepare to assign non-conflicting resnos to resjunk attributes */
 	if (pstate->p_last_resno <= pstate->p_target_relation->rd_rel->relnatts)
 		pstate->p_last_resno = pstate->p_target_relation->rd_rel->relnatts + 1;
@@ -412,7 +437,9 @@ transformInsertStmt(ParseState *pstate, InsertStmt *stmt)
 	/* Validate stmt->cols list, or build default list if no list given */
 	icolumns = checkInsertTargets(pstate, stmt->cols, &attrnos);
 
-	/* Prepare non-junk columns for assignment to target table */
+	/*
+	 * Prepare columns for assignment to target table.
+	 */
 	numuseratts = 0;
 	attnos = attrnos;
 	foreach(tl, qry->targetList)
@@ -421,18 +448,7 @@ transformInsertStmt(ParseState *pstate, InsertStmt *stmt)
 		Resdom	   *resnode = tle->resdom;
 		Ident	   *id;
 
-		if (resnode->resjunk)
-		{
-
-			/*
-			 * Resjunk nodes need no additional processing, but be sure
-			 * they have names and resnos that do not match any target
-			 * columns; else rewriter or planner might get confused.
-			 */
-			resnode->resname = "?resjunk?";
-			resnode->resno = (AttrNumber) pstate->p_last_resno++;
-			continue;
-		}
+		Assert(!resnode->resjunk);
 		if (icolumns == NIL || attnos == NIL)
 			elog(ERROR, "INSERT has more expressions than target columns");
 		id = (Ident *) lfirst(icolumns);
@@ -458,7 +474,8 @@ transformInsertStmt(ParseState *pstate, InsertStmt *stmt)
 	 * have defaults and were not assigned to by the user.
 	 *
 	 * XXX wouldn't it make more sense to do this further downstream, after
-	 * the rule rewriter?
+	 * the rule rewriter?  As is, altering a column default will not change
+	 * the behavior of INSERTs in already-defined rules.
 	 */
 	rd_att = pstate->p_target_relation->rd_att;
 	if (rd_att->constr && rd_att->constr->num_defval > 0)
@@ -498,13 +515,17 @@ transformInsertStmt(ParseState *pstate, InsertStmt *stmt)
 		}
 	}
 
-	if (stmt->forUpdate != NULL)
-		transformForUpdate(qry, stmt->forUpdate);
+	/* done building the range table and jointree */
+	qry->rtable = pstate->p_rtable;
+	qry->jointree = makeFromExpr(pstate->p_joinlist, NULL);
+	qry->resultRelation = refnameRangeTablePosn(pstate, stmt->relname, NULL);
 
-	/* in case of subselects in default clauses... */
 	qry->hasSubLinks = pstate->p_hasSubLinks;
+	qry->hasAggs = pstate->p_hasAggs;
+	if (pstate->p_hasAggs)
+		parseCheckAggregates(pstate, qry, NULL);
 
-	return (Query *) qry;
+	return qry;
 }
 
 /*
@@ -1608,6 +1629,7 @@ transformRuleStmt(ParseState *pstate, RuleStmt *stmt)
  * transformSelectStmt -
  *	  transforms a Select Statement
  *
+ * Note: this is also used for DECLARE CURSOR statements.
  */
 static Query *
 transformSelectStmt(ParseState *pstate, SelectStmt *stmt)
@@ -1617,13 +1639,42 @@ transformSelectStmt(ParseState *pstate, SelectStmt *stmt)
 
 	qry->commandType = CMD_SELECT;
 
+	if (stmt->portalname)
+	{
+		/* DECLARE CURSOR */
+		if (stmt->into)
+			elog(ERROR, "DECLARE CURSOR must not specify INTO");
+		if (stmt->forUpdate)
+			elog(ERROR, "DECLARE/UPDATE is not supported"
+				 "\n\tCursors must be READ ONLY");
+		/*
+		 *	15 august 1991 -- since 3.0 postgres does locking
+		 *	right, we discovered that portals were violating
+		 *	locking protocol.  portal locks cannot span xacts.
+		 *	as a short-term fix, we installed the check here.
+		 *							-- mao
+		 */
+		if (!IsTransactionBlock())
+			elog(ERROR, "DECLARE CURSOR may only be used in begin/end transaction blocks");
+
+		qry->into = stmt->portalname;
+		qry->isTemp = stmt->istemp;
+		qry->isPortal = TRUE;
+		qry->isBinary = stmt->binary; /* internal portal */
+	}
+	else
+	{
+		/* SELECT */
+		qry->into = stmt->into;
+		qry->isTemp = stmt->istemp;
+		qry->isPortal = FALSE;
+		qry->isBinary = FALSE;
+	}
+
 	/* set up a range table */
 	makeRangeTable(pstate, stmt->fromClause);
 
-	qry->into = stmt->into;
-	qry->isTemp = stmt->istemp;
-	qry->isPortal = FALSE;
-
+	/* transform targetlist and WHERE */
 	qry->targetList = transformTargetList(pstate, stmt->targetList);
 
 	qual = transformWhereClause(pstate, stmt->whereClause);
@@ -1647,34 +1698,357 @@ transformSelectStmt(ParseState *pstate, SelectStmt *stmt)
 												  qry->targetList,
 												  &qry->sortClause);
 
+	qry->limitOffset = stmt->limitOffset;
+	qry->limitCount = stmt->limitCount;
+
 	qry->hasSubLinks = pstate->p_hasSubLinks;
 	qry->hasAggs = pstate->p_hasAggs;
 	if (pstate->p_hasAggs || qry->groupClause || qry->havingQual)
 		parseCheckAggregates(pstate, qry, qual);
 
+	qry->rtable = pstate->p_rtable;
+	qry->jointree = makeFromExpr(pstate->p_joinlist, qual);
+
+	if (stmt->forUpdate != NULL)
+		transformForUpdate(qry, stmt->forUpdate);
+
+	return qry;
+}
+
+/*
+ * transformSetOperationsStmt -
+ *	  transforms a SetOperations Statement
+ *
+ * SetOperations is actually just a SELECT, but with UNION/INTERSECT/EXCEPT
+ * structure to it.  We must transform each leaf SELECT and build up a top-
+ * level Query that contains the leaf SELECTs as subqueries in its rangetable.
+ * The SetOperations tree (with leaf SelectStmts replaced by RangeTblRef nodes)
+ * becomes the setOperations field of the top-level Query.
+ */
+static Query *
+transformSetOperationStmt(ParseState *pstate, SetOperationStmt *stmt)
+{
+	Query	   *qry = makeNode(Query);
+	Node	   *node;
+	SelectStmt *leftmostSelect;
+	Query	   *leftmostQuery;
+	char	   *into;
+	char	   *portalname;
+	bool		binary;
+	bool		istemp;
+	List	   *sortClause;
+	Node	   *limitOffset;
+	Node	   *limitCount;
+	List	   *forUpdate;
+	List	   *lefttl,
+			   *dtlist;
+	int			tllen;
+
+	qry->commandType = CMD_SELECT;
+
 	/*
-	 * The INSERT INTO ... SELECT ... could have a UNION in child, so
-	 * unionClause may be false
+	 * Find leftmost leaf SelectStmt and extract the one-time-only items
+	 * from it.
 	 */
-	qry->unionall = stmt->unionall;
+	node = stmt->larg;
+	while (node && IsA(node, SetOperationStmt))
+		node = ((SetOperationStmt *) node)->larg;
+	Assert(node && IsA(node, SelectStmt));
+	leftmostSelect = (SelectStmt *) node;
+
+	into = leftmostSelect->into;
+	portalname = leftmostSelect->portalname;
+	binary = leftmostSelect->binary;
+	istemp = leftmostSelect->istemp;
+	sortClause = leftmostSelect->sortClause;
+	limitOffset = leftmostSelect->limitOffset;
+	limitCount = leftmostSelect->limitCount;
+	forUpdate = leftmostSelect->forUpdate;
+
+	/* clear them to prevent complaints in transformSetOperationTree() */
+	leftmostSelect->into = NULL;
+	leftmostSelect->portalname = NULL;
+	leftmostSelect->binary = false;
+	leftmostSelect->istemp = false;
+	leftmostSelect->sortClause = NIL;
+	leftmostSelect->limitOffset = NULL;
+	leftmostSelect->limitCount = NULL;
+	leftmostSelect->forUpdate = NIL;
+
+	/* We don't actually support forUpdate with set ops at the moment. */
+	if (forUpdate)
+		elog(ERROR, "SELECT FOR UPDATE is not allowed with UNION/INTERSECT/EXCEPT");
 
 	/*
-	 * Just hand through the unionClause and intersectClause. We will
-	 * handle it in the function Except_Intersect_Rewrite()
+	 * Recursively transform the components of the tree.
 	 */
-	qry->unionClause = stmt->unionClause;
-	qry->intersectClause = stmt->intersectClause;
+	stmt = (SetOperationStmt *)
+		transformSetOperationTree(pstate, (Node *) stmt);
+	Assert(stmt && IsA(stmt, SetOperationStmt));
+	qry->setOperations = (Node *) stmt;
+
+	/*
+	 * Re-find leftmost SELECT (now it's a sub-query in rangetable)
+	 */
+	node = stmt->larg;
+	while (node && IsA(node, SetOperationStmt))
+		node = ((SetOperationStmt *) node)->larg;
+	Assert(node && IsA(node, RangeTblRef));
+	leftmostQuery = rt_fetch(((RangeTblRef *) node)->rtindex,
+							 pstate->p_rtable)->subquery;
+	Assert(leftmostQuery != NULL);
+	/*
+	 * Generate dummy targetlist for outer query using column names of
+	 * leftmost select and common datatypes of topmost set operation
+	 */
+	qry->targetList = NIL;
+	lefttl = leftmostQuery->targetList;
+	foreach(dtlist, stmt->colTypes)
+	{
+		Oid		colType = (Oid) lfirsti(dtlist);
+		char   *colName = ((TargetEntry *) lfirst(lefttl))->resdom->resname;
+		Resdom *resdom;
+		Node   *expr;
+
+		resdom = makeResdom((AttrNumber) pstate->p_last_resno++,
+							colType,
+							-1,
+							pstrdup(colName),
+							false);
+		expr = (Node *) makeVar(1,
+								resdom->resno,
+								colType,
+								-1,
+								0);
+		qry->targetList = lappend(qry->targetList,
+								  makeTargetEntry(resdom, expr));
+		lefttl = lnext(lefttl);
+	}
+	/*
+	 * Insert one-time items into top-level query
+	 *
+	 * This needs to agree with transformSelectStmt!
+	 */
+	if (portalname)
+	{
+		/* DECLARE CURSOR */
+		if (into)
+			elog(ERROR, "DECLARE CURSOR must not specify INTO");
+		if (forUpdate)
+			elog(ERROR, "DECLARE/UPDATE is not supported"
+				 "\n\tCursors must be READ ONLY");
+		/*
+		 *	15 august 1991 -- since 3.0 postgres does locking
+		 *	right, we discovered that portals were violating
+		 *	locking protocol.  portal locks cannot span xacts.
+		 *	as a short-term fix, we installed the check here.
+		 *							-- mao
+		 */
+		if (!IsTransactionBlock())
+			elog(ERROR, "DECLARE CURSOR may only be used in begin/end transaction blocks");
+
+		qry->into = portalname;
+		qry->isTemp = istemp;
+		qry->isPortal = TRUE;
+		qry->isBinary = binary; /* internal portal */
+	}
+	else
+	{
+		/* SELECT */
+		qry->into = into;
+		qry->isTemp = istemp;
+		qry->isPortal = FALSE;
+		qry->isBinary = FALSE;
+	}
+
+	/*
+	 * For now, we don't support resjunk sort clauses on the output of a
+	 * setOperation tree --- you can only use the SQL92-spec options of
+	 * selecting an output column by name or number.  Enforce by checking
+	 * that transformSortClause doesn't add any items to tlist.
+	 */
+	tllen = length(qry->targetList);
+
+	qry->sortClause = transformSortClause(pstate,
+										  sortClause,
+										  qry->targetList);
+
+	if (tllen != length(qry->targetList))
+		elog(ERROR, "ORDER BY on a UNION/INTERSECT/EXCEPT result must be on one of the result columns");
+
+	qry->limitOffset = limitOffset;
+	qry->limitCount = limitCount;
+
+	qry->hasSubLinks = pstate->p_hasSubLinks;
+	qry->hasAggs = pstate->p_hasAggs;
+	if (pstate->p_hasAggs || qry->groupClause || qry->havingQual)
+		parseCheckAggregates(pstate, qry, NULL);
 
 	qry->rtable = pstate->p_rtable;
-	qry->jointree = makeFromExpr(pstate->p_joinlist, qual);
+	qry->jointree = makeFromExpr(pstate->p_joinlist, NULL);
 
-	if (stmt->forUpdate != NULL)
-		transformForUpdate(qry, stmt->forUpdate);
+	if (forUpdate != NULL)
+		transformForUpdate(qry, forUpdate);
 
-	return (Query *) qry;
+	return qry;
+}
+
+/*
+ * transformSetOperationTree
+ *		Recursively transform leaves and internal nodes of a set-op tree
+ */
+static Node *
+transformSetOperationTree(ParseState *pstate, Node *node)
+{
+	if (IsA(node, SelectStmt))
+	{
+		SelectStmt *stmt = (SelectStmt *) node;
+		List   *save_rtable;
+		List   *selectList;
+		Query  *selectQuery;
+		char	selectName[32];
+		RangeTblEntry *rte;
+		RangeTblRef *rtr;
+
+		/*
+		 * Validity-check leaf SELECTs for disallowed ops.  INTO check is
+		 * necessary, the others should have been disallowed by grammar.
+		 */
+		if (stmt->into)
+			elog(ERROR, "INTO is only allowed on first SELECT of UNION/INTERSECT/EXCEPT");
+		if (stmt->portalname)
+			elog(ERROR, "Portal is only allowed on first SELECT of UNION/INTERSECT/EXCEPT");
+		if (stmt->sortClause)
+			elog(ERROR, "ORDER BY is only allowed at end of UNION/INTERSECT/EXCEPT");
+		if (stmt->limitOffset || stmt->limitCount)
+			elog(ERROR, "LIMIT is only allowed at end of UNION/INTERSECT/EXCEPT");
+		if (stmt->forUpdate)
+			elog(ERROR, "FOR UPDATE is only allowed at end of UNION/INTERSECT/EXCEPT");
+		/*
+		 * Transform SelectStmt into a Query.  We do not want any previously
+		 * transformed leaf queries to be visible in the outer context of
+		 * this sub-query, so temporarily make the top-level pstate have an
+		 * empty rtable.  (We needn't do the same with the joinlist because
+		 * we aren't entering anything in the top-level joinlist.)
+		 */
+		save_rtable = pstate->p_rtable;
+		pstate->p_rtable = NIL;
+		selectList = parse_analyze(makeList1(stmt), pstate);
+		pstate->p_rtable = save_rtable;
+
+		Assert(length(selectList) == 1);
+		selectQuery = (Query *) lfirst(selectList);
+		/*
+		 * Make the leaf query be a subquery in the top-level rangetable.
+		 */
+		sprintf(selectName, "*SELECT* %d", length(pstate->p_rtable) + 1);
+		rte = addRangeTableEntryForSubquery(pstate,
+											selectQuery,
+											makeAttr(pstrdup(selectName),
+													 NULL),
+											false);
+		/*
+		 * Return a RangeTblRef to replace the SelectStmt in the set-op tree.
+		 */
+		rtr = makeNode(RangeTblRef);
+		/* assume new rte is at end */
+		rtr->rtindex = length(pstate->p_rtable);
+		Assert(rte == rt_fetch(rtr->rtindex, pstate->p_rtable));
+		return (Node *) rtr;
+	}
+	else if (IsA(node, SetOperationStmt))
+	{
+		SetOperationStmt *op = (SetOperationStmt *) node;
+		List   *lcoltypes;
+		List   *rcoltypes;
+		const char *context;
+
+		context = (op->op == SETOP_UNION ? "UNION" :
+				   (op->op == SETOP_INTERSECT ? "INTERSECT" :
+					"EXCEPT"));
+		/*
+		 * Recursively transform the child nodes.
+		 */
+		op->larg = transformSetOperationTree(pstate, op->larg);
+		op->rarg = transformSetOperationTree(pstate, op->rarg);
+		/*
+		 * Verify that the two children have the same number of non-junk
+		 * columns, and determine the types of the merged output columns.
+		 */
+		lcoltypes = getSetColTypes(pstate, op->larg);
+		rcoltypes = getSetColTypes(pstate, op->rarg);
+		if (length(lcoltypes) != length(rcoltypes))
+			elog(ERROR, "Each %s query must have the same number of columns",
+				 context);
+		op->colTypes = NIL;
+		while (lcoltypes != NIL)
+		{
+			Oid		lcoltype = (Oid) lfirsti(lcoltypes);
+			Oid		rcoltype = (Oid) lfirsti(rcoltypes);
+			Oid		rescoltype;
+
+			rescoltype = select_common_type(makeListi2(lcoltype, rcoltype),
+											context);
+			op->colTypes = lappendi(op->colTypes, rescoltype);
+			lcoltypes = lnext(lcoltypes);
+			rcoltypes = lnext(rcoltypes);
+		}
+		return (Node *) op;
+	}
+	else
+	{
+		elog(ERROR, "transformSetOperationTree: unexpected node %d",
+			 (int) nodeTag(node));
+		return NULL;			/* keep compiler quiet */
+	}
 }
 
 /*
+ * getSetColTypes
+ *		Get output column types of an (already transformed) set-op node
+ */
+static List *
+getSetColTypes(ParseState *pstate, Node *node)
+{
+	if (IsA(node, RangeTblRef))
+	{
+		RangeTblRef *rtr = (RangeTblRef *) node;
+		RangeTblEntry *rte = rt_fetch(rtr->rtindex, pstate->p_rtable);
+		Query  *selectQuery = rte->subquery;
+		List   *result = NIL;
+		List   *tl;
+
+		Assert(selectQuery != NULL);
+		/* Get types of non-junk columns */
+		foreach(tl, selectQuery->targetList)
+		{
+			TargetEntry *tle = (TargetEntry *) lfirst(tl);
+			Resdom	   *resnode = tle->resdom;
+
+			if (resnode->resjunk)
+				continue;
+			result = lappendi(result, resnode->restype);
+		}
+		return result;
+	}
+	else if (IsA(node, SetOperationStmt))
+	{
+		SetOperationStmt *op = (SetOperationStmt *) node;
+
+		/* Result already computed during transformation of node */
+		Assert(op->colTypes != NIL);
+		return op->colTypes;
+	}
+	else
+	{
+		elog(ERROR, "getSetColTypes: unexpected node %d",
+			 (int) nodeTag(node));
+		return NIL;				/* keep compiler quiet */
+	}
+}
+
+
+/*
  * transformUpdateStmt -
  *	  transforms an update statement
  *
@@ -1756,26 +2130,6 @@ transformUpdateStmt(ParseState *pstate, UpdateStmt *stmt)
 	if (origTargetList != NIL)
 		elog(ERROR, "UPDATE target count mismatch --- internal error");
 
-	return (Query *) qry;
-}
-
-/*
- * transformCursorStmt -
- *	  transform a Create Cursor Statement
- *
- */
-static Query *
-transformCursorStmt(ParseState *pstate, SelectStmt *stmt)
-{
-	Query	   *qry;
-
-	qry = transformSelectStmt(pstate, stmt);
-
-	qry->into = stmt->portalname;
-	qry->isTemp = stmt->istemp;
-	qry->isPortal = TRUE;
-	qry->isBinary = stmt->binary;		/* internal portal */
-
 	return qry;
 }
 
@@ -2039,106 +2393,11 @@ transformAlterTableStmt(ParseState *pstate, AlterTableStmt *stmt)
 	return qry;
 }
 
-
-/* This function steps through the tree
- * built up by the select_w_o_sort rule
- * and builds a list of all SelectStmt Nodes found
- * The built up list is handed back in **select_list.
- * If one of the SelectStmt Nodes has the 'unionall' flag
- * set to true *unionall_present hands back 'true' */
-void
-create_select_list(Node *ptr, List **select_list, bool *unionall_present)
-{
-	if (IsA(ptr, SelectStmt))
-	{
-		*select_list = lappend(*select_list, ptr);
-		if (((SelectStmt *) ptr)->unionall == TRUE)
-			*unionall_present = TRUE;
-		return;
-	}
-
-	/* Recursively call for all arguments. A NOT expr has no lexpr! */
-	if (((A_Expr *) ptr)->lexpr != NULL)
-		create_select_list(((A_Expr *) ptr)->lexpr, select_list, unionall_present);
-	create_select_list(((A_Expr *) ptr)->rexpr, select_list, unionall_present);
-}
-
-/* Changes the A_Expr Nodes to Expr Nodes and exchanges ANDs and ORs.
- * The reason for the exchange is easy: We implement INTERSECTs and EXCEPTs
- * by rewriting these queries to semantically equivalent queries that use
- * IN and NOT IN subselects. To be able to use all three operations
- * (UNIONs INTERSECTs and EXCEPTs) in one complex query we have to
- * translate the queries into Disjunctive Normal Form (DNF). Unfortunately
- * there is no function 'dnfify' but there is a function 'cnfify'
- * which produces DNF when we exchange ANDs and ORs before calling
- * 'cnfify' and exchange them back in the result.
- *
- * If an EXCEPT or INTERSECT is present *intersect_present
- * hands back 'true' */
-Node *
-A_Expr_to_Expr(Node *ptr, bool *intersect_present)
-{
-	Node	   *result = NULL;
-
-	switch (nodeTag(ptr))
-	{
-		case T_A_Expr:
-			{
-				A_Expr	   *a = (A_Expr *) ptr;
-
-				switch (a->oper)
-				{
-					case AND:
-						{
-							Expr	   *expr = makeNode(Expr);
-							Node	   *lexpr = A_Expr_to_Expr(((A_Expr *) ptr)->lexpr, intersect_present);
-							Node	   *rexpr = A_Expr_to_Expr(((A_Expr *) ptr)->rexpr, intersect_present);
-
-							*intersect_present = TRUE;
-
-							expr->typeOid = BOOLOID;
-							expr->opType = OR_EXPR;
-							expr->args = makeList2(lexpr, rexpr);
-							result = (Node *) expr;
-							break;
-						}
-					case OR:
-						{
-							Expr	   *expr = makeNode(Expr);
-							Node	   *lexpr = A_Expr_to_Expr(((A_Expr *) ptr)->lexpr, intersect_present);
-							Node	   *rexpr = A_Expr_to_Expr(((A_Expr *) ptr)->rexpr, intersect_present);
-
-							expr->typeOid = BOOLOID;
-							expr->opType = AND_EXPR;
-							expr->args = makeList2(lexpr, rexpr);
-							result = (Node *) expr;
-							break;
-						}
-					case NOT:
-						{
-							Expr	   *expr = makeNode(Expr);
-							Node	   *rexpr = A_Expr_to_Expr(((A_Expr *) ptr)->rexpr, intersect_present);
-
-							expr->typeOid = BOOLOID;
-							expr->opType = NOT_EXPR;
-							expr->args = makeList1(rexpr);
-							result = (Node *) expr;
-							break;
-						}
-				}
-				break;
-			}
-		default:
-			result = ptr;
-	}
-	return result;
-}
-
 void
 CheckSelectForUpdate(Query *qry)
 {
-	if (qry->unionClause || qry->intersectClause)
-		elog(ERROR, "SELECT FOR UPDATE is not allowed with UNION/INTERSECT/EXCEPT clause");
+	if (qry->setOperations)
+		elog(ERROR, "SELECT FOR UPDATE is not allowed with UNION/INTERSECT/EXCEPT");
 	if (qry->distinctClause != NIL)
 		elog(ERROR, "SELECT FOR UPDATE is not allowed with DISTINCT clause");
 	if (qry->groupClause != NIL)
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 31aea152fa6..f13b942abd8 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -11,7 +11,7 @@
  *
  *
  * IDENTIFICATION
- *	  $Header: /cvsroot/pgsql/src/backend/parser/gram.y,v 2.193 2000/09/29 18:21:36 tgl Exp $
+ *	  $Header: /cvsroot/pgsql/src/backend/parser/gram.y,v 2.194 2000/10/05 19:11:33 tgl Exp $
  *
  * HISTORY
  *	  AUTHOR			DATE			MAJOR EVENT
@@ -78,6 +78,7 @@ static Node *makeA_Expr(int oper, char *opname, Node *lexpr, Node *rexpr);
 static Node *makeTypeCast(Node *arg, TypeName *typename);
 static Node *makeRowExpr(char *opr, List *largs, List *rargs);
 static void mapTargetColumns(List *source, List *target);
+static SelectStmt *findLeftmostSelect(Node *node);
 static bool exprIsNullConstant(Node *arg);
 static Node *doNegate(Node *n);
 static void doNegateFloat(Value *v);
@@ -112,25 +113,26 @@ static void doNegateFloat(Value *v);
 	VersionStmt			*vstmt;
 	DefineStmt			*dstmt;
 	RuleStmt			*rstmt;
-	InsertStmt			*astmt;
+	InsertStmt			*istmt;
 }
 
 %type <node>	stmt,
-		AlterSchemaStmt, AlterTableStmt, ClosePortalStmt,
-		CopyStmt, CreateStmt, CreateAsStmt, CreateSchemaStmt, CreateSeqStmt, DefineStmt, DropStmt,
-		TruncateStmt, CommentStmt,
-		ExtendStmt, FetchStmt,	GrantStmt, CreateTrigStmt, DropSchemaStmt, DropTrigStmt,
-		CreatePLangStmt, DropPLangStmt,
-		IndexStmt, ListenStmt, UnlistenStmt, LockStmt, OptimizableStmt,
-		ProcedureStmt, ReindexStmt, RemoveAggrStmt, RemoveOperStmt,
-		RemoveFuncStmt, RemoveStmt,
-		RenameStmt, RevokeStmt, RuleStmt, SetSessionStmt, TransactionStmt, ViewStmt, LoadStmt,
-		CreatedbStmt, DropdbStmt, VacuumStmt, CursorStmt, SubSelect,
-		UpdateStmt, InsertStmt, select_clause, SelectStmt, NotifyStmt, DeleteStmt, 
-		ClusterStmt, ExplainStmt, VariableSetStmt, VariableShowStmt, VariableResetStmt,
-		CreateUserStmt, AlterUserStmt, DropUserStmt, RuleActionStmt,
-		RuleActionStmtOrEmpty, ConstraintsSetStmt,
-		CreateGroupStmt, AlterGroupStmt, DropGroupStmt
+		AlterGroupStmt, AlterSchemaStmt, AlterTableStmt, AlterUserStmt,
+		ClosePortalStmt, ClusterStmt, CommentStmt, ConstraintsSetStmt,
+		CopyStmt, CreateAsStmt, CreateGroupStmt, CreatePLangStmt,
+		CreateSchemaStmt, CreateSeqStmt, CreateStmt, CreateTrigStmt,
+		CreateUserStmt, CreatedbStmt, CursorStmt, DefineStmt, DeleteStmt,
+		DropGroupStmt, DropPLangStmt, DropSchemaStmt, DropStmt, DropTrigStmt,
+		DropUserStmt, DropdbStmt, ExplainStmt, ExtendStmt, FetchStmt,
+		GrantStmt, IndexStmt, InsertStmt, ListenStmt, LoadStmt, LockStmt,
+		NotifyStmt, OptimizableStmt, ProcedureStmt, ReindexStmt,
+		RemoveAggrStmt, RemoveFuncStmt, RemoveOperStmt, RemoveStmt,
+		RenameStmt, RevokeStmt, RuleActionStmt, RuleActionStmtOrEmpty,
+		RuleStmt, SelectStmt, SetSessionStmt, TransactionStmt, TruncateStmt,
+		UnlistenStmt, UpdateStmt, VacuumStmt, VariableResetStmt,
+		VariableSetStmt, VariableShowStmt, ViewStmt
+
+%type <node>	select_clause, select_subclause
 
 %type <list>	SessionList
 %type <node>	SessionClause
@@ -212,7 +214,7 @@ static void doNegateFloat(Value *v);
 %type <list>	OptSeqList
 %type <defelt>	OptSeqElem
 
-%type <astmt>	insert_rest
+%type <istmt>	insert_rest
 
 %type <node>	OptTableElement, ConstraintElem
 %type <node>	columnDef
@@ -1533,16 +1535,16 @@ OptInherit:  INHERITS '(' relation_name_list ')'		{ $$ = $3; }
 
 CreateAsStmt:  CREATE OptTemp TABLE relation_name OptUnder OptCreateAs AS SelectStmt
 				{
-					SelectStmt *n = (SelectStmt *)$8;
-                    if ($5 != NIL)
-						yyerror("CREATE TABLE/AS SELECT does not support UNDER");
-					if ($6 != NIL)
-						mapTargetColumns($6, n->targetList);
+					SelectStmt *n = findLeftmostSelect($8);
 					if (n->into != NULL)
 						elog(ERROR,"CREATE TABLE/AS SELECT may not specify INTO");
 					n->istemp = $2;
 					n->into = $4;
-					$$ = (Node *)n;
+                    if ($5 != NIL)
+						yyerror("CREATE TABLE/AS SELECT does not support UNDER");
+					if ($6 != NIL)
+						mapTargetColumns($6, n->targetList);
+					$$ = $8;
 				}
 		;
 
@@ -2909,11 +2911,7 @@ ViewStmt:  CREATE VIEW name opt_column_list AS SelectStmt
 					ViewStmt *n = makeNode(ViewStmt);
 					n->viewname = $3;
 					n->aliases = $4;
-					n->query = (Query *)$6;
-					if (((SelectStmt *)n->query)->unionClause != NULL)
-						elog(ERROR,"UNION in views is not implemented");
-					if (((SelectStmt *)n->query)->forUpdate != NULL)
-						elog(ERROR, "SELECT FOR UPDATE is not allowed in CREATE VIEW");
+					n->query = (Query *) $6;
 					$$ = (Node *)n;
 				}
 		;
@@ -3156,78 +3154,37 @@ InsertStmt:  INSERT INTO relation_name insert_rest
 insert_rest:  VALUES '(' target_list ')'
 				{
 					$$ = makeNode(InsertStmt);
-					$$->cols = NULL;
-					$$->distinctClause = NIL;
+					$$->cols = NIL;
 					$$->targetList = $3;
-					$$->fromClause = NIL;
-					$$->whereClause = NULL;
-					$$->groupClause = NIL;
-					$$->havingClause = NULL;
-					$$->unionClause = NIL;
+					$$->selectStmt = NULL;
 				}
 		| DEFAULT VALUES
 				{
 					$$ = makeNode(InsertStmt);
-					$$->distinctClause = NIL;
+					$$->cols = NIL;
 					$$->targetList = NIL;
-					$$->fromClause = NIL;
-					$$->whereClause = NULL;
-					$$->groupClause = NIL;
-					$$->havingClause = NULL;
-					$$->unionClause = NIL;
-				 	$$->intersectClause = NIL;
-				}
-/* We want the full power of SelectStatements including INTERSECT and EXCEPT
- * for insertion.  However, we can't support sort or limit clauses.
- */
+					$$->selectStmt = NULL;
+				}
 		| SelectStmt
 				{
-					SelectStmt *n = (SelectStmt *) $1;
-					if (n->sortClause)
-						elog(ERROR, "ORDER BY is not allowed in INSERT/SELECT");
 					$$ = makeNode(InsertStmt);
 					$$->cols = NIL;
-					$$->distinctClause = n->distinctClause;
-					$$->targetList = n->targetList;
-					$$->fromClause = n->fromClause;
-					$$->whereClause = n->whereClause;
-					$$->groupClause = n->groupClause;
-					$$->havingClause = n->havingClause;
-					$$->unionClause = n->unionClause;
-					$$->intersectClause = n->intersectClause;
-					$$->unionall = n->unionall;
-					$$->forUpdate = n->forUpdate;
+					$$->targetList = NIL;
+					$$->selectStmt = $1;
 				}
 		| '(' columnList ')' VALUES '(' target_list ')'
 				{
 					$$ = makeNode(InsertStmt);
 					$$->cols = $2;
-					$$->distinctClause = NIL;
 					$$->targetList = $6;
-					$$->fromClause = NIL;
-					$$->whereClause = NULL;
-					$$->groupClause = NIL;
-					$$->havingClause = NULL;
-					$$->unionClause = NIL; 
-				 	$$->intersectClause = NIL;
+					$$->selectStmt = NULL;
 				}
 		| '(' columnList ')' SelectStmt
 				{
-					SelectStmt *n = (SelectStmt *) $4;
-					if (n->sortClause)
-						elog(ERROR, "ORDER BY is not allowed in INSERT/SELECT");
 					$$ = makeNode(InsertStmt);
 					$$->cols = $2;
-					$$->distinctClause = n->distinctClause;
-					$$->targetList = n->targetList;
-					$$->fromClause = n->fromClause;
-					$$->whereClause = n->whereClause;
-					$$->groupClause = n->groupClause;
-					$$->havingClause = n->havingClause;
-					$$->unionClause = n->unionClause;
-					$$->intersectClause = n->intersectClause;
-					$$->unionall = n->unionall;
-					$$->forUpdate = n->forUpdate;
+					$$->targetList = NIL;
+					$$->selectStmt = $4;
 				}
 		;
 
@@ -3324,26 +3281,10 @@ UpdateStmt:  UPDATE opt_only relation_name
  *****************************************************************************/
 CursorStmt:  DECLARE name opt_cursor CURSOR FOR SelectStmt
   				{
- 					SelectStmt *n;
-  
- 					n= (SelectStmt *)$6;
-  					/* from PORTAL name */
-  					/*
-  					 *	15 august 1991 -- since 3.0 postgres does locking
-					 *	right, we discovered that portals were violating
-					 *	locking protocol.  portal locks cannot span xacts.
-					 *	as a short-term fix, we installed the check here.
-					 *							-- mao
-					 */
-					if (!IsTransactionBlock())
-						elog(ERROR,"Named portals may only be used in begin/end transaction blocks");
-
+ 					SelectStmt *n = findLeftmostSelect($6);
 					n->portalname = $2;
 					n->binary = $3;
-					if (n->forUpdate != NULL)
-							elog(ERROR,"DECLARE/UPDATE is not supported"
-								 		"\n\tCursors must be READ ONLY");
-					$$ = (Node *)n;
+					$$ = $6;
 				}
 		;
 
@@ -3364,92 +3305,22 @@ opt_cursor:  BINARY						{ $$ = TRUE; }
 /* A complete SELECT statement looks like this.  Note sort, for_update,
  * and limit clauses can only appear once, not in each set operation.
  * 
- * The rule returns a SelectStmt Node having the set operations attached to 
- * unionClause and intersectClause (NIL if no set operations were present)
+ * The rule returns either a SelectStmt node or a SetOperationStmt tree.
+ * One-time clauses are attached to the leftmost SelectStmt leaf.
+ *
+ * NOTE: only the leftmost SelectStmt leaf should have INTO, either.
+ * However, this is not checked by the grammar; parse analysis must check it.
  */
 
 SelectStmt:	  select_clause sort_clause for_update_clause opt_select_limit
 			{
-				if IsA($1, SelectStmt)
-				{
-					/* There were no set operations, so just attach the
-					 * one-time clauses.
-					 */
-					SelectStmt *n = (SelectStmt *) $1;
-					n->sortClause = $2;
-					n->forUpdate = $3;
-					n->limitOffset = nth(0, $4);
-					n->limitCount = nth(1, $4);
-					$$ = (Node *) n;
-                }
-				else
-				{
-					/* There were set operations.  The root of the operator
-					 * tree is delivered by $1, but we must hand back a
-					 * SelectStmt node not an A_Expr Node.
-					 * So we find the leftmost 'SelectStmt' in the operator
-					 * tree $1 (which is the first Select Statement in the
-					 * query), which will be the returned node.
-					 * Then we attach the whole operator tree to that node's
-					 * 'intersectClause', and a list of all 'SelectStmt' Nodes
-					 * in the tree to its 'unionClause'. (NOTE that this means
-					 * the top node has indirect recursive pointers to itself!
-					 * This would cause trouble if we tried copyObject!!)
-					 * The intersectClause and unionClause subtrees will be
-					 * left untouched by the main parser, and will only be
-					 * processed when control gets to the function
-					 * Except_Intersect_Rewrite() (in rewriteHandler.c).
-					 */
-					Node *op = (Node *) $1;
-					List *select_list = NIL;
-					SelectStmt *first_select;
-					bool	intersect_present = FALSE,
-						unionall_present = FALSE;
-
-					/* Take the operator tree as an argument and create a
-					 * list of all SelectStmt Nodes found in the tree.
-					 *
-					 * If one of the SelectStmt Nodes has the 'unionall' flag
-					 * set to true the 'unionall_present' flag is also set to
-					 * true.
-					 */
-					create_select_list(op, &select_list, &unionall_present);
-
-					/* Replace all the A_Expr Nodes in the operator tree by
-					 * Expr Nodes.
-					 *
-					 * If an INTERSECT or an EXCEPT is present, the 
-					 * 'intersect_present' flag is set to true
-					 */
-					op = A_Expr_to_Expr(op, &intersect_present);
+				SelectStmt *n = findLeftmostSelect($1);
 
-					/* If both flags are set to true we have a UNION ALL
-					 * statement mixed up with INTERSECT or EXCEPT 
-					 * which can not be handled at the moment.
-					 */
-					if (intersect_present && unionall_present)
-						elog(ERROR, "UNION ALL not allowed in mixed set operations");
-
-					/* Get the leftmost SeletStmt Node (which automatically
-					 * represents the first Select Statement of the query!)
-					 */
-					first_select = (SelectStmt *) lfirst(select_list);
-
-					/* Attach the list of all SeletStmt Nodes to unionClause */
-					first_select->unionClause = select_list;
-
-					/* Attach the whole operator tree to intersectClause */
-					first_select->intersectClause = (List *) op;
-
-					/* finally attach the sort clause &etc */
-					first_select->sortClause = $2;
-					first_select->forUpdate = $3;
-					first_select->limitOffset = nth(0, $4);
-					first_select->limitCount = nth(1, $4);
-					$$ = (Node *) first_select;
-				}		
-				if (((SelectStmt *)$$)->forUpdate != NULL && QueryIsRule)
-					elog(ERROR, "SELECT/FOR UPDATE is not allowed in CREATE RULE");
+				n->sortClause = $2;
+				n->forUpdate = $3;
+				n->limitOffset = nth(0, $4);
+				n->limitCount = nth(1, $4);
+				$$ = $1;
 			}
 		;
 
@@ -3458,82 +3329,69 @@ SelectStmt:	  select_clause sort_clause for_update_clause opt_select_limit
  * the ordering of the set operations.  Without '(' and ')' we want the
  * operations to be ordered per the precedence specs at the head of this file.
  *
+ * Since parentheses around SELECTs also appear in the expression grammar,
+ * there is a parse ambiguity if parentheses are allowed at the top level of a
+ * select_clause: are the parens part of the expression or part of the select?
+ * We separate select_clause into two levels to resolve this: select_clause
+ * can have top-level parentheses, select_subclause cannot.
+ *
  * Note that sort clauses cannot be included at this level --- a sort clause
  * can only appear at the end of the complete Select, and it will be handled
  * by the topmost SelectStmt rule.  Likewise FOR UPDATE and LIMIT.
- * 
- * The rule builds up an operator tree using A_Expr Nodes. AND Nodes represent
- * INTERSECTs, OR Nodes represent UNIONs, and AND NOT nodes represent EXCEPTs. 
- * The SelectStatements to be connected are the left and right arguments to
- * the A_Expr Nodes.
- * If no set operations appear in the query, the tree consists only of one
- * SelectStmt Node.
  */
-select_clause: '(' select_clause ')'
+select_clause: '(' select_subclause ')'
 			{
 				$$ = $2; 
 			}
-		| SubSelect
+		| select_subclause
 			{
 				$$ = $1; 
 			}
-		| select_clause EXCEPT opt_all select_clause
-			{
-				$$ = (Node *)makeA_Expr(AND,NULL,$1,
-										makeA_Expr(NOT,NULL,NULL,$4));
-				if ($3)
-					elog(ERROR, "EXCEPT ALL is not implemented yet");
-			}
-		| select_clause UNION opt_all select_clause
-			{	
-				if (IsA($4, SelectStmt))
-				{
-					SelectStmt *n = (SelectStmt *)$4;
-					n->unionall = $3;
-					/* NOTE: if UNION ALL appears with a parenthesized set
-					 * operation to its right, the ALL is silently discarded.
-					 * Should we generate an error instead?  I think it may
-					 * be OK since ALL with UNION to its right is ignored
-					 * anyway...
-					 */
-				}
-				$$ = (Node *)makeA_Expr(OR,NULL,$1,$4);
-			}
-		| select_clause INTERSECT opt_all select_clause
-			{
-				$$ = (Node *)makeA_Expr(AND,NULL,$1,$4);
-				if ($3)
-					elog(ERROR, "INTERSECT ALL is not implemented yet");
-			}
-		; 
+		;
 
-SubSelect:	SELECT opt_distinct target_list
+select_subclause: SELECT opt_distinct target_list
 			 result from_clause where_clause
 			 group_clause having_clause
 				{
 					SelectStmt *n = makeNode(SelectStmt);
 					n->distinctClause = $2;
-					n->unionall = FALSE;
 					n->targetList = $3;
-					/* This is new: Subselects support the INTO clause
-					 * which allows queries that are not part of the
-					 * SQL92 standard and should not be formulated!
-					 * We need it for INTERSECT and EXCEPT and I did not
-					 * want to create a new rule 'SubSelect1' including the
-					 * feature. If it makes troubles we will have to add 
-					 * a new rule and change this to prevent INTOs in 
-					 * Subselects again.
-					 */
 					n->istemp = (bool) ((Value *) lfirst($4))->val.ival;
 					n->into = (char *) lnext($4);
-
 					n->fromClause = $5;
 					n->whereClause = $6;
 					n->groupClause = $7;
 					n->havingClause = $8;
 					$$ = (Node *)n;
 				}
-		;
+		| select_clause UNION opt_all select_clause
+			{	
+				SetOperationStmt *n = makeNode(SetOperationStmt);
+				n->op = SETOP_UNION;
+				n->all = $3;
+				n->larg = $1;
+				n->rarg = $4;
+				$$ = (Node *) n;
+			}
+		| select_clause INTERSECT opt_all select_clause
+			{
+				SetOperationStmt *n = makeNode(SetOperationStmt);
+				n->op = SETOP_INTERSECT;
+				n->all = $3;
+				n->larg = $1;
+				n->rarg = $4;
+				$$ = (Node *) n;
+			}
+		| select_clause EXCEPT opt_all select_clause
+			{
+				SetOperationStmt *n = makeNode(SetOperationStmt);
+				n->op = SETOP_EXCEPT;
+				n->all = $3;
+				n->larg = $1;
+				n->rarg = $4;
+				$$ = (Node *) n;
+			}
+		; 
 
 		/* easy way to return two values. Can someone improve this?  bjm */
 result:  INTO OptTempTableName			{ $$ = $2; }
@@ -3763,7 +3621,7 @@ table_ref:  relation_expr
 					$1->name = $2;
 					$$ = (Node *) $1;
 				}
-		| '(' select_clause ')' alias_clause
+		| '(' select_subclause ')' alias_clause
 				{
 					RangeSubselect *n = makeNode(RangeSubselect);
 					n->subquery = $2;
@@ -4316,7 +4174,7 @@ opt_interval:  datetime							{ $$ = makeList1($1); }
  * Define row_descriptor to allow yacc to break the reduce/reduce conflict
  *  with singleton expressions.
  */
-row_expr: '(' row_descriptor ')' IN '(' SubSelect ')'
+row_expr: '(' row_descriptor ')' IN '(' select_subclause ')'
 				{
 					SubLink *n = makeNode(SubLink);
 					n->lefthand = $2;
@@ -4326,7 +4184,7 @@ row_expr: '(' row_descriptor ')' IN '(' SubSelect ')'
 					n->subselect = $6;
 					$$ = (Node *)n;
 				}
-		| '(' row_descriptor ')' NOT IN '(' SubSelect ')'
+		| '(' row_descriptor ')' NOT IN '(' select_subclause ')'
 				{
 					SubLink *n = makeNode(SubLink);
 					n->lefthand = $2;
@@ -4336,7 +4194,7 @@ row_expr: '(' row_descriptor ')' IN '(' SubSelect ')'
 					n->subselect = $7;
 					$$ = (Node *)n;
 				}
-		| '(' row_descriptor ')' all_Op sub_type '(' SubSelect ')'
+		| '(' row_descriptor ')' all_Op sub_type '(' select_subclause ')'
 				{
 					SubLink *n = makeNode(SubLink);
 					n->lefthand = $2;
@@ -4349,7 +4207,7 @@ row_expr: '(' row_descriptor ')' IN '(' SubSelect ')'
 					n->subselect = $7;
 					$$ = (Node *)n;
 				}
-		| '(' row_descriptor ')' all_Op '(' SubSelect ')'
+		| '(' row_descriptor ')' all_Op '(' select_subclause ')'
 				{
 					SubLink *n = makeNode(SubLink);
 					n->lefthand = $2;
@@ -4680,7 +4538,7 @@ a_expr:  c_expr
 						$$ = n;
 					}
 				}
-		| a_expr all_Op sub_type '(' SubSelect ')'
+		| a_expr all_Op sub_type '(' select_subclause ')'
 				{
 					SubLink *n = makeNode(SubLink);
 					n->lefthand = makeList1($1);
@@ -5076,7 +4934,7 @@ c_expr:  attr
 					n->agg_distinct = FALSE;
 					$$ = (Node *)n;
 				}
-		| '(' SubSelect ')'
+		| '(' select_subclause ')'
 				{
 					SubLink *n = makeNode(SubLink);
 					n->lefthand = NIL;
@@ -5086,7 +4944,7 @@ c_expr:  attr
 					n->subselect = $2;
 					$$ = (Node *)n;
 				}
-		| EXISTS '(' SubSelect ')'
+		| EXISTS '(' select_subclause ')'
 				{
 					SubLink *n = makeNode(SubLink);
 					n->lefthand = NIL;
@@ -5185,7 +5043,7 @@ trim_list:  a_expr FROM expr_list
 				{ $$ = $1; }
 		;
 
-in_expr:  SubSelect
+in_expr:  select_subclause
 				{
 					SubLink *n = makeNode(SubLink);
 					n->subselect = $1;
@@ -5912,6 +5770,19 @@ mapTargetColumns(List *src, List *dst)
 } /* mapTargetColumns() */
 
 
+/* findLeftmostSelect()
+ *		Find the leftmost SelectStmt in a SetOperationStmt parsetree.
+ */
+static SelectStmt *
+findLeftmostSelect(Node *node)
+{
+	while (node && IsA(node, SetOperationStmt))
+		node = ((SetOperationStmt *) node)->larg;
+	Assert(node && IsA(node, SelectStmt));
+	return (SelectStmt *) node;
+}
+
+
 /* xlateSqlFunc()
  * Convert alternate function names to internal Postgres functions.
  *
diff --git a/src/backend/parser/parse_clause.c b/src/backend/parser/parse_clause.c
index cc849ebf07b..20233ed1950 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.68 2000/09/29 18:21:36 tgl Exp $
+ *	  $Header: /cvsroot/pgsql/src/backend/parser/parse_clause.c,v 1.69 2000/10/05 19:11:33 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -347,7 +347,8 @@ transformTableEntry(ParseState *pstate, RangeVar *r)
 static RangeTblRef *
 transformRangeSubselect(ParseState *pstate, RangeSubselect *r)
 {
-	SelectStmt *subquery = (SelectStmt *) r->subquery;
+	List	   *save_rtable;
+	List	   *save_joinlist;
 	List	   *parsetrees;
 	Query	   *query;
 	RangeTblEntry *rte;
@@ -362,19 +363,21 @@ transformRangeSubselect(ParseState *pstate, RangeSubselect *r)
 		elog(ERROR, "sub-select in FROM must have an alias");
 
 	/*
-	 * subquery node might not be SelectStmt if user wrote something like
-	 * FROM (SELECT ... UNION SELECT ...).  Our current implementation of
-	 * UNION/INTERSECT/EXCEPT is too messy to deal with here, so punt until
-	 * we redesign querytrees to make it more reasonable.
+	 * Analyze and transform the subquery.  This is a bit tricky because
+	 * we don't want the subquery to be able to see any FROM items already
+	 * created in the current query (per SQL92, the scope of a FROM item
+	 * does not include other FROM items).  But it does need to be able to
+	 * see any further-up parent states, so we can't just pass a null parent
+	 * pstate link.  So, temporarily make the current query level have an
+	 * empty rtable and joinlist.
 	 */
-	if (subquery == NULL || !IsA(subquery, SelectStmt))
-		elog(ERROR, "Set operations not yet supported in subselects in FROM");
-
-	/*
-	 * Analyze and transform the subquery as if it were an independent
-	 * statement (we do NOT want it to see the outer query as a parent).
-	 */
-	parsetrees = parse_analyze(makeList1(subquery), NULL);
+	save_rtable = pstate->p_rtable;
+	save_joinlist = pstate->p_joinlist;
+	pstate->p_rtable = NIL;
+	pstate->p_joinlist = NIL;
+	parsetrees = parse_analyze(makeList1(r->subquery), pstate);
+	pstate->p_rtable = save_rtable;
+	pstate->p_joinlist = save_joinlist;
 
 	/*
 	 * Check that we got something reasonable.  Some of these conditions
@@ -1181,108 +1184,3 @@ exprIsInSortList(Node *expr, List *sortList, List *targetList)
 	}
 	return false;
 }
-
-/* transformUnionClause()
- * Transform a UNION clause.
- * Note that the union clause is actually a fully-formed select structure.
- * So, it is evaluated as a select, then the resulting target fields
- *	are matched up to ensure correct types in the results.
- * The select clause parsing is done recursively, so the unions are evaluated
- *	right-to-left. One might want to look at all columns from all clauses before
- *	trying to coerce, but unless we keep track of the call depth we won't know
- *	when to do this because of the recursion.
- * Let's just try matching in pairs for now (right to left) and see if it works.
- * - thomas 1998-05-22
- */
-#ifdef NOT_USED
-static List *
-transformUnionClause(List *unionClause, List *targetlist)
-{
-	List	   *union_list = NIL;
-	List	   *qlist,
-			   *qlist_item;
-
-	if (unionClause)
-	{
-		/* recursion */
-		qlist = parse_analyze(unionClause, NULL);
-
-		foreach(qlist_item, qlist)
-		{
-			Query	   *query = (Query *) lfirst(qlist_item);
-			List	   *prev_target = targetlist;
-			List	   *next_target;
-			int			prev_len = 0,
-						next_len = 0;
-
-			foreach(prev_target, targetlist)
-				if (!((TargetEntry *) lfirst(prev_target))->resdom->resjunk)
-				prev_len++;
-
-			foreach(next_target, query->targetList)
-				if (!((TargetEntry *) lfirst(next_target))->resdom->resjunk)
-				next_len++;
-
-			if (prev_len != next_len)
-				elog(ERROR, "Each UNION clause must have the same number of columns");
-
-			foreach(next_target, query->targetList)
-			{
-				Oid			itype;
-				Oid			otype;
-
-				otype = ((TargetEntry *) lfirst(prev_target))->resdom->restype;
-				itype = ((TargetEntry *) lfirst(next_target))->resdom->restype;
-
-				/* one or both is a NULL column? then don't convert... */
-				if (otype == InvalidOid)
-				{
-					/* propagate a known type forward, if available */
-					if (itype != InvalidOid)
-						((TargetEntry *) lfirst(prev_target))->resdom->restype = itype;
-#if FALSE
-					else
-					{
-						((TargetEntry *) lfirst(prev_target))->resdom->restype = UNKNOWNOID;
-						((TargetEntry *) lfirst(next_target))->resdom->restype = UNKNOWNOID;
-					}
-#endif
-				}
-				else if (itype == InvalidOid)
-				{
-				}
-				/* they don't match in type? then convert... */
-				else if (itype != otype)
-				{
-					Node	   *expr;
-
-					expr = ((TargetEntry *) lfirst(next_target))->expr;
-					expr = CoerceTargetExpr(NULL, expr, itype, otype, -1);
-					if (expr == NULL)
-					{
-						elog(ERROR, "Unable to transform %s to %s"
-							 "\n\tEach UNION clause must have compatible target types",
-							 typeidTypeName(itype),
-							 typeidTypeName(otype));
-					}
-					((TargetEntry *) lfirst(next_target))->expr = expr;
-					((TargetEntry *) lfirst(next_target))->resdom->restype = otype;
-				}
-
-				/* both are UNKNOWN? then evaluate as text... */
-				else if (itype == UNKNOWNOID)
-				{
-					((TargetEntry *) lfirst(next_target))->resdom->restype = TEXTOID;
-					((TargetEntry *) lfirst(prev_target))->resdom->restype = TEXTOID;
-				}
-				prev_target = lnext(prev_target);
-			}
-			union_list = lappend(union_list, query);
-		}
-		return union_list;
-	}
-	else
-		return NIL;
-}
-
-#endif
diff --git a/src/backend/parser/parse_coerce.c b/src/backend/parser/parse_coerce.c
index bd098fb6c68..ef13d67cf1c 100644
--- a/src/backend/parser/parse_coerce.c
+++ b/src/backend/parser/parse_coerce.c
@@ -8,7 +8,7 @@
  *
  *
  * IDENTIFICATION
- *	  $Header: /cvsroot/pgsql/src/backend/parser/parse_coerce.c,v 2.46 2000/07/30 22:13:50 tgl Exp $
+ *	  $Header: /cvsroot/pgsql/src/backend/parser/parse_coerce.c,v 2.47 2000/10/05 19:11:33 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -314,6 +314,100 @@ coerce_type_typmod(ParseState *pstate, Node *node,
 }
 
 
+/* select_common_type()
+ *		Determine the common supertype of a list of input expression types.
+ *		This is used for determining the output type of CASE and UNION
+ *		constructs.
+ *
+ * typeids is a nonempty integer list of type OIDs.  Note that earlier items
+ * in the list will be preferred if there is doubt.
+ * 'context' is a phrase to use in the error message if we fail to select
+ * a usable type.
+ *
+ * XXX this code is WRONG, since (for example) given the input (int4,int8)
+ * it will select int4, whereas according to SQL92 clause 9.3 the correct
+ * answer is clearly int8.  To fix this we need a notion of a promotion
+ * hierarchy within type categories --- something more complete than
+ * just a single preferred type.
+ */
+Oid
+select_common_type(List *typeids, const char *context)
+{
+	Oid			ptype;
+	CATEGORY	pcategory;
+	List	   *l;
+
+	Assert(typeids != NIL);
+	ptype = (Oid) lfirsti(typeids);
+	pcategory = TypeCategory(ptype);
+	foreach(l, lnext(typeids))
+	{
+		Oid		ntype = (Oid) lfirsti(l);
+
+		/* move on to next one if no new information... */
+		if (ntype && (ntype != UNKNOWNOID) && (ntype != ptype))
+		{
+			if (!ptype || ptype == UNKNOWNOID)
+			{
+				/* so far, only nulls so take anything... */
+				ptype = ntype;
+				pcategory = TypeCategory(ptype);
+			}
+			else if (TypeCategory(ntype) != pcategory)
+			{
+				/*
+				 * both types in different categories? then
+				 * not much hope...
+				 */
+				elog(ERROR, "%s types \"%s\" and \"%s\" not matched",
+					 context, typeidTypeName(ptype), typeidTypeName(ntype));
+			}
+			else if (IsPreferredType(pcategory, ntype)
+					 && can_coerce_type(1, &ptype, &ntype))
+			{
+				/*
+				 * new one is preferred and can convert? then
+				 * take it...
+				 */
+				ptype = ntype;
+				pcategory = TypeCategory(ptype);
+			}
+		}
+	}
+	return ptype;
+}
+
+/* coerce_to_common_type()
+ *		Coerce an expression to the given type.
+ *
+ * This is used following select_common_type() to coerce the individual
+ * expressions to the desired type.  'context' is a phrase to use in the
+ * error message if we fail to coerce.
+ *
+ * NOTE: pstate may be NULL.
+ */
+Node *
+coerce_to_common_type(ParseState *pstate, Node *node,
+					  Oid targetTypeId,
+					  const char *context)
+{
+	Oid			inputTypeId = exprType(node);
+
+	if (inputTypeId == targetTypeId)
+		return node;			/* no work */
+	if (can_coerce_type(1, &inputTypeId, &targetTypeId))
+	{
+		node = coerce_type(pstate, node, inputTypeId, targetTypeId, -1);
+	}
+	else
+	{
+		elog(ERROR, "%s unable to convert to type \"%s\"",
+			 context, typeidTypeName(targetTypeId));
+	}
+	return node;
+}
+
+
 /* TypeCategory()
  * Assign a category to the specified OID.
  */
diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c
index 591fdab8782..7b647124d1f 100644
--- a/src/backend/parser/parse_expr.c
+++ b/src/backend/parser/parse_expr.c
@@ -8,7 +8,7 @@
  *
  *
  * IDENTIFICATION
- *	  $Header: /cvsroot/pgsql/src/backend/parser/parse_expr.c,v 1.84 2000/09/29 18:21:36 tgl Exp $
+ *	  $Header: /cvsroot/pgsql/src/backend/parser/parse_expr.c,v 1.85 2000/10/05 19:11:33 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -412,9 +412,9 @@ transformExpr(ParseState *pstate, Node *expr, int precedence)
 			{
 				CaseExpr   *c = (CaseExpr *) expr;
 				CaseWhen   *w;
+				List	   *typeids = NIL;
 				List	   *args;
 				Oid			ptype;
-				CATEGORY	pcategory;
 
 				/* transform the list of arguments */
 				foreach(args, c->args)
@@ -432,6 +432,7 @@ transformExpr(ParseState *pstate, Node *expr, int precedence)
 						w->expr = (Node *) a;
 					}
 					lfirst(args) = transformExpr(pstate, (Node *) w, precedence);
+					typeids = lappendi(typeids, exprType(w->result));
 				}
 
 				/*
@@ -452,104 +453,26 @@ transformExpr(ParseState *pstate, Node *expr, int precedence)
 					c->defresult = (Node *) n;
 				}
 				c->defresult = transformExpr(pstate, c->defresult, precedence);
+				/*
+				 * Note: default result is considered the most significant
+				 * type in determining preferred type.  This is how the code
+				 * worked before, but it seems a little bogus to me --- tgl
+				 */
+				typeids = lconsi(exprType(c->defresult), typeids);
 
-				/* now check types across result clauses... */
-				c->casetype = exprType(c->defresult);
-				ptype = c->casetype;
-				pcategory = TypeCategory(ptype);
-				foreach(args, c->args)
-				{
-					Oid			wtype;
-
-					w = lfirst(args);
-					wtype = exprType(w->result);
-					/* move on to next one if no new information... */
-					if (wtype && (wtype != UNKNOWNOID)
-						&& (wtype != ptype))
-					{
-						if (!ptype || ptype == UNKNOWNOID)
-						{
-							/* so far, only nulls so take anything... */
-							ptype = wtype;
-							pcategory = TypeCategory(ptype);
-						}
-						else if ((TypeCategory(wtype) != pcategory)
-								 || ((TypeCategory(wtype) == USER_TYPE)
-							&& (TypeCategory(c->casetype) == USER_TYPE)))
-						{
-
-							/*
-							 * both types in different categories? then
-							 * not much hope...
-							 */
-							elog(ERROR, "CASE/WHEN types '%s' and '%s' not matched",
-								 typeidTypeName(c->casetype), typeidTypeName(wtype));
-						}
-						else if (IsPreferredType(pcategory, wtype)
-								 && can_coerce_type(1, &ptype, &wtype))
-						{
-
-							/*
-							 * new one is preferred and can convert? then
-							 * take it...
-							 */
-							ptype = wtype;
-							pcategory = TypeCategory(ptype);
-						}
-					}
-				}
+				ptype = select_common_type(typeids, "CASE");
+				c->casetype = ptype;
 
 				/* Convert default result clause, if necessary */
-				if (c->casetype != ptype)
-				{
-					if (!c->casetype || c->casetype == UNKNOWNOID)
-					{
+				c->defresult = coerce_to_common_type(pstate, c->defresult,
+													 ptype, "CASE/ELSE");
 
-						/*
-						 * default clause is NULL, so assign preferred
-						 * type from WHEN clauses...
-						 */
-						c->casetype = ptype;
-					}
-					else if (can_coerce_type(1, &c->casetype, &ptype))
-					{
-						c->defresult = coerce_type(pstate, c->defresult,
-												 c->casetype, ptype, -1);
-						c->casetype = ptype;
-					}
-					else
-					{
-						elog(ERROR, "CASE/ELSE unable to convert to type '%s'",
-							 typeidTypeName(ptype));
-					}
-				}
-
-				/* Convert when clauses, if not null and if necessary */
+				/* Convert when-clause results, if necessary */
 				foreach(args, c->args)
 				{
-					Oid			wtype;
-
 					w = lfirst(args);
-					wtype = exprType(w->result);
-
-					/*
-					 * only bother with conversion if not NULL and
-					 * different type...
-					 */
-					if (wtype && (wtype != UNKNOWNOID)
-						&& (wtype != ptype))
-					{
-						if (can_coerce_type(1, &wtype, &ptype))
-						{
-							w->result = coerce_type(pstate, w->result, wtype,
-													ptype, -1);
-						}
-						else
-						{
-							elog(ERROR, "CASE/WHEN unable to convert to type '%s'",
-								 typeidTypeName(ptype));
-						}
-					}
+					w->result = coerce_to_common_type(pstate, w->result,
+													  ptype, "CASE/WHEN");
 				}
 
 				result = expr;
@@ -560,7 +483,7 @@ transformExpr(ParseState *pstate, Node *expr, int precedence)
 			{
 				CaseWhen   *w = (CaseWhen *) expr;
 
-				w->expr = transformExpr(pstate, (Node *) w->expr, precedence);
+				w->expr = transformExpr(pstate, w->expr, precedence);
 				if (exprType(w->expr) != BOOLOID)
 					elog(ERROR, "WHEN clause must have a boolean result");
 
@@ -575,7 +498,7 @@ transformExpr(ParseState *pstate, Node *expr, int precedence)
 					n->val.type = T_Null;
 					w->result = (Node *) n;
 				}
-				w->result = transformExpr(pstate, (Node *) w->result, precedence);
+				w->result = transformExpr(pstate, w->result, precedence);
 				result = expr;
 				break;
 			}