diff options
author | Tom Lane <tgl@sss.pgh.pa.us> | 2000-10-05 19:11:39 +0000 |
---|---|---|
committer | Tom Lane <tgl@sss.pgh.pa.us> | 2000-10-05 19:11:39 +0000 |
commit | 05e3d0ee8666b74f11ffad16f46e372459d6e53e (patch) | |
tree | b273892bfda60f6bad315e84aaa2e9826e226931 /src/include | |
parent | 5292637f52c6db8a22f99177f228273cb69fc510 (diff) | |
download | postgresql-05e3d0ee8666b74f11ffad16f46e372459d6e53e.tar.gz postgresql-05e3d0ee8666b74f11ffad16f46e372459d6e53e.zip |
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!
Diffstat (limited to 'src/include')
-rw-r--r-- | src/include/catalog/catversion.h | 4 | ||||
-rw-r--r-- | src/include/executor/nodeSetOp.h | 25 | ||||
-rw-r--r-- | src/include/nodes/execnodes.h | 26 | ||||
-rw-r--r-- | src/include/nodes/nodes.h | 9 | ||||
-rw-r--r-- | src/include/nodes/parsenodes.h | 79 | ||||
-rw-r--r-- | src/include/nodes/pg_list.h | 3 | ||||
-rw-r--r-- | src/include/nodes/plannodes.h | 37 | ||||
-rw-r--r-- | src/include/optimizer/clauses.h | 6 | ||||
-rw-r--r-- | src/include/optimizer/planmain.h | 7 | ||||
-rw-r--r-- | src/include/optimizer/planner.h | 4 | ||||
-rw-r--r-- | src/include/optimizer/prep.h | 4 | ||||
-rw-r--r-- | src/include/parser/analyze.h | 5 | ||||
-rw-r--r-- | src/include/parser/parse_coerce.h | 7 |
13 files changed, 160 insertions, 56 deletions
diff --git a/src/include/catalog/catversion.h b/src/include/catalog/catversion.h index c64b5a9a9de..8310bc3c01a 100644 --- a/src/include/catalog/catversion.h +++ b/src/include/catalog/catversion.h @@ -37,7 +37,7 @@ * Portions Copyright (c) 1996-2000, PostgreSQL, Inc * Portions Copyright (c) 1994, Regents of the University of California * - * $Id: catversion.h,v 1.48 2000/09/29 18:21:37 tgl Exp $ + * $Id: catversion.h,v 1.49 2000/10/05 19:11:35 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -53,6 +53,6 @@ */ /* yyyymmddN */ -#define CATALOG_VERSION_NO 200009281 +#define CATALOG_VERSION_NO 200010041 #endif diff --git a/src/include/executor/nodeSetOp.h b/src/include/executor/nodeSetOp.h new file mode 100644 index 00000000000..0414cc46ef0 --- /dev/null +++ b/src/include/executor/nodeSetOp.h @@ -0,0 +1,25 @@ +/*------------------------------------------------------------------------- + * + * nodeSetOp.h + * + * + * + * Portions Copyright (c) 1996-2000, PostgreSQL, Inc + * Portions Copyright (c) 1994, Regents of the University of California + * + * $Id: nodeSetOp.h,v 1.1 2000/10/05 19:11:36 tgl Exp $ + * + *------------------------------------------------------------------------- + */ +#ifndef NODESETOP_H +#define NODESETOP_H + +#include "nodes/plannodes.h" + +extern TupleTableSlot *ExecSetOp(SetOp *node); +extern bool ExecInitSetOp(SetOp *node, EState *estate, Plan *parent); +extern int ExecCountSlotsSetOp(SetOp *node); +extern void ExecEndSetOp(SetOp *node); +extern void ExecReScanSetOp(SetOp *node, ExprContext *exprCtxt, Plan *parent); + +#endif /* NODESETOP_H */ diff --git a/src/include/nodes/execnodes.h b/src/include/nodes/execnodes.h index 65d35a2977e..06de4be54cb 100644 --- a/src/include/nodes/execnodes.h +++ b/src/include/nodes/execnodes.h @@ -7,7 +7,7 @@ * Portions Copyright (c) 1996-2000, PostgreSQL, Inc * Portions Copyright (c) 1994, Regents of the University of California * - * $Id: execnodes.h,v 1.50 2000/09/29 18:21:38 tgl Exp $ + * $Id: execnodes.h,v 1.51 2000/10/05 19:11:36 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -348,7 +348,6 @@ typedef struct ResultState * whichplan which plan is being executed * nplans how many plans are in the list * initialized array of ExecInitNode() results - * rtentries range table for the current plan * result_relation_info_list array of each subplan's result relation info * junkFilter_list array of each subplan's junk filter * ---------------- @@ -359,7 +358,6 @@ typedef struct AppendState int as_whichplan; int as_nplans; bool *as_initialized; - List *as_rtentries; List *as_result_relation_info_list; List *as_junkFilter_list; } AppendState; @@ -460,14 +458,12 @@ typedef struct TidScanState * The sub-query will have its own EState, which we save here. * ScanTupleSlot references the current output tuple of the sub-query. * - * SubQueryDesc queryDesc for sub-query * SubEState exec state for sub-query * ---------------- */ typedef struct SubqueryScanState { CommonScanState csstate; /* its first field is NodeTag */ - struct QueryDesc *sss_SubQueryDesc; EState *sss_SubEState; } SubqueryScanState; @@ -659,6 +655,26 @@ typedef struct UniqueState MemoryContext tempContext; /* short-term context for comparisons */ } UniqueState; +/* ---------------- + * SetOpState information + * + * SetOp nodes are used "on top of" sort nodes to discard + * duplicate tuples returned from the sort phase. These are + * more complex than a simple Unique since we have to count + * how many duplicates to return. + * ---------------- + */ +typedef struct SetOpState +{ + CommonState cstate; /* its first field is NodeTag */ + FmgrInfo *eqfunctions; /* per-field lookup data for equality fns */ + bool subplan_done; /* has subplan returned EOF? */ + long numLeft; /* number of left-input dups of cur group */ + long numRight; /* number of right-input dups of cur group */ + long numOutput; /* number of dups left to output */ + MemoryContext tempContext; /* short-term context for comparisons */ +} SetOpState; + /* ---------------- * HashState information diff --git a/src/include/nodes/nodes.h b/src/include/nodes/nodes.h index c72516477dc..fe798492c78 100644 --- a/src/include/nodes/nodes.h +++ b/src/include/nodes/nodes.h @@ -7,7 +7,7 @@ * Portions Copyright (c) 1996-2000, PostgreSQL, Inc * Portions Copyright (c) 1994, Regents of the University of California * - * $Id: nodes.h,v 1.77 2000/09/29 18:21:38 tgl Exp $ + * $Id: nodes.h,v 1.78 2000/10/05 19:11:36 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -45,7 +45,7 @@ typedef enum NodeTag T_Agg, T_Unique, T_Hash, - T_Choose_XXX, /* not used anymore; this tag# is available */ + T_SetOp, T_Group, T_SubPlan, T_TidScan, @@ -121,6 +121,7 @@ typedef enum NodeTag T_HashState, T_TidScanState, T_SubqueryScanState, + T_SetOpState, /*--------------------- * TAGS FOR MEMORY NODES (memnodes.h) @@ -141,7 +142,7 @@ typedef enum NodeTag T_Null, /*--------------------- - * TAGS FOR PARSE TREE NODES (parsenode.h) + * TAGS FOR PARSE TREE NODES (parsenodes.h) *--------------------- */ T_Query = 600, @@ -150,7 +151,7 @@ typedef enum NodeTag T_UpdateStmt, T_SelectStmt, T_AlterTableStmt, - T_AggregateStmtXXX, /* not used anymore; this tag# is available */ + T_SetOperationStmt, T_ChangeACLStmt, T_ClosePortalStmt, T_ClusterStmt, diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h index ab805406eca..41309426e8b 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -7,7 +7,7 @@ * Portions Copyright (c) 1996-2000, PostgreSQL, Inc * Portions Copyright (c) 1994, Regents of the University of California * - * $Id: parsenodes.h,v 1.114 2000/09/29 18:21:38 tgl Exp $ + * $Id: parsenodes.h,v 1.115 2000/10/05 19:11:36 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -29,7 +29,6 @@ * * we need the isPortal flag because portal names can be null too; can * get rid of it if we support CURSOR as a commandType. - * */ typedef struct Query { @@ -45,33 +44,32 @@ typedef struct Query bool isPortal; /* is this a retrieve into portal? */ bool isBinary; /* binary portal? */ bool isTemp; /* is 'into' a temp table? */ - bool unionall; /* union without unique sort */ + bool hasAggs; /* has aggregates in tlist or havingQual */ bool hasSubLinks; /* has subquery SubLink */ List *rtable; /* list of range table entries */ FromExpr *jointree; /* table join tree (FROM and WHERE clauses) */ - List *targetList; /* target list (of TargetEntry) */ - List *rowMarks; /* integer list of RT indexes of relations * that are selected FOR UPDATE */ - List *distinctClause; /* a list of SortClause's */ - - List *sortClause; /* a list of SortClause's */ + List *targetList; /* target list (of TargetEntry) */ List *groupClause; /* a list of GroupClause's */ Node *havingQual; /* qualifications applied to groups */ - List *intersectClause; - List *unionClause; /* unions are linked under the previous - * query */ + List *distinctClause; /* a list of SortClause's */ + + List *sortClause; /* a list of SortClause's */ Node *limitOffset; /* # of result tuples to skip */ Node *limitCount; /* # of result tuples to return */ + Node *setOperations; /* set-operation tree if this is top level + * of a UNION/INTERSECT/EXCEPT query */ + /* internal to planner */ List *base_rel_list; /* list of base-relation RelOptInfos */ List *join_rel_list; /* list of join-relation RelOptInfos */ @@ -785,19 +783,14 @@ typedef struct InsertStmt { NodeTag type; char *relname; /* relation to insert into */ - List *distinctClause; /* NULL, list of DISTINCT ON exprs, or - * lcons(NIL,NIL) for all (SELECT - * DISTINCT) */ - List *cols; /* names of the columns */ + List *cols; /* optional: names of the target columns */ + /* + * An INSERT statement has *either* VALUES or SELECT, never both. + * If VALUES, a targetList is supplied (empty for DEFAULT VALUES). + * If SELECT, a complete SelectStmt (or SetOperation tree) is supplied. + */ List *targetList; /* the target list (of ResTarget) */ - List *fromClause; /* the from clause */ - Node *whereClause; /* qualifications */ - List *groupClause; /* GROUP BY clauses */ - Node *havingClause; /* having conditional-expression */ - List *unionClause; /* union subselect parameters */ - bool unionall; /* union without unique sort */ - List *intersectClause; - List *forUpdate; /* FOR UPDATE clause */ + Node *selectStmt; /* the source SELECT */ } InsertStmt; /* ---------------------- @@ -842,20 +835,50 @@ typedef struct SelectStmt Node *whereClause; /* qualifications */ List *groupClause; /* GROUP BY clauses */ Node *havingClause; /* having conditional-expression */ - List *intersectClause; - List *exceptClause; - - List *unionClause; /* union subselect parameters */ List *sortClause; /* sort clause (a list of SortGroupBy's) */ char *portalname; /* the portal (cursor) to create */ bool binary; /* a binary (internal) portal? */ bool istemp; /* into is a temp table */ - bool unionall; /* union without unique sort */ Node *limitOffset; /* # of result tuples to skip */ Node *limitCount; /* # of result tuples to return */ List *forUpdate; /* FOR UPDATE clause */ } SelectStmt; +/* ---------------------- + * Select Statement with Set Operations + * + * UNION/INTERSECT/EXCEPT operations are represented in the output of gram.y + * as a tree whose leaves are SelectStmts and internal nodes are + * SetOperationStmts. The statement-wide info (ORDER BY, etc clauses) + * is placed in the leftmost SelectStmt leaf. + * + * After parse analysis, there is a top-level Query node containing the leaf + * SELECTs as subqueries in its range table. Its setOperations field is the + * SetOperationStmt tree with leaf SelectStmt nodes replaced by RangeTblRef + * nodes. The statement-wide options such as ORDER BY are attached to this + * top-level Query. + * ---------------------- + */ +typedef enum SetOperation +{ + SETOP_UNION, + SETOP_INTERSECT, + SETOP_EXCEPT +} SetOperation; + +typedef struct SetOperationStmt +{ + NodeTag type; + SetOperation op; /* type of set op */ + bool all; /* ALL specified? */ + Node *larg; /* left child */ + Node *rarg; /* right child */ + /* Eventually add fields for CORRESPONDING spec here */ + + /* This field is filled in during parse analysis: */ + List *colTypes; /* integer list of OIDs of output column types */ +} SetOperationStmt; + /**************************************************************************** * Supporting data structures for Parse Trees * diff --git a/src/include/nodes/pg_list.h b/src/include/nodes/pg_list.h index 953f6edebee..07c58348e07 100644 --- a/src/include/nodes/pg_list.h +++ b/src/include/nodes/pg_list.h @@ -7,7 +7,7 @@ * Portions Copyright (c) 1996-2000, PostgreSQL, Inc * Portions Copyright (c) 1994, Regents of the University of California * - * $Id: pg_list.h,v 1.20 2000/09/29 18:21:39 tgl Exp $ + * $Id: pg_list.h,v 1.21 2000/10/05 19:11:36 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -135,6 +135,7 @@ extern List *lreverse(List *l); extern List *set_union(List *list1, List *list2); extern List *set_unioni(List *list1, List *list2); +extern bool equali(List *list1, List *list2); extern bool sameseti(List *list1, List *list2); extern bool nonoverlap_setsi(List *list1, List *list2); extern bool is_subseti(List *list1, List *list2); diff --git a/src/include/nodes/plannodes.h b/src/include/nodes/plannodes.h index ca5727f0152..d8e3df4829a 100644 --- a/src/include/nodes/plannodes.h +++ b/src/include/nodes/plannodes.h @@ -7,7 +7,7 @@ * Portions Copyright (c) 1996-2000, PostgreSQL, Inc * Portions Copyright (c) 1994, Regents of the University of California * - * $Id: plannodes.h,v 1.43 2000/09/29 18:21:39 tgl Exp $ + * $Id: plannodes.h,v 1.44 2000/10/05 19:11:36 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -46,6 +46,7 @@ * Material MaterialState matstate; * Sort SortState sortstate; * Unique UniqueState uniquestate; + * SetOp SetOpState setopstate; * Hash HashState hashstate; * * ---------------------------------------------------------------- @@ -145,16 +146,19 @@ typedef struct Result /* ---------------- * append node + * + * Append nodes can modify the query's rtable during execution. + * If inheritrelid > 0, then the RTE with index inheritrelid is replaced + * by the i'th element of inheritrtable to execute the i'th subplan. + * We assume that this RTE is not used in any other part of the + * query plan tree, else confusion may result... * ---------------- */ typedef struct Append { Plan plan; List *appendplans; - List *unionrtables; /* List of range tables, one for each - * union query. */ - Index inheritrelid; /* The range table has to be changed for - * inheritance. */ + Index inheritrelid; List *inheritrtable; AppendState *appendstate; } Append; @@ -349,6 +353,29 @@ typedef struct Unique } Unique; /* ---------------- + * setop node + * ---------------- + */ +typedef enum SetOpCmd +{ + SETOPCMD_INTERSECT, + SETOPCMD_INTERSECT_ALL, + SETOPCMD_EXCEPT, + SETOPCMD_EXCEPT_ALL +} SetOpCmd; + +typedef struct SetOp +{ + Plan plan; + SetOpCmd cmd; /* what to do */ + int numCols; /* number of columns to check for + * duplicate-ness */ + AttrNumber *dupColIdx; /* indexes into the target list */ + AttrNumber flagColIdx; + SetOpState *setopstate; +} SetOp; + +/* ---------------- * hash build node * ---------------- */ diff --git a/src/include/optimizer/clauses.h b/src/include/optimizer/clauses.h index 881940f9a18..612750dfaf3 100644 --- a/src/include/optimizer/clauses.h +++ b/src/include/optimizer/clauses.h @@ -7,7 +7,7 @@ * Portions Copyright (c) 1996-2000, PostgreSQL, Inc * Portions Copyright (c) 1994, Regents of the University of California * - * $Id: clauses.h,v 1.40 2000/09/29 18:21:40 tgl Exp $ + * $Id: clauses.h,v 1.41 2000/10/05 19:11:37 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -75,7 +75,9 @@ extern bool expression_tree_walker(Node *node, bool (*walker) (), extern Node *expression_tree_mutator(Node *node, Node *(*mutator) (), void *context); extern bool query_tree_walker(Query *query, bool (*walker) (), - void *context); + void *context, bool visitQueryRTEs); +extern void query_tree_mutator(Query *query, Node *(*mutator) (), + void *context, bool visitQueryRTEs); #define is_subplan(clause) ((clause) != NULL && \ IsA(clause, Expr) && \ diff --git a/src/include/optimizer/planmain.h b/src/include/optimizer/planmain.h index e46d0bdbd84..015590a5ee2 100644 --- a/src/include/optimizer/planmain.h +++ b/src/include/optimizer/planmain.h @@ -7,7 +7,7 @@ * Portions Copyright (c) 1996-2000, PostgreSQL, Inc * Portions Copyright (c) 1994, Regents of the University of California * - * $Id: planmain.h,v 1.45 2000/09/29 18:21:40 tgl Exp $ + * $Id: planmain.h,v 1.46 2000/10/05 19:11:37 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -26,6 +26,8 @@ extern Plan *query_planner(Query *root, List *tlist, double tuple_fraction); * prototypes for plan/createplan.c */ extern Plan *create_plan(Query *root, Path *best_path); +extern SubqueryScan *make_subqueryscan(List *qptlist, List *qpqual, + Index scanrelid, Plan *subplan); extern Sort *make_sort(List *tlist, Plan *lefttree, int keycount); extern Sort *make_sort_from_pathkeys(List *tlist, Plan *lefttree, List *pathkeys); @@ -34,7 +36,10 @@ extern Group *make_group(List *tlist, bool tuplePerGroup, int ngrp, AttrNumber *grpColIdx, Plan *lefttree); extern Material *make_material(List *tlist, Plan *lefttree); extern Unique *make_unique(List *tlist, Plan *lefttree, List *distinctList); +extern SetOp *make_setop(SetOpCmd cmd, List *tlist, Plan *lefttree, + List *distinctList, AttrNumber flagColIdx); extern Result *make_result(List *tlist, Node *resconstantqual, Plan *subplan); +extern void copy_plan_costsize(Plan *dest, Plan *src); /* * prototypes for plan/initsplan.c diff --git a/src/include/optimizer/planner.h b/src/include/optimizer/planner.h index da099940e6e..3fc0f435dc3 100644 --- a/src/include/optimizer/planner.h +++ b/src/include/optimizer/planner.h @@ -7,7 +7,7 @@ * Portions Copyright (c) 1996-2000, PostgreSQL, Inc * Portions Copyright (c) 1994, Regents of the University of California * - * $Id: planner.h,v 1.16 2000/08/21 20:55:28 tgl Exp $ + * $Id: planner.h,v 1.17 2000/10/05 19:11:37 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -22,4 +22,6 @@ extern Plan *planner(Query *parse); extern Plan *subquery_planner(Query *parse, double tuple_fraction); extern Plan *union_planner(Query *parse, double tuple_fraction); +extern Plan *make_sortplan(List *tlist, Plan *plannode, List *sortcls); + #endif /* PLANNER_H */ diff --git a/src/include/optimizer/prep.h b/src/include/optimizer/prep.h index 4ffddcf5668..dcdf6fb62f6 100644 --- a/src/include/optimizer/prep.h +++ b/src/include/optimizer/prep.h @@ -7,7 +7,7 @@ * Portions Copyright (c) 1996-2000, PostgreSQL, Inc * Portions Copyright (c) 1994, Regents of the University of California * - * $Id: prep.h,v 1.23 2000/06/20 04:22:13 tgl Exp $ + * $Id: prep.h,v 1.24 2000/10/05 19:11:37 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -32,11 +32,11 @@ extern List *preprocess_targetlist(List *tlist, int command_type, /* * prototypes for prepunion.c */ +extern Plan *plan_set_operations(Query *parse); extern List *find_all_inheritors(Oid parentrel); extern bool find_inheritable_rt_entry(List *rangetable, Index *rt_index, List **inheritors); extern Plan *plan_inherit_queries(Query *root, List *tlist, Index rt_index, List *inheritors); -extern Plan *plan_union_queries(Query *parse); #endif /* PREP_H */ diff --git a/src/include/parser/analyze.h b/src/include/parser/analyze.h index 691ec92c1fc..afd8a34fb3e 100644 --- a/src/include/parser/analyze.h +++ b/src/include/parser/analyze.h @@ -6,7 +6,7 @@ * Portions Copyright (c) 1996-2000, PostgreSQL, Inc * Portions Copyright (c) 1994, Regents of the University of California * - * $Id: analyze.h,v 1.10 2000/01/26 05:58:26 momjian Exp $ + * $Id: analyze.h,v 1.11 2000/10/05 19:11:38 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -17,7 +17,4 @@ extern List *parse_analyze(List *pl, ParseState *parentParseState); -extern void create_select_list(Node *ptr, List **select_list, bool *unionall_present); -extern Node *A_Expr_to_Expr(Node *ptr, bool *intersect_present); - #endif /* ANALYZE_H */ diff --git a/src/include/parser/parse_coerce.h b/src/include/parser/parse_coerce.h index acea75d01df..7dd95f5b47c 100644 --- a/src/include/parser/parse_coerce.h +++ b/src/include/parser/parse_coerce.h @@ -7,7 +7,7 @@ * Portions Copyright (c) 1996-2000, PostgreSQL, Inc * Portions Copyright (c) 1994, Regents of the University of California * - * $Id: parse_coerce.h,v 1.23 2000/08/21 04:48:52 tgl Exp $ + * $Id: parse_coerce.h,v 1.24 2000/10/05 19:11:38 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -135,4 +135,9 @@ extern Node *coerce_type(ParseState *pstate, Node *node, Oid inputTypeId, extern Node *coerce_type_typmod(ParseState *pstate, Node *node, Oid targetTypeId, int32 atttypmod); +extern Oid select_common_type(List *typeids, const char *context); +extern Node *coerce_to_common_type(ParseState *pstate, Node *node, + Oid targetTypeId, + const char *context); + #endif /* PARSE_COERCE_H */ |