Hi
> PERCENT and WITH TIES can play together, per spec.
>
I also Implement PERCENT WITH TIES option. patch is attached
i don't start a new tread because the patches share common code
regards
Surafel
diff --git a/contrib/postgres_fdw/postgres_fdw.c b/contrib/postgres_fdw/postgres_fdw.c
index 9fc53cad68..d512f4ddd0 100644
--- a/contrib/postgres_fdw/postgres_fdw.c
+++ b/contrib/postgres_fdw/postgres_fdw.c
@@ -3097,7 +3097,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);
+ LIMIT_OPTION_COUNT, fpextra->offset_est,
+ fpextra->count_est);
retrieved_rows = rows;
}
}
diff --git a/doc/src/sgml/ref/select.sgml b/doc/src/sgml/ref/select.sgml
index b93e4ca208..9811380ec6 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 | WITH TIES } ]
+ [ FETCH { FIRST | NEXT } [ <replaceable class="parameter">count</replaceable> ] [ PERCENT ] { ROW | ROWS } { ONLY | WITH TIES } ]
[ 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>
@@ -1434,7 +1434,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 | WITH TIES }
+FETCH { FIRST | NEXT } [ <replaceable class="parameter">count</replaceable> ] [ PERCENT ] { ROW | ROWS } { ONLY | WITH TIES }
</synopsis>
In this syntax, the <replaceable class="parameter">start</replaceable>
or <replaceable class="parameter">count</replaceable> value is required by
@@ -1444,6 +1444,9 @@ 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.
+ 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.
The <literal>WITH TIES</literal> option is used to return any additional
rows that tie for the last place in the result set according to
the <literal>ORDER BY</literal> clause; <literal>ORDER BY</literal>
diff --git a/src/backend/catalog/sql_features.txt b/src/backend/catalog/sql_features.txt
index b6e58e8493..68e0c5e2e1 100644
--- a/src/backend/catalog/sql_features.txt
+++ b/src/backend/catalog/sql_features.txt
@@ -344,7 +344,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 YES
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 d85cf7d93e..cfb28a9fd4 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"
@@ -29,6 +31,8 @@
static void recompute_limits(LimitState *node);
static int64 compute_tuples_needed(LimitState *node);
+#define IsPercentOption(opt) \
+ (opt == LIMIT_OPTION_PERCENT || opt == LIMIT_OPTION_PER_WITH_TIES)
/* ----------------------------------------------------------------
* ExecLimit
@@ -53,6 +57,7 @@ ExecLimit(PlanState *pstate)
*/
direction = node->ps.state->es_direction;
outerPlan = outerPlanState(node);
+ slot = node->subSlot;
/*
* The main logic is a simple state machine.
@@ -82,7 +87,15 @@ ExecLimit(PlanState *pstate)
/*
* Check for empty window; if so, treat like empty subplan.
*/
- if (node->count <= 0 && !node->noCount)
+ if (IsPercentOption(node->limitOption))
+ {
+ if (node->percent == 0.0)
+ {
+ node->lstate = LIMIT_EMPTY;
+ return NULL;
+ }
+ }
+ else if (node->count <= 0 && !node->noCount)
{
node->lstate = LIMIT_EMPTY;
return NULL;
@@ -118,6 +131,16 @@ ExecLimit(PlanState *pstate)
break;
}
+ /*
+ * We may needed this tuple in subsequent scan so put it into
+ * tuplestore.
+ */
+ if (IsPercentOption(node->limitOption))
+ {
+ tuplestore_puttupleslot(node->tupleStore, slot);
+ tuplestore_advance(node->tupleStore, true);
+ }
+
/*
* Okay, we have the first tuple of the window.
*/
@@ -135,6 +158,85 @@ 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 (IsPercentOption(node->limitOption) && 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 (IsPercentOption(node->limitOption) && 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 (IsPercentOption(node->limitOption))
+ {
+ /*
+ * 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. At
* the end of the window, the behavior depends on whether WITH
@@ -152,7 +254,8 @@ ExecLimit(PlanState *pstate)
* whether rescans are possible.
*/
if (!node->noCount &&
- node->position - node->offset >= node->count)
+ node->position - node->offset >= node->count
+ && !IsPercentOption(node->limitOption))
{
if (node->limitOption == LIMIT_OPTION_COUNT)
{
@@ -166,54 +269,88 @@ ExecLimit(PlanState *pstate)
}
}
else
+ {
+ if (IsPercentOption(node->limitOption))
+ {
+ if (tuplestore_gettupleslot_heaptuple(node->tupleStore, true, true, slot))
+ {
+ node->subSlot = slot;
+ node->position++;
+ break;
+ }
+ else
+ {
+ node->lstate = LIMIT_SUBPLANEOF;
+ return NULL;
+ }
+
+ }
+ else
+ {
+ /*
+ * Get next tuple from subplan, if any.
+ */
+ slot = ExecProcNode(outerPlan);
+ if (TupIsNull(slot))
+ {
+ node->lstate = LIMIT_SUBPLANEOF;
+ return NULL;
+ }
+
+ /*
+ * If WITH TIES is active, and this is the last
+ * in-window tuple, save it to be used in subsequent
+ * WINDOWEND_TIES processing.
+ */
+ if (node->limitOption == LIMIT_OPTION_WITH_TIES &&
+ node->position - node->offset == node->count - 1)
+ {
+ ExecCopySlot(node->last_slot, slot);
+ }
+ node->subSlot = slot;
+ node->position++;
+ break;
+ }
+ }
+ }
+ else
+ {
+ /*
+ * Backwards scan, so check for stepping off start of window.
+ * As above, only change state-machine status if so.
+ */
+ if (node->position <= node->offset + 1)
+ {
+ node->lstate = LIMIT_WINDOWSTART;
+ return NULL;
+ }
+
+ /* In percent case the result is already in tuplestore */
+ if (IsPercentOption(node->limitOption))
+ {
+ if (tuplestore_gettupleslot_heaptuple(node->tupleStore, false, true, slot))
+ {
+ node->subSlot = slot;
+ node->position--;
+ node->backwardPosition++;
+ break;
+ }
+ else
+ elog(ERROR, "LIMIT subplan failed to run backwards");
+ }
+ else
{
/*
- * Get next tuple from subplan, if any.
+ * Get previous tuple from subplan; there should be one!
*/
slot = ExecProcNode(outerPlan);
if (TupIsNull(slot))
- {
- node->lstate = LIMIT_SUBPLANEOF;
- return NULL;
- }
-
- /*
- * If WITH TIES is active, and this is the last in-window
- * tuple, save it to be used in subsequent WINDOWEND_TIES
- * processing.
- */
- if (node->limitOption == LIMIT_OPTION_WITH_TIES &&
- node->position - node->offset == node->count - 1)
- {
- ExecCopySlot(node->last_slot, slot);
- }
+ elog(ERROR, "LIMIT subplan failed to run backwards");
node->subSlot = slot;
- node->position++;
+ node->position--;
break;
}
}
- else
- {
- /*
- * Backwards scan, so check for stepping off start of window.
- * As above, only change state-machine status if so.
- */
- if (node->position <= node->offset + 1)
- {
- 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--;
- break;
- }
Assert(node->lstate == LIMIT_WINDOWEND_TIES);
/* FALL THRU */
@@ -279,15 +416,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 (IsPercentOption(node->limitOption))
+ {
+ 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
+ {
+ /*
+ * 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:
@@ -393,12 +547,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 (IsPercentOption(node->limitOption))
+ {
+ /*
+ * 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
@@ -411,6 +589,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;
@@ -419,7 +599,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));
}
@@ -431,7 +612,8 @@ recompute_limits(LimitState *node)
static int64
compute_tuples_needed(LimitState *node)
{
- if ((node->noCount) || (node->limitOption == LIMIT_OPTION_WITH_TIES))
+ if ((node->noCount) || (node->limitOption == LIMIT_OPTION_WITH_TIES)
+ || (IsPercentOption(node->limitOption)))
return -1;
/* Note: if this overflows, we'll return a negative value, which is OK */
return node->count + node->offset;
@@ -521,6 +703,9 @@ ExecInitLimit(Limit *node, EState *estate, int eflags)
&limitstate->ps);
}
+ if (IsPercentOption(node->limitOption))
+ limitstate->tupleStore = tuplestore_begin_heap(true, false, work_mem);
+
return limitstate;
}
@@ -536,6 +721,8 @@ ExecEndLimit(LimitState *node)
{
ExecFreeExprContext(&node->ps);
ExecEndNode(outerPlanState(node));
+ if (node->tupleStore != NULL)
+ tuplestore_end(node->tupleStore);
}
@@ -555,4 +742,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/optimizer/util/pathnode.c b/src/backend/optimizer/util/pathnode.c
index c1fc866cbf..c4dabb02de 100644
--- a/src/backend/optimizer/util/pathnode.c
+++ b/src/backend/optimizer/util/pathnode.c
@@ -3665,7 +3665,7 @@ 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);
+ limitOption, offset_est, count_est);
return pathnode;
}
@@ -3690,6 +3690,7 @@ void
adjust_limit_rows_costs(double *rows, /* in/out parameter */
Cost *startup_cost, /* in/out parameter */
Cost *total_cost, /* in/out parameter */
+ LimitOption limitOption,
int64 offset_est,
int64 count_est)
{
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index dbb47d4982..0168cb37f8 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -680,7 +680,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
@@ -11559,6 +11559,14 @@ limit_clause:
n->limitOption = LIMIT_OPTION_COUNT;
$$ = n;
}
+ | FETCH first_or_next select_fetch_first_value PERCENT row_or_rows ONLY
+ {
+ SelectLimit *n = (SelectLimit *) palloc(sizeof(SelectLimit));
+ n->limitOffset = NULL;
+ n->limitCount = $3;
+ n->limitOption = LIMIT_OPTION_PERCENT;
+ $$ = n;
+ }
| FETCH first_or_next select_fetch_first_value row_or_rows WITH TIES
{
SelectLimit *n = (SelectLimit *) palloc(sizeof(SelectLimit));
@@ -15202,6 +15210,7 @@ unreserved_keyword:
| PARTITION
| PASSING
| PASSWORD
+ | PERCENT
| PLANS
| POLICY
| PRECEDING
diff --git a/src/backend/parser/parse_clause.c b/src/backend/parser/parse_clause.c
index 6fff13479e..d3782697c8 100644
--- a/src/backend/parser/parse_clause.c
+++ b/src/backend/parser/parse_clause.c
@@ -1754,8 +1754,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 2cbcb4b85e..43e5647201 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -5243,7 +5243,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)
{
if (query->limitOption == LIMIT_OPTION_WITH_TIES)
{
diff --git a/src/backend/utils/sort/tuplestore.c b/src/backend/utils/sort/tuplestore.c
index 452a85a423..8743a0f03d 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 cf832d7f90..d9c441187a 100644
--- a/src/include/nodes/execnodes.h
+++ b/src/include/nodes/execnodes.h
@@ -2501,6 +2501,11 @@ typedef struct LimitState
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 381d84b4e4..9253ac734c 100644
--- a/src/include/nodes/nodes.h
+++ b/src/include/nodes/nodes.h
@@ -836,6 +836,8 @@ typedef enum LimitOption
{
LIMIT_OPTION_COUNT, /* FETCH FIRST... ONLY */
LIMIT_OPTION_WITH_TIES, /* FETCH FIRST... WITH TIES */
+ LIMIT_OPTION_PERCENT, /* FETCH FIRST... PERCENT ONLY */
+ LIMIT_OPTION_PER_WITH_TIES, /* FETCH FIRST... PERCENT WITH TIES */
LIMIT_OPTION_DEFAULT, /* No limit present */
} LimitOption;
diff --git a/src/include/optimizer/pathnode.h b/src/include/optimizer/pathnode.h
index 715a24ad29..ddc8f74138 100644
--- a/src/include/optimizer/pathnode.h
+++ b/src/include/optimizer/pathnode.h
@@ -272,7 +272,7 @@ extern LimitPath *create_limit_path(PlannerInfo *root, RelOptInfo *rel,
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);
+ LimitOption limitOption, int64 offset_est, int64 count_est);
extern Path *reparameterize_path(PlannerInfo *root, Path *path,
Relids required_outer,
diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h
index 08f22ce211..1605a6f8ea 100644
--- a/src/include/parser/kwlist.h
+++ b/src/include/parser/kwlist.h
@@ -306,6 +306,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/utils/tuplestore.h b/src/include/utils/tuplestore.h
index 2c6403f9e8..1d59aa0d57 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 e6f6809fbe..46557e2dae 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);
@@ -338,6 +395,46 @@ fetch backward all in c5;
123 | 456
(2 rows)
+declare c6 cursor for select * from int8_tbl fetch first 50 percent rows only;
+fetch all in c6;
+ q1 | q2
+------------------+------------------
+ 123 | 456
+ 123 | 4567890123456789
+ 4567890123456789 | 123
+(3 rows)
+
+fetch 1 in c6;
+ q1 | q2
+----+----
+(0 rows)
+
+fetch backward 1 in c6;
+ q1 | q2
+------------------+-----
+ 4567890123456789 | 123
+(1 row)
+
+fetch backward all in c6;
+ q1 | q2
+-----+------------------
+ 123 | 4567890123456789
+ 123 | 456
+(2 rows)
+
+fetch backward 1 in c6;
+ q1 | q2
+----+----
+(0 rows)
+
+fetch all in c6;
+ q1 | q2
+------------------+------------------
+ 123 | 456
+ 123 | 4567890123456789
+ 4567890123456789 | 123
+(3 rows)
+
rollback;
-- Stress test for variable LIMIT in conjunction with bounded-heap sorting
SELECT
diff --git a/src/test/regress/sql/limit.sql b/src/test/regress/sql/limit.sql
index d2d4ef132d..c6e660913f 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;
@@ -81,6 +97,14 @@ fetch backward all in c5;
fetch all in c5;
fetch backward all in c5;
+declare c6 cursor for select * from int8_tbl fetch first 50 percent rows only;
+fetch all in c6;
+fetch 1 in c6;
+fetch backward 1 in c6;
+fetch backward all in c6;
+fetch backward 1 in c6;
+fetch all in c6;
+
rollback;
-- Stress test for variable LIMIT in conjunction with bounded-heap sorting
diff --git a/src/backend/executor/nodeLimit.c b/src/backend/executor/nodeLimit.c
index cfb28a9fd4..bb117c77fd 100644
--- a/src/backend/executor/nodeLimit.c
+++ b/src/backend/executor/nodeLimit.c
@@ -193,6 +193,12 @@ ExecLimit(PlanState *pstate)
if (!(node->ps.state->es_top_eflags & EXEC_FLAG_BACKWARD))
(void) ExecShutdownNode(outerPlan);
+ /*
+ * The only operation from here is backward scan We have
+ * to move one postion forward to get previous tuple
+ */
+ tuplestore_advance(node->tupleStore, true);
+
return NULL;
}
@@ -215,26 +221,51 @@ ExecLimit(PlanState *pstate)
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
+ * If WITH TIES is active, get previous tuple,
+ * save it to be used in subsequent WINDOWEND_TIES
+ * processing.
*/
- tuplestore_advance(node->tupleStore, false);
- return NULL;
- }
+ if (node->limitOption == LIMIT_OPTION_PER_WITH_TIES)
+ {
+ slot = node->subSlot;
+ tuplestore_advance(node->tupleStore, false);
+ if (!tuplestore_gettupleslot_heaptuple(node->tupleStore, true, true, slot))
+ {
+ node->lstate = LIMIT_SUBPLANEOF;
+ tuplestore_advance(node->tupleStore, true);
+ return NULL;
+ }
+
+ ExecCopySlot(node->last_slot, slot);
+ node->lstate = LIMIT_WINDOWEND_TIES;
+ /* we'll fall through to the next case */
+ }
+ else
+ {
+ node->lstate = LIMIT_SUBPLANEOF;
- tuplestore_puttupleslot(node->tupleStore, slot);
+ /*
+ * 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 postion backward, and then we will
+ * scan forward for it for the first tuple and
+ * precede as usual for the rest
+ */
+ tuplestore_advance(node->tupleStore, true);
+ return NULL;
+ }
+ }
+ if (node->lstate != LIMIT_WINDOWEND_TIES)
+ {
+ tuplestore_puttupleslot(node->tupleStore, slot);
- cnt = tuplestore_tuple_count(node->tupleStore) + node->offset;
+ cnt = tuplestore_tuple_count(node->tupleStore) + node->offset;
- node->count = ceil(node->percent * cnt / 100.0);
- } while (node->position - node->offset >= node->count);
+ node->count = ceil(node->percent * cnt / 100.0);
+ }
+ } while (node->position - node->offset >= node->count && node->lstate != LIMIT_WINDOWEND_TIES);
}
/*
@@ -255,7 +286,7 @@ ExecLimit(PlanState *pstate)
*/
if (!node->noCount &&
node->position - node->offset >= node->count
- && !IsPercentOption(node->limitOption))
+ && !IsPercentOption(node->limitOption) && node->lstate != LIMIT_WINDOWEND_TIES)
{
if (node->limitOption == LIMIT_OPTION_COUNT)
{
@@ -268,49 +299,45 @@ ExecLimit(PlanState *pstate)
/* we'll fall through to the next case */
}
}
- else
+ else if (IsPercentOption(node->limitOption) && node->lstate != LIMIT_WINDOWEND_TIES)
{
- if (IsPercentOption(node->limitOption))
+ if (tuplestore_gettupleslot_heaptuple(node->tupleStore, true, true, slot))
{
- if (tuplestore_gettupleslot_heaptuple(node->tupleStore, true, true, slot))
- {
- node->subSlot = slot;
- node->position++;
- break;
- }
- else
- {
- node->lstate = LIMIT_SUBPLANEOF;
- return NULL;
- }
-
- }
- else
- {
- /*
- * Get next tuple from subplan, if any.
- */
- slot = ExecProcNode(outerPlan);
- if (TupIsNull(slot))
- {
- node->lstate = LIMIT_SUBPLANEOF;
- return NULL;
- }
-
- /*
- * If WITH TIES is active, and this is the last
- * in-window tuple, save it to be used in subsequent
- * WINDOWEND_TIES processing.
- */
- if (node->limitOption == LIMIT_OPTION_WITH_TIES &&
- node->position - node->offset == node->count - 1)
- {
- ExecCopySlot(node->last_slot, slot);
- }
node->subSlot = slot;
node->position++;
break;
}
+ else
+ {
+ node->lstate = LIMIT_SUBPLANEOF;
+ return NULL;
+ }
+ }
+ else if (!IsPercentOption(node->limitOption) && node->lstate != LIMIT_WINDOWEND_TIES)
+ {
+ /*
+ * Get next tuple from subplan, if any.
+ */
+ slot = ExecProcNode(outerPlan);
+ if (TupIsNull(slot))
+ {
+ node->lstate = LIMIT_SUBPLANEOF;
+ return NULL;
+ }
+
+ /*
+ * If WITH TIES is active, and this is the last in-window
+ * tuple, save it to be used in subsequent WINDOWEND_TIES
+ * processing.
+ */
+ if (node->limitOption == LIMIT_OPTION_WITH_TIES &&
+ node->position - node->offset == node->count - 1)
+ {
+ ExecCopySlot(node->last_slot, slot);
+ }
+ node->subSlot = slot;
+ node->position++;
+ break;
}
}
else
@@ -359,14 +386,25 @@ ExecLimit(PlanState *pstate)
if (ScanDirectionIsForward(direction))
{
/*
- * Advance the subplan until we find the first row with
- * different ORDER BY pathkeys.
+ * Advance the subplan or tuple store until we find the first
+ * row with different ORDER BY pathkeys.
*/
- slot = ExecProcNode(outerPlan);
- if (TupIsNull(slot))
+ if (node->limitOption == LIMIT_OPTION_PER_WITH_TIES)
{
- node->lstate = LIMIT_SUBPLANEOF;
- return NULL;
+ if (!tuplestore_gettupleslot_heaptuple(node->tupleStore, true, true, slot))
+ {
+ node->lstate = LIMIT_SUBPLANEOF;
+ return NULL;
+ }
+ }
+ else
+ {
+ slot = ExecProcNode(outerPlan);
+ if (TupIsNull(slot))
+ {
+ node->lstate = LIMIT_SUBPLANEOF;
+ return NULL;
+ }
}
/*
@@ -399,15 +437,30 @@ ExecLimit(PlanState *pstate)
}
/*
- * Get previous tuple from subplan; there should be one! And
- * change state-machine status.
+ * Get previous tuple from subplan or tuple store; there
+ * should be one! And change state-machine status.
*/
- slot = ExecProcNode(outerPlan);
- if (TupIsNull(slot))
- elog(ERROR, "LIMIT subplan failed to run backwards");
- node->subSlot = slot;
- node->position--;
- node->lstate = LIMIT_INWINDOW;
+ if (node->limitOption == LIMIT_OPTION_PER_WITH_TIES)
+ {
+ if (tuplestore_gettupleslot_heaptuple(node->tupleStore, false, true, slot))
+ {
+ node->backwardPosition++;
+ node->position--;
+ node->subSlot = slot;
+ node->lstate = LIMIT_INWINDOW;
+ }
+ else
+ elog(ERROR, "LIMIT subplan failed to run backwards");
+ }
+ else
+ {
+ slot = ExecProcNode(outerPlan);
+ if (TupIsNull(slot))
+ elog(ERROR, "LIMIT subplan failed to run backwards");
+ node->subSlot = slot;
+ node->position--;
+ node->lstate = LIMIT_INWINDOW;
+ }
}
break;
@@ -416,11 +469,12 @@ ExecLimit(PlanState *pstate)
return NULL;
/*
- * Scan forward for the first tuple
+ * Scan forward for the previous tuple. there should be one! Note
+ * previous tuple must be in window.
*/
if (IsPercentOption(node->limitOption))
{
- if (tuplestore_gettupleslot_heaptuple(node->tupleStore, true, true, slot))
+ if (tuplestore_gettupleslot_heaptuple(node->tupleStore, false, true, slot))
{
node->subSlot = slot;
node->lstate = LIMIT_INWINDOW;
@@ -461,6 +515,16 @@ ExecLimit(PlanState *pstate)
node->subSlot = slot;
node->lstate = LIMIT_INWINDOW;
}
+ if (node->limitOption == LIMIT_OPTION_PER_WITH_TIES)
+ {
+ if (tuplestore_gettupleslot_heaptuple(node->tupleStore, false, true, slot))
+ {
+ node->subSlot = slot;
+ node->lstate = LIMIT_INWINDOW;
+ }
+ else
+ elog(ERROR, "LIMIT subplan failed to run backwards");
+ }
else
{
/*
@@ -686,7 +750,8 @@ ExecInitLimit(Limit *node, EState *estate, int eflags)
/*
* Initialize the equality evaluation, to detect ties.
*/
- if (node->limitOption == LIMIT_OPTION_WITH_TIES)
+ if (node->limitOption == LIMIT_OPTION_WITH_TIES
+ || node->limitOption == LIMIT_OPTION_PER_WITH_TIES)
{
TupleDesc desc;
const TupleTableSlotOps *ops;
diff --git a/src/backend/optimizer/plan/createplan.c b/src/backend/optimizer/plan/createplan.c
index 99278eed93..c4a81dd2ad 100644
--- a/src/backend/optimizer/plan/createplan.c
+++ b/src/backend/optimizer/plan/createplan.c
@@ -2701,7 +2701,8 @@ create_limit_plan(PlannerInfo *root, LimitPath *best_path, int flags)
subplan = create_plan_recurse(root, best_path->subpath, flags);
/* Extract information necessary for comparing rows for WITH TIES. */
- if (best_path->limitOption == LIMIT_OPTION_WITH_TIES)
+ if (best_path->limitOption == LIMIT_OPTION_WITH_TIES ||
+ best_path->limitOption == LIMIT_OPTION_PER_WITH_TIES)
{
Query *parse = root->parse;
ListCell *l;
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 0168cb37f8..d18aad8722 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -11575,6 +11575,14 @@ limit_clause:
n->limitOption = LIMIT_OPTION_WITH_TIES;
$$ = n;
}
+ | FETCH first_or_next select_fetch_first_value PERCENT row_or_rows WITH TIES
+ {
+ SelectLimit *n = (SelectLimit *) palloc(sizeof(SelectLimit));
+ n->limitOffset = NULL;
+ n->limitCount = $3;
+ n->limitOption = LIMIT_OPTION_PER_WITH_TIES;
+ $$ = n;
+ }
| FETCH first_or_next row_or_rows ONLY
{
SelectLimit *n = (SelectLimit *) palloc(sizeof(SelectLimit));
@@ -15921,7 +15929,8 @@ insertSelectOptions(SelectStmt *stmt,
ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
errmsg("multiple limit options not allowed")));
- if (!stmt->sortClause && limitClause->limitOption == LIMIT_OPTION_WITH_TIES)
+ if (!stmt->sortClause && (limitClause->limitOption == LIMIT_OPTION_WITH_TIES
+ || limitClause->limitOption == LIMIT_OPTION_PER_WITH_TIES))
ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
errmsg("WITH TIES options can not be specified without ORDER BY clause")));
diff --git a/src/backend/parser/parse_clause.c b/src/backend/parser/parse_clause.c
index d3782697c8..2837771d1d 100644
--- a/src/backend/parser/parse_clause.c
+++ b/src/backend/parser/parse_clause.c
@@ -1754,7 +1754,9 @@ transformLimitClause(ParseState *pstate, Node *clause,
return NULL;
qual = transformExpr(pstate, clause, exprKind);
- if (limitOption == LIMIT_OPTION_PERCENT && strcmp(constructName, "LIMIT") == 0)
+ if ((limitOption == LIMIT_OPTION_PERCENT || limitOption == LIMIT_OPTION_PER_WITH_TIES)
+ && strcmp(constructName, "LIMIT") == 0)
+
qual = coerce_to_specific_type(pstate, qual, FLOAT8OID, constructName);
else
qual = coerce_to_specific_type(pstate, qual, INT8OID, constructName);
diff --git a/src/test/regress/expected/limit.out b/src/test/regress/expected/limit.out
index 46557e2dae..7352632b6c 100644
--- a/src/test/regress/expected/limit.out
+++ b/src/test/regress/expected/limit.out
@@ -435,6 +435,43 @@ fetch all in c6;
4567890123456789 | 123
(3 rows)
+declare c7 cursor for select * from int8_tbl order by q1 fetch first 15 percent rows with ties;
+fetch all in c7;
+ q1 | q2
+-----+------------------
+ 123 | 456
+ 123 | 4567890123456789
+(2 rows)
+
+fetch 1 in c7;
+ q1 | q2
+----+----
+(0 rows)
+
+fetch backward 1 in c7;
+ q1 | q2
+-----+------------------
+ 123 | 4567890123456789
+(1 row)
+
+fetch backward all in c7;
+ q1 | q2
+-----+-----
+ 123 | 456
+(1 row)
+
+fetch backward 1 in c7;
+ q1 | q2
+----+----
+(0 rows)
+
+fetch all in c7;
+ q1 | q2
+-----+------------------
+ 123 | 456
+ 123 | 4567890123456789
+(2 rows)
+
rollback;
-- Stress test for variable LIMIT in conjunction with bounded-heap sorting
SELECT
@@ -716,6 +753,80 @@ SELECT thousand
0
(2 rows)
+--
+-- FETCH FIRST
+-- Check the PERCENT WITH TIES clause
+--
+SELECT thousand
+ FROM onek WHERE thousand < 5
+ ORDER BY thousand FETCH FIRST 2 PERCENT ROW ONLY;
+ thousand
+----------
+ 0
+(1 row)
+
+SELECT thousand
+ FROM onek WHERE thousand < 5
+ ORDER BY thousand FETCH FIRST 2 PERCENT ROWS WITH TIES;
+ thousand
+----------
+ 0
+ 0
+ 0
+ 0
+ 0
+ 0
+ 0
+ 0
+ 0
+ 0
+(10 rows)
+
+SELECT thousand
+ FROM onek WHERE thousand < 5
+ ORDER BY thousand FETCH FIRST 21 PERCENT ROW ONLY;
+ thousand
+----------
+ 0
+ 0
+ 0
+ 0
+ 0
+ 0
+ 0
+ 0
+ 0
+ 0
+ 1
+(11 rows)
+
+SELECT thousand
+ FROM onek WHERE thousand < 5
+ ORDER BY thousand FETCH FIRST 21 PERCENT ROW WITH TIES;
+ thousand
+----------
+ 0
+ 0
+ 0
+ 0
+ 0
+ 0
+ 0
+ 0
+ 0
+ 0
+ 1
+ 1
+ 1
+ 1
+ 1
+ 1
+ 1
+ 1
+ 1
+ 1
+(20 rows)
+
-- should fail
SELECT ''::text AS two, unique1, unique2, stringu1
FROM onek WHERE unique1 > 50
diff --git a/src/test/regress/sql/limit.sql b/src/test/regress/sql/limit.sql
index c6e660913f..f9e3d24910 100644
--- a/src/test/regress/sql/limit.sql
+++ b/src/test/regress/sql/limit.sql
@@ -105,6 +105,14 @@ fetch backward all in c6;
fetch backward 1 in c6;
fetch all in c6;
+declare c7 cursor for select * from int8_tbl order by q1 fetch first 15 percent rows with ties;
+fetch all in c7;
+fetch 1 in c7;
+fetch backward 1 in c7;
+fetch backward all in c7;
+fetch backward 1 in c7;
+fetch all in c7;
+
rollback;
-- Stress test for variable LIMIT in conjunction with bounded-heap sorting
@@ -197,6 +205,27 @@ SELECT thousand
FROM onek WHERE thousand < 5
ORDER BY thousand FETCH FIRST 2 ROW ONLY;
+--
+-- FETCH FIRST
+-- Check the PERCENT WITH TIES clause
+--
+
+SELECT thousand
+ FROM onek WHERE thousand < 5
+ ORDER BY thousand FETCH FIRST 2 PERCENT ROW ONLY;
+
+SELECT thousand
+ FROM onek WHERE thousand < 5
+ ORDER BY thousand FETCH FIRST 2 PERCENT ROWS WITH TIES;
+
+SELECT thousand
+ FROM onek WHERE thousand < 5
+ ORDER BY thousand FETCH FIRST 21 PERCENT ROW ONLY;
+
+SELECT thousand
+ FROM onek WHERE thousand < 5
+ ORDER BY thousand FETCH FIRST 21 PERCENT ROW WITH TIES;
+
-- should fail
SELECT ''::text AS two, unique1, unique2, stringu1
FROM onek WHERE unique1 > 50
