Re: Add RESPECT/IGNORE NULLS and FROM FIRST/LAST options

[Oliver Ford]

On Sat, 6 May 2023, 04:57 Tatsuo Ishii,  wrote:

> Attached is the patch to implement this (on top of your patch).
>
> test=# SELECT row_number() RESPECT NULLS OVER () FROM (SELECT 1) AS s;
> ERROR:  window function row_number cannot have RESPECT NULLS or IGNORE
> NULLS
>

The last time this was discussed (
https://www.postgresql.org/message-id/1037735.1610402426%40sss.pgh.pa.us)
it was suggested to make the feature generalizable, beyond what the
standard says it should be limited to.

With it generalizable, there would need to be extra checks for custom
functions, such as if they allow multiple column arguments (which I'll add
in v2 of the patch if the design's accepted).

So I think we need a consensus on whether to stick to limiting it to
several specific functions, or making it generalized yet agreeing the rules
to limit it (such as no agg functions, and no functions with multiple
column arguments).

Re: Add RESPECT/IGNORE NULLS and FROM FIRST/LAST options

[Oliver Ford]

On Sun, Apr 23, 2023 at 4:29 AM Tatsuo Ishii  wrote:

> > Vik Fearing  writes:
> >
> >> For me, this is perfectly okay.  Keep them at the lowest level of
> >> reservation as possible.
> >
> > Yeah, keep them unreserved if at all possible.  Any higher reservation
> > level risks breaking existing applications that might be using these
> > words as column or function names.
>
> Agreed.
> <http://www.sraoss.co.jp>

  <http://www.sraoss.co.jp>
Attached is a new version of the code and tests to implement this. There's
now no modification to windowfuncs.c or the catalog,
it's only a bool added to FuncCall which if set to true, ignores nulls. It
adds IGNORE/RESPECT at the Unreserved, As Label level.

The implementation also aims at better performance over previous versions
by not disabling set_mark, and using an array to
track previous non-null positions in SEEK_HEAD or SEEK_CURRENT with Forward
(lead, but not lag). The mark is set if a row
is out of frame and further rows can't be in frame (to ensure it works with
an exclusion clause).

The attached test patch is mostly the same as in the previous patch
set, but it doesn't fail on row_number anymore as the main patch
only rejects aggregate functions. The test patch also adds a test for
EXCLUDE CURRENT ROW and for two contiguous null rows.

I've not yet tested custom window functions with the patch, but I'm happy
to add them to the test patch in v2 if we want to go this way
in implementing this feature.
From 81c48df9a08deb065379e8bccffb2f5592faa4d0 Mon Sep 17 00:00:00 2001
From: Oliver Ford 
Date: Wed, 19 Apr 2023 01:07:14 +0100
Subject: [PATCH] initial window ignore

---
 src/backend/executor/nodeWindowAgg.c | 263 ++-
 src/backend/optimizer/util/clauses.c |   1 +
 src/backend/parser/gram.y|  20 +-
 src/backend/parser/parse_func.c  |   9 +
 src/backend/utils/adt/ruleutils.c|   7 +-
 src/include/nodes/parsenodes.h   |   1 +
 src/include/nodes/primnodes.h|   2 +
 src/include/parser/kwlist.h  |   2 +
 8 files changed, 297 insertions(+), 8 deletions(-)

diff --git a/src/backend/executor/nodeWindowAgg.c b/src/backend/executor/nodeWindowAgg.c
index 4f0618f27a..fac0e05dee 100644
--- a/src/backend/executor/nodeWindowAgg.c
+++ b/src/backend/executor/nodeWindowAgg.c
@@ -69,6 +69,11 @@ typedef struct WindowObjectData
 	int			readptr;		/* tuplestore read pointer for this fn */
 	int64		markpos;		/* row that markptr is positioned on */
 	int64		seekpos;		/* row that readptr is positioned on */
+
+	bool		ignore_nulls;	/* ignore nulls */
+	int64		*win_nonnulls;	/* tracks non-nulls in ignore nulls mode */
+	int			nonnulls_size;	/* track size of the win_nonnulls array */
+	int			nonnulls_len;	/* track length of the win_nonnulls array */
 } WindowObjectData;
 
 /*
@@ -97,6 +102,7 @@ typedef struct WindowStatePerFuncData
 	bool		plain_agg;		/* is it just a plain aggregate function? */
 	int			aggno;			/* if so, index of its WindowStatePerAggData */
 
+	bool		ignore_nulls;	/* ignore nulls */
 	WindowObject winobj;		/* object used in window function API */
 }			WindowStatePerFuncData;
 
@@ -2560,14 +2566,14 @@ ExecInitWindowAgg(WindowAgg *node, EState *estate, int eflags)
 			elog(ERROR, "WindowFunc with winref %u assigned to WindowAgg with winref %u",
  wfunc->winref, node->winref);
 
-		/* Look for a previous duplicate window function */
+		/* Look for a previous duplicate window function, which needs the same ignore_nulls value */
 		for (i = 0; i <= wfuncno; i++)
 		{
 			if (equal(wfunc, perfunc[i].wfunc) &&
 !contain_volatile_functions((Node *) wfunc))
 break;
 		}
-		if (i <= wfuncno)
+		if (i <= wfuncno && wfunc->ignore_nulls == perfunc[i].ignore_nulls)
 		{
 			/* Found a match to an existing entry, so just mark it */
 			wfuncstate->wfuncno = i;
@@ -2620,6 +2626,13 @@ ExecInitWindowAgg(WindowAgg *node, EState *estate, int eflags)
 			winobj->argstates = wfuncstate->args;
 			winobj->localmem = NULL;
 			perfuncstate->winobj = winobj;
+			winobj->ignore_nulls = wfunc->ignore_nulls;
+			if (winobj->ignore_nulls)
+			{
+winobj->win_nonnulls = palloc_array(int64, 16);
+winobj->nonnulls_size = 16;
+winobj->nonnulls_len = 0;
+			}
 
 			/* It's a real window function, so set up to call it. */
 			fmgr_info_cxt(wfunc->winfnoid, >flinfo,
@@ -3306,6 +3319,244 @@ WinRowsArePeers(WindowObject winobj, int64 pos1, int64 pos2)
 	return res;
 }
 
+static void increment_notnulls(WindowObject winobj, int64 pos)
+{
+	if (winobj->nonnulls_len == winobj->nonnulls_size)
+	{
+		winobj->nonnulls_size *= 2;
+		winobj->win_nonnulls =
+			repalloc_array(winobj->win_nonnulls,
+			int64,
+			winobj->nonnulls_size);
+	}
+	winobj->win_nonnulls[winobj->nonnulls_len] = pos;
+	winobj->nonnulls_len++;
+}
+
+static Datum ignorenul

Re: Add RESPECT/IGNORE NULLS and FROM FIRST/LAST options

[Oliver Ford]

On Sat, 22 Apr 2023, 13:14 Tatsuo Ishii,  wrote:

> I revisited the thread:
>
> https://www.postgresql.org/message-id/flat/CAGMVOdsbtRwE_4%2Bv8zjH1d9xfovDeQAGLkP_B6k69_VoFEgX-A%40mail.gmail.com
>
> and came up with attached POC patch (I used some varibale names
> appearing in the Krasiyan Andreev's patch). I really love to have
> RESPECT/IGNORE NULLS because I believe they are convenient for
> users. For FIRST/LAST I am not so excited since there are alternatives
> as our document stats, so FIRST/LAST are not included in the patch.
>
> Currently in the patch only nth_value is allowed to use RESPECT/IGNORE
> NULLS. I think it's not hard to implement it for others (lead, lag,
> first_value and last_value).  No document nor test patches are
> included for now.
>

I've actually recently been looking at this feature again recently as well.
One thing I wondered, but would need consensus, is to take the
SEEK_HEAD/SEEK_TAIL case statements out of WinGetFuncArgInPartition. This
function is only called by leadlag_common, which uses SEEK_CURRENT, so
those case statements are never reached. Taking them out simplifies the
code as it is but means future features might need it re-added (although
I'm not sure the use case for it, as that function is for window funcs that
ignore the frame options).


> Note that RESPECT/IGNORE are not registered as reserved keywords in
> this patch (but registered as unreserved keywords). I am not sure if
> this is acceptable or not.
>
> > The questions of how we interface to the individual window functions
> > are really independent of how we handle the parsing problem.  My
> > first inclination is to just pass the flags down to the window functions
> > (store them in WindowObject and provide some additional inquiry functions
> > in windowapi.h) and let them deal with it.

I agree with this.  Also I do not change the prototype of
> nth_value. So I pass RESPECT/IGNORE NULLS information from the raw
> parser to parse/analysis and finally to WindowObject.
>

This is a much better option than my older patch which needed to change the
functions.


> > It's also worth wondering if we couldn't just implement the flags in
> > some generic fashion and not need to involve the window functions at
> > all.  FROM LAST, for example, could and perhaps should be implemented
> > by inverting the sort order.  Possibly IGNORE NULLS could be implemented
> > inside the WinGetFuncArgXXX functions?  These behaviors might or might
> > not make much sense with other window functions, but that doesn't seem
> > like it's our problem.
>
> Yes, probably we could make WinGetFuncArgXXX a little bit smarter in
> this direction (not implemented in the patch at this point).
>

+1 for doing it here. Maybe also refactor WinGetFuncArgInFrame, putting the
exclusion checks in a static function as that function is already pretty
big?


> Best reagards,
> --
> Tatsuo Ishii
> SRA OSS LLC
> English: http://www.sraoss.co.jp/index_en/
> Japanese:http://www.sraoss.co.jp
>

Add RESPECT/IGNORE NULLS and FROM FIRST/LAST options

[Oliver Ford]

Adds the options RESPECT/IGNORE NULLS (null treatment clause) and FROM
FIRST/LAST to the non-aggregate window functions.

A previous patch
(https://www.postgresql.org/message-id/CA+=vxna5_n1q5q5okxc0aqnndbo2ru6gvw+86wk+onsunjd...@mail.gmail.com)
partially implemented this feature. However, that patch worked by
adding the null treatment clause to the window frame's frameOptions
variable, and consequently had the limitation that it wasn't possible
to reuse a window frame definition in a single query where two
functions were called that had different null treatment options. This
meant that the patch was never committed. The attached path takes a
different approach which gets around this limitation.

For example, the following query would not work correctly with the
implementation in the old patch but does with the attached patch:

WITH cte (x) AS (
select null union select 1 union select 2)
SELECT x,
first_value(x) over w as with_default,
first_value(x) respect nulls over w as with_respect,
first_value(x) ignore nulls over w as with_ignore
from cte WINDOW w as (order by x nulls first rows between unbounded
preceding and unbounded following);
 x | with_default | with_respect | with_ignore
---+--+--+-
   |  |  |   1
 1 |  |  |   1
 2 |  |  |   1
(3 rows)


== Implementation ==

The patch adds two types to the pg_type catalog: "ignorenulls" and
"fromlast". These types are of the Boolean category, and work as
wrappers around the bool type. They are used as function arguments to
extra versions of the window functions that take additional boolean
arguments. RESPECT NULLS and FROM FIRST are ignored by the parser, but
IGNORE NULLS and FROM LAST lead to the extra versions being called
with arguments to ignore nulls and order from last.

== Testing ==

Updated documentation and added regression tests. All existing tests
pass. This change will need a catversion bump.
Thanks to Krasiyan Andreev for initially testing this patch.


0001-respect.patch
Description: Binary data

Re: Add RANGE with values and exclusions clauses to the Window Functions

[Oliver Ford]

On Tuesday, 30 January 2018, Tom Lane  wrote:

> Another thing I'm a little confused by is the precise API for the in_range
> support functions (the lack of any documentation for it doesn't help).
> I wonder why you chose to provide two support functions per datatype
> combination rather than one with an additional boolean argument.
> In fact, it almost seems like the "end" flag could already do the
> job, though I may be missing something.  As-is, it seems like this
> setup involves a lot of duplicate code and catalog entries ... what
> are we gaining from that?
>
> regards, tom lane
>

We could instead remove the "desc" functions and flip the values of both
"preceding" and "end" for a descending order. It just needs an extra bool
in the parsenode/plannode structs to send to nodeWindowAgg.

I used two functions because it seemed cleaner to me to get the Oid of the
function in the parser for both ordering types, so then nodeWindowAgg
doesn't have to know about sort order (doesn't have to have extra
conditionals for the two). But yes it does increase the catalog and code
size so is probably better removed.

I will send out v10 soon with the desc functions removed and the
EXCLUDE_NO_OTHERS define removed.

Re: Add RANGE with values and exclusions clauses to the Window Functions

[Oliver Ford]

On Monday, 29 January 2018, Tom Lane <t...@sss.pgh.pa.us> wrote:

> Oliver Ford <ojf...@gmail.com> writes:
> > On Monday, 29 January 2018, Tom Lane <t...@sss.pgh.pa.us> wrote:
> >> I've started to go through this in some detail, and I'm wondering why
> >> you invented a FRAMEOPTION_EXCLUDE_NO_OTHERS option bit rather than
> >> just representing that choice as default (0).
>
> > My guess is that it's a little like putting "ORDER BY x ASC" when ASC is
> > usually default behavior - it adds some documentation, perhaps for people
> > new to SQL or to make your intention more explicit. That's the only
> reason
> > I can think of as to why the standards committee included it.
>
> Yeah, they like to do that.  And "ORDER BY x ASC" is actually a precise
> precedent, because we don't print ASC either, cf get_rule_orderby().
>
> regards, tom lane
>

I would strongly suggest taking it out entirely then. There really doesn't
seem a point in adding a new keyword and a new condition in the grammar if
it is going to do absolutely nothing.

If anyone thinks it's useful to have I can just take it out of ruleutils
and remove its define. But personally I would remove it entirely as it's
really just clutter.

Re: Add RANGE with values and exclusions clauses to the Window Functions

[Oliver Ford]

On Monday, 29 January 2018, Tom Lane <t...@sss.pgh.pa.us> wrote:

> Oliver Ford <ojf...@gmail.com> writes:
> > [ 0001-window-frame-v9.patch ]
>
> I've started to go through this in some detail, and I'm wondering why
> you invented a FRAMEOPTION_EXCLUDE_NO_OTHERS option bit rather than
> just representing that choice as default (0).  As you have it, a
> window definition that explicitly specifies EXCLUDE NO OTHERS will be
> considered unequal to one that just defaults to that behavior, in
> e.g. transformWindowFuncCall().  That seems undesirable, if not
> outright wrong.  Against that, having the bit allows ruleutils.c
> to print "EXCLUDE NO OTHERS" when the input included that, but that
> seems like a wash if not an anti-feature.  We've never been big on
> making ruleutils output distinguish explicit from implicit selection
> of a default setting, and in this case it could possibly lead to
> outputting a query in a form that is not backwards-compatible to
> older PG versions, when there's no need to be incompatible.


Exclude No Others does seem pretty pointless to me, but it's in the
standard so I included it as an option that can be printed by ruleutils. I
can't imagine it being much used, but if people do want to document that
they are not excluding other rows they can do so.

My guess is that it's a little like putting "ORDER BY x ASC" when ASC is
usually default behavior - it adds some documentation, perhaps for people
new to SQL or to make your intention more explicit. That's the only reason
I can think of as to why the standards committee included it.


> If there's some other consideration I'm missing, please say what;
> otherwise I'll change it.
>
> BTW, I generally recommend not including a catversion change in
> submitted patches.  It causes merge problems any time some other
> catversion-bumping patch gets committed, and it can't possibly be
> the right value for the final commit since you aren't likely to
> be able to predict that date in advance.  It surely doesn't hurt
> to remind the committer that a catversion bump is needed, but just
> do that in the submission message.
>

Ok won't do that again.

Re: Add RANGE with values and exclusions clauses to the Window Functions

[Oliver Ford]

On Tuesday, 9 January 2018, Tom Lane  wrote:
>
> So the approach I'm imagining now is a datatype-specific support function
> along the lines of
>
> in_range(a, b, delta) returns bool
>
> which is supposed to return true if a <= b + delta, or something along
> that line --- exact details of the definition TBD --- with the proviso
> that if b + delta would overflow then the result is automatically true.
>
> We could probably also delegate the requirement of throwing an error
> for negative delta to this function, eliminating the need for the
> datatype-independent core code to know how to tell that, which is the
> other datatype-dependent behavior needed per spec.
>
> Likely there are two of these, one each for the PRECEDING and FOLLOWING
> cases.
>
>
>
Would you prefer two functions, or a single function with a parameter for
PRECEDING/FOLLOWING? Maybe:

  in_range(a, b, delta, following) returns bool

Where following is a bool which is true if FOLLOWING was specified and
false if PRECEDING was specified?

Re: proposal: alternative psql commands quit and exit

[Oliver Ford]

On Fri, Dec 8, 2017 at 3:10 PM, David G. Johnston
<david.g.johns...@gmail.com> wrote:
> On Fri, Dec 8, 2017 at 7:34 AM, Oliver Ford <ojf...@gmail.com> wrote:
>>
>> On Thu, Dec 7, 2017 at 11:47 PM, Everaldo Canuto
>> <everaldo.can...@gmail.com> wrote:
>>
>> +1 from me. When I first used Postgres I struggled with how to quit
>> psql. I felt that making people look up how to quit the program is bad
>> UI design. I admired Postgres as a database, but had the impression
>> that it was harder to use than MySQL.
>
>
>>
>> Not being able to quit or
>> describe a table in the way I was used to was frustrating.
>
>
> Whomever comes second is almost always going to have that problem.

And for most people, Postgres is their second or third database.

>> who want a db that is intuitive.
>
>
> Intuitive and "works like xyz" are not the same thing ...


Most people will have worked with Bash, Powershell, MySQL, etc and for
them intuitive means typing exit or quit. It would be strange to them
that psql behaves differently than what they're used to. My guess
would be that this quitting difficulty put more people off Postgres
than anything to do with the backend. First impressions count when
retaining users.

Re: proposal: alternative psql commands quit and exit

[Oliver Ford]

On Thu, Dec 7, 2017 at 11:47 PM, Everaldo Canuto
 wrote:
> Some of us unfortunately have to work with multiple databases like Oracle or
> MySQL. Their respective clients mysql and sqlplus uses "quit" or "exit" to
> exit sql client.
>
> Oracle's sqlplus uses "quit" or "exit" and MySQL client can be exited using
> "quit" and "exit" but for compatibility with psql, it also supports "\q" and
> "\quit".
>
> Postgres psql already support "\q" and "\quit" but I think that could be
> cool if it supports "exit" and "quit", talking to friends I saw that I am
> the only that sometimes try to exit psql with "exit'.

+1 from me. When I first used Postgres I struggled with how to quit
psql. I felt that making people look up how to quit the program is bad
UI design. I admired Postgres as a database, but had the impression
that it was harder to use than MySQL. Not being able to quit or
describe a table in the way I was used to was frustrating.

If anyone is unsure on this point, I'd recommend reading Joel
Spolsky's articles on UI design. He clearly explains how a program
model should match a user model. The fact that it's always been done
this way is irrelevant to new users, who want a db that is intuitive.

Add GROUPS option to the Window Functions

[Oliver Ford]

Adds the GROUPS option to the window framing clause. This further
resolves TODO list item "Implement full support for window framing
clauses" and implements SQL:2011 T620. No other mainstream db has this
feature.

Apply this on top of my previous patch available here:
https://www.postgresql.org/message-id/CAGMVOdvETRCKpeT06Uoq5RsNUOdH7d1iYy7C1Pze%3DL5%3DgBzs-Q%40mail.gmail.com

== Specification ==

The GROUPS option is defined in SQL:2011 in addition to ROWS and
RANGE. Where ROWS calculate frame bounds by the number of rows before
and after the current row, and RANGE by the values of an ORDER BY
column, GROUPS calculates frame bounds by the number of changes to the
values of the ORDER BY columns.

GROUPS behaves similar to RANGE in that if two rows are peers, they
are both included in the frame. A row is out of frame if it is both
not a peer of the current row and also outside of the bounds specified
by start_value and end_value. Note that if neither start_value or
end_value are specified, then GROUPS will always produce the same
results as RANGE. So UNBOUNDED PRECEDING AND CURRENT ROW, or CURRENT
ROW AND UNBOUNDED FOLLOWING produce the same results in GROUPS and
RANGE mode (the syntax is slightly confusing as CURRENT ROW in these
modes includes peers of the actual current row).

The standard also defines an EXCLUDE GROUP option which excludes the
current row and any peers from the frame. This can be used in all
three modes, and is included in the patch.

== Performance Considerations ==

The code calculates the size of each window group for every partition
and stores this in a dynamic array. I chose 16 as the initial capacity
of the array, which doubles as needed. Real-world testing may show
that a lower or higher initial capacity is preferable for the majority
of use cases. The code also calls pfree on this array at the end of
each partition, to avoid memory hogging if there are many partitions.

== Testing ==

Tested on Windows with MinGW. All existing regression tests pass. New
tests and updated documentation is included. Tests show the results of
the GROUPS option and the EXCLUDE GROUP option also working in RANGE
and ROWS mode.


0001-window-groups-v1.patch
Description: Binary data

Re: Add RANGE with values and exclusions clauses to the Window Functions

[Oliver Ford]

On Tue, Nov 28, 2017 at 4:38 AM, David Fetter <da...@fetter.org> wrote:
> On Mon, Nov 27, 2017 at 04:55:17PM +0000, Oliver Ford wrote:
>> On Mon, Nov 27, 2017 at 4:40 PM, Erik Rijkers <e...@xs4all.nl> wrote:
>> > On 2017-11-27 17:34, Erik Rijkers wrote:
>> >>
>> >> On 2017-11-27 16:01, Oliver Ford wrote:
>> >>>
>> >>> Attached is it in bare diff form.
>> >>
>> >>
>> >> [0001-window-frame-v3.patch]
>> >>
>> >> Thanks, that did indeed fix it:
>> >>
>> >> make && make check  now  ok.
>> >>
>> >> There were errors in the doc build (unmatched tags); I fixed them in
>> >> the attached doc-patch (which should go on top of yours).
>> >
>> >
>> > 0001-window-frame-v3-fixtags.diff
>> >
>> > now attached, I hope...
>> >
>>
>> Cheers here's v4 with the correct docs.
>
> I've taken the liberty of adding float8, somewhat mechanically.  Do
> the docs need some change, assuming that addition is useful?
>
> Best,
> David.
> --
> David Fetter <david(at)fetter(dot)org> http://fetter.org/
> Phone: +1 415 235 3778

The SQL:2011 standard says that the range values should only be an
integer or interval. My understanding is therefore that the ORDER BY
columns should only be either an integer, with integer range values -
or a date/time, with interval range values.

I think if we go outside the standard we should leave it for another
patch and further discussion. But maybe others would prefer to add
support for more types even if they are non-standard?

Re: Add RANGE with values and exclusions clauses to the Window Functions

[Oliver Ford]

On Mon, Nov 27, 2017 at 4:40 PM, Erik Rijkers <e...@xs4all.nl> wrote:
> On 2017-11-27 17:34, Erik Rijkers wrote:
>>
>> On 2017-11-27 16:01, Oliver Ford wrote:
>>>
>>> Attached is it in bare diff form.
>>
>>
>> [0001-window-frame-v3.patch]
>>
>> Thanks, that did indeed fix it:
>>
>> make && make check  now  ok.
>>
>> There were errors in the doc build (unmatched tags); I fixed them in
>> the attached doc-patch (which should go on top of yours).
>
>
> 0001-window-frame-v3-fixtags.diff
>
> now attached, I hope...
>

Cheers here's v4 with the correct docs.


0001-window-frame-v4.patch
Description: Binary data

Re: Add RANGE with values and exclusions clauses to the Window Functions

[Oliver Ford]

On Mon, Nov 27, 2017 at 12:06 PM, Oliver Ford <ojf...@gmail.com> wrote:
> On Fri, Nov 24, 2017 at 3:08 PM, Erikjan Rijkers <e...@xs4all.nl> wrote:
>>   SELECT pg_get_viewdef('v_window');
>> ! pg_get_viewdef
>> ! --
>> !   SELECT i.i,+
>> !  sum(i.i) OVER (ORDER BY i.i) AS sum_rows+
>>   FROM generate_series(1, 10) i(i);
>>   (1 row)
>>
>> --- 1034,1043 
>>   (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)
>>
>>
>> This small hickup didn't prevent building an instance but obviously I
>> haven't done any real tests yet.
>>
>>
>> thanks,
>>
>>
>> Erik Rijkers
>
> After another clone and rebuild it works alright with the correct
> spacing on mine, so the attached v2 should all pass. I noticed that I
> hadn't added the exclusions clauses to the view defs code, so that's
> also in this patch with extra tests to cover it.

Sorry previous patch was in full-commit form and not just a diff.
Attached is it in bare diff form.


0001-window-frame-v3.patch
Description: Binary data

Add RANGE with values and exclusions clauses to the Window Functions

[Oliver Ford]

Adds RANGE BETWEEN with a start and end value, as well as an
exclusions clause, to the window functions. This partially resolves
TODO list item "Implement full support for window framing clauses".

== Specification ==

The window functions already allow a "ROWS BETWEEN start_value
PRECEDING/FOLLOWING AND end_value PRECEDING/FOLLOWING" to restrict the
number of rows within a partition that are piped into an aggregate
function based on their position before or after the current row. This
patch adds an equivalent for RANGE which restricts the rows based on
whether the _values_ of the ORDER BY column for all other rows in the
partition are within the start_value and end_value bounds. This brings
PostgreSQL to parity with Oracle, and implements a SQL:2011 standard
feature.

SQL:2011 also defines a window frame exclusion clause, which excludes
certain rows from the result. This clause doesn't seem to be
implemented in any mainstream RDBMS (MariaDb mentions that fact in its
documentation here:
https://mariadb.com/kb/en/library/window-functions-overview/ and has
it on its TODO list). This patch implements three EXCLUDE clauses
described in the standard:

EXCLUDE CURRENT ROW - excludes the current row from the result
EXCLUDE TIES - excludes identical rows from the result
EXCLUDE NO OTHERS - does nothing, is the default behavior; exists
purely to describe the intention not to exclude any other rows

The RANGE BETWEEN clause requires a single ORDER BY column which must
be either an integer or a date/time type. If the column is a date/time
type then start_value and end_value must both be an interval type. If
the column is an integer, then the values must both be integers.

== Testing ==

Tested on Windows with MinGW. All existing regression tests pass. New
tests and updated documentation is included. Tests show both the new
RANGE with values working and the exclusion clause working in both
RANGE and ROWS mode.

== Future Work ==

The standard also defines, in addition to RANGE and ROWS, a GROUPS
option with a corresponding EXCLUDE GROUP option. This also doesn't
seem to be implemented anywhere else, and I plan to implement it next.

This patch also adds some new error messages which have not been
internationalized.


0001-window-frame-v1.patch
Description: Binary data