aboutsummaryrefslogtreecommitdiff
path: root/src
diff options
context:
space:
mode:
Diffstat (limited to 'src')
-rw-r--r--src/backend/executor/nodeWindowAgg.c436
-rw-r--r--src/backend/nodes/copyfuncs.c6
-rw-r--r--src/backend/nodes/equalfuncs.c4
-rw-r--r--src/backend/nodes/outfuncs.c7
-rw-r--r--src/backend/nodes/readfuncs.c3
-rw-r--r--src/backend/optimizer/plan/createplan.c9
-rw-r--r--src/backend/optimizer/plan/planner.c5
-rw-r--r--src/backend/parser/gram.y113
-rw-r--r--src/backend/parser/keywords.c6
-rw-r--r--src/backend/parser/parse_agg.c23
-rw-r--r--src/backend/parser/parse_clause.c5
-rw-r--r--src/backend/utils/adt/ruleutils.c37
-rw-r--r--src/include/catalog/catversion.h4
-rw-r--r--src/include/nodes/execnodes.h6
-rw-r--r--src/include/nodes/parsenodes.h38
-rw-r--r--src/include/nodes/plannodes.h4
-rw-r--r--src/include/optimizer/planmain.h6
-rw-r--r--src/test/regress/expected/window.out160
-rw-r--r--src/test/regress/sql/window.sql32
19 files changed, 643 insertions, 261 deletions
diff --git a/src/backend/executor/nodeWindowAgg.c b/src/backend/executor/nodeWindowAgg.c
index 37ef9a5e830..181e66a8964 100644
--- a/src/backend/executor/nodeWindowAgg.c
+++ b/src/backend/executor/nodeWindowAgg.c
@@ -27,7 +27,7 @@
* Portions Copyright (c) 1994, Regents of the University of California
*
* IDENTIFICATION
- * $PostgreSQL: pgsql/src/backend/executor/nodeWindowAgg.c,v 1.1 2008/12/28 18:53:55 tgl Exp $
+ * $PostgreSQL: pgsql/src/backend/executor/nodeWindowAgg.c,v 1.2 2008/12/31 00:08:35 tgl Exp $
*
*-------------------------------------------------------------------------
*/
@@ -118,11 +118,10 @@ typedef struct WindowStatePerAggData
bool initValueIsNull;
/*
- * cached value for non-moving frame
+ * cached value for current frame boundaries
*/
Datum resultValue;
bool resultValueIsNull;
- bool hasResult;
/*
* We need the len and byval info for the agg's input, result, and
@@ -164,6 +163,10 @@ static void begin_partition(WindowAggState *winstate);
static void spool_tuples(WindowAggState *winstate, int64 pos);
static void release_partition(WindowAggState *winstate);
+static bool row_is_in_frame(WindowAggState *winstate, int64 pos,
+ TupleTableSlot *slot);
+static void update_frametailpos(WindowObject winobj, TupleTableSlot *slot);
+
static WindowStatePerAggData *initialize_peragg(WindowAggState *winstate,
WindowFunc *wfunc,
WindowStatePerAgg peraggstate);
@@ -198,6 +201,7 @@ initialize_windowaggregate(WindowAggState *winstate,
}
peraggstate->transValueIsNull = peraggstate->initValueIsNull;
peraggstate->noTransValue = peraggstate->initValueIsNull;
+ peraggstate->resultValueIsNull = true;
}
/*
@@ -385,9 +389,7 @@ eval_windowaggregates(WindowAggState *winstate)
int i;
MemoryContext oldContext;
ExprContext *econtext;
- TupleTableSlot *first_peer_slot = winstate->first_peer_slot;
- TupleTableSlot *slot;
- bool first;
+ TupleTableSlot *agg_row_slot;
numaggs = winstate->numaggs;
if (numaggs == 0)
@@ -397,35 +399,45 @@ eval_windowaggregates(WindowAggState *winstate)
econtext = winstate->ss.ps.ps_ExprContext;
/*
- * We don't currently support explicitly-specified window frames. That
- * means that the window frame always includes all the rows in the
- * partition preceding and including the current row, and all its
- * peers. As a special case, if there's no ORDER BY, all rows are peers,
- * so the window frame includes all rows in the partition.
+ * Currently, we support only a subset of the SQL-standard window framing
+ * rules. In all the supported cases, the window frame always consists
+ * of a contiguous group of rows extending forward from the start of the
+ * partition, and rows only enter the frame, never exit it, as the
+ * current row advances forward. This makes it possible to use an
+ * incremental strategy for evaluating aggregates: we run the transition
+ * function for each row added to the frame, and run the final function
+ * whenever we need the current aggregate value. This is considerably
+ * more efficient than the naive approach of re-running the entire
+ * aggregate calculation for each current row. It does assume that the
+ * final function doesn't damage the running transition value. (Some
+ * C-coded aggregates do that for efficiency's sake --- but they are
+ * supposed to do so only when their fcinfo->context is an AggState, not
+ * a WindowAggState.)
*
- * When there's peer rows, all rows in a peer group will have the same
- * aggregate values. The values will be calculated when current position
- * reaches the first peer row, and on all the following peer rows we will
- * just return the saved results.
+ * In many common cases, multiple rows share the same frame and hence
+ * the same aggregate value. (In particular, if there's no ORDER BY in
+ * a RANGE window, then all rows are peers and so they all have window
+ * frame equal to the whole partition.) We optimize such cases by
+ * calculating the aggregate value once when we reach the first row of a
+ * peer group, and then returning the saved value for all subsequent rows.
*
- * 'aggregatedupto' keeps track of the last row that has already been
- * accumulated for the aggregates. When the current row has no peers,
- * aggregatedupto will be the same as the current row after this
- * function. If there are peer rows, all peers will be accumulated in one
- * call of this function, and aggregatedupto will be ahead of the current
- * position. If there's no ORDER BY, and thus all rows are peers, the
- * first call will aggregate all rows in the partition.
+ * 'aggregatedupto' keeps track of the first row that has not yet been
+ * accumulated into the aggregate transition values. Whenever we start a
+ * new peer group, we accumulate forward to the end of the peer group.
*
- * TODO: In the future, we could implement sliding frames by recalculating
- * the aggregate whenever a row exits the frame. That would be pretty
- * slow, though. For aggregates like SUM and COUNT we could implement a
- * "negative transition function" that would be called for all the rows
- * that exit the frame.
+ * TODO: In the future, we should implement the full SQL-standard set
+ * of framing rules. We could implement the other cases by recalculating
+ * the aggregates whenever a row exits the frame. That would be pretty
+ * slow, though. For aggregates like SUM and COUNT we could implement a
+ * "negative transition function" that would be called for each row as it
+ * exits the frame. We'd have to think about avoiding recalculation of
+ * volatile arguments of aggregate functions, too.
*/
/*
* If we've already aggregated up through current row, reuse the
- * saved result values
+ * saved result values. NOTE: this test works for the currently
+ * supported framing rules, but will need fixing when more are added.
*/
if (winstate->aggregatedupto > winstate->currentpos)
{
@@ -440,76 +452,61 @@ eval_windowaggregates(WindowAggState *winstate)
}
/* Initialize aggregates on first call for partition */
- for (i = 0; i < numaggs; i++)
+ if (winstate->currentpos == 0)
{
- peraggstate = &winstate->peragg[i];
- wfuncno = peraggstate->wfuncno;
- if (!peraggstate->hasResult)
+ for (i = 0; i < numaggs; i++)
+ {
+ peraggstate = &winstate->peragg[i];
+ wfuncno = peraggstate->wfuncno;
initialize_windowaggregate(winstate,
&winstate->perfunc[wfuncno],
- &winstate->peragg[i]);
- }
-
- /*
- * If this is the first call for this partition, fetch the first row
- * for comparing peer rows. On subsequent calls, we'll always read
- * ahead until we reach the first non-peer row, and store that row in
- * first_peer_slot, for use in the next call.
- */
- if (TupIsNull(first_peer_slot))
- {
- spool_tuples(winstate, winstate->aggregatedupto);
- tuplestore_select_read_pointer(winstate->buffer, winstate->agg_ptr);
- if (!tuplestore_gettupleslot(winstate->buffer, true, first_peer_slot))
- elog(ERROR, "unexpected end of tuplestore");
+ peraggstate);
+ }
}
/*
- * Advance until we reach the next non-peer row
+ * Advance until we reach a row not in frame (or end of partition).
+ *
+ * Note the loop invariant: agg_row_slot is either empty or holds the
+ * row at position aggregatedupto. The agg_ptr read pointer must always
+ * point to the next row to read into agg_row_slot.
*/
- first = true;
+ agg_row_slot = winstate->agg_row_slot;
for (;;)
{
- if (!first)
+ /* Fetch next row if we didn't already */
+ if (TupIsNull(agg_row_slot))
{
- /* Fetch the next row, and see if it's a peer */
spool_tuples(winstate, winstate->aggregatedupto);
tuplestore_select_read_pointer(winstate->buffer,
winstate->agg_ptr);
- slot = winstate->temp_slot_1;
- if (!tuplestore_gettupleslot(winstate->buffer, true, slot))
- break;
- if (!are_peers(winstate, first_peer_slot, slot))
- {
- ExecCopySlot(first_peer_slot, slot);
- break;
- }
- }
- else
- {
- /*
- * On first iteration, just accumulate the tuple saved from
- * last call
- */
- slot = first_peer_slot;
- first = false;
+ if (!tuplestore_gettupleslot(winstate->buffer, true, agg_row_slot))
+ break; /* must be end of partition */
}
- /* set tuple context for evaluation of aggregate arguments */
- winstate->tmpcontext->ecxt_outertuple = slot;
+ /* Exit loop (for now) if not in frame */
+ if (!row_is_in_frame(winstate, winstate->aggregatedupto, agg_row_slot))
+ break;
+ /* Set tuple context for evaluation of aggregate arguments */
+ winstate->tmpcontext->ecxt_outertuple = agg_row_slot;
+
+ /* Accumulate row into the aggregates */
for (i = 0; i < numaggs; i++)
{
- wfuncno = winstate->peragg[i].wfuncno;
-
+ peraggstate = &winstate->peragg[i];
+ wfuncno = peraggstate->wfuncno;
advance_windowaggregate(winstate,
&winstate->perfunc[wfuncno],
- &winstate->peragg[i]);
-
+ peraggstate);
}
+
/* Reset per-input-tuple context after each tuple */
ResetExprContext(winstate->tmpcontext);
+
+ /* And advance the aggregated-row state */
winstate->aggregatedupto++;
+ ExecClearTuple(agg_row_slot);
}
/*
@@ -526,37 +523,44 @@ eval_windowaggregates(WindowAggState *winstate)
isnull = &econtext->ecxt_aggnulls[wfuncno];
finalize_windowaggregate(winstate,
&winstate->perfunc[wfuncno],
- peraggstate, result, isnull);
+ peraggstate,
+ result, isnull);
/*
- * save the result for the next (non-shrinking frame) call.
+ * save the result in case next row shares the same frame.
+ *
+ * XXX in some framing modes, eg ROWS/END_CURRENT_ROW, we can know
+ * in advance that the next row can't possibly share the same frame.
+ * Is it worth detecting that and skipping this code?
*/
- if (!peraggstate->resulttypeByVal && !*isnull)
+ if (!peraggstate->resulttypeByVal)
{
/*
- * clear old resultValue in order not to leak memory.
+ * clear old resultValue in order not to leak memory. (Note:
+ * the new result can't possibly be the same datum as old
+ * resultValue, because we never passed it to the trans function.)
*/
- if (peraggstate->hasResult &&
- (DatumGetPointer(peraggstate->resultValue) !=
- DatumGetPointer(*result)) &&
- !peraggstate->resultValueIsNull)
+ if (!peraggstate->resultValueIsNull)
pfree(DatumGetPointer(peraggstate->resultValue));
/*
* If pass-by-ref, copy it into our global context.
*/
- oldContext = MemoryContextSwitchTo(winstate->wincontext);
- peraggstate->resultValue = datumCopy(*result,
- peraggstate->resulttypeByVal,
- peraggstate->resulttypeLen);
- MemoryContextSwitchTo(oldContext);
+ if (!*isnull)
+ {
+ oldContext = MemoryContextSwitchTo(winstate->wincontext);
+ peraggstate->resultValue =
+ datumCopy(*result,
+ peraggstate->resulttypeByVal,
+ peraggstate->resulttypeLen);
+ MemoryContextSwitchTo(oldContext);
+ }
}
else
{
peraggstate->resultValue = *result;
}
peraggstate->resultValueIsNull = *isnull;
- peraggstate->hasResult = true;
}
}
@@ -620,14 +624,16 @@ begin_partition(WindowAggState *winstate)
int i;
winstate->partition_spooled = false;
+ winstate->frametail_valid = false;
winstate->spooled_rows = 0;
winstate->currentpos = 0;
winstate->frametailpos = -1;
winstate->aggregatedupto = 0;
+ ExecClearTuple(winstate->agg_row_slot);
/*
* If this is the very first partition, we need to fetch the first
- * input row to store in it.
+ * input row to store in first_part_slot.
*/
if (TupIsNull(winstate->first_part_slot))
{
@@ -775,35 +781,138 @@ release_partition(WindowAggState *winstate)
/* Release any partition-local state of this window function */
if (perfuncstate->winobj)
perfuncstate->winobj->localmem = NULL;
-
- /* Reset agg result cache */
- if (perfuncstate->plain_agg)
- {
- int aggno = perfuncstate->aggno;
- WindowStatePerAggData *peraggstate = &winstate->peragg[aggno];
-
- peraggstate->resultValueIsNull = true;
- peraggstate->hasResult = false;
- }
}
/*
* Release all partition-local memory (in particular, any partition-local
- * state or aggregate temp data that we might have trashed our pointers
- * to in the above loop). We don't rely on retail pfree because some
+ * state that we might have trashed our pointers to in the above loop, and
+ * any aggregate temp data). We don't rely on retail pfree because some
* aggregates might have allocated data we don't have direct pointers to.
*/
MemoryContextResetAndDeleteChildren(winstate->wincontext);
- /* Ensure eval_windowaggregates will see next call as partition start */
- ExecClearTuple(winstate->first_peer_slot);
-
if (winstate->buffer)
tuplestore_end(winstate->buffer);
winstate->buffer = NULL;
winstate->partition_spooled = false;
}
+/*
+ * row_is_in_frame
+ * Determine whether a row is in the current row's window frame according
+ * to our window framing rule
+ *
+ * The caller must have already determined that the row is in the partition
+ * and fetched it into a slot. This function just encapsulates the framing
+ * rules.
+ */
+static bool
+row_is_in_frame(WindowAggState *winstate, int64 pos, TupleTableSlot *slot)
+{
+ WindowAgg *node = (WindowAgg *) winstate->ss.ps.plan;
+ int frameOptions = node->frameOptions;
+
+ Assert(pos >= 0); /* else caller error */
+
+ /* We only support frame start mode UNBOUNDED PRECEDING for now */
+ Assert(frameOptions & FRAMEOPTION_START_UNBOUNDED_PRECEDING);
+
+ /* In UNBOUNDED FOLLOWING mode, all partition rows are in frame */
+ if (frameOptions & FRAMEOPTION_END_UNBOUNDED_FOLLOWING)
+ return true;
+
+ /* Else frame tail mode must be CURRENT ROW */
+ Assert(frameOptions & FRAMEOPTION_END_CURRENT_ROW);
+
+ /* if row is current row or a predecessor, it must be in frame */
+ if (pos <= winstate->currentpos)
+ return true;
+
+ /* In ROWS mode, *only* such rows are in frame */
+ if (frameOptions & FRAMEOPTION_ROWS)
+ return false;
+
+ /* Else must be RANGE mode */
+ Assert(frameOptions & FRAMEOPTION_RANGE);
+
+ /* In frame iff it's a peer of current row */
+ return are_peers(winstate, slot, winstate->ss.ss_ScanTupleSlot);
+}
+
+/*
+ * update_frametailpos
+ * make frametailpos valid for the current row
+ *
+ * Uses the winobj's read pointer for any required fetches; the winobj's
+ * mark must not be past the currently known frame tail. Also uses the
+ * specified slot for any required fetches.
+ */
+static void
+update_frametailpos(WindowObject winobj, TupleTableSlot *slot)
+{
+ WindowAggState *winstate = winobj->winstate;
+ WindowAgg *node = (WindowAgg *) winstate->ss.ps.plan;
+ int frameOptions = node->frameOptions;
+ int64 ftnext;
+
+ if (winstate->frametail_valid)
+ return; /* already known for current row */
+
+ /* We only support frame start mode UNBOUNDED PRECEDING for now */
+ Assert(frameOptions & FRAMEOPTION_START_UNBOUNDED_PRECEDING);
+
+ /* In UNBOUNDED FOLLOWING mode, all partition rows are in frame */
+ if (frameOptions & FRAMEOPTION_END_UNBOUNDED_FOLLOWING)
+ {
+ spool_tuples(winstate, -1);
+ winstate->frametailpos = winstate->spooled_rows - 1;
+ winstate->frametail_valid = true;
+ return;
+ }
+
+ /* Else frame tail mode must be CURRENT ROW */
+ Assert(frameOptions & FRAMEOPTION_END_CURRENT_ROW);
+
+ /* In ROWS mode, exactly the rows up to current are in frame */
+ if (frameOptions & FRAMEOPTION_ROWS)
+ {
+ winstate->frametailpos = winstate->currentpos;
+ winstate->frametail_valid = true;
+ return;
+ }
+
+ /* Else must be RANGE mode */
+ Assert(frameOptions & FRAMEOPTION_RANGE);
+
+ /* If no ORDER BY, all rows are peers with each other */
+ if (node->ordNumCols == 0)
+ {
+ spool_tuples(winstate, -1);
+ winstate->frametailpos = winstate->spooled_rows - 1;
+ winstate->frametail_valid = true;
+ return;
+ }
+
+ /*
+ * Else we have to search for the first non-peer of the current row.
+ * We assume the current value of frametailpos is a lower bound on the
+ * possible frame tail location, ie, frame tail never goes backward, and
+ * that currentpos is also a lower bound, ie, current row is always in
+ * frame.
+ */
+ ftnext = Max(winstate->frametailpos, winstate->currentpos) + 1;
+ for (;;)
+ {
+ if (!window_gettupleslot(winobj, ftnext, slot))
+ break; /* end of partition */
+ if (!are_peers(winstate, slot, winstate->ss.ss_ScanTupleSlot))
+ break; /* not peer of current row */
+ ftnext++;
+ }
+ winstate->frametailpos = ftnext - 1;
+ winstate->frametail_valid = true;
+}
+
/* -----------------
* ExecWindowAgg
@@ -849,11 +958,14 @@ restart:
{
/* Initialize for first partition and set current row = 0 */
begin_partition(winstate);
+ /* If there are no input rows, we'll detect that and exit below */
}
else
{
/* Advance current row within partition */
winstate->currentpos++;
+ /* This might mean that the frame tail moves, too */
+ winstate->frametail_valid = false;
}
/*
@@ -887,8 +999,7 @@ restart:
ResetExprContext(econtext);
/*
- * Read the current row from the tuplestore, and save in ScanTupleSlot
- * for possible use by WinGetFuncArgCurrent or the final projection step.
+ * Read the current row from the tuplestore, and save in ScanTupleSlot.
* (We can't rely on the outerplan's output slot because we may have to
* read beyond the current row.)
*
@@ -1002,7 +1113,7 @@ ExecInitWindowAgg(WindowAgg *node, EState *estate, int eflags)
ExecInitScanTupleSlot(estate, &winstate->ss);
ExecInitResultTupleSlot(estate, &winstate->ss.ps);
winstate->first_part_slot = ExecInitExtraTupleSlot(estate);
- winstate->first_peer_slot = ExecInitExtraTupleSlot(estate);
+ winstate->agg_row_slot = ExecInitExtraTupleSlot(estate);
winstate->temp_slot_1 = ExecInitExtraTupleSlot(estate);
winstate->temp_slot_2 = ExecInitExtraTupleSlot(estate);
@@ -1031,7 +1142,7 @@ ExecInitWindowAgg(WindowAgg *node, EState *estate, int eflags)
ExecSetSlotDescriptor(winstate->first_part_slot,
winstate->ss.ss_ScanTupleSlot->tts_tupleDescriptor);
- ExecSetSlotDescriptor(winstate->first_peer_slot,
+ ExecSetSlotDescriptor(winstate->agg_row_slot,
winstate->ss.ss_ScanTupleSlot->tts_tupleDescriptor);
ExecSetSlotDescriptor(winstate->temp_slot_1,
winstate->ss.ss_ScanTupleSlot->tts_tupleDescriptor);
@@ -1081,6 +1192,10 @@ ExecInitWindowAgg(WindowAgg *node, EState *estate, int eflags)
AclResult aclresult;
int i;
+ if (wfunc->winref != node->winref) /* planner screwed up? */
+ elog(ERROR, "WindowFunc with winref %u assigned to WindowAgg with winref %u",
+ wfunc->winref, node->winref);
+
/* Look for a previous duplicate window function */
for (i = 0; i <= wfuncno; i++)
{
@@ -1183,7 +1298,7 @@ ExecEndWindowAgg(WindowAggState *node)
ExecClearTuple(node->ss.ss_ScanTupleSlot);
ExecClearTuple(node->first_part_slot);
- ExecClearTuple(node->first_peer_slot);
+ ExecClearTuple(node->agg_row_slot);
ExecClearTuple(node->temp_slot_1);
ExecClearTuple(node->temp_slot_2);
@@ -1219,7 +1334,7 @@ ExecReScanWindowAgg(WindowAggState *node, ExprContext *exprCtxt)
/* release all temp tuples, but especially first_part_slot */
ExecClearTuple(node->ss.ss_ScanTupleSlot);
ExecClearTuple(node->first_part_slot);
- ExecClearTuple(node->first_peer_slot);
+ ExecClearTuple(node->agg_row_slot);
ExecClearTuple(node->temp_slot_1);
ExecClearTuple(node->temp_slot_2);
@@ -1411,6 +1526,8 @@ GetAggInitVal(Datum textInitVal, Oid transtype)
/*
* are_peers
* compare two rows to see if they are equal according to the ORDER BY clause
+ *
+ * NB: this does not consider the window frame mode.
*/
static bool
are_peers(WindowAggState *winstate, TupleTableSlot *slot1,
@@ -1430,7 +1547,8 @@ are_peers(WindowAggState *winstate, TupleTableSlot *slot1,
/*
* window_gettupleslot
- * Fetch the pos'th tuple of the current partition into the slot
+ * Fetch the pos'th tuple of the current partition into the slot,
+ * using the winobj's read pointer
*
* Returns true if successful, false if no such row
*/
@@ -1582,6 +1700,8 @@ WinSetMarkPosition(WindowObject winobj, int64 markpos)
* WinRowsArePeers
* Compare two rows (specified by absolute position in window) to see
* if they are equal according to the ORDER BY clause.
+ *
+ * NB: this does not consider the window frame mode.
*/
bool
WinRowsArePeers(WindowObject winobj, int64 pos1, int64 pos2)
@@ -1593,7 +1713,6 @@ WinRowsArePeers(WindowObject winobj, int64 pos1, int64 pos2)
bool res;
Assert(WindowObjectIsValid(winobj));
-
winstate = winobj->winstate;
node = (WindowAgg *) winstate->ss.ps.plan;
@@ -1642,27 +1761,28 @@ WinGetFuncArgInPartition(WindowObject winobj, int argno,
int relpos, int seektype, bool set_mark,
bool *isnull, bool *isout)
{
+ WindowAggState *winstate;
ExprContext *econtext;
TupleTableSlot *slot;
bool gottuple;
int64 abs_pos;
Assert(WindowObjectIsValid(winobj));
-
- econtext = winobj->winstate->ss.ps.ps_ExprContext;
- slot = winobj->winstate->temp_slot_1;
+ winstate = winobj->winstate;
+ econtext = winstate->ss.ps.ps_ExprContext;
+ slot = winstate->temp_slot_1;
switch (seektype)
{
case WINDOW_SEEK_CURRENT:
- abs_pos = winobj->winstate->currentpos + relpos;
+ abs_pos = winstate->currentpos + relpos;
break;
case WINDOW_SEEK_HEAD:
abs_pos = relpos;
break;
case WINDOW_SEEK_TAIL:
- spool_tuples(winobj->winstate, -1);
- abs_pos = winobj->winstate->spooled_rows - 1 + relpos;
+ spool_tuples(winstate, -1);
+ abs_pos = winstate->spooled_rows - 1 + relpos;
break;
default:
elog(ERROR, "unrecognized window seek type: %d", seektype);
@@ -1670,10 +1790,7 @@ WinGetFuncArgInPartition(WindowObject winobj, int argno,
break;
}
- if (abs_pos >= 0)
- gottuple = window_gettupleslot(winobj, abs_pos, slot);
- else
- gottuple = false;
+ gottuple = window_gettupleslot(winobj, abs_pos, slot);
if (!gottuple)
{
@@ -1717,34 +1834,28 @@ WinGetFuncArgInFrame(WindowObject winobj, int argno,
int relpos, int seektype, bool set_mark,
bool *isnull, bool *isout)
{
+ WindowAggState *winstate;
ExprContext *econtext;
TupleTableSlot *slot;
bool gottuple;
int64 abs_pos;
- int64 frametailpos;
Assert(WindowObjectIsValid(winobj));
-
- /* if no ordering columns, partition and frame are the same thing */
- if (((WindowAgg *) winobj->winstate->ss.ps.plan)->ordNumCols == 0)
- return WinGetFuncArgInPartition(winobj, argno, relpos, seektype,
- set_mark, isnull, isout);
-
- econtext = winobj->winstate->ss.ps.ps_ExprContext;
- slot = winobj->winstate->temp_slot_1;
- frametailpos = winobj->winstate->frametailpos;
+ winstate = winobj->winstate;
+ econtext = winstate->ss.ps.ps_ExprContext;
+ slot = winstate->temp_slot_1;
switch (seektype)
{
case WINDOW_SEEK_CURRENT:
- abs_pos = winobj->winstate->currentpos + relpos;
+ abs_pos = winstate->currentpos + relpos;
break;
case WINDOW_SEEK_HEAD:
abs_pos = relpos;
break;
case WINDOW_SEEK_TAIL:
- /* abs_pos is calculated later */
- abs_pos = 0; /* keep compiler quiet */
+ update_frametailpos(winobj, slot);
+ abs_pos = winstate->frametailpos + relpos;
break;
default:
elog(ERROR, "unrecognized window seek type: %d", seektype);
@@ -1752,58 +1863,9 @@ WinGetFuncArgInFrame(WindowObject winobj, int argno,
break;
}
- /*
- * Seek for frame tail. If the tail position is before current,
- * always check if the tail is after the current or not.
- */
- if (frametailpos <= winobj->winstate->currentpos)
- {
- int64 add = 1;
-
- for (;;)
- {
- spool_tuples(winobj->winstate, winobj->winstate->currentpos + add);
- if (winobj->winstate->spooled_rows > winobj->winstate->currentpos + add)
- {
- /*
- * When seektype is not TAIL, we may optimize not to
- * spool unnecessary tuples. In TAIL mode, we need to search
- * until we find a row that's definitely not a peer.
- */
- if (!WinRowsArePeers(winobj, winobj->winstate->currentpos,
- winobj->winstate->currentpos + add) ||
- (seektype != WINDOW_SEEK_TAIL &&
- winobj->winstate->currentpos + add < abs_pos))
- break;
- add++;
- }
- else
- {
- /*
- * If hit the partition end, the last row is the frame tail.
- */
- break;
- }
- }
- frametailpos = winobj->winstate->currentpos + add - 1;
- winobj->winstate->frametailpos = frametailpos;
- }
-
- if (seektype == WINDOW_SEEK_TAIL)
- {
- abs_pos = frametailpos + relpos;
- }
-
- /*
- * If there is an ORDER BY (we don't support other window frame
- * specifications yet), the frame runs from first row of the partition
- * to the last peer of the current row. Otherwise the frame is the
- * whole partition.
- */
- if (abs_pos < 0 || abs_pos > frametailpos)
- gottuple = false;
- else
- gottuple = window_gettupleslot(winobj, abs_pos, slot);
+ gottuple = window_gettupleslot(winobj, abs_pos, slot);
+ if (gottuple)
+ gottuple = row_is_in_frame(winstate, abs_pos, slot);
if (!gottuple)
{
diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c
index 412fd96e5bf..8f2f7696bc1 100644
--- a/src/backend/nodes/copyfuncs.c
+++ b/src/backend/nodes/copyfuncs.c
@@ -15,7 +15,7 @@
* Portions Copyright (c) 1994, Regents of the University of California
*
* IDENTIFICATION
- * $PostgreSQL: pgsql/src/backend/nodes/copyfuncs.c,v 1.417 2008/12/28 18:53:55 tgl Exp $
+ * $PostgreSQL: pgsql/src/backend/nodes/copyfuncs.c,v 1.418 2008/12/31 00:08:35 tgl Exp $
*
*-------------------------------------------------------------------------
*/
@@ -678,6 +678,7 @@ _copyWindowAgg(WindowAgg *from)
CopyPlanFields((Plan *) from, (Plan *) newnode);
+ COPY_SCALAR_FIELD(winref);
COPY_SCALAR_FIELD(partNumCols);
if (from->partNumCols > 0)
{
@@ -690,6 +691,7 @@ _copyWindowAgg(WindowAgg *from)
COPY_POINTER_FIELD(ordColIdx, from->ordNumCols * sizeof(AttrNumber));
COPY_POINTER_FIELD(ordOperators, from->ordNumCols * sizeof(Oid));
}
+ COPY_SCALAR_FIELD(frameOptions);
return newnode;
}
@@ -1783,6 +1785,7 @@ _copyWindowClause(WindowClause *from)
COPY_STRING_FIELD(refname);
COPY_NODE_FIELD(partitionClause);
COPY_NODE_FIELD(orderClause);
+ COPY_SCALAR_FIELD(frameOptions);
COPY_SCALAR_FIELD(winref);
COPY_SCALAR_FIELD(copiedOrder);
@@ -2010,6 +2013,7 @@ _copyWindowDef(WindowDef *from)
COPY_STRING_FIELD(refname);
COPY_NODE_FIELD(partitionClause);
COPY_NODE_FIELD(orderClause);
+ COPY_SCALAR_FIELD(frameOptions);
COPY_LOCATION_FIELD(location);
return newnode;
diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c
index e96c66152e8..1131217731c 100644
--- a/src/backend/nodes/equalfuncs.c
+++ b/src/backend/nodes/equalfuncs.c
@@ -22,7 +22,7 @@
* Portions Copyright (c) 1994, Regents of the University of California
*
* IDENTIFICATION
- * $PostgreSQL: pgsql/src/backend/nodes/equalfuncs.c,v 1.342 2008/12/28 18:53:56 tgl Exp $
+ * $PostgreSQL: pgsql/src/backend/nodes/equalfuncs.c,v 1.343 2008/12/31 00:08:35 tgl Exp $
*
*-------------------------------------------------------------------------
*/
@@ -2005,6 +2005,7 @@ _equalWindowDef(WindowDef *a, WindowDef *b)
COMPARE_STRING_FIELD(refname);
COMPARE_NODE_FIELD(partitionClause);
COMPARE_NODE_FIELD(orderClause);
+ COMPARE_SCALAR_FIELD(frameOptions);
COMPARE_LOCATION_FIELD(location);
return true;
@@ -2143,6 +2144,7 @@ _equalWindowClause(WindowClause *a, WindowClause *b)
COMPARE_STRING_FIELD(refname);
COMPARE_NODE_FIELD(partitionClause);
COMPARE_NODE_FIELD(orderClause);
+ COMPARE_SCALAR_FIELD(frameOptions);
COMPARE_SCALAR_FIELD(winref);
COMPARE_SCALAR_FIELD(copiedOrder);
diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.c
index f926f1314cd..5ece924e795 100644
--- a/src/backend/nodes/outfuncs.c
+++ b/src/backend/nodes/outfuncs.c
@@ -8,7 +8,7 @@
*
*
* IDENTIFICATION
- * $PostgreSQL: pgsql/src/backend/nodes/outfuncs.c,v 1.347 2008/12/28 18:53:56 tgl Exp $
+ * $PostgreSQL: pgsql/src/backend/nodes/outfuncs.c,v 1.348 2008/12/31 00:08:36 tgl Exp $
*
* NOTES
* Every node type that can appear in stored rules' parsetrees *must*
@@ -575,6 +575,7 @@ _outWindowAgg(StringInfo str, WindowAgg *node)
_outPlanInfo(str, (Plan *) node);
+ WRITE_UINT_FIELD(winref);
WRITE_INT_FIELD(partNumCols);
appendStringInfo(str, " :partColIdx");
@@ -594,6 +595,8 @@ _outWindowAgg(StringInfo str, WindowAgg *node)
appendStringInfo(str, " :ordOperations");
for (i = 0; i < node->ordNumCols; i++)
appendStringInfo(str, " %u", node->ordOperators[i]);
+
+ WRITE_INT_FIELD(frameOptions);
}
static void
@@ -1953,6 +1956,7 @@ _outWindowClause(StringInfo str, WindowClause *node)
WRITE_STRING_FIELD(refname);
WRITE_NODE_FIELD(partitionClause);
WRITE_NODE_FIELD(orderClause);
+ WRITE_INT_FIELD(frameOptions);
WRITE_UINT_FIELD(winref);
WRITE_BOOL_FIELD(copiedOrder);
}
@@ -2242,6 +2246,7 @@ _outWindowDef(StringInfo str, WindowDef *node)
WRITE_STRING_FIELD(refname);
WRITE_NODE_FIELD(partitionClause);
WRITE_NODE_FIELD(orderClause);
+ WRITE_INT_FIELD(frameOptions);
WRITE_LOCATION_FIELD(location);
}
diff --git a/src/backend/nodes/readfuncs.c b/src/backend/nodes/readfuncs.c
index 7bcc8e8047d..a225915de76 100644
--- a/src/backend/nodes/readfuncs.c
+++ b/src/backend/nodes/readfuncs.c
@@ -8,7 +8,7 @@
*
*
* IDENTIFICATION
- * $PostgreSQL: pgsql/src/backend/nodes/readfuncs.c,v 1.218 2008/12/28 18:53:56 tgl Exp $
+ * $PostgreSQL: pgsql/src/backend/nodes/readfuncs.c,v 1.219 2008/12/31 00:08:36 tgl Exp $
*
* NOTES
* Path and Plan nodes do not have any readfuncs support, because we
@@ -231,6 +231,7 @@ _readWindowClause(void)
READ_STRING_FIELD(refname);
READ_NODE_FIELD(partitionClause);
READ_NODE_FIELD(orderClause);
+ READ_INT_FIELD(frameOptions);
READ_UINT_FIELD(winref);
READ_BOOL_FIELD(copiedOrder);
diff --git a/src/backend/optimizer/plan/createplan.c b/src/backend/optimizer/plan/createplan.c
index b53b5e1470e..55baa9a51a6 100644
--- a/src/backend/optimizer/plan/createplan.c
+++ b/src/backend/optimizer/plan/createplan.c
@@ -10,7 +10,7 @@
*
*
* IDENTIFICATION
- * $PostgreSQL: pgsql/src/backend/optimizer/plan/createplan.c,v 1.253 2008/12/28 18:53:56 tgl Exp $
+ * $PostgreSQL: pgsql/src/backend/optimizer/plan/createplan.c,v 1.254 2008/12/31 00:08:36 tgl Exp $
*
*-------------------------------------------------------------------------
*/
@@ -3261,22 +3261,25 @@ make_agg(PlannerInfo *root, List *tlist, List *qual,
}
WindowAgg *
-make_windowagg(PlannerInfo *root, List *tlist, int numWindowFuncs,
+make_windowagg(PlannerInfo *root, List *tlist,
+ int numWindowFuncs, Index winref,
int partNumCols, AttrNumber *partColIdx, Oid *partOperators,
int ordNumCols, AttrNumber *ordColIdx, Oid *ordOperators,
- Plan *lefttree)
+ int frameOptions, Plan *lefttree)
{
WindowAgg *node = makeNode(WindowAgg);
Plan *plan = &node->plan;
Path windowagg_path; /* dummy for result of cost_windowagg */
QualCost qual_cost;
+ node->winref = winref;
node->partNumCols = partNumCols;
node->partColIdx = partColIdx;
node->partOperators = partOperators;
node->ordNumCols = ordNumCols;
node->ordColIdx = ordColIdx;
node->ordOperators = ordOperators;
+ node->frameOptions = frameOptions;
copy_plan_costsize(plan, lefttree); /* only care about copying size */
cost_windowagg(&windowagg_path, root,
diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c
index b4b578d5973..495503bb51e 100644
--- a/src/backend/optimizer/plan/planner.c
+++ b/src/backend/optimizer/plan/planner.c
@@ -8,7 +8,7 @@
*
*
* IDENTIFICATION
- * $PostgreSQL: pgsql/src/backend/optimizer/plan/planner.c,v 1.248 2008/12/28 18:53:56 tgl Exp $
+ * $PostgreSQL: pgsql/src/backend/optimizer/plan/planner.c,v 1.249 2008/12/31 00:08:36 tgl Exp $
*
*-------------------------------------------------------------------------
*/
@@ -1398,12 +1398,14 @@ grouping_planner(PlannerInfo *root, double tuple_fraction)
make_windowagg(root,
(List *) copyObject(window_tlist),
list_length(wflists->windowFuncs[wc->winref]),
+ wc->winref,
partNumCols,
partColIdx,
partOperators,
ordNumCols,
ordColIdx,
ordOperators,
+ wc->frameOptions,
result_plan);
}
}
@@ -2412,6 +2414,7 @@ select_active_windows(PlannerInfo *root, WindowFuncLists *wflists)
WindowClause *wc2 = (WindowClause *) lfirst(lc);
next = lnext(lc);
+ /* framing options are NOT to be compared here! */
if (equal(wc->partitionClause, wc2->partitionClause) &&
equal(wc->orderClause, wc2->orderClause))
{
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 59b7ada7b43..34dc40a1056 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -11,7 +11,7 @@
*
*
* IDENTIFICATION
- * $PostgreSQL: pgsql/src/backend/parser/gram.y,v 2.648 2008/12/28 18:53:58 tgl Exp $
+ * $PostgreSQL: pgsql/src/backend/parser/gram.y,v 2.649 2008/12/31 00:08:36 tgl Exp $
*
* HISTORY
* AUTHOR DATE MAJOR EVENT
@@ -406,6 +406,7 @@ static TypeName *TableFuncTypeName(List *columns);
%type <list> window_clause window_definition_list opt_partition_clause
%type <windef> window_definition over_clause window_specification
%type <str> opt_existing_window_name
+%type <ival> opt_frame_clause frame_extent frame_bound
/*
@@ -439,7 +440,7 @@ static TypeName *TableFuncTypeName(List *columns);
EACH ELSE ENABLE_P ENCODING ENCRYPTED END_P ENUM_P ESCAPE EXCEPT
EXCLUDING EXCLUSIVE EXECUTE EXISTS EXPLAIN EXTERNAL EXTRACT
- FALSE_P FAMILY FETCH FIRST_P FLOAT_P FOR FORCE FOREIGN FORWARD
+ FALSE_P FAMILY FETCH FIRST_P FLOAT_P FOLLOWING FOR FORCE FOREIGN FORWARD
FREEZE FROM FULL FUNCTION
GLOBAL GRANT GRANTED GREATEST GROUP_P
@@ -469,14 +470,14 @@ static TypeName *TableFuncTypeName(List *columns);
ORDER OUT_P OUTER_P OVER OVERLAPS OVERLAY OWNED OWNER
PARSER PARTIAL PARTITION PASSWORD PLACING PLANS POSITION
- PRECISION PRESERVE PREPARE PREPARED PRIMARY
+ PRECEDING PRECISION PRESERVE PREPARE PREPARED PRIMARY
PRIOR PRIVILEGES PROCEDURAL PROCEDURE
QUOTE
- READ REAL REASSIGN RECHECK RECURSIVE REFERENCES REINDEX RELATIVE_P RELEASE
- RENAME REPEATABLE REPLACE REPLICA RESET RESTART RESTRICT RETURNING RETURNS
- REVOKE RIGHT ROLE ROLLBACK ROW ROWS RULE
+ RANGE READ REAL REASSIGN RECHECK RECURSIVE REFERENCES REINDEX
+ RELATIVE_P RELEASE RENAME REPEATABLE REPLACE REPLICA RESET RESTART
+ RESTRICT RETURNING RETURNS REVOKE RIGHT ROLE ROLLBACK ROW ROWS RULE
SAVEPOINT SCHEMA SCROLL SEARCH SECOND_P SECURITY SELECT SEQUENCE
SERIALIZABLE SERVER SESSION SESSION_USER SET SETOF SHARE
@@ -488,7 +489,7 @@ static TypeName *TableFuncTypeName(List *columns);
TO TRAILING TRANSACTION TREAT TRIGGER TRIM TRUE_P
TRUNCATE TRUSTED TYPE_P
- UNCOMMITTED UNENCRYPTED UNION UNIQUE UNKNOWN UNLISTEN UNTIL
+ UNBOUNDED UNCOMMITTED UNENCRYPTED UNION UNIQUE UNKNOWN UNLISTEN UNTIL
UPDATE USER USING
VACUUM VALID VALIDATOR VALUE_P VALUES VARCHAR VARIADIC VARYING
@@ -533,10 +534,12 @@ static TypeName *TableFuncTypeName(List *columns);
* between POSTFIXOP and Op. We can safely assign the same priority to
* various unreserved keywords as needed to resolve ambiguities (this can't
* have any bad effects since obviously the keywords will still behave the
- * same as if they weren't keywords). We need to do this for PARTITION
- * to support opt_existing_window_name.
+ * same as if they weren't keywords). We need to do this for PARTITION,
+ * RANGE, ROWS to support opt_existing_window_name; and for RANGE, ROWS
+ * so that they can follow a_expr without creating
+ * postfix-operator problems.
*/
-%nonassoc IDENT PARTITION
+%nonassoc IDENT PARTITION RANGE ROWS
%left Op OPERATOR /* multi-character ops and user-defined operators */
%nonassoc NOTNULL
%nonassoc ISNULL
@@ -9235,10 +9238,11 @@ over_clause: OVER window_specification
| OVER ColId
{
WindowDef *n = makeNode(WindowDef);
- n->name = NULL;
- n->refname = $2;
+ n->name = $2;
+ n->refname = NULL;
n->partitionClause = NIL;
n->orderClause = NIL;
+ n->frameOptions = FRAMEOPTION_DEFAULTS;
n->location = @2;
$$ = n;
}
@@ -9247,13 +9251,14 @@ over_clause: OVER window_specification
;
window_specification: '(' opt_existing_window_name opt_partition_clause
- opt_sort_clause ')'
+ opt_sort_clause opt_frame_clause ')'
{
WindowDef *n = makeNode(WindowDef);
n->name = NULL;
n->refname = $2;
n->partitionClause = $3;
n->orderClause = $4;
+ n->frameOptions = $5;
n->location = @1;
$$ = n;
}
@@ -9268,7 +9273,6 @@ window_specification: '(' opt_existing_window_name opt_partition_clause
* that the shift/reduce conflict is resolved in favor of reducing the rule.
* These keywords are thus precluded from being an existing_window_name but
* are not reserved for any other purpose.
- * (RANGE/ROWS are not an issue as of 8.4 for lack of frame_clause support.)
*/
opt_existing_window_name: ColId { $$ = $1; }
| /*EMPTY*/ %prec Op { $$ = NULL; }
@@ -9279,6 +9283,83 @@ opt_partition_clause: PARTITION BY expr_list { $$ = $3; }
;
/*
+ * This is only a subset of the full SQL:2008 frame_clause grammar.
+ * We don't support <expression> PRECEDING, <expression> FOLLOWING,
+ * nor <window frame exclusion> yet.
+ */
+opt_frame_clause:
+ RANGE frame_extent
+ {
+ $$ = FRAMEOPTION_NONDEFAULT | FRAMEOPTION_RANGE | $2;
+ }
+ | ROWS frame_extent
+ {
+ $$ = FRAMEOPTION_NONDEFAULT | FRAMEOPTION_ROWS | $2;
+ }
+ | /*EMPTY*/
+ { $$ = FRAMEOPTION_DEFAULTS; }
+ ;
+
+frame_extent: frame_bound
+ {
+ /* reject invalid cases */
+ if ($1 & FRAMEOPTION_START_UNBOUNDED_FOLLOWING)
+ ereport(ERROR,
+ (errcode(ERRCODE_WINDOWING_ERROR),
+ errmsg("frame start cannot be UNBOUNDED FOLLOWING"),
+ scanner_errposition(@1)));
+ if ($1 & FRAMEOPTION_START_CURRENT_ROW)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("frame start at CURRENT ROW is not implemented"),
+ scanner_errposition(@1)));
+ $$ = $1 | FRAMEOPTION_END_CURRENT_ROW;
+ }
+ | BETWEEN frame_bound AND frame_bound
+ {
+ /* reject invalid cases */
+ if ($2 & FRAMEOPTION_START_UNBOUNDED_FOLLOWING)
+ ereport(ERROR,
+ (errcode(ERRCODE_WINDOWING_ERROR),
+ errmsg("frame start cannot be UNBOUNDED FOLLOWING"),
+ scanner_errposition(@2)));
+ if ($2 & FRAMEOPTION_START_CURRENT_ROW)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("frame start at CURRENT ROW is not implemented"),
+ scanner_errposition(@2)));
+ if ($4 & FRAMEOPTION_START_UNBOUNDED_PRECEDING)
+ ereport(ERROR,
+ (errcode(ERRCODE_WINDOWING_ERROR),
+ errmsg("frame end cannot be UNBOUNDED PRECEDING"),
+ scanner_errposition(@4)));
+ /* shift converts START_ options to END_ options */
+ $$ = FRAMEOPTION_BETWEEN | $2 | ($4 << 1);
+ }
+ ;
+
+/*
+ * This is used for both frame start and frame end, with output set up on
+ * the assumption it's frame start; the frame_extent productions must reject
+ * invalid cases.
+ */
+frame_bound:
+ UNBOUNDED PRECEDING
+ {
+ $$ = FRAMEOPTION_START_UNBOUNDED_PRECEDING;
+ }
+ | UNBOUNDED FOLLOWING
+ {
+ $$ = FRAMEOPTION_START_UNBOUNDED_FOLLOWING;
+ }
+ | CURRENT_P ROW
+ {
+ $$ = FRAMEOPTION_START_CURRENT_ROW;
+ }
+ ;
+
+
+/*
* Supporting nonterminals for expressions.
*/
@@ -10012,6 +10093,7 @@ unreserved_keyword:
| EXTERNAL
| FAMILY
| FIRST_P
+ | FOLLOWING
| FORCE
| FORWARD
| FUNCTION
@@ -10086,6 +10168,7 @@ unreserved_keyword:
| PARTITION
| PASSWORD
| PLANS
+ | PRECEDING
| PREPARE
| PREPARED
| PRESERVE
@@ -10094,6 +10177,7 @@ unreserved_keyword:
| PROCEDURAL
| PROCEDURE
| QUOTE
+ | RANGE
| READ
| REASSIGN
| RECHECK
@@ -10151,6 +10235,7 @@ unreserved_keyword:
| TRUNCATE
| TRUSTED
| TYPE_P
+ | UNBOUNDED
| UNCOMMITTED
| UNENCRYPTED
| UNKNOWN
diff --git a/src/backend/parser/keywords.c b/src/backend/parser/keywords.c
index c3ad852258b..730974b94d8 100644
--- a/src/backend/parser/keywords.c
+++ b/src/backend/parser/keywords.c
@@ -11,7 +11,7 @@
*
*
* IDENTIFICATION
- * $PostgreSQL: pgsql/src/backend/parser/keywords.c,v 1.207 2008/12/28 18:53:58 tgl Exp $
+ * $PostgreSQL: pgsql/src/backend/parser/keywords.c,v 1.208 2008/12/31 00:08:37 tgl Exp $
*
*-------------------------------------------------------------------------
*/
@@ -172,6 +172,7 @@ const ScanKeyword ScanKeywords[] = {
{"fetch", FETCH, RESERVED_KEYWORD},
{"first", FIRST_P, UNRESERVED_KEYWORD},
{"float", FLOAT_P, COL_NAME_KEYWORD},
+ {"following", FOLLOWING, UNRESERVED_KEYWORD},
{"for", FOR, RESERVED_KEYWORD},
{"force", FORCE, UNRESERVED_KEYWORD},
{"foreign", FOREIGN, RESERVED_KEYWORD},
@@ -299,6 +300,7 @@ const ScanKeyword ScanKeywords[] = {
{"placing", PLACING, RESERVED_KEYWORD},
{"plans", PLANS, UNRESERVED_KEYWORD},
{"position", POSITION, COL_NAME_KEYWORD},
+ {"preceding", PRECEDING, UNRESERVED_KEYWORD},
{"precision", PRECISION, COL_NAME_KEYWORD},
{"prepare", PREPARE, UNRESERVED_KEYWORD},
{"prepared", PREPARED, UNRESERVED_KEYWORD},
@@ -309,6 +311,7 @@ const ScanKeyword ScanKeywords[] = {
{"procedural", PROCEDURAL, UNRESERVED_KEYWORD},
{"procedure", PROCEDURE, UNRESERVED_KEYWORD},
{"quote", QUOTE, UNRESERVED_KEYWORD},
+ {"range", RANGE, UNRESERVED_KEYWORD},
{"read", READ, UNRESERVED_KEYWORD},
{"real", REAL, COL_NAME_KEYWORD},
{"reassign", REASSIGN, UNRESERVED_KEYWORD},
@@ -388,6 +391,7 @@ const ScanKeyword ScanKeywords[] = {
{"truncate", TRUNCATE, UNRESERVED_KEYWORD},
{"trusted", TRUSTED, UNRESERVED_KEYWORD},
{"type", TYPE_P, UNRESERVED_KEYWORD},
+ {"unbounded", UNBOUNDED, UNRESERVED_KEYWORD},
{"uncommitted", UNCOMMITTED, UNRESERVED_KEYWORD},
{"unencrypted", UNENCRYPTED, UNRESERVED_KEYWORD},
{"union", UNION, RESERVED_KEYWORD},
diff --git a/src/backend/parser/parse_agg.c b/src/backend/parser/parse_agg.c
index 6dba470e39f..23913c62914 100644
--- a/src/backend/parser/parse_agg.c
+++ b/src/backend/parser/parse_agg.c
@@ -8,7 +8,7 @@
*
*
* IDENTIFICATION
- * $PostgreSQL: pgsql/src/backend/parser/parse_agg.c,v 1.85 2008/12/28 18:53:58 tgl Exp $
+ * $PostgreSQL: pgsql/src/backend/parser/parse_agg.c,v 1.86 2008/12/31 00:08:37 tgl Exp $
*
*-------------------------------------------------------------------------
*/
@@ -123,25 +123,27 @@ transformWindowFuncCall(ParseState *pstate, WindowFunc *wfunc,
locate_windowfunc((Node *) wfunc->args))));
/*
- * If the OVER clause just specifies a reference name, find that
+ * If the OVER clause just specifies a window name, find that
* WINDOW clause (which had better be present). Otherwise, try to
* match all the properties of the OVER clause, and make a new entry
* in the p_windowdefs list if no luck.
*/
- Assert(!windef->name);
- if (windef->refname &&
- windef->partitionClause == NIL &&
- windef->orderClause == NIL)
+ if (windef->name)
{
Index winref = 0;
ListCell *lc;
+ Assert(windef->refname == NULL &&
+ windef->partitionClause == NIL &&
+ windef->orderClause == NIL &&
+ windef->frameOptions == FRAMEOPTION_DEFAULTS);
+
foreach(lc, pstate->p_windowdefs)
{
WindowDef *refwin = (WindowDef *) lfirst(lc);
winref++;
- if (refwin->name && strcmp(refwin->name, windef->refname) == 0)
+ if (refwin->name && strcmp(refwin->name, windef->name) == 0)
{
wfunc->winref = winref;
break;
@@ -150,7 +152,7 @@ transformWindowFuncCall(ParseState *pstate, WindowFunc *wfunc,
if (lc == NULL) /* didn't find it? */
ereport(ERROR,
(errcode(ERRCODE_UNDEFINED_OBJECT),
- errmsg("window \"%s\" does not exist", windef->refname),
+ errmsg("window \"%s\" does not exist", windef->name),
parser_errposition(pstate, windef->location)));
}
else
@@ -164,14 +166,15 @@ transformWindowFuncCall(ParseState *pstate, WindowFunc *wfunc,
winref++;
if (refwin->refname && windef->refname &&
- strcmp(refwin->name, windef->refname) == 0)
+ strcmp(refwin->refname, windef->refname) == 0)
/* matched on refname */ ;
else if (!refwin->refname && !windef->refname)
/* matched, no refname */ ;
else
continue;
if (equal(refwin->partitionClause, windef->partitionClause) &&
- equal(refwin->orderClause, windef->orderClause))
+ equal(refwin->orderClause, windef->orderClause) &&
+ refwin->frameOptions == windef->frameOptions)
{
/* found a duplicate window specification */
wfunc->winref = winref;
diff --git a/src/backend/parser/parse_clause.c b/src/backend/parser/parse_clause.c
index df30361f0a5..247d54d8782 100644
--- a/src/backend/parser/parse_clause.c
+++ b/src/backend/parser/parse_clause.c
@@ -8,7 +8,7 @@
*
*
* IDENTIFICATION
- * $PostgreSQL: pgsql/src/backend/parser/parse_clause.c,v 1.182 2008/12/28 18:53:58 tgl Exp $
+ * $PostgreSQL: pgsql/src/backend/parser/parse_clause.c,v 1.183 2008/12/31 00:08:37 tgl Exp $
*
*-------------------------------------------------------------------------
*/
@@ -1554,7 +1554,7 @@ transformWindowDefinitions(ParseState *pstate,
* Per spec, a windowdef that references a previous one copies the
* previous partition clause (and mustn't specify its own). It can
* specify its own ordering clause. but only if the previous one
- * had none.
+ * had none. It always specifies its own framing clause.
*/
if (refwc)
{
@@ -1592,6 +1592,7 @@ transformWindowDefinitions(ParseState *pstate,
wc->orderClause = orderClause;
wc->copiedOrder = false;
}
+ wc->frameOptions = windef->frameOptions;
wc->winref = winref;
result = lappend(result, wc);
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index 969977cea51..4a44b733cf3 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -9,7 +9,7 @@
*
*
* IDENTIFICATION
- * $PostgreSQL: pgsql/src/backend/utils/adt/ruleutils.c,v 1.291 2008/12/28 18:53:59 tgl Exp $
+ * $PostgreSQL: pgsql/src/backend/utils/adt/ruleutils.c,v 1.292 2008/12/31 00:08:37 tgl Exp $
*
*-------------------------------------------------------------------------
*/
@@ -2903,7 +2903,7 @@ get_rule_windowspec(WindowClause *wc, List *targetList,
appendStringInfoString(buf, quote_identifier(wc->refname));
needspace = true;
}
- /* partitions are always inherited, so only print if no refname */
+ /* partition clauses are always inherited, so only print if no refname */
if (wc->partitionClause && !wc->refname)
{
if (needspace)
@@ -2921,6 +2921,7 @@ get_rule_windowspec(WindowClause *wc, List *targetList,
}
needspace = true;
}
+ /* print ordering clause only if not inherited */
if (wc->orderClause && !wc->copiedOrder)
{
if (needspace)
@@ -2929,6 +2930,38 @@ get_rule_windowspec(WindowClause *wc, List *targetList,
get_rule_orderby(wc->orderClause, targetList, false, context);
needspace = true;
}
+ /* framing clause is never inherited, so print unless it's default */
+ if (wc->frameOptions & FRAMEOPTION_NONDEFAULT)
+ {
+ if (needspace)
+ appendStringInfoChar(buf, ' ');
+ if (wc->frameOptions & FRAMEOPTION_RANGE)
+ appendStringInfoString(buf, "RANGE ");
+ else if (wc->frameOptions & FRAMEOPTION_ROWS)
+ appendStringInfoString(buf, "ROWS ");
+ else
+ Assert(false);
+ if (wc->frameOptions & FRAMEOPTION_BETWEEN)
+ appendStringInfoString(buf, "BETWEEN ");
+ if (wc->frameOptions & FRAMEOPTION_START_UNBOUNDED_PRECEDING)
+ appendStringInfoString(buf, "UNBOUNDED PRECEDING ");
+ else if (wc->frameOptions & FRAMEOPTION_START_CURRENT_ROW)
+ appendStringInfoString(buf, "CURRENT ROW ");
+ else
+ Assert(false);
+ if (wc->frameOptions & FRAMEOPTION_BETWEEN)
+ {
+ appendStringInfoString(buf, "AND ");
+ if (wc->frameOptions & FRAMEOPTION_END_UNBOUNDED_FOLLOWING)
+ appendStringInfoString(buf, "UNBOUNDED FOLLOWING ");
+ else if (wc->frameOptions & FRAMEOPTION_END_CURRENT_ROW)
+ appendStringInfoString(buf, "CURRENT ROW ");
+ else
+ Assert(false);
+ }
+ /* we will now have a trailing space; remove it */
+ buf->len--;
+ }
appendStringInfoChar(buf, ')');
}
diff --git a/src/include/catalog/catversion.h b/src/include/catalog/catversion.h
index 21bbd429ae8..eff2c3bf9ad 100644
--- a/src/include/catalog/catversion.h
+++ b/src/include/catalog/catversion.h
@@ -37,7 +37,7 @@
* Portions Copyright (c) 1996-2008, PostgreSQL Global Development Group
* Portions Copyright (c) 1994, Regents of the University of California
*
- * $PostgreSQL: pgsql/src/include/catalog/catversion.h,v 1.514 2008/12/28 18:53:59 tgl Exp $
+ * $PostgreSQL: pgsql/src/include/catalog/catversion.h,v 1.515 2008/12/31 00:08:38 tgl Exp $
*
*-------------------------------------------------------------------------
*/
@@ -53,6 +53,6 @@
*/
/* yyyymmddN */
-#define CATALOG_VERSION_NO 200812281
+#define CATALOG_VERSION_NO 200812301
#endif
diff --git a/src/include/nodes/execnodes.h b/src/include/nodes/execnodes.h
index 258abdee6aa..8d3746f2cfe 100644
--- a/src/include/nodes/execnodes.h
+++ b/src/include/nodes/execnodes.h
@@ -7,7 +7,7 @@
* Portions Copyright (c) 1996-2008, PostgreSQL Global Development Group
* Portions Copyright (c) 1994, Regents of the University of California
*
- * $PostgreSQL: pgsql/src/include/nodes/execnodes.h,v 1.197 2008/12/28 18:54:00 tgl Exp $
+ * $PostgreSQL: pgsql/src/include/nodes/execnodes.h,v 1.198 2008/12/31 00:08:38 tgl Exp $
*
*-------------------------------------------------------------------------
*/
@@ -1533,12 +1533,14 @@ typedef struct WindowAggState
* have been spooled into tuplestore */
bool more_partitions; /* true if there's more partitions after
* this one */
+ bool frametail_valid; /* true if frametailpos is known up to date
+ * for current row */
TupleTableSlot *first_part_slot; /* first tuple of current or next
* partition */
/* temporary slots for tuples fetched back from tuplestore */
- TupleTableSlot *first_peer_slot;
+ TupleTableSlot *agg_row_slot;
TupleTableSlot *temp_slot_1;
TupleTableSlot *temp_slot_2;
} WindowAggState;
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 43c4452d781..6a0cca3632e 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -13,7 +13,7 @@
* Portions Copyright (c) 1996-2008, PostgreSQL Global Development Group
* Portions Copyright (c) 1994, Regents of the University of California
*
- * $PostgreSQL: pgsql/src/include/nodes/parsenodes.h,v 1.385 2008/12/28 18:54:00 tgl Exp $
+ * $PostgreSQL: pgsql/src/include/nodes/parsenodes.h,v 1.386 2008/12/31 00:08:38 tgl Exp $
*
*-------------------------------------------------------------------------
*/
@@ -382,18 +382,47 @@ typedef struct SortBy
/*
* WindowDef - raw representation of WINDOW and OVER clauses
+ *
+ * For entries in a WINDOW list, "name" is the window name being defined.
+ * For OVER clauses, we use "name" for the "OVER window" syntax, or "refname"
+ * for the "OVER (window)" syntax, which is subtly different --- the latter
+ * implies overriding the window frame clause.
*/
typedef struct WindowDef
{
NodeTag type;
- char *name; /* window name (NULL in an OVER clause) */
+ char *name; /* window's own name */
char *refname; /* referenced window name, if any */
List *partitionClause; /* PARTITION BY expression list */
List *orderClause; /* ORDER BY (list of SortBy) */
+ int frameOptions; /* frame_clause options, see below */
int location; /* parse location, or -1 if none/unknown */
} WindowDef;
/*
+ * frameOptions is an OR of these bits. The NONDEFAULT and BETWEEN bits are
+ * used so that ruleutils.c can tell which properties were specified and
+ * which were defaulted; the correct behavioral bits must be set either way.
+ * The START_foo and END_foo options must come in pairs of adjacent bits for
+ * the convenience of gram.y, even though some of them are useless/invalid.
+ * We will need more bits (and fields) to cover the full SQL:2008 option set.
+ */
+#define FRAMEOPTION_NONDEFAULT 0x00001 /* any specified? */
+#define FRAMEOPTION_RANGE 0x00002 /* RANGE behavior */
+#define FRAMEOPTION_ROWS 0x00004 /* ROWS behavior */
+#define FRAMEOPTION_BETWEEN 0x00008 /* BETWEEN given? */
+#define FRAMEOPTION_START_UNBOUNDED_PRECEDING 0x00010 /* start is U. P. */
+#define FRAMEOPTION_END_UNBOUNDED_PRECEDING 0x00020 /* (disallowed) */
+#define FRAMEOPTION_START_UNBOUNDED_FOLLOWING 0x00040 /* (disallowed) */
+#define FRAMEOPTION_END_UNBOUNDED_FOLLOWING 0x00080 /* end is U. F. */
+#define FRAMEOPTION_START_CURRENT_ROW 0x00100 /* start is C. R. */
+#define FRAMEOPTION_END_CURRENT_ROW 0x00200 /* end is C. R. */
+
+#define FRAMEOPTION_DEFAULTS \
+ (FRAMEOPTION_RANGE | FRAMEOPTION_START_UNBOUNDED_PRECEDING | \
+ FRAMEOPTION_END_CURRENT_ROW)
+
+/*
* RangeSubselect - subquery appearing in a FROM clause
*/
typedef struct RangeSubselect
@@ -744,8 +773,8 @@ typedef struct SortGroupClause
* winref is an ID number referenced by WindowFunc nodes; it must be unique
* among the members of a Query's windowClause list.
* When refname isn't null, the partitionClause is always copied from there;
- * the orderClause might or might not be copied. (We don't implement
- * framing clauses yet, but if we did, they are never copied, per spec.)
+ * the orderClause might or might not be copied (see copiedOrder); the framing
+ * options are never copied, per spec.
*/
typedef struct WindowClause
{
@@ -754,6 +783,7 @@ typedef struct WindowClause
char *refname; /* referenced window name, if any */
List *partitionClause; /* PARTITION BY list */
List *orderClause; /* ORDER BY list */
+ int frameOptions; /* frame_clause options, see WindowDef */
Index winref; /* ID referenced by window functions */
bool copiedOrder; /* did we copy orderClause from refname? */
} WindowClause;
diff --git a/src/include/nodes/plannodes.h b/src/include/nodes/plannodes.h
index e320fd5fb4c..959a4a9f7c9 100644
--- a/src/include/nodes/plannodes.h
+++ b/src/include/nodes/plannodes.h
@@ -7,7 +7,7 @@
* Portions Copyright (c) 1996-2008, PostgreSQL Global Development Group
* Portions Copyright (c) 1994, Regents of the University of California
*
- * $PostgreSQL: pgsql/src/include/nodes/plannodes.h,v 1.106 2008/12/28 18:54:00 tgl Exp $
+ * $PostgreSQL: pgsql/src/include/nodes/plannodes.h,v 1.107 2008/12/31 00:08:38 tgl Exp $
*
*-------------------------------------------------------------------------
*/
@@ -543,12 +543,14 @@ typedef struct Agg
typedef struct WindowAgg
{
Plan plan;
+ Index winref; /* ID referenced by window functions */
int partNumCols; /* number of columns in partition clause */
AttrNumber *partColIdx; /* their indexes in the target list */
Oid *partOperators; /* equality operators for partition columns */
int ordNumCols; /* number of columns in ordering clause */
AttrNumber *ordColIdx; /* their indexes in the target list */
Oid *ordOperators; /* equality operators for ordering columns */
+ int frameOptions; /* frame_clause options, see WindowDef */
} WindowAgg;
/* ----------------
diff --git a/src/include/optimizer/planmain.h b/src/include/optimizer/planmain.h
index d6f5ff160f6..5ae71625219 100644
--- a/src/include/optimizer/planmain.h
+++ b/src/include/optimizer/planmain.h
@@ -7,7 +7,7 @@
* Portions Copyright (c) 1996-2008, PostgreSQL Global Development Group
* Portions Copyright (c) 1994, Regents of the University of California
*
- * $PostgreSQL: pgsql/src/include/optimizer/planmain.h,v 1.115 2008/12/28 18:54:01 tgl Exp $
+ * $PostgreSQL: pgsql/src/include/optimizer/planmain.h,v 1.116 2008/12/31 00:08:38 tgl Exp $
*
*-------------------------------------------------------------------------
*/
@@ -57,10 +57,10 @@ extern Agg *make_agg(PlannerInfo *root, List *tlist, List *qual,
long numGroups, int numAggs,
Plan *lefttree);
extern WindowAgg *make_windowagg(PlannerInfo *root, List *tlist,
- int numWindowFuncs,
+ int numWindowFuncs, Index winref,
int partNumCols, AttrNumber *partColIdx, Oid *partOperators,
int ordNumCols, AttrNumber *ordColIdx, Oid *ordOperators,
- Plan *lefttree);
+ int frameOptions, Plan *lefttree);
extern Group *make_group(PlannerInfo *root, List *tlist, List *qual,
int numGroupCols, AttrNumber *grpColIdx, Oid *grpOperators,
double numGroups,
diff --git a/src/test/regress/expected/window.out b/src/test/regress/expected/window.out
index 8454d82183b..5ef3e6c3175 100644
--- a/src/test/regress/expected/window.out
+++ b/src/test/regress/expected/window.out
@@ -587,30 +587,153 @@ SELECT empno, depname, salary, bonus, depadj, MIN(bonus) OVER (ORDER BY empno),
11 | develop | 5200 | 500 | 200 | 500 | 200
(10 rows)
+-- test non-default frame specifications
+SELECT four, ten,
+ sum(ten) over (partition by four order by ten),
+ last_value(ten) over (partition by four order by ten)
+FROM (select distinct ten, four from tenk1) ss;
+ four | ten | sum | last_value
+------+-----+-----+------------
+ 0 | 0 | 0 | 0
+ 0 | 2 | 2 | 2
+ 0 | 4 | 6 | 4
+ 0 | 6 | 12 | 6
+ 0 | 8 | 20 | 8
+ 1 | 1 | 1 | 1
+ 1 | 3 | 4 | 3
+ 1 | 5 | 9 | 5
+ 1 | 7 | 16 | 7
+ 1 | 9 | 25 | 9
+ 2 | 0 | 0 | 0
+ 2 | 2 | 2 | 2
+ 2 | 4 | 6 | 4
+ 2 | 6 | 12 | 6
+ 2 | 8 | 20 | 8
+ 3 | 1 | 1 | 1
+ 3 | 3 | 4 | 3
+ 3 | 5 | 9 | 5
+ 3 | 7 | 16 | 7
+ 3 | 9 | 25 | 9
+(20 rows)
+
+SELECT four, ten,
+ sum(ten) over (partition by four order by ten range between unbounded preceding and current row),
+ last_value(ten) over (partition by four order by ten range between unbounded preceding and current row)
+FROM (select distinct ten, four from tenk1) ss;
+ four | ten | sum | last_value
+------+-----+-----+------------
+ 0 | 0 | 0 | 0
+ 0 | 2 | 2 | 2
+ 0 | 4 | 6 | 4
+ 0 | 6 | 12 | 6
+ 0 | 8 | 20 | 8
+ 1 | 1 | 1 | 1
+ 1 | 3 | 4 | 3
+ 1 | 5 | 9 | 5
+ 1 | 7 | 16 | 7
+ 1 | 9 | 25 | 9
+ 2 | 0 | 0 | 0
+ 2 | 2 | 2 | 2
+ 2 | 4 | 6 | 4
+ 2 | 6 | 12 | 6
+ 2 | 8 | 20 | 8
+ 3 | 1 | 1 | 1
+ 3 | 3 | 4 | 3
+ 3 | 5 | 9 | 5
+ 3 | 7 | 16 | 7
+ 3 | 9 | 25 | 9
+(20 rows)
+
+SELECT four, ten,
+ sum(ten) over (partition by four order by ten range between unbounded preceding and unbounded following),
+ last_value(ten) over (partition by four order by ten range between unbounded preceding and unbounded following)
+FROM (select distinct ten, four from tenk1) ss;
+ four | ten | sum | last_value
+------+-----+-----+------------
+ 0 | 0 | 20 | 8
+ 0 | 2 | 20 | 8
+ 0 | 4 | 20 | 8
+ 0 | 6 | 20 | 8
+ 0 | 8 | 20 | 8
+ 1 | 1 | 25 | 9
+ 1 | 3 | 25 | 9
+ 1 | 5 | 25 | 9
+ 1 | 7 | 25 | 9
+ 1 | 9 | 25 | 9
+ 2 | 0 | 20 | 8
+ 2 | 2 | 20 | 8
+ 2 | 4 | 20 | 8
+ 2 | 6 | 20 | 8
+ 2 | 8 | 20 | 8
+ 3 | 1 | 25 | 9
+ 3 | 3 | 25 | 9
+ 3 | 5 | 25 | 9
+ 3 | 7 | 25 | 9
+ 3 | 9 | 25 | 9
+(20 rows)
+
+SELECT four, ten/4 as two,
+ sum(ten/4) over (partition by four order by ten/4 range between unbounded preceding and current row),
+ last_value(ten/4) over (partition by four order by ten/4 range between unbounded preceding and current row)
+FROM (select distinct ten, four from tenk1) ss;
+ four | two | sum | last_value
+------+-----+-----+------------
+ 0 | 0 | 0 | 0
+ 0 | 0 | 0 | 0
+ 0 | 1 | 2 | 1
+ 0 | 1 | 2 | 1
+ 0 | 2 | 4 | 2
+ 1 | 0 | 0 | 0
+ 1 | 0 | 0 | 0
+ 1 | 1 | 2 | 1
+ 1 | 1 | 2 | 1
+ 1 | 2 | 4 | 2
+ 2 | 0 | 0 | 0
+ 2 | 0 | 0 | 0
+ 2 | 1 | 2 | 1
+ 2 | 1 | 2 | 1
+ 2 | 2 | 4 | 2
+ 3 | 0 | 0 | 0
+ 3 | 0 | 0 | 0
+ 3 | 1 | 2 | 1
+ 3 | 1 | 2 | 1
+ 3 | 2 | 4 | 2
+(20 rows)
+
+SELECT four, ten/4 as two,
+ sum(ten/4) over (partition by four order by ten/4 rows between unbounded preceding and current row),
+ last_value(ten/4) over (partition by four order by ten/4 rows between unbounded preceding and current row)
+FROM (select distinct ten, four from tenk1) ss;
+ four | two | sum | last_value
+------+-----+-----+------------
+ 0 | 0 | 0 | 0
+ 0 | 0 | 0 | 0
+ 0 | 1 | 1 | 1
+ 0 | 1 | 2 | 1
+ 0 | 2 | 4 | 2
+ 1 | 0 | 0 | 0
+ 1 | 0 | 0 | 0
+ 1 | 1 | 1 | 1
+ 1 | 1 | 2 | 1
+ 1 | 2 | 4 | 2
+ 2 | 0 | 0 | 0
+ 2 | 0 | 0 | 0
+ 2 | 1 | 1 | 1
+ 2 | 1 | 2 | 1
+ 2 | 2 | 4 | 2
+ 3 | 0 | 0 | 0
+ 3 | 0 | 0 | 0
+ 3 | 1 | 1 | 1
+ 3 | 1 | 2 | 1
+ 3 | 2 | 4 | 2
+(20 rows)
+
-- with UNION
SELECT count(*) OVER (PARTITION BY four) FROM (SELECT * FROM tenk1 UNION ALL SELECT * FROM tenk2)s LIMIT 0;
count
-------
(0 rows)
--- via a VIEW
-CREATE TEMPORARY VIEW vsumsalary AS
-SELECT SUM(salary) OVER (PARTITION BY depname) FROM empsalary;
-SELECT * FROM vsumsalary;
- sum
--------
- 25100
- 25100
- 25100
- 25100
- 25100
- 7400
- 7400
- 14600
- 14600
- 14600
-(10 rows)
-
-- ordering by a non-integer constant is allowed
SELECT rank() OVER (ORDER BY length('abc'));
rank
@@ -669,5 +792,4 @@ ERROR: argument of ntile must be greater than zero
SELECT nth_value(four, 0) OVER (ORDER BY ten), ten, four FROM tenk1;
ERROR: argument of nth_value must be greater than zero
-- cleanup
-DROP VIEW vsumsalary;
DROP TABLE empsalary;
diff --git a/src/test/regress/sql/window.sql b/src/test/regress/sql/window.sql
index 82f8624e450..5ecb3e3e656 100644
--- a/src/test/regress/sql/window.sql
+++ b/src/test/regress/sql/window.sql
@@ -135,14 +135,35 @@ SELECT empno, depname, salary, bonus, depadj, MIN(bonus) OVER (ORDER BY empno),
THEN 200 END AS depadj FROM empsalary
)s;
+-- test non-default frame specifications
+SELECT four, ten,
+ sum(ten) over (partition by four order by ten),
+ last_value(ten) over (partition by four order by ten)
+FROM (select distinct ten, four from tenk1) ss;
+
+SELECT four, ten,
+ sum(ten) over (partition by four order by ten range between unbounded preceding and current row),
+ last_value(ten) over (partition by four order by ten range between unbounded preceding and current row)
+FROM (select distinct ten, four from tenk1) ss;
+
+SELECT four, ten,
+ sum(ten) over (partition by four order by ten range between unbounded preceding and unbounded following),
+ last_value(ten) over (partition by four order by ten range between unbounded preceding and unbounded following)
+FROM (select distinct ten, four from tenk1) ss;
+
+SELECT four, ten/4 as two,
+ sum(ten/4) over (partition by four order by ten/4 range between unbounded preceding and current row),
+ last_value(ten/4) over (partition by four order by ten/4 range between unbounded preceding and current row)
+FROM (select distinct ten, four from tenk1) ss;
+
+SELECT four, ten/4 as two,
+ sum(ten/4) over (partition by four order by ten/4 rows between unbounded preceding and current row),
+ last_value(ten/4) over (partition by four order by ten/4 rows between unbounded preceding and current row)
+FROM (select distinct ten, four from tenk1) ss;
+
-- with UNION
SELECT count(*) OVER (PARTITION BY four) FROM (SELECT * FROM tenk1 UNION ALL SELECT * FROM tenk2)s LIMIT 0;
--- via a VIEW
-CREATE TEMPORARY VIEW vsumsalary AS
-SELECT SUM(salary) OVER (PARTITION BY depname) FROM empsalary;
-SELECT * FROM vsumsalary;
-
-- ordering by a non-integer constant is allowed
SELECT rank() OVER (ORDER BY length('abc'));
@@ -175,5 +196,4 @@ SELECT ntile(0) OVER (ORDER BY ten), ten, four FROM tenk1;
SELECT nth_value(four, 0) OVER (ORDER BY ten), ten, four FROM tenk1;
-- cleanup
-DROP VIEW vsumsalary;
DROP TABLE empsalary;
generated by cgit v1.2.3 (git 2.25.1) at 2025-05-30 20:06:04 +0000