Hi Tom, In the attached patch i include the comments given
regards Surafel
diff --git a/contrib/postgres_fdw/postgres_fdw.c b/contrib/postgres_fdw/postgres_fdw.c index 82d8140ba2..692d6492bd 100644 --- a/contrib/postgres_fdw/postgres_fdw.c +++ b/contrib/postgres_fdw/postgres_fdw.c @@ -3035,7 +3035,8 @@ estimate_path_cost_size(PlannerInfo *root, if (fpextra && fpextra->has_limit) { adjust_limit_rows_costs(&rows, &startup_cost, &total_cost, - fpextra->offset_est, fpextra->count_est); + fpextra->offset_est, fpextra->count_est, + LIMIT_OPTION_COUNT); retrieved_rows = rows; } } diff --git a/doc/src/sgml/ref/select.sgml b/doc/src/sgml/ref/select.sgml index 06d611b64c..4ef42df51d 100644 --- a/doc/src/sgml/ref/select.sgml +++ b/doc/src/sgml/ref/select.sgml @@ -44,7 +44,7 @@ SELECT [ ALL | DISTINCT [ ON ( <replaceable class="parameter">expression</replac [ ORDER BY <replaceable class="parameter">expression</replaceable> [ ASC | DESC | USING <replaceable class="parameter">operator</replaceable> ] [ NULLS { FIRST | LAST } ] [, ...] ] [ LIMIT { <replaceable class="parameter">count</replaceable> | ALL } ] [ OFFSET <replaceable class="parameter">start</replaceable> [ ROW | ROWS ] ] - [ FETCH { FIRST | NEXT } [ <replaceable class="parameter">count</replaceable> ] { ROW | ROWS } ONLY ] + [ FETCH { FIRST | NEXT } [ <replaceable class="parameter">count</replaceable> ] [ PERCENT ] { ROW | ROWS } ONLY ] [ FOR { UPDATE | NO KEY UPDATE | SHARE | KEY SHARE } [ OF <replaceable class="parameter">table_name</replaceable> [, ...] ] [ NOWAIT | SKIP LOCKED ] [...] ] <phrase>where <replaceable class="parameter">from_item</replaceable> can be one of:</phrase> @@ -1430,7 +1430,7 @@ OFFSET <replaceable class="parameter">start</replaceable> which <productname>PostgreSQL</productname> also supports. It is: <synopsis> OFFSET <replaceable class="parameter">start</replaceable> { ROW | ROWS } -FETCH { FIRST | NEXT } [ <replaceable class="parameter">count</replaceable> ] { ROW | ROWS } ONLY +FETCH { FIRST | NEXT } [ <replaceable class="parameter">count</replaceable> ] [ PERCENT ] { ROW | ROWS } ONLY </synopsis> In this syntax, the <replaceable class="parameter">start</replaceable> or <replaceable class="parameter">count</replaceable> value is required by @@ -1440,8 +1440,10 @@ FETCH { FIRST | NEXT } [ <replaceable class="parameter">count</replaceable> ] { ambiguity. If <replaceable class="parameter">count</replaceable> is omitted in a <literal>FETCH</literal> clause, it defaults to 1. - <literal>ROW</literal> - and <literal>ROWS</literal> as well as <literal>FIRST</literal> + With <literal>PERCENT</literal> count specifies the maximum number of rows to return + in percentage round up to the nearest integer. Using <literal>PERCENT</literal> + is best suited to returning single-digit percentages of the query's total row count. + <literal>ROW</literal> and <literal>ROWS</literal> as well as <literal>FIRST</literal> and <literal>NEXT</literal> are noise words that don't influence the effects of these clauses. According to the standard, the <literal>OFFSET</literal> clause must come diff --git a/src/backend/catalog/sql_features.txt b/src/backend/catalog/sql_features.txt index 059ec02cd0..1adc312f7a 100644 --- a/src/backend/catalog/sql_features.txt +++ b/src/backend/catalog/sql_features.txt @@ -348,7 +348,7 @@ F862 <result offset clause> in subqueries YES F863 Nested <result offset clause> in <query expression> YES F864 Top-level <result offset clause> in views YES F865 <offset row count> in <result offset clause> YES -F866 FETCH FIRST clause: PERCENT option NO +F866 FETCH FIRST clause: PERCENT option YES F867 FETCH FIRST clause: WITH TIES option NO R010 Row pattern recognition: FROM clause NO R020 Row pattern recognition: WINDOW clause NO diff --git a/src/backend/executor/nodeLimit.c b/src/backend/executor/nodeLimit.c index baa669abe8..c03bb2c971 100644 --- a/src/backend/executor/nodeLimit.c +++ b/src/backend/executor/nodeLimit.c @@ -21,6 +21,8 @@ #include "postgres.h" +#include <math.h> + #include "executor/executor.h" #include "executor/nodeLimit.h" #include "miscadmin.h" @@ -52,6 +54,7 @@ ExecLimit(PlanState *pstate) */ direction = node->ps.state->es_direction; outerPlan = outerPlanState(node); + slot = node->subSlot; /* * The main logic is a simple state machine. @@ -81,7 +84,15 @@ ExecLimit(PlanState *pstate) /* * Check for empty window; if so, treat like empty subplan. */ - if (node->count <= 0 && !node->noCount) + if (node->limitOption == LIMIT_OPTION_PERCENT) + { + if (node->percent == 0.0) + { + node->lstate = LIMIT_EMPTY; + return NULL; + } + } + else if (node->count <= 0 && !node->noCount) { node->lstate = LIMIT_EMPTY; return NULL; @@ -107,6 +118,16 @@ ExecLimit(PlanState *pstate) break; } + /* + * We may needed this tuple in subsequent scan so put it into + * tuplestore. + */ + if (node->limitOption == LIMIT_OPTION_PERCENT) + { + tuplestore_puttupleslot(node->tupleStore, slot); + tuplestore_advance(node->tupleStore, true); + } + /* * Okay, we have the first tuple of the window. */ @@ -124,6 +145,82 @@ ExecLimit(PlanState *pstate) case LIMIT_INWINDOW: if (ScanDirectionIsForward(direction)) { + /* + * In case of coming back from backward scan the tuple is + * already in tuple store. + */ + if (node->limitOption == LIMIT_OPTION_PERCENT && node->backwardPosition > 0) + { + if (tuplestore_gettupleslot_heaptuple(node->tupleStore, true, true, slot)) + { + node->subSlot = slot; + node->position++; + node->backwardPosition--; + return slot; + } + else + { + node->lstate = LIMIT_SUBPLANEOF; + return NULL; + } + } + + /* + * In LIMIT_OPTION_PERCENT case no need of executing outerPlan multiple + * times. + */ + if (node->limitOption == LIMIT_OPTION_PERCENT && node->reachEnd) + { + node->lstate = LIMIT_WINDOWEND; + + /* + * If we know we won't need to back up, we can release + * resources at this point. + */ + if (!(node->ps.state->es_top_eflags & EXEC_FLAG_BACKWARD)) + (void) ExecShutdownNode(outerPlan); + + return NULL; + } + + /* + * When in percentage mode, we need to see if we can get any + * additional rows from the subplan (enough to increase the + * node->count value). + */ + if (node->limitOption == LIMIT_OPTION_PERCENT) + { + /* loop until the node->count became greater than the number of tuple returned so far */ + do + { + int64 cnt; + + slot = ExecProcNode(outerPlan); + if (TupIsNull(slot)) + { + node->reachEnd = true; + node->lstate = LIMIT_SUBPLANEOF; + + /* + * The only operation from here is backward scan + * but there's no API to refetch the tuple at the + * current position. We have to move one tuple + * backward, and then we will scan forward for it + * for the first tuple and precede as usual + * for the rest + */ + tuplestore_advance(node->tupleStore, false); + return NULL; + } + + tuplestore_puttupleslot(node->tupleStore, slot); + + cnt = tuplestore_tuple_count(node->tupleStore) + node->offset; + + node->count = ceil(node->percent * cnt / 100.0); + }while (node->position - node->offset >= node->count); + } + /* * Forwards scan, so check for stepping off end of window. If * we are at the end of the window, return NULL without @@ -145,17 +242,34 @@ ExecLimit(PlanState *pstate) return NULL; } - /* - * Get next tuple from subplan, if any. - */ - slot = ExecProcNode(outerPlan); - if (TupIsNull(slot)) + if (node->limitOption == LIMIT_OPTION_PERCENT) { - node->lstate = LIMIT_SUBPLANEOF; - return NULL; + if (tuplestore_gettupleslot_heaptuple(node->tupleStore, true, true, slot)) + { + node->subSlot = slot; + node->position++; + } + else + { + node->lstate = LIMIT_SUBPLANEOF; + return NULL; + } + + } + else if (node->limitOption == LIMIT_OPTION_COUNT) + { + /* + * Get next tuple from subplan, if any. + */ + slot = ExecProcNode(outerPlan); + if (TupIsNull(slot)) + { + node->lstate = LIMIT_SUBPLANEOF; + return NULL; + } + node->subSlot = slot; + node->position++; } - node->subSlot = slot; - node->position++; } else { @@ -168,15 +282,29 @@ ExecLimit(PlanState *pstate) node->lstate = LIMIT_WINDOWSTART; return NULL; } - - /* - * Get previous tuple from subplan; there should be one! - */ - slot = ExecProcNode(outerPlan); - if (TupIsNull(slot)) - elog(ERROR, "LIMIT subplan failed to run backwards"); - node->subSlot = slot; - node->position--; + /* In LIMIT_OPTION_PERCENT case the result is already in tuplestore */ + if (node->limitOption == LIMIT_OPTION_PERCENT) + { + if (tuplestore_gettupleslot_heaptuple(node->tupleStore, false, true, slot)) + { + node->subSlot = slot; + node->position--; + node->backwardPosition++; + } + else + elog(ERROR, "LIMIT subplan failed to run backwards"); + } + else if (node->limitOption == LIMIT_OPTION_COUNT) + { + /* + * Get previous tuple from subplan; there should be one! + */ + slot = ExecProcNode(outerPlan); + if (TupIsNull(slot)) + elog(ERROR, "LIMIT subplan failed to run backwards"); + node->subSlot = slot; + node->position--; + } } break; @@ -185,15 +313,32 @@ ExecLimit(PlanState *pstate) return NULL; /* - * Backing up from subplan EOF, so re-fetch previous tuple; there - * should be one! Note previous tuple must be in window. + * Scan forward for the first tuple */ - slot = ExecProcNode(outerPlan); - if (TupIsNull(slot)) - elog(ERROR, "LIMIT subplan failed to run backwards"); - node->subSlot = slot; - node->lstate = LIMIT_INWINDOW; - /* position does not change 'cause we didn't advance it before */ + if (node->limitOption == LIMIT_OPTION_PERCENT) + { + if (tuplestore_gettupleslot_heaptuple(node->tupleStore, true, true, slot)) + { + node->subSlot = slot; + node->lstate = LIMIT_INWINDOW; + } + else + elog(ERROR, "LIMIT subplan failed to run backwards"); + } + else if (node->limitOption == LIMIT_OPTION_COUNT) + { + /* + * Backing up from subplan EOF, so re-fetch previous tuple; + * there should be one! Note previous tuple must be in + * window. + */ + slot = ExecProcNode(outerPlan); + if (TupIsNull(slot)) + elog(ERROR, "LIMIT subplan failed to run backwards"); + node->subSlot = slot; + node->lstate = LIMIT_INWINDOW; + /* position does not change 'cause we didn't advance it before */ + } break; case LIMIT_WINDOWEND: @@ -283,12 +428,36 @@ recompute_limits(LimitState *node) } else { - node->count = DatumGetInt64(val); - if (node->count < 0) - ereport(ERROR, - (errcode(ERRCODE_INVALID_ROW_COUNT_IN_LIMIT_CLAUSE), - errmsg("LIMIT must not be negative"))); - node->noCount = false; + if (node->limitOption == LIMIT_OPTION_PERCENT) + { + /* + * We expect to return at least one row (unless there are no + * rows in the subplan), and we'll update this count later as + * we go. + */ + node->count = 0; + node->percent = DatumGetFloat8(val); + + if (node->percent < 0) + ereport(ERROR, + (errcode(ERRCODE_INVALID_ROW_COUNT_IN_LIMIT_CLAUSE), + errmsg("PERCENT must not be negative"))); + + if (node->percent > 100) + ereport(ERROR, + (errcode(ERRCODE_INVALID_ROW_COUNT_IN_LIMIT_CLAUSE), + errmsg("PERCENT must not be greater than 100"))); + + } + else + { + node->count = DatumGetInt64(val); + if (node->count < 0) + ereport(ERROR, + (errcode(ERRCODE_INVALID_ROW_COUNT_IN_LIMIT_CLAUSE), + errmsg("LIMIT must not be negative"))); + + } } } else @@ -301,6 +470,8 @@ recompute_limits(LimitState *node) /* Reset position to start-of-scan */ node->position = 0; node->subSlot = NULL; + node->reachEnd = false; + node->backwardPosition = 0; /* Set state-machine state */ node->lstate = LIMIT_RESCAN; @@ -309,7 +480,8 @@ recompute_limits(LimitState *node) * Notify child node about limit. Note: think not to "optimize" by * skipping ExecSetTupleBound if compute_tuples_needed returns < 0. We * must update the child node anyway, in case this is a rescan and the - * previous time we got a different result. + * previous time we got a different result. In LIMIT_OPTION_PERCENT option there are + * no bound on the number of output tuples */ ExecSetTupleBound(compute_tuples_needed(node), outerPlanState(node)); } @@ -321,7 +493,7 @@ recompute_limits(LimitState *node) static int64 compute_tuples_needed(LimitState *node) { - if (node->noCount) + if (node->noCount || node->limitOption == LIMIT_OPTION_PERCENT) return -1; /* Note: if this overflows, we'll return a negative value, which is OK */ return node->count + node->offset; @@ -374,6 +546,7 @@ ExecInitLimit(Limit *node, EState *estate, int eflags) (PlanState *) limitstate); limitstate->limitCount = ExecInitExpr((Expr *) node->limitCount, (PlanState *) limitstate); + limitstate->limitOption = node->limitOption; /* * Initialize result type. @@ -390,6 +563,9 @@ ExecInitLimit(Limit *node, EState *estate, int eflags) */ limitstate->ps.ps_ProjInfo = NULL; + if (node->limitOption == LIMIT_OPTION_PERCENT) + limitstate->tupleStore = tuplestore_begin_heap(true, false, work_mem); + return limitstate; } @@ -405,6 +581,8 @@ ExecEndLimit(LimitState *node) { ExecFreeExprContext(&node->ps); ExecEndNode(outerPlanState(node)); + if (node->tupleStore != NULL) + tuplestore_end(node->tupleStore); } @@ -424,4 +602,6 @@ ExecReScanLimit(LimitState *node) */ if (node->ps.lefttree->chgParam == NULL) ExecReScan(node->ps.lefttree); + if (node->tupleStore != NULL) + tuplestore_clear(node->tupleStore); } diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c index a2617c7cfd..d2c6ababb3 100644 --- a/src/backend/nodes/copyfuncs.c +++ b/src/backend/nodes/copyfuncs.c @@ -1147,6 +1147,7 @@ _copyLimit(const Limit *from) */ COPY_NODE_FIELD(limitOffset); COPY_NODE_FIELD(limitCount); + COPY_SCALAR_FIELD(limitOption); return newnode; } @@ -3035,6 +3036,7 @@ _copyQuery(const Query *from) COPY_NODE_FIELD(sortClause); COPY_NODE_FIELD(limitOffset); COPY_NODE_FIELD(limitCount); + COPY_SCALAR_FIELD(limitOption); COPY_NODE_FIELD(rowMarks); COPY_NODE_FIELD(setOperations); COPY_NODE_FIELD(constraintDeps); @@ -3119,6 +3121,7 @@ _copySelectStmt(const SelectStmt *from) COPY_NODE_FIELD(sortClause); COPY_NODE_FIELD(limitOffset); COPY_NODE_FIELD(limitCount); + COPY_SCALAR_FIELD(limitOption); COPY_NODE_FIELD(lockingClause); COPY_NODE_FIELD(withClause); COPY_SCALAR_FIELD(op); diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c index 4f2ebe5118..2e14fa72b4 100644 --- a/src/backend/nodes/equalfuncs.c +++ b/src/backend/nodes/equalfuncs.c @@ -975,6 +975,7 @@ _equalQuery(const Query *a, const Query *b) COMPARE_NODE_FIELD(sortClause); COMPARE_NODE_FIELD(limitOffset); COMPARE_NODE_FIELD(limitCount); + COMPARE_SCALAR_FIELD(limitOption); COMPARE_NODE_FIELD(rowMarks); COMPARE_NODE_FIELD(setOperations); COMPARE_NODE_FIELD(constraintDeps); @@ -1049,6 +1050,7 @@ _equalSelectStmt(const SelectStmt *a, const SelectStmt *b) COMPARE_NODE_FIELD(sortClause); COMPARE_NODE_FIELD(limitOffset); COMPARE_NODE_FIELD(limitCount); + COMPARE_SCALAR_FIELD(limitOption); COMPARE_NODE_FIELD(lockingClause); COMPARE_NODE_FIELD(withClause); COMPARE_SCALAR_FIELD(op); diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.c index e6ce8e2110..959dc79a00 100644 --- a/src/backend/nodes/outfuncs.c +++ b/src/backend/nodes/outfuncs.c @@ -911,6 +911,7 @@ _outLimit(StringInfo str, const Limit *node) WRITE_NODE_FIELD(limitOffset); WRITE_NODE_FIELD(limitCount); + WRITE_ENUM_FIELD(limitOption, LimitOption); } static void @@ -2108,6 +2109,7 @@ _outLimitPath(StringInfo str, const LimitPath *node) WRITE_NODE_FIELD(subpath); WRITE_NODE_FIELD(limitOffset); WRITE_NODE_FIELD(limitCount); + WRITE_ENUM_FIELD(limitOption, LimitOption); } static void @@ -2704,6 +2706,7 @@ _outSelectStmt(StringInfo str, const SelectStmt *node) WRITE_NODE_FIELD(sortClause); WRITE_NODE_FIELD(limitOffset); WRITE_NODE_FIELD(limitCount); + WRITE_ENUM_FIELD(limitOption, LimitOption); WRITE_NODE_FIELD(lockingClause); WRITE_NODE_FIELD(withClause); WRITE_ENUM_FIELD(op, SetOperation); @@ -2914,6 +2917,7 @@ _outQuery(StringInfo str, const Query *node) WRITE_NODE_FIELD(sortClause); WRITE_NODE_FIELD(limitOffset); WRITE_NODE_FIELD(limitCount); + WRITE_ENUM_FIELD(limitOption, LimitOption); WRITE_NODE_FIELD(rowMarks); WRITE_NODE_FIELD(setOperations); WRITE_NODE_FIELD(constraintDeps); diff --git a/src/backend/nodes/readfuncs.c b/src/backend/nodes/readfuncs.c index 764e3bb90c..65a59d6650 100644 --- a/src/backend/nodes/readfuncs.c +++ b/src/backend/nodes/readfuncs.c @@ -278,6 +278,7 @@ _readQuery(void) READ_NODE_FIELD(sortClause); READ_NODE_FIELD(limitOffset); READ_NODE_FIELD(limitCount); + READ_ENUM_FIELD(limitOption, LimitOption); READ_NODE_FIELD(rowMarks); READ_NODE_FIELD(setOperations); READ_NODE_FIELD(constraintDeps); @@ -2337,6 +2338,7 @@ _readLimit(void) READ_NODE_FIELD(limitOffset); READ_NODE_FIELD(limitCount); + READ_ENUM_FIELD(limitOption, LimitOption); READ_DONE(); } diff --git a/src/backend/optimizer/plan/createplan.c b/src/backend/optimizer/plan/createplan.c index 0c036209f0..01c2590b08 100644 --- a/src/backend/optimizer/plan/createplan.c +++ b/src/backend/optimizer/plan/createplan.c @@ -2333,7 +2333,8 @@ create_minmaxagg_plan(PlannerInfo *root, MinMaxAggPath *best_path) plan = (Plan *) make_limit(plan, subparse->limitOffset, - subparse->limitCount); + subparse->limitCount, + subparse->limitOption); /* Must apply correct cost/width data to Limit node */ plan->startup_cost = mminfo->path->startup_cost; @@ -2646,7 +2647,8 @@ create_limit_plan(PlannerInfo *root, LimitPath *best_path, int flags) plan = make_limit(subplan, best_path->limitOffset, - best_path->limitCount); + best_path->limitCount, + best_path->limitOption); copy_generic_path_info(&plan->plan, (Path *) best_path); @@ -6549,7 +6551,7 @@ make_lockrows(Plan *lefttree, List *rowMarks, int epqParam) * Build a Limit plan node */ Limit * -make_limit(Plan *lefttree, Node *limitOffset, Node *limitCount) +make_limit(Plan *lefttree, Node *limitOffset, Node *limitCount, LimitOption limitOption) { Limit *node = makeNode(Limit); Plan *plan = &node->plan; @@ -6561,6 +6563,7 @@ make_limit(Plan *lefttree, Node *limitOffset, Node *limitCount) node->limitOffset = limitOffset; node->limitCount = limitCount; + node->limitOption = limitOption; return node; } diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c index 17c5f086fb..72452a7d5b 100644 --- a/src/backend/optimizer/plan/planner.c +++ b/src/backend/optimizer/plan/planner.c @@ -2315,6 +2315,7 @@ grouping_planner(PlannerInfo *root, bool inheritance_update, path = (Path *) create_limit_path(root, final_rel, path, parse->limitOffset, parse->limitCount, + parse->limitOption, offset_est, count_est); } @@ -2884,12 +2885,17 @@ preprocess_limit(PlannerInfo *root, double tuple_fraction, /* * A LIMIT clause limits the absolute number of tuples returned. * However, if it's not a constant LIMIT then we have to guess; for - * lack of a better idea, assume 10% of the plan's result is wanted. + * lack of a better idea, assume 10% of the plan's result is wanted for + * LIMIT_OPTION_COUNT and 100% wanted in a case of LIMIT_OPTION_PERCENT. + */ if (*count_est < 0 || *offset_est < 0) { /* LIMIT or OFFSET is an expression ... punt ... */ - limit_fraction = 0.10; + if (parse->limitOption == LIMIT_OPTION_PERCENT) + limit_fraction = 1.0; + else + limit_fraction = 0.10; } else { diff --git a/src/backend/optimizer/util/pathnode.c b/src/backend/optimizer/util/pathnode.c index 34acb732ee..15ad2ae6aa 100644 --- a/src/backend/optimizer/util/pathnode.c +++ b/src/backend/optimizer/util/pathnode.c @@ -3546,6 +3546,7 @@ LimitPath * create_limit_path(PlannerInfo *root, RelOptInfo *rel, Path *subpath, Node *limitOffset, Node *limitCount, + LimitOption limitOption, int64 offset_est, int64 count_est) { LimitPath *pathnode = makeNode(LimitPath); @@ -3567,6 +3568,7 @@ create_limit_path(PlannerInfo *root, RelOptInfo *rel, pathnode->subpath = subpath; pathnode->limitOffset = limitOffset; pathnode->limitCount = limitCount; + pathnode->limitOption = limitOption; /* * Adjust the output rows count and costs according to the offset/limit. @@ -3574,7 +3576,8 @@ create_limit_path(PlannerInfo *root, RelOptInfo *rel, adjust_limit_rows_costs(&pathnode->path.rows, &pathnode->path.startup_cost, &pathnode->path.total_cost, - offset_est, count_est); + offset_est, count_est, + limitOption); return pathnode; } @@ -3600,7 +3603,8 @@ adjust_limit_rows_costs(double *rows, /* in/out parameter */ Cost *startup_cost, /* in/out parameter */ Cost *total_cost, /* in/out parameter */ int64 offset_est, - int64 count_est) + int64 count_est, + LimitOption limitOption) { double input_rows = *rows; Cost input_startup_cost = *startup_cost; @@ -3633,6 +3637,17 @@ adjust_limit_rows_costs(double *rows, /* in/out parameter */ count_rows = (double) count_est; else count_rows = clamp_row_est(input_rows * 0.10); + if (limitOption == LIMIT_OPTION_PERCENT) + { + count_rows = clamp_row_est((input_rows * count_est) / 100); + if (rows > 0) + { + *startup_cost = count_rows * + input_total_cost / input_rows; + *total_cost = input_total_cost + + (count_rows * 0.1); + } + } if (count_rows > *rows) count_rows = *rows; if (input_rows > 0) diff --git a/src/backend/parser/analyze.c b/src/backend/parser/analyze.c index 85d7a96406..79b389009c 100644 --- a/src/backend/parser/analyze.c +++ b/src/backend/parser/analyze.c @@ -1288,10 +1288,11 @@ transformSelectStmt(ParseState *pstate, SelectStmt *stmt) } /* transform LIMIT */ - qry->limitOffset = transformLimitClause(pstate, stmt->limitOffset, + qry->limitOffset = transformLimitClause(pstate, stmt->limitOffset, stmt->limitOption, EXPR_KIND_OFFSET, "OFFSET"); - qry->limitCount = transformLimitClause(pstate, stmt->limitCount, + qry->limitCount = transformLimitClause(pstate, stmt->limitCount, stmt->limitOption, EXPR_KIND_LIMIT, "LIMIT"); + qry->limitOption = stmt->limitOption; /* transform window clauses after we have seen all window functions */ qry->windowClause = transformWindowDefinitions(pstate, @@ -1536,10 +1537,11 @@ transformValuesClause(ParseState *pstate, SelectStmt *stmt) EXPR_KIND_ORDER_BY, false /* allow SQL92 rules */ ); - qry->limitOffset = transformLimitClause(pstate, stmt->limitOffset, + qry->limitOffset = transformLimitClause(pstate, stmt->limitOffset, stmt->limitOption, EXPR_KIND_OFFSET, "OFFSET"); - qry->limitCount = transformLimitClause(pstate, stmt->limitCount, + qry->limitCount = transformLimitClause(pstate, stmt->limitCount, stmt->limitOption, EXPR_KIND_LIMIT, "LIMIT"); + qry->limitOption = stmt->limitOption; if (stmt->lockingClause) ereport(ERROR, @@ -1770,10 +1772,11 @@ transformSetOperationStmt(ParseState *pstate, SelectStmt *stmt) parser_errposition(pstate, exprLocation(list_nth(qry->targetList, tllen))))); - qry->limitOffset = transformLimitClause(pstate, limitOffset, + qry->limitOffset = transformLimitClause(pstate, limitOffset, stmt->limitOption, EXPR_KIND_OFFSET, "OFFSET"); - qry->limitCount = transformLimitClause(pstate, limitCount, + qry->limitCount = transformLimitClause(pstate, limitCount, stmt->limitOption, EXPR_KIND_LIMIT, "LIMIT"); + qry->limitOption = stmt->limitOption; qry->rtable = pstate->p_rtable; qry->jointree = makeFromExpr(pstate->p_joinlist, NULL); diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index a954acf509..789aab7fe4 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -127,6 +127,21 @@ typedef struct ImportQual List *table_names; } ImportQual; +/* Private struct for the result of opt_select_limit production */ +typedef struct SelectLimit +{ + Node *limitOffset; + Node *limitCount; + LimitOption limitOption; +} SelectLimit; + +/* Private struct for the result of limit_clause production */ +typedef struct LimitClause +{ + Node *limitCount; + LimitOption limitOption; +} LimitClause; + /* ConstraintAttributeSpec yields an integer bitmask of these flags: */ #define CAS_NOT_DEFERRABLE 0x01 #define CAS_DEFERRABLE 0x02 @@ -165,6 +180,7 @@ static List *makeOrderedSetArgs(List *directargs, List *orderedargs, static void insertSelectOptions(SelectStmt *stmt, List *sortClause, List *lockingClause, Node *limitOffset, Node *limitCount, + LimitOption limitOption, WithClause *withClause, core_yyscan_t yyscanner); static Node *makeSetOp(SetOperation op, bool all, Node *larg, Node *rarg); @@ -242,6 +258,8 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); PartitionSpec *partspec; PartitionBoundSpec *partboundspec; RoleSpec *rolespec; + struct SelectLimit *SelectLimit; + struct LimitClause *LimitClause; } %type <node> stmt schema_stmt @@ -373,6 +391,8 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); %type <ival> import_qualification_type %type <importqual> import_qualification %type <node> vacuum_relation +%type <SelectLimit> opt_select_limit select_limit +%type <LimitClause> limit_clause %type <list> stmtblock stmtmulti OptTableElementList TableElementList OptInherit definition @@ -393,8 +413,8 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); target_list opt_target_list insert_column_list set_target_list set_clause_list set_clause def_list operator_def_list indirection opt_indirection - reloption_list group_clause TriggerFuncArgs select_limit - opt_select_limit opclass_item_list opclass_drop_list + reloption_list group_clause TriggerFuncArgs + opclass_item_list opclass_drop_list opclass_purpose opt_opfamily transaction_mode_list_or_empty OptTableFuncElementList TableFuncElementList opt_type_modifiers prep_type_clause @@ -455,7 +475,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); comment_type_any_name comment_type_name security_label_type_any_name security_label_type_name -%type <node> fetch_args limit_clause select_limit_value +%type <node> fetch_args select_limit_value offset_clause select_offset_value select_fetch_first_value I_or_F_const %type <ival> row_or_rows first_or_next @@ -667,7 +687,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); ORDER ORDINALITY OTHERS OUT_P OUTER_P OVER OVERLAPS OVERLAY OVERRIDING OWNED OWNER - PARALLEL PARSER PARTIAL PARTITION PASSING PASSWORD PLACING PLANS POLICY + PARALLEL PARSER PARTIAL PARTITION PASSING PASSWORD PERCENT PLACING PLANS POLICY POSITION PRECEDING PRECISION PRESERVE PREPARE PREPARED PRIMARY PRIOR PRIVILEGES PROCEDURAL PROCEDURE PROCEDURES PROGRAM PUBLICATION @@ -11225,14 +11245,15 @@ select_no_parens: | select_clause sort_clause { insertSelectOptions((SelectStmt *) $1, $2, NIL, - NULL, NULL, NULL, + NULL, NULL, LIMIT_OPTION_DEFAULT, NULL, yyscanner); $$ = $1; } | select_clause opt_sort_clause for_locking_clause opt_select_limit { insertSelectOptions((SelectStmt *) $1, $2, $3, - list_nth($4, 0), list_nth($4, 1), + ($4)->limitOffset, ($4)->limitCount, + ($4)->limitOption, NULL, yyscanner); $$ = $1; @@ -11240,7 +11261,8 @@ select_no_parens: | select_clause opt_sort_clause select_limit opt_for_locking_clause { insertSelectOptions((SelectStmt *) $1, $2, $4, - list_nth($3, 0), list_nth($3, 1), + ($3)->limitOffset, ($3)->limitCount, + ($3)->limitOption, NULL, yyscanner); $$ = $1; @@ -11249,7 +11271,7 @@ select_no_parens: { insertSelectOptions((SelectStmt *) $2, NULL, NIL, NULL, NULL, - $1, + LIMIT_OPTION_DEFAULT, $1, yyscanner); $$ = $2; } @@ -11257,14 +11279,15 @@ select_no_parens: { insertSelectOptions((SelectStmt *) $2, $3, NIL, NULL, NULL, - $1, + LIMIT_OPTION_DEFAULT, $1, yyscanner); $$ = $2; } | with_clause select_clause opt_sort_clause for_locking_clause opt_select_limit { insertSelectOptions((SelectStmt *) $2, $3, $4, - list_nth($5, 0), list_nth($5, 1), + ($5)->limitOffset, ($5)->limitCount, + ($5)->limitOption, $1, yyscanner); $$ = $2; @@ -11272,7 +11295,8 @@ select_no_parens: | with_clause select_clause opt_sort_clause select_limit opt_for_locking_clause { insertSelectOptions((SelectStmt *) $2, $3, $5, - list_nth($4, 0), list_nth($4, 1), + ($4)->limitOffset, ($4)->limitCount, + ($4)->limitOption, $1, yyscanner); $$ = $2; @@ -11566,20 +11590,60 @@ sortby: a_expr USING qual_all_Op opt_nulls_order select_limit: - limit_clause offset_clause { $$ = list_make2($2, $1); } - | offset_clause limit_clause { $$ = list_make2($1, $2); } - | limit_clause { $$ = list_make2(NULL, $1); } - | offset_clause { $$ = list_make2($1, NULL); } + limit_clause offset_clause + { + SelectLimit *n = (SelectLimit *) palloc(sizeof(SelectLimit)); + n->limitOffset = $2; + n->limitCount = ($1)->limitCount; + n->limitOption = ($1)->limitOption; + $$ = n; + } + | offset_clause limit_clause + { + SelectLimit *n = (SelectLimit *) palloc(sizeof(SelectLimit)); + n->limitOffset = $1; + n->limitCount = ($2)->limitCount; + n->limitOption = ($2)->limitOption; + $$ = n; + } + | limit_clause + { + SelectLimit *n = (SelectLimit *) palloc(sizeof(SelectLimit)); + n->limitOffset = NULL; + n->limitCount = ($1)->limitCount; + n->limitOption = ($1)->limitOption; + $$ = n; + } + | offset_clause + { + SelectLimit *n = (SelectLimit *) palloc(sizeof(SelectLimit)); + n->limitOffset = $1; + n->limitCount = NULL; + n->limitOption = LIMIT_OPTION_DEFAULT; + $$ = n; + } ; opt_select_limit: select_limit { $$ = $1; } - | /* EMPTY */ { $$ = list_make2(NULL,NULL); } + | /* EMPTY */ + { + SelectLimit *n = (SelectLimit *) palloc(sizeof(SelectLimit)); + n->limitOffset = NULL; + n->limitCount = NULL; + n->limitOption = LIMIT_OPTION_DEFAULT; + $$ = n; + } ; limit_clause: LIMIT select_limit_value - { $$ = $2; } + { + LimitClause *n = (LimitClause *) palloc(sizeof(LimitClause)); + n->limitCount = $2; + n->limitOption = LIMIT_OPTION_COUNT; + $$ = n; + } | LIMIT select_limit_value ',' select_offset_value { /* Disabled because it was too confusing, bjm 2002-02-18 */ @@ -11597,9 +11661,26 @@ limit_clause: * we can see the ONLY token in the lookahead slot. */ | FETCH first_or_next select_fetch_first_value row_or_rows ONLY - { $$ = $3; } + { + LimitClause *n = (LimitClause *) palloc(sizeof(LimitClause)); + n->limitCount = $3; + n->limitOption = LIMIT_OPTION_COUNT; + $$ = n; + } + | FETCH first_or_next select_fetch_first_value PERCENT row_or_rows ONLY + { + LimitClause *n = (LimitClause *) palloc(sizeof(LimitClause)); + n->limitCount = $3; + n->limitOption = LIMIT_OPTION_PERCENT; + $$ = n; + } | FETCH first_or_next row_or_rows ONLY - { $$ = makeIntConst(1, -1); } + { + LimitClause *n = (LimitClause *) palloc(sizeof(LimitClause)); + n->limitCount = makeIntConst(1, -1); + n->limitOption = LIMIT_OPTION_COUNT; + $$ = n; + } ; offset_clause: @@ -15192,6 +15273,7 @@ unreserved_keyword: | PARTITION | PASSING | PASSWORD + | PERCENT | PLANS | POLICY | PRECEDING @@ -15856,6 +15938,7 @@ static void insertSelectOptions(SelectStmt *stmt, List *sortClause, List *lockingClause, Node *limitOffset, Node *limitCount, + LimitOption limitOption, WithClause *withClause, core_yyscan_t yyscanner) { @@ -15894,6 +15977,14 @@ insertSelectOptions(SelectStmt *stmt, parser_errposition(exprLocation(limitCount)))); stmt->limitCount = limitCount; } + if (limitOption && limitOption != LIMIT_OPTION_DEFAULT) + { + if (stmt->limitOption) + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("multiple LIMIT options not allowed"))); + stmt->limitOption = limitOption; + } if (withClause) { if (stmt->withClause) diff --git a/src/backend/parser/parse_clause.c b/src/backend/parser/parse_clause.c index 260ccd4d7f..884a4a01b4 100644 --- a/src/backend/parser/parse_clause.c +++ b/src/backend/parser/parse_clause.c @@ -1704,7 +1704,7 @@ transformWhereClause(ParseState *pstate, Node *clause, * constructName does not affect the semantics, but is used in error messages */ Node * -transformLimitClause(ParseState *pstate, Node *clause, +transformLimitClause(ParseState *pstate, Node *clause, LimitOption limitOption, ParseExprKind exprKind, const char *constructName) { Node *qual; @@ -1713,8 +1713,10 @@ transformLimitClause(ParseState *pstate, Node *clause, return NULL; qual = transformExpr(pstate, clause, exprKind); - - qual = coerce_to_specific_type(pstate, qual, INT8OID, constructName); + if (limitOption == LIMIT_OPTION_PERCENT && strcmp(constructName, "LIMIT") == 0) + qual = coerce_to_specific_type(pstate, qual, FLOAT8OID, constructName); + else + qual = coerce_to_specific_type(pstate, qual, INT8OID, constructName); /* LIMIT can't refer to any variables of the current query */ checkExprIsVarFree(pstate, qual, constructName); diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c index 3e64390d81..bf88e276e7 100644 --- a/src/backend/utils/adt/ruleutils.c +++ b/src/backend/utils/adt/ruleutils.c @@ -5310,7 +5310,15 @@ get_select_query_def(Query *query, deparse_context *context, -PRETTYINDENT_STD, PRETTYINDENT_STD, 0); get_rule_expr(query->limitOffset, context, false); } - if (query->limitCount != NULL) + if (query->limitOption == LIMIT_OPTION_PERCENT) + { + appendContextKeyword(context, " FETCH FIRST ", + -PRETTYINDENT_STD, PRETTYINDENT_STD, 0); + get_rule_expr(query->limitCount, context, false); + appendContextKeyword(context, " PERCENT ROWS ONLY ", + -PRETTYINDENT_STD, PRETTYINDENT_STD, 0); + } + if (query->limitCount != NULL && query->limitOption != LIMIT_OPTION_PERCENT) { appendContextKeyword(context, " LIMIT ", -PRETTYINDENT_STD, PRETTYINDENT_STD, 0); diff --git a/src/backend/utils/sort/tuplestore.c b/src/backend/utils/sort/tuplestore.c index 3fc7f92182..a03e568f1f 100644 --- a/src/backend/utils/sort/tuplestore.c +++ b/src/backend/utils/sort/tuplestore.c @@ -1100,6 +1100,39 @@ tuplestore_gettupleslot(Tuplestorestate *state, bool forward, } } +/* + * tuplestore_gettupleslot_heaptuple + * It is similar to tuplestore_gettupleslot except it return stored HeapTuple + * instead of MinimalTuple + */ +bool +tuplestore_gettupleslot_heaptuple(Tuplestorestate *state, bool forward, + bool copy, TupleTableSlot *slot) +{ + MinimalTuple tuple; + HeapTuple htuple; + bool should_free; + + tuple = (MinimalTuple) tuplestore_gettuple(state, forward, &should_free); + + if (tuple) + { + if (copy && !should_free) + { + tuple = heap_copy_minimal_tuple(tuple); + should_free = true; + } + htuple = heap_tuple_from_minimal_tuple(tuple); + ExecForceStoreHeapTuple(htuple, slot, should_free); + return true; + } + else + { + ExecClearTuple(slot); + return false; + } +} + /* * tuplestore_advance - exported function to adjust position without fetching * diff --git a/src/include/nodes/execnodes.h b/src/include/nodes/execnodes.h index f42189d2bf..b41151cd50 100644 --- a/src/include/nodes/execnodes.h +++ b/src/include/nodes/execnodes.h @@ -2299,8 +2299,14 @@ typedef struct LimitState PlanState ps; /* its first field is NodeTag */ ExprState *limitOffset; /* OFFSET parameter, or NULL if none */ ExprState *limitCount; /* COUNT parameter, or NULL if none */ + LimitOption limitOption; /* limit specification type */ int64 offset; /* current OFFSET value */ int64 count; /* current COUNT, if any */ + float8 percent; /* percentage */ + int64 backwardPosition; /* the number of tuple returned in + * backward scan */ + bool reachEnd; /* if true, outerPlan executed until the end */ + Tuplestorestate *tupleStore; /* holds the returned tuple */ bool noCount; /* if true, ignore count */ LimitStateCond lstate; /* state machine status, as above */ int64 position; /* 1-based index of last tuple returned */ diff --git a/src/include/nodes/nodes.h b/src/include/nodes/nodes.h index 3cbb08df92..08bd32867e 100644 --- a/src/include/nodes/nodes.h +++ b/src/include/nodes/nodes.h @@ -821,4 +821,17 @@ typedef enum OnConflictAction ONCONFLICT_UPDATE /* ON CONFLICT ... DO UPDATE */ } OnConflictAction; +/* + * LimitOption - + * LIMIT option of query + * + * This is needed in both parsenodes.h and plannodes.h, so put it here... + */ +typedef enum LimitOption +{ + LIMIT_OPTION_COUNT, /* LIMIT in exact number of rows */ + LIMIT_OPTION_PERCENT, /* LIMIT in percentage */ + LIMIT_OPTION_DEFAULT /* There are no LIMIT */ +}LimitOption; + #endif /* NODES_H */ diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h index 94ded3c135..b158e9eaeb 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -159,6 +159,7 @@ typedef struct Query Node *limitOffset; /* # of result tuples to skip (int8 expr) */ Node *limitCount; /* # of result tuples to return (int8 expr) */ + LimitOption limitOption; /* limit type */ List *rowMarks; /* a list of RowMarkClause's */ @@ -1595,6 +1596,7 @@ typedef struct SelectStmt List *sortClause; /* sort clause (a list of SortBy's) */ Node *limitOffset; /* # of result tuples to skip */ Node *limitCount; /* # of result tuples to return */ + LimitOption limitOption; /* limit type */ List *lockingClause; /* FOR UPDATE (list of LockingClause's) */ WithClause *withClause; /* WITH clause */ diff --git a/src/include/nodes/pathnodes.h b/src/include/nodes/pathnodes.h index 23a06d718e..b77697f45a 100644 --- a/src/include/nodes/pathnodes.h +++ b/src/include/nodes/pathnodes.h @@ -1793,6 +1793,7 @@ typedef struct LimitPath Path *subpath; /* path representing input source */ Node *limitOffset; /* OFFSET parameter, or NULL if none */ Node *limitCount; /* COUNT parameter, or NULL if none */ + LimitOption limitOption; /* LIMIT in percentage or exact number */ } LimitPath; diff --git a/src/include/nodes/plannodes.h b/src/include/nodes/plannodes.h index 8e6594e355..c8c497295b 100644 --- a/src/include/nodes/plannodes.h +++ b/src/include/nodes/plannodes.h @@ -967,6 +967,7 @@ typedef struct Limit Plan plan; Node *limitOffset; /* OFFSET parameter, or NULL if none */ Node *limitCount; /* COUNT parameter, or NULL if none */ + LimitOption limitOption; /* LIMIT in percentage or exact number */ } Limit; diff --git a/src/include/optimizer/pathnode.h b/src/include/optimizer/pathnode.h index a12af54971..117ed8d761 100644 --- a/src/include/optimizer/pathnode.h +++ b/src/include/optimizer/pathnode.h @@ -262,10 +262,11 @@ extern ModifyTablePath *create_modifytable_path(PlannerInfo *root, extern LimitPath *create_limit_path(PlannerInfo *root, RelOptInfo *rel, Path *subpath, Node *limitOffset, Node *limitCount, + LimitOption limitOption, int64 offset_est, int64 count_est); extern void adjust_limit_rows_costs(double *rows, Cost *startup_cost, Cost *total_cost, - int64 offset_est, int64 count_est); + int64 offset_est, int64 count_est, LimitOption limitOption); extern Path *reparameterize_path(PlannerInfo *root, Path *path, Relids required_outer, diff --git a/src/include/optimizer/planmain.h b/src/include/optimizer/planmain.h index e7aaddd50d..3395e558c4 100644 --- a/src/include/optimizer/planmain.h +++ b/src/include/optimizer/planmain.h @@ -56,7 +56,7 @@ extern Agg *make_agg(List *tlist, List *qual, int numGroupCols, AttrNumber *grpColIdx, Oid *grpOperators, Oid *grpCollations, List *groupingSets, List *chain, double dNumGroups, Plan *lefttree); -extern Limit *make_limit(Plan *lefttree, Node *limitOffset, Node *limitCount); +extern Limit *make_limit(Plan *lefttree, Node *limitOffset, Node *limitCount, LimitOption limitOption); /* * prototypes for plan/initsplan.c diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h index 00ace8425e..e1a8d703ab 100644 --- a/src/include/parser/kwlist.h +++ b/src/include/parser/kwlist.h @@ -299,6 +299,7 @@ PG_KEYWORD("partial", PARTIAL, UNRESERVED_KEYWORD) PG_KEYWORD("partition", PARTITION, UNRESERVED_KEYWORD) PG_KEYWORD("passing", PASSING, UNRESERVED_KEYWORD) PG_KEYWORD("password", PASSWORD, UNRESERVED_KEYWORD) +PG_KEYWORD("percent", PERCENT, UNRESERVED_KEYWORD) PG_KEYWORD("placing", PLACING, RESERVED_KEYWORD) PG_KEYWORD("plans", PLANS, UNRESERVED_KEYWORD) PG_KEYWORD("policy", POLICY, UNRESERVED_KEYWORD) diff --git a/src/include/parser/parse_clause.h b/src/include/parser/parse_clause.h index 42adc63d1f..e66510e965 100644 --- a/src/include/parser/parse_clause.h +++ b/src/include/parser/parse_clause.h @@ -22,7 +22,7 @@ extern int setTargetTable(ParseState *pstate, RangeVar *relation, extern Node *transformWhereClause(ParseState *pstate, Node *clause, ParseExprKind exprKind, const char *constructName); -extern Node *transformLimitClause(ParseState *pstate, Node *clause, +extern Node *transformLimitClause(ParseState *pstate, Node *clause, LimitOption limitOption, ParseExprKind exprKind, const char *constructName); extern List *transformGroupClause(ParseState *pstate, List *grouplist, List **groupingSets, diff --git a/src/include/utils/tuplestore.h b/src/include/utils/tuplestore.h index f9b6fcec29..eeefde3da4 100644 --- a/src/include/utils/tuplestore.h +++ b/src/include/utils/tuplestore.h @@ -47,7 +47,6 @@ typedef struct Tuplestorestate Tuplestorestate; extern Tuplestorestate *tuplestore_begin_heap(bool randomAccess, bool interXact, int maxKBytes); - extern void tuplestore_set_eflags(Tuplestorestate *state, int eflags); extern void tuplestore_puttupleslot(Tuplestorestate *state, @@ -72,9 +71,12 @@ extern bool tuplestore_in_memory(Tuplestorestate *state); extern bool tuplestore_gettupleslot(Tuplestorestate *state, bool forward, bool copy, TupleTableSlot *slot); +extern bool tuplestore_gettupleslot_heaptuple(Tuplestorestate *state, bool forward, + bool copy, TupleTableSlot *slot); extern bool tuplestore_advance(Tuplestorestate *state, bool forward); + extern bool tuplestore_skiptuples(Tuplestorestate *state, int64 ntuples, bool forward); diff --git a/src/test/regress/expected/limit.out b/src/test/regress/expected/limit.out index c18f547cbd..95f9f5f3d9 100644 --- a/src/test/regress/expected/limit.out +++ b/src/test/regress/expected/limit.out @@ -108,6 +108,63 @@ SELECT ''::text AS five, unique1, unique2, stringu1 | 904 | 793 | UIAAAA (5 rows) +-- +-- PERCENT +-- Check the PERCENT option of limit clause +-- +SELECT ''::text AS two, unique1, unique2, stringu1 + FROM onek WHERE unique1 > 50 + ORDER BY unique1 FETCH FIRST 1 PERCENT ROWS ONLY; + two | unique1 | unique2 | stringu1 +-----+---------+---------+---------- + | 51 | 76 | ZBAAAA + | 52 | 985 | ACAAAA + | 53 | 196 | BCAAAA + | 54 | 356 | CCAAAA + | 55 | 627 | DCAAAA + | 56 | 54 | ECAAAA + | 57 | 942 | FCAAAA + | 58 | 114 | GCAAAA + | 59 | 593 | HCAAAA + | 60 | 483 | ICAAAA +(10 rows) + +SELECT ''::text AS two, unique1, unique2, stringu1 + FROM onek WHERE unique1 > 60 AND unique1 < 63 + ORDER BY unique1 FETCH FIRST 50 PERCENT ROWS ONLY; + two | unique1 | unique2 | stringu1 +-----+---------+---------+---------- + | 61 | 560 | JCAAAA +(1 row) + +SELECT ''::text AS three, unique1, unique2, stringu1 + FROM onek WHERE unique1 > 100 + ORDER BY unique1 FETCH FIRST 1 PERCENT ROWS ONLY OFFSET 20; + three | unique1 | unique2 | stringu1 +-------+---------+---------+---------- + | 121 | 700 | REAAAA + | 122 | 519 | SEAAAA + | 123 | 777 | TEAAAA + | 124 | 503 | UEAAAA + | 125 | 849 | VEAAAA + | 126 | 330 | WEAAAA + | 127 | 511 | XEAAAA + | 128 | 721 | YEAAAA + | 129 | 696 | ZEAAAA +(9 rows) + +SELECT ''::text AS eleven, unique1, unique2, stringu1 + FROM onek WHERE unique1 < 50 + ORDER BY unique1 DESC FETCH FIRST 10 PERCENT ROWS ONLY OFFSET 39; + eleven | unique1 | unique2 | stringu1 +--------+---------+---------+---------- + | 10 | 520 | KAAAAA + | 9 | 49 | JAAAAA + | 8 | 653 | IAAAAA + | 7 | 647 | HAAAAA + | 6 | 978 | GAAAAA +(5 rows) + -- Test null limit and offset. The planner would discard a simple null -- constant, so to ensure executor is exercised, do this: select * from int8_tbl limit (case when random() < 0.5 then null::bigint end); @@ -286,6 +343,46 @@ fetch all in c4; ----+---- (0 rows) +declare c5 cursor for select * from int8_tbl fetch first 50 percent rows only; +fetch all in c5; + q1 | q2 +------------------+------------------ + 123 | 456 + 123 | 4567890123456789 + 4567890123456789 | 123 +(3 rows) + +fetch 1 in c5; + q1 | q2 +----+---- +(0 rows) + +fetch backward 1 in c5; + q1 | q2 +------------------+----- + 4567890123456789 | 123 +(1 row) + +fetch backward all in c5; + q1 | q2 +-----+------------------ + 123 | 4567890123456789 + 123 | 456 +(2 rows) + +fetch backward 1 in c5; + q1 | q2 +----+---- +(0 rows) + +fetch all in c5; + q1 | q2 +------------------+------------------ + 123 | 456 + 123 | 4567890123456789 + 4567890123456789 | 123 +(3 rows) + rollback; -- Stress test for variable LIMIT in conjunction with bounded-heap sorting SELECT @@ -503,3 +600,19 @@ select sum(tenthous) as s1, sum(tenthous) + random()*0 as s2 45020 | 45020 (3 rows) +select sum(tenthous) as s1, sum(tenthous) + random()*0 as s2 + from tenk1 group by thousand order by thousand FETCH FIRST 1 PERCENT ROWS ONLY; + s1 | s2 +-------+------- + 45000 | 45000 + 45010 | 45010 + 45020 | 45020 + 45030 | 45030 + 45040 | 45040 + 45050 | 45050 + 45060 | 45060 + 45070 | 45070 + 45080 | 45080 + 45090 | 45090 +(10 rows) + diff --git a/src/test/regress/sql/limit.sql b/src/test/regress/sql/limit.sql index 2a313d80ca..051b21a099 100644 --- a/src/test/regress/sql/limit.sql +++ b/src/test/regress/sql/limit.sql @@ -31,6 +31,23 @@ SELECT ''::text AS five, unique1, unique2, stringu1 FROM onek ORDER BY unique1 LIMIT 5 OFFSET 900; +-- +-- PERCENT +-- Check the PERCENT option of limit clause +-- +SELECT ''::text AS two, unique1, unique2, stringu1 + FROM onek WHERE unique1 > 50 + ORDER BY unique1 FETCH FIRST 1 PERCENT ROWS ONLY; +SELECT ''::text AS two, unique1, unique2, stringu1 + FROM onek WHERE unique1 > 60 AND unique1 < 63 + ORDER BY unique1 FETCH FIRST 50 PERCENT ROWS ONLY; +SELECT ''::text AS three, unique1, unique2, stringu1 + FROM onek WHERE unique1 > 100 + ORDER BY unique1 FETCH FIRST 1 PERCENT ROWS ONLY OFFSET 20; +SELECT ''::text AS eleven, unique1, unique2, stringu1 + FROM onek WHERE unique1 < 50 + ORDER BY unique1 DESC FETCH FIRST 10 PERCENT ROWS ONLY OFFSET 39; + -- Test null limit and offset. The planner would discard a simple null -- constant, so to ensure executor is exercised, do this: select * from int8_tbl limit (case when random() < 0.5 then null::bigint end); @@ -38,7 +55,6 @@ select * from int8_tbl offset (case when random() < 0.5 then null::bigint end); -- Test assorted cases involving backwards fetch from a LIMIT plan node begin; - declare c1 cursor for select * from int8_tbl limit 10; fetch all in c1; fetch 1 in c1; @@ -71,6 +87,14 @@ fetch backward all in c4; fetch backward 1 in c4; fetch all in c4; +declare c5 cursor for select * from int8_tbl fetch first 50 percent rows only; +fetch all in c5; +fetch 1 in c5; +fetch backward 1 in c5; +fetch backward all in c5; +fetch backward 1 in c5; +fetch all in c5; + rollback; -- Stress test for variable LIMIT in conjunction with bounded-heap sorting @@ -141,3 +165,6 @@ select sum(tenthous) as s1, sum(tenthous) + random()*0 as s2 select sum(tenthous) as s1, sum(tenthous) + random()*0 as s2 from tenk1 group by thousand order by thousand limit 3; + +select sum(tenthous) as s1, sum(tenthous) + random()*0 as s2 + from tenk1 group by thousand order by thousand FETCH FIRST 1 PERCENT ROWS ONLY;