IN clauses appearing at top level of WHERE can now be handled as joins.

There are two implementation techniques: the executor understands a new
JOIN_IN jointype, which emits at most one matching row per left-hand row,
or the result of the IN's sub-select can be fed through a DISTINCT filter
and then joined as an ordinary relation.
Along the way, some minor code cleanup in the optimizer; notably, break
out most of the jointree-rearrangement preprocessing in planner.c and
put it in a new file prep/prepjointree.c.

8 files changed, 36 insertions, 13 deletions

diff --git a/src/include/optimizer/joininfo.h b/src/include/optimizer/joininfo.h
index f17e278238c..37131b722d2 100644
--- a/src/include/optimizer/joininfo.h
+++ b/src/include/optimizer/joininfo.h
@@ -7,7 +7,7 @@
  * Portions Copyright (c) 1996-2002, PostgreSQL Global Development Group
  * Portions Copyright (c) 1994, Regents of the University of California
  *
- * $Id: joininfo.h,v 1.21 2002/06/20 20:29:51 momjian Exp $
+ * $Id: joininfo.h,v 1.22 2003/01/20 18:55:04 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -17,5 +17,6 @@
 #include "nodes/relation.h"
 
 extern JoinInfo *find_joininfo_node(RelOptInfo *this_rel, List *join_relids);
+extern JoinInfo *make_joininfo_node(RelOptInfo *this_rel, List *join_relids);
 
 #endif   /* JOININFO_H */
diff --git a/src/include/optimizer/pathnode.h b/src/include/optimizer/pathnode.h
index 77ed27e7e55..759b18c2499 100644
--- a/src/include/optimizer/pathnode.h
+++ b/src/include/optimizer/pathnode.h
@@ -7,7 +7,7 @@
  * Portions Copyright (c) 1996-2002, PostgreSQL Global Development Group
  * Portions Copyright (c) 1994, Regents of the University of California
  *
- * $Id: pathnode.h,v 1.47 2003/01/15 19:35:47 tgl Exp $
+ * $Id: pathnode.h,v 1.48 2003/01/20 18:55:05 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -38,6 +38,8 @@ extern AppendPath *create_append_path(RelOptInfo *rel, List *subpaths);
 extern ResultPath *create_result_path(RelOptInfo *rel, Path *subpath,
 									  List *constantqual);
 extern MaterialPath *create_material_path(RelOptInfo *rel, Path *subpath);
+extern UniquePath *create_unique_path(Query *root, RelOptInfo *rel,
+									  Path *subpath);
 extern Path *create_subqueryscan_path(RelOptInfo *rel);
 extern Path *create_functionscan_path(Query *root, RelOptInfo *rel);
 
@@ -75,6 +77,7 @@ extern void build_base_rel(Query *root, int relid);
 extern RelOptInfo *build_other_rel(Query *root, int relid);
 extern RelOptInfo *find_base_rel(Query *root, int relid);
 extern RelOptInfo *build_join_rel(Query *root,
+			   List *joinrelids,
 			   RelOptInfo *outer_rel,
 			   RelOptInfo *inner_rel,
 			   JoinType jointype,
diff --git a/src/include/optimizer/planmain.h b/src/include/optimizer/planmain.h
index 66925931609..cf9c2ddeb64 100644
--- a/src/include/optimizer/planmain.h
+++ b/src/include/optimizer/planmain.h
@@ -7,7 +7,7 @@
  * Portions Copyright (c) 1996-2002, PostgreSQL Global Development Group
  * Portions Copyright (c) 1994, Regents of the University of California
  *
- * $Id: planmain.h,v 1.66 2003/01/15 23:10:32 tgl Exp $
+ * $Id: planmain.h,v 1.67 2003/01/20 18:55:05 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -32,6 +32,8 @@ extern SubqueryScan *make_subqueryscan(List *qptlist, List *qpqual,
 extern Append *make_append(List *appendplans, bool isTarget, List *tlist);
 extern Sort *make_sort(Query *root, List *tlist,
 		  Plan *lefttree, int keycount);
+extern Sort *make_sort_from_sortclauses(Query *root, List *tlist,
+										Plan *lefttree, List *sortcls);
 extern Agg *make_agg(Query *root, List *tlist, List *qual,
 					 AggStrategy aggstrategy,
 					 int numGroupCols, AttrNumber *grpColIdx,
diff --git a/src/include/optimizer/planner.h b/src/include/optimizer/planner.h
index f49583a7ef3..16885b2f138 100644
--- a/src/include/optimizer/planner.h
+++ b/src/include/optimizer/planner.h
@@ -7,7 +7,7 @@
  * Portions Copyright (c) 1996-2002, PostgreSQL Global Development Group
  * Portions Copyright (c) 1994, Regents of the University of California
  *
- * $Id: planner.h,v 1.24 2002/06/20 20:29:51 momjian Exp $
+ * $Id: planner.h,v 1.25 2003/01/20 18:55:05 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -21,7 +21,4 @@
 extern Plan *planner(Query *parse);
 extern Plan *subquery_planner(Query *parse, double tuple_fraction);
 
-extern Plan *make_sortplan(Query *parse, List *tlist,
-			  Plan *plannode, List *sortcls);
-
 #endif   /* PLANNER_H */
diff --git a/src/include/optimizer/prep.h b/src/include/optimizer/prep.h
index 1bb64af3ae5..17ecb4d593f 100644
--- a/src/include/optimizer/prep.h
+++ b/src/include/optimizer/prep.h
@@ -7,7 +7,7 @@
  * Portions Copyright (c) 1996-2002, PostgreSQL Global Development Group
  * Portions Copyright (c) 1994, Regents of the University of California
  *
- * $Id: prep.h,v 1.33 2002/08/29 16:03:49 tgl Exp $
+ * $Id: prep.h,v 1.34 2003/01/20 18:55:05 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -18,6 +18,16 @@
 #include "nodes/plannodes.h"
 
 /*
+ * prototypes for prepjointree.c
+ */
+extern Node *pull_up_IN_clauses(Query *parse, Node *node);
+extern Node *pull_up_subqueries(Query *parse, Node *jtnode,
+				   bool below_outer_join);
+extern Node *preprocess_jointree(Query *parse, Node *jtnode);
+extern List *get_relids_in_jointree(Node *jtnode);
+extern List *get_relids_for_join(Query *parse, int joinrelid);
+
+/*
  * prototypes for prepqual.c
  */
 extern List *canonicalize_qual(Expr *qual, bool removeAndFlag);
diff --git a/src/include/optimizer/subselect.h b/src/include/optimizer/subselect.h
index 8fead9929f6..2e6a4640684 100644
--- a/src/include/optimizer/subselect.h
+++ b/src/include/optimizer/subselect.h
@@ -2,6 +2,11 @@
  *
  * subselect.h
  *
+ * Portions Copyright (c) 1996-2002, PostgreSQL Global Development Group
+ * Portions Copyright (c) 1994, Regents of the University of California
+ *
+ * $Id: subselect.h,v 1.17 2003/01/20 18:55:05 tgl Exp $
+ *
  *-------------------------------------------------------------------------
  */
 #ifndef SUBSELECT_H
@@ -14,8 +19,9 @@ extern List *PlannerInitPlan;	/* init subplans for current query */
 extern List *PlannerParamVar;	/* to get Var from Param->paramid */
 extern int	PlannerPlanId;		/* to assign unique ID to subquery plans */
 
-extern List *SS_finalize_plan(Plan *plan, List *rtable);
+extern Node *convert_IN_to_join(Query *parse, SubLink *sublink);
 extern Node *SS_replace_correlation_vars(Node *expr);
 extern Node *SS_process_sublinks(Node *expr, bool isQual);
+extern List *SS_finalize_plan(Plan *plan, List *rtable);
 
 #endif   /* SUBSELECT_H */
diff --git a/src/include/optimizer/tlist.h b/src/include/optimizer/tlist.h
index 7b82b5ae291..b38f4016f98 100644
--- a/src/include/optimizer/tlist.h
+++ b/src/include/optimizer/tlist.h
@@ -7,7 +7,7 @@
  * Portions Copyright (c) 1996-2002, PostgreSQL Global Development Group
  * Portions Copyright (c) 1994, Regents of the University of California
  *
- * $Id: tlist.h,v 1.32 2002/06/20 20:29:51 momjian Exp $
+ * $Id: tlist.h,v 1.33 2003/01/20 18:55:06 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -32,5 +32,7 @@ extern TargetEntry *get_sortgroupclause_tle(SortClause *sortClause,
 						List *targetList);
 extern Node *get_sortgroupclause_expr(SortClause *sortClause,
 						 List *targetList);
+extern List *get_sortgrouplist_exprs(List *sortClauses,
+						 List *targetList);
 
 #endif   /* TLIST_H */
diff --git a/src/include/optimizer/var.h b/src/include/optimizer/var.h
index 07b8b311d07..b207acac593 100644
--- a/src/include/optimizer/var.h
+++ b/src/include/optimizer/var.h
@@ -7,14 +7,14 @@
  * Portions Copyright (c) 1996-2002, PostgreSQL Global Development Group
  * Portions Copyright (c) 1994, Regents of the University of California
  *
- * $Id: var.h,v 1.24 2003/01/15 19:35:47 tgl Exp $
+ * $Id: var.h,v 1.25 2003/01/20 18:55:06 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
 #ifndef VAR_H
 #define VAR_H
 
-#include "nodes/primnodes.h"
+#include "nodes/parsenodes.h"
 
 
 extern List *pull_varnos(Node *node);
@@ -22,7 +22,9 @@ extern bool contain_var_reference(Node *node, int varno, int varattno,
 					  int levelsup);
 extern bool contain_whole_tuple_var(Node *node, int varno, int levelsup);
 extern bool contain_var_clause(Node *node);
+extern bool contain_vars_of_level(Node *node, int levelsup);
+extern bool contain_vars_above_level(Node *node, int levelsup);
 extern List *pull_var_clause(Node *node, bool includeUpperVars);
-extern Node *flatten_join_alias_vars(Node *node, List *rtable);
+extern Node *flatten_join_alias_vars(Query *root, Node *node);
 
 #endif   /* VAR_H */