*** a/src/backend/executor/nodeWindowAgg.c --- b/src/backend/executor/nodeWindowAgg.c *************** *** 164,170 **** 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, --- 164,171 ---- static void release_partition(WindowAggState *winstate); static bool row_is_in_frame(WindowAggState *winstate, int64 pos, ! TupleTableSlot *slot); ! static void update_frameheadpos(WindowObject winobj, TupleTableSlot *slot); static void update_frametailpos(WindowObject winobj, TupleTableSlot *slot); static WindowStatePerAggData *initialize_peragg(WindowAggState *winstate, *************** *** 391,396 **** eval_windowaggregates(WindowAggState *winstate) --- 392,398 ---- MemoryContext oldContext; ExprContext *econtext; TupleTableSlot *agg_row_slot; + WindowObject agg_winobj; numaggs = winstate->numaggs; if (numaggs == 0) *************** *** 398,405 **** eval_windowaggregates(WindowAggState *winstate) --- 400,410 ---- /* final output execution is in ps_ExprContext */ econtext = winstate->ss.ps.ps_ExprContext; + agg_winobj = winstate->agg_winobj; + agg_row_slot = winstate->agg_row_slot; /* + * XXX: * 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 *************** *** 434,466 **** eval_windowaggregates(WindowAggState *winstate) */ /* ! * If we've already aggregated up through current row, reuse the 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) { for (i = 0; i < numaggs; i++) { peraggstate = &winstate->peragg[i]; wfuncno = peraggstate->wfuncno; ! econtext->ecxt_aggvalues[wfuncno] = peraggstate->resultValue; ! econtext->ecxt_aggnulls[wfuncno] = peraggstate->resultValueIsNull; } - return; } ! /* Initialize aggregates on first call for partition */ ! if (winstate->currentpos == 0) { for (i = 0; i < numaggs; i++) { peraggstate = &winstate->peragg[i]; wfuncno = peraggstate->wfuncno; ! initialize_windowaggregate(winstate, ! &winstate->perfunc[wfuncno], ! peraggstate); } } /* --- 439,508 ---- */ /* ! * First, update frame positions. Aggregate framing detection is similar ! * to one in window function APIs, but it optimizes it's own flow to ! * detect frame head and tail, which finds the tail during aggregation ! * rather than before starting. This results in tuplestore buffering ! * be minimized in such simple frame like RANGE BETWEEN UNBOUNDED PRECEDING ! * AND CURRENT ROW (ie default when specifying only ORDER BY clause.) ! */ ! update_frameheadpos(agg_winobj, winstate->agg_row_slot); ! ! /* ! * Initialize aggregates on first call for partition or when some rows ! * were off from frame since the last call. */ ! if (winstate->currentpos == 0 || ! winstate->frameheadpos > winstate->aggregatedbase) { for (i = 0; i < numaggs; i++) { peraggstate = &winstate->peragg[i]; wfuncno = peraggstate->wfuncno; ! initialize_windowaggregate(winstate, ! &winstate->perfunc[wfuncno], ! peraggstate); ! } ! ! winstate->aggregatedbase = winstate->frameheadpos; ! ! /* ! * When frame head is advancing, aggregate mark position should be ! * updated so that tuplestore can discard unnecessary rows. ! * When the condition is false (i.e. FRAMEOPTION_START_UNBOUNDED_PRECEDING), ! * mark_ptr is invalid and we don't care about mark position. ! */ ! if (agg_winobj->markptr > 0) ! { ! if (winstate->frameheadpos < winstate->currentpos) ! WinSetMarkPosition(agg_winobj, winstate->frameheadpos - 1); ! ! winstate->aggregatedupto = winstate->aggregatedbase; ! /* ! * Discard current state, so that subsequent loop starts with null slot ! */ ! if (!TupIsNull(agg_row_slot)) ! ExecClearTuple(agg_row_slot); } } ! /* ! * If we've already aggregated up through current row and frame covers ! * current row, reuse the saved result values. NOTE: this test should get ! * more efficient in some framing modes. ! */ ! if (winstate->frametail_stable && ! winstate->aggregatedbase <= winstate->currentpos && ! winstate->aggregatedupto > winstate->currentpos) { for (i = 0; i < numaggs; i++) { peraggstate = &winstate->peragg[i]; wfuncno = peraggstate->wfuncno; ! econtext->ecxt_aggvalues[wfuncno] = peraggstate->resultValue; ! econtext->ecxt_aggnulls[wfuncno] = peraggstate->resultValueIsNull; } + return; } /* *************** *** 470,486 **** eval_windowaggregates(WindowAggState *winstate) * at position aggregatedupto. The agg_ptr read pointer must always point * to the next row to read into agg_row_slot. */ - agg_row_slot = winstate->agg_row_slot; for (;;) { /* Fetch next row if we didn't already */ if (TupIsNull(agg_row_slot)) { ! spool_tuples(winstate, winstate->aggregatedupto); ! tuplestore_select_read_pointer(winstate->buffer, ! winstate->agg_ptr); ! if (!tuplestore_gettupleslot(winstate->buffer, true, true, ! agg_row_slot)) break; /* must be end of partition */ } --- 512,530 ---- * at position aggregatedupto. The agg_ptr read pointer must always point * to the next row to read into agg_row_slot. */ for (;;) { /* Fetch next row if we didn't already */ if (TupIsNull(agg_row_slot)) { ! /* ! * NOTE: ! * Here window_gettupleslot() tends to fetch current row ! * (but not always). We should optimize window_gettupleslot() ! * on this case, where reads ahead and back currently. ! */ ! if(!window_gettupleslot(agg_winobj, winstate->aggregatedupto, ! agg_row_slot)) break; /* must be end of partition */ } *************** *** 627,634 **** begin_partition(WindowAggState *winstate) winstate->frametail_valid = false; winstate->spooled_rows = 0; winstate->currentpos = 0; winstate->frametailpos = -1; ! winstate->aggregatedupto = 0; ExecClearTuple(winstate->agg_row_slot); /* --- 671,679 ---- winstate->frametail_valid = false; winstate->spooled_rows = 0; winstate->currentpos = 0; + winstate->frameheadpos = -1; winstate->frametailpos = -1; ! ExecClearTuple(winstate->agg_row_slot); /* *************** *** 665,671 **** begin_partition(WindowAggState *winstate) /* create a read pointer for aggregates, if needed */ if (winstate->numaggs > 0) ! winstate->agg_ptr = tuplestore_alloc_read_pointer(winstate->buffer, 0); /* create mark and read pointers for each real window function */ for (i = 0; i < numfuncs; i++) --- 710,738 ---- /* create a read pointer for aggregates, if needed */ if (winstate->numaggs > 0) ! { ! WindowObject agg_winobj = winstate->agg_winobj; ! int readptr_flag = 0; ! WindowAgg *node = (WindowAgg *) winstate->ss.ps.plan; ! int frameOptions = node->frameOptions; ! ! /* create a read pointer for aggregate marking if the frame head may move */ ! if (!(frameOptions & FRAMEOPTION_START_UNBOUNDED_PRECEDING)) ! { ! agg_winobj->markptr = tuplestore_alloc_read_pointer(winstate->buffer, 0); ! readptr_flag = EXEC_FLAG_BACKWARD; ! // winstate->agg_base_ptr = tuplestore_alloc_read_pointer(winstate->buffer, 0); ! } ! ! agg_winobj->readptr = tuplestore_alloc_read_pointer(winstate->buffer, ! readptr_flag); ! // winstate->agg_ptr = tuplestore_alloc_read_pointer(winstate->buffer, 0); ! ! agg_winobj->markpos = -1; ! agg_winobj->seekpos = -1; ! winstate->aggregatedbase = 0; ! winstate->aggregatedupto = 0; ! } /* create mark and read pointers for each real window function */ for (i = 0; i < numfuncs; i++) *************** *** 814,842 **** row_is_in_frame(WindowAggState *winstate, int64 pos, TupleTableSlot *slot) 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); } /* --- 881,1073 ---- Assert(pos >= 0); /* else caller error */ ! if (frameOptions & FRAMEOPTION_START_CURRENT_ROW) ! { ! if (frameOptions & FRAMEOPTION_ROWS) ! { ! if (pos < winstate->currentpos) ! return false; ! } ! else ! { ! Assert(frameOptions & FRAMEOPTION_RANGE); ! ! /* preceding row that is not peer is out of frame */ ! if (pos < winstate->currentpos && ! !are_peers(winstate, slot, winstate->ss.ss_ScanTupleSlot)) ! return false; ! } ! } ! ! if (frameOptions & FRAMEOPTION_START_VALUE_PRECEDING) ! { ! if (frameOptions & FRAMEOPTION_ROWS) ! { ! A_Const *offset = (A_Const *) node->startOffset; ! ! Assert(offset != NULL && IsA(&(offset->val), Integer)); ! if (pos < winstate->currentpos - intVal(&(offset->val))) ! return false; ! } ! else ! elog(ERROR, "RANGE BETWEEN n PRECEDING AND ... is not supported so far"); ! } ! ! if (frameOptions & FRAMEOPTION_START_VALUE_FOLLOWING) ! { ! if (frameOptions & FRAMEOPTION_ROWS) ! { ! A_Const *offset = (A_Const *) node->startOffset; ! ! Assert(offset != NULL && IsA(&(offset->val), Integer)); ! if (pos < winstate->currentpos + intVal(&(offset->val))) ! return false; ! } ! else ! elog(ERROR, "RANGE BETWEEN n FOLLOWING AND ... is not supported so far"); ! } ! ! /* seems ok for starting bound */ /* In UNBOUNDED FOLLOWING mode, all partition rows are in frame */ if (frameOptions & FRAMEOPTION_END_UNBOUNDED_FOLLOWING) return true; ! if (frameOptions & FRAMEOPTION_END_CURRENT_ROW) ! { ! if (frameOptions & FRAMEOPTION_ROWS) ! { ! return (pos <= winstate->currentpos); ! } ! else ! { ! Assert(frameOptions & FRAMEOPTION_RANGE); ! /* preceding row or peer is in frame */ ! return pos <= winstate->currentpos || ! are_peers(winstate, slot, winstate->ss.ss_ScanTupleSlot); ! } ! } ! if (frameOptions & FRAMEOPTION_END_VALUE_PRECEDING) ! { ! if (frameOptions & FRAMEOPTION_ROWS) ! { ! A_Const *offset = (A_Const *) node->endOffset; ! ! Assert(offset != NULL && IsA(&(offset->val), Integer)); ! return (pos <= winstate->currentpos - intVal(&(offset->val))); ! } ! else ! elog(ERROR, "RANGE BETWEEN ... AND n PRECEDING is not supported so far"); ! } ! ! if (frameOptions & FRAMEOPTION_END_VALUE_FOLLOWING) ! { ! if (frameOptions & FRAMEOPTION_ROWS) ! { ! A_Const *offset = (A_Const *) node->endOffset; ! ! Assert(offset != NULL && IsA(&(offset->val), Integer)); ! return (pos <= winstate->currentpos + intVal(&(offset->val))); ! } ! else ! elog(ERROR, "RANGE BETWEEN ... AND n FOLLOWING is not supported so far"); ! } ! ! /* It is not supposed to reach here */ ! elog(ERROR, "invalid frame type"); ! ! return false; /* keep compiler quiet */ ! } ! ! static void ! update_frameheadpos(WindowObject winobj, TupleTableSlot *slot) ! { ! WindowAggState *winstate = winobj->winstate; ! WindowAgg *node = (WindowAgg *) winstate->ss.ps.plan; ! int frameOptions = node->frameOptions; ! ! if (winstate->framehead_valid) ! return; ! ! if (frameOptions & FRAMEOPTION_START_UNBOUNDED_PRECEDING) ! { ! winstate->frameheadpos = 0; ! winstate->framehead_valid = true; ! return; ! } ! if (frameOptions & FRAMEOPTION_START_CURRENT_ROW) ! { ! int64 pos = winstate->currentpos; ! if (frameOptions & FRAMEOPTION_ROWS) ! { ! winstate->frameheadpos = pos; ! winstate->framehead_valid = true; ! return; ! } ! Assert(frameOptions & FRAMEOPTION_RANGE); ! ! if (node->ordNumCols == 0) ! { ! winstate->frameheadpos = 0; ! winstate->framehead_valid = true; ! return; ! } ! /* ! * In RANGE START_CURRENT mode, frame head is ! * the head row of all peers. ! */ ! for(;;) ! { ! if (!window_gettupleslot(winobj, pos, slot)) ! break; ! if (!are_peers(winstate, slot, ! winstate->ss.ss_ScanTupleSlot)) ! break; ! pos--; ! } ! winstate->frameheadpos = pos + 1; ! winstate->framehead_valid = true; ! return; ! } ! ! if (frameOptions & FRAMEOPTION_START_VALUE_PRECEDING) ! { ! if (frameOptions & FRAMEOPTION_ROWS) ! { ! A_Const *offset = (A_Const *) node->startOffset; ! ! Assert(offset != NULL && IsA(&(offset->val), Integer)); ! winstate->frameheadpos = winstate->currentpos - intVal(&(offset->val)); ! if (winstate->frameheadpos < 0) ! winstate->frameheadpos = 0; ! winstate->framehead_valid = true; ! return; ! } ! elog(ERROR, "RANGE BETWEEN n PRECEDING AND ... is not supported so far"); ! } ! ! if (frameOptions & FRAMEOPTION_START_VALUE_FOLLOWING) ! { ! if (frameOptions & FRAMEOPTION_ROWS) ! { ! A_Const *offset = (A_Const *) node->startOffset; ! ! Assert(offset != NULL && IsA(&(offset->val), Integer)); ! winstate->frameheadpos = winstate->currentpos + intVal(&(offset->val)); ! ! /* we have to know end of the partition to cut off overflow */ ! spool_tuples(winstate, -1); ! if (winstate->frameheadpos >= winstate->spooled_rows) ! winstate->frameheadpos = winstate->spooled_rows - 1; ! winstate->framehead_valid = true; ! return; ! } ! elog(ERROR, "RANGE BETWEEN n FOLLOWING AND ... is not supported so far"); ! } } /* *************** *** 858,866 **** update_frametailpos(WindowObject winobj, TupleTableSlot *slot) 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) { --- 1089,1094 ---- *************** *** 870,916 **** update_frametailpos(WindowObject winobj, TupleTableSlot *slot) 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; } --- 1098,1185 ---- return; } ! if (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 ! { ! /* 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; ! return; ! } ! } ! ! if (frameOptions & FRAMEOPTION_END_VALUE_PRECEDING) { ! /* In ROWS mode, frame boundary phisically n before current */ ! if (frameOptions & FRAMEOPTION_ROWS) ! { ! A_Const *offset = (A_Const *) node->endOffset; ! ! Assert(offset != NULL && IsA(&(offset->val), Integer)); ! winstate->frametailpos = winstate->currentpos - intVal(&(offset->val)); ! if(winstate->frametailpos < 0) ! winstate->frametailpos = 0; ! winstate->frametail_valid = true; ! return; ! } ! else ! elog(ERROR, "RANGE BETWEEN ... AND n PRECEDING is not supported for now"); } ! if (frameOptions & FRAMEOPTION_END_VALUE_FOLLOWING) { ! if (frameOptions & FRAMEOPTION_ROWS) ! { ! A_Const *offset = (A_Const *) node->endOffset; ! ! Assert(offset != NULL && IsA(&(offset->val), Integer)); ! winstate->frametailpos = winstate->currentpos + intVal(&(offset->val)); ! ! /* we have to know end of the partition to cut off overflow */ ! spool_tuples(winstate, -1); ! if (winstate->frametailpos >= winstate->spooled_rows) ! winstate->frametailpos = winstate->spooled_rows - 1; ! winstate->frametail_valid = true; ! return; ! } ! else ! elog(ERROR, "RANGE BETWEEN ... AND n FOLLOWING is not supported for now"); } } *************** *** 964,969 **** restart: --- 1233,1239 ---- { /* Advance current row within partition */ winstate->currentpos++; + winstate->framehead_valid = false; /* This might mean that the frame tail moves, too */ winstate->frametail_valid = false; } *************** *** 1264,1272 **** ExecInitWindowAgg(WindowAgg *node, EState *estate, int eflags) --- 1534,1558 ---- winstate->numfuncs = wfuncno + 1; winstate->numaggs = aggno + 1; + if (winstate->numaggs > 0) + { + WindowObject agg_winobj = makeNode(WindowObjectData); + + agg_winobj->winstate = winstate; + agg_winobj->argstates = NULL; + agg_winobj->localmem = NULL; + /* make sure markptr = -1 to invalidate. it may not be used */ + agg_winobj->markptr = -1; + agg_winobj->readptr = -1; + winstate->agg_winobj = agg_winobj; + } + winstate->partition_spooled = false; winstate->more_partitions = false; + winstate->frametail_stable = !(node->frameOptions & + (FRAMEOPTION_END_VALUE_PRECEDING | + FRAMEOPTION_END_VALUE_FOLLOWING)); return winstate; } *************** *** 1749,1754 **** WinGetFuncArgInPartition(WindowObject winobj, int argno, --- 2035,2042 ---- bool *isnull, bool *isout) { WindowAggState *winstate; + WindowAgg *node; + int frameOptions; ExprContext *econtext; TupleTableSlot *slot; bool gottuple; *************** *** 1756,1761 **** WinGetFuncArgInPartition(WindowObject winobj, int argno, --- 2044,2051 ---- Assert(WindowObjectIsValid(winobj)); winstate = winobj->winstate; + node = (WindowAgg *) winstate->ss.ps.plan; + frameOptions = node->frameOptions; econtext = winstate->ss.ps.ps_ExprContext; slot = winstate->temp_slot_1; *************** *** 1791,1797 **** WinGetFuncArgInPartition(WindowObject winobj, int argno, if (isout) *isout = false; if (set_mark) ! WinSetMarkPosition(winobj, abs_pos); econtext->ecxt_outertuple = slot; return ExecEvalExpr((ExprState *) list_nth(winobj->argstates, argno), econtext, isnull, NULL); --- 2081,2103 ---- if (isout) *isout = false; if (set_mark) ! { ! int64 mark_pos = abs_pos; ! ! /* ! * In moving RANGE frame mode, we must keep mark at ! * frameheadpos - 1, since that row is to be checked ! * to find frame starting boundary. ! */ ! if (!(frameOptions & FRAMEOPTION_START_UNBOUNDED_PRECEDING) && ! frameOptions & FRAMEOPTION_RANGE) ! { ! update_frameheadpos(winobj, winstate->temp_slot_2); ! if (mark_pos >= winstate->frameheadpos) ! mark_pos = winstate->frameheadpos - 1; ! } ! WinSetMarkPosition(winobj, mark_pos); ! } econtext->ecxt_outertuple = slot; return ExecEvalExpr((ExprState *) list_nth(winobj->argstates, argno), econtext, isnull, NULL); *************** *** 1822,1827 **** WinGetFuncArgInFrame(WindowObject winobj, int argno, --- 2128,2135 ---- bool *isnull, bool *isout) { WindowAggState *winstate; + WindowAgg *node; + int frameOptions; ExprContext *econtext; TupleTableSlot *slot; bool gottuple; *************** *** 1829,1834 **** WinGetFuncArgInFrame(WindowObject winobj, int argno, --- 2137,2144 ---- Assert(WindowObjectIsValid(winobj)); winstate = winobj->winstate; + node = (WindowAgg *) winstate->ss.ps.plan; + frameOptions = node->frameOptions; econtext = winstate->ss.ps.ps_ExprContext; slot = winstate->temp_slot_1; *************** *** 1838,1844 **** WinGetFuncArgInFrame(WindowObject winobj, int argno, abs_pos = winstate->currentpos + relpos; break; case WINDOW_SEEK_HEAD: ! abs_pos = relpos; break; case WINDOW_SEEK_TAIL: update_frametailpos(winobj, slot); --- 2148,2155 ---- abs_pos = winstate->currentpos + relpos; break; case WINDOW_SEEK_HEAD: ! update_frameheadpos(winobj, slot); ! abs_pos = winstate->frameheadpos + relpos; break; case WINDOW_SEEK_TAIL: update_frametailpos(winobj, slot); *************** *** 1865,1872 **** WinGetFuncArgInFrame(WindowObject winobj, int argno, { if (isout) *isout = false; if (set_mark) ! WinSetMarkPosition(winobj, abs_pos); econtext->ecxt_outertuple = slot; return ExecEvalExpr((ExprState *) list_nth(winobj->argstates, argno), econtext, isnull, NULL); --- 2176,2200 ---- { if (isout) *isout = false; + if (set_mark) ! { ! int64 mark_pos = abs_pos; ! ! /* ! * In moving RANGE frame mode, we must keep mark at ! * frameheadpos - 1, since that row is to be checked ! * to find frame starting boundary. ! */ ! if (!(frameOptions & FRAMEOPTION_START_UNBOUNDED_PRECEDING) && ! frameOptions & FRAMEOPTION_RANGE) ! { ! update_frameheadpos(winobj, winstate->temp_slot_2); ! if (mark_pos >= winstate->frameheadpos) ! mark_pos = winstate->frameheadpos - 1; ! } ! WinSetMarkPosition(winobj, mark_pos); ! } econtext->ecxt_outertuple = slot; return ExecEvalExpr((ExprState *) list_nth(winobj->argstates, argno), econtext, isnull, NULL); *** a/src/backend/nodes/copyfuncs.c --- b/src/backend/nodes/copyfuncs.c *************** *** 718,723 **** _copyWindowAgg(WindowAgg *from) --- 718,725 ---- COPY_POINTER_FIELD(ordOperators, from->ordNumCols * sizeof(Oid)); } COPY_SCALAR_FIELD(frameOptions); + COPY_NODE_FIELD(startOffset); + COPY_NODE_FIELD(endOffset); return newnode; } *************** *** 1844,1850 **** _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); --- 1846,1852 ---- COPY_STRING_FIELD(refname); COPY_NODE_FIELD(partitionClause); COPY_NODE_FIELD(orderClause); ! COPY_NODE_FIELD(frame); COPY_SCALAR_FIELD(winref); COPY_SCALAR_FIELD(copiedOrder); *************** *** 2062,2067 **** _copySortBy(SortBy *from) --- 2064,2082 ---- return newnode; } + static WindowFrameDef * + _copyWindowFrameDef(WindowFrameDef *from) + { + WindowFrameDef *newnode = makeNode(WindowFrameDef); + + COPY_SCALAR_FIELD(options); + COPY_NODE_FIELD(startOffset); + COPY_NODE_FIELD(endOffset); + COPY_LOCATION_FIELD(location); + + return newnode; + } + static WindowDef * _copyWindowDef(WindowDef *from) { *************** *** 2071,2077 **** _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; --- 2086,2092 ---- COPY_STRING_FIELD(refname); COPY_NODE_FIELD(partitionClause); COPY_NODE_FIELD(orderClause); ! COPY_NODE_FIELD(frame); COPY_LOCATION_FIELD(location); return newnode; *************** *** 4154,4159 **** copyObject(void *from) --- 4169,4177 ---- case T_SortBy: retval = _copySortBy(from); break; + case T_WindowFrameDef: + retval = _copyWindowFrameDef(from); + break; case T_WindowDef: retval = _copyWindowDef(from); break; *** a/src/backend/nodes/equalfuncs.c --- b/src/backend/nodes/equalfuncs.c *************** *** 2035,2047 **** _equalSortBy(SortBy *a, SortBy *b) } static bool _equalWindowDef(WindowDef *a, WindowDef *b) { COMPARE_STRING_FIELD(name); COMPARE_STRING_FIELD(refname); COMPARE_NODE_FIELD(partitionClause); COMPARE_NODE_FIELD(orderClause); ! COMPARE_SCALAR_FIELD(frameOptions); COMPARE_LOCATION_FIELD(location); return true; --- 2035,2057 ---- } static bool + _equalWindowFrameDef(WindowFrameDef *a, WindowFrameDef *b) + { + COMPARE_SCALAR_FIELD(options); + COMPARE_NODE_FIELD(startOffset); + COMPARE_NODE_FIELD(endOffset); + + return true; + } + + static bool _equalWindowDef(WindowDef *a, WindowDef *b) { COMPARE_STRING_FIELD(name); COMPARE_STRING_FIELD(refname); COMPARE_NODE_FIELD(partitionClause); COMPARE_NODE_FIELD(orderClause); ! COMPARE_NODE_FIELD(frame); COMPARE_LOCATION_FIELD(location); return true; *************** *** 2186,2192 **** _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); --- 2196,2202 ---- COMPARE_STRING_FIELD(refname); COMPARE_NODE_FIELD(partitionClause); COMPARE_NODE_FIELD(orderClause); ! COMPARE_NODE_FIELD(frame); COMPARE_SCALAR_FIELD(winref); COMPARE_SCALAR_FIELD(copiedOrder); *************** *** 2847,2852 **** equal(void *a, void *b) --- 2857,2865 ---- case T_SortBy: retval = _equalSortBy(a, b); break; + case T_WindowFrameDef: + retval = _equalWindowFrameDef(a, b); + break; case T_WindowDef: retval = _equalWindowDef(a, b); break; *** a/src/backend/nodes/outfuncs.c --- b/src/backend/nodes/outfuncs.c *************** *** 610,615 **** _outWindowAgg(StringInfo str, WindowAgg *node) --- 610,617 ---- appendStringInfo(str, " %u", node->ordOperators[i]); WRITE_INT_FIELD(frameOptions); + WRITE_NODE_FIELD(startOffset); + WRITE_NODE_FIELD(endOffset); } static void *************** *** 2024,2030 **** _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); } --- 2026,2032 ---- WRITE_STRING_FIELD(refname); WRITE_NODE_FIELD(partitionClause); WRITE_NODE_FIELD(orderClause); ! WRITE_NODE_FIELD(frame); WRITE_UINT_FIELD(winref); WRITE_BOOL_FIELD(copiedOrder); } *************** *** 2307,2312 **** _outSortBy(StringInfo str, SortBy *node) --- 2309,2324 ---- } static void + _outWindowFrameDef(StringInfo str, WindowFrameDef *node) + { + WRITE_NODE_TYPE("WINDOWFRAMEDEF"); + + WRITE_INT_FIELD(options); + WRITE_NODE_FIELD(startOffset); + WRITE_NODE_FIELD(endOffset); + } + + static void _outWindowDef(StringInfo str, WindowDef *node) { WRITE_NODE_TYPE("WINDOWDEF"); *************** *** 2315,2321 **** _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); } --- 2327,2333 ---- WRITE_STRING_FIELD(refname); WRITE_NODE_FIELD(partitionClause); WRITE_NODE_FIELD(orderClause); ! WRITE_NODE_FIELD(frame); WRITE_LOCATION_FIELD(location); } *************** *** 2848,2853 **** _outNode(StringInfo str, void *obj) --- 2860,2868 ---- case T_SortBy: _outSortBy(str, obj); break; + case T_WindowFrameDef: + _outWindowFrameDef(str, obj); + break; case T_WindowDef: _outWindowDef(str, obj); break; *** a/src/backend/nodes/readfuncs.c --- b/src/backend/nodes/readfuncs.c *************** *** 278,284 **** _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); --- 278,284 ---- READ_STRING_FIELD(refname); READ_NODE_FIELD(partitionClause); READ_NODE_FIELD(orderClause); ! READ_NODE_FIELD(frame); READ_UINT_FIELD(winref); READ_BOOL_FIELD(copiedOrder); *** a/src/backend/optimizer/plan/createplan.c --- b/src/backend/optimizer/plan/createplan.c *************** *** 3360,3366 **** make_windowagg(PlannerInfo *root, List *tlist, int numWindowFuncs, Index winref, int partNumCols, AttrNumber *partColIdx, Oid *partOperators, int ordNumCols, AttrNumber *ordColIdx, Oid *ordOperators, ! int frameOptions, Plan *lefttree) { WindowAgg *node = makeNode(WindowAgg); Plan *plan = &node->plan; --- 3360,3367 ---- int numWindowFuncs, Index winref, int partNumCols, AttrNumber *partColIdx, Oid *partOperators, int ordNumCols, AttrNumber *ordColIdx, Oid *ordOperators, ! int frameOptions, Node *startOffset, Node *endOffset, ! Plan *lefttree) { WindowAgg *node = makeNode(WindowAgg); Plan *plan = &node->plan; *************** *** 3375,3380 **** make_windowagg(PlannerInfo *root, List *tlist, --- 3376,3383 ---- node->ordColIdx = ordColIdx; node->ordOperators = ordOperators; node->frameOptions = frameOptions; + node->startOffset = startOffset; + node->endOffset = endOffset; copy_plan_costsize(plan, lefttree); /* only care about copying size */ cost_windowagg(&windowagg_path, root, *** a/src/backend/optimizer/plan/planner.c --- b/src/backend/optimizer/plan/planner.c *************** *** 1484,1490 **** grouping_planner(PlannerInfo *root, double tuple_fraction) result_plan = (Plan *) make_windowagg(root, (List *) copyObject(window_tlist), ! list_length(wflists->windowFuncs[wc->winref]), wc->winref, partNumCols, partColIdx, --- 1484,1490 ---- result_plan = (Plan *) make_windowagg(root, (List *) copyObject(window_tlist), ! list_length(wflists->windowFuncs[wc->winref]), wc->winref, partNumCols, partColIdx, *************** *** 1492,1498 **** grouping_planner(PlannerInfo *root, double tuple_fraction) ordNumCols, ordColIdx, ordOperators, ! wc->frameOptions, result_plan); } } --- 1492,1500 ---- ordNumCols, ordColIdx, ordOperators, ! wc->frame->options, ! wc->frame->startOffset, ! wc->frame->endOffset, result_plan); } } *** a/src/backend/parser/gram.y --- b/src/backend/parser/gram.y *************** *** 428,434 **** static TypeName *TableFuncTypeName(List *columns); %type window_clause window_definition_list opt_partition_clause %type window_definition over_clause window_specification %type opt_existing_window_name ! %type opt_frame_clause frame_extent frame_bound /* --- 428,434 ---- %type window_clause window_definition_list opt_partition_clause %type window_definition over_clause window_specification %type opt_existing_window_name ! %type opt_frame_clause frame_extent frame_bound /* *************** *** 9687,9697 **** over_clause: OVER window_specification | OVER ColId { WindowDef *n = makeNode(WindowDef); n->name = $2; n->refname = NULL; n->partitionClause = NIL; n->orderClause = NIL; ! n->frameOptions = FRAMEOPTION_DEFAULTS; n->location = @2; $$ = n; } --- 9687,9701 ---- | OVER ColId { WindowDef *n = makeNode(WindowDef); + WindowFrameDef *frame = makeNode(WindowFrameDef); + frame->options = FRAMEOPTION_DEFAULTS; + frame->startOffset = NULL; + frame->endOffset = NULL; n->name = $2; n->refname = NULL; n->partitionClause = NIL; n->orderClause = NIL; ! n->frame = frame; n->location = @2; $$ = n; } *************** *** 9707,9713 **** window_specification: '(' opt_existing_window_name opt_partition_clause n->refname = $2; n->partitionClause = $3; n->orderClause = $4; ! n->frameOptions = $5; n->location = @1; $$ = n; } --- 9711,9717 ---- n->refname = $2; n->partitionClause = $3; n->orderClause = $4; ! n->frame = (WindowFrameDef *) $5; n->location = @1; $$ = n; } *************** *** 9739,9789 **** opt_partition_clause: PARTITION BY expr_list { $$ = $3; } 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"), parser_errposition(@1))); ! if ($1 & FRAMEOPTION_START_CURRENT_ROW) ! ereport(ERROR, ! (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), ! errmsg("frame start at CURRENT ROW is not implemented"), ! parser_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"), parser_errposition(@2))); ! if ($2 & FRAMEOPTION_START_CURRENT_ROW) ! ereport(ERROR, ! (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), ! errmsg("frame start at CURRENT ROW is not implemented"), ! parser_errposition(@2))); ! if ($4 & FRAMEOPTION_START_UNBOUNDED_PRECEDING) ereport(ERROR, (errcode(ERRCODE_WINDOWING_ERROR), errmsg("frame end cannot be UNBOUNDED PRECEDING"), parser_errposition(@4))); /* shift converts START_ options to END_ options */ ! $$ = FRAMEOPTION_BETWEEN | $2 | ($4 << 1); } ; --- 9743,9826 ---- opt_frame_clause: RANGE frame_extent { ! WindowFrameDef *frame = (WindowFrameDef *) $2; ! frame->options |= FRAMEOPTION_NONDEFAULT | FRAMEOPTION_RANGE; ! frame->location = @1; ! $$ = (Node *) frame; } | ROWS frame_extent { ! WindowFrameDef *frame = (WindowFrameDef *) $2; ! frame->options |= FRAMEOPTION_NONDEFAULT | FRAMEOPTION_ROWS; ! frame->location = @1; ! $$ = (Node *) frame; } | /*EMPTY*/ ! { ! WindowFrameDef *frame = makeNode(WindowFrameDef); ! frame->options = FRAMEOPTION_DEFAULTS; ! frame->startOffset = NULL; ! frame->endOffset = NULL; ! frame->location = -1; ! $$ = (Node *) frame; ! } ; frame_extent: frame_bound { + WindowFrameDef *frame = (WindowFrameDef *) $1; /* reject invalid cases */ ! if (frame->options & FRAMEOPTION_START_UNBOUNDED_FOLLOWING) ereport(ERROR, (errcode(ERRCODE_WINDOWING_ERROR), errmsg("frame start cannot be UNBOUNDED FOLLOWING"), parser_errposition(@1))); ! frame->options |= FRAMEOPTION_END_CURRENT_ROW; ! $$ = (Node *) frame; } | BETWEEN frame_bound AND frame_bound { + WindowFrameDef *frame1 = (WindowFrameDef *) $2; + WindowFrameDef *frame2 = (WindowFrameDef *) $4; /* reject invalid cases */ ! if (frame1->options & FRAMEOPTION_START_UNBOUNDED_FOLLOWING) ereport(ERROR, (errcode(ERRCODE_WINDOWING_ERROR), errmsg("frame start cannot be UNBOUNDED FOLLOWING"), parser_errposition(@2))); ! if (frame2->options & FRAMEOPTION_START_UNBOUNDED_PRECEDING) ereport(ERROR, (errcode(ERRCODE_WINDOWING_ERROR), errmsg("frame end cannot be UNBOUNDED PRECEDING"), parser_errposition(@4))); + if (frame1->options & FRAMEOPTION_START_CURRENT_ROW && + frame2->options & + (FRAMEOPTION_START_UNBOUNDED_PRECEDING | + FRAMEOPTION_START_VALUE_PRECEDING)) + ereport(ERROR, + (errcode(ERRCODE_WINDOWING_ERROR), + errmsg("frame from current row cannot have preceding rows"), + parser_errposition(@4))); + if (frame1->options & FRAMEOPTION_START_VALUE_FOLLOWING && + frame2->options & + (FRAMEOPTION_START_VALUE_PRECEDING | + FRAMEOPTION_START_CURRENT_ROW)) + ereport(ERROR, + (errcode(ERRCODE_WINDOWING_ERROR), + errmsg("frame from following row cannot have preceding rows"), + parser_errposition(@4))); + if (frame1->options & FRAMEOPTION_START_VALUE_FOLLOWING && + frame2->options & FRAMEOPTION_END_VALUE_PRECEDING) + ereport(ERROR, + (errcode(ERRCODE_WINDOWING_ERROR), + errmsg("frame cannot be BETWEEN x FOLLOWING AND y PRECEDING"), + parser_errposition(@1))); /* shift converts START_ options to END_ options */ ! frame1->options |= frame2->options << 1; ! frame1->endOffset = frame2->startOffset; ! /* frame2 is not necessary anymore */ ! pfree(frame2); ! $$ = (Node *) frame1; } ; *************** *** 9795,9809 **** frame_extent: frame_bound frame_bound: UNBOUNDED PRECEDING { ! $$ = FRAMEOPTION_START_UNBOUNDED_PRECEDING; } | UNBOUNDED FOLLOWING { ! $$ = FRAMEOPTION_START_UNBOUNDED_FOLLOWING; } | CURRENT_P ROW { ! $$ = FRAMEOPTION_START_CURRENT_ROW; } ; --- 9832,9870 ---- frame_bound: UNBOUNDED PRECEDING { ! WindowFrameDef *frame = makeNode(WindowFrameDef); ! frame->options = FRAMEOPTION_START_UNBOUNDED_PRECEDING; ! frame->startOffset = NULL; ! frame->endOffset = NULL; ! $$ = (Node *) frame; } | UNBOUNDED FOLLOWING { ! WindowFrameDef *frame = makeNode(WindowFrameDef); ! frame->options = FRAMEOPTION_START_UNBOUNDED_FOLLOWING; ! frame->startOffset = NULL; ! frame->endOffset = NULL; ! $$ = (Node *) frame; } | CURRENT_P ROW { ! WindowFrameDef *frame = makeNode(WindowFrameDef); ! frame->options = FRAMEOPTION_START_CURRENT_ROW; ! $$ = (Node *) frame; ! } ! | Iconst PRECEDING ! { ! WindowFrameDef *frame = makeNode(WindowFrameDef); ! frame->options = FRAMEOPTION_START_VALUE_PRECEDING; ! frame->startOffset = makeIntConst($1, @1); ! $$ = (Node *) frame; ! } ! | Iconst FOLLOWING ! { ! WindowFrameDef *frame = makeNode(WindowFrameDef); ! frame->options = FRAMEOPTION_START_VALUE_FOLLOWING; ! frame->startOffset = makeIntConst($1, @1); ! $$ = (Node *) frame; } ; *** a/src/backend/parser/parse_agg.c --- b/src/backend/parser/parse_agg.c *************** *** 136,142 **** transformWindowFuncCall(ParseState *pstate, WindowFunc *wfunc, Assert(windef->refname == NULL && windef->partitionClause == NIL && windef->orderClause == NIL && ! windef->frameOptions == FRAMEOPTION_DEFAULTS); foreach(lc, pstate->p_windowdefs) { --- 136,142 ---- Assert(windef->refname == NULL && windef->partitionClause == NIL && windef->orderClause == NIL && ! windef->frame->options == FRAMEOPTION_DEFAULTS); foreach(lc, pstate->p_windowdefs) { *************** *** 174,180 **** transformWindowFuncCall(ParseState *pstate, WindowFunc *wfunc, continue; if (equal(refwin->partitionClause, windef->partitionClause) && equal(refwin->orderClause, windef->orderClause) && ! refwin->frameOptions == windef->frameOptions) { /* found a duplicate window specification */ wfunc->winref = winref; --- 174,180 ---- continue; if (equal(refwin->partitionClause, windef->partitionClause) && equal(refwin->orderClause, windef->orderClause) && ! equal(refwin->frame, windef->frame)) { /* found a duplicate window specification */ wfunc->winref = winref; *** a/src/backend/parser/parse_clause.c --- b/src/backend/parser/parse_clause.c *************** *** 1657,1669 **** transformWindowDefinitions(ParseState *pstate, wc->orderClause = orderClause; wc->copiedOrder = false; } ! if (refwc && refwc->frameOptions != FRAMEOPTION_DEFAULTS) ereport(ERROR, (errcode(ERRCODE_WINDOWING_ERROR), errmsg("cannot override frame clause of window \"%s\"", windef->refname), parser_errposition(pstate, windef->location))); ! wc->frameOptions = windef->frameOptions; wc->winref = winref; result = lappend(result, wc); --- 1657,1669 ---- wc->orderClause = orderClause; wc->copiedOrder = false; } ! if (refwc && refwc->frame->options != FRAMEOPTION_DEFAULTS) ereport(ERROR, (errcode(ERRCODE_WINDOWING_ERROR), errmsg("cannot override frame clause of window \"%s\"", windef->refname), parser_errposition(pstate, windef->location))); ! wc->frame = copyObject(windef->frame); wc->winref = winref; result = lappend(result, wc); *** a/src/backend/utils/adt/ruleutils.c --- b/src/backend/utils/adt/ruleutils.c *************** *** 2985,2990 **** get_rule_windowspec(WindowClause *wc, List *targetList, --- 2985,2991 ---- { StringInfo buf = context->buf; bool needspace = false; + WindowFrameDef *frame = wc->frame; const char *sep; ListCell *l; *************** *** 3022,3051 **** get_rule_windowspec(WindowClause *wc, List *targetList, 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); --- 3023,3052 ---- needspace = true; } /* framing clause is never inherited, so print unless it's default */ ! if (frame->options & FRAMEOPTION_NONDEFAULT) { if (needspace) appendStringInfoChar(buf, ' '); ! if (frame->options & FRAMEOPTION_RANGE) appendStringInfoString(buf, "RANGE "); ! else if (frame->options & FRAMEOPTION_ROWS) appendStringInfoString(buf, "ROWS "); else Assert(false); ! if (frame->options & FRAMEOPTION_BETWEEN) appendStringInfoString(buf, "BETWEEN "); ! if (frame->options & FRAMEOPTION_START_UNBOUNDED_PRECEDING) appendStringInfoString(buf, "UNBOUNDED PRECEDING "); ! else if (frame->options & FRAMEOPTION_START_CURRENT_ROW) appendStringInfoString(buf, "CURRENT ROW "); else Assert(false); ! if (frame->options & FRAMEOPTION_BETWEEN) { appendStringInfoString(buf, "AND "); ! if (frame->options & FRAMEOPTION_END_UNBOUNDED_FOLLOWING) appendStringInfoString(buf, "UNBOUNDED FOLLOWING "); ! else if (frame->options & FRAMEOPTION_END_CURRENT_ROW) appendStringInfoString(buf, "CURRENT ROW "); else Assert(false); *** a/src/include/nodes/execnodes.h --- b/src/include/nodes/execnodes.h *************** *** 14,19 **** --- 14,20 ---- #ifndef EXECNODES_H #define EXECNODES_H + #include "windowapi.h" #include "access/genam.h" #include "access/heapam.h" #include "access/skey.h" *************** *** 1585,1594 **** typedef struct WindowAggState FmgrInfo *ordEqfunctions; /* equality funcs for ordering columns */ Tuplestorestate *buffer; /* stores rows of current partition */ int current_ptr; /* read pointer # for current */ - int agg_ptr; /* read pointer # for aggregates */ int64 spooled_rows; /* total # of rows in buffer */ int64 currentpos; /* position of current row in partition */ int64 frametailpos; /* current frame tail position */ int64 aggregatedupto; /* rows before this one are aggregated */ MemoryContext wincontext; /* context for partition-lifespan data */ --- 1586,1597 ---- FmgrInfo *ordEqfunctions; /* equality funcs for ordering columns */ Tuplestorestate *buffer; /* stores rows of current partition */ int current_ptr; /* read pointer # for current */ int64 spooled_rows; /* total # of rows in buffer */ int64 currentpos; /* position of current row in partition */ + int64 frameheadpos; /* current frame head position */ int64 frametailpos; /* current frame tail position */ + WindowObject agg_winobj; + int64 aggregatedbase; /* row position this aggregate started on */ int64 aggregatedupto; /* rows before this one are aggregated */ MemoryContext wincontext; /* context for partition-lifespan data */ *************** *** 1600,1607 **** typedef struct WindowAggState --- 1603,1613 ---- * tuplestore */ bool more_partitions;/* true if there's more partitions after this * one */ + bool framehead_valid;/* true if frameheadpos is known up to date + * for current row */ bool frametail_valid;/* true if frametailpos is known up to date * for current row */ + bool frametail_stable; /* frame's tail doesn't move */ TupleTableSlot *first_part_slot; /* first tuple of current or next * partition */ *** a/src/include/nodes/nodes.h --- b/src/include/nodes/nodes.h *************** *** 362,367 **** typedef enum NodeTag --- 362,368 ---- T_ResTarget, T_TypeCast, T_SortBy, + T_WindowFrameDef, T_WindowDef, T_RangeSubselect, T_RangeFunction, *** a/src/include/nodes/parsenodes.h --- b/src/include/nodes/parsenodes.h *************** *** 376,381 **** typedef struct SortBy --- 376,393 ---- } SortBy; /* + * WindowFrameDef - for FRAME clause description in WINDOW definition + */ + typedef struct WindowFrameDef + { + NodeTag type; + int options; /* frame_clause options, see below */ + Node *startOffset; /* how far is the starting bound */ + Node *endOffset; /* how far is the ending bound */ + int location; /* parse location, or -1 if none/unknown */ + } WindowFrameDef; + + /* * WindowDef - raw representation of WINDOW and OVER clauses * * For entries in a WINDOW list, "name" is the window name being defined. *************** *** 390,396 **** typedef struct WindowDef 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; --- 402,408 ---- char *refname; /* referenced window name, if any */ List *partitionClause; /* PARTITION BY expression list */ List *orderClause; /* ORDER BY (list of SortBy) */ ! WindowFrameDef *frame; /* FRAME clause including boundary values */ int location; /* parse location, or -1 if none/unknown */ } WindowDef; *************** *** 412,417 **** typedef struct WindowDef --- 424,433 ---- #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_START_VALUE_PRECEDING 0x01000 /* start is V. P. */ + #define FRAMEOPTION_END_VALUE_PRECEDING 0x02000 /* end is V.P. */ + #define FRAMEOPTION_START_VALUE_FOLLOWING 0x04000 /* start is V. F. */ + #define FRAMEOPTION_END_VALUE_FOLLOWING 0x08000 /* end is V. F. */ #define FRAMEOPTION_DEFAULTS \ (FRAMEOPTION_RANGE | FRAMEOPTION_START_UNBOUNDED_PRECEDING | \ *************** *** 794,800 **** 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; --- 810,816 ---- char *refname; /* referenced window name, if any */ List *partitionClause; /* PARTITION BY list */ List *orderClause; /* ORDER BY list */ ! WindowFrameDef *frame; /* frame_clause options and boundaries */ Index winref; /* ID referenced by window functions */ bool copiedOrder; /* did we copy orderClause from refname? */ } WindowClause; *** a/src/include/nodes/plannodes.h --- b/src/include/nodes/plannodes.h *************** *** 552,557 **** typedef struct WindowAgg --- 552,559 ---- AttrNumber *ordColIdx; /* their indexes in the target list */ Oid *ordOperators; /* equality operators for ordering columns */ int frameOptions; /* frame_clause options, see WindowDef */ + Node *startOffset; /* frame start boundary */ + Node *endOffset; /* frame end boundary */ } WindowAgg; /* ---------------- *** a/src/include/optimizer/planmain.h --- b/src/include/optimizer/planmain.h *************** *** 61,67 **** extern WindowAgg *make_windowagg(PlannerInfo *root, List *tlist, int numWindowFuncs, Index winref, int partNumCols, AttrNumber *partColIdx, Oid *partOperators, int ordNumCols, AttrNumber *ordColIdx, Oid *ordOperators, ! int frameOptions, Plan *lefttree); extern Group *make_group(PlannerInfo *root, List *tlist, List *qual, int numGroupCols, AttrNumber *grpColIdx, Oid *grpOperators, double numGroups, --- 61,68 ---- int numWindowFuncs, Index winref, int partNumCols, AttrNumber *partColIdx, Oid *partOperators, int ordNumCols, AttrNumber *ordColIdx, Oid *ordOperators, ! int frameOptions, Node *startOffset, Node *endOffset, ! Plan *lefttree); extern Group *make_group(PlannerInfo *root, List *tlist, List *qual, int numGroupCols, AttrNumber *grpColIdx, Oid *grpOperators, double numGroups, *** a/src/test/regress/expected/window.out --- b/src/test/regress/expected/window.out *************** *** 728,733 **** FROM (select distinct ten, four from tenk1) ss; --- 728,871 ---- 3 | 2 | 4 | 2 (20 rows) + SELECT sum(unique1) over (order by four range between current row and unbounded following), + unique1, four + FROM tenk1 WHERE unique1 < 10; + sum | unique1 | four + -----+---------+------ + 45 | 0 | 0 + 45 | 8 | 0 + 45 | 4 | 0 + 33 | 5 | 1 + 33 | 9 | 1 + 33 | 1 | 1 + 18 | 6 | 2 + 18 | 2 | 2 + 10 | 3 | 3 + 10 | 7 | 3 + (10 rows) + + SELECT sum(unique1) over (rows between current row and unbounded following), + unique1, four + FROM tenk1 WHERE unique1 < 10; + sum | unique1 | four + -----+---------+------ + 45 | 4 | 0 + 41 | 2 | 2 + 39 | 1 | 1 + 38 | 6 | 2 + 32 | 9 | 1 + 23 | 8 | 0 + 15 | 5 | 1 + 10 | 3 | 3 + 7 | 7 | 3 + 0 | 0 | 0 + (10 rows) + + SELECT sum(unique1) over (rows between 2 preceding and 2 following), + unique1, four + FROM tenk1 WHERE unique1 < 10; + sum | unique1 | four + -----+---------+------ + 7 | 4 | 0 + 13 | 2 | 2 + 22 | 1 | 1 + 26 | 6 | 2 + 29 | 9 | 1 + 31 | 8 | 0 + 32 | 5 | 1 + 23 | 3 | 3 + 15 | 7 | 3 + 10 | 0 | 0 + (10 rows) + + SELECT sum(unique1) over (rows between 2 preceding and 1 preceding), + unique1, four + FROM tenk1 WHERE unique1 < 10; + sum | unique1 | four + -----+---------+------ + | 4 | 0 + 4 | 2 | 2 + 6 | 1 | 1 + 3 | 6 | 2 + 7 | 9 | 1 + 15 | 8 | 0 + 17 | 5 | 1 + 13 | 3 | 3 + 8 | 7 | 3 + 10 | 0 | 0 + (10 rows) + + SELECT sum(unique1) over (rows between 1 following and 3 following), + unique1, four + FROM tenk1 WHERE unique1 < 10; + sum | unique1 | four + -----+---------+------ + 9 | 4 | 0 + 16 | 2 | 2 + 23 | 1 | 1 + 22 | 6 | 2 + 16 | 9 | 1 + 15 | 8 | 0 + 10 | 5 | 1 + 7 | 3 | 3 + 0 | 7 | 3 + 0 | 0 | 0 + (10 rows) + + SELECT sum(unique1) over (rows between unbounded preceding and 1 following), + unique1, four + FROM tenk1 WHERE unique1 < 10; + sum | unique1 | four + -----+---------+------ + 6 | 4 | 0 + 7 | 2 | 2 + 13 | 1 | 1 + 22 | 6 | 2 + 30 | 9 | 1 + 35 | 8 | 0 + 38 | 5 | 1 + 45 | 3 | 3 + 45 | 7 | 3 + 45 | 0 | 0 + (10 rows) + + SELECT sum(unique1) over (w range between current row and unbounded following), + unique1, four + FROM tenk1 WHERE unique1 < 10 WINDOW w AS (order by four); + sum | unique1 | four + -----+---------+------ + 45 | 0 | 0 + 45 | 8 | 0 + 45 | 4 | 0 + 33 | 5 | 1 + 33 | 9 | 1 + 33 | 1 | 1 + 18 | 6 | 2 + 18 | 2 | 2 + 10 | 3 | 3 + 10 | 7 | 3 + (10 rows) + + SELECT first_value(unique1) over w, + nth_value(unique1, 2) over w AS nth_2, + last_value(unique1) over w, unique1, four + FROM tenk1 WHERE unique1 < 10 + WINDOW w AS (order by four range between current row and unbounded following); + first_value | nth_2 | last_value | unique1 | four + -------------+-------+------------+---------+------ + 0 | 8 | 7 | 0 | 0 + 0 | 8 | 7 | 8 | 0 + 0 | 8 | 7 | 4 | 0 + 5 | 9 | 7 | 5 | 1 + 5 | 9 | 7 | 9 | 1 + 5 | 9 | 7 | 1 | 1 + 6 | 2 | 7 | 6 | 2 + 6 | 2 | 7 | 2 | 2 + 3 | 7 | 7 | 3 | 3 + 3 | 7 | 7 | 7 | 3 + (10 rows) + -- with UNION SELECT count(*) OVER (PARTITION BY four) FROM (SELECT * FROM tenk1 UNION ALL SELECT * FROM tenk2)s LIMIT 0; count *** a/src/test/regress/sql/window.sql --- b/src/test/regress/sql/window.sql *************** *** 161,166 **** SELECT four, ten/4 as two, --- 161,200 ---- 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; + SELECT sum(unique1) over (order by four range between current row and unbounded following), + unique1, four + FROM tenk1 WHERE unique1 < 10; + + SELECT sum(unique1) over (rows between current row and unbounded following), + unique1, four + FROM tenk1 WHERE unique1 < 10; + + SELECT sum(unique1) over (rows between 2 preceding and 2 following), + unique1, four + FROM tenk1 WHERE unique1 < 10; + + SELECT sum(unique1) over (rows between 2 preceding and 1 preceding), + unique1, four + FROM tenk1 WHERE unique1 < 10; + + SELECT sum(unique1) over (rows between 1 following and 3 following), + unique1, four + FROM tenk1 WHERE unique1 < 10; + + SELECT sum(unique1) over (rows between unbounded preceding and 1 following), + unique1, four + FROM tenk1 WHERE unique1 < 10; + + SELECT sum(unique1) over (w range between current row and unbounded following), + unique1, four + FROM tenk1 WHERE unique1 < 10 WINDOW w AS (order by four); + + SELECT first_value(unique1) over w, + nth_value(unique1, 2) over w AS nth_2, + last_value(unique1) over w, unique1, four + FROM tenk1 WHERE unique1 < 10 + WINDOW w AS (order by four range between current row and unbounded following); + -- with UNION SELECT count(*) OVER (PARTITION BY four) FROM (SELECT * FROM tenk1 UNION ALL SELECT * FROM tenk2)s LIMIT 0;