Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Implement <null treatment> for LEAD, LAG, FIRST_VALUE, LAST_VALUE and NTH_VALUE [CORE6462] #6695

Open
firebird-automations opened this issue Jan 6, 2021 · 0 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: @mrotteveel

Votes: 1

SQL:2016 defines a <null treatment> clause for LEAD, LAG, FIRST_VALUE, LAST_VALUE and NTH_VALUE, which specifies if nulls are ignored (skipped/eliminated) or not. Currently Firebird only implements the implicit default behaviour (RESPECT NULLS).

Relevant syntax:

"""
<lead or lag function> ::=
<lead or lag> <left paren> <lead or lag extent>
[ <comma> <offset> [ <comma> <default expression> ] ] <right paren>
[ <null treatment> ]

<lead or lag> ::=
LEAD | LAG

<null treatment> ::=
RESPECT NULLS | IGNORE NULLS

<first or last value function> ::=
<first or last value> <left paren> <value expression> <right paren> [ <null treatment> ]

<first or last value> ::=
FIRST_VALUE | LAST_VALUE

<nth value function> ::=
NTH_VALUE <left paren> <value expression> <comma> <nth row> <right paren>
[ <from first or last> ] [ <null treatment> ]
"""

Relevant syntax rules:

"""
6) ...
j) If <lead or lag function> is specified, then:
i) ...
ii) ...
iii) ...
iv) If <null treatment> is specified, then let NTREAT be the <null treatment>; otherwise, let NTREAT be RESPECT NULLS.
v) ...

7) If <first or last value function> or <nth value function> is specified, then:
a) ...
b) If <null treatment> is not specified, then RESPECT NULLS is implicit.
c) ...
"""

Relevant general rules:

"""
b) If <window function type> is <lead or lag function>, then:
i) Let OFFSET be the value of OFF and let DEFAULT be the value of VE2.
ii) Let T be the collection of rows in the window frame of the current row defined by WDX, as specified by the General Rules of Subclause 7.15, "<window clause>".
iii) If LEAD is specified, then:
1) Case:
A) If NTREAT is RESPECT NULLS, then let TX be the sequence of values that is the result of applying VE1 to each row of T that follows the current row, ordered according to the window ordering of WDX.
B) Otherwise, let TX be the sequence of values that is the result of applying VE1 to each row of T that follows the current row and eliminating null values, ordered according to the window ordering of WDX.
2) Let n be the number of values in TX.
3) Case:
A) If OFFSET > n, then the value of <window function> is DEFAULT.
B) If OFFSET = 0 (zero), then the value of <window function> is the value of VE1 evaluated for the current row.
C) Otherwise, the value of <window function> is the m-th value of TX, where m = OFFSET.
iv) If LAG is specified, then:
1) Case:
A) If NTREAT is RESPECT NULLS, then let TX be the sequence of values that is the result of applying VE1 to each row of T that precedes the current row, ordered according to the row ordering of WDX.
B) Otherwise, let TX be the sequence of values that is the result of applying VE1 to each row of T that precedes the current row and eliminating null values, ordered according to the row ordering of WDX.
2) Let n be the number of values in TX.
3) Case:
A) If OFFSET > n, then the value of <window function> is DEFAULT.
B) If OFFSET = 0 (zero), then the value of <window function> is the value of VE1 evaluated for the current row.
C) Otherwise, the value of <window function> is the m-th value of TX, where m = (n - OFFSET + 1).

c) If <window function type> is <first or last value function>, then:
i) Let T be the collection of rows in the window frame of the current row defined by WDX, as
specified by the General Rules of Subclause 7.15, "<window clause>".
ii) Case:
1) If RESPECT NULLS is specified or implicit, then let TX be the sequence of values that is the result of applying the <value expression> to each row of T, ordered according to the row ordering of WDX.
2) Otherwise, let TX be the sequence of values that is the result of applying the <value expression> to each row of T and eliminating null values, ordered according to the row ordering of WDX.
iii) Case:
1) If TX is empty, then the value of <window function> is the null value.
2) If FIRST_VALUE is specified, then the value of <window function> is the first value of TX.
3) Otherwise, the value of <window function> is the last value of TX.

d) If <window function type> is <nth value function>, then:
i) Let RN be the value of <nth row>.
ii) Case:
1) If RN is the null value, then the result is the null value.
2) If RN is less than or equal to 0 (zero), then an exception condition is raised: data exception — invalid argument for NTH_VALUE function.
3) Otherwise:
A) Let T be the collection of rows in the window frame of the current row defined by WDX, as specified by the General Rules of Subclause 7.15, "<window clause>".
B) Case:
I) If RESPECT NULLS is specified or implicit, then let TX be the sequence of values that is the result of applying the <value expression> to each row of T, ordered according to the row ordering of WDX.
II) Otherwise, let TX be the sequence of values that is the result of applying the <value expression> to each row of T and eliminating null values, ordered according to the row ordering of WDX.
C) Let TXN be the number of values in TX.
I) If TXN = 0 (zero) or if TXN < RN, then the value of <window function> is the null value.
II) Case:
1) If FROM LAST is specified, then the value of <window function> is the m-th value of TX, where m = (TXN - RN + 1).
2) Otherwise, the value of <window function> is the m-th value of TX, where m = RN.
"""

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

1 participant