You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
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.
"""
The text was updated successfully, but these errors were encountered:
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.
"""
The text was updated successfully, but these errors were encountered: