Window Function: nth_value(value any, nth integer) [from first | from last] [CORE3621]

[firebird-automations]

Submitted by: Sean Leyne (seanleyne)

Jira_subtask_inward CORE1688

Commits: FirebirdSQL/firebird@d88b5cf3352567f4c27b448d7d94cedb7511be50 FirebirdSQL/firebird@51565beebd1c177b0e50f1c83a559bfb88fce92a FirebirdSQL/firebird@cea1efd81b7dc1402cf6ae29002d239293126385 FirebirdSQL/fbt-repository@d94f3c23999702c3b482472024099c6b9d0512b5 FirebirdSQL/fbt-repository@6319e20ffb9e5c4cecb24df7636f360666970b03

[firebird-automations]

Modified by: Sean Leyne (seanleyne)

assignee: Adriano dos Santos Fernandes \[ asfernandes \]

[firebird-automations]

Modified by: @asfernandes

status: Open \[ 1 \] =\> Resolved \[ 5 \] resolution: Fixed \[ 1 \] Fix Version: 3\.0 Alpha 1 \[ 10331 \]

[firebird-automations]

Modified by: @pcisar

status: Resolved \[ 5 \] =\> Closed \[ 6 \]

[firebird-automations]

Modified by: @asfernandes

summary: Window Function: nth\_value\(value any, nth integer\) =\> Window Function: nth\_value\(value any, nth integer\) \[from first \| from last\]

[firebird-automations]

Commented by: @livius2

Hi,

this ticket have resolution "Fixed" that mean that NTH_VALUE work? I see that it not working as expected

CREATE TABLE TEST ( NAME varchar(20), D date, VAL integer, POINTS integer );

CREATE INDEX IDX_TEST1 ON TEST (NAME);

INSERT INTO TEST (NAME, D, VAL, POINTS) VALUES ('KARLOS', '01.11.2014', '700', '8900'); INSERT INTO TEST (NAME, D, VAL, POINTS) VALUES ('KARLOS', '02.11.2014', '800', '9000'); INSERT INTO TEST (NAME, D, VAL, POINTS) VALUES ('KARLOS', '05.11.2014', '600', '8600'); INSERT INTO TEST (NAME, D, VAL, POINTS) VALUES ('KARLOS', '08.11.2014', '900', '9200'); INSERT INTO TEST (NAME, D, VAL, POINTS) VALUES ('MARTINES', '01.11.2014', '1200', '3000'); INSERT INTO TEST (NAME, D, VAL, POINTS) VALUES ('MARTINES', '02.11.2014', '1300', '3100'); INSERT INTO TEST (NAME, D, VAL, POINTS) VALUES ('MARTINES', '05.11.2014', '1400', '3400'); INSERT INTO TEST (NAME, D, VAL, POINTS) VALUES ('MARTINES', '08.11.2014', '1300', '3300');

SELECT http://T.NAME, T.VAL, T.D ,(SELECT FIRST 1 T2.VAL FROM TEST T2 WHERE http://T2.NAME=T.NAME ORDER BY T2.D ASC) AS "FIRST" ,FIRST_VALUE(T.VAL) OVER(PARTITION BY http://T.NAME ORDER BY T.D ASC) AS "WFIRST" ,NTH_VALUE(T.VAL, 1) OVER(PARTITION BY http://T.NAME ORDER BY T.D ASC) AS "WNFIRST"

,\(SELECT FIRST 1 SKIP 1 T2\.VAL FROM TEST T2 WHERE <http://T2.NAME=T.NAME> ORDER BY T2\.D ASC\) AS "SECOND"
,NTH\_VALUE\(T\.VAL, 2\) OVER\(PARTITION BY <http://T.NAME> ORDER BY T\.D ASC\) AS "WNSECOND"

,\(SELECT FIRST 1 SKIP 2 T2\.VAL FROM TEST T2 WHERE <http://T2.NAME=T.NAME> ORDER BY T2\.D ASC\) AS "THIRD"
,NTH\_VALUE\(T\.VAL, 3\) OVER\(PARTITION BY <http://T.NAME> ORDER BY T\.D ASC\) AS "WNTHIRD"

,\(SELECT FIRST 1 SKIP 3 T2\.VAL FROM TEST T2 WHERE <http://T2.NAME=T.NAME> ORDER BY T2\.D ASC\) AS "FOURTH"
,NTH\_VALUE\(T\.VAL, 4\) OVER\(PARTITION BY <http://T.NAME> ORDER BY T\.D ASC\) AS "WNFOURTH"

,\(SELECT FIRST 1 SKIP 4 T2\.VAL FROM TEST T2 WHERE <http://T2.NAME=T.NAME> ORDER BY T2\.D ASC\) AS "FIFTH"
,NTH\_VALUE\(T\.VAL, 5\) OVER\(PARTITION BY <http://T.NAME> ORDER BY T\.D ASC\) AS "WNFIFTH"

FROM TEST T

ORDER BY http://T.NAME, T.D ASC

result

NAME VAL D FIRST WFIRST WNFIRST SECOND WNSECOND THIRD WNTHIRD FOURTH WNFOURTH FIFTH WNFIFTH 1.KARLOS 700 01.11.2014 700 700 700 800 NULL 600 NULL 900 NULL NULL NULL 2.KARLOS 800 02.11.2014 700 700 700 800 800 600 NULL 900 NULL NULL NULL 3.KARLOS 600 05.11.2014 700 700 700 800 800 600 600 900 NULL NULL NULL 4.KARLOS 900 08.11.2014 700 700 700 800 800 600 600 900 900 NULL NULL 5.MARTINES 1200 01.11.2014 1200 1200 1200 1300 NULL 1400 NULL 1300 NULL NULL NULL 6.MARTINES 1300 02.11.2014 1200 1200 1200 1300 1300 1400 NULL 1300 NULL NULL NULL 7.MARTINES 1400 05.11.2014 1200 1200 1200 1300 1300 1400 1400 1300 NULL NULL NULL 8.MARTINES 1300 08.11.2014 1200 1200 1200 1300 1300 1400 1400 1300 1300 NULL NULL

as you can see WNSECOND for first row is wrong NULL WNTHIRD for row 1 and 2 is wrong NULL

This is most wanted functionality Whole Window functions make FB better then ever

[firebird-automations]

Commented by: @asfernandes

Did you read this section of the documentation?

It's important to note that FIRST_VALUE, LAST_VALUE and NTH_VALUE also operates on a window frame, and Firebird is currently always framing from the first to the current (and not the last) row of the partition. This is likely to get strange results for NTH_VALUE and specially LAST_VALUE.

[firebird-automations]

Commented by: @livius2

Reallt? no.

i follow your subtasks and when i see that somethink is done "fixed" i test this. This task should remain open do you think? It is not done.

ps. thanks for great work :)

[firebird-automations]

Commented by: @asfernandes

What is not done and why it's not done?

Because it don't work as you wish does not mean so.

Did you tested another RDBMS? It should behave like Firebird.

I really don't tested your comments, I'm just guessing you're talking about the "framing thing" as asked you to confirm.

The main task already has a subticket for frame: CORE3647. It will not be implemented in this version.

[firebird-automations]

Commented by: @livius2

Now i see - we can get only previous row from already fetched resultset not nth_value from ordered partition and because of that i got Null for nth_value(2) for first row - because second row is not already fetched ..

When do you think think can this be supported Q2 2015 ? :) Or this can take much more time?