Implement <null treatment> for LEAD, LAG, FIRST_VALUE, LAST_VALUE and NTH_VALUE
-------------------------------------------------------------------------------
Key: CORE-6462
URL: http://tracker.firebirdsql.org/browse/CORE-6462
Project: Firebird Core
Issue Type: Improvement
Components: Engine
Affects Versions: 3.0.7, 4.0 Beta 2
Reporter: Mark Rotteveel
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.
"""
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
