Re: BUG #15143: Window Functions – Paranthese not allowed before OVER term

The following bug has been logged on the website:

Bug reference: 15143
Logged by: David
Email address: david(at)daily-harvest(dot)com
PostgreSQL version: 9.6.6
Operating system: Mac / DBeaver
Description:

```SELECT *,
(P2.received_at - LAG(P2.received_at)) OVER (PARTITION BY anonymous_id ORDER
BY P2.received_at DESC) AS time_diff
--((EXTRACT(EPOCH FROM (P2.received_at - LEAD(P2.received_at)))/60)) OVER
(PARTITION BY P2.anonymous_id ORDER BY P2.received_at DESC) AS
time_diff_minutes
FROM javascript.pages P2```

For the second line, I have to remove the parentheses around (P2.received_at
- LAG(P2.received_at)) for it to run. Which doesn't make sense. And more
importantly, I can't seem to get the 3rd line (currently commented out) to
run because of this issue of Postgres seeming to not allow parentheses
before the OVER

On Wed, Apr 4, 2018 at 4:41 PM, PG Bug reporting form <
noreply(at)postgresql(dot)org> wrote:

> The following bug has been logged on the website:
>
> Bug reference: 15143
> Logged by: David
> Email address: david(at)daily-harvest(dot)com
> PostgreSQL version: 9.6.6
> Operating system: Mac / DBeaver
> Description:
>
> ```

SELECT *,
> (P2.received_at - LAG(P2.received_at)) OVER (PARTITION BY anonymous_id
> ORDER
> BY P2.received_at DESC) AS time_diff
> --((EXTRACT(EPOCH FROM (P2.received_at - LEAD(P2.received_at)))/60)) OVER
> (PARTITION BY P2.anonymous_id ORDER BY P2.received_at DESC) AS
> time_diff_minutes
> FROM javascript.pages P2```
>
> For the second line, I have to remove the parentheses around
> (P2.received_at
> - LAG(P2.received_at)) for it to run. Which doesn't make sense. And more
> importantly, I can't seem to get the 3rd line (currently commented out) to
> run because of this issue of Postgres seeming to not allow parentheses
> before the OVER
>

​Working as documented:

"​A window function call always contains an OVER clause directly following
the window function's name and argument(s)"

/docs/10/static/tutorial-window.html

When trying to create expressions using the result of a window function it
is sometime necessary to move window function computation into a subquery
and perform the calculations in the outer layer.

The formal syntax is defined here:

/docs/10/static/sql-expressions.html#SYNTAX-WINDOW-FUNCTIONS

Note, the tutorial probably could use updating since a FILTER clause can be
inserted in between the function invocation and the OVER...but only a
FILTER clause.

David J.

On 5 April 2018 at 12:23, David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com> wrote:
> On Wed, Apr 4, 2018 at 4:41 PM, PG Bug reporting form
>> SELECT *,
>> (P2.received_at - LAG(P2.received_at)) OVER (PARTITION BY anonymous_id
>> ORDER
>> BY P2.received_at DESC) AS time_diff
>> --((EXTRACT(EPOCH FROM (P2.received_at - LEAD(P2.received_at)))/60)) OVER
>> (PARTITION BY P2.anonymous_id ORDER BY P2.received_at DESC) AS
>> time_diff_minutes
>> FROM javascript.pages P2```
>>
>> For the second line, I have to remove the parentheses around
>> (P2.received_at
>> - LAG(P2.received_at)) for it to run. Which doesn't make sense. And more
>> importantly, I can't seem to get the 3rd line (currently commented out) to
>> run because of this issue of Postgres seeming to not allow parentheses
>> before the OVER
>
>
> Working as documented:
>
> "A window function call always contains an OVER clause directly following
> the window function's name and argument(s)"

Yeah, how else would the window function know which window clause it belongs to?

If you'd done: SELECT (LEAD(col) - LAG(col)) OVER (...) could
PostgreSQL just assume that you meant to link both the lead and lag to
the same over clause?

If you want to shrink the syntax down a bit, then you can define your
WINDOW clauses at the end of the query:

select lead(...) over w,lag(...) over w from table window w as
(partition by ... order by ...);

This might make it easier to read if you're embedding the window
functions in other expressions.

--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

On Wed, Apr 4, 2018 at 7:32 PM, David Rowley <david(dot)rowley(at)2ndquadrant(dot)com>
wrote:

> > Working as documented:
> >
> > "A window function call always contains an OVER clause directly following
> > the window function's name and argument(s)"
>
> Yeah, how else would the window function know which window clause it
> belongs to?
>
> If you'd done: SELECT (LEAD(col) - LAG(col)) OVER (...) could
> PostgreSQL just assume that you meant to link both the lead and lag to
> the same over clause?
>

​Well, if there is only a single aggregate function in the expression there
isn't any ambiguity. If there happened to be more than one the system
could emit a parsing error saying as much.​ While likely more
user-friendly I don't imagine its worth the headache in the parser.

I did kinda mis-speak earlier though - there probably aren't any
expressions that require a window function to end up in a subquery, but
usually if I get to the point of using complex expressions readability will
lead me to do so.

David J.

On 5 April 2018 at 14:40, David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com> wrote:
> On Wed, Apr 4, 2018 at 7:32 PM, David Rowley <david(dot)rowley(at)2ndquadrant(dot)com>
> wrote:
>>
>> > Working as documented:
>> >
>> > "A window function call always contains an OVER clause directly
>> > following
>> > the window function's name and argument(s)"
>>
>> Yeah, how else would the window function know which window clause it
>> belongs to?
>>
>> If you'd done: SELECT (LEAD(col) - LAG(col)) OVER (...) could
>> PostgreSQL just assume that you meant to link both the lead and lag to
>> the same over clause?
>
>
> Well, if there is only a single aggregate function in the expression there
> isn't any ambiguity. If there happened to be more than one the system could
> emit a parsing error saying as much. While likely more user-friendly I
> don't imagine its worth the headache in the parser.

Perhaps, but I guess it would be pretty hard to know what's an
aggregate and what's a window function when there are multiple.

Consider:

SELECT (SUM(x) - SUM(y)) OVER w1 FROM t WINDOW w1 AS (...);

Is SUM(x) an aggregate or a window function? how about SUM(y)? one of
them must be since there's an OVER clause.

OVER is also quite like FILTER, so someone may expect us to also support:

SELECT (SUM(x) - SUM(y)) FILTER(WHERE x > 0) FROM t;

So I think we're pretty good to leave this untouched.

--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

Thank you David and David!!

I realized it's simpler to create separate variables (ex, Sum(x) and
SUM(Y)) and put them in an inner table. It's one of those things you look
at with new fresh eyes the next days and it just makes sense.

Appreciate the help!

On Wed, Apr 4, 2018 at 11:02 PM, David Rowley <david(dot)rowley(at)2ndquadrant(dot)com>
wrote:

> On 5 April 2018 at 14:40, David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com>
> wrote:
> > On Wed, Apr 4, 2018 at 7:32 PM, David Rowley <
> david(dot)rowley(at)2ndquadrant(dot)com>
> > wrote:
> >>
> >> > Working as documented:
> >> >
> >> > "A window function call always contains an OVER clause directly
> >> > following
> >> > the window function's name and argument(s)"
> >>
> >> Yeah, how else would the window function know which window clause it
> >> belongs to?
> >>
> >> If you'd done: SELECT (LEAD(col) - LAG(col)) OVER (...) could
> >> PostgreSQL just assume that you meant to link both the lead and lag to
> >> the same over clause?
> >
> >
> > Well, if there is only a single aggregate function in the expression
> there
> > isn't any ambiguity. If there happened to be more than one the system
> could
> > emit a parsing error saying as much. While likely more user-friendly I
> > don't imagine its worth the headache in the parser.
>
> Perhaps, but I guess it would be pretty hard to know what's an
> aggregate and what's a window function when there are multiple.
>
> Consider:
>
> SELECT (SUM(x) - SUM(y)) OVER w1 FROM t WINDOW w1 AS (...);
>
> Is SUM(x) an aggregate or a window function? how about SUM(y)? one of
> them must be since there's an OVER clause.
>
> OVER is also quite like FILTER, so someone may expect us to also support:
>
> SELECT (SUM(x) - SUM(y)) FILTER(WHERE x > 0) FROM t;
>
> So I think we're pretty good to leave this untouched.
>
> --
> David Rowley http://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Training & Services
>