Fix optimizer to not try to push WHERE clauses down into a sub-SELECT that

has a DISTINCT ON clause, per bug report from Anthony Wood.  While at it,
improve the DISTINCT-ON-clause recognizer routine to not be fooled by out-
of-order DISTINCT lists.
Also, back-patch earlier fix to not push down into sub-SELECT with LIMIT.

2 files changed, 62 insertions, 4 deletions

diff --git a/src/backend/optimizer/path/allpaths.c b/src/backend/optimizer/path/allpaths.c
index 1cf73dffff7..820a0e6e26f 100644
--- a/src/backend/optimizer/path/allpaths.c
+++ b/src/backend/optimizer/path/allpaths.c
@@ -8,7 +8,7 @@
  *
  *
  * IDENTIFICATION
- *	  $Header: /cvsroot/pgsql/src/backend/optimizer/path/allpaths.c,v 1.72 2001/03/22 03:59:34 momjian Exp $
+ *	  $Header: /cvsroot/pgsql/src/backend/optimizer/path/allpaths.c,v 1.72.2.1 2001/07/31 18:39:12 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -125,11 +125,17 @@ set_base_rel_pathlists(Query *root)
 			 * Non-pushed-down clauses will get evaluated as qpquals of
 			 * the SubqueryScan node.
 			 *
+			 * We can't push down into subqueries with LIMIT or DISTINCT ON
+			 * clauses, either.
+			 *
 			 * XXX Are there any cases where we want to make a policy
 			 * decision not to push down, because it'd result in a worse
 			 * plan?
 			 */
-			if (rte->subquery->setOperations == NULL)
+			if (rte->subquery->setOperations == NULL &&
+				rte->subquery->limitOffset == NULL &&
+				rte->subquery->limitCount == NULL &&
+				!has_distinct_on_clause(rte->subquery))
 			{
 				/* OK to consider pushing down individual quals */
 				List	   *upperrestrictlist = NIL;
diff --git a/src/backend/optimizer/util/clauses.c b/src/backend/optimizer/util/clauses.c
index 8bd6ef6f68b..5e2a7b63dca 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.84 2001/03/27 17:12:34 tgl Exp $
+ *	  $Header: /cvsroot/pgsql/src/backend/optimizer/util/clauses.c,v 1.84.2.1 2001/07/31 18:39:12 tgl Exp $
  *
  * HISTORY
  *	  AUTHOR			DATE			MAJOR EVENT
@@ -730,13 +730,65 @@ pull_constant_clauses(List *quals, List **constantQual)
 
 
 /*****************************************************************************
+ *		Tests on clauses of queries
+ *
+ * Possibly this code should go someplace else, since this isn't quite the
+ * same meaning of "clause" as is used elsewhere in this module.  But I can't
+ * think of a better place for it...
+ *****************************************************************************/
+
+/*
+ * Test whether a query uses DISTINCT ON, ie, has a distinct-list that is
+ * just a subset of the output columns.
+ */
+bool
+has_distinct_on_clause(Query *query)
+{
+	List   *targetList;
+
+	/* Is there a DISTINCT clause at all? */
+	if (query->distinctClause == NIL)
+		return false;
+	/*
+	 * If the DISTINCT list contains all the nonjunk targetlist items,
+	 * then it's a simple DISTINCT, else it's DISTINCT ON.  We do not
+	 * require the lists to be in the same order (since the parser may
+	 * have adjusted the DISTINCT clause ordering to agree with ORDER BY).
+	 */
+	foreach(targetList, query->targetList)
+	{
+		TargetEntry *tle = (TargetEntry *) lfirst(targetList);
+		Index		ressortgroupref;
+		List	   *distinctClause;
+
+		if (tle->resdom->resjunk)
+			continue;
+		ressortgroupref = tle->resdom->ressortgroupref;
+		if (ressortgroupref == 0)
+			return true;		/* definitely not in DISTINCT list */
+		foreach(distinctClause, query->distinctClause)
+		{
+			SortClause *scl = (SortClause *) lfirst(distinctClause);
+
+			if (scl->tleSortGroupRef == ressortgroupref)
+				break;			/* found TLE in DISTINCT */
+		}
+		if (distinctClause == NIL)
+			return true;		/* this TLE is not in DISTINCT list */
+	}
+	/* It's a simple DISTINCT */
+	return false;
+}
+
+
+/*****************************************************************************
  *																			 *
  *		General clause-manipulating routines								 *
  *																			 *
  *****************************************************************************/
 
 /*
- * clause_relids_vars
+ * clause_get_relids_vars
  *	  Retrieves distinct relids and vars appearing within a clause.
  *
  * '*relids' is set to an integer list of all distinct "varno"s appearing