diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 487c7ff..d6fd518 100644 *** a/doc/src/sgml/func.sgml --- b/doc/src/sgml/func.sgml *************** SELECT xmlagg(x) FROM (SELECT x FROM tes *** 14729,14736 **** partition through the last peer of the current row. This is likely to give unhelpful results for last_value and sometimes also nth_value. You can redefine the frame by ! adding a suitable frame specification (RANGE or ! ROWS) to the OVER clause. See for more information about frame specifications. --- 14729,14736 ---- partition through the last peer of the current row. This is likely to give unhelpful results for last_value and sometimes also nth_value. You can redefine the frame by ! adding a suitable frame specification (RANGE, ! ROWS or GROUPS) to the OVER clause. See for more information about frame specifications. diff --git a/doc/src/sgml/ref/select.sgml b/doc/src/sgml/ref/select.sgml index 8a3e86b..3381dca 100644 *** a/doc/src/sgml/ref/select.sgml --- b/doc/src/sgml/ref/select.sgml *************** WINDOW wi *** 859,866 **** The frame_clause can be one of ! { RANGE | ROWS } frame_start ! { RANGE | ROWS } BETWEEN frame_start AND frame_end where frame_start and frame_end can be --- 859,866 ---- The frame_clause can be one of ! { RANGE | ROWS | GROUPS } frame_start [ frame_exclusion_clause ] ! { RANGE | ROWS | GROUPS } BETWEEN frame_start AND frame_end [ frame_exclusion_clause ] where frame_start and frame_end can be *************** CURRENT ROW *** 874,879 **** --- 874,889 ---- UNBOUNDED FOLLOWING + and the optional frame_exclusion_clause can be + one of + + + EXCLUDE CURRENT ROW + EXCLUDE GROUP + EXCLUDE TIES + EXCLUDE NO OTHERS + + If frame_end is omitted it defaults to CURRENT ROW. Restrictions are that frame_start cannot be UNBOUNDED FOLLOWING, *************** UNBOUNDED FOLLOWING *** 894,918 **** In general, UNBOUNDED PRECEDING means that the frame starts with the first row of the partition, and similarly UNBOUNDED FOLLOWING means that the frame ends with the last ! row of the partition (regardless of RANGE or ROWS ! mode). In ROWS mode, CURRENT ROW means that the frame starts or ends with the current row; but in ! RANGE mode it means that the frame starts or ends with the current row's first or last peer in the ORDER BY ordering. The value PRECEDING and ! value FOLLOWING cases are currently only ! allowed in ROWS mode. They indicate that the frame starts ! or ends with the row that many rows before or after the current row. ! value must be an integer expression not ! containing any variables, aggregate functions, or window functions. ! The value must not be null or negative; but it can be zero, which ! selects the current row itself. Beware that the ROWS options can produce unpredictable results if the ORDER BY ordering does not order the rows ! uniquely. The RANGE options are designed to ensure that rows that are peers in the ORDER BY ordering are treated alike; all peer rows will be in the same frame. --- 904,940 ---- In general, UNBOUNDED PRECEDING means that the frame starts with the first row of the partition, and similarly UNBOUNDED FOLLOWING means that the frame ends with the last ! row of the partition (regardless of RANGE, ROWS ! or GROUPS mode). In ROWS mode, CURRENT ROW means that the frame starts or ends with the current row; but in ! RANGE or GROUPS mode it means that the frame starts or ends with the current row's first or last peer in the ORDER BY ordering. The value PRECEDING and ! value FOLLOWING cases differ depending on ! whether the frame clause is in ROWS, RANGEor GROUPS mode. In ! ROWS mode, they indicate that the frame starts or ends with the row that ! many rows before or after the current row. In RANGE mode, they indicate that ! the frame starts or ends when the ORDER BY column's value for each row is within the bounds ! specified by value for both the start and the end of the frame. In GROUPS mode, ! they indicate the number of changes to the value of the ORDER BY columns (i.e., groups of peers). ! In ROWS or GROUPS mode, value must be an integer expression not ! containing any variables, aggregate functions, or window functions.In RANGE mode, ! there must be exactly one ORDER BY column of a supported type. In all three modes, the value must not be null or ! negative; but it can be zero, which just selects the current row itself. ! ! ! ! For the frame_exclusion_clause, EXCLUDE CURRENT ROW ! excludes the current row from the frame. EXCLUDE TIES excludes any peers of the current row from the ! frame. EXCLUDE GROUP excludes both the current row and any peers of the current row from the frame. ! EXCLUDE NO OTHERS does nothing, but is provided in order to optionally document the intention ! not to exclude any other rows. Beware that the ROWS options can produce unpredictable results if the ORDER BY ordering does not order the rows ! uniquely. The RANGE and GROUPS options are designed to ensure that rows that are peers in the ORDER BY ordering are treated alike; all peer rows will be in the same frame. diff --git a/doc/src/sgml/syntax.sgml b/doc/src/sgml/syntax.sgml index a938a21..162ee04 100644 *** a/doc/src/sgml/syntax.sgml --- b/doc/src/sgml/syntax.sgml *************** FROM generate_series(1,10) AS s(i); *** 1805,1812 **** and the optional frame_clause can be one of ! { RANGE | ROWS } frame_start ! { RANGE | ROWS } BETWEEN frame_start AND frame_end where frame_start and frame_end can be one of --- 1805,1812 ---- and the optional frame_clause can be one of ! { RANGE | ROWS | GROUPS } frame_start [ frame_exclusion_clause ] ! { RANGE | ROWS | GROUPS } BETWEEN frame_start AND frame_end [ frame_exclusion_clause ] where frame_start and frame_end can be one of *************** CURRENT ROW *** 1817,1822 **** --- 1817,1829 ---- value FOLLOWING UNBOUNDED FOLLOWING + where the optional frame_exclusion_clause can be one of + + EXCLUDE CURRENT ROW + EXCLUDE GROUP + EXCLUDE TIES + EXCLUDE NO OTHERS + *************** UNBOUNDED FOLLOWING *** 1857,1864 **** the set of rows constituting the window frame, which is a subset of the current partition, for those window functions that act on the frame instead of the whole partition. The frame can be specified in ! either RANGE or ROWS mode; in either case, it ! runs from the frame_start to the frame_end. If frame_end is omitted, it defaults to CURRENT ROW. --- 1864,1871 ---- the set of rows constituting the window frame, which is a subset of the current partition, for those window functions that act on the frame instead of the whole partition. The frame can be specified in ! either RANGE, ROWS or GROUPS mode; ! in each case, it runs from the frame_start to the frame_end. If frame_end is omitted, it defaults to CURRENT ROW. *************** UNBOUNDED FOLLOWING *** 1871,1877 **** ! In RANGE mode, a frame_start of CURRENT ROW means the frame starts with the current row's first peer row (a row that ORDER BY considers equivalent to the current row), while a frame_end of --- 1878,1884 ---- ! In RANGE or GROUPS mode, a frame_start of CURRENT ROW means the frame starts with the current row's first peer row (a row that ORDER BY considers equivalent to the current row), while a frame_end of *************** UNBOUNDED FOLLOWING *** 1882,1894 **** The value PRECEDING and ! value FOLLOWING cases are currently only ! allowed in ROWS mode. They indicate that the frame starts ! or ends the specified number of rows before or after the current row. ! value must be an integer expression not ! containing any variables, aggregate functions, or window functions. ! The value must not be null or negative; but it can be zero, which ! just selects the current row. --- 1889,1918 ---- The value PRECEDING and ! value FOLLOWING cases, when used ! in ROWS mode, indicate that the frame starts or ends the specified ! number of rows before or after the current row. In ROWS mode, ! value must be an integer expression not containing any variables, ! aggregate functions, or window functions. ! When used in RANGE mode, they indicate that the frame starts or ends when the value of ! each row's ORDER BY column is within the start value and end value bounds. In both modes, ! the value must not be null or negative; but it can be zero, which just selects the current row. ! ! ! ! In GROUPS mode, value PRECEDING and ! value FOLLOWING cases indicate that the frame starts or ends the specified ! number of window framing groups before or after the current window framing group. ! Two rows are in the same window framing group if they are peers, (i.e., their ORDER BY column values ! match). This mode allows the selection of a frame by the number of changes to the ORDER BY columns. ! ! ! ! For the frame_exclusion_clause, EXCLUDE CURRENT ROW ! excludes the current row from the frame. EXCLUDE TIES excludes any peers of the current row from the ! frame. EXCLUDE GROUP excludes both the current row and any peers of the current row from the frame. ! EXCLUDE NO OTHERS does nothing, but is provided in order to optionally document the intention not to ! exclude any other rows. diff --git a/doc/src/sgml/xindex.sgml b/doc/src/sgml/xindex.sgml index 81c0cdc..38f9439 100644 *** a/doc/src/sgml/xindex.sgml --- b/doc/src/sgml/xindex.sgml *************** *** 401,409 **** ! B-trees require a single support function, and allow a second one to be supplied at the operator class author's option, as shown in . --- 401,414 ---- ! B-trees require a comparison support function, ! and allow two additional support functions to be supplied at the operator class author's option, as shown in . + (These additional support functions are not directly used during B-tree + index operations. Rather, they provide support for other parts of + the system, for which a B-tree operator class defines the semantics + of sorting.)
*************** *** 431,436 **** --- 436,449 ---- 2 + + + Compare a test value to a base value plus/minus an offset, and return + true or false according to the comparison result, as documented + in src/backend/access/nbtree/README (optional) + + 3 +
*************** SELECT * FROM mytable ORDER BY somecol U *** 1182,1187 **** --- 1195,1233 ---- + Another SQL feature that requires even more data-type-specific knowledge + is the RANGE offset + PRECEDING/FOLLOWING option for + window functions (see ). + For a query such as + + SELECT sum(x) OVER (ORDER BY x RANGE BETWEEN 5 PRECEDING AND 10 FOLLOWING) + FROM mytable; + + it is not sufficient to know how to order by x; + the database must also understand how to subtract 5 or + add 10 to the current row's value of x + to identify the bounds of the current window frame. Comparing the + resulting bounds to other rows' values of x is + possible, using the comparison operators provided by the B-tree operator + class that defines the ORDER BY ordering — but + addition and subtraction operators are not part of the operator class, so + which ones should be used? Hard-wiring that choice would be undesirable, + because different sort orders (different B-tree operator classes) might + need different behavior. Therefore, a B-tree operator class can specify + an in_range support function that encapsulates the + addition and subtraction behaviors that make sense for its sort order. + It can even provide more than one in_range support function, in case + there is more than one data type that makes sense to use as the offset + in RANGE clauses. + If the B-tree operator class associated with the window's ORDER + BY clause does not have a matching in_range support function, + the offset + PRECEDING/FOLLOWING + option is not supported. + + + Another important point is that an equality operator that appears in a hash operator family is a candidate for hash joins, hash aggregation, and related optimizations. The hash operator family diff --git a/src/backend/access/nbtree/README b/src/backend/access/nbtree/README index a3f11da..39cf7ea 100644 *** a/src/backend/access/nbtree/README --- b/src/backend/access/nbtree/README *************** Also, index searches using a key of a di *** 676,678 **** --- 676,746 ---- to behave sanely across two datatypes. The extensions to three or more datatypes within a family are not strictly required by the btree index mechanism itself, but the planner relies on them for optimization purposes. + + Window In-Range Support Functions + --------------------------------- + + Optionally, a btree operator family may include "in_range" support + functions among its pg_amproc entries. These are not used during btree + index operations; rather, they extend the semantics of the operator family + so that it can support window clauses containing the "RANGE value + PRECEDING" and "RANGE value FOLLOWING" frame bound types. Fundamentally, + the extra information provided is how to add or subtract an "offset" value + in a way that is compatible with the family's data ordering. + + An in_range function must have the signature + + in_range(val type1, base type1, offset type2, sub bool, less bool) + returns bool + + "val" and "base" must be of the same type, which is one of the types + supported by the operator family (i.e., a type for which it provides an + ordering). However, "offset" could be of a different type, which might be + one otherwise unsupported by the family. An example is that the built-in + time_ops family provides an in_range function that has "offset" of + type interval. A family can provide in_range functions for any of its + supported types and one or more offset types. Each in_range function + should be entered in pg_amproc with amproclefttype equal to "type1", + amprocrighttype equal to "type2", and amprocnum equal to BTINRANGE_PROC. + + The essential semantics of an in_range function depend on the boolean + flag parameters. It should add or subtract "base" and "offset", then + compare "val" to the result, as follows: + + return val >= (base + offset) if !sub and !less + return val <= (base + offset) if !sub and less + return val >= (base - offset) if sub and !less + return val <= (base - offset) if sub and less + + Before doing so, the function should check the sign of "offset": if it + is less than zero, raise error ERRCODE_INVALID_PRECEDING_FOLLOWING_SIZE + (22013) with error text like "invalid preceding or following size in window + function". (This is required by the SQL standard, although nonstandard + operator families might perhaps choose to ignore this restriction, since + there seems to be little semantic necessity for it.) This requirement is + delegated to the in_range function so that the core code needn't + understand what "less than zero" means for a particular data type. + + An additional expectation is that in_range functions should, if practical, + avoid throwing an error if "base + offset" or "base - offset" would + overflow. The correct comparison result can be determined even if that + value would be out of the datatype's range. Note that if the datatype + includes concepts such as "infinity" or "NaN", extra care may be needed + to ensure that in_range's results agree with the normal sort order of + the operator family. + + The results of the in_range function must be consistent with the sort + ordering imposed by the operator family. To be precise, given any + fixed value of "offset" and either value of "sub", then: + * If in_range with less = true is true for some "val1" and "base", + it is true for every val2 <= val1 with the same base. + * If in_range with less = true is false for some "val1" and "base", + it is false for every val2 >= val1 with the same base. + * If in_range with less = true is true for some "val" and "base1", + it is true for every base2 >= base1 with the same val. + * If in_range with less = true is false for some "val" and "base1", + it is false for every base2 <= base1 with the same val. + Analogous statements with inverted conditions hold when less = false. + + in_range functions need not handle NULL inputs, and typically will be + marked strict. diff --git a/src/backend/access/nbtree/nbtvalidate.c b/src/backend/access/nbtree/nbtvalidate.c index 8f4ccc8..f24091c 100644 *** a/src/backend/access/nbtree/nbtvalidate.c --- b/src/backend/access/nbtree/nbtvalidate.c *************** btvalidate(Oid opclassoid) *** 51,56 **** --- 51,57 ---- List *grouplist; OpFamilyOpFuncGroup *opclassgroup; List *familytypes; + int usefulgroups; int i; ListCell *lc; *************** btvalidate(Oid opclassoid) *** 95,100 **** --- 96,109 ---- ok = check_amproc_signature(procform->amproc, VOIDOID, true, 1, 1, INTERNALOID); break; + case BTINRANGE_PROC: + ok = check_amproc_signature(procform->amproc, BOOLOID, true, + 5, 5, + procform->amproclefttype, + procform->amproclefttype, + procform->amprocrighttype, + BOOLOID, BOOLOID); + break; default: ereport(INFO, (errcode(ERRCODE_INVALID_OBJECT_DEFINITION), *************** btvalidate(Oid opclassoid) *** 165,176 **** --- 174,201 ---- /* Now check for inconsistent groups of operators/functions */ grouplist = identify_opfamily_groups(oprlist, proclist); + usefulgroups = 0; opclassgroup = NULL; familytypes = NIL; foreach(lc, grouplist) { OpFamilyOpFuncGroup *thisgroup = (OpFamilyOpFuncGroup *) lfirst(lc); + /* + * It is possible for an in_range support function to have a RHS type + * that is otherwise irrelevant to the opfamily --- for instance, SQL + * requires the datetime_ops opclass to have range support with an + * interval offset. So, if this group appears to contain only an + * in_range function, ignore it: it doesn't represent a pair of + * supported types. + */ + if (thisgroup->operatorset == 0 && + thisgroup->functionset == (1 << BTINRANGE_PROC)) + continue; + + /* Else count it as a relevant group */ + usefulgroups++; + /* Remember the group exactly matching the test opclass */ if (thisgroup->lefttype == opcintype && thisgroup->righttype == opcintype) *************** btvalidate(Oid opclassoid) *** 186,193 **** /* * Complain if there seems to be an incomplete set of either operators ! * or support functions for this datatype pair. The only thing that ! * is considered optional is the sortsupport function. */ if (thisgroup->operatorset != ((1 << BTLessStrategyNumber) | --- 211,218 ---- /* * Complain if there seems to be an incomplete set of either operators ! * or support functions for this datatype pair. The only things ! * considered optional are the sortsupport and in_range functions. */ if (thisgroup->operatorset != ((1 << BTLessStrategyNumber) | *************** btvalidate(Oid opclassoid) *** 234,241 **** * additional qual clauses from equivalence classes, so it seems * reasonable to insist that all built-in btree opfamilies be complete. */ ! if (list_length(grouplist) != ! list_length(familytypes) * list_length(familytypes)) { ereport(INFO, (errcode(ERRCODE_INVALID_OBJECT_DEFINITION), --- 259,265 ---- * additional qual clauses from equivalence classes, so it seems * reasonable to insist that all built-in btree opfamilies be complete. */ ! if (usefulgroups != (list_length(familytypes) * list_length(familytypes))) { ereport(INFO, (errcode(ERRCODE_INVALID_OBJECT_DEFINITION), diff --git a/src/backend/catalog/dependency.c b/src/backend/catalog/dependency.c index be60270..b7e39af 100644 *** a/src/backend/catalog/dependency.c --- b/src/backend/catalog/dependency.c *************** find_expr_references_walker(Node *node, *** 1883,1888 **** --- 1883,1904 ---- context->addrs); return false; } + else if (IsA(node, WindowClause)) + { + WindowClause *wc = (WindowClause *) node; + + if (OidIsValid(wc->startInRangeFunc)) + add_object_address(OCLASS_PROC, wc->startInRangeFunc, 0, + context->addrs); + if (OidIsValid(wc->endInRangeFunc)) + add_object_address(OCLASS_PROC, wc->endInRangeFunc, 0, + context->addrs); + if (OidIsValid(wc->inRangeColl) && + wc->inRangeColl != DEFAULT_COLLATION_OID) + add_object_address(OCLASS_COLLATION, wc->inRangeColl, 0, + context->addrs); + /* fall through to examine substructure */ + } else if (IsA(node, Query)) { /* Recurse into RTE subquery or not-yet-planned sublink subquery */ diff --git a/src/backend/catalog/sql_features.txt b/src/backend/catalog/sql_features.txt index 8e746f3..20d61f3 100644 *** a/src/backend/catalog/sql_features.txt --- b/src/backend/catalog/sql_features.txt *************** T616 Null treatment option for LEAD and *** 498,504 **** T617 FIRST_VALUE and LAST_VALUE function YES T618 NTH_VALUE function NO function exists, but some options missing T619 Nested window functions NO ! T620 WINDOW clause: GROUPS option NO T621 Enhanced numeric functions YES T631 IN predicate with one list element YES T641 Multiple column assignment NO only some syntax variants supported --- 498,504 ---- T617 FIRST_VALUE and LAST_VALUE function YES T618 NTH_VALUE function NO function exists, but some options missing T619 Nested window functions NO ! T620 WINDOW clause: GROUPS option YES T621 Enhanced numeric functions YES T631 IN predicate with one list element YES T641 Multiple column assignment NO only some syntax variants supported diff --git a/src/backend/commands/opclasscmds.c b/src/backend/commands/opclasscmds.c index 1768140..e4b1369 100644 *** a/src/backend/commands/opclasscmds.c --- b/src/backend/commands/opclasscmds.c *************** assignProcTypes(OpFamilyMember *member, *** 1128,1137 **** procform = (Form_pg_proc) GETSTRUCT(proctup); /* ! * btree comparison procs must be 2-arg procs returning int4, while btree ! * sortsupport procs must take internal and return void. hash support ! * proc 1 must be a 1-arg proc returning int4, while proc 2 must be a ! * 2-arg proc returning int8. Otherwise we don't know. */ if (amoid == BTREE_AM_OID) { --- 1128,1138 ---- procform = (Form_pg_proc) GETSTRUCT(proctup); /* ! * btree comparison procs must be 2-arg procs returning int4. btree ! * sortsupport procs must take internal and return void. btree in_range ! * procs must be 5-arg procs returning bool. hash support proc 1 must be ! * a 1-arg proc returning int4, while proc 2 must be a 2-arg proc ! * returning int8. Otherwise we don't know. */ if (amoid == BTREE_AM_OID) { *************** assignProcTypes(OpFamilyMember *member, *** 1171,1176 **** --- 1172,1197 ---- * Can't infer lefttype/righttype from proc, so use default rule */ } + else if (member->number == BTINRANGE_PROC) + { + if (procform->pronargs != 5) + ereport(ERROR, + (errcode(ERRCODE_INVALID_OBJECT_DEFINITION), + errmsg("btree in_range procedures must have five arguments"))); + if (procform->prorettype != BOOLOID) + ereport(ERROR, + (errcode(ERRCODE_INVALID_OBJECT_DEFINITION), + errmsg("btree in_range procedures must return boolean"))); + + /* + * If lefttype/righttype isn't specified, use the proc's input + * types (we look at the test-value and offset arguments) + */ + if (!OidIsValid(member->lefttype)) + member->lefttype = procform->proargtypes.values[0]; + if (!OidIsValid(member->righttype)) + member->righttype = procform->proargtypes.values[2]; + } } else if (amoid == HASH_AM_OID) { diff --git a/src/backend/executor/nodeWindowAgg.c b/src/backend/executor/nodeWindowAgg.c index 0afb1c8..7b95ab3 100644 *** a/src/backend/executor/nodeWindowAgg.c --- b/src/backend/executor/nodeWindowAgg.c *************** static void begin_partition(WindowAggSta *** 180,189 **** 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_frameheadpos(WindowObject winobj, TupleTableSlot *slot); ! static void update_frametailpos(WindowObject winobj, TupleTableSlot *slot); static WindowStatePerAggData *initialize_peragg(WindowAggState *winstate, WindowFunc *wfunc, --- 180,190 ---- static void spool_tuples(WindowAggState *winstate, int64 pos); static void release_partition(WindowAggState *winstate); ! static int row_is_in_frame(WindowAggState *winstate, int64 pos, TupleTableSlot *slot); ! static void update_frameheadpos(WindowAggState *winstate); ! static void update_frametailpos(WindowAggState *winstate); ! static void update_grouptailpos(WindowAggState *winstate); static WindowStatePerAggData *initialize_peragg(WindowAggState *winstate, WindowFunc *wfunc, *************** eval_windowaggregates(WindowAggState *wi *** 683,693 **** temp_slot = winstate->temp_slot_1; /* ! * Currently, we support only a subset of the SQL-standard window framing ! * rules. ! * ! * If the frame start is UNBOUNDED_PRECEDING, the window frame 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 --- 684,692 ---- temp_slot = winstate->temp_slot_1; /* ! * If the window's frame start clause is UNBOUNDED_PRECEDING and no ! * exclusion clause is specified, then the window frame 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 *************** eval_windowaggregates(WindowAggState *wi *** 710,715 **** --- 709,719 ---- * must perform the aggregation all over again for all tuples within the * new frame boundaries. * + * If there's any exclusion clause, then we may have to aggregate over a + * non-contiguous set of rows, so we punt and recalculate for every row. + * (For some frame end choices, it might be that the frame is always + * contiguous anyway, but that's an optimization to investigate later.) + * * 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 *************** eval_windowaggregates(WindowAggState *wi *** 728,734 **** * The frame head should never move backwards, and the code below wouldn't * cope if it did, so for safety we complain if it does. */ ! update_frameheadpos(agg_winobj, temp_slot); if (winstate->frameheadpos < winstate->aggregatedbase) elog(ERROR, "window frame head moved backward"); --- 732,738 ---- * The frame head should never move backwards, and the code below wouldn't * cope if it did, so for safety we complain if it does. */ ! update_frameheadpos(winstate); if (winstate->frameheadpos < winstate->aggregatedbase) elog(ERROR, "window frame head moved backward"); *************** eval_windowaggregates(WindowAggState *wi *** 737,751 **** * the result values that were previously saved at the bottom of this * function. Since we don't know the current frame's end yet, this is not * possible to check for fully. But if the frame end mode is UNBOUNDED ! * FOLLOWING or CURRENT ROW, and the current row lies within the previous ! * row's frame, then the two frames' ends must coincide. Note that on the ! * first row aggregatedbase == aggregatedupto, meaning this test must ! * fail, so we don't need to check the "there was no previous row" case ! * explicitly here. */ if (winstate->aggregatedbase == winstate->frameheadpos && (winstate->frameOptions & (FRAMEOPTION_END_UNBOUNDED_FOLLOWING | FRAMEOPTION_END_CURRENT_ROW)) && winstate->aggregatedbase <= winstate->currentpos && winstate->aggregatedupto > winstate->currentpos) { --- 741,756 ---- * the result values that were previously saved at the bottom of this * function. Since we don't know the current frame's end yet, this is not * possible to check for fully. But if the frame end mode is UNBOUNDED ! * FOLLOWING or CURRENT ROW, no exclusion clause is specified, and the ! * current row lies within the previous row's frame, then the two frames' ! * ends must coincide. Note that on the first row aggregatedbase == ! * aggregatedupto, meaning this test must fail, so we don't need to check ! * the "there was no previous row" case explicitly here. */ if (winstate->aggregatedbase == winstate->frameheadpos && (winstate->frameOptions & (FRAMEOPTION_END_UNBOUNDED_FOLLOWING | FRAMEOPTION_END_CURRENT_ROW)) && + !(winstate->frameOptions & FRAMEOPTION_EXCLUSION) && winstate->aggregatedbase <= winstate->currentpos && winstate->aggregatedupto > winstate->currentpos) { *************** eval_windowaggregates(WindowAggState *wi *** 766,771 **** --- 771,777 ---- * - if we're processing the first row in the partition, or * - if the frame's head moved and we cannot use an inverse * transition function, or + * - we have an EXCLUSION clause, or * - if the new frame doesn't overlap the old one * * Note that we don't strictly need to restart in the last case, but if *************** eval_windowaggregates(WindowAggState *wi *** 780,785 **** --- 786,792 ---- if (winstate->currentpos == 0 || (winstate->aggregatedbase != winstate->frameheadpos && !OidIsValid(peraggstate->invtransfn_oid)) || + (winstate->frameOptions & FRAMEOPTION_EXCLUSION) || winstate->aggregatedupto <= winstate->frameheadpos) { peraggstate->restart = true; *************** eval_windowaggregates(WindowAggState *wi *** 920,925 **** --- 927,934 ---- */ for (;;) { + int ret; + /* Fetch next row if we didn't already */ if (TupIsNull(agg_row_slot)) { *************** eval_windowaggregates(WindowAggState *wi *** 928,936 **** break; /* must be end of partition */ } ! /* 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; --- 937,951 ---- break; /* must be end of partition */ } ! /* ! * Exit loop if no more rows can be in frame. Skip aggregation if ! * current row is not in frame but there might be more in the frame. ! */ ! ret = row_is_in_frame(winstate, winstate->aggregatedupto, agg_row_slot); ! if (ret < 0) break; + if (ret == 0) + goto next_tuple; /* Set tuple context for evaluation of aggregate arguments */ winstate->tmpcontext->ecxt_outertuple = agg_row_slot; *************** eval_windowaggregates(WindowAggState *wi *** 951,956 **** --- 966,972 ---- peraggstate); } + next_tuple: /* Reset per-input-tuple context after each tuple */ ResetExprContext(winstate->tmpcontext); *************** eval_windowfunction(WindowAggState *wins *** 1061,1066 **** --- 1077,1083 ---- static void begin_partition(WindowAggState *winstate) { + WindowAgg *node = (WindowAgg *) winstate->ss.ps.plan; PlanState *outerPlan = outerPlanState(winstate); int numfuncs = winstate->numfuncs; int i; *************** begin_partition(WindowAggState *winstate *** 1068,1078 **** winstate->partition_spooled = false; winstate->framehead_valid = false; winstate->frametail_valid = false; winstate->spooled_rows = 0; winstate->currentpos = 0; winstate->frameheadpos = 0; ! winstate->frametailpos = -1; ExecClearTuple(winstate->agg_row_slot); /* * If this is the very first partition, we need to fetch the first input --- 1085,1105 ---- winstate->partition_spooled = false; winstate->framehead_valid = false; winstate->frametail_valid = false; + winstate->grouptail_valid = false; winstate->spooled_rows = 0; winstate->currentpos = 0; winstate->frameheadpos = 0; ! winstate->frametailpos = 0; ! winstate->currentgroup = 0; ! winstate->frameheadgroup = 0; ! winstate->frametailgroup = 0; ! winstate->groupheadpos = 0; ! winstate->grouptailpos = -1; /* see update_grouptailpos */ ExecClearTuple(winstate->agg_row_slot); + if (winstate->framehead_slot) + ExecClearTuple(winstate->framehead_slot); + if (winstate->frametail_slot) + ExecClearTuple(winstate->frametail_slot); /* * If this is the very first partition, we need to fetch the first input *************** begin_partition(WindowAggState *winstate *** 1099,1105 **** /* * Set up read pointers for the tuplestore. The current pointer doesn't * need BACKWARD capability, but the per-window-function read pointers do, ! * and the aggregate pointer does if frame start is movable. */ winstate->current_ptr = 0; /* read pointer 0 is pre-allocated */ --- 1126,1132 ---- /* * Set up read pointers for the tuplestore. The current pointer doesn't * need BACKWARD capability, but the per-window-function read pointers do, ! * and the aggregate pointer does if we might need to restart aggregation. */ winstate->current_ptr = 0; /* read pointer 0 is pre-allocated */ *************** begin_partition(WindowAggState *winstate *** 1112,1121 **** WindowObject agg_winobj = winstate->agg_winobj; int readptr_flags = 0; ! /* If the frame head is potentially movable ... */ ! if (!(winstate->frameOptions & FRAMEOPTION_START_UNBOUNDED_PRECEDING)) { ! /* ... create a mark pointer to track the frame head */ agg_winobj->markptr = tuplestore_alloc_read_pointer(winstate->buffer, 0); /* and the read pointer will need BACKWARD capability */ readptr_flags |= EXEC_FLAG_BACKWARD; --- 1139,1152 ---- WindowObject agg_winobj = winstate->agg_winobj; int readptr_flags = 0; ! /* ! * If the frame head is potentially movable, or we have an EXCLUSION ! * clause, we might need to restart aggregation ... ! */ ! if (!(winstate->frameOptions & FRAMEOPTION_START_UNBOUNDED_PRECEDING) || ! (winstate->frameOptions & FRAMEOPTION_EXCLUSION)) { ! /* ... so create a mark pointer to track the frame head */ agg_winobj->markptr = tuplestore_alloc_read_pointer(winstate->buffer, 0); /* and the read pointer will need BACKWARD capability */ readptr_flags |= EXEC_FLAG_BACKWARD; *************** begin_partition(WindowAggState *winstate *** 1150,1155 **** --- 1181,1224 ---- } /* + * If we are in RANGE or GROUPS mode, then determining frame boundaries + * requires physical access to the frame endpoint rows, except in + * degenerate cases. We create read pointers to point to those rows, to + * simplify access and ensure that the tuplestore doesn't discard the + * endpoint rows prematurely. (Must match logic in update_frameheadpos + * and update_frametailpos.) + */ + winstate->framehead_ptr = winstate->frametail_ptr = -1; /* if not used */ + + if ((winstate->frameOptions & (FRAMEOPTION_RANGE | FRAMEOPTION_GROUPS)) && + node->ordNumCols != 0) + { + if (!(winstate->frameOptions & FRAMEOPTION_START_UNBOUNDED_PRECEDING)) + winstate->framehead_ptr = + tuplestore_alloc_read_pointer(winstate->buffer, 0); + if (!(winstate->frameOptions & FRAMEOPTION_END_UNBOUNDED_FOLLOWING)) + winstate->frametail_ptr = + tuplestore_alloc_read_pointer(winstate->buffer, 0); + } + + /* + * If we have an exclusion clause that requires knowing the boundaries of + * the current row's peer group, we create a read pointer to track the + * tail position of the peer group (i.e., first row of the next peer + * group). The head position does not require its own pointer because we + * maintain that as a side effect of advancing the current row. + */ + winstate->grouptail_ptr = -1; + + if ((winstate->frameOptions & (FRAMEOPTION_EXCLUDE_GROUP | + FRAMEOPTION_EXCLUDE_TIES)) && + node->ordNumCols != 0) + { + winstate->grouptail_ptr = + tuplestore_alloc_read_pointer(winstate->buffer, 0); + } + + /* * Store the first tuple into the tuplestore (it's always available now; * we either read it above, or saved it at the end of previous partition) */ *************** release_partition(WindowAggState *winsta *** 1275,1344 **** * 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) { int frameOptions = winstate->frameOptions; Assert(pos >= 0); /* else caller error */ ! /* First, check frame starting conditions */ ! if (frameOptions & FRAMEOPTION_START_CURRENT_ROW) ! { ! if (frameOptions & FRAMEOPTION_ROWS) ! { ! /* rows before current row are out of frame */ ! if (pos < winstate->currentpos) ! return false; ! } ! else if (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; ! } ! else ! Assert(false); ! } ! else if (frameOptions & FRAMEOPTION_START_VALUE) ! { ! if (frameOptions & FRAMEOPTION_ROWS) ! { ! int64 offset = DatumGetInt64(winstate->startOffsetValue); ! ! /* rows before current row + offset are out of frame */ ! if (frameOptions & FRAMEOPTION_START_VALUE_PRECEDING) ! offset = -offset; ! ! if (pos < winstate->currentpos + offset) ! return false; ! } ! else if (frameOptions & FRAMEOPTION_RANGE) ! { ! /* parser should have rejected this */ ! elog(ERROR, "window frame with value offset is not implemented"); ! } ! else ! Assert(false); ! } ! /* Okay so far, now check frame ending conditions */ if (frameOptions & FRAMEOPTION_END_CURRENT_ROW) { if (frameOptions & FRAMEOPTION_ROWS) { /* rows after current row are out of frame */ if (pos > winstate->currentpos) ! return false; } ! else if (frameOptions & FRAMEOPTION_RANGE) { /* following row that is not peer is out of frame */ if (pos > winstate->currentpos && !are_peers(winstate, slot, winstate->ss.ss_ScanTupleSlot)) ! return false; } else Assert(false); --- 1344,1391 ---- * 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. + * + * Returns: + * -1, if the row is out of frame and no succeeding rows can be in frame + * 0, if the row is out of frame but succeeding rows might be in frame + * 1, if the row is in frame + * + * May clobber winstate->temp_slot_2. */ ! static int row_is_in_frame(WindowAggState *winstate, int64 pos, TupleTableSlot *slot) { int frameOptions = winstate->frameOptions; Assert(pos >= 0); /* else caller error */ ! /* ! * First, check frame starting conditions. We might as well delegate this ! * to update_frameheadpos always; it doesn't add any notable cost. ! */ ! update_frameheadpos(winstate); ! if (pos < winstate->frameheadpos) ! return 0; ! /* ! * Okay so far, now check frame ending conditions. Here, we avoid calling ! * update_frametailpos in simple cases, so as not to spool tuples further ! * ahead than necessary. ! */ if (frameOptions & FRAMEOPTION_END_CURRENT_ROW) { if (frameOptions & FRAMEOPTION_ROWS) { /* rows after current row are out of frame */ if (pos > winstate->currentpos) ! return -1; } ! else if (frameOptions & (FRAMEOPTION_RANGE | FRAMEOPTION_GROUPS)) { /* following row that is not peer is out of frame */ if (pos > winstate->currentpos && !are_peers(winstate, slot, winstate->ss.ss_ScanTupleSlot)) ! return -1; } else Assert(false); *************** row_is_in_frame(WindowAggState *winstate *** 1354,1393 **** offset = -offset; if (pos > winstate->currentpos + offset) ! return false; } ! else if (frameOptions & FRAMEOPTION_RANGE) { ! /* parser should have rejected this */ ! elog(ERROR, "window frame with value offset is not implemented"); } else Assert(false); } /* If we get here, it's in frame */ ! return true; } /* * update_frameheadpos * make frameheadpos valid for the current row * ! * Uses the winobj's read pointer for any required fetches; hence, if the ! * frame mode is one that requires row comparisons, the winobj's mark must ! * not be past the currently known frame head. Also uses the specified slot ! * for any required fetches. */ static void ! update_frameheadpos(WindowObject winobj, TupleTableSlot *slot) { - WindowAggState *winstate = winobj->winstate; WindowAgg *node = (WindowAgg *) winstate->ss.ps.plan; int frameOptions = winstate->frameOptions; if (winstate->framehead_valid) return; /* already known for current row */ if (frameOptions & FRAMEOPTION_START_UNBOUNDED_PRECEDING) { /* In UNBOUNDED PRECEDING mode, frame head is always row 0 */ --- 1401,1470 ---- offset = -offset; if (pos > winstate->currentpos + offset) ! return -1; } ! else if (frameOptions & (FRAMEOPTION_RANGE | FRAMEOPTION_GROUPS)) { ! /* hard cases, so delegate to update_frametailpos */ ! update_frametailpos(winstate); ! if (pos >= winstate->frametailpos) ! return -1; } else Assert(false); } + /* Check exclusion clause */ + if (frameOptions & FRAMEOPTION_EXCLUDE_CURRENT_ROW) + { + if (pos == winstate->currentpos) + return 0; + } + else if ((frameOptions & FRAMEOPTION_EXCLUDE_GROUP) || + ((frameOptions & FRAMEOPTION_EXCLUDE_TIES) && + pos != winstate->currentpos)) + { + WindowAgg *node = (WindowAgg *) winstate->ss.ps.plan; + + /* If no ORDER BY, all rows are peers with each other */ + if (node->ordNumCols == 0) + return 0; + /* Otherwise, check the group boundaries */ + if (pos >= winstate->groupheadpos) + { + update_grouptailpos(winstate); + if (pos < winstate->grouptailpos) + return 0; + } + } + /* If we get here, it's in frame */ ! return 1; } /* * update_frameheadpos * make frameheadpos valid for the current row * ! * Note that frameheadpos is computed without regard for any window exclusion ! * clause; the current row and/or its peers are considered part of the frame ! * for this purpose even if they must be excluded later. ! * ! * May clobber winstate->temp_slot_2. */ static void ! update_frameheadpos(WindowAggState *winstate) { WindowAgg *node = (WindowAgg *) winstate->ss.ps.plan; int frameOptions = winstate->frameOptions; + MemoryContext oldcontext; if (winstate->framehead_valid) return; /* already known for current row */ + /* We may be called in a short-lived context */ + oldcontext = MemoryContextSwitchTo(winstate->ss.ps.ps_ExprContext->ecxt_per_query_memory); + if (frameOptions & FRAMEOPTION_START_UNBOUNDED_PRECEDING) { /* In UNBOUNDED PRECEDING mode, frame head is always row 0 */ *************** update_frameheadpos(WindowObject winobj, *** 1402,1439 **** winstate->frameheadpos = winstate->currentpos; winstate->framehead_valid = true; } ! else if (frameOptions & FRAMEOPTION_RANGE) { - int64 fhprev; - /* If no ORDER BY, all rows are peers with each other */ if (node->ordNumCols == 0) { winstate->frameheadpos = 0; winstate->framehead_valid = true; return; } /* ! * In RANGE START_CURRENT mode, frame head is the first row that ! * is a peer of current row. We search backwards from current, ! * which could be a bit inefficient if peer sets are large. Might ! * be better to have a separate read pointer that moves forward ! * tracking the frame head. */ ! fhprev = winstate->currentpos - 1; ! for (;;) { ! /* assume the frame head can't go backwards */ ! if (fhprev < winstate->frameheadpos) ! break; ! if (!window_gettupleslot(winobj, fhprev, slot)) ! break; /* start of partition */ ! if (!are_peers(winstate, slot, winstate->ss.ss_ScanTupleSlot)) ! break; /* not peer of current row */ ! fhprev--; } - winstate->frameheadpos = fhprev + 1; winstate->framehead_valid = true; } else --- 1479,1525 ---- winstate->frameheadpos = winstate->currentpos; winstate->framehead_valid = true; } ! else if (frameOptions & (FRAMEOPTION_RANGE | FRAMEOPTION_GROUPS)) { /* If no ORDER BY, all rows are peers with each other */ if (node->ordNumCols == 0) { winstate->frameheadpos = 0; winstate->framehead_valid = true; + MemoryContextSwitchTo(oldcontext); return; } /* ! * In RANGE or GROUPS START_CURRENT_ROW mode, frame head is the ! * first row that is a peer of current row. We keep a copy of the ! * last-known frame head row in framehead_slot, and advance as ! * necessary. Note that if we reach end of partition, we will ! * leave frameheadpos = end+1 and framehead_slot empty. */ ! tuplestore_select_read_pointer(winstate->buffer, ! winstate->framehead_ptr); ! if (winstate->frameheadpos == 0 && ! TupIsNull(winstate->framehead_slot)) { ! /* fetch first row into framehead_slot, if we didn't already */ ! if (!tuplestore_gettupleslot(winstate->buffer, true, true, ! winstate->framehead_slot)) ! elog(ERROR, "unexpected end of tuplestore"); ! } ! ! while (!TupIsNull(winstate->framehead_slot)) ! { ! if (are_peers(winstate, winstate->framehead_slot, ! winstate->ss.ss_ScanTupleSlot)) ! break; /* this row is the correct frame head */ ! /* Note we advance frameheadpos even if the fetch fails */ ! winstate->frameheadpos++; ! spool_tuples(winstate, winstate->frameheadpos); ! if (!tuplestore_gettupleslot(winstate->buffer, true, true, ! winstate->framehead_slot)) ! break; /* end of partition */ } winstate->framehead_valid = true; } else *************** update_frameheadpos(WindowObject winobj, *** 1453,1459 **** /* frame head can't go before first row */ if (winstate->frameheadpos < 0) winstate->frameheadpos = 0; ! else if (winstate->frameheadpos > winstate->currentpos) { /* make sure frameheadpos is not past end of partition */ spool_tuples(winstate, winstate->frameheadpos - 1); --- 1539,1545 ---- /* frame head can't go before first row */ if (winstate->frameheadpos < 0) winstate->frameheadpos = 0; ! else if (winstate->frameheadpos > winstate->currentpos + 1) { /* make sure frameheadpos is not past end of partition */ spool_tuples(winstate, winstate->frameheadpos - 1); *************** update_frameheadpos(WindowObject winobj, *** 1464,1503 **** } else if (frameOptions & FRAMEOPTION_RANGE) { ! /* parser should have rejected this */ ! elog(ERROR, "window frame with value offset is not implemented"); } else Assert(false); } else Assert(false); } /* * update_frametailpos * make frametailpos valid for the current row * ! * Uses the winobj's read pointer for any required fetches; hence, if the ! * frame mode is one that requires row comparisons, 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 = winstate->frameOptions; if (winstate->frametail_valid) return; /* already known for current row */ if (frameOptions & FRAMEOPTION_END_UNBOUNDED_FOLLOWING) { /* In UNBOUNDED FOLLOWING mode, all partition rows are in frame */ spool_tuples(winstate, -1); ! winstate->frametailpos = winstate->spooled_rows - 1; winstate->frametail_valid = true; } else if (frameOptions & FRAMEOPTION_END_CURRENT_ROW) --- 1550,1721 ---- } else if (frameOptions & FRAMEOPTION_RANGE) { ! /* ! * In RANGE START_VALUE mode, frame head is the first row that ! * satisfies the in_range constraint relative to the current row. ! * We keep a copy of the last-known frame head row in ! * framehead_slot, and advance as necessary. Note that if we ! * reach end of partition, we will leave frameheadpos = end+1 and ! * framehead_slot empty. ! */ ! bool sub, ! less; ! ! /* Precompute flags for in_range checks */ ! if (frameOptions & FRAMEOPTION_START_VALUE_PRECEDING) ! sub = true; /* subtract startOffset from current row */ ! else ! sub = false; /* add it */ ! less = false; /* normally, we want frame head >= sum */ ! /* If sort order is descending, flip both flags */ ! if (!winstate->inRangeAsc) ! { ! sub = !sub; ! less = true; ! } ! ! tuplestore_select_read_pointer(winstate->buffer, ! winstate->framehead_ptr); ! if (winstate->frameheadpos == 0 && ! TupIsNull(winstate->framehead_slot)) ! { ! /* fetch first row into framehead_slot, if we didn't already */ ! if (!tuplestore_gettupleslot(winstate->buffer, true, true, ! winstate->framehead_slot)) ! elog(ERROR, "unexpected end of tuplestore"); ! } ! ! while (!TupIsNull(winstate->framehead_slot)) ! { ! Datum headval, ! currval; ! bool headisnull, ! currisnull; ! ! headval = slot_getattr(winstate->framehead_slot, 1, ! &headisnull); ! currval = slot_getattr(winstate->ss.ss_ScanTupleSlot, 1, ! &currisnull); ! if (headisnull || currisnull) ! { ! /* order of the rows depends only on nulls_first */ ! if (winstate->inRangeNullsFirst) ! { ! /* advance head if head is null and curr is not */ ! if (!headisnull || currisnull) ! break; ! } ! else ! { ! /* advance head if head is not null and curr is null */ ! if (headisnull || !currisnull) ! break; ! } ! } ! else ! { ! if (DatumGetBool(FunctionCall5Coll(&winstate->startInRangeFunc, ! winstate->inRangeColl, ! headval, ! currval, ! winstate->startOffsetValue, ! BoolGetDatum(sub), ! BoolGetDatum(less)))) ! break; /* this row is the correct frame head */ ! } ! /* Note we advance frameheadpos even if the fetch fails */ ! winstate->frameheadpos++; ! spool_tuples(winstate, winstate->frameheadpos); ! if (!tuplestore_gettupleslot(winstate->buffer, true, true, ! winstate->framehead_slot)) ! break; /* end of partition */ ! } ! winstate->framehead_valid = true; ! } ! else if (frameOptions & FRAMEOPTION_GROUPS) ! { ! /* ! * In GROUPS START_VALUE mode, frame head is the first row of the ! * first peer group whose number satisfies the offset constraint. ! * We keep a copy of the last-known frame head row in ! * framehead_slot, and advance as necessary. Note that if we ! * reach end of partition, we will leave frameheadpos = end+1 and ! * framehead_slot empty. ! */ ! int64 offset = DatumGetInt64(winstate->startOffsetValue); ! int64 minheadgroup; ! ! if (frameOptions & FRAMEOPTION_START_VALUE_PRECEDING) ! minheadgroup = winstate->currentgroup - offset; ! else ! minheadgroup = winstate->currentgroup + offset; ! ! tuplestore_select_read_pointer(winstate->buffer, ! winstate->framehead_ptr); ! if (winstate->frameheadpos == 0 && ! TupIsNull(winstate->framehead_slot)) ! { ! /* fetch first row into framehead_slot, if we didn't already */ ! if (!tuplestore_gettupleslot(winstate->buffer, true, true, ! winstate->framehead_slot)) ! elog(ERROR, "unexpected end of tuplestore"); ! } ! ! while (!TupIsNull(winstate->framehead_slot)) ! { ! if (winstate->frameheadgroup >= minheadgroup) ! break; /* this row is the correct frame head */ ! ExecCopySlot(winstate->temp_slot_2, winstate->framehead_slot); ! /* Note we advance frameheadpos even if the fetch fails */ ! winstate->frameheadpos++; ! spool_tuples(winstate, winstate->frameheadpos); ! if (!tuplestore_gettupleslot(winstate->buffer, true, true, ! winstate->framehead_slot)) ! break; /* end of partition */ ! if (!are_peers(winstate, winstate->temp_slot_2, ! winstate->framehead_slot)) ! winstate->frameheadgroup++; ! } ! ExecClearTuple(winstate->temp_slot_2); ! winstate->framehead_valid = true; } else Assert(false); } else Assert(false); + + MemoryContextSwitchTo(oldcontext); } /* * update_frametailpos * make frametailpos valid for the current row * ! * Note that frametailpos is computed without regard for any window exclusion ! * clause; the current row and/or its peers are considered part of the frame ! * for this purpose even if they must be excluded later. ! * ! * May clobber winstate->temp_slot_2. */ static void ! update_frametailpos(WindowAggState *winstate) { WindowAgg *node = (WindowAgg *) winstate->ss.ps.plan; int frameOptions = winstate->frameOptions; + MemoryContext oldcontext; if (winstate->frametail_valid) return; /* already known for current row */ + /* We may be called in a short-lived context */ + oldcontext = MemoryContextSwitchTo(winstate->ss.ps.ps_ExprContext->ecxt_per_query_memory); + if (frameOptions & FRAMEOPTION_END_UNBOUNDED_FOLLOWING) { /* In UNBOUNDED FOLLOWING mode, all partition rows are in frame */ spool_tuples(winstate, -1); ! winstate->frametailpos = winstate->spooled_rows; winstate->frametail_valid = true; } else if (frameOptions & FRAMEOPTION_END_CURRENT_ROW) *************** update_frametailpos(WindowObject winobj, *** 1505,1543 **** if (frameOptions & FRAMEOPTION_ROWS) { /* In ROWS mode, exactly the rows up to current are in frame */ ! winstate->frametailpos = winstate->currentpos; winstate->frametail_valid = true; } ! else if (frameOptions & FRAMEOPTION_RANGE) { - int64 ftnext; - /* 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, ! * frame end always >= current row. */ ! 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; } else --- 1723,1775 ---- if (frameOptions & FRAMEOPTION_ROWS) { /* In ROWS mode, exactly the rows up to current are in frame */ ! winstate->frametailpos = winstate->currentpos + 1; winstate->frametail_valid = true; } ! else if (frameOptions & (FRAMEOPTION_RANGE | FRAMEOPTION_GROUPS)) { /* If no ORDER BY, all rows are peers with each other */ if (node->ordNumCols == 0) { spool_tuples(winstate, -1); ! winstate->frametailpos = winstate->spooled_rows; winstate->frametail_valid = true; + MemoryContextSwitchTo(oldcontext); return; } /* ! * In RANGE or GROUPS END_CURRENT_ROW mode, frame end is the last ! * row that is a peer of current row, frame tail is the row after ! * that (if any). We keep a copy of the last-known frame tail row ! * in frametail_slot, and advance as necessary. Note that if we ! * reach end of partition, we will leave frametailpos = end+1 and ! * frametail_slot empty. */ ! tuplestore_select_read_pointer(winstate->buffer, ! winstate->frametail_ptr); ! if (winstate->frametailpos == 0 && ! TupIsNull(winstate->frametail_slot)) { ! /* fetch first row into frametail_slot, if we didn't already */ ! if (!tuplestore_gettupleslot(winstate->buffer, true, true, ! winstate->frametail_slot)) ! elog(ERROR, "unexpected end of tuplestore"); ! } ! ! while (!TupIsNull(winstate->frametail_slot)) ! { ! if (winstate->frametailpos > winstate->currentpos && ! !are_peers(winstate, winstate->frametail_slot, ! winstate->ss.ss_ScanTupleSlot)) ! break; /* this row is the frame tail */ ! /* Note we advance frametailpos even if the fetch fails */ ! winstate->frametailpos++; ! spool_tuples(winstate, winstate->frametailpos); ! if (!tuplestore_gettupleslot(winstate->buffer, true, true, ! winstate->frametail_slot)) break; /* end of partition */ } winstate->frametail_valid = true; } else *************** update_frametailpos(WindowObject winobj, *** 1553,1581 **** if (frameOptions & FRAMEOPTION_END_VALUE_PRECEDING) offset = -offset; ! winstate->frametailpos = winstate->currentpos + offset; ! /* smallest allowable value of frametailpos is -1 */ if (winstate->frametailpos < 0) ! winstate->frametailpos = -1; ! else if (winstate->frametailpos > winstate->currentpos) { ! /* make sure frametailpos is not past last row of partition */ ! spool_tuples(winstate, winstate->frametailpos); ! if (winstate->frametailpos >= winstate->spooled_rows) ! winstate->frametailpos = winstate->spooled_rows - 1; } winstate->frametail_valid = true; } else if (frameOptions & FRAMEOPTION_RANGE) { ! /* parser should have rejected this */ ! elog(ERROR, "window frame with value offset is not implemented"); } else Assert(false); } else Assert(false); } --- 1785,1998 ---- if (frameOptions & FRAMEOPTION_END_VALUE_PRECEDING) offset = -offset; ! winstate->frametailpos = winstate->currentpos + offset + 1; ! /* smallest allowable value of frametailpos is 0 */ if (winstate->frametailpos < 0) ! winstate->frametailpos = 0; ! else if (winstate->frametailpos > winstate->currentpos + 1) { ! /* make sure frametailpos is not past end of partition */ ! spool_tuples(winstate, winstate->frametailpos - 1); ! if (winstate->frametailpos > winstate->spooled_rows) ! winstate->frametailpos = winstate->spooled_rows; } winstate->frametail_valid = true; } else if (frameOptions & FRAMEOPTION_RANGE) { ! /* ! * In RANGE END_VALUE mode, frame end is the last row that ! * satisfies the in_range constraint relative to the current row, ! * frame tail is the row after that (if any). We keep a copy of ! * the last-known frame tail row in frametail_slot, and advance as ! * necessary. Note that if we reach end of partition, we will ! * leave frametailpos = end+1 and frametail_slot empty. ! */ ! bool sub, ! less; ! ! /* Precompute flags for in_range checks */ ! if (frameOptions & FRAMEOPTION_END_VALUE_PRECEDING) ! sub = true; /* subtract endOffset from current row */ ! else ! sub = false; /* add it */ ! less = true; /* normally, we want frame tail <= sum */ ! /* If sort order is descending, flip both flags */ ! if (!winstate->inRangeAsc) ! { ! sub = !sub; ! less = false; ! } ! ! tuplestore_select_read_pointer(winstate->buffer, ! winstate->frametail_ptr); ! if (winstate->frametailpos == 0 && ! TupIsNull(winstate->frametail_slot)) ! { ! /* fetch first row into frametail_slot, if we didn't already */ ! if (!tuplestore_gettupleslot(winstate->buffer, true, true, ! winstate->frametail_slot)) ! elog(ERROR, "unexpected end of tuplestore"); ! } ! ! while (!TupIsNull(winstate->frametail_slot)) ! { ! Datum tailval, ! currval; ! bool tailisnull, ! currisnull; ! ! tailval = slot_getattr(winstate->frametail_slot, 1, ! &tailisnull); ! currval = slot_getattr(winstate->ss.ss_ScanTupleSlot, 1, ! &currisnull); ! if (tailisnull || currisnull) ! { ! /* order of the rows depends only on nulls_first */ ! if (winstate->inRangeNullsFirst) ! { ! /* advance tail if tail is null or curr is not */ ! if (!tailisnull) ! break; ! } ! else ! { ! /* advance tail if tail is not null or curr is null */ ! if (!currisnull) ! break; ! } ! } ! else ! { ! if (!DatumGetBool(FunctionCall5Coll(&winstate->endInRangeFunc, ! winstate->inRangeColl, ! tailval, ! currval, ! winstate->endOffsetValue, ! BoolGetDatum(sub), ! BoolGetDatum(less)))) ! break; /* this row is the correct frame tail */ ! } ! /* Note we advance frametailpos even if the fetch fails */ ! winstate->frametailpos++; ! spool_tuples(winstate, winstate->frametailpos); ! if (!tuplestore_gettupleslot(winstate->buffer, true, true, ! winstate->frametail_slot)) ! break; /* end of partition */ ! } ! winstate->frametail_valid = true; ! } ! else if (frameOptions & FRAMEOPTION_GROUPS) ! { ! /* ! * In GROUPS END_VALUE mode, frame end is the last row of the last ! * peer group whose number satisfies the offset constraint, and ! * frame tail is the row after that (if any). We keep a copy of ! * the last-known frame tail row in frametail_slot, and advance as ! * necessary. Note that if we reach end of partition, we will ! * leave frametailpos = end+1 and frametail_slot empty. ! */ ! int64 offset = DatumGetInt64(winstate->endOffsetValue); ! int64 maxtailgroup; ! ! if (frameOptions & FRAMEOPTION_END_VALUE_PRECEDING) ! maxtailgroup = winstate->currentgroup - offset; ! else ! maxtailgroup = winstate->currentgroup + offset; ! ! tuplestore_select_read_pointer(winstate->buffer, ! winstate->frametail_ptr); ! if (winstate->frametailpos == 0 && ! TupIsNull(winstate->frametail_slot)) ! { ! /* fetch first row into frametail_slot, if we didn't already */ ! if (!tuplestore_gettupleslot(winstate->buffer, true, true, ! winstate->frametail_slot)) ! elog(ERROR, "unexpected end of tuplestore"); ! } ! ! while (!TupIsNull(winstate->frametail_slot)) ! { ! if (winstate->frametailgroup > maxtailgroup) ! break; /* this row is the correct frame tail */ ! ExecCopySlot(winstate->temp_slot_2, winstate->frametail_slot); ! /* Note we advance frametailpos even if the fetch fails */ ! winstate->frametailpos++; ! spool_tuples(winstate, winstate->frametailpos); ! if (!tuplestore_gettupleslot(winstate->buffer, true, true, ! winstate->frametail_slot)) ! break; /* end of partition */ ! if (!are_peers(winstate, winstate->temp_slot_2, ! winstate->frametail_slot)) ! winstate->frametailgroup++; ! } ! ExecClearTuple(winstate->temp_slot_2); ! winstate->frametail_valid = true; } else Assert(false); } else Assert(false); + + MemoryContextSwitchTo(oldcontext); + } + + /* + * update_grouptailpos + * make grouptailpos valid for the current row + * + * May clobber winstate->temp_slot_2. + */ + static void + update_grouptailpos(WindowAggState *winstate) + { + WindowAgg *node = (WindowAgg *) winstate->ss.ps.plan; + MemoryContext oldcontext; + + if (winstate->grouptail_valid) + return; /* already known for current row */ + + /* We may be called in a short-lived context */ + oldcontext = MemoryContextSwitchTo(winstate->ss.ps.ps_ExprContext->ecxt_per_query_memory); + + /* If no ORDER BY, all rows are peers with each other */ + if (node->ordNumCols == 0) + { + spool_tuples(winstate, -1); + winstate->grouptailpos = winstate->spooled_rows; + winstate->grouptail_valid = true; + MemoryContextSwitchTo(oldcontext); + return; + } + + /* + * Because grouptail_valid is reset only when current row advances into a + * new peer group, we always reach here knowing that grouptailpos needs to + * be advanced by at least one row. Hence, unlike the otherwise similar + * case for frame tail tracking, we do not need persistent storage of the + * group tail row. + */ + Assert(winstate->grouptailpos <= winstate->currentpos); + tuplestore_select_read_pointer(winstate->buffer, + winstate->grouptail_ptr); + for (;;) + { + /* Note we advance grouptailpos even if the fetch fails */ + winstate->grouptailpos++; + spool_tuples(winstate, winstate->grouptailpos); + if (!tuplestore_gettupleslot(winstate->buffer, true, true, + winstate->temp_slot_2)) + break; /* end of partition */ + if (winstate->grouptailpos > winstate->currentpos && + !are_peers(winstate, winstate->temp_slot_2, + winstate->ss.ss_ScanTupleSlot)) + break; /* this row is the group tail */ + } + ExecClearTuple(winstate->temp_slot_2); + winstate->grouptail_valid = true; + + MemoryContextSwitchTo(oldcontext); } *************** ExecWindowAgg(PlanState *pstate) *** 1602,1608 **** return NULL; /* ! * Compute frame offset values, if any, during first call. */ if (winstate->all_first) { --- 2019,2027 ---- return NULL; /* ! * Compute frame offset values, if any, during first call (or after a ! * rescan). These are assumed to hold constant throughout the scan; if ! * user gives us a volatile expression, we'll only use its initial value. */ if (winstate->all_first) { *************** ExecWindowAgg(PlanState *pstate) *** 1627,1640 **** get_typlenbyval(exprType((Node *) winstate->startOffset->expr), &len, &byval); winstate->startOffsetValue = datumCopy(value, byval, len); ! if (frameOptions & FRAMEOPTION_ROWS) { /* value is known to be int8 */ int64 offset = DatumGetInt64(value); if (offset < 0) ereport(ERROR, ! (errcode(ERRCODE_INVALID_PARAMETER_VALUE), errmsg("frame starting offset must not be negative"))); } } --- 2046,2059 ---- get_typlenbyval(exprType((Node *) winstate->startOffset->expr), &len, &byval); winstate->startOffsetValue = datumCopy(value, byval, len); ! if (frameOptions & (FRAMEOPTION_ROWS | FRAMEOPTION_GROUPS)) { /* value is known to be int8 */ int64 offset = DatumGetInt64(value); if (offset < 0) ereport(ERROR, ! (errcode(ERRCODE_INVALID_PRECEDING_FOLLOWING_SIZE), errmsg("frame starting offset must not be negative"))); } } *************** ExecWindowAgg(PlanState *pstate) *** 1652,1665 **** get_typlenbyval(exprType((Node *) winstate->endOffset->expr), &len, &byval); winstate->endOffsetValue = datumCopy(value, byval, len); ! if (frameOptions & FRAMEOPTION_ROWS) { /* value is known to be int8 */ int64 offset = DatumGetInt64(value); if (offset < 0) ereport(ERROR, ! (errcode(ERRCODE_INVALID_PARAMETER_VALUE), errmsg("frame ending offset must not be negative"))); } } --- 2071,2084 ---- get_typlenbyval(exprType((Node *) winstate->endOffset->expr), &len, &byval); winstate->endOffsetValue = datumCopy(value, byval, len); ! if (frameOptions & (FRAMEOPTION_ROWS | FRAMEOPTION_GROUPS)) { /* value is known to be int8 */ int64 offset = DatumGetInt64(value); if (offset < 0) ereport(ERROR, ! (errcode(ERRCODE_INVALID_PRECEDING_FOLLOWING_SIZE), errmsg("frame ending offset must not be negative"))); } } *************** ExecWindowAgg(PlanState *pstate) *** 1679,1684 **** --- 2098,2104 ---- /* This might mean that the frame moves, too */ winstate->framehead_valid = false; winstate->frametail_valid = false; + /* we don't need to invalidate grouptail here; see below */ } /* *************** ExecWindowAgg(PlanState *pstate) *** 1718,1729 **** * out of the tuplestore, since window function evaluation might cause the * tuplestore to dump its state to disk.) * * Current row must be in the tuplestore, since we spooled it above. */ tuplestore_select_read_pointer(winstate->buffer, winstate->current_ptr); ! if (!tuplestore_gettupleslot(winstate->buffer, true, true, ! winstate->ss.ss_ScanTupleSlot)) ! elog(ERROR, "unexpected end of tuplestore"); /* * Evaluate true window functions --- 2138,2175 ---- * out of the tuplestore, since window function evaluation might cause the * tuplestore to dump its state to disk.) * + * In GROUPS mode, or when tracking a group-oriented exclusion clause, we + * must also detect entering a new peer group and update associated state + * when that happens. We use temp_slot_2 to temporarily hold the previous + * row for this purpose. + * * Current row must be in the tuplestore, since we spooled it above. */ tuplestore_select_read_pointer(winstate->buffer, winstate->current_ptr); ! if ((winstate->frameOptions & (FRAMEOPTION_GROUPS | ! FRAMEOPTION_EXCLUDE_GROUP | ! FRAMEOPTION_EXCLUDE_TIES)) && ! winstate->currentpos > 0) ! { ! ExecCopySlot(winstate->temp_slot_2, winstate->ss.ss_ScanTupleSlot); ! if (!tuplestore_gettupleslot(winstate->buffer, true, true, ! winstate->ss.ss_ScanTupleSlot)) ! elog(ERROR, "unexpected end of tuplestore"); ! if (!are_peers(winstate, winstate->temp_slot_2, ! winstate->ss.ss_ScanTupleSlot)) ! { ! winstate->currentgroup++; ! winstate->groupheadpos = winstate->currentpos; ! winstate->grouptail_valid = false; ! } ! ExecClearTuple(winstate->temp_slot_2); ! } ! else ! { ! if (!tuplestore_gettupleslot(winstate->buffer, true, true, ! winstate->ss.ss_ScanTupleSlot)) ! elog(ERROR, "unexpected end of tuplestore"); ! } /* * Evaluate true window functions *************** ExecWindowAgg(PlanState *pstate) *** 1747,1752 **** --- 2193,2215 ---- eval_windowaggregates(winstate); /* + * If we have created auxiliary read pointers for the frame or group + * boundaries, force them to be kept up-to-date, because we don't know + * whether the window function(s) will do anything that requires that. + * Failing to advance the pointers would result in being unable to trim + * data from the tuplestore, which is bad. (If we could know in advance + * whether the window functions will use frame boundary info, we could + * skip creating these pointers in the first place ... but unfortunately + * the window function API doesn't require that.) + */ + if (winstate->framehead_ptr >= 0) + update_frameheadpos(winstate); + if (winstate->frametail_ptr >= 0) + update_frametailpos(winstate); + if (winstate->grouptail_ptr >= 0) + update_grouptailpos(winstate); + + /* * Truncate any no-longer-needed rows from the tuplestore. */ tuplestore_trim(winstate->buffer); *************** ExecInitWindowAgg(WindowAgg *node, EStat *** 1777,1782 **** --- 2240,2246 ---- ExprContext *tmpcontext; WindowStatePerFunc perfunc; WindowStatePerAgg peragg; + int frameOptions = node->frameOptions; int numfuncs, wfuncno, numaggs, *************** ExecInitWindowAgg(WindowAgg *node, EStat *** 1832,1837 **** --- 2296,2315 ---- winstate->temp_slot_2 = ExecInitExtraTupleSlot(estate); /* + * create frame head and tail slots only if needed (must match logic in + * update_frameheadpos and update_frametailpos) + */ + winstate->framehead_slot = winstate->frametail_slot = NULL; + + if (frameOptions & (FRAMEOPTION_RANGE | FRAMEOPTION_GROUPS)) + { + if (!(frameOptions & FRAMEOPTION_START_UNBOUNDED_PRECEDING)) + winstate->framehead_slot = ExecInitExtraTupleSlot(estate); + if (!(frameOptions & FRAMEOPTION_END_UNBOUNDED_FOLLOWING)) + winstate->frametail_slot = ExecInitExtraTupleSlot(estate); + } + + /* * WindowAgg nodes never have quals, since they can only occur at the * logical top level of a query (ie, after any WHERE or HAVING filters) */ *************** ExecInitWindowAgg(WindowAgg *node, EStat *** 1858,1863 **** --- 2336,2347 ---- winstate->ss.ss_ScanTupleSlot->tts_tupleDescriptor); ExecSetSlotDescriptor(winstate->temp_slot_2, winstate->ss.ss_ScanTupleSlot->tts_tupleDescriptor); + if (winstate->framehead_slot) + ExecSetSlotDescriptor(winstate->framehead_slot, + winstate->ss.ss_ScanTupleSlot->tts_tupleDescriptor); + if (winstate->frametail_slot) + ExecSetSlotDescriptor(winstate->frametail_slot, + winstate->ss.ss_ScanTupleSlot->tts_tupleDescriptor); /* * Initialize result tuple type and projection info. *************** ExecInitWindowAgg(WindowAgg *node, EStat *** 1991,1997 **** } /* copy frame options to state node for easy access */ ! winstate->frameOptions = node->frameOptions; /* initialize frame bound offset expressions */ winstate->startOffset = ExecInitExpr((Expr *) node->startOffset, --- 2475,2481 ---- } /* copy frame options to state node for easy access */ ! winstate->frameOptions = frameOptions; /* initialize frame bound offset expressions */ winstate->startOffset = ExecInitExpr((Expr *) node->startOffset, *************** ExecInitWindowAgg(WindowAgg *node, EStat *** 1999,2004 **** --- 2483,2497 ---- winstate->endOffset = ExecInitExpr((Expr *) node->endOffset, (PlanState *) winstate); + /* Lookup in_range support functions if needed */ + if (OidIsValid(node->startInRangeFunc)) + fmgr_info(node->startInRangeFunc, &winstate->startInRangeFunc); + if (OidIsValid(node->endInRangeFunc)) + fmgr_info(node->endInRangeFunc, &winstate->endInRangeFunc); + winstate->inRangeColl = node->inRangeColl; + winstate->inRangeAsc = node->inRangeAsc; + winstate->inRangeNullsFirst = node->inRangeNullsFirst; + winstate->all_first = true; winstate->partition_spooled = false; winstate->more_partitions = false; *************** ExecEndWindowAgg(WindowAggState *node) *** 2023,2028 **** --- 2516,2525 ---- ExecClearTuple(node->agg_row_slot); ExecClearTuple(node->temp_slot_1); ExecClearTuple(node->temp_slot_2); + if (node->framehead_slot) + ExecClearTuple(node->framehead_slot); + if (node->frametail_slot) + ExecClearTuple(node->frametail_slot); /* * Free both the expr contexts. *************** ExecReScanWindowAgg(WindowAggState *node *** 2068,2073 **** --- 2565,2574 ---- ExecClearTuple(node->agg_row_slot); ExecClearTuple(node->temp_slot_1); ExecClearTuple(node->temp_slot_2); + if (node->framehead_slot) + ExecClearTuple(node->framehead_slot); + if (node->frametail_slot) + ExecClearTuple(node->frametail_slot); /* Forget current wfunc values */ MemSet(econtext->ecxt_aggvalues, 0, sizeof(Datum) * node->numfuncs); *************** WinSetMarkPosition(WindowObject winobj, *** 2574,2580 **** /* * 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. --- 3075,3081 ---- /* * WinRowsArePeers ! * Compare two rows (specified by absolute position in partition) to see * if they are equal according to the ORDER BY clause. * * NB: this does not consider the window frame mode. *************** WinRowsArePeers(WindowObject winobj, int *** 2596,2601 **** --- 3097,3106 ---- if (node->ordNumCols == 0) return true; + /* + * Note: OK to use temp_slot_2 here because we aren't calling any + * frame-related functions (those tend to clobber temp_slot_2). + */ slot1 = winstate->temp_slot_1; slot2 = winstate->temp_slot_2; *************** WinGetFuncArgInPartition(WindowObject wi *** 2680,2709 **** if (isout) *isout = false; if (set_mark) ! { ! int frameOptions = winstate->frameOptions; ! int64 mark_pos = abs_pos; ! ! /* ! * In RANGE mode with a moving frame head, we must not let the ! * mark advance past frameheadpos, since that row has to be ! * fetchable during future update_frameheadpos calls. ! * ! * XXX it is very ugly to pollute window functions' marks with ! * this consideration; it could for instance mask a logic bug that ! * lets a window function fetch rows before what it had claimed ! * was its mark. Perhaps use a separate mark for frame head ! * probes? ! */ ! if ((frameOptions & FRAMEOPTION_RANGE) && ! !(frameOptions & FRAMEOPTION_START_UNBOUNDED_PRECEDING)) ! { ! update_frameheadpos(winobj, winstate->temp_slot_2); ! if (mark_pos > winstate->frameheadpos) ! mark_pos = winstate->frameheadpos; ! } ! WinSetMarkPosition(winobj, mark_pos); ! } econtext->ecxt_outertuple = slot; return ExecEvalExpr((ExprState *) list_nth(winobj->argstates, argno), econtext, isnull); --- 3185,3191 ---- 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); *************** WinGetFuncArgInPartition(WindowObject wi *** 2714,2732 **** * WinGetFuncArgInFrame * Evaluate a window function's argument expression on a specified * row of the window frame. The row is identified in lseek(2) style, ! * i.e. relative to the current, first, or last row. * * argno: argument number to evaluate (counted from 0) * relpos: signed rowcount offset from the seek position ! * seektype: WINDOW_SEEK_CURRENT, WINDOW_SEEK_HEAD, or WINDOW_SEEK_TAIL ! * set_mark: If the row is found and set_mark is true, the mark is moved to ! * the row as a side-effect. * isnull: output argument, receives isnull status of result * isout: output argument, set to indicate whether target row position * is out of frame (can pass NULL if caller doesn't care about this) * ! * Specifying a nonexistent row is not an error, it just causes a null result ! * (plus setting *isout true, if isout isn't NULL). */ Datum WinGetFuncArgInFrame(WindowObject winobj, int argno, --- 3196,3229 ---- * WinGetFuncArgInFrame * Evaluate a window function's argument expression on a specified * row of the window frame. The row is identified in lseek(2) style, ! * i.e. relative to the first or last row of the frame. (We do not ! * support WINDOW_SEEK_CURRENT here, because it's not very clear what ! * that should mean if the current row isn't part of the frame.) * * argno: argument number to evaluate (counted from 0) * relpos: signed rowcount offset from the seek position ! * seektype: WINDOW_SEEK_HEAD or WINDOW_SEEK_TAIL ! * set_mark: If the row is found/in frame and set_mark is true, the mark is ! * moved to the row as a side-effect. * isnull: output argument, receives isnull status of result * isout: output argument, set to indicate whether target row position * is out of frame (can pass NULL if caller doesn't care about this) * ! * Specifying a nonexistent or not-in-frame row is not an error, it just ! * causes a null result (plus setting *isout true, if isout isn't NULL). ! * ! * Note that some exclusion-clause options lead to situations where the ! * rows that are in-frame are not consecutive in the partition. But we ! * count only in-frame rows when measuring relpos. ! * ! * The set_mark flag is interpreted as meaning that the caller will specify ! * a constant (or, perhaps, monotonically increasing) relpos in successive ! * calls, so that *if there is no exclusion clause* there will be no need ! * to fetch a row before the previously fetched row. But we do not expect ! * the caller to know how to account for exclusion clauses. Therefore, ! * if there is an exclusion clause we take responsibility for adjusting the ! * mark request to something that will be safe given the above assumption ! * about relpos. */ Datum WinGetFuncArgInFrame(WindowObject winobj, int argno, *************** WinGetFuncArgInFrame(WindowObject winobj *** 2736,2743 **** WindowAggState *winstate; ExprContext *econtext; TupleTableSlot *slot; - bool gottuple; int64 abs_pos; Assert(WindowObjectIsValid(winobj)); winstate = winobj->winstate; --- 3233,3240 ---- WindowAggState *winstate; ExprContext *econtext; TupleTableSlot *slot; int64 abs_pos; + int64 mark_pos; Assert(WindowObjectIsValid(winobj)); winstate = winobj->winstate; *************** WinGetFuncArgInFrame(WindowObject winobj *** 2747,2812 **** switch (seektype) { case WINDOW_SEEK_CURRENT: ! 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); ! abs_pos = winstate->frametailpos + relpos; break; default: elog(ERROR, "unrecognized window seek type: %d", seektype); ! abs_pos = 0; /* keep compiler quiet */ break; } ! gottuple = window_gettupleslot(winobj, abs_pos, slot); ! if (gottuple) ! gottuple = row_is_in_frame(winstate, abs_pos, slot); ! if (!gottuple) ! { ! if (isout) ! *isout = true; ! *isnull = true; ! return (Datum) 0; ! } ! else ! { ! if (isout) ! *isout = false; ! if (set_mark) ! { ! int frameOptions = winstate->frameOptions; ! int64 mark_pos = abs_pos; ! /* ! * In RANGE mode with a moving frame head, we must not let the ! * mark advance past frameheadpos, since that row has to be ! * fetchable during future update_frameheadpos calls. ! * ! * XXX it is very ugly to pollute window functions' marks with ! * this consideration; it could for instance mask a logic bug that ! * lets a window function fetch rows before what it had claimed ! * was its mark. Perhaps use a separate mark for frame head ! * probes? ! */ ! if ((frameOptions & FRAMEOPTION_RANGE) && ! !(frameOptions & FRAMEOPTION_START_UNBOUNDED_PRECEDING)) ! { ! update_frameheadpos(winobj, winstate->temp_slot_2); ! if (mark_pos > winstate->frameheadpos) ! mark_pos = winstate->frameheadpos; ! } ! WinSetMarkPosition(winobj, mark_pos); ! } ! econtext->ecxt_outertuple = slot; ! return ExecEvalExpr((ExprState *) list_nth(winobj->argstates, argno), ! econtext, isnull); ! } } /* --- 3244,3410 ---- switch (seektype) { case WINDOW_SEEK_CURRENT: ! elog(ERROR, "WINDOW_SEEK_CURRENT is not supported for WinGetFuncArgInFrame"); ! abs_pos = mark_pos = 0; /* keep compiler quiet */ break; case WINDOW_SEEK_HEAD: ! /* rejecting relpos < 0 is easy and simplifies code below */ ! if (relpos < 0) ! goto out_of_frame; ! update_frameheadpos(winstate); abs_pos = winstate->frameheadpos + relpos; + mark_pos = abs_pos; + + /* + * Account for exclusion option if one is active, but advance only + * abs_pos not mark_pos. This prevents changes of the current + * row's peer group from resulting in trying to fetch a row before + * some previous mark position. + * + * Note that in some corner cases such as current row being + * outside frame, these calculations are theoretically too simple, + * but it doesn't matter because we'll end up deciding the row is + * out of frame. We do not attempt to avoid fetching rows past + * end of frame; that would happen in some cases anyway. + */ + switch (winstate->frameOptions & FRAMEOPTION_EXCLUSION) + { + case 0: + /* no adjustment needed */ + break; + case FRAMEOPTION_EXCLUDE_CURRENT_ROW: + if (abs_pos >= winstate->currentpos && + winstate->currentpos >= winstate->frameheadpos) + abs_pos++; + break; + case FRAMEOPTION_EXCLUDE_GROUP: + update_grouptailpos(winstate); + if (abs_pos >= winstate->groupheadpos && + winstate->grouptailpos > winstate->frameheadpos) + { + int64 overlapstart = Max(winstate->groupheadpos, + winstate->frameheadpos); + + abs_pos += winstate->grouptailpos - overlapstart; + } + break; + case FRAMEOPTION_EXCLUDE_TIES: + update_grouptailpos(winstate); + if (abs_pos >= winstate->groupheadpos && + winstate->grouptailpos > winstate->frameheadpos) + { + int64 overlapstart = Max(winstate->groupheadpos, + winstate->frameheadpos); + + if (abs_pos == overlapstart) + abs_pos = winstate->currentpos; + else + abs_pos += winstate->grouptailpos - overlapstart - 1; + } + break; + default: + elog(ERROR, "unrecognized frame option state: 0x%x", + winstate->frameOptions); + break; + } break; case WINDOW_SEEK_TAIL: ! /* rejecting relpos > 0 is easy and simplifies code below */ ! if (relpos > 0) ! goto out_of_frame; ! update_frametailpos(winstate); ! abs_pos = winstate->frametailpos - 1 + relpos; ! ! /* ! * Account for exclusion option if one is active. If there is no ! * exclusion, we can safely set the mark at the accessed row. But ! * if there is, we can only mark the frame start, because we can't ! * be sure how far back in the frame the exclusion might cause us ! * to fetch in future. Furthermore, we have to actually check ! * against frameheadpos here, since it's unsafe to try to fetch a ! * row before frame start if the mark might be there already. ! */ ! switch (winstate->frameOptions & FRAMEOPTION_EXCLUSION) ! { ! case 0: ! /* no adjustment needed */ ! mark_pos = abs_pos; ! break; ! case FRAMEOPTION_EXCLUDE_CURRENT_ROW: ! if (abs_pos <= winstate->currentpos && ! winstate->currentpos < winstate->frametailpos) ! abs_pos--; ! update_frameheadpos(winstate); ! if (abs_pos < winstate->frameheadpos) ! goto out_of_frame; ! mark_pos = winstate->frameheadpos; ! break; ! case FRAMEOPTION_EXCLUDE_GROUP: ! update_grouptailpos(winstate); ! if (abs_pos < winstate->grouptailpos && ! winstate->groupheadpos < winstate->frametailpos) ! { ! int64 overlapend = Min(winstate->grouptailpos, ! winstate->frametailpos); ! ! abs_pos -= overlapend - winstate->groupheadpos; ! } ! update_frameheadpos(winstate); ! if (abs_pos < winstate->frameheadpos) ! goto out_of_frame; ! mark_pos = winstate->frameheadpos; ! break; ! case FRAMEOPTION_EXCLUDE_TIES: ! update_grouptailpos(winstate); ! if (abs_pos < winstate->grouptailpos && ! winstate->groupheadpos < winstate->frametailpos) ! { ! int64 overlapend = Min(winstate->grouptailpos, ! winstate->frametailpos); ! ! if (abs_pos == overlapend - 1) ! abs_pos = winstate->currentpos; ! else ! abs_pos -= overlapend - 1 - winstate->groupheadpos; ! } ! update_frameheadpos(winstate); ! if (abs_pos < winstate->frameheadpos) ! goto out_of_frame; ! mark_pos = winstate->frameheadpos; ! break; ! default: ! elog(ERROR, "unrecognized frame option state: 0x%x", ! winstate->frameOptions); ! mark_pos = 0; /* keep compiler quiet */ ! break; ! } break; default: elog(ERROR, "unrecognized window seek type: %d", seektype); ! abs_pos = mark_pos = 0; /* keep compiler quiet */ break; } ! if (!window_gettupleslot(winobj, abs_pos, slot)) ! goto out_of_frame; ! /* The code above does not detect all out-of-frame cases, so check */ ! if (row_is_in_frame(winstate, abs_pos, slot) <= 0) ! goto out_of_frame; ! if (isout) ! *isout = false; ! if (set_mark) ! WinSetMarkPosition(winobj, mark_pos); ! econtext->ecxt_outertuple = slot; ! return ExecEvalExpr((ExprState *) list_nth(winobj->argstates, argno), ! econtext, isnull); ! ! out_of_frame: ! if (isout) ! *isout = true; ! *isnull = true; ! return (Datum) 0; } /* diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c index bafe0d1..82255b0 100644 *** a/src/backend/nodes/copyfuncs.c --- b/src/backend/nodes/copyfuncs.c *************** _copyWindowAgg(const WindowAgg *from) *** 1012,1017 **** --- 1012,1022 ---- COPY_SCALAR_FIELD(frameOptions); COPY_NODE_FIELD(startOffset); COPY_NODE_FIELD(endOffset); + COPY_SCALAR_FIELD(startInRangeFunc); + COPY_SCALAR_FIELD(endInRangeFunc); + COPY_SCALAR_FIELD(inRangeColl); + COPY_SCALAR_FIELD(inRangeAsc); + COPY_SCALAR_FIELD(inRangeNullsFirst); return newnode; } *************** _copyWindowClause(const WindowClause *fr *** 2412,2417 **** --- 2417,2427 ---- COPY_SCALAR_FIELD(frameOptions); COPY_NODE_FIELD(startOffset); COPY_NODE_FIELD(endOffset); + COPY_SCALAR_FIELD(startInRangeFunc); + COPY_SCALAR_FIELD(endInRangeFunc); + COPY_SCALAR_FIELD(inRangeColl); + COPY_SCALAR_FIELD(inRangeAsc); + COPY_SCALAR_FIELD(inRangeNullsFirst); COPY_SCALAR_FIELD(winref); COPY_SCALAR_FIELD(copiedOrder); diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c index 02ca7d5..b9bc8e3 100644 *** a/src/backend/nodes/equalfuncs.c --- b/src/backend/nodes/equalfuncs.c *************** _equalWindowClause(const WindowClause *a *** 2735,2740 **** --- 2735,2745 ---- COMPARE_SCALAR_FIELD(frameOptions); COMPARE_NODE_FIELD(startOffset); COMPARE_NODE_FIELD(endOffset); + COMPARE_SCALAR_FIELD(startInRangeFunc); + COMPARE_SCALAR_FIELD(endInRangeFunc); + COMPARE_SCALAR_FIELD(inRangeColl); + COMPARE_SCALAR_FIELD(inRangeAsc); + COMPARE_SCALAR_FIELD(inRangeNullsFirst); COMPARE_SCALAR_FIELD(winref); COMPARE_SCALAR_FIELD(copiedOrder); diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.c index e6ba096..011d2a3 100644 *** a/src/backend/nodes/outfuncs.c --- b/src/backend/nodes/outfuncs.c *************** _outWindowAgg(StringInfo str, const Wind *** 840,845 **** --- 840,850 ---- WRITE_INT_FIELD(frameOptions); WRITE_NODE_FIELD(startOffset); WRITE_NODE_FIELD(endOffset); + WRITE_OID_FIELD(startInRangeFunc); + WRITE_OID_FIELD(endInRangeFunc); + WRITE_OID_FIELD(inRangeColl); + WRITE_BOOL_FIELD(inRangeAsc); + WRITE_BOOL_FIELD(inRangeNullsFirst); } static void *************** _outWindowClause(StringInfo str, const W *** 2985,2990 **** --- 2990,3000 ---- WRITE_INT_FIELD(frameOptions); WRITE_NODE_FIELD(startOffset); WRITE_NODE_FIELD(endOffset); + WRITE_OID_FIELD(startInRangeFunc); + WRITE_OID_FIELD(endInRangeFunc); + WRITE_OID_FIELD(inRangeColl); + WRITE_BOOL_FIELD(inRangeAsc); + WRITE_BOOL_FIELD(inRangeNullsFirst); WRITE_UINT_FIELD(winref); WRITE_BOOL_FIELD(copiedOrder); } diff --git a/src/backend/nodes/readfuncs.c b/src/backend/nodes/readfuncs.c index 22d8b9d..068db35 100644 *** a/src/backend/nodes/readfuncs.c --- b/src/backend/nodes/readfuncs.c *************** _readWindowClause(void) *** 369,374 **** --- 369,379 ---- READ_INT_FIELD(frameOptions); READ_NODE_FIELD(startOffset); READ_NODE_FIELD(endOffset); + READ_OID_FIELD(startInRangeFunc); + READ_OID_FIELD(endInRangeFunc); + READ_OID_FIELD(inRangeColl); + READ_BOOL_FIELD(inRangeAsc); + READ_BOOL_FIELD(inRangeNullsFirst); READ_UINT_FIELD(winref); READ_BOOL_FIELD(copiedOrder); *************** _readWindowAgg(void) *** 2139,2144 **** --- 2144,2154 ---- READ_INT_FIELD(frameOptions); READ_NODE_FIELD(startOffset); READ_NODE_FIELD(endOffset); + READ_OID_FIELD(startInRangeFunc); + READ_OID_FIELD(endInRangeFunc); + READ_OID_FIELD(inRangeColl); + READ_BOOL_FIELD(inRangeAsc); + READ_BOOL_FIELD(inRangeNullsFirst); READ_DONE(); } diff --git a/src/backend/optimizer/plan/createplan.c b/src/backend/optimizer/plan/createplan.c index c46e131..da0cc7f 100644 *** a/src/backend/optimizer/plan/createplan.c --- b/src/backend/optimizer/plan/createplan.c *************** static WindowAgg *make_windowagg(List *t *** 261,266 **** --- 261,268 ---- int partNumCols, AttrNumber *partColIdx, Oid *partOperators, int ordNumCols, AttrNumber *ordColIdx, Oid *ordOperators, int frameOptions, Node *startOffset, Node *endOffset, + Oid startInRangeFunc, Oid endInRangeFunc, + Oid inRangeColl, bool inRangeAsc, bool inRangeNullsFirst, Plan *lefttree); static Group *make_group(List *tlist, List *qual, int numGroupCols, AttrNumber *grpColIdx, Oid *grpOperators, *************** create_windowagg_plan(PlannerInfo *root, *** 2123,2128 **** --- 2125,2135 ---- wc->frameOptions, wc->startOffset, wc->endOffset, + wc->startInRangeFunc, + wc->endInRangeFunc, + wc->inRangeColl, + wc->inRangeAsc, + wc->inRangeNullsFirst, subplan); copy_generic_path_info(&plan->plan, (Path *) best_path); *************** make_windowagg(List *tlist, Index winref *** 6080,6085 **** --- 6087,6094 ---- int partNumCols, AttrNumber *partColIdx, Oid *partOperators, int ordNumCols, AttrNumber *ordColIdx, Oid *ordOperators, int frameOptions, Node *startOffset, Node *endOffset, + Oid startInRangeFunc, Oid endInRangeFunc, + Oid inRangeColl, bool inRangeAsc, bool inRangeNullsFirst, Plan *lefttree) { WindowAgg *node = makeNode(WindowAgg); *************** make_windowagg(List *tlist, Index winref *** 6095,6100 **** --- 6104,6114 ---- node->frameOptions = frameOptions; node->startOffset = startOffset; node->endOffset = endOffset; + node->startInRangeFunc = startInRangeFunc; + node->endInRangeFunc = endInRangeFunc; + node->inRangeColl = inRangeColl; + node->inRangeAsc = inRangeAsc; + node->inRangeNullsFirst = inRangeNullsFirst; plan->targetlist = tlist; plan->lefttree = lefttree; diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index 5329432..60c64a1 100644 *** a/src/backend/parser/gram.y --- b/src/backend/parser/gram.y *************** static Node *makeRecursiveViewSelect(cha *** 570,575 **** --- 570,576 ---- %type window_clause window_definition_list opt_partition_clause %type window_definition over_clause window_specification opt_frame_clause frame_extent frame_bound + %type opt_window_exclusion_clause %type opt_existing_window_name %type opt_if_not_exists %type generated_when override_kind *************** static Node *makeRecursiveViewSelect(cha *** 632,638 **** FALSE_P FAMILY FETCH FILTER FIRST_P FLOAT_P FOLLOWING FOR FORCE FOREIGN FORWARD FREEZE FROM FULL FUNCTION FUNCTIONS ! GENERATED GLOBAL GRANT GRANTED GREATEST GROUP_P GROUPING HANDLER HAVING HEADER_P HOLD HOUR_P --- 633,639 ---- FALSE_P FAMILY FETCH FILTER FIRST_P FLOAT_P FOLLOWING FOR FORCE FOREIGN FORWARD FREEZE FROM FULL FUNCTION FUNCTIONS ! GENERATED GLOBAL GRANT GRANTED GREATEST GROUP_P GROUPING GROUPS HANDLER HAVING HEADER_P HOLD HOUR_P *************** static Node *makeRecursiveViewSelect(cha *** 656,662 **** NULLS_P NUMERIC OBJECT_P OF OFF OFFSET OIDS OLD ON ONLY OPERATOR OPTION OPTIONS OR ! ORDER ORDINALITY OUT_P OUTER_P OVER OVERLAPS OVERLAY OVERRIDING OWNED OWNER PARALLEL PARSER PARTIAL PARTITION PASSING PASSWORD PLACING PLANS POLICY POSITION PRECEDING PRECISION PRESERVE PREPARE PREPARED PRIMARY --- 657,664 ---- NULLS_P NUMERIC OBJECT_P OF OFF OFFSET OIDS OLD ON ONLY OPERATOR OPTION OPTIONS OR ! ORDER ORDINALITY OTHERS OUT_P OUTER_P ! OVER OVERLAPS OVERLAY OVERRIDING OWNED OWNER PARALLEL PARSER PARTIAL PARTITION PASSING PASSWORD PLACING PLANS POLICY POSITION PRECEDING PRECISION PRESERVE PREPARE PREPARED PRIMARY *************** static Node *makeRecursiveViewSelect(cha *** 676,682 **** SUBSCRIPTION SUBSTRING SYMMETRIC SYSID SYSTEM_P TABLE TABLES TABLESAMPLE TABLESPACE TEMP TEMPLATE TEMPORARY TEXT_P THEN ! TIME TIMESTAMP TO TRAILING TRANSACTION TRANSFORM TREAT TRIGGER TRIM TRUE_P TRUNCATE TRUSTED TYPE_P TYPES_P UNBOUNDED UNCOMMITTED UNENCRYPTED UNION UNIQUE UNKNOWN UNLISTEN UNLOGGED --- 678,685 ---- SUBSCRIPTION SUBSTRING SYMMETRIC SYSID SYSTEM_P TABLE TABLES TABLESAMPLE TABLESPACE TEMP TEMPLATE TEMPORARY TEXT_P THEN ! TIES TIME TIMESTAMP TO TRAILING TRANSACTION TRANSFORM ! TREAT TRIGGER TRIM TRUE_P TRUNCATE TRUSTED TYPE_P TYPES_P UNBOUNDED UNCOMMITTED UNENCRYPTED UNION UNIQUE UNKNOWN UNLISTEN UNLOGGED *************** static Node *makeRecursiveViewSelect(cha *** 724,732 **** * 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, ! * RANGE, ROWS to support opt_existing_window_name; and for RANGE, ROWS ! * so that they can follow a_expr without creating postfix-operator problems; * for GENERATED so that it can follow b_expr; * and for NULL so that it can follow b_expr in ColQualList without creating * postfix-operator problems. --- 727,736 ---- * 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, RANGE, ROWS, GROUPS to support opt_existing_window_name; ! * for RANGE, ROWS, GROUPS so that they can follow a_expr without creating ! * postfix-operator problems; * for GENERATED so that it can follow b_expr; * and for NULL so that it can follow b_expr in ColQualList without creating * postfix-operator problems. *************** static Node *makeRecursiveViewSelect(cha *** 746,752 **** * blame any funny behavior of UNBOUNDED on the SQL standard, though. */ %nonassoc UNBOUNDED /* ideally should have same precedence as IDENT */ ! %nonassoc IDENT GENERATED NULL_P PARTITION RANGE ROWS PRECEDING FOLLOWING CUBE ROLLUP %left Op OPERATOR /* multi-character ops and user-defined operators */ %left '+' '-' %left '*' '/' '%' --- 750,756 ---- * blame any funny behavior of UNBOUNDED on the SQL standard, though. */ %nonassoc UNBOUNDED /* ideally should have same precedence as IDENT */ ! %nonassoc IDENT GENERATED NULL_P PARTITION RANGE ROWS GROUPS PRECEDING FOLLOWING CUBE ROLLUP %left Op OPERATOR /* multi-character ops and user-defined operators */ %left '+' '-' %left '*' '/' '%' *************** window_specification: '(' opt_existing_w *** 14003,14009 **** ; /* ! * If we see PARTITION, RANGE, or ROWS as the first token after the '(' * of a window_specification, we want the assumption to be that there is * no existing_window_name; but those keywords are unreserved and so could * be ColIds. We fix this by making them have the same precedence as IDENT --- 14007,14013 ---- ; /* ! * If we see PARTITION, RANGE, ROWS or GROUPS as the first token after the '(' * of a window_specification, we want the assumption to be that there is * no existing_window_name; but those keywords are unreserved and so could * be ColIds. We fix this by making them have the same precedence as IDENT *************** opt_partition_clause: PARTITION BY expr_ *** 14023,14055 **** /* * For frame clauses, we return a WindowDef, but only some fields are used: * frameOptions, startOffset, and endOffset. - * - * This is only a subset of the full SQL:2008 frame_clause grammar. - * We don't support yet. */ opt_frame_clause: ! RANGE frame_extent { WindowDef *n = $2; n->frameOptions |= FRAMEOPTION_NONDEFAULT | FRAMEOPTION_RANGE; ! if (n->frameOptions & (FRAMEOPTION_START_VALUE_PRECEDING | ! FRAMEOPTION_END_VALUE_PRECEDING)) ! ereport(ERROR, ! (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), ! errmsg("RANGE PRECEDING is only supported with UNBOUNDED"), ! parser_errposition(@1))); ! if (n->frameOptions & (FRAMEOPTION_START_VALUE_FOLLOWING | ! FRAMEOPTION_END_VALUE_FOLLOWING)) ! ereport(ERROR, ! (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), ! errmsg("RANGE FOLLOWING is only supported with UNBOUNDED"), ! parser_errposition(@1))); $$ = n; } ! | ROWS frame_extent { WindowDef *n = $2; n->frameOptions |= FRAMEOPTION_NONDEFAULT | FRAMEOPTION_ROWS; $$ = n; } | /*EMPTY*/ --- 14027,14053 ---- /* * For frame clauses, we return a WindowDef, but only some fields are used: * frameOptions, startOffset, and endOffset. */ opt_frame_clause: ! RANGE frame_extent opt_window_exclusion_clause { WindowDef *n = $2; n->frameOptions |= FRAMEOPTION_NONDEFAULT | FRAMEOPTION_RANGE; ! n->frameOptions |= $3; $$ = n; } ! | ROWS frame_extent opt_window_exclusion_clause { WindowDef *n = $2; n->frameOptions |= FRAMEOPTION_NONDEFAULT | FRAMEOPTION_ROWS; + n->frameOptions |= $3; + $$ = n; + } + | GROUPS frame_extent opt_window_exclusion_clause + { + WindowDef *n = $2; + n->frameOptions |= FRAMEOPTION_NONDEFAULT | FRAMEOPTION_GROUPS; + n->frameOptions |= $3; $$ = n; } | /*EMPTY*/ *************** frame_bound: *** 14166,14171 **** --- 14164,14177 ---- } ; + opt_window_exclusion_clause: + EXCLUDE CURRENT_P ROW { $$ = FRAMEOPTION_EXCLUDE_CURRENT_ROW; } + | EXCLUDE GROUP_P { $$ = FRAMEOPTION_EXCLUDE_GROUP; } + | EXCLUDE TIES { $$ = FRAMEOPTION_EXCLUDE_TIES; } + | EXCLUDE NO OTHERS { $$ = 0; } + | /*EMPTY*/ { $$ = 0; } + ; + /* * Supporting nonterminals for expressions. *************** unreserved_keyword: *** 15027,15032 **** --- 15033,15039 ---- | GENERATED | GLOBAL | GRANTED + | GROUPS | HANDLER | HEADER_P | HOLD *************** unreserved_keyword: *** 15092,15097 **** --- 15099,15105 ---- | OPTION | OPTIONS | ORDINALITY + | OTHERS | OVER | OVERRIDING | OWNED *************** unreserved_keyword: *** 15182,15187 **** --- 15190,15196 ---- | TEMPLATE | TEMPORARY | TEXT_P + | TIES | TRANSACTION | TRANSFORM | TRIGGER diff --git a/src/backend/parser/parse_agg.c b/src/backend/parser/parse_agg.c index 6a9f1b0..7471394 100644 *** a/src/backend/parser/parse_agg.c --- b/src/backend/parser/parse_agg.c *************** check_agglevels_and_constraints(ParseSta *** 420,425 **** --- 420,432 ---- err = _("grouping operations are not allowed in window ROWS"); break; + case EXPR_KIND_WINDOW_FRAME_GROUPS: + if (isAgg) + err = _("aggregate functions are not allowed in window GROUPS"); + else + err = _("grouping operations are not allowed in window GROUPS"); + + break; case EXPR_KIND_SELECT_TARGET: /* okay */ break; *************** transformWindowFuncCall(ParseState *psta *** 835,840 **** --- 842,848 ---- case EXPR_KIND_WINDOW_ORDER: case EXPR_KIND_WINDOW_FRAME_RANGE: case EXPR_KIND_WINDOW_FRAME_ROWS: + case EXPR_KIND_WINDOW_FRAME_GROUPS: err = _("window functions are not allowed in window definitions"); break; case EXPR_KIND_SELECT_TARGET: diff --git a/src/backend/parser/parse_clause.c b/src/backend/parser/parse_clause.c index 406cd1d..949092b 100644 *** a/src/backend/parser/parse_clause.c --- b/src/backend/parser/parse_clause.c *************** *** 18,27 **** --- 18,30 ---- #include "miscadmin.h" #include "access/heapam.h" + #include "access/htup_details.h" + #include "access/nbtree.h" #include "access/tsmapi.h" #include "catalog/catalog.h" #include "catalog/heap.h" #include "catalog/pg_am.h" + #include "catalog/pg_amproc.h" #include "catalog/pg_collation.h" #include "catalog/pg_constraint_fn.h" #include "catalog/pg_type.h" *************** *** 43,50 **** --- 46,56 ---- #include "parser/parse_target.h" #include "parser/parse_type.h" #include "rewrite/rewriteManip.h" + #include "utils/builtins.h" #include "utils/guc.h" + #include "utils/catcache.h" #include "utils/lsyscache.h" + #include "utils/syscache.h" #include "utils/rel.h" *************** static List *addTargetToGroupList(ParseS *** 95,100 **** --- 101,107 ---- List *grouplist, List *targetlist, int location); static WindowClause *findWindowClause(List *wclist, const char *name); static Node *transformFrameOffset(ParseState *pstate, int frameOptions, + Oid rangeopfamily, Oid rangeopcintype, Oid *inRangeFunc, Node *clause); *************** transformWindowDefinitions(ParseState *p *** 2627,2632 **** --- 2634,2641 ---- WindowClause *refwc = NULL; List *partitionClause; List *orderClause; + Oid rangeopfamily = InvalidOid; + Oid rangeopcintype = InvalidOid; WindowClause *wc; winref++; *************** transformWindowDefinitions(ParseState *p *** 2753,2762 **** --- 2762,2810 ---- parser_errposition(pstate, windef->location))); } wc->frameOptions = windef->frameOptions; + + /* + * RANGE with "value PRECEDING" or "value FOLLOWING" requires exactly + * one ORDER BY column; check that and get its sort opfamily info. + */ + if ((wc->frameOptions & FRAMEOPTION_RANGE) && + (wc->frameOptions & (FRAMEOPTION_START_VALUE_PRECEDING | + FRAMEOPTION_END_VALUE_PRECEDING | + FRAMEOPTION_START_VALUE_FOLLOWING | + FRAMEOPTION_END_VALUE_FOLLOWING))) + { + SortGroupClause *sortcl; + Node *sortkey; + int16 rangestrategy; + + if (list_length(wc->orderClause) != 1) + ereport(ERROR, + (errcode(ERRCODE_WINDOWING_ERROR), + errmsg("RANGE with value PRECEDING/FOLLOWING requires exactly one ORDER BY column"), + parser_errposition(pstate, windef->location))); + sortcl = castNode(SortGroupClause, linitial(wc->orderClause)); + sortkey = get_sortgroupclause_expr(sortcl, *targetlist); + /* Find the sort operator in pg_amop */ + if (!get_ordering_op_properties(sortcl->sortop, + &rangeopfamily, + &rangeopcintype, + &rangestrategy)) + elog(ERROR, "operator %u is not a valid ordering operator", + sortcl->sortop); + /* Record properties of sort ordering */ + wc->inRangeColl = exprCollation(sortkey); + wc->inRangeAsc = (rangestrategy == BTLessStrategyNumber); + wc->inRangeNullsFirst = sortcl->nulls_first; + } + /* Process frame offset expressions */ wc->startOffset = transformFrameOffset(pstate, wc->frameOptions, + rangeopfamily, rangeopcintype, + &wc->startInRangeFunc, windef->startOffset); wc->endOffset = transformFrameOffset(pstate, wc->frameOptions, + rangeopfamily, rangeopcintype, + &wc->endInRangeFunc, windef->endOffset); wc->winref = winref; *************** findWindowClause(List *wclist, const cha *** 3489,3501 **** /* * transformFrameOffset * Process a window frame offset expression */ static Node * ! transformFrameOffset(ParseState *pstate, int frameOptions, Node *clause) { const char *constructName = NULL; Node *node; /* Quick exit if no offset expression */ if (clause == NULL) return NULL; --- 3537,3560 ---- /* * transformFrameOffset * Process a window frame offset expression + * + * In RANGE mode, rangeopfamily is the sort opfamily for the input ORDER BY + * column, and rangeopcintype is the input data type the sort operator is + * registered with. We expect the in_range function to be registered with + * that same type. (In binary-compatible cases, it might be different from + * the input column's actual type, so we can't use that for the lookups.) + * We'll return the OID of the in_range function to *inRangeFunc. */ static Node * ! transformFrameOffset(ParseState *pstate, int frameOptions, ! Oid rangeopfamily, Oid rangeopcintype, Oid *inRangeFunc, ! Node *clause) { const char *constructName = NULL; Node *node; + *inRangeFunc = InvalidOid; /* default result */ + /* Quick exit if no offset expression */ if (clause == NULL) return NULL; *************** transformFrameOffset(ParseState *pstate, *** 3513,3528 **** } else if (frameOptions & FRAMEOPTION_RANGE) { /* Transform the raw expression tree */ node = transformExpr(pstate, clause, EXPR_KIND_WINDOW_FRAME_RANGE); /* ! * this needs a lot of thought to decide how to support in the context ! * of Postgres' extensible datatype framework */ constructName = "RANGE"; ! /* error was already thrown by gram.y, this is just a backstop */ ! elog(ERROR, "window frame with value offset is not implemented"); } else { --- 3572,3676 ---- } else if (frameOptions & FRAMEOPTION_RANGE) { + /* + * We must look up the in_range support function that's to be used, + * possibly choosing one of several, and coerce the "offset" value to + * the appropriate input type. + */ + Oid nodeType; + Oid preferredType; + int nfuncs = 0; + int nmatches = 0; + Oid selectedType = InvalidOid; + Oid selectedFunc = InvalidOid; + CatCList *proclist; + int i; + /* Transform the raw expression tree */ node = transformExpr(pstate, clause, EXPR_KIND_WINDOW_FRAME_RANGE); + nodeType = exprType(node); /* ! * If there are multiple candidates, we'll prefer the one that exactly ! * matches nodeType; or if nodeType is as yet unknown, prefer the one ! * that exactly matches the sort column type. (The second rule is ! * like what we do for "known_type operator unknown".) ! */ ! preferredType = (nodeType != UNKNOWNOID) ? nodeType : rangeopcintype; ! ! /* Find the in_range support functions applicable to this case */ ! proclist = SearchSysCacheList2(AMPROCNUM, ! ObjectIdGetDatum(rangeopfamily), ! ObjectIdGetDatum(rangeopcintype)); ! for (i = 0; i < proclist->n_members; i++) ! { ! HeapTuple proctup = &proclist->members[i]->tuple; ! Form_pg_amproc procform = (Form_pg_amproc) GETSTRUCT(proctup); ! ! /* The search will find all support proc types; ignore others */ ! if (procform->amprocnum != BTINRANGE_PROC) ! continue; ! nfuncs++; ! ! /* Ignore function if given value can't be coerced to that type */ ! if (!can_coerce_type(1, &nodeType, &procform->amprocrighttype, ! COERCION_IMPLICIT)) ! continue; ! nmatches++; ! ! /* Remember preferred match, or any match if didn't find that */ ! if (selectedType != preferredType) ! { ! selectedType = procform->amprocrighttype; ! selectedFunc = procform->amproc; ! } ! } ! ReleaseCatCacheList(proclist); ! ! /* ! * Throw error if needed. It seems worth taking the trouble to ! * distinguish "no support at all" from "you didn't match any ! * available offset type". */ + if (nfuncs == 0) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("RANGE with value PRECEDING/FOLLOWING is not supported for column type %s", + format_type_be(rangeopcintype)), + parser_errposition(pstate, exprLocation(node)))); + if (nmatches == 0) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("RANGE with value PRECEDING/FOLLOWING is not supported for column type %s and offset type %s", + format_type_be(rangeopcintype), + format_type_be(nodeType)), + errhint("Cast the offset value to an appropriate type."), + parser_errposition(pstate, exprLocation(node)))); + if (nmatches != 1 && selectedType != preferredType) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("RANGE with value PRECEDING/FOLLOWING has multiple interpretations for column type %s and offset type %s", + format_type_be(rangeopcintype), + format_type_be(nodeType)), + errhint("Cast the offset value to the exact intended type."), + parser_errposition(pstate, exprLocation(node)))); + + /* OK, coerce the offset to the right type */ constructName = "RANGE"; ! node = coerce_to_specific_type(pstate, node, ! selectedType, constructName); ! *inRangeFunc = selectedFunc; ! } ! else if (frameOptions & FRAMEOPTION_GROUPS) ! { ! /* Transform the raw expression tree */ ! node = transformExpr(pstate, clause, EXPR_KIND_WINDOW_FRAME_GROUPS); ! ! /* ! * Like LIMIT clause, simply coerce to int8 ! */ ! constructName = "GROUPS"; ! node = coerce_to_specific_type(pstate, node, INT8OID, constructName); } else { diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c index b2f5e46..d45926f 100644 *** a/src/backend/parser/parse_expr.c --- b/src/backend/parser/parse_expr.c *************** transformSubLink(ParseState *pstate, Sub *** 1805,1810 **** --- 1805,1811 ---- case EXPR_KIND_WINDOW_ORDER: case EXPR_KIND_WINDOW_FRAME_RANGE: case EXPR_KIND_WINDOW_FRAME_ROWS: + case EXPR_KIND_WINDOW_FRAME_GROUPS: case EXPR_KIND_SELECT_TARGET: case EXPR_KIND_INSERT_TARGET: case EXPR_KIND_UPDATE_SOURCE: *************** ParseExprKindName(ParseExprKind exprKind *** 3428,3433 **** --- 3429,3436 ---- return "window RANGE"; case EXPR_KIND_WINDOW_FRAME_ROWS: return "window ROWS"; + case EXPR_KIND_WINDOW_FRAME_GROUPS: + return "window GROUPS"; case EXPR_KIND_SELECT_TARGET: return "SELECT"; case EXPR_KIND_INSERT_TARGET: diff --git a/src/backend/parser/parse_func.c b/src/backend/parser/parse_func.c index ffae0f3..4a7bc77 100644 *** a/src/backend/parser/parse_func.c --- b/src/backend/parser/parse_func.c *************** check_srf_call_placement(ParseState *pst *** 2227,2232 **** --- 2227,2233 ---- break; case EXPR_KIND_WINDOW_FRAME_RANGE: case EXPR_KIND_WINDOW_FRAME_ROWS: + case EXPR_KIND_WINDOW_FRAME_GROUPS: err = _("set-returning functions are not allowed in window definitions"); break; case EXPR_KIND_SELECT_TARGET: diff --git a/src/backend/utils/adt/date.c b/src/backend/utils/adt/date.c index 747ef49..eea2904 100644 *** a/src/backend/utils/adt/date.c --- b/src/backend/utils/adt/date.c *************** timestamptz_cmp_date(PG_FUNCTION_ARGS) *** 1011,1016 **** --- 1011,1044 ---- PG_RETURN_INT32(timestamptz_cmp_internal(dt1, dt2)); } + /* + * in_range support function for date. + * + * We implement this by promoting the dates to timestamp (without time zone) + * and then using the timestamp-and-interval in_range function. + */ + Datum + in_range_date_interval(PG_FUNCTION_ARGS) + { + DateADT val = PG_GETARG_DATEADT(0); + DateADT base = PG_GETARG_DATEADT(1); + Interval *offset = PG_GETARG_INTERVAL_P(2); + bool sub = PG_GETARG_BOOL(3); + bool less = PG_GETARG_BOOL(4); + Timestamp valStamp; + Timestamp baseStamp; + + valStamp = date2timestamp(val); + baseStamp = date2timestamp(base); + + return DirectFunctionCall5(in_range_timestamp_interval, + TimestampGetDatum(valStamp), + TimestampGetDatum(baseStamp), + IntervalPGetDatum(offset), + BoolGetDatum(sub), + BoolGetDatum(less)); + } + /* Add an interval to a date, giving a new date. * Must handle both positive and negative intervals. *************** time_mi_interval(PG_FUNCTION_ARGS) *** 1842,1847 **** --- 1870,1914 ---- PG_RETURN_TIMEADT(result); } + /* + * in_range support function for time. + */ + Datum + in_range_time_interval(PG_FUNCTION_ARGS) + { + TimeADT val = PG_GETARG_TIMEADT(0); + TimeADT base = PG_GETARG_TIMEADT(1); + Interval *offset = PG_GETARG_INTERVAL_P(2); + bool sub = PG_GETARG_BOOL(3); + bool less = PG_GETARG_BOOL(4); + TimeADT sum; + + /* + * Like time_pl_interval/time_mi_interval, we disregard the month and day + * fields of the offset. So our test for negative should too. + */ + if (offset->time < 0) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PRECEDING_FOLLOWING_SIZE), + errmsg("invalid preceding or following size in window function"))); + + /* + * We can't use time_pl_interval/time_mi_interval here, because their + * wraparound behavior would give wrong (or at least undesirable) answers. + * Fortunately the equivalent non-wrapping behavior is trivial, especially + * since we don't worry about integer overflow. + */ + if (sub) + sum = base - offset->time; + else + sum = base + offset->time; + + if (less) + PG_RETURN_BOOL(val <= sum); + else + PG_RETURN_BOOL(val >= sum); + } + /* time_part() * Extract specified field from time type. *************** timetz_mi_interval(PG_FUNCTION_ARGS) *** 2305,2310 **** --- 2372,2417 ---- PG_RETURN_TIMETZADT_P(result); } + /* + * in_range support function for timetz. + */ + Datum + in_range_timetz_interval(PG_FUNCTION_ARGS) + { + TimeTzADT *val = PG_GETARG_TIMETZADT_P(0); + TimeTzADT *base = PG_GETARG_TIMETZADT_P(1); + Interval *offset = PG_GETARG_INTERVAL_P(2); + bool sub = PG_GETARG_BOOL(3); + bool less = PG_GETARG_BOOL(4); + TimeTzADT sum; + + /* + * Like timetz_pl_interval/timetz_mi_interval, we disregard the month and + * day fields of the offset. So our test for negative should too. + */ + if (offset->time < 0) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PRECEDING_FOLLOWING_SIZE), + errmsg("invalid preceding or following size in window function"))); + + /* + * We can't use timetz_pl_interval/timetz_mi_interval here, because their + * wraparound behavior would give wrong (or at least undesirable) answers. + * Fortunately the equivalent non-wrapping behavior is trivial, especially + * since we don't worry about integer overflow. + */ + if (sub) + sum.time = base->time - offset->time; + else + sum.time = base->time + offset->time; + sum.zone = base->zone; + + if (less) + PG_RETURN_BOOL(timetz_cmp_internal(val, &sum) <= 0); + else + PG_RETURN_BOOL(timetz_cmp_internal(val, &sum) >= 0); + } + /* overlaps_timetz() --- implements the SQL OVERLAPS operator. * * Algorithm is per SQL spec. This is much harder than you'd think diff --git a/src/backend/utils/adt/int.c b/src/backend/utils/adt/int.c index 7352908..559c365 100644 *** a/src/backend/utils/adt/int.c --- b/src/backend/utils/adt/int.c *************** int42ge(PG_FUNCTION_ARGS) *** 585,590 **** --- 585,742 ---- PG_RETURN_BOOL(arg1 >= arg2); } + + /*---------------------------------------------------------- + * in_range functions for int4 and int2, + * including cross-data-type comparisons. + * + * Note: we provide separate intN_int8 functions for performance + * reasons. This forces also providing intN_int2, else cases with a + * smallint offset value would fail to resolve which function to use. + * But that's an unlikely situation, so don't duplicate code for it. + *---------------------------------------------------------*/ + + Datum + in_range_int4_int4(PG_FUNCTION_ARGS) + { + int32 val = PG_GETARG_INT32(0); + int32 base = PG_GETARG_INT32(1); + int32 offset = PG_GETARG_INT32(2); + bool sub = PG_GETARG_BOOL(3); + bool less = PG_GETARG_BOOL(4); + int32 sum; + + if (offset < 0) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PRECEDING_FOLLOWING_SIZE), + errmsg("invalid preceding or following size in window function"))); + + if (sub) + offset = -offset; /* cannot overflow */ + + if (unlikely(pg_add_s32_overflow(base, offset, &sum))) + { + /* + * If sub is false, the true sum is surely more than val, so correct + * answer is the same as "less". If sub is true, the true sum is + * surely less than val, so the answer is "!less". + */ + PG_RETURN_BOOL(sub ? !less : less); + } + + if (less) + PG_RETURN_BOOL(val <= sum); + else + PG_RETURN_BOOL(val >= sum); + } + + Datum + in_range_int4_int2(PG_FUNCTION_ARGS) + { + /* Doesn't seem worth duplicating code for, so just invoke int4_int4 */ + return DirectFunctionCall5(in_range_int4_int4, + PG_GETARG_DATUM(0), + PG_GETARG_DATUM(1), + Int32GetDatum((int32) PG_GETARG_INT16(2)), + PG_GETARG_DATUM(3), + PG_GETARG_DATUM(4)); + } + + Datum + in_range_int4_int8(PG_FUNCTION_ARGS) + { + /* We must do all the math in int64 */ + int64 val = (int64) PG_GETARG_INT32(0); + int64 base = (int64) PG_GETARG_INT32(1); + int64 offset = PG_GETARG_INT64(2); + bool sub = PG_GETARG_BOOL(3); + bool less = PG_GETARG_BOOL(4); + int64 sum; + + if (offset < 0) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PRECEDING_FOLLOWING_SIZE), + errmsg("invalid preceding or following size in window function"))); + + if (sub) + offset = -offset; /* cannot overflow */ + + if (unlikely(pg_add_s64_overflow(base, offset, &sum))) + { + /* + * If sub is false, the true sum is surely more than val, so correct + * answer is the same as "less". If sub is true, the true sum is + * surely less than val, so the answer is "!less". + */ + PG_RETURN_BOOL(sub ? !less : less); + } + + if (less) + PG_RETURN_BOOL(val <= sum); + else + PG_RETURN_BOOL(val >= sum); + } + + Datum + in_range_int2_int4(PG_FUNCTION_ARGS) + { + /* We must do all the math in int32 */ + int32 val = (int32) PG_GETARG_INT16(0); + int32 base = (int32) PG_GETARG_INT16(1); + int32 offset = PG_GETARG_INT32(2); + bool sub = PG_GETARG_BOOL(3); + bool less = PG_GETARG_BOOL(4); + int32 sum; + + if (offset < 0) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PRECEDING_FOLLOWING_SIZE), + errmsg("invalid preceding or following size in window function"))); + + if (sub) + offset = -offset; /* cannot overflow */ + + if (unlikely(pg_add_s32_overflow(base, offset, &sum))) + { + /* + * If sub is false, the true sum is surely more than val, so correct + * answer is the same as "less". If sub is true, the true sum is + * surely less than val, so the answer is "!less". + */ + PG_RETURN_BOOL(sub ? !less : less); + } + + if (less) + PG_RETURN_BOOL(val <= sum); + else + PG_RETURN_BOOL(val >= sum); + } + + Datum + in_range_int2_int2(PG_FUNCTION_ARGS) + { + /* Doesn't seem worth duplicating code for, so just invoke int2_int4 */ + return DirectFunctionCall5(in_range_int2_int4, + PG_GETARG_DATUM(0), + PG_GETARG_DATUM(1), + Int32GetDatum((int32) PG_GETARG_INT16(2)), + PG_GETARG_DATUM(3), + PG_GETARG_DATUM(4)); + } + + Datum + in_range_int2_int8(PG_FUNCTION_ARGS) + { + /* Doesn't seem worth duplicating code for, so just invoke int4_int8 */ + return DirectFunctionCall5(in_range_int4_int8, + Int32GetDatum((int32) PG_GETARG_INT16(0)), + Int32GetDatum((int32) PG_GETARG_INT16(1)), + PG_GETARG_DATUM(2), + PG_GETARG_DATUM(3), + PG_GETARG_DATUM(4)); + } + + /* * int[24]pl - returns arg1 + arg2 * int[24]mi - returns arg1 - arg2 diff --git a/src/backend/utils/adt/int8.c b/src/backend/utils/adt/int8.c index ae6a468..e6bae68 100644 *** a/src/backend/utils/adt/int8.c --- b/src/backend/utils/adt/int8.c *************** *** 14,20 **** #include "postgres.h" #include ! #include /* for _isnan */ #include #include --- 14,20 ---- #include "postgres.h" #include ! #include /* for _isnan */ #include #include *************** int28ge(PG_FUNCTION_ARGS) *** 469,474 **** --- 469,514 ---- PG_RETURN_BOOL(val1 >= val2); } + /* + * in_range support function for int8. + * + * Note: we needn't supply int8_int4 or int8_int2 variants, as implicit + * coercion of the offset value takes care of those scenarios just as well. + */ + Datum + in_range_int8_int8(PG_FUNCTION_ARGS) + { + int64 val = PG_GETARG_INT64(0); + int64 base = PG_GETARG_INT64(1); + int64 offset = PG_GETARG_INT64(2); + bool sub = PG_GETARG_BOOL(3); + bool less = PG_GETARG_BOOL(4); + int64 sum; + + if (offset < 0) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PRECEDING_FOLLOWING_SIZE), + errmsg("invalid preceding or following size in window function"))); + + if (sub) + offset = -offset; /* cannot overflow */ + + if (unlikely(pg_add_s64_overflow(base, offset, &sum))) + { + /* + * If sub is false, the true sum is surely more than val, so correct + * answer is the same as "less". If sub is true, the true sum is + * surely less than val, so the answer is "!less". + */ + PG_RETURN_BOOL(sub ? !less : less); + } + + if (less) + PG_RETURN_BOOL(val <= sum); + else + PG_RETURN_BOOL(val >= sum); + } + /*---------------------------------------------------------- * Arithmetic operators on 64-bit integers. diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c index c5f5a1c..eb0d481 100644 *** a/src/backend/utils/adt/ruleutils.c --- b/src/backend/utils/adt/ruleutils.c *************** get_rule_windowspec(WindowClause *wc, Li *** 5877,5882 **** --- 5877,5884 ---- appendStringInfoString(buf, "RANGE "); else if (wc->frameOptions & FRAMEOPTION_ROWS) appendStringInfoString(buf, "ROWS "); + else if (wc->frameOptions & FRAMEOPTION_GROUPS) + appendStringInfoString(buf, "GROUPS "); else Assert(false); if (wc->frameOptions & FRAMEOPTION_BETWEEN) *************** get_rule_windowspec(WindowClause *wc, Li *** 5917,5922 **** --- 5919,5930 ---- else Assert(false); } + if (wc->frameOptions & FRAMEOPTION_EXCLUDE_CURRENT_ROW) + appendStringInfoString(buf, "EXCLUDE CURRENT ROW "); + else if (wc->frameOptions & FRAMEOPTION_EXCLUDE_GROUP) + appendStringInfoString(buf, "EXCLUDE GROUP "); + else if (wc->frameOptions & FRAMEOPTION_EXCLUDE_TIES) + appendStringInfoString(buf, "EXCLUDE TIES "); /* we will now have a trailing space; remove it */ buf->len--; } diff --git a/src/backend/utils/adt/timestamp.c b/src/backend/utils/adt/timestamp.c index e6a1eed..103f91a 100644 *** a/src/backend/utils/adt/timestamp.c --- b/src/backend/utils/adt/timestamp.c *************** interval_div(PG_FUNCTION_ARGS) *** 3258,3263 **** --- 3258,3367 ---- PG_RETURN_INTERVAL_P(result); } + + /* + * in_range support functions for timestamps and intervals. + * + * Per SQL spec, we support these with interval as the offset type. + * The spec's restriction that the offset not be negative is a bit hard to + * decipher for intervals, but we choose to interpret it the same as our + * interval comparison operators would. + */ + + Datum + in_range_timestamptz_interval(PG_FUNCTION_ARGS) + { + TimestampTz val = PG_GETARG_TIMESTAMPTZ(0); + TimestampTz base = PG_GETARG_TIMESTAMPTZ(1); + Interval *offset = PG_GETARG_INTERVAL_P(2); + bool sub = PG_GETARG_BOOL(3); + bool less = PG_GETARG_BOOL(4); + TimestampTz sum; + + if (int128_compare(interval_cmp_value(offset), int64_to_int128(0)) < 0) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PRECEDING_FOLLOWING_SIZE), + errmsg("invalid preceding or following size in window function"))); + + /* We don't currently bother to avoid overflow hazards here */ + if (sub) + sum = DatumGetTimestampTz(DirectFunctionCall2(timestamptz_mi_interval, + TimestampTzGetDatum(base), + IntervalPGetDatum(offset))); + else + sum = DatumGetTimestampTz(DirectFunctionCall2(timestamptz_pl_interval, + TimestampTzGetDatum(base), + IntervalPGetDatum(offset))); + + if (less) + PG_RETURN_BOOL(val <= sum); + else + PG_RETURN_BOOL(val >= sum); + } + + Datum + in_range_timestamp_interval(PG_FUNCTION_ARGS) + { + Timestamp val = PG_GETARG_TIMESTAMP(0); + Timestamp base = PG_GETARG_TIMESTAMP(1); + Interval *offset = PG_GETARG_INTERVAL_P(2); + bool sub = PG_GETARG_BOOL(3); + bool less = PG_GETARG_BOOL(4); + Timestamp sum; + + if (int128_compare(interval_cmp_value(offset), int64_to_int128(0)) < 0) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PRECEDING_FOLLOWING_SIZE), + errmsg("invalid preceding or following size in window function"))); + + /* We don't currently bother to avoid overflow hazards here */ + if (sub) + sum = DatumGetTimestamp(DirectFunctionCall2(timestamp_mi_interval, + TimestampGetDatum(base), + IntervalPGetDatum(offset))); + else + sum = DatumGetTimestamp(DirectFunctionCall2(timestamp_pl_interval, + TimestampGetDatum(base), + IntervalPGetDatum(offset))); + + if (less) + PG_RETURN_BOOL(val <= sum); + else + PG_RETURN_BOOL(val >= sum); + } + + Datum + in_range_interval_interval(PG_FUNCTION_ARGS) + { + Interval *val = PG_GETARG_INTERVAL_P(0); + Interval *base = PG_GETARG_INTERVAL_P(1); + Interval *offset = PG_GETARG_INTERVAL_P(2); + bool sub = PG_GETARG_BOOL(3); + bool less = PG_GETARG_BOOL(4); + Interval *sum; + + if (int128_compare(interval_cmp_value(offset), int64_to_int128(0)) < 0) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PRECEDING_FOLLOWING_SIZE), + errmsg("invalid preceding or following size in window function"))); + + /* We don't currently bother to avoid overflow hazards here */ + if (sub) + sum = DatumGetIntervalP(DirectFunctionCall2(interval_mi, + IntervalPGetDatum(base), + IntervalPGetDatum(offset))); + else + sum = DatumGetIntervalP(DirectFunctionCall2(interval_pl, + IntervalPGetDatum(base), + IntervalPGetDatum(offset))); + + if (less) + PG_RETURN_BOOL(interval_cmp_internal(val, sum) <= 0); + else + PG_RETURN_BOOL(interval_cmp_internal(val, sum) >= 0); + } + + /* * interval_accum, interval_accum_inv, and interval_avg implement the * AVG(interval) aggregate. diff --git a/src/backend/utils/errcodes.txt b/src/backend/utils/errcodes.txt index 1475bfe..9871d1e 100644 *** a/src/backend/utils/errcodes.txt --- b/src/backend/utils/errcodes.txt *************** Section: Class 22 - Data Exception *** 177,182 **** --- 177,183 ---- 22P06 E ERRCODE_NONSTANDARD_USE_OF_ESCAPE_CHARACTER nonstandard_use_of_escape_character 22010 E ERRCODE_INVALID_INDICATOR_PARAMETER_VALUE invalid_indicator_parameter_value 22023 E ERRCODE_INVALID_PARAMETER_VALUE invalid_parameter_value + 22013 E ERRCODE_INVALID_PRECEDING_FOLLOWING_SIZE invalid_preceding_following_size 2201B E ERRCODE_INVALID_REGULAR_EXPRESSION invalid_regular_expression 2201W E ERRCODE_INVALID_ROW_COUNT_IN_LIMIT_CLAUSE invalid_row_count_in_limit_clause 2201X E ERRCODE_INVALID_ROW_COUNT_IN_RESULT_OFFSET_CLAUSE invalid_row_count_in_result_offset_clause diff --git a/src/include/access/nbtree.h b/src/include/access/nbtree.h index 0f6a401..6680709 100644 *** a/src/include/access/nbtree.h --- b/src/include/access/nbtree.h *************** typedef struct BTMetaPageData *** 225,235 **** * To facilitate accelerated sorting, an operator class may choose to * offer a second procedure (BTSORTSUPPORT_PROC). For full details, see * src/include/utils/sortsupport.h. */ #define BTORDER_PROC 1 #define BTSORTSUPPORT_PROC 2 ! #define BTNProcs 2 /* * We need to be able to tell the difference between read and write --- 225,241 ---- * To facilitate accelerated sorting, an operator class may choose to * offer a second procedure (BTSORTSUPPORT_PROC). For full details, see * src/include/utils/sortsupport.h. + * + * To support window frames defined by "RANGE value PRECEDING/FOLLOWING", + * an operator class may choose to offer a third amproc procedure + * (BTINRANGE_PROC), independently of whether it offers sortsupport. + * For full details, see src/backend/access/nbtree/README. */ #define BTORDER_PROC 1 #define BTSORTSUPPORT_PROC 2 ! #define BTINRANGE_PROC 3 ! #define BTNProcs 3 /* * We need to be able to tell the difference between read and write diff --git a/src/include/catalog/pg_amproc.h b/src/include/catalog/pg_amproc.h index f545a05..c3d0ff7 100644 *** a/src/include/catalog/pg_amproc.h --- b/src/include/catalog/pg_amproc.h *************** DATA(insert ( 434 1184 1184 1 1314 )); *** 96,101 **** --- 96,104 ---- DATA(insert ( 434 1184 1184 2 3137 )); DATA(insert ( 434 1184 1082 1 2383 )); DATA(insert ( 434 1184 1114 1 2533 )); + DATA(insert ( 434 1082 1186 3 4133 )); + DATA(insert ( 434 1114 1186 3 4134 )); + DATA(insert ( 434 1184 1186 3 4135 )); DATA(insert ( 1970 700 700 1 354 )); DATA(insert ( 1970 700 700 2 3132 )); DATA(insert ( 1970 700 701 1 2194 )); *************** DATA(insert ( 1976 21 21 1 350 )); *** 107,121 **** --- 110,132 ---- DATA(insert ( 1976 21 21 2 3129 )); DATA(insert ( 1976 21 23 1 2190 )); DATA(insert ( 1976 21 20 1 2192 )); + DATA(insert ( 1976 21 20 3 4130 )); + DATA(insert ( 1976 21 23 3 4131 )); + DATA(insert ( 1976 21 21 3 4132 )); DATA(insert ( 1976 23 23 1 351 )); DATA(insert ( 1976 23 23 2 3130 )); DATA(insert ( 1976 23 20 1 2188 )); DATA(insert ( 1976 23 21 1 2191 )); + DATA(insert ( 1976 23 20 3 4127 )); + DATA(insert ( 1976 23 23 3 4128 )); + DATA(insert ( 1976 23 21 3 4129 )); DATA(insert ( 1976 20 20 1 842 )); DATA(insert ( 1976 20 20 2 3131 )); DATA(insert ( 1976 20 23 1 2189 )); DATA(insert ( 1976 20 21 1 2193 )); + DATA(insert ( 1976 20 20 3 4126 )); DATA(insert ( 1982 1186 1186 1 1315 )); + DATA(insert ( 1982 1186 1186 3 4136 )); DATA(insert ( 1984 829 829 1 836 )); DATA(insert ( 1984 829 829 2 3359 )); DATA(insert ( 1986 19 19 1 359 )); *************** DATA(insert ( 1991 30 30 1 404 )); *** 128,134 **** --- 139,147 ---- DATA(insert ( 1994 25 25 1 360 )); DATA(insert ( 1994 25 25 2 3255 )); DATA(insert ( 1996 1083 1083 1 1107 )); + DATA(insert ( 1996 1083 1186 3 4137 )); DATA(insert ( 2000 1266 1266 1 1358 )); + DATA(insert ( 2000 1266 1186 3 4138 )); DATA(insert ( 2002 1562 1562 1 1672 )); DATA(insert ( 2095 25 25 1 2166 )); DATA(insert ( 2095 25 25 2 3332 )); diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h index f01648c..2a53213 100644 *** a/src/include/catalog/pg_proc.h --- b/src/include/catalog/pg_proc.h *************** DATA(insert OID = 381 ( bttintervalcmp *** 647,652 **** --- 647,666 ---- DESCR("less-equal-greater"); DATA(insert OID = 382 ( btarraycmp PGNSP PGUID 12 1 0 0 0 f f f f t f i s 2 0 23 "2277 2277" _null_ _null_ _null_ _null_ _null_ btarraycmp _null_ _null_ _null_ )); DESCR("less-equal-greater"); + DATA(insert OID = 4126 ( in_range PGNSP PGUID 12 1 0 0 0 f f f f t f i s 5 0 16 "20 20 20 16 16" _null_ _null_ _null_ _null_ _null_ in_range_int8_int8 _null_ _null_ _null_ )); + DESCR("window RANGE support"); + DATA(insert OID = 4127 ( in_range PGNSP PGUID 12 1 0 0 0 f f f f t f i s 5 0 16 "23 23 20 16 16" _null_ _null_ _null_ _null_ _null_ in_range_int4_int8 _null_ _null_ _null_ )); + DESCR("window RANGE support"); + DATA(insert OID = 4128 ( in_range PGNSP PGUID 12 1 0 0 0 f f f f t f i s 5 0 16 "23 23 23 16 16" _null_ _null_ _null_ _null_ _null_ in_range_int4_int4 _null_ _null_ _null_ )); + DESCR("window RANGE support"); + DATA(insert OID = 4129 ( in_range PGNSP PGUID 12 1 0 0 0 f f f f t f i s 5 0 16 "23 23 21 16 16" _null_ _null_ _null_ _null_ _null_ in_range_int4_int2 _null_ _null_ _null_ )); + DESCR("window RANGE support"); + DATA(insert OID = 4130 ( in_range PGNSP PGUID 12 1 0 0 0 f f f f t f i s 5 0 16 "21 21 20 16 16" _null_ _null_ _null_ _null_ _null_ in_range_int2_int8 _null_ _null_ _null_ )); + DESCR("window RANGE support"); + DATA(insert OID = 4131 ( in_range PGNSP PGUID 12 1 0 0 0 f f f f t f i s 5 0 16 "21 21 23 16 16" _null_ _null_ _null_ _null_ _null_ in_range_int2_int4 _null_ _null_ _null_ )); + DESCR("window RANGE support"); + DATA(insert OID = 4132 ( in_range PGNSP PGUID 12 1 0 0 0 f f f f t f i s 5 0 16 "21 21 21 16 16" _null_ _null_ _null_ _null_ _null_ in_range_int2_int2 _null_ _null_ _null_ )); + DESCR("window RANGE support"); DATA(insert OID = 361 ( lseg_distance PGNSP PGUID 12 1 0 0 0 f f f f t f i s 2 0 701 "601 601" _null_ _null_ _null_ _null_ _null_ lseg_distance _null_ _null_ _null_ )); DATA(insert OID = 362 ( lseg_interpt PGNSP PGUID 12 1 0 0 0 f f f f t f i s 2 0 600 "601 601" _null_ _null_ _null_ _null_ _null_ lseg_interpt _null_ _null_ _null_ )); *************** DATA(insert OID = 1092 ( date_cmp P *** 1216,1221 **** --- 1230,1237 ---- DESCR("less-equal-greater"); DATA(insert OID = 3136 ( date_sortsupport PGNSP PGUID 12 1 0 0 0 f f f f t f i s 1 0 2278 "2281" _null_ _null_ _null_ _null_ _null_ date_sortsupport _null_ _null_ _null_ )); DESCR("sort support"); + DATA(insert OID = 4133 ( in_range PGNSP PGUID 12 1 0 0 0 f f f f t f i s 5 0 16 "1082 1082 1186 16 16" _null_ _null_ _null_ _null_ _null_ in_range_date_interval _null_ _null_ _null_ )); + DESCR("window RANGE support"); /* OIDS 1100 - 1199 */ *************** DATA(insert OID = 2045 ( timestamp_cmp *** 3141,3146 **** --- 3157,3174 ---- DESCR("less-equal-greater"); DATA(insert OID = 3137 ( timestamp_sortsupport PGNSP PGUID 12 1 0 0 0 f f f f t f i s 1 0 2278 "2281" _null_ _null_ _null_ _null_ _null_ timestamp_sortsupport _null_ _null_ _null_ )); DESCR("sort support"); + + DATA(insert OID = 4134 ( in_range PGNSP PGUID 12 1 0 0 0 f f f f t f i s 5 0 16 "1114 1114 1186 16 16" _null_ _null_ _null_ _null_ _null_ in_range_timestamp_interval _null_ _null_ _null_ )); + DESCR("window RANGE support"); + DATA(insert OID = 4135 ( in_range PGNSP PGUID 12 1 0 0 0 f f f f t f s s 5 0 16 "1184 1184 1186 16 16" _null_ _null_ _null_ _null_ _null_ in_range_timestamptz_interval _null_ _null_ _null_ )); + DESCR("window RANGE support"); + DATA(insert OID = 4136 ( in_range PGNSP PGUID 12 1 0 0 0 f f f f t f i s 5 0 16 "1186 1186 1186 16 16" _null_ _null_ _null_ _null_ _null_ in_range_interval_interval _null_ _null_ _null_ )); + DESCR("window RANGE support"); + DATA(insert OID = 4137 ( in_range PGNSP PGUID 12 1 0 0 0 f f f f t f i s 5 0 16 "1083 1083 1186 16 16" _null_ _null_ _null_ _null_ _null_ in_range_time_interval _null_ _null_ _null_ )); + DESCR("window RANGE support"); + DATA(insert OID = 4138 ( in_range PGNSP PGUID 12 1 0 0 0 f f f f t f i s 5 0 16 "1266 1266 1186 16 16" _null_ _null_ _null_ _null_ _null_ in_range_timetz_interval _null_ _null_ _null_ )); + DESCR("window RANGE support"); + DATA(insert OID = 2046 ( time PGNSP PGUID 12 1 0 0 0 f f f f t f i s 1 0 1083 "1266" _null_ _null_ _null_ _null_ _null_ timetz_time _null_ _null_ _null_ )); DESCR("convert time with time zone to time"); DATA(insert OID = 2047 ( timetz PGNSP PGUID 12 1 0 0 0 f f f f t f s s 1 0 1266 "1083" _null_ _null_ _null_ _null_ _null_ time_timetz _null_ _null_ _null_ )); diff --git a/src/include/nodes/execnodes.h b/src/include/nodes/execnodes.h index a2a2a9f..6a0228c 100644 *** a/src/include/nodes/execnodes.h --- b/src/include/nodes/execnodes.h *************** typedef struct WindowAggState *** 1885,1895 **** FmgrInfo *partEqfunctions; /* equality funcs for partition columns */ 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 */ /* use struct pointer to avoid including windowapi.h here */ struct WindowObjectData *agg_winobj; /* winobj for aggregate fetches */ int64 aggregatedbase; /* start row for current aggregates */ --- 1885,1898 ---- FmgrInfo *partEqfunctions; /* equality funcs for partition columns */ FmgrInfo *ordEqfunctions; /* equality funcs for ordering columns */ Tuplestorestate *buffer; /* stores rows of current partition */ ! int current_ptr; /* read pointer # for current row */ ! int framehead_ptr; /* read pointer # for frame head, if used */ ! int frametail_ptr; /* read pointer # for frame tail, if used */ ! int grouptail_ptr; /* read pointer # for group tail, if used */ 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 (frame end+1) */ /* use struct pointer to avoid including windowapi.h here */ struct WindowObjectData *agg_winobj; /* winobj for aggregate fetches */ int64 aggregatedbase; /* start row for current aggregates */ *************** typedef struct WindowAggState *** 1901,1906 **** --- 1904,1923 ---- Datum startOffsetValue; /* result of startOffset evaluation */ Datum endOffsetValue; /* result of endOffset evaluation */ + /* these fields are used when processing RANGE value PRECEDING/FOLLOWING: */ + FmgrInfo startInRangeFunc; /* in_range function for startOffset */ + FmgrInfo endInRangeFunc; /* in_range function for endOffset */ + Oid inRangeColl; /* collation for in_range tests */ + bool inRangeAsc; /* use ASC sort order for in_range tests? */ + bool inRangeNullsFirst; /* nulls sort first for in_range tests? */ + + /* these fields are used in GROUPS mode: */ + int64 currentgroup; /* peer group # of current row in partition */ + int64 frameheadgroup; /* peer group # of frame head row */ + int64 frametailgroup; /* peer group # of frame tail row */ + int64 groupheadpos; /* current row's peer group head position */ + int64 grouptailpos; /* " " " " tail position (group end+1) */ + MemoryContext partcontext; /* context for partition-lifespan data */ MemoryContext aggcontext; /* shared context for aggregate working data */ MemoryContext curaggcontext; /* current aggregate's working data */ *************** typedef struct WindowAggState *** 1916,1924 **** --- 1933,1945 ---- * date for current row */ bool frametail_valid; /* true if frametailpos is known up to * date for current row */ + bool grouptail_valid; /* true if grouptailpos is known up to + * date for current row */ TupleTableSlot *first_part_slot; /* first tuple of current or next * partition */ + TupleTableSlot *framehead_slot; /* first tuple of current frame */ + TupleTableSlot *frametail_slot; /* first tuple after current frame */ /* temporary slots for tuples fetched back from tuplestore */ TupleTableSlot *agg_row_slot; diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h index a16de28..bbf358b 100644 *** a/src/include/nodes/parsenodes.h --- b/src/include/nodes/parsenodes.h *************** typedef struct WindowDef *** 499,525 **** * 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_START_VALUE_PRECEDING 0x00400 /* start is V. P. */ ! #define FRAMEOPTION_END_VALUE_PRECEDING 0x00800 /* end is V. P. */ ! #define FRAMEOPTION_START_VALUE_FOLLOWING 0x01000 /* start is V. F. */ ! #define FRAMEOPTION_END_VALUE_FOLLOWING 0x02000 /* end is V. F. */ #define FRAMEOPTION_START_VALUE \ (FRAMEOPTION_START_VALUE_PRECEDING | FRAMEOPTION_START_VALUE_FOLLOWING) #define FRAMEOPTION_END_VALUE \ (FRAMEOPTION_END_VALUE_PRECEDING | FRAMEOPTION_END_VALUE_FOLLOWING) #define FRAMEOPTION_DEFAULTS \ (FRAMEOPTION_RANGE | FRAMEOPTION_START_UNBOUNDED_PRECEDING | \ --- 499,531 ---- * 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. */ #define FRAMEOPTION_NONDEFAULT 0x00001 /* any specified? */ #define FRAMEOPTION_RANGE 0x00002 /* RANGE behavior */ #define FRAMEOPTION_ROWS 0x00004 /* ROWS behavior */ ! #define FRAMEOPTION_GROUPS 0x00008 /* GROUPS behavior */ ! #define FRAMEOPTION_BETWEEN 0x00010 /* BETWEEN given? */ ! #define FRAMEOPTION_START_UNBOUNDED_PRECEDING 0x00020 /* start is U. P. */ ! #define FRAMEOPTION_END_UNBOUNDED_PRECEDING 0x00040 /* (disallowed) */ ! #define FRAMEOPTION_START_UNBOUNDED_FOLLOWING 0x00080 /* (disallowed) */ ! #define FRAMEOPTION_END_UNBOUNDED_FOLLOWING 0x00100 /* end is U. F. */ ! #define FRAMEOPTION_START_CURRENT_ROW 0x00200 /* start is C. R. */ ! #define FRAMEOPTION_END_CURRENT_ROW 0x00400 /* end is C. R. */ ! #define FRAMEOPTION_START_VALUE_PRECEDING 0x00800 /* start is V. P. */ ! #define FRAMEOPTION_END_VALUE_PRECEDING 0x01000 /* end is V. P. */ ! #define FRAMEOPTION_START_VALUE_FOLLOWING 0x02000 /* start is V. F. */ ! #define FRAMEOPTION_END_VALUE_FOLLOWING 0x04000 /* end is V. F. */ ! #define FRAMEOPTION_EXCLUDE_CURRENT_ROW 0x08000 /* omit C.R. */ ! #define FRAMEOPTION_EXCLUDE_GROUP 0x10000 /* omit C.R. & peers */ ! #define FRAMEOPTION_EXCLUDE_TIES 0x20000 /* omit C.R.'s peers */ #define FRAMEOPTION_START_VALUE \ (FRAMEOPTION_START_VALUE_PRECEDING | FRAMEOPTION_START_VALUE_FOLLOWING) #define FRAMEOPTION_END_VALUE \ (FRAMEOPTION_END_VALUE_PRECEDING | FRAMEOPTION_END_VALUE_FOLLOWING) + #define FRAMEOPTION_EXCLUSION \ + (FRAMEOPTION_EXCLUDE_CURRENT_ROW | FRAMEOPTION_EXCLUDE_GROUP | \ + FRAMEOPTION_EXCLUDE_TIES) #define FRAMEOPTION_DEFAULTS \ (FRAMEOPTION_RANGE | FRAMEOPTION_START_UNBOUNDED_PRECEDING | \ *************** typedef struct GroupingSet *** 1277,1282 **** --- 1283,1291 ---- * if the clause originally came from WINDOW, and is NULL if it originally * was an OVER clause (but note that we collapse out duplicate OVERs). * partitionClause and orderClause are lists of SortGroupClause structs. + * If we have RANGE with value PRECEDING/FOLLOWING, the semantics of that are + * specified by startInRangeFunc/inRangeColl/inRangeAsc/inRangeNullsFirst + * for the start offset, or endInRangeFunc/inRange* for the end offset. * 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; *************** typedef struct WindowClause *** 1293,1298 **** --- 1302,1312 ---- int frameOptions; /* frame_clause options, see WindowDef */ Node *startOffset; /* expression for starting bound, if any */ Node *endOffset; /* expression for ending bound, if any */ + Oid startInRangeFunc; /* in_range function for startOffset */ + Oid endInRangeFunc; /* in_range function for endOffset */ + Oid inRangeColl; /* collation for in_range tests */ + bool inRangeAsc; /* use ASC sort order for in_range tests? */ + bool inRangeNullsFirst; /* nulls sort first for in_range tests? */ 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 baf3c07..6a71087 100644 *** a/src/include/nodes/plannodes.h --- b/src/include/nodes/plannodes.h *************** typedef struct WindowAgg *** 811,816 **** --- 811,822 ---- int frameOptions; /* frame_clause options, see WindowDef */ Node *startOffset; /* expression for starting bound, if any */ Node *endOffset; /* expression for ending bound, if any */ + /* these fields are used when processing RANGE value PRECEDING/FOLLOWING: */ + Oid startInRangeFunc; /* in_range function for startOffset */ + Oid endInRangeFunc; /* in_range function for endOffset */ + Oid inRangeColl; /* collation for in_range tests */ + bool inRangeAsc; /* use ASC sort order for in_range tests? */ + bool inRangeNullsFirst; /* nulls sort first for in_range tests? */ } WindowAgg; /* ---------------- diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h index 26af944..cf32197 100644 *** a/src/include/parser/kwlist.h --- b/src/include/parser/kwlist.h *************** PG_KEYWORD("granted", GRANTED, UNRESERVE *** 182,187 **** --- 182,188 ---- PG_KEYWORD("greatest", GREATEST, COL_NAME_KEYWORD) PG_KEYWORD("group", GROUP_P, RESERVED_KEYWORD) PG_KEYWORD("grouping", GROUPING, COL_NAME_KEYWORD) + PG_KEYWORD("groups", GROUPS, UNRESERVED_KEYWORD) PG_KEYWORD("handler", HANDLER, UNRESERVED_KEYWORD) PG_KEYWORD("having", HAVING, RESERVED_KEYWORD) PG_KEYWORD("header", HEADER_P, UNRESERVED_KEYWORD) *************** PG_KEYWORD("options", OPTIONS, UNRESERVE *** 283,288 **** --- 284,290 ---- PG_KEYWORD("or", OR, RESERVED_KEYWORD) PG_KEYWORD("order", ORDER, RESERVED_KEYWORD) PG_KEYWORD("ordinality", ORDINALITY, UNRESERVED_KEYWORD) + PG_KEYWORD("others", OTHERS, UNRESERVED_KEYWORD) PG_KEYWORD("out", OUT_P, COL_NAME_KEYWORD) PG_KEYWORD("outer", OUTER_P, TYPE_FUNC_NAME_KEYWORD) PG_KEYWORD("over", OVER, UNRESERVED_KEYWORD) *************** PG_KEYWORD("template", TEMPLATE, UNRESER *** 397,402 **** --- 399,405 ---- PG_KEYWORD("temporary", TEMPORARY, UNRESERVED_KEYWORD) PG_KEYWORD("text", TEXT_P, UNRESERVED_KEYWORD) PG_KEYWORD("then", THEN, RESERVED_KEYWORD) + PG_KEYWORD("ties", TIES, UNRESERVED_KEYWORD) PG_KEYWORD("time", TIME, COL_NAME_KEYWORD) PG_KEYWORD("timestamp", TIMESTAMP, COL_NAME_KEYWORD) PG_KEYWORD("to", TO, RESERVED_KEYWORD) diff --git a/src/include/parser/parse_node.h b/src/include/parser/parse_node.h index 4e96fa7..2e0792d 100644 *** a/src/include/parser/parse_node.h --- b/src/include/parser/parse_node.h *************** typedef enum ParseExprKind *** 45,50 **** --- 45,51 ---- EXPR_KIND_WINDOW_ORDER, /* window definition ORDER BY */ EXPR_KIND_WINDOW_FRAME_RANGE, /* window frame clause with RANGE */ EXPR_KIND_WINDOW_FRAME_ROWS, /* window frame clause with ROWS */ + EXPR_KIND_WINDOW_FRAME_GROUPS, /* window frame clause with GROUPS */ EXPR_KIND_SELECT_TARGET, /* SELECT target list item */ EXPR_KIND_INSERT_TARGET, /* INSERT target list item */ EXPR_KIND_UPDATE_SOURCE, /* UPDATE assignment source item */ *************** typedef enum ParseExprKind *** 67,73 **** EXPR_KIND_EXECUTE_PARAMETER, /* parameter value in EXECUTE */ EXPR_KIND_TRIGGER_WHEN, /* WHEN condition in CREATE TRIGGER */ EXPR_KIND_POLICY, /* USING or WITH CHECK expr in policy */ ! EXPR_KIND_PARTITION_EXPRESSION, /* PARTITION BY expression */ EXPR_KIND_CALL /* CALL argument */ } ParseExprKind; --- 68,74 ---- EXPR_KIND_EXECUTE_PARAMETER, /* parameter value in EXECUTE */ EXPR_KIND_TRIGGER_WHEN, /* WHEN condition in CREATE TRIGGER */ EXPR_KIND_POLICY, /* USING or WITH CHECK expr in policy */ ! EXPR_KIND_PARTITION_EXPRESSION, /* PARTITION BY expression */ EXPR_KIND_CALL /* CALL argument */ } ParseExprKind; diff --git a/src/test/regress/expected/alter_generic.out b/src/test/regress/expected/alter_generic.out index 200828a..44356de 100644 *** a/src/test/regress/expected/alter_generic.out --- b/src/test/regress/expected/alter_generic.out *************** ERROR: invalid operator number 0, must *** 354,362 **** ALTER OPERATOR FAMILY alt_opf4 USING btree ADD OPERATOR 1 < ; -- operator without argument types ERROR: operator argument types must be specified in ALTER OPERATOR FAMILY ALTER OPERATOR FAMILY alt_opf4 USING btree ADD FUNCTION 0 btint42cmp(int4, int2); -- function number should be between 1 and 5 ! ERROR: invalid procedure number 0, must be between 1 and 2 ALTER OPERATOR FAMILY alt_opf4 USING btree ADD FUNCTION 6 btint42cmp(int4, int2); -- function number should be between 1 and 5 ! ERROR: invalid procedure number 6, must be between 1 and 2 ALTER OPERATOR FAMILY alt_opf4 USING btree ADD STORAGE invalid_storage; -- Ensure STORAGE is not a part of ALTER OPERATOR FAMILY ERROR: STORAGE cannot be specified in ALTER OPERATOR FAMILY DROP OPERATOR FAMILY alt_opf4 USING btree; --- 354,362 ---- ALTER OPERATOR FAMILY alt_opf4 USING btree ADD OPERATOR 1 < ; -- operator without argument types ERROR: operator argument types must be specified in ALTER OPERATOR FAMILY ALTER OPERATOR FAMILY alt_opf4 USING btree ADD FUNCTION 0 btint42cmp(int4, int2); -- function number should be between 1 and 5 ! ERROR: invalid procedure number 0, must be between 1 and 3 ALTER OPERATOR FAMILY alt_opf4 USING btree ADD FUNCTION 6 btint42cmp(int4, int2); -- function number should be between 1 and 5 ! ERROR: invalid procedure number 6, must be between 1 and 3 ALTER OPERATOR FAMILY alt_opf4 USING btree ADD STORAGE invalid_storage; -- Ensure STORAGE is not a part of ALTER OPERATOR FAMILY ERROR: STORAGE cannot be specified in ALTER OPERATOR FAMILY DROP OPERATOR FAMILY alt_opf4 USING btree; diff --git a/src/test/regress/expected/window.out b/src/test/regress/expected/window.out index 19f909f..e9d56e9 100644 *** a/src/test/regress/expected/window.out --- b/src/test/regress/expected/window.out *************** CREATE TEMPORARY TABLE empsalary ( *** 5,23 **** depname varchar, empno bigint, salary int, ! enroll_date date ); INSERT INTO empsalary VALUES ! ('develop', 10, 5200, '2007-08-01'), ! ('sales', 1, 5000, '2006-10-01'), ! ('personnel', 5, 3500, '2007-12-10'), ! ('sales', 4, 4800, '2007-08-08'), ! ('personnel', 2, 3900, '2006-12-23'), ! ('develop', 7, 4200, '2008-01-01'), ! ('develop', 9, 4500, '2008-01-01'), ! ('sales', 3, 4800, '2007-08-01'), ! ('develop', 8, 6000, '2006-10-01'), ! ('develop', 11, 5200, '2007-08-15'); SELECT depname, empno, salary, sum(salary) OVER (PARTITION BY depname) FROM empsalary ORDER BY depname, salary; depname | empno | salary | sum -----------+-------+--------+------- --- 5,28 ---- depname varchar, empno bigint, salary int, ! enroll_date date, ! enroll_time time, ! enroll_timetz timetz, ! enroll_interval interval, ! enroll_timestamptz timestamptz, ! enroll_timestamp timestamp ); INSERT INTO empsalary VALUES ! ('develop', 10, 5200, '2007-08-01', '11:00', '11:00 BST', '1 year'::interval, TIMESTAMP '2000-10-19 10:23:54+01', TIMESTAMP '2000-10-19 10:23:54'), ! ('sales', 1, 5000, '2006-10-01', '12:00', '12:00 BST', '2 years'::interval, TIMESTAMP '2001-10-19 10:23:54+01', TIMESTAMP '2001-10-19 10:23:54'), ! ('personnel', 5, 3500, '2007-12-10', '13:00', '13:00 BST', '3 years'::interval, TIMESTAMP '2001-10-19 10:23:54+01', TIMESTAMP '2001-10-19 10:23:54'), ! ('sales', 4, 4800, '2007-08-08', '14:00', '14:00 BST', '4 years'::interval, TIMESTAMP '2002-10-19 10:23:54+01', TIMESTAMP '2002-10-19 10:23:54'), ! ('personnel', 2, 3900, '2006-12-23', '15:00', '15:00 BST', '5 years'::interval, TIMESTAMP '2003-10-19 10:23:54+01', TIMESTAMP '2003-10-19 10:23:54'), ! ('develop', 7, 4200, '2008-01-01', '15:00', '15:00 BST', '5 years'::interval, TIMESTAMP '2004-10-19 10:23:54+01', TIMESTAMP '2004-10-19 10:23:54'), ! ('develop', 9, 4500, '2008-01-01', '17:00', '17:00 BST', '7 years'::interval, TIMESTAMP '2005-10-19 10:23:54+01', TIMESTAMP '2005-10-19 10:23:54'), ! ('sales', 3, 4800, '2007-08-01', '18:00', '18:00 BST', '8 years'::interval, TIMESTAMP '2006-10-19 10:23:54+01', TIMESTAMP '2006-10-19 10:23:54'), ! ('develop', 8, 6000, '2006-10-01', '19:00', '19:00 BST', '9 years'::interval, TIMESTAMP '2007-10-19 10:23:54+01', TIMESTAMP '2007-10-19 10:23:54'), ! ('develop', 11, 5200, '2007-08-15', '20:00', '20:00 BST', '10 years'::interval, TIMESTAMP '2008-10-19 10:23:54+01', TIMESTAMP '2008-10-19 10:23:54'); SELECT depname, empno, salary, sum(salary) OVER (PARTITION BY depname) FROM empsalary ORDER BY depname, salary; depname | empno | salary | sum -----------+-------+--------+------- *************** FROM tenk1 WHERE unique1 < 10; *** 819,824 **** --- 824,999 ---- 10 | 0 | 0 (10 rows) + SELECT sum(unique1) over (rows between 2 preceding and 2 following exclude no others), + 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 2 following exclude current row), + unique1, four + FROM tenk1 WHERE unique1 < 10; + sum | unique1 | four + -----+---------+------ + 3 | 4 | 0 + 11 | 2 | 2 + 21 | 1 | 1 + 20 | 6 | 2 + 20 | 9 | 1 + 23 | 8 | 0 + 27 | 5 | 1 + 20 | 3 | 3 + 8 | 7 | 3 + 10 | 0 | 0 + (10 rows) + + SELECT sum(unique1) over (rows between 2 preceding and 2 following exclude group), + unique1, four + FROM tenk1 WHERE unique1 < 10; + sum | unique1 | four + -----+---------+------ + | 4 | 0 + | 2 | 2 + | 1 | 1 + | 6 | 2 + | 9 | 1 + | 8 | 0 + | 5 | 1 + | 3 | 3 + | 7 | 3 + | 0 | 0 + (10 rows) + + SELECT sum(unique1) over (rows between 2 preceding and 2 following exclude ties), + unique1, four + FROM tenk1 WHERE unique1 < 10; + sum | unique1 | four + -----+---------+------ + 4 | 4 | 0 + 2 | 2 | 2 + 1 | 1 | 1 + 6 | 6 | 2 + 9 | 9 | 1 + 8 | 8 | 0 + 5 | 5 | 1 + 3 | 3 | 3 + 7 | 7 | 3 + 0 | 0 | 0 + (10 rows) + + SELECT first_value(unique1) over (ORDER BY four rows between current row and 2 following exclude current row), + unique1, four + FROM tenk1 WHERE unique1 < 10; + first_value | unique1 | four + -------------+---------+------ + 8 | 0 | 0 + 4 | 8 | 0 + 5 | 4 | 0 + 9 | 5 | 1 + 1 | 9 | 1 + 6 | 1 | 1 + 2 | 6 | 2 + 3 | 2 | 2 + 7 | 3 | 3 + | 7 | 3 + (10 rows) + + SELECT first_value(unique1) over (ORDER BY four rows between current row and 2 following exclude group), + unique1, four + FROM tenk1 WHERE unique1 < 10; + first_value | unique1 | four + -------------+---------+------ + | 0 | 0 + 5 | 8 | 0 + 5 | 4 | 0 + | 5 | 1 + 6 | 9 | 1 + 6 | 1 | 1 + 3 | 6 | 2 + 3 | 2 | 2 + | 3 | 3 + | 7 | 3 + (10 rows) + + SELECT first_value(unique1) over (ORDER BY four rows between current row and 2 following exclude ties), + unique1, four + FROM tenk1 WHERE unique1 < 10; + first_value | unique1 | four + -------------+---------+------ + 0 | 0 | 0 + 8 | 8 | 0 + 4 | 4 | 0 + 5 | 5 | 1 + 9 | 9 | 1 + 1 | 1 | 1 + 6 | 6 | 2 + 2 | 2 | 2 + 3 | 3 | 3 + 7 | 7 | 3 + (10 rows) + + SELECT last_value(unique1) over (ORDER BY four rows between current row and 2 following exclude current row), + unique1, four + FROM tenk1 WHERE unique1 < 10; + last_value | unique1 | four + ------------+---------+------ + 4 | 0 | 0 + 5 | 8 | 0 + 9 | 4 | 0 + 1 | 5 | 1 + 6 | 9 | 1 + 2 | 1 | 1 + 3 | 6 | 2 + 7 | 2 | 2 + 7 | 3 | 3 + | 7 | 3 + (10 rows) + + SELECT last_value(unique1) over (ORDER BY four rows between current row and 2 following exclude group), + unique1, four + FROM tenk1 WHERE unique1 < 10; + last_value | unique1 | four + ------------+---------+------ + | 0 | 0 + 5 | 8 | 0 + 9 | 4 | 0 + | 5 | 1 + 6 | 9 | 1 + 2 | 1 | 1 + 3 | 6 | 2 + 7 | 2 | 2 + | 3 | 3 + | 7 | 3 + (10 rows) + + SELECT last_value(unique1) over (ORDER BY four rows between current row and 2 following exclude ties), + unique1, four + FROM tenk1 WHERE unique1 < 10; + last_value | unique1 | four + ------------+---------+------ + 0 | 0 | 0 + 5 | 8 | 0 + 9 | 4 | 0 + 5 | 5 | 1 + 6 | 9 | 1 + 2 | 1 | 1 + 3 | 6 | 2 + 7 | 2 | 2 + 3 | 3 | 3 + 7 | 7 | 3 + (10 rows) + SELECT sum(unique1) over (rows between 2 preceding and 1 preceding), unique1, four FROM tenk1 WHERE unique1 < 10; *************** FROM tenk1 WHERE unique1 < 10 WINDOW w A *** 887,899 **** 10 | 7 | 3 (10 rows) ! -- fail: not implemented yet ! SELECT sum(unique1) over (order by four range between 2::int8 preceding and 1::int2 preceding), unique1, four ! FROM tenk1 WHERE unique1 < 10; ! ERROR: RANGE PRECEDING is only supported with UNBOUNDED ! LINE 1: SELECT sum(unique1) over (order by four range between 2::int... ! ^ SELECT first_value(unique1) over w, nth_value(unique1, 2) over w AS nth_2, last_value(unique1) over w, unique1, four --- 1062,1118 ---- 10 | 7 | 3 (10 rows) ! SELECT sum(unique1) over (w range between unbounded preceding and current row exclude current row), unique1, four ! FROM tenk1 WHERE unique1 < 10 WINDOW w AS (order by four); ! sum | unique1 | four ! -----+---------+------ ! 12 | 0 | 0 ! 4 | 8 | 0 ! 8 | 4 | 0 ! 22 | 5 | 1 ! 18 | 9 | 1 ! 26 | 1 | 1 ! 29 | 6 | 2 ! 33 | 2 | 2 ! 42 | 3 | 3 ! 38 | 7 | 3 ! (10 rows) ! ! SELECT sum(unique1) over (w range between unbounded preceding and current row exclude group), ! unique1, four ! FROM tenk1 WHERE unique1 < 10 WINDOW w AS (order by four); ! sum | unique1 | four ! -----+---------+------ ! | 0 | 0 ! | 8 | 0 ! | 4 | 0 ! 12 | 5 | 1 ! 12 | 9 | 1 ! 12 | 1 | 1 ! 27 | 6 | 2 ! 27 | 2 | 2 ! 35 | 3 | 3 ! 35 | 7 | 3 ! (10 rows) ! ! SELECT sum(unique1) over (w range between unbounded preceding and current row exclude ties), ! unique1, four ! FROM tenk1 WHERE unique1 < 10 WINDOW w AS (order by four); ! sum | unique1 | four ! -----+---------+------ ! 0 | 0 | 0 ! 8 | 8 | 0 ! 4 | 4 | 0 ! 17 | 5 | 1 ! 21 | 9 | 1 ! 13 | 1 | 1 ! 33 | 6 | 2 ! 29 | 2 | 2 ! 38 | 3 | 3 ! 42 | 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 *************** SELECT pg_get_viewdef('v_window'); *** 958,963 **** --- 1177,2960 ---- FROM generate_series(1, 10) i(i); (1 row) + CREATE OR REPLACE TEMP VIEW v_window AS + SELECT i, sum(i) over (order by i rows between 1 preceding and 1 following + exclude current row) as sum_rows FROM generate_series(1, 10) i; + SELECT * FROM v_window; + i | sum_rows + ----+---------- + 1 | 2 + 2 | 4 + 3 | 6 + 4 | 8 + 5 | 10 + 6 | 12 + 7 | 14 + 8 | 16 + 9 | 18 + 10 | 9 + (10 rows) + + SELECT pg_get_viewdef('v_window'); + pg_get_viewdef + ----------------------------------------------------------------------------------------------------------- + SELECT i.i, + + sum(i.i) OVER (ORDER BY i.i ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING EXCLUDE CURRENT ROW) AS sum_rows+ + FROM generate_series(1, 10) i(i); + (1 row) + + CREATE OR REPLACE TEMP VIEW v_window AS + SELECT i, sum(i) over (order by i rows between 1 preceding and 1 following + exclude group) as sum_rows FROM generate_series(1, 10) i; + SELECT * FROM v_window; + i | sum_rows + ----+---------- + 1 | 2 + 2 | 4 + 3 | 6 + 4 | 8 + 5 | 10 + 6 | 12 + 7 | 14 + 8 | 16 + 9 | 18 + 10 | 9 + (10 rows) + + SELECT pg_get_viewdef('v_window'); + pg_get_viewdef + ----------------------------------------------------------------------------------------------------- + SELECT i.i, + + sum(i.i) OVER (ORDER BY i.i ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING EXCLUDE GROUP) AS sum_rows+ + FROM generate_series(1, 10) i(i); + (1 row) + + CREATE OR REPLACE TEMP VIEW v_window AS + SELECT i, sum(i) over (order by i rows between 1 preceding and 1 following + exclude ties) as sum_rows FROM generate_series(1, 10) i; + SELECT * FROM v_window; + i | sum_rows + ----+---------- + 1 | 3 + 2 | 6 + 3 | 9 + 4 | 12 + 5 | 15 + 6 | 18 + 7 | 21 + 8 | 24 + 9 | 27 + 10 | 19 + (10 rows) + + SELECT pg_get_viewdef('v_window'); + pg_get_viewdef + ---------------------------------------------------------------------------------------------------- + SELECT i.i, + + sum(i.i) OVER (ORDER BY i.i ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING EXCLUDE TIES) AS sum_rows+ + FROM generate_series(1, 10) i(i); + (1 row) + + CREATE OR REPLACE TEMP VIEW v_window AS + SELECT i, sum(i) over (order by i rows between 1 preceding and 1 following + exclude no others) as sum_rows FROM generate_series(1, 10) i; + SELECT * FROM v_window; + i | sum_rows + ----+---------- + 1 | 3 + 2 | 6 + 3 | 9 + 4 | 12 + 5 | 15 + 6 | 18 + 7 | 21 + 8 | 24 + 9 | 27 + 10 | 19 + (10 rows) + + SELECT pg_get_viewdef('v_window'); + pg_get_viewdef + --------------------------------------------------------------------------------------- + SELECT i.i, + + sum(i.i) OVER (ORDER BY i.i ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS sum_rows+ + FROM generate_series(1, 10) i(i); + (1 row) + + CREATE OR REPLACE TEMP VIEW v_window AS + SELECT i, sum(i) over (order by i groups between 1 preceding and 1 following + exclude no others) as sum_rows FROM generate_series(1, 10) i; + SELECT * FROM v_window; + i | sum_rows + ----+---------- + 1 | 3 + 2 | 6 + 3 | 9 + 4 | 12 + 5 | 15 + 6 | 18 + 7 | 21 + 8 | 24 + 9 | 27 + 10 | 19 + (10 rows) + + SELECT pg_get_viewdef('v_window'); + pg_get_viewdef + ----------------------------------------------------------------------------------------- + SELECT i.i, + + sum(i.i) OVER (ORDER BY i.i GROUPS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS sum_rows+ + FROM generate_series(1, 10) i(i); + (1 row) + + -- RANGE BETWEEN with values tests + SELECT sum(unique1) over (order by four range between 2::int8 preceding and 1::int2 preceding), + unique1, four + FROM tenk1 WHERE unique1 < 10; + sum | unique1 | four + -----+---------+------ + | 0 | 0 + | 8 | 0 + | 4 | 0 + 12 | 5 | 1 + 12 | 9 | 1 + 12 | 1 | 1 + 27 | 6 | 2 + 27 | 2 | 2 + 23 | 3 | 3 + 23 | 7 | 3 + (10 rows) + + SELECT sum(unique1) over (order by four desc range between 2::int8 preceding and 1::int2 preceding), + unique1, four + FROM tenk1 WHERE unique1 < 10; + sum | unique1 | four + -----+---------+------ + | 3 | 3 + | 7 | 3 + 10 | 6 | 2 + 10 | 2 | 2 + 18 | 9 | 1 + 18 | 5 | 1 + 18 | 1 | 1 + 23 | 0 | 0 + 23 | 8 | 0 + 23 | 4 | 0 + (10 rows) + + SELECT sum(unique1) over (order by four range between 2::int8 preceding and 1::int2 preceding exclude no others), + unique1, four + FROM tenk1 WHERE unique1 < 10; + sum | unique1 | four + -----+---------+------ + | 0 | 0 + | 8 | 0 + | 4 | 0 + 12 | 5 | 1 + 12 | 9 | 1 + 12 | 1 | 1 + 27 | 6 | 2 + 27 | 2 | 2 + 23 | 3 | 3 + 23 | 7 | 3 + (10 rows) + + SELECT sum(unique1) over (order by four range between 2::int8 preceding and 1::int2 preceding exclude current row), + unique1, four + FROM tenk1 WHERE unique1 < 10; + sum | unique1 | four + -----+---------+------ + | 0 | 0 + | 8 | 0 + | 4 | 0 + 12 | 5 | 1 + 12 | 9 | 1 + 12 | 1 | 1 + 27 | 6 | 2 + 27 | 2 | 2 + 23 | 3 | 3 + 23 | 7 | 3 + (10 rows) + + SELECT sum(unique1) over (order by four range between 2::int8 preceding and 1::int2 preceding exclude group), + unique1, four + FROM tenk1 WHERE unique1 < 10; + sum | unique1 | four + -----+---------+------ + | 0 | 0 + | 8 | 0 + | 4 | 0 + 12 | 5 | 1 + 12 | 9 | 1 + 12 | 1 | 1 + 27 | 6 | 2 + 27 | 2 | 2 + 23 | 3 | 3 + 23 | 7 | 3 + (10 rows) + + SELECT sum(unique1) over (order by four range between 2::int8 preceding and 1::int2 preceding exclude ties), + unique1, four + FROM tenk1 WHERE unique1 < 10; + sum | unique1 | four + -----+---------+------ + | 0 | 0 + | 8 | 0 + | 4 | 0 + 12 | 5 | 1 + 12 | 9 | 1 + 12 | 1 | 1 + 27 | 6 | 2 + 27 | 2 | 2 + 23 | 3 | 3 + 23 | 7 | 3 + (10 rows) + + SELECT sum(unique1) over (order by four range between 2::int8 preceding and 6::int2 following exclude ties), + unique1, four + FROM tenk1 WHERE unique1 < 10; + sum | unique1 | four + -----+---------+------ + 33 | 0 | 0 + 41 | 8 | 0 + 37 | 4 | 0 + 35 | 5 | 1 + 39 | 9 | 1 + 31 | 1 | 1 + 43 | 6 | 2 + 39 | 2 | 2 + 26 | 3 | 3 + 30 | 7 | 3 + (10 rows) + + SELECT sum(unique1) over (order by four range between 2::int8 preceding and 6::int2 following exclude group), + unique1, four + FROM tenk1 WHERE unique1 < 10; + sum | unique1 | four + -----+---------+------ + 33 | 0 | 0 + 33 | 8 | 0 + 33 | 4 | 0 + 30 | 5 | 1 + 30 | 9 | 1 + 30 | 1 | 1 + 37 | 6 | 2 + 37 | 2 | 2 + 23 | 3 | 3 + 23 | 7 | 3 + (10 rows) + + SELECT sum(unique1) over (partition by four order by unique1 range between 5::int8 preceding and 6::int2 following), + unique1, four + FROM tenk1 WHERE unique1 < 10; + sum | unique1 | four + -----+---------+------ + 4 | 0 | 0 + 12 | 4 | 0 + 12 | 8 | 0 + 6 | 1 | 1 + 15 | 5 | 1 + 14 | 9 | 1 + 8 | 2 | 2 + 8 | 6 | 2 + 10 | 3 | 3 + 10 | 7 | 3 + (10 rows) + + SELECT sum(unique1) over (partition by four order by unique1 range between 5::int8 preceding and 6::int2 following + exclude current row),unique1, four + FROM tenk1 WHERE unique1 < 10; + sum | unique1 | four + -----+---------+------ + 4 | 0 | 0 + 8 | 4 | 0 + 4 | 8 | 0 + 5 | 1 | 1 + 10 | 5 | 1 + 5 | 9 | 1 + 6 | 2 | 2 + 2 | 6 | 2 + 7 | 3 | 3 + 3 | 7 | 3 + (10 rows) + + select sum(salary) over (order by enroll_date range between '1 year'::interval preceding and '1 year'::interval following), + salary, enroll_date from empsalary; + sum | salary | enroll_date + -------+--------+------------- + 34900 | 5000 | 10-01-2006 + 34900 | 6000 | 10-01-2006 + 38400 | 3900 | 12-23-2006 + 47100 | 4800 | 08-01-2007 + 47100 | 5200 | 08-01-2007 + 47100 | 4800 | 08-08-2007 + 47100 | 5200 | 08-15-2007 + 36100 | 3500 | 12-10-2007 + 32200 | 4500 | 01-01-2008 + 32200 | 4200 | 01-01-2008 + (10 rows) + + select sum(salary) over (order by enroll_date desc range between '1 year'::interval preceding and '1 year'::interval following), + salary, enroll_date from empsalary; + sum | salary | enroll_date + -------+--------+------------- + 32200 | 4200 | 01-01-2008 + 32200 | 4500 | 01-01-2008 + 36100 | 3500 | 12-10-2007 + 47100 | 5200 | 08-15-2007 + 47100 | 4800 | 08-08-2007 + 47100 | 4800 | 08-01-2007 + 47100 | 5200 | 08-01-2007 + 38400 | 3900 | 12-23-2006 + 34900 | 5000 | 10-01-2006 + 34900 | 6000 | 10-01-2006 + (10 rows) + + select sum(salary) over (order by enroll_date desc range between '1 year'::interval following and '1 year'::interval following), + salary, enroll_date from empsalary; + sum | salary | enroll_date + -----+--------+------------- + | 4200 | 01-01-2008 + | 4500 | 01-01-2008 + | 3500 | 12-10-2007 + | 5200 | 08-15-2007 + | 4800 | 08-08-2007 + | 4800 | 08-01-2007 + | 5200 | 08-01-2007 + | 3900 | 12-23-2006 + | 5000 | 10-01-2006 + | 6000 | 10-01-2006 + (10 rows) + + select sum(salary) over (order by enroll_date range between '1 year'::interval preceding and '1 year'::interval following + exclude current row), salary, enroll_date from empsalary; + sum | salary | enroll_date + -------+--------+------------- + 29900 | 5000 | 10-01-2006 + 28900 | 6000 | 10-01-2006 + 34500 | 3900 | 12-23-2006 + 42300 | 4800 | 08-01-2007 + 41900 | 5200 | 08-01-2007 + 42300 | 4800 | 08-08-2007 + 41900 | 5200 | 08-15-2007 + 32600 | 3500 | 12-10-2007 + 27700 | 4500 | 01-01-2008 + 28000 | 4200 | 01-01-2008 + (10 rows) + + select sum(salary) over (order by enroll_date range between '1 year'::interval preceding and '1 year'::interval following + exclude group), salary, enroll_date from empsalary; + sum | salary | enroll_date + -------+--------+------------- + 23900 | 5000 | 10-01-2006 + 23900 | 6000 | 10-01-2006 + 34500 | 3900 | 12-23-2006 + 37100 | 4800 | 08-01-2007 + 37100 | 5200 | 08-01-2007 + 42300 | 4800 | 08-08-2007 + 41900 | 5200 | 08-15-2007 + 32600 | 3500 | 12-10-2007 + 23500 | 4500 | 01-01-2008 + 23500 | 4200 | 01-01-2008 + (10 rows) + + select sum(salary) over (order by enroll_date range between '1 year'::interval preceding and '1 year'::interval following + exclude ties), salary, enroll_date from empsalary; + sum | salary | enroll_date + -------+--------+------------- + 28900 | 5000 | 10-01-2006 + 29900 | 6000 | 10-01-2006 + 38400 | 3900 | 12-23-2006 + 41900 | 4800 | 08-01-2007 + 42300 | 5200 | 08-01-2007 + 47100 | 4800 | 08-08-2007 + 47100 | 5200 | 08-15-2007 + 36100 | 3500 | 12-10-2007 + 28000 | 4500 | 01-01-2008 + 27700 | 4200 | 01-01-2008 + (10 rows) + + select sum(salary) over (order by enroll_time range between '1 hour'::interval preceding and '2 hours'::interval following), + salary, enroll_time from empsalary; + sum | salary | enroll_time + -------+--------+------------- + 13700 | 5200 | 11:00:00 + 18500 | 5000 | 12:00:00 + 21400 | 3500 | 13:00:00 + 16400 | 4800 | 14:00:00 + 17400 | 3900 | 15:00:00 + 17400 | 4200 | 15:00:00 + 15300 | 4500 | 17:00:00 + 20500 | 4800 | 18:00:00 + 16000 | 6000 | 19:00:00 + 11200 | 5200 | 20:00:00 + (10 rows) + + select sum(salary) over (order by enroll_time desc range between '1 hour'::interval preceding and '2 hours'::interval following), + salary, enroll_time from empsalary; + sum | salary | enroll_time + -------+--------+------------- + 16000 | 5200 | 20:00:00 + 20500 | 6000 | 19:00:00 + 15300 | 4800 | 18:00:00 + 17400 | 4500 | 17:00:00 + 16400 | 4200 | 15:00:00 + 16400 | 3900 | 15:00:00 + 21400 | 4800 | 14:00:00 + 18500 | 3500 | 13:00:00 + 13700 | 5000 | 12:00:00 + 10200 | 5200 | 11:00:00 + (10 rows) + + select sum(salary) over (order by enroll_time desc range between '1 hour'::interval following and '2 hours'::interval following), + salary, enroll_time from empsalary; + sum | salary | enroll_time + -------+--------+------------- + 10800 | 5200 | 20:00:00 + 9300 | 6000 | 19:00:00 + 4500 | 4800 | 18:00:00 + 8100 | 4500 | 17:00:00 + 8300 | 4200 | 15:00:00 + 8300 | 3900 | 15:00:00 + 8500 | 4800 | 14:00:00 + 10200 | 3500 | 13:00:00 + 5200 | 5000 | 12:00:00 + | 5200 | 11:00:00 + (10 rows) + + select sum(salary) over (order by enroll_time range between '1 hour'::interval preceding and '2 hours'::interval following + exclude current row), salary, enroll_time from empsalary; + sum | salary | enroll_time + -------+--------+------------- + 8500 | 5200 | 11:00:00 + 13500 | 5000 | 12:00:00 + 17900 | 3500 | 13:00:00 + 11600 | 4800 | 14:00:00 + 13500 | 3900 | 15:00:00 + 13200 | 4200 | 15:00:00 + 10800 | 4500 | 17:00:00 + 15700 | 4800 | 18:00:00 + 10000 | 6000 | 19:00:00 + 6000 | 5200 | 20:00:00 + (10 rows) + + select sum(salary) over (order by enroll_time range between '1 hour'::interval preceding and '2 hours'::interval following + exclude group), salary, enroll_time from empsalary; + sum | salary | enroll_time + -------+--------+------------- + 8500 | 5200 | 11:00:00 + 13500 | 5000 | 12:00:00 + 17900 | 3500 | 13:00:00 + 11600 | 4800 | 14:00:00 + 9300 | 3900 | 15:00:00 + 9300 | 4200 | 15:00:00 + 10800 | 4500 | 17:00:00 + 15700 | 4800 | 18:00:00 + 10000 | 6000 | 19:00:00 + 6000 | 5200 | 20:00:00 + (10 rows) + + select sum(salary) over (order by enroll_time range between '1 hour'::interval preceding and '2 hours'::interval following + exclude ties), salary, enroll_time from empsalary; + sum | salary | enroll_time + -------+--------+------------- + 13700 | 5200 | 11:00:00 + 18500 | 5000 | 12:00:00 + 21400 | 3500 | 13:00:00 + 16400 | 4800 | 14:00:00 + 13200 | 3900 | 15:00:00 + 13500 | 4200 | 15:00:00 + 15300 | 4500 | 17:00:00 + 20500 | 4800 | 18:00:00 + 16000 | 6000 | 19:00:00 + 11200 | 5200 | 20:00:00 + (10 rows) + + select sum(salary) over (order by enroll_timetz range between '1 hour'::interval preceding and '2 hours'::interval following), + salary, enroll_timetz from empsalary; + sum | salary | enroll_timetz + -------+--------+--------------- + 13700 | 5200 | 11:00:00+01 + 18500 | 5000 | 12:00:00+01 + 21400 | 3500 | 13:00:00+01 + 16400 | 4800 | 14:00:00+01 + 17400 | 3900 | 15:00:00+01 + 17400 | 4200 | 15:00:00+01 + 15300 | 4500 | 17:00:00+01 + 20500 | 4800 | 18:00:00+01 + 16000 | 6000 | 19:00:00+01 + 11200 | 5200 | 20:00:00+01 + (10 rows) + + select sum(salary) over (order by enroll_timetz desc range between '1 hour'::interval preceding and '2 hours'::interval following), + salary, enroll_timetz from empsalary; + sum | salary | enroll_timetz + -------+--------+--------------- + 16000 | 5200 | 20:00:00+01 + 20500 | 6000 | 19:00:00+01 + 15300 | 4800 | 18:00:00+01 + 17400 | 4500 | 17:00:00+01 + 16400 | 4200 | 15:00:00+01 + 16400 | 3900 | 15:00:00+01 + 21400 | 4800 | 14:00:00+01 + 18500 | 3500 | 13:00:00+01 + 13700 | 5000 | 12:00:00+01 + 10200 | 5200 | 11:00:00+01 + (10 rows) + + select sum(salary) over (order by enroll_timetz desc range between '1 hour'::interval following and '2 hours'::interval following), + salary, enroll_timetz from empsalary; + sum | salary | enroll_timetz + -------+--------+--------------- + 10800 | 5200 | 20:00:00+01 + 9300 | 6000 | 19:00:00+01 + 4500 | 4800 | 18:00:00+01 + 8100 | 4500 | 17:00:00+01 + 8300 | 4200 | 15:00:00+01 + 8300 | 3900 | 15:00:00+01 + 8500 | 4800 | 14:00:00+01 + 10200 | 3500 | 13:00:00+01 + 5200 | 5000 | 12:00:00+01 + | 5200 | 11:00:00+01 + (10 rows) + + select sum(salary) over (order by enroll_timetz range between '1 hour'::interval preceding and '2 hours'::interval following + exclude current row), salary, enroll_timetz from empsalary; + sum | salary | enroll_timetz + -------+--------+--------------- + 8500 | 5200 | 11:00:00+01 + 13500 | 5000 | 12:00:00+01 + 17900 | 3500 | 13:00:00+01 + 11600 | 4800 | 14:00:00+01 + 13500 | 3900 | 15:00:00+01 + 13200 | 4200 | 15:00:00+01 + 10800 | 4500 | 17:00:00+01 + 15700 | 4800 | 18:00:00+01 + 10000 | 6000 | 19:00:00+01 + 6000 | 5200 | 20:00:00+01 + (10 rows) + + select sum(salary) over (order by enroll_timetz range between '1 hour'::interval preceding and '2 hours'::interval following + exclude group), salary, enroll_timetz from empsalary; + sum | salary | enroll_timetz + -------+--------+--------------- + 8500 | 5200 | 11:00:00+01 + 13500 | 5000 | 12:00:00+01 + 17900 | 3500 | 13:00:00+01 + 11600 | 4800 | 14:00:00+01 + 9300 | 3900 | 15:00:00+01 + 9300 | 4200 | 15:00:00+01 + 10800 | 4500 | 17:00:00+01 + 15700 | 4800 | 18:00:00+01 + 10000 | 6000 | 19:00:00+01 + 6000 | 5200 | 20:00:00+01 + (10 rows) + + select sum(salary) over (order by enroll_timetz range between '1 hour'::interval preceding and '2 hours'::interval following + exclude ties), salary, enroll_timetz from empsalary; + sum | salary | enroll_timetz + -------+--------+--------------- + 13700 | 5200 | 11:00:00+01 + 18500 | 5000 | 12:00:00+01 + 21400 | 3500 | 13:00:00+01 + 16400 | 4800 | 14:00:00+01 + 13200 | 3900 | 15:00:00+01 + 13500 | 4200 | 15:00:00+01 + 15300 | 4500 | 17:00:00+01 + 20500 | 4800 | 18:00:00+01 + 16000 | 6000 | 19:00:00+01 + 11200 | 5200 | 20:00:00+01 + (10 rows) + + select sum(salary) over (order by enroll_interval range between '1 year'::interval preceding and '2 years'::interval following), + salary, enroll_interval from empsalary; + sum | salary | enroll_interval + -------+--------+----------------- + 13700 | 5200 | @ 1 year + 18500 | 5000 | @ 2 years + 21400 | 3500 | @ 3 years + 16400 | 4800 | @ 4 years + 17400 | 3900 | @ 5 years + 17400 | 4200 | @ 5 years + 15300 | 4500 | @ 7 years + 20500 | 4800 | @ 8 years + 16000 | 6000 | @ 9 years + 11200 | 5200 | @ 10 years + (10 rows) + + select sum(salary) over (order by enroll_interval desc range between '1 year'::interval preceding and '2 years'::interval following), + salary, enroll_interval from empsalary; + sum | salary | enroll_interval + -------+--------+----------------- + 16000 | 5200 | @ 10 years + 20500 | 6000 | @ 9 years + 15300 | 4800 | @ 8 years + 17400 | 4500 | @ 7 years + 16400 | 4200 | @ 5 years + 16400 | 3900 | @ 5 years + 21400 | 4800 | @ 4 years + 18500 | 3500 | @ 3 years + 13700 | 5000 | @ 2 years + 10200 | 5200 | @ 1 year + (10 rows) + + select sum(salary) over (order by enroll_interval desc range between '1 year'::interval following and '2 years'::interval following), + salary, enroll_interval from empsalary; + sum | salary | enroll_interval + -------+--------+----------------- + 10800 | 5200 | @ 10 years + 9300 | 6000 | @ 9 years + 4500 | 4800 | @ 8 years + 8100 | 4500 | @ 7 years + 8300 | 4200 | @ 5 years + 8300 | 3900 | @ 5 years + 8500 | 4800 | @ 4 years + 10200 | 3500 | @ 3 years + 5200 | 5000 | @ 2 years + | 5200 | @ 1 year + (10 rows) + + select sum(salary) over (order by enroll_interval range between '1 year'::interval preceding and '2 years'::interval following + exclude current row), salary, enroll_interval from empsalary; + sum | salary | enroll_interval + -------+--------+----------------- + 8500 | 5200 | @ 1 year + 13500 | 5000 | @ 2 years + 17900 | 3500 | @ 3 years + 11600 | 4800 | @ 4 years + 13500 | 3900 | @ 5 years + 13200 | 4200 | @ 5 years + 10800 | 4500 | @ 7 years + 15700 | 4800 | @ 8 years + 10000 | 6000 | @ 9 years + 6000 | 5200 | @ 10 years + (10 rows) + + select sum(salary) over (order by enroll_interval range between '1 year'::interval preceding and '2 years'::interval following + exclude group), salary, enroll_interval from empsalary; + sum | salary | enroll_interval + -------+--------+----------------- + 8500 | 5200 | @ 1 year + 13500 | 5000 | @ 2 years + 17900 | 3500 | @ 3 years + 11600 | 4800 | @ 4 years + 9300 | 3900 | @ 5 years + 9300 | 4200 | @ 5 years + 10800 | 4500 | @ 7 years + 15700 | 4800 | @ 8 years + 10000 | 6000 | @ 9 years + 6000 | 5200 | @ 10 years + (10 rows) + + select sum(salary) over (order by enroll_interval range between '1 year'::interval preceding and '2 years'::interval following + exclude ties), salary, enroll_interval from empsalary; + sum | salary | enroll_interval + -------+--------+----------------- + 13700 | 5200 | @ 1 year + 18500 | 5000 | @ 2 years + 21400 | 3500 | @ 3 years + 16400 | 4800 | @ 4 years + 13200 | 3900 | @ 5 years + 13500 | 4200 | @ 5 years + 15300 | 4500 | @ 7 years + 20500 | 4800 | @ 8 years + 16000 | 6000 | @ 9 years + 11200 | 5200 | @ 10 years + (10 rows) + + select sum(salary) over (order by enroll_timestamptz range between '1 year'::interval preceding and '2 years'::interval following), + salary, enroll_timestamptz from empsalary; + sum | salary | enroll_timestamptz + -------+--------+------------------------------ + 18500 | 5200 | Thu Oct 19 10:23:54 2000 PDT + 22400 | 5000 | Fri Oct 19 10:23:54 2001 PDT + 22400 | 3500 | Fri Oct 19 10:23:54 2001 PDT + 21400 | 4800 | Sat Oct 19 10:23:54 2002 PDT + 17400 | 3900 | Sun Oct 19 10:23:54 2003 PDT + 17400 | 4200 | Tue Oct 19 10:23:54 2004 PDT + 19500 | 4500 | Wed Oct 19 10:23:54 2005 PDT + 20500 | 4800 | Thu Oct 19 10:23:54 2006 PDT + 16000 | 6000 | Fri Oct 19 10:23:54 2007 PDT + 11200 | 5200 | Sun Oct 19 10:23:54 2008 PDT + (10 rows) + + select sum(salary) over (order by enroll_timestamptz desc range between '1 year'::interval preceding and '2 years'::interval following), + salary, enroll_timestamptz from empsalary; + sum | salary | enroll_timestamptz + -------+--------+------------------------------ + 16000 | 5200 | Sun Oct 19 10:23:54 2008 PDT + 20500 | 6000 | Fri Oct 19 10:23:54 2007 PDT + 19500 | 4800 | Thu Oct 19 10:23:54 2006 PDT + 17400 | 4500 | Wed Oct 19 10:23:54 2005 PDT + 17400 | 4200 | Tue Oct 19 10:23:54 2004 PDT + 21400 | 3900 | Sun Oct 19 10:23:54 2003 PDT + 22400 | 4800 | Sat Oct 19 10:23:54 2002 PDT + 18500 | 3500 | Fri Oct 19 10:23:54 2001 PDT + 18500 | 5000 | Fri Oct 19 10:23:54 2001 PDT + 13700 | 5200 | Thu Oct 19 10:23:54 2000 PDT + (10 rows) + + select sum(salary) over (order by enroll_timestamptz desc range between '1 year'::interval following and '2 years'::interval following), + salary, enroll_timestamptz from empsalary; + sum | salary | enroll_timestamptz + -------+--------+------------------------------ + 10800 | 5200 | Sun Oct 19 10:23:54 2008 PDT + 9300 | 6000 | Fri Oct 19 10:23:54 2007 PDT + 8700 | 4800 | Thu Oct 19 10:23:54 2006 PDT + 8100 | 4500 | Wed Oct 19 10:23:54 2005 PDT + 8700 | 4200 | Tue Oct 19 10:23:54 2004 PDT + 13300 | 3900 | Sun Oct 19 10:23:54 2003 PDT + 13700 | 4800 | Sat Oct 19 10:23:54 2002 PDT + 5200 | 3500 | Fri Oct 19 10:23:54 2001 PDT + 5200 | 5000 | Fri Oct 19 10:23:54 2001 PDT + | 5200 | Thu Oct 19 10:23:54 2000 PDT + (10 rows) + + select sum(salary) over (order by enroll_timestamptz range between '1 year'::interval preceding and '2 years'::interval following + exclude current row), salary, enroll_timestamptz from empsalary; + sum | salary | enroll_timestamptz + -------+--------+------------------------------ + 13300 | 5200 | Thu Oct 19 10:23:54 2000 PDT + 17400 | 5000 | Fri Oct 19 10:23:54 2001 PDT + 18900 | 3500 | Fri Oct 19 10:23:54 2001 PDT + 16600 | 4800 | Sat Oct 19 10:23:54 2002 PDT + 13500 | 3900 | Sun Oct 19 10:23:54 2003 PDT + 13200 | 4200 | Tue Oct 19 10:23:54 2004 PDT + 15000 | 4500 | Wed Oct 19 10:23:54 2005 PDT + 15700 | 4800 | Thu Oct 19 10:23:54 2006 PDT + 10000 | 6000 | Fri Oct 19 10:23:54 2007 PDT + 6000 | 5200 | Sun Oct 19 10:23:54 2008 PDT + (10 rows) + + select sum(salary) over (order by enroll_timestamptz range between '1 year'::interval preceding and '2 years'::interval following + exclude group), salary, enroll_timestamptz from empsalary; + sum | salary | enroll_timestamptz + -------+--------+------------------------------ + 13300 | 5200 | Thu Oct 19 10:23:54 2000 PDT + 13900 | 5000 | Fri Oct 19 10:23:54 2001 PDT + 13900 | 3500 | Fri Oct 19 10:23:54 2001 PDT + 16600 | 4800 | Sat Oct 19 10:23:54 2002 PDT + 13500 | 3900 | Sun Oct 19 10:23:54 2003 PDT + 13200 | 4200 | Tue Oct 19 10:23:54 2004 PDT + 15000 | 4500 | Wed Oct 19 10:23:54 2005 PDT + 15700 | 4800 | Thu Oct 19 10:23:54 2006 PDT + 10000 | 6000 | Fri Oct 19 10:23:54 2007 PDT + 6000 | 5200 | Sun Oct 19 10:23:54 2008 PDT + (10 rows) + + select sum(salary) over (order by enroll_timestamptz range between '1 year'::interval preceding and '2 years'::interval following + exclude ties), salary, enroll_timestamptz from empsalary; + sum | salary | enroll_timestamptz + -------+--------+------------------------------ + 18500 | 5200 | Thu Oct 19 10:23:54 2000 PDT + 18900 | 5000 | Fri Oct 19 10:23:54 2001 PDT + 17400 | 3500 | Fri Oct 19 10:23:54 2001 PDT + 21400 | 4800 | Sat Oct 19 10:23:54 2002 PDT + 17400 | 3900 | Sun Oct 19 10:23:54 2003 PDT + 17400 | 4200 | Tue Oct 19 10:23:54 2004 PDT + 19500 | 4500 | Wed Oct 19 10:23:54 2005 PDT + 20500 | 4800 | Thu Oct 19 10:23:54 2006 PDT + 16000 | 6000 | Fri Oct 19 10:23:54 2007 PDT + 11200 | 5200 | Sun Oct 19 10:23:54 2008 PDT + (10 rows) + + select sum(salary) over (order by enroll_timestamp range between '1 year'::interval preceding and '2 years'::interval following), + salary, enroll_timestamp from empsalary; + sum | salary | enroll_timestamp + -------+--------+-------------------------- + 18500 | 5200 | Thu Oct 19 10:23:54 2000 + 22400 | 5000 | Fri Oct 19 10:23:54 2001 + 22400 | 3500 | Fri Oct 19 10:23:54 2001 + 21400 | 4800 | Sat Oct 19 10:23:54 2002 + 17400 | 3900 | Sun Oct 19 10:23:54 2003 + 17400 | 4200 | Tue Oct 19 10:23:54 2004 + 19500 | 4500 | Wed Oct 19 10:23:54 2005 + 20500 | 4800 | Thu Oct 19 10:23:54 2006 + 16000 | 6000 | Fri Oct 19 10:23:54 2007 + 11200 | 5200 | Sun Oct 19 10:23:54 2008 + (10 rows) + + select sum(salary) over (order by enroll_timestamp desc range between '1 year'::interval preceding and '2 years'::interval following), + salary, enroll_timestamp from empsalary; + sum | salary | enroll_timestamp + -------+--------+-------------------------- + 16000 | 5200 | Sun Oct 19 10:23:54 2008 + 20500 | 6000 | Fri Oct 19 10:23:54 2007 + 19500 | 4800 | Thu Oct 19 10:23:54 2006 + 17400 | 4500 | Wed Oct 19 10:23:54 2005 + 17400 | 4200 | Tue Oct 19 10:23:54 2004 + 21400 | 3900 | Sun Oct 19 10:23:54 2003 + 22400 | 4800 | Sat Oct 19 10:23:54 2002 + 18500 | 3500 | Fri Oct 19 10:23:54 2001 + 18500 | 5000 | Fri Oct 19 10:23:54 2001 + 13700 | 5200 | Thu Oct 19 10:23:54 2000 + (10 rows) + + select sum(salary) over (order by enroll_timestamp desc range between '1 year'::interval following and '2 years'::interval following), + salary, enroll_timestamp from empsalary; + sum | salary | enroll_timestamp + -------+--------+-------------------------- + 10800 | 5200 | Sun Oct 19 10:23:54 2008 + 9300 | 6000 | Fri Oct 19 10:23:54 2007 + 8700 | 4800 | Thu Oct 19 10:23:54 2006 + 8100 | 4500 | Wed Oct 19 10:23:54 2005 + 8700 | 4200 | Tue Oct 19 10:23:54 2004 + 13300 | 3900 | Sun Oct 19 10:23:54 2003 + 13700 | 4800 | Sat Oct 19 10:23:54 2002 + 5200 | 3500 | Fri Oct 19 10:23:54 2001 + 5200 | 5000 | Fri Oct 19 10:23:54 2001 + | 5200 | Thu Oct 19 10:23:54 2000 + (10 rows) + + select sum(salary) over (order by enroll_timestamp range between '1 year'::interval preceding and '2 years'::interval following + exclude current row), salary, enroll_timestamp from empsalary; + sum | salary | enroll_timestamp + -------+--------+-------------------------- + 13300 | 5200 | Thu Oct 19 10:23:54 2000 + 17400 | 5000 | Fri Oct 19 10:23:54 2001 + 18900 | 3500 | Fri Oct 19 10:23:54 2001 + 16600 | 4800 | Sat Oct 19 10:23:54 2002 + 13500 | 3900 | Sun Oct 19 10:23:54 2003 + 13200 | 4200 | Tue Oct 19 10:23:54 2004 + 15000 | 4500 | Wed Oct 19 10:23:54 2005 + 15700 | 4800 | Thu Oct 19 10:23:54 2006 + 10000 | 6000 | Fri Oct 19 10:23:54 2007 + 6000 | 5200 | Sun Oct 19 10:23:54 2008 + (10 rows) + + select sum(salary) over (order by enroll_timestamp range between '1 year'::interval preceding and '2 years'::interval following + exclude group), salary, enroll_timestamp from empsalary; + sum | salary | enroll_timestamp + -------+--------+-------------------------- + 13300 | 5200 | Thu Oct 19 10:23:54 2000 + 13900 | 5000 | Fri Oct 19 10:23:54 2001 + 13900 | 3500 | Fri Oct 19 10:23:54 2001 + 16600 | 4800 | Sat Oct 19 10:23:54 2002 + 13500 | 3900 | Sun Oct 19 10:23:54 2003 + 13200 | 4200 | Tue Oct 19 10:23:54 2004 + 15000 | 4500 | Wed Oct 19 10:23:54 2005 + 15700 | 4800 | Thu Oct 19 10:23:54 2006 + 10000 | 6000 | Fri Oct 19 10:23:54 2007 + 6000 | 5200 | Sun Oct 19 10:23:54 2008 + (10 rows) + + select sum(salary) over (order by enroll_timestamp range between '1 year'::interval preceding and '2 years'::interval following + exclude ties), salary, enroll_timestamp from empsalary; + sum | salary | enroll_timestamp + -------+--------+-------------------------- + 18500 | 5200 | Thu Oct 19 10:23:54 2000 + 18900 | 5000 | Fri Oct 19 10:23:54 2001 + 17400 | 3500 | Fri Oct 19 10:23:54 2001 + 21400 | 4800 | Sat Oct 19 10:23:54 2002 + 17400 | 3900 | Sun Oct 19 10:23:54 2003 + 17400 | 4200 | Tue Oct 19 10:23:54 2004 + 19500 | 4500 | Wed Oct 19 10:23:54 2005 + 20500 | 4800 | Thu Oct 19 10:23:54 2006 + 16000 | 6000 | Fri Oct 19 10:23:54 2007 + 11200 | 5200 | Sun Oct 19 10:23:54 2008 + (10 rows) + + select sum(salary) over (order by enroll_timestamp range between current row and '2 years'::interval following), + salary, enroll_timestamp from empsalary; + sum | salary | enroll_timestamp + -------+--------+-------------------------- + 18500 | 5200 | Thu Oct 19 10:23:54 2000 + 17200 | 5000 | Fri Oct 19 10:23:54 2001 + 17200 | 3500 | Fri Oct 19 10:23:54 2001 + 12900 | 4800 | Sat Oct 19 10:23:54 2002 + 12600 | 3900 | Sun Oct 19 10:23:54 2003 + 13500 | 4200 | Tue Oct 19 10:23:54 2004 + 15300 | 4500 | Wed Oct 19 10:23:54 2005 + 16000 | 4800 | Thu Oct 19 10:23:54 2006 + 11200 | 6000 | Fri Oct 19 10:23:54 2007 + 5200 | 5200 | Sun Oct 19 10:23:54 2008 + (10 rows) + + select sum(salary) over (order by enroll_timestamp range between '1 year'::interval preceding and current row), + salary, enroll_timestamp from empsalary; + sum | salary | enroll_timestamp + -------+--------+-------------------------- + 5200 | 5200 | Thu Oct 19 10:23:54 2000 + 13700 | 5000 | Fri Oct 19 10:23:54 2001 + 13700 | 3500 | Fri Oct 19 10:23:54 2001 + 13300 | 4800 | Sat Oct 19 10:23:54 2002 + 8700 | 3900 | Sun Oct 19 10:23:54 2003 + 8100 | 4200 | Tue Oct 19 10:23:54 2004 + 8700 | 4500 | Wed Oct 19 10:23:54 2005 + 9300 | 4800 | Thu Oct 19 10:23:54 2006 + 10800 | 6000 | Fri Oct 19 10:23:54 2007 + 11200 | 5200 | Sun Oct 19 10:23:54 2008 + (10 rows) + + select sum(salary) over(order by enroll_date range between unbounded preceding and '1 year'::interval following), + salary, enroll_date from empsalary; + sum | salary | enroll_date + -------+--------+------------- + 34900 | 5000 | 10-01-2006 + 34900 | 6000 | 10-01-2006 + 38400 | 3900 | 12-23-2006 + 47100 | 4800 | 08-01-2007 + 47100 | 5200 | 08-01-2007 + 47100 | 4800 | 08-08-2007 + 47100 | 5200 | 08-15-2007 + 47100 | 3500 | 12-10-2007 + 47100 | 4500 | 01-01-2008 + 47100 | 4200 | 01-01-2008 + (10 rows) + + select sum(salary) over(order by enroll_date range between unbounded preceding and '1 year'::interval following + exclude current row), salary, enroll_date from empsalary; + sum | salary | enroll_date + -------+--------+------------- + 29900 | 5000 | 10-01-2006 + 28900 | 6000 | 10-01-2006 + 34500 | 3900 | 12-23-2006 + 42300 | 4800 | 08-01-2007 + 41900 | 5200 | 08-01-2007 + 42300 | 4800 | 08-08-2007 + 41900 | 5200 | 08-15-2007 + 43600 | 3500 | 12-10-2007 + 42600 | 4500 | 01-01-2008 + 42900 | 4200 | 01-01-2008 + (10 rows) + + select sum(salary) over(order by enroll_date range between unbounded preceding and '1 year'::interval following + exclude group), salary, enroll_date from empsalary; + sum | salary | enroll_date + -------+--------+------------- + 23900 | 5000 | 10-01-2006 + 23900 | 6000 | 10-01-2006 + 34500 | 3900 | 12-23-2006 + 37100 | 4800 | 08-01-2007 + 37100 | 5200 | 08-01-2007 + 42300 | 4800 | 08-08-2007 + 41900 | 5200 | 08-15-2007 + 43600 | 3500 | 12-10-2007 + 38400 | 4500 | 01-01-2008 + 38400 | 4200 | 01-01-2008 + (10 rows) + + select sum(salary) over(order by enroll_date range between unbounded preceding and '1 year'::interval following + exclude ties), salary, enroll_date from empsalary; + sum | salary | enroll_date + -------+--------+------------- + 28900 | 5000 | 10-01-2006 + 29900 | 6000 | 10-01-2006 + 38400 | 3900 | 12-23-2006 + 41900 | 4800 | 08-01-2007 + 42300 | 5200 | 08-01-2007 + 47100 | 4800 | 08-08-2007 + 47100 | 5200 | 08-15-2007 + 47100 | 3500 | 12-10-2007 + 42900 | 4500 | 01-01-2008 + 42600 | 4200 | 01-01-2008 + (10 rows) + + select first_value(salary) over(order by salary range between 1000 preceding and 1000 following), + lead(salary) over(order by salary range between 1000 preceding and 1000 following), + nth_value(salary, 1) over(order by salary range between 1000 preceding and 1000 following), + salary from empsalary; + first_value | lead | nth_value | salary + -------------+------+-----------+-------- + 3500 | 3900 | 3500 | 3500 + 3500 | 4200 | 3500 | 3900 + 3500 | 4500 | 3500 | 4200 + 3500 | 4800 | 3500 | 4500 + 3900 | 4800 | 3900 | 4800 + 3900 | 5000 | 3900 | 4800 + 4200 | 5200 | 4200 | 5000 + 4200 | 5200 | 4200 | 5200 + 4200 | 6000 | 4200 | 5200 + 5000 | | 5000 | 6000 + (10 rows) + + select last_value(salary) over(order by salary range between 1000 preceding and 1000 following), + lag(salary) over(order by salary range between 1000 preceding and 1000 following), + salary from empsalary; + last_value | lag | salary + ------------+------+-------- + 4500 | | 3500 + 4800 | 3500 | 3900 + 5200 | 3900 | 4200 + 5200 | 4200 | 4500 + 5200 | 4500 | 4800 + 5200 | 4800 | 4800 + 6000 | 4800 | 5000 + 6000 | 5000 | 5200 + 6000 | 5200 | 5200 + 6000 | 5200 | 6000 + (10 rows) + + select first_value(salary) over(order by salary range between 1000 following and 3000 following + exclude current row), + lead(salary) over(order by salary range between 1000 following and 3000 following exclude ties), + nth_value(salary, 1) over(order by salary range between 1000 following and 3000 following + exclude ties), + salary from empsalary; + first_value | lead | nth_value | salary + -------------+------+-----------+-------- + 4500 | 3900 | 4500 | 3500 + 5000 | 4200 | 5000 | 3900 + 5200 | 4500 | 5200 | 4200 + 6000 | 4800 | 6000 | 4500 + 6000 | 4800 | 6000 | 4800 + 6000 | 5000 | 6000 | 4800 + 6000 | 5200 | 6000 | 5000 + | 5200 | | 5200 + | 6000 | | 5200 + | | | 6000 + (10 rows) + + select last_value(salary) over(order by salary range between 1000 following and 3000 following + exclude group), + lag(salary) over(order by salary range between 1000 following and 3000 following exclude group), + salary from empsalary; + last_value | lag | salary + ------------+------+-------- + 6000 | | 3500 + 6000 | 3500 | 3900 + 6000 | 3900 | 4200 + 6000 | 4200 | 4500 + 6000 | 4500 | 4800 + 6000 | 4800 | 4800 + 6000 | 4800 | 5000 + | 5000 | 5200 + | 5200 | 5200 + | 5200 | 6000 + (10 rows) + + select first_value(salary) over(order by enroll_date range between unbounded preceding and '1 year'::interval following + exclude ties), + last_value(salary) over(order by enroll_date range between unbounded preceding and '1 year'::interval following), + salary, enroll_date from empsalary; + first_value | last_value | salary | enroll_date + -------------+------------+--------+------------- + 5000 | 5200 | 5000 | 10-01-2006 + 6000 | 5200 | 6000 | 10-01-2006 + 5000 | 3500 | 3900 | 12-23-2006 + 5000 | 4200 | 4800 | 08-01-2007 + 5000 | 4200 | 5200 | 08-01-2007 + 5000 | 4200 | 4800 | 08-08-2007 + 5000 | 4200 | 5200 | 08-15-2007 + 5000 | 4200 | 3500 | 12-10-2007 + 5000 | 4200 | 4500 | 01-01-2008 + 5000 | 4200 | 4200 | 01-01-2008 + (10 rows) + + select first_value(salary) over(order by enroll_date range between unbounded preceding and '1 year'::interval following + exclude ties), + last_value(salary) over(order by enroll_date range between unbounded preceding and '1 year'::interval following + exclude ties), + salary, enroll_date from empsalary; + first_value | last_value | salary | enroll_date + -------------+------------+--------+------------- + 5000 | 5200 | 5000 | 10-01-2006 + 6000 | 5200 | 6000 | 10-01-2006 + 5000 | 3500 | 3900 | 12-23-2006 + 5000 | 4200 | 4800 | 08-01-2007 + 5000 | 4200 | 5200 | 08-01-2007 + 5000 | 4200 | 4800 | 08-08-2007 + 5000 | 4200 | 5200 | 08-15-2007 + 5000 | 4200 | 3500 | 12-10-2007 + 5000 | 4500 | 4500 | 01-01-2008 + 5000 | 4200 | 4200 | 01-01-2008 + (10 rows) + + select first_value(salary) over(order by enroll_date range between unbounded preceding and '1 year'::interval following + exclude group), + last_value(salary) over(order by enroll_date range between unbounded preceding and '1 year'::interval following + exclude group), + salary, enroll_date from empsalary; + first_value | last_value | salary | enroll_date + -------------+------------+--------+------------- + 3900 | 5200 | 5000 | 10-01-2006 + 3900 | 5200 | 6000 | 10-01-2006 + 5000 | 3500 | 3900 | 12-23-2006 + 5000 | 4200 | 4800 | 08-01-2007 + 5000 | 4200 | 5200 | 08-01-2007 + 5000 | 4200 | 4800 | 08-08-2007 + 5000 | 4200 | 5200 | 08-15-2007 + 5000 | 4200 | 3500 | 12-10-2007 + 5000 | 3500 | 4500 | 01-01-2008 + 5000 | 3500 | 4200 | 01-01-2008 + (10 rows) + + select first_value(salary) over(order by enroll_date range between unbounded preceding and '1 year'::interval following + exclude current row), + last_value(salary) over(order by enroll_date range between unbounded preceding and '1 year'::interval following + exclude current row), + salary, enroll_date from empsalary; + first_value | last_value | salary | enroll_date + -------------+------------+--------+------------- + 6000 | 5200 | 5000 | 10-01-2006 + 5000 | 5200 | 6000 | 10-01-2006 + 5000 | 3500 | 3900 | 12-23-2006 + 5000 | 4200 | 4800 | 08-01-2007 + 5000 | 4200 | 5200 | 08-01-2007 + 5000 | 4200 | 4800 | 08-08-2007 + 5000 | 4200 | 5200 | 08-15-2007 + 5000 | 4200 | 3500 | 12-10-2007 + 5000 | 4200 | 4500 | 01-01-2008 + 5000 | 4500 | 4200 | 01-01-2008 + (10 rows) + + -- RANGE BETWEEN with null values + select x, y, + first_value(y) over w, + last_value(y) over w + from + (select x, x as y from generate_series(1,5) as x + union all select null, 42 + union all select null, 43) ss + window w as + (order by x asc nulls first range between 2 preceding and 2 following); + x | y | first_value | last_value + ---+----+-------------+------------ + | 42 | 42 | 43 + | 43 | 42 | 43 + 1 | 1 | 1 | 3 + 2 | 2 | 1 | 4 + 3 | 3 | 1 | 5 + 4 | 4 | 2 | 5 + 5 | 5 | 3 | 5 + (7 rows) + + select x, y, + first_value(y) over w, + last_value(y) over w + from + (select x, x as y from generate_series(1,5) as x + union all select null, 42 + union all select null, 43) ss + window w as + (order by x asc nulls last range between 2 preceding and 2 following); + x | y | first_value | last_value + ---+----+-------------+------------ + 1 | 1 | 1 | 3 + 2 | 2 | 1 | 4 + 3 | 3 | 1 | 5 + 4 | 4 | 2 | 5 + 5 | 5 | 3 | 5 + | 42 | 42 | 43 + | 43 | 42 | 43 + (7 rows) + + select x, y, + first_value(y) over w, + last_value(y) over w + from + (select x, x as y from generate_series(1,5) as x + union all select null, 42 + union all select null, 43) ss + window w as + (order by x desc nulls first range between 2 preceding and 2 following); + x | y | first_value | last_value + ---+----+-------------+------------ + | 43 | 43 | 42 + | 42 | 43 | 42 + 5 | 5 | 5 | 3 + 4 | 4 | 5 | 2 + 3 | 3 | 5 | 1 + 2 | 2 | 4 | 1 + 1 | 1 | 3 | 1 + (7 rows) + + select x, y, + first_value(y) over w, + last_value(y) over w + from + (select x, x as y from generate_series(1,5) as x + union all select null, 42 + union all select null, 43) ss + window w as + (order by x desc nulls last range between 2 preceding and 2 following); + x | y | first_value | last_value + ---+----+-------------+------------ + 5 | 5 | 5 | 3 + 4 | 4 | 5 | 2 + 3 | 3 | 5 | 1 + 2 | 2 | 4 | 1 + 1 | 1 | 3 | 1 + | 42 | 42 | 43 + | 43 | 42 | 43 + (7 rows) + + -- RANGE BETWEEN with values negative tests + select sum(salary) over (order by enroll_timestamp, enroll_date range between '1 year'::interval preceding and '2 years'::interval following + exclude ties), salary, enroll_timestamp from empsalary; + ERROR: RANGE with value PRECEDING/FOLLOWING requires exactly one ORDER BY column + LINE 1: select sum(salary) over (order by enroll_timestamp, enroll_d... + ^ + select sum(salary) over (range between '1 year'::interval preceding and '2 years'::interval following + exclude ties), salary, enroll_timestamp from empsalary; + ERROR: RANGE with value PRECEDING/FOLLOWING requires exactly one ORDER BY column + LINE 1: select sum(salary) over (range between '1 year'::interval pr... + ^ + select sum(salary) over (order by depname range between '1 year'::interval preceding and '2 years'::interval following + exclude ties), salary, enroll_timestamp from empsalary; + ERROR: RANGE with value PRECEDING/FOLLOWING is not supported for column type text + LINE 1: ... sum(salary) over (order by depname range between '1 year'::... + ^ + select max(enroll_date) over (order by enroll_timestamp range between 1 preceding and 2 following + exclude ties), salary, enroll_timestamp from empsalary; + ERROR: RANGE with value PRECEDING/FOLLOWING is not supported for column type timestamp without time zone and offset type integer + LINE 1: ...te) over (order by enroll_timestamp range between 1 precedin... + ^ + HINT: Cast the offset value to an appropriate type. + select max(enroll_date) over (order by salary range between -1 preceding and 2 following + exclude ties), salary, enroll_timestamp from empsalary; + ERROR: invalid preceding or following size in window function + select max(enroll_date) over (order by salary range between 1 preceding and -2 following + exclude ties), salary, enroll_timestamp from empsalary; + ERROR: invalid preceding or following size in window function + select max(enroll_date) over (order by salary range between '1 year'::interval preceding and '2 years'::interval following + exclude ties), salary, enroll_timestamp from empsalary; + ERROR: RANGE with value PRECEDING/FOLLOWING is not supported for column type integer and offset type interval + LINE 1: ...(enroll_date) over (order by salary range between '1 year'::... + ^ + HINT: Cast the offset value to an appropriate type. + -- GROUPS tests + SELECT sum(unique1) over (order by four groups between unbounded preceding and current row), + unique1, four + FROM tenk1 WHERE unique1 < 10; + sum | unique1 | four + -----+---------+------ + 12 | 0 | 0 + 12 | 8 | 0 + 12 | 4 | 0 + 27 | 5 | 1 + 27 | 9 | 1 + 27 | 1 | 1 + 35 | 6 | 2 + 35 | 2 | 2 + 45 | 3 | 3 + 45 | 7 | 3 + (10 rows) + + SELECT sum(unique1) over (order by four groups between unbounded preceding and unbounded following), + unique1, four + FROM tenk1 WHERE unique1 < 10; + sum | unique1 | four + -----+---------+------ + 45 | 0 | 0 + 45 | 8 | 0 + 45 | 4 | 0 + 45 | 5 | 1 + 45 | 9 | 1 + 45 | 1 | 1 + 45 | 6 | 2 + 45 | 2 | 2 + 45 | 3 | 3 + 45 | 7 | 3 + (10 rows) + + SELECT sum(unique1) over (order by four groups 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 (order by four groups between 1 preceding and unbounded following), + unique1, four + FROM tenk1 WHERE unique1 < 10; + sum | unique1 | four + -----+---------+------ + 45 | 0 | 0 + 45 | 8 | 0 + 45 | 4 | 0 + 45 | 5 | 1 + 45 | 9 | 1 + 45 | 1 | 1 + 33 | 6 | 2 + 33 | 2 | 2 + 18 | 3 | 3 + 18 | 7 | 3 + (10 rows) + + SELECT sum(unique1) over (order by four groups between 1 following and unbounded following), + unique1, four + FROM tenk1 WHERE unique1 < 10; + sum | unique1 | four + -----+---------+------ + 33 | 0 | 0 + 33 | 8 | 0 + 33 | 4 | 0 + 18 | 5 | 1 + 18 | 9 | 1 + 18 | 1 | 1 + 10 | 6 | 2 + 10 | 2 | 2 + | 3 | 3 + | 7 | 3 + (10 rows) + + SELECT sum(unique1) over (order by four groups between unbounded preceding and 2 following), + unique1, four + FROM tenk1 WHERE unique1 < 10; + sum | unique1 | four + -----+---------+------ + 35 | 0 | 0 + 35 | 8 | 0 + 35 | 4 | 0 + 45 | 5 | 1 + 45 | 9 | 1 + 45 | 1 | 1 + 45 | 6 | 2 + 45 | 2 | 2 + 45 | 3 | 3 + 45 | 7 | 3 + (10 rows) + + SELECT sum(unique1) over (order by four groups between 2 preceding and 1 preceding), + unique1, four + FROM tenk1 WHERE unique1 < 10; + sum | unique1 | four + -----+---------+------ + | 0 | 0 + | 8 | 0 + | 4 | 0 + 12 | 5 | 1 + 12 | 9 | 1 + 12 | 1 | 1 + 27 | 6 | 2 + 27 | 2 | 2 + 23 | 3 | 3 + 23 | 7 | 3 + (10 rows) + + SELECT sum(unique1) over (order by four groups between 2 preceding and 1 following), + unique1, four + FROM tenk1 WHERE unique1 < 10; + sum | unique1 | four + -----+---------+------ + 27 | 0 | 0 + 27 | 8 | 0 + 27 | 4 | 0 + 35 | 5 | 1 + 35 | 9 | 1 + 35 | 1 | 1 + 45 | 6 | 2 + 45 | 2 | 2 + 33 | 3 | 3 + 33 | 7 | 3 + (10 rows) + + SELECT sum(unique1) over (order by four groups between 0 preceding and 0 following), + unique1, four + FROM tenk1 WHERE unique1 < 10; + sum | unique1 | four + -----+---------+------ + 12 | 0 | 0 + 12 | 8 | 0 + 12 | 4 | 0 + 15 | 5 | 1 + 15 | 9 | 1 + 15 | 1 | 1 + 8 | 6 | 2 + 8 | 2 | 2 + 10 | 3 | 3 + 10 | 7 | 3 + (10 rows) + + SELECT sum(unique1) over (order by four groups between 2 preceding and 1 following + exclude current row), unique1, four + FROM tenk1 WHERE unique1 < 10; + sum | unique1 | four + -----+---------+------ + 27 | 0 | 0 + 19 | 8 | 0 + 23 | 4 | 0 + 30 | 5 | 1 + 26 | 9 | 1 + 34 | 1 | 1 + 39 | 6 | 2 + 43 | 2 | 2 + 30 | 3 | 3 + 26 | 7 | 3 + (10 rows) + + SELECT sum(unique1) over (order by four groups between 2 preceding and 1 following + exclude group), unique1, four + FROM tenk1 WHERE unique1 < 10; + sum | unique1 | four + -----+---------+------ + 15 | 0 | 0 + 15 | 8 | 0 + 15 | 4 | 0 + 20 | 5 | 1 + 20 | 9 | 1 + 20 | 1 | 1 + 37 | 6 | 2 + 37 | 2 | 2 + 23 | 3 | 3 + 23 | 7 | 3 + (10 rows) + + SELECT sum(unique1) over (order by four groups between 2 preceding and 1 following + exclude ties), unique1, four + FROM tenk1 WHERE unique1 < 10; + sum | unique1 | four + -----+---------+------ + 15 | 0 | 0 + 23 | 8 | 0 + 19 | 4 | 0 + 25 | 5 | 1 + 29 | 9 | 1 + 21 | 1 | 1 + 43 | 6 | 2 + 39 | 2 | 2 + 26 | 3 | 3 + 30 | 7 | 3 + (10 rows) + + SELECT sum(unique1) over (partition by ten + order by four groups between 0 preceding and 0 following),unique1, four, ten + FROM tenk1 WHERE unique1 < 10; + sum | unique1 | four | ten + -----+---------+------+----- + 0 | 0 | 0 | 0 + 1 | 1 | 1 | 1 + 2 | 2 | 2 | 2 + 3 | 3 | 3 | 3 + 4 | 4 | 0 | 4 + 5 | 5 | 1 | 5 + 6 | 6 | 2 | 6 + 7 | 7 | 3 | 7 + 8 | 8 | 0 | 8 + 9 | 9 | 1 | 9 + (10 rows) + + SELECT sum(unique1) over (partition by ten + order by four groups between 0 preceding and 0 following exclude current row), unique1, four, ten + FROM tenk1 WHERE unique1 < 10; + sum | unique1 | four | ten + -----+---------+------+----- + | 0 | 0 | 0 + | 1 | 1 | 1 + | 2 | 2 | 2 + | 3 | 3 | 3 + | 4 | 0 | 4 + | 5 | 1 | 5 + | 6 | 2 | 6 + | 7 | 3 | 7 + | 8 | 0 | 8 + | 9 | 1 | 9 + (10 rows) + + SELECT sum(unique1) over (partition by ten + order by four groups between 0 preceding and 0 following exclude group), unique1, four, ten + FROM tenk1 WHERE unique1 < 10; + sum | unique1 | four | ten + -----+---------+------+----- + | 0 | 0 | 0 + | 1 | 1 | 1 + | 2 | 2 | 2 + | 3 | 3 | 3 + | 4 | 0 | 4 + | 5 | 1 | 5 + | 6 | 2 | 6 + | 7 | 3 | 7 + | 8 | 0 | 8 + | 9 | 1 | 9 + (10 rows) + + SELECT sum(unique1) over (partition by ten + order by four groups between 0 preceding and 0 following exclude ties), unique1, four, ten + FROM tenk1 WHERE unique1 < 10; + sum | unique1 | four | ten + -----+---------+------+----- + 0 | 0 | 0 | 0 + 1 | 1 | 1 | 1 + 2 | 2 | 2 | 2 + 3 | 3 | 3 | 3 + 4 | 4 | 0 | 4 + 5 | 5 | 1 | 5 + 6 | 6 | 2 | 6 + 7 | 7 | 3 | 7 + 8 | 8 | 0 | 8 + 9 | 9 | 1 | 9 + (10 rows) + + select first_value(salary) over(order by enroll_date groups between 1 preceding and 1 following), + lead(salary) over(order by enroll_date groups between 1 preceding and 1 following), + nth_value(salary, 1) over(order by enroll_date groups between 1 preceding and 1 following), + salary, enroll_date from empsalary; + first_value | lead | nth_value | salary | enroll_date + -------------+------+-----------+--------+------------- + 5000 | 6000 | 5000 | 5000 | 10-01-2006 + 5000 | 3900 | 5000 | 6000 | 10-01-2006 + 5000 | 4800 | 5000 | 3900 | 12-23-2006 + 3900 | 5200 | 3900 | 4800 | 08-01-2007 + 3900 | 4800 | 3900 | 5200 | 08-01-2007 + 4800 | 5200 | 4800 | 4800 | 08-08-2007 + 4800 | 3500 | 4800 | 5200 | 08-15-2007 + 5200 | 4500 | 5200 | 3500 | 12-10-2007 + 3500 | 4200 | 3500 | 4500 | 01-01-2008 + 3500 | | 3500 | 4200 | 01-01-2008 + (10 rows) + + select last_value(salary) over(order by enroll_date groups between 1 preceding and 1 following), + lag(salary) over(order by enroll_date groups between 1 preceding and 1 following), + salary, enroll_date from empsalary; + last_value | lag | salary | enroll_date + ------------+------+--------+------------- + 3900 | | 5000 | 10-01-2006 + 3900 | 5000 | 6000 | 10-01-2006 + 5200 | 6000 | 3900 | 12-23-2006 + 4800 | 3900 | 4800 | 08-01-2007 + 4800 | 4800 | 5200 | 08-01-2007 + 5200 | 5200 | 4800 | 08-08-2007 + 3500 | 4800 | 5200 | 08-15-2007 + 4200 | 5200 | 3500 | 12-10-2007 + 4200 | 3500 | 4500 | 01-01-2008 + 4200 | 4500 | 4200 | 01-01-2008 + (10 rows) + + select first_value(salary) over(order by enroll_date groups between 1 following and 3 following + exclude current row), + lead(salary) over(order by enroll_date groups between 1 following and 3 following exclude ties), + nth_value(salary, 1) over(order by enroll_date groups between 1 following and 3 following + exclude ties), + salary, enroll_date from empsalary; + first_value | lead | nth_value | salary | enroll_date + -------------+------+-----------+--------+------------- + 3900 | 6000 | 3900 | 5000 | 10-01-2006 + 3900 | 3900 | 3900 | 6000 | 10-01-2006 + 4800 | 4800 | 4800 | 3900 | 12-23-2006 + 4800 | 5200 | 4800 | 4800 | 08-01-2007 + 4800 | 4800 | 4800 | 5200 | 08-01-2007 + 5200 | 5200 | 5200 | 4800 | 08-08-2007 + 3500 | 3500 | 3500 | 5200 | 08-15-2007 + 4500 | 4500 | 4500 | 3500 | 12-10-2007 + | 4200 | | 4500 | 01-01-2008 + | | | 4200 | 01-01-2008 + (10 rows) + + select last_value(salary) over(order by enroll_date groups between 1 following and 3 following + exclude group), + lag(salary) over(order by enroll_date groups between 1 following and 3 following exclude group), + salary, enroll_date from empsalary; + last_value | lag | salary | enroll_date + ------------+------+--------+------------- + 4800 | | 5000 | 10-01-2006 + 4800 | 5000 | 6000 | 10-01-2006 + 5200 | 6000 | 3900 | 12-23-2006 + 3500 | 3900 | 4800 | 08-01-2007 + 3500 | 4800 | 5200 | 08-01-2007 + 4200 | 5200 | 4800 | 08-08-2007 + 4200 | 4800 | 5200 | 08-15-2007 + 4200 | 5200 | 3500 | 12-10-2007 + | 3500 | 4500 | 01-01-2008 + | 4500 | 4200 | 01-01-2008 + (10 rows) + + -- Show differences in values mode between ROWS, RANGE, and GROUPS + WITH cte (x) AS ( + SELECT * FROM generate_series(1, 35, 2) + ) + SELECT x, (sum(x) over w) + FROM cte + WINDOW w AS (ORDER BY x rows between 1 preceding and 1 following); + x | sum + ----+----- + 1 | 4 + 3 | 9 + 5 | 15 + 7 | 21 + 9 | 27 + 11 | 33 + 13 | 39 + 15 | 45 + 17 | 51 + 19 | 57 + 21 | 63 + 23 | 69 + 25 | 75 + 27 | 81 + 29 | 87 + 31 | 93 + 33 | 99 + 35 | 68 + (18 rows) + + WITH cte (x) AS ( + SELECT * FROM generate_series(1, 35, 2) + ) + SELECT x, (sum(x) over w) + FROM cte + WINDOW w AS (ORDER BY x range between 1 preceding and 1 following); + x | sum + ----+----- + 1 | 1 + 3 | 3 + 5 | 5 + 7 | 7 + 9 | 9 + 11 | 11 + 13 | 13 + 15 | 15 + 17 | 17 + 19 | 19 + 21 | 21 + 23 | 23 + 25 | 25 + 27 | 27 + 29 | 29 + 31 | 31 + 33 | 33 + 35 | 35 + (18 rows) + + WITH cte (x) AS ( + SELECT * FROM generate_series(1, 35, 2) + ) + SELECT x, (sum(x) over w) + FROM cte + WINDOW w AS (ORDER BY x groups between 1 preceding and 1 following); + x | sum + ----+----- + 1 | 4 + 3 | 9 + 5 | 15 + 7 | 21 + 9 | 27 + 11 | 33 + 13 | 39 + 15 | 45 + 17 | 51 + 19 | 57 + 21 | 63 + 23 | 69 + 25 | 75 + 27 | 81 + 29 | 87 + 31 | 93 + 33 | 99 + 35 | 68 + (18 rows) + + WITH cte (x) AS ( + select 1 union all select 1 union all select 1 union all + SELECT * FROM generate_series(5, 49, 2) + ) + SELECT x, (sum(x) over w) + FROM cte + WINDOW w AS (ORDER BY x rows between 1 preceding and 1 following); + x | sum + ----+----- + 1 | 2 + 1 | 3 + 1 | 7 + 5 | 13 + 7 | 21 + 9 | 27 + 11 | 33 + 13 | 39 + 15 | 45 + 17 | 51 + 19 | 57 + 21 | 63 + 23 | 69 + 25 | 75 + 27 | 81 + 29 | 87 + 31 | 93 + 33 | 99 + 35 | 105 + 37 | 111 + 39 | 117 + 41 | 123 + 43 | 129 + 45 | 135 + 47 | 141 + 49 | 96 + (26 rows) + + WITH cte (x) AS ( + select 1 union all select 1 union all select 1 union all + SELECT * FROM generate_series(5, 49, 2) + ) + SELECT x, (sum(x) over w) + FROM cte + WINDOW w AS (ORDER BY x range between 1 preceding and 1 following); + x | sum + ----+----- + 1 | 3 + 1 | 3 + 1 | 3 + 5 | 5 + 7 | 7 + 9 | 9 + 11 | 11 + 13 | 13 + 15 | 15 + 17 | 17 + 19 | 19 + 21 | 21 + 23 | 23 + 25 | 25 + 27 | 27 + 29 | 29 + 31 | 31 + 33 | 33 + 35 | 35 + 37 | 37 + 39 | 39 + 41 | 41 + 43 | 43 + 45 | 45 + 47 | 47 + 49 | 49 + (26 rows) + + WITH cte (x) AS ( + select 1 union all select 1 union all select 1 union all + SELECT * FROM generate_series(5, 49, 2) + ) + SELECT x, (sum(x) over w) + FROM cte + WINDOW w AS (ORDER BY x groups between 1 preceding and 1 following); + x | sum + ----+----- + 1 | 8 + 1 | 8 + 1 | 8 + 5 | 15 + 7 | 21 + 9 | 27 + 11 | 33 + 13 | 39 + 15 | 45 + 17 | 51 + 19 | 57 + 21 | 63 + 23 | 69 + 25 | 75 + 27 | 81 + 29 | 87 + 31 | 93 + 33 | 99 + 35 | 105 + 37 | 111 + 39 | 117 + 41 | 123 + 43 | 129 + 45 | 135 + 47 | 141 + 49 | 96 + (26 rows) + -- with UNION SELECT count(*) OVER (PARTITION BY four) FROM (SELECT * FROM tenk1 UNION ALL SELECT * FROM tenk2)s LIMIT 0; count diff --git a/src/test/regress/sql/window.sql b/src/test/regress/sql/window.sql index e2a1a1c..a724864 100644 *** a/src/test/regress/sql/window.sql --- b/src/test/regress/sql/window.sql *************** CREATE TEMPORARY TABLE empsalary ( *** 6,25 **** depname varchar, empno bigint, salary int, ! enroll_date date ); INSERT INTO empsalary VALUES ! ('develop', 10, 5200, '2007-08-01'), ! ('sales', 1, 5000, '2006-10-01'), ! ('personnel', 5, 3500, '2007-12-10'), ! ('sales', 4, 4800, '2007-08-08'), ! ('personnel', 2, 3900, '2006-12-23'), ! ('develop', 7, 4200, '2008-01-01'), ! ('develop', 9, 4500, '2008-01-01'), ! ('sales', 3, 4800, '2007-08-01'), ! ('develop', 8, 6000, '2006-10-01'), ! ('develop', 11, 5200, '2007-08-15'); SELECT depname, empno, salary, sum(salary) OVER (PARTITION BY depname) FROM empsalary ORDER BY depname, salary; --- 6,30 ---- depname varchar, empno bigint, salary int, ! enroll_date date, ! enroll_time time, ! enroll_timetz timetz, ! enroll_interval interval, ! enroll_timestamptz timestamptz, ! enroll_timestamp timestamp ); INSERT INTO empsalary VALUES ! ('develop', 10, 5200, '2007-08-01', '11:00', '11:00 BST', '1 year'::interval, TIMESTAMP '2000-10-19 10:23:54+01', TIMESTAMP '2000-10-19 10:23:54'), ! ('sales', 1, 5000, '2006-10-01', '12:00', '12:00 BST', '2 years'::interval, TIMESTAMP '2001-10-19 10:23:54+01', TIMESTAMP '2001-10-19 10:23:54'), ! ('personnel', 5, 3500, '2007-12-10', '13:00', '13:00 BST', '3 years'::interval, TIMESTAMP '2001-10-19 10:23:54+01', TIMESTAMP '2001-10-19 10:23:54'), ! ('sales', 4, 4800, '2007-08-08', '14:00', '14:00 BST', '4 years'::interval, TIMESTAMP '2002-10-19 10:23:54+01', TIMESTAMP '2002-10-19 10:23:54'), ! ('personnel', 2, 3900, '2006-12-23', '15:00', '15:00 BST', '5 years'::interval, TIMESTAMP '2003-10-19 10:23:54+01', TIMESTAMP '2003-10-19 10:23:54'), ! ('develop', 7, 4200, '2008-01-01', '15:00', '15:00 BST', '5 years'::interval, TIMESTAMP '2004-10-19 10:23:54+01', TIMESTAMP '2004-10-19 10:23:54'), ! ('develop', 9, 4500, '2008-01-01', '17:00', '17:00 BST', '7 years'::interval, TIMESTAMP '2005-10-19 10:23:54+01', TIMESTAMP '2005-10-19 10:23:54'), ! ('sales', 3, 4800, '2007-08-01', '18:00', '18:00 BST', '8 years'::interval, TIMESTAMP '2006-10-19 10:23:54+01', TIMESTAMP '2006-10-19 10:23:54'), ! ('develop', 8, 6000, '2006-10-01', '19:00', '19:00 BST', '9 years'::interval, TIMESTAMP '2007-10-19 10:23:54+01', TIMESTAMP '2007-10-19 10:23:54'), ! ('develop', 11, 5200, '2007-08-15', '20:00', '20:00 BST', '10 years'::interval, TIMESTAMP '2008-10-19 10:23:54+01', TIMESTAMP '2008-10-19 10:23:54'); SELECT depname, empno, salary, sum(salary) OVER (PARTITION BY depname) FROM empsalary ORDER BY depname, salary; *************** SELECT sum(unique1) over (rows between 2 *** 189,194 **** --- 194,239 ---- unique1, four FROM tenk1 WHERE unique1 < 10; + SELECT sum(unique1) over (rows between 2 preceding and 2 following exclude no others), + unique1, four + FROM tenk1 WHERE unique1 < 10; + + SELECT sum(unique1) over (rows between 2 preceding and 2 following exclude current row), + unique1, four + FROM tenk1 WHERE unique1 < 10; + + SELECT sum(unique1) over (rows between 2 preceding and 2 following exclude group), + unique1, four + FROM tenk1 WHERE unique1 < 10; + + SELECT sum(unique1) over (rows between 2 preceding and 2 following exclude ties), + unique1, four + FROM tenk1 WHERE unique1 < 10; + + SELECT first_value(unique1) over (ORDER BY four rows between current row and 2 following exclude current row), + unique1, four + FROM tenk1 WHERE unique1 < 10; + + SELECT first_value(unique1) over (ORDER BY four rows between current row and 2 following exclude group), + unique1, four + FROM tenk1 WHERE unique1 < 10; + + SELECT first_value(unique1) over (ORDER BY four rows between current row and 2 following exclude ties), + unique1, four + FROM tenk1 WHERE unique1 < 10; + + SELECT last_value(unique1) over (ORDER BY four rows between current row and 2 following exclude current row), + unique1, four + FROM tenk1 WHERE unique1 < 10; + + SELECT last_value(unique1) over (ORDER BY four rows between current row and 2 following exclude group), + unique1, four + FROM tenk1 WHERE unique1 < 10; + + SELECT last_value(unique1) over (ORDER BY four rows between current row and 2 following exclude ties), + 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 (w range betwee *** 205,214 **** unique1, four FROM tenk1 WHERE unique1 < 10 WINDOW w AS (order by four); ! -- fail: not implemented yet ! SELECT sum(unique1) over (order by four range between 2::int8 preceding and 1::int2 preceding), unique1, four ! FROM tenk1 WHERE unique1 < 10; SELECT first_value(unique1) over w, nth_value(unique1, 2) over w AS nth_2, --- 250,266 ---- unique1, four FROM tenk1 WHERE unique1 < 10 WINDOW w AS (order by four); ! SELECT sum(unique1) over (w range between unbounded preceding and current row exclude current row), unique1, four ! FROM tenk1 WHERE unique1 < 10 WINDOW w AS (order by four); ! ! SELECT sum(unique1) over (w range between unbounded preceding and current row exclude group), ! unique1, four ! FROM tenk1 WHERE unique1 < 10 WINDOW w AS (order by four); ! ! SELECT sum(unique1) over (w range between unbounded preceding and current row exclude ties), ! 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, *************** SELECT * FROM v_window; *** 230,235 **** --- 282,734 ---- SELECT pg_get_viewdef('v_window'); + CREATE OR REPLACE TEMP VIEW v_window AS + SELECT i, sum(i) over (order by i rows between 1 preceding and 1 following + exclude current row) as sum_rows FROM generate_series(1, 10) i; + + SELECT * FROM v_window; + + SELECT pg_get_viewdef('v_window'); + + CREATE OR REPLACE TEMP VIEW v_window AS + SELECT i, sum(i) over (order by i rows between 1 preceding and 1 following + exclude group) as sum_rows FROM generate_series(1, 10) i; + + SELECT * FROM v_window; + + SELECT pg_get_viewdef('v_window'); + + CREATE OR REPLACE TEMP VIEW v_window AS + SELECT i, sum(i) over (order by i rows between 1 preceding and 1 following + exclude ties) as sum_rows FROM generate_series(1, 10) i; + + SELECT * FROM v_window; + + SELECT pg_get_viewdef('v_window'); + + CREATE OR REPLACE TEMP VIEW v_window AS + SELECT i, sum(i) over (order by i rows between 1 preceding and 1 following + exclude no others) as sum_rows FROM generate_series(1, 10) i; + + SELECT * FROM v_window; + + SELECT pg_get_viewdef('v_window'); + + CREATE OR REPLACE TEMP VIEW v_window AS + SELECT i, sum(i) over (order by i groups between 1 preceding and 1 following + exclude no others) as sum_rows FROM generate_series(1, 10) i; + + SELECT * FROM v_window; + + SELECT pg_get_viewdef('v_window'); + + -- RANGE BETWEEN with values tests + SELECT sum(unique1) over (order by four range between 2::int8 preceding and 1::int2 preceding), + unique1, four + FROM tenk1 WHERE unique1 < 10; + + SELECT sum(unique1) over (order by four desc range between 2::int8 preceding and 1::int2 preceding), + unique1, four + FROM tenk1 WHERE unique1 < 10; + + SELECT sum(unique1) over (order by four range between 2::int8 preceding and 1::int2 preceding exclude no others), + unique1, four + FROM tenk1 WHERE unique1 < 10; + + SELECT sum(unique1) over (order by four range between 2::int8 preceding and 1::int2 preceding exclude current row), + unique1, four + FROM tenk1 WHERE unique1 < 10; + + SELECT sum(unique1) over (order by four range between 2::int8 preceding and 1::int2 preceding exclude group), + unique1, four + FROM tenk1 WHERE unique1 < 10; + + SELECT sum(unique1) over (order by four range between 2::int8 preceding and 1::int2 preceding exclude ties), + unique1, four + FROM tenk1 WHERE unique1 < 10; + + SELECT sum(unique1) over (order by four range between 2::int8 preceding and 6::int2 following exclude ties), + unique1, four + FROM tenk1 WHERE unique1 < 10; + + SELECT sum(unique1) over (order by four range between 2::int8 preceding and 6::int2 following exclude group), + unique1, four + FROM tenk1 WHERE unique1 < 10; + + SELECT sum(unique1) over (partition by four order by unique1 range between 5::int8 preceding and 6::int2 following), + unique1, four + FROM tenk1 WHERE unique1 < 10; + + SELECT sum(unique1) over (partition by four order by unique1 range between 5::int8 preceding and 6::int2 following + exclude current row),unique1, four + FROM tenk1 WHERE unique1 < 10; + + select sum(salary) over (order by enroll_date range between '1 year'::interval preceding and '1 year'::interval following), + salary, enroll_date from empsalary; + + select sum(salary) over (order by enroll_date desc range between '1 year'::interval preceding and '1 year'::interval following), + salary, enroll_date from empsalary; + + select sum(salary) over (order by enroll_date desc range between '1 year'::interval following and '1 year'::interval following), + salary, enroll_date from empsalary; + + select sum(salary) over (order by enroll_date range between '1 year'::interval preceding and '1 year'::interval following + exclude current row), salary, enroll_date from empsalary; + + select sum(salary) over (order by enroll_date range between '1 year'::interval preceding and '1 year'::interval following + exclude group), salary, enroll_date from empsalary; + + select sum(salary) over (order by enroll_date range between '1 year'::interval preceding and '1 year'::interval following + exclude ties), salary, enroll_date from empsalary; + + select sum(salary) over (order by enroll_time range between '1 hour'::interval preceding and '2 hours'::interval following), + salary, enroll_time from empsalary; + + select sum(salary) over (order by enroll_time desc range between '1 hour'::interval preceding and '2 hours'::interval following), + salary, enroll_time from empsalary; + + select sum(salary) over (order by enroll_time desc range between '1 hour'::interval following and '2 hours'::interval following), + salary, enroll_time from empsalary; + + select sum(salary) over (order by enroll_time range between '1 hour'::interval preceding and '2 hours'::interval following + exclude current row), salary, enroll_time from empsalary; + + select sum(salary) over (order by enroll_time range between '1 hour'::interval preceding and '2 hours'::interval following + exclude group), salary, enroll_time from empsalary; + + select sum(salary) over (order by enroll_time range between '1 hour'::interval preceding and '2 hours'::interval following + exclude ties), salary, enroll_time from empsalary; + + select sum(salary) over (order by enroll_timetz range between '1 hour'::interval preceding and '2 hours'::interval following), + salary, enroll_timetz from empsalary; + + select sum(salary) over (order by enroll_timetz desc range between '1 hour'::interval preceding and '2 hours'::interval following), + salary, enroll_timetz from empsalary; + + select sum(salary) over (order by enroll_timetz desc range between '1 hour'::interval following and '2 hours'::interval following), + salary, enroll_timetz from empsalary; + + select sum(salary) over (order by enroll_timetz range between '1 hour'::interval preceding and '2 hours'::interval following + exclude current row), salary, enroll_timetz from empsalary; + + select sum(salary) over (order by enroll_timetz range between '1 hour'::interval preceding and '2 hours'::interval following + exclude group), salary, enroll_timetz from empsalary; + + select sum(salary) over (order by enroll_timetz range between '1 hour'::interval preceding and '2 hours'::interval following + exclude ties), salary, enroll_timetz from empsalary; + + select sum(salary) over (order by enroll_interval range between '1 year'::interval preceding and '2 years'::interval following), + salary, enroll_interval from empsalary; + + select sum(salary) over (order by enroll_interval desc range between '1 year'::interval preceding and '2 years'::interval following), + salary, enroll_interval from empsalary; + + select sum(salary) over (order by enroll_interval desc range between '1 year'::interval following and '2 years'::interval following), + salary, enroll_interval from empsalary; + + select sum(salary) over (order by enroll_interval range between '1 year'::interval preceding and '2 years'::interval following + exclude current row), salary, enroll_interval from empsalary; + + select sum(salary) over (order by enroll_interval range between '1 year'::interval preceding and '2 years'::interval following + exclude group), salary, enroll_interval from empsalary; + + select sum(salary) over (order by enroll_interval range between '1 year'::interval preceding and '2 years'::interval following + exclude ties), salary, enroll_interval from empsalary; + + select sum(salary) over (order by enroll_timestamptz range between '1 year'::interval preceding and '2 years'::interval following), + salary, enroll_timestamptz from empsalary; + + select sum(salary) over (order by enroll_timestamptz desc range between '1 year'::interval preceding and '2 years'::interval following), + salary, enroll_timestamptz from empsalary; + + select sum(salary) over (order by enroll_timestamptz desc range between '1 year'::interval following and '2 years'::interval following), + salary, enroll_timestamptz from empsalary; + + select sum(salary) over (order by enroll_timestamptz range between '1 year'::interval preceding and '2 years'::interval following + exclude current row), salary, enroll_timestamptz from empsalary; + + select sum(salary) over (order by enroll_timestamptz range between '1 year'::interval preceding and '2 years'::interval following + exclude group), salary, enroll_timestamptz from empsalary; + + select sum(salary) over (order by enroll_timestamptz range between '1 year'::interval preceding and '2 years'::interval following + exclude ties), salary, enroll_timestamptz from empsalary; + + select sum(salary) over (order by enroll_timestamp range between '1 year'::interval preceding and '2 years'::interval following), + salary, enroll_timestamp from empsalary; + + select sum(salary) over (order by enroll_timestamp desc range between '1 year'::interval preceding and '2 years'::interval following), + salary, enroll_timestamp from empsalary; + + select sum(salary) over (order by enroll_timestamp desc range between '1 year'::interval following and '2 years'::interval following), + salary, enroll_timestamp from empsalary; + + select sum(salary) over (order by enroll_timestamp range between '1 year'::interval preceding and '2 years'::interval following + exclude current row), salary, enroll_timestamp from empsalary; + + select sum(salary) over (order by enroll_timestamp range between '1 year'::interval preceding and '2 years'::interval following + exclude group), salary, enroll_timestamp from empsalary; + + select sum(salary) over (order by enroll_timestamp range between '1 year'::interval preceding and '2 years'::interval following + exclude ties), salary, enroll_timestamp from empsalary; + + select sum(salary) over (order by enroll_timestamp range between current row and '2 years'::interval following), + salary, enroll_timestamp from empsalary; + + select sum(salary) over (order by enroll_timestamp range between '1 year'::interval preceding and current row), + salary, enroll_timestamp from empsalary; + + select sum(salary) over(order by enroll_date range between unbounded preceding and '1 year'::interval following), + salary, enroll_date from empsalary; + + select sum(salary) over(order by enroll_date range between unbounded preceding and '1 year'::interval following + exclude current row), salary, enroll_date from empsalary; + + select sum(salary) over(order by enroll_date range between unbounded preceding and '1 year'::interval following + exclude group), salary, enroll_date from empsalary; + + select sum(salary) over(order by enroll_date range between unbounded preceding and '1 year'::interval following + exclude ties), salary, enroll_date from empsalary; + + select first_value(salary) over(order by salary range between 1000 preceding and 1000 following), + lead(salary) over(order by salary range between 1000 preceding and 1000 following), + nth_value(salary, 1) over(order by salary range between 1000 preceding and 1000 following), + salary from empsalary; + + select last_value(salary) over(order by salary range between 1000 preceding and 1000 following), + lag(salary) over(order by salary range between 1000 preceding and 1000 following), + salary from empsalary; + + select first_value(salary) over(order by salary range between 1000 following and 3000 following + exclude current row), + lead(salary) over(order by salary range between 1000 following and 3000 following exclude ties), + nth_value(salary, 1) over(order by salary range between 1000 following and 3000 following + exclude ties), + salary from empsalary; + + select last_value(salary) over(order by salary range between 1000 following and 3000 following + exclude group), + lag(salary) over(order by salary range between 1000 following and 3000 following exclude group), + salary from empsalary; + + select first_value(salary) over(order by enroll_date range between unbounded preceding and '1 year'::interval following + exclude ties), + last_value(salary) over(order by enroll_date range between unbounded preceding and '1 year'::interval following), + salary, enroll_date from empsalary; + + select first_value(salary) over(order by enroll_date range between unbounded preceding and '1 year'::interval following + exclude ties), + last_value(salary) over(order by enroll_date range between unbounded preceding and '1 year'::interval following + exclude ties), + salary, enroll_date from empsalary; + + select first_value(salary) over(order by enroll_date range between unbounded preceding and '1 year'::interval following + exclude group), + last_value(salary) over(order by enroll_date range between unbounded preceding and '1 year'::interval following + exclude group), + salary, enroll_date from empsalary; + + select first_value(salary) over(order by enroll_date range between unbounded preceding and '1 year'::interval following + exclude current row), + last_value(salary) over(order by enroll_date range between unbounded preceding and '1 year'::interval following + exclude current row), + salary, enroll_date from empsalary; + + -- RANGE BETWEEN with null values + select x, y, + first_value(y) over w, + last_value(y) over w + from + (select x, x as y from generate_series(1,5) as x + union all select null, 42 + union all select null, 43) ss + window w as + (order by x asc nulls first range between 2 preceding and 2 following); + + select x, y, + first_value(y) over w, + last_value(y) over w + from + (select x, x as y from generate_series(1,5) as x + union all select null, 42 + union all select null, 43) ss + window w as + (order by x asc nulls last range between 2 preceding and 2 following); + + select x, y, + first_value(y) over w, + last_value(y) over w + from + (select x, x as y from generate_series(1,5) as x + union all select null, 42 + union all select null, 43) ss + window w as + (order by x desc nulls first range between 2 preceding and 2 following); + + select x, y, + first_value(y) over w, + last_value(y) over w + from + (select x, x as y from generate_series(1,5) as x + union all select null, 42 + union all select null, 43) ss + window w as + (order by x desc nulls last range between 2 preceding and 2 following); + + -- RANGE BETWEEN with values negative tests + select sum(salary) over (order by enroll_timestamp, enroll_date range between '1 year'::interval preceding and '2 years'::interval following + exclude ties), salary, enroll_timestamp from empsalary; + + select sum(salary) over (range between '1 year'::interval preceding and '2 years'::interval following + exclude ties), salary, enroll_timestamp from empsalary; + + select sum(salary) over (order by depname range between '1 year'::interval preceding and '2 years'::interval following + exclude ties), salary, enroll_timestamp from empsalary; + + select max(enroll_date) over (order by enroll_timestamp range between 1 preceding and 2 following + exclude ties), salary, enroll_timestamp from empsalary; + + select max(enroll_date) over (order by salary range between -1 preceding and 2 following + exclude ties), salary, enroll_timestamp from empsalary; + + select max(enroll_date) over (order by salary range between 1 preceding and -2 following + exclude ties), salary, enroll_timestamp from empsalary; + + select max(enroll_date) over (order by salary range between '1 year'::interval preceding and '2 years'::interval following + exclude ties), salary, enroll_timestamp from empsalary; + + -- GROUPS tests + + SELECT sum(unique1) over (order by four groups between unbounded preceding and current row), + unique1, four + FROM tenk1 WHERE unique1 < 10; + + SELECT sum(unique1) over (order by four groups between unbounded preceding and unbounded following), + unique1, four + FROM tenk1 WHERE unique1 < 10; + + SELECT sum(unique1) over (order by four groups between current row and unbounded following), + unique1, four + FROM tenk1 WHERE unique1 < 10; + + SELECT sum(unique1) over (order by four groups between 1 preceding and unbounded following), + unique1, four + FROM tenk1 WHERE unique1 < 10; + + SELECT sum(unique1) over (order by four groups between 1 following and unbounded following), + unique1, four + FROM tenk1 WHERE unique1 < 10; + + SELECT sum(unique1) over (order by four groups between unbounded preceding and 2 following), + unique1, four + FROM tenk1 WHERE unique1 < 10; + + SELECT sum(unique1) over (order by four groups between 2 preceding and 1 preceding), + unique1, four + FROM tenk1 WHERE unique1 < 10; + + SELECT sum(unique1) over (order by four groups between 2 preceding and 1 following), + unique1, four + FROM tenk1 WHERE unique1 < 10; + + SELECT sum(unique1) over (order by four groups between 0 preceding and 0 following), + unique1, four + FROM tenk1 WHERE unique1 < 10; + + SELECT sum(unique1) over (order by four groups between 2 preceding and 1 following + exclude current row), unique1, four + FROM tenk1 WHERE unique1 < 10; + + SELECT sum(unique1) over (order by four groups between 2 preceding and 1 following + exclude group), unique1, four + FROM tenk1 WHERE unique1 < 10; + + SELECT sum(unique1) over (order by four groups between 2 preceding and 1 following + exclude ties), unique1, four + FROM tenk1 WHERE unique1 < 10; + + SELECT sum(unique1) over (partition by ten + order by four groups between 0 preceding and 0 following),unique1, four, ten + FROM tenk1 WHERE unique1 < 10; + + SELECT sum(unique1) over (partition by ten + order by four groups between 0 preceding and 0 following exclude current row), unique1, four, ten + FROM tenk1 WHERE unique1 < 10; + + SELECT sum(unique1) over (partition by ten + order by four groups between 0 preceding and 0 following exclude group), unique1, four, ten + FROM tenk1 WHERE unique1 < 10; + + SELECT sum(unique1) over (partition by ten + order by four groups between 0 preceding and 0 following exclude ties), unique1, four, ten + FROM tenk1 WHERE unique1 < 10; + + select first_value(salary) over(order by enroll_date groups between 1 preceding and 1 following), + lead(salary) over(order by enroll_date groups between 1 preceding and 1 following), + nth_value(salary, 1) over(order by enroll_date groups between 1 preceding and 1 following), + salary, enroll_date from empsalary; + + select last_value(salary) over(order by enroll_date groups between 1 preceding and 1 following), + lag(salary) over(order by enroll_date groups between 1 preceding and 1 following), + salary, enroll_date from empsalary; + + select first_value(salary) over(order by enroll_date groups between 1 following and 3 following + exclude current row), + lead(salary) over(order by enroll_date groups between 1 following and 3 following exclude ties), + nth_value(salary, 1) over(order by enroll_date groups between 1 following and 3 following + exclude ties), + salary, enroll_date from empsalary; + + select last_value(salary) over(order by enroll_date groups between 1 following and 3 following + exclude group), + lag(salary) over(order by enroll_date groups between 1 following and 3 following exclude group), + salary, enroll_date from empsalary; + + -- Show differences in values mode between ROWS, RANGE, and GROUPS + WITH cte (x) AS ( + SELECT * FROM generate_series(1, 35, 2) + ) + SELECT x, (sum(x) over w) + FROM cte + WINDOW w AS (ORDER BY x rows between 1 preceding and 1 following); + + WITH cte (x) AS ( + SELECT * FROM generate_series(1, 35, 2) + ) + SELECT x, (sum(x) over w) + FROM cte + WINDOW w AS (ORDER BY x range between 1 preceding and 1 following); + + WITH cte (x) AS ( + SELECT * FROM generate_series(1, 35, 2) + ) + SELECT x, (sum(x) over w) + FROM cte + WINDOW w AS (ORDER BY x groups between 1 preceding and 1 following); + + WITH cte (x) AS ( + select 1 union all select 1 union all select 1 union all + SELECT * FROM generate_series(5, 49, 2) + ) + SELECT x, (sum(x) over w) + FROM cte + WINDOW w AS (ORDER BY x rows between 1 preceding and 1 following); + + WITH cte (x) AS ( + select 1 union all select 1 union all select 1 union all + SELECT * FROM generate_series(5, 49, 2) + ) + SELECT x, (sum(x) over w) + FROM cte + WINDOW w AS (ORDER BY x range between 1 preceding and 1 following); + + WITH cte (x) AS ( + select 1 union all select 1 union all select 1 union all + SELECT * FROM generate_series(5, 49, 2) + ) + SELECT x, (sum(x) over w) + FROM cte + WINDOW w AS (ORDER BY x groups between 1 preceding and 1 following); + -- with UNION SELECT count(*) OVER (PARTITION BY four) FROM (SELECT * FROM tenk1 UNION ALL SELECT * FROM tenk2)s LIMIT 0;