[Firebird-devel] row_number window function with over()

[liviuslivius]

Hi,
 
i post this question on support group by may be this is better place for this question, because it is about new feature in FB3.0
my English is not good then maybe i put example about what i saying
look at this simple table and unordered inserted data
 
CREATE TABLE TEST
(
  ID integer NOT NULL PRIMARY KEY
);
 
commit;
 
INSERT INTO TEST (ID) VALUES (2);
INSERT INTO TEST (ID) VALUES (1);
INSERT INTO TEST (ID) VALUES (4);
INSERT INTO TEST (ID) VALUES (3);
INSERT INTO TEST (ID) VALUES (5);
 
commit;
 
################ simple select to show data order in page ########################
 
SELECT T.ID FROM TEST T
 
[ID]
2
1
4
3
5
 
############### now row_number without order in query and in over() #########################
 
SELECT T.ID, row_number() over() FROM TEST T
[ID] [ROW_NUMBER]
2 1
1 2
4 3
3 4
5 5
 
??????????????? now row_number with order in query asc and nothing in over() ???????????????
 
should this query resultset look like this  or row_number column should be sequential 1, 2, 3, 4, 5?
SELECT T.ID, row_number() over() FROM TEST T order by T.ID ASC
[ID] [ROW_NUMBER]
1 2
2 1
3 4
4 3
5 5
 
??????????????? now row_number with order in query desc and nothing in over() ???????????????
should this query resultset look like this  or row_number column should be sequential 1, 2, 3, 4, 5?
SELECT T.ID, row_number() over() FROM TEST T order by T.ID DESC
[ID] [ROW_NUMBER]
5 5
4 3
3 4
2 1
1 2
 
########################################
this i suppose is ok we specify order in over() (interesting that resultset is also ordered by ID column asc)
SELECT T.ID, row_number() over(order by T.ID ASC) FROM TEST T
[ID] [ROW_NUMBER]
1 1
2 2
3 3
4 4
5 5
 
########################################
this i suppose is ok we specify order in over() (interesting that resultset is also ordered by ID column desc)
SELECT T.ID, row_number() over(order by T.ID DESC) FROM TEST T
[ID] [ROW_NUMBER]
5 1
4 2
3 3
2 4
1 5
 
########################################
this i suppose is ok we specify order in over() and in query
SELECT T.ID, row_number() over(order by T.ID ASC) FROM TEST T ORDER BY T.ID ASC
[ID] [ROW_NUMBER]
1 1
2 2
3 3
4 4
5 5
 
########################################
this i suppose is ok we specify order in over() and in query
SELECT T.ID, row_number() over(order by T.ID ASC) FROM TEST T ORDER BY T.ID DESC
[ID] [ROW_NUMBER]
5 5
4 4
3 3
2 2
1 1
 
########################################
this i suppose is ok we specify order in over() and in query
SELECT T.ID, row_number() over(order by T.ID DESC) FROM TEST T ORDER BY T.ID ASC
[ID] [ROW_NUMBER]
1 5
2 4
3 3
4 2
5 1
 
########################################
this i suppose is ok we specify order in over() and in query
SELECT T.ID, row_number() over(order by T.ID DESC) FROM TEST T ORDER BY T.ID DESC
[ID] [ROW_NUMBER]
5 1
4 2
3 3
2 4
1 5
 
regards,
Karol Bieniaszewski
 

[Adriano dos Santos Fernandes]

Put in your head that:
1) you can have more than one column using ROW_NUMBER, each which
different order
2) no implicit order will be assumed when only one column using
ROW_NUMBER is present

then all your questions will be answered.


Adriano

Post by liviuslivius
Hi,
i post this question on support group by may be this is better place
for this question, because it is about new feature in FB3.0
my English is not good then maybe i put example about what i saying
look at this simple table and unordered inserted data
CREATE TABLE TEST
(
ID integer NOT NULL PRIMARY KEY
);
commit;
INSERT INTO TEST (ID) VALUES (2);
INSERT INTO TEST (ID) VALUES (1);
INSERT INTO TEST (ID) VALUES (4);
INSERT INTO TEST (ID) VALUES (3);
INSERT INTO TEST (ID) VALUES (5);
commit;
################ simple select to show data order in page
########################
SELECT T.ID FROM TEST T
[ID]
2
1
4
3
5
############### now row_number without order in query and in over()
#########################
SELECT T.ID, row_number() over() FROM TEST T
[ID][ROW_NUMBER]
21
12
43
34
55
??????????????? now row_number with order in query asc and nothing in
over() ???????????????
should this query resultset look like this or row_number column
should be sequential 1, 2, 3, 4, 5?
SELECT T.ID, row_number() over() FROM TEST T order by T.ID ASC
[ID][ROW_NUMBER]
12
21
34
43
55
??????????????? now row_number with order in query desc and nothing in
over() ???????????????
should this query resultset look like this or row_number column
should be sequential 1, 2, 3, 4, 5?
SELECT T.ID, row_number() over() FROM TEST T order by T.ID DESC
[ID][ROW_NUMBER]
55
43
34
21
12
########################################
this i suppose is ok we specify order in over() (interesting that
resultset is also ordered by ID column asc)
SELECT T.ID, row_number() over(order by T.ID ASC) FROM TEST T
[ID][ROW_NUMBER]
11
22
33
44
55
########################################
this i suppose is ok we specify order in over() (interesting that
resultset is also ordered by ID column desc)
SELECT T.ID, row_number() over(order by T.ID DESC) FROM TEST T
[ID][ROW_NUMBER]
51
42
33
24
15
########################################
this i suppose is ok we specify order in over() and in query
SELECT T.ID, row_number() over(order by T.ID ASC) FROM TEST T ORDER BY T.ID ASC
[ID][ROW_NUMBER]
11
22
33
44
55
########################################
this i suppose is ok we specify order in over() and in query
SELECT T.ID, row_number() over(order by T.ID ASC) FROM TEST T ORDER BY T.ID DESC
[ID][ROW_NUMBER]
55
44
33
22
11
########################################
this i suppose is ok we specify order in over() and in query
SELECT T.ID, row_number() over(order by T.ID DESC) FROM TEST T ORDER BY T.ID ASC
[ID][ROW_NUMBER]
15
24
33
42
51
########################################
this i suppose is ok we specify order in over() and in query
SELECT T.ID, row_number() over(order by T.ID DESC) FROM TEST T ORDER BY T.ID DESC
[ID][ROW_NUMBER]
51
42
33
24
15
regards,
Karol Bieniaszewski
------------------------------------------------------------------------------
Get 100% visibility into Java/.NET code with AppDynamics Lite!
It's a free troubleshooting tool designed for production.
Get down to code-level detail for bottlenecks, with <2% overhead.
Download for free and get started troubleshooting in minutes.
http://pubads.g.doubleclick.net/gampad/clk?id=48897031&iu=/4140/ostg.clktrk
Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel

[liviuslivius]

Hi,

you do not understand me correctly
i talking about query with over() not over(order by)
i know that i can specify many row_number clause in one query

Firebird allow this construction over(without order by) and then it should numerate records in resultset sequential
Example MSSQL disallow row_number with over(without order by)
this is message from MSSQL
"The function 'row_number' must have an OVER clause with ORDER BY."

If Firebird allow this structure, then this two queries should always numerate resultset not some internal order of records
e.g.
SLECT T.ID, row_number() over() FROM TEST T order by T.ID ASC <-- asc
[ID][ROW_NUMBER]
1 1
2 2
3 3
4 4
5 5

SELECT T.ID, row_number() over() FROM TEST T order by T.ID DESC <-- desc
[ID][ROW_NUMBER]
5 1
4 2
3 3
2 4
1 5

but now Firebird return row_number like this

SELECT T.ID, row_number() over() FROM TEST T order by T.ID ASC
[ID][ROW_NUMBER]
1 2
2 1
3 4
4 3
5 5

this should be fixed or disallowed by Firebird like MSSQL

regards,
Karol Bieniaszewski

Post by Adriano dos Santos Fernandes
1) you can have more than one column using ROW_NUMBER, each which
different order
2) no implicit order will be assumed when only one column using
ROW_NUMBER is present
then all your questions will be answered.
Adriano

Post by liviuslivius
Hi,
i post this question on support group by may be this is better place
for this question, because it is about new feature in FB3.0
my English is not good then maybe i put example about what i saying
look at this simple table and unordered inserted data
CREATE TABLE TEST
(
ID integer NOT NULL PRIMARY KEY
);
commit;
INSERT INTO TEST (ID) VALUES (2);
INSERT INTO TEST (ID) VALUES (1);
INSERT INTO TEST (ID) VALUES (4);
INSERT INTO TEST (ID) VALUES (3);
INSERT INTO TEST (ID) VALUES (5);
commit;
################ simple select to show data order in page
########################
SELECT T.ID FROM TEST T
[ID]
2
1
4
3
5
############### now row_number without order in query and in over()
#########################
SELECT T.ID, row_number() over() FROM TEST T
[ID][ROW_NUMBER]
21
12
43
34
55
??????????????? now row_number with order in query asc and nothing in
over() ???????????????
should this query resultset look like this or row_number column
should be sequential 1, 2, 3, 4, 5?
SELECT T.ID, row_number() over() FROM TEST T order by T.ID ASC
[ID][ROW_NUMBER]
12
21
34
43
55
??????????????? now row_number with order in query desc and nothing in
over() ???????????????
should this query resultset look like this or row_number column
should be sequential 1, 2, 3, 4, 5?
SELECT T.ID, row_number() over() FROM TEST T order by T.ID DESC
[ID][ROW_NUMBER]
55
43
34
21
12
########################################
this i suppose is ok we specify order in over() (interesting that
resultset is also ordered by ID column asc)
SELECT T.ID, row_number() over(order by T.ID ASC) FROM TEST T
[ID][ROW_NUMBER]
11
22
33
44
55
########################################
this i suppose is ok we specify order in over() (interesting that
resultset is also ordered by ID column desc)
SELECT T.ID, row_number() over(order by T.ID DESC) FROM TEST T
[ID][ROW_NUMBER]
51
42
33
24
15
########################################
this i suppose is ok we specify order in over() and in query
SELECT T.ID, row_number() over(order by T.ID ASC) FROM TEST T ORDER BY
T.ID ASC
[ID][ROW_NUMBER]
11
22
33
44
55
########################################
this i suppose is ok we specify order in over() and in query
SELECT T.ID, row_number() over(order by T.ID ASC) FROM TEST T ORDER BY
T.ID DESC
[ID][ROW_NUMBER]
55
44
33
22
11
########################################
this i suppose is ok we specify order in over() and in query
SELECT T.ID, row_number() over(order by T.ID DESC) FROM TEST T ORDER
BY T.ID ASC
[ID][ROW_NUMBER]
15
24
33
42
51
########################################
this i suppose is ok we specify order in over() and in query
SELECT T.ID, row_number() over(order by T.ID DESC) FROM TEST T ORDER
BY T.ID DESC
[ID][ROW_NUMBER]
51
42
33
24
15
regards,
Karol Bieniaszewski
------------------------------------------------------------------------------
Get 100% visibility into Java/.NET code with AppDynamics Lite!
It's a free troubleshooting tool designed for production.
Get down to code-level detail for bottlenecks, with <2% overhead.
Download for free and get started troubleshooting in minutes.
http://pubads.g.doubleclick.net/gampad/clk?id=48897031&iu=/4140/ostg.clktrk
Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel

------------------------------------------------------------------------------
Get 100% visibility into Java/.NET code with AppDynamics Lite!
It's a free troubleshooting tool designed for production.
Get down to code-level detail for bottlenecks, with <2% overhead.
Download for free and get started troubleshooting in minutes.
http://pubads.g.doubleclick.net/gampad/clk?id=48897031&iu=/4140/ostg.clktrk
Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel

[Mark Rotteveel]

Post by liviuslivius
Hi,
you do not understand me correctly
i talking about query with over() not over(order by)
i know that i can specify many row_number clause in one query
Firebird allow this construction over(without order by) and then it should numerate records in resultset sequential
Example MSSQL disallow row_number with over(without order by)
this is message from MSSQL
"The function 'row_number' must have an OVER clause with ORDER BY."
If Firebird allow this structure, then this two queries should always numerate resultset not some internal order of records
e.g.
SLECT T.ID, row_number() over() FROM TEST T order by T.ID ASC <-- asc
[ID][ROW_NUMBER]
1 1
2 2
3 3
4 4
5 5
SELECT T.ID, row_number() over() FROM TEST T order by T.ID DESC <-- desc
[ID][ROW_NUMBER]
5 1
4 2
3 3
2 4
1 5
but now Firebird return row_number like this
SELECT T.ID, row_number() over() FROM TEST T order by T.ID ASC
[ID][ROW_NUMBER]
1 2
2 1
3 4
4 3
5 5
this should be fixed or disallowed by Firebird like MSSQL

According to the SQL Specification (as far as I read it correctly) you
are not entirely correct, it says SQL:2011 Foundation, 6.10 <window
function>:

"
7) If <ntile function>, <lead or lag function>, <rank function type> or
ROW_NUMBER is specified, then:
a) If <ntile function>, <lead or lag function>, RANK or DENSE_RANK is
specified, then the window ordering clause WOC of WDX shall be present.
...
"

In other words, ROW_NUMBER does not require a window ordering clause.
The specification also says that ROW_NUMBER is not deterministic. In
other words: you should not expect any specific ordering of row_number
if you did not specify an order by in the window and in the query itself.

However it also says (under Conformance Rules):
"
4) Without Feature T612, “Advanced OLAP operations”, conforming SQL
language shall not contain a <window function> that simply contains
ROW_NUMBER and immediately contains a <window name or specification>
whose window structure descriptor does not contain a window ordering clause.
"

I am not sure how to read this though, because I believe they actually
mean: "to implement feature T612, you must have ...".

[liviuslivius]

Post by Mark Rotteveel

Post by liviuslivius
Hi,
you do not understand me correctly
i talking about query with over() not over(order by)
i know that i can specify many row_number clause in one query
Firebird allow this construction over(without order by) and then it should numerate records in resultset sequential
Example MSSQL disallow row_number with over(without order by)
this is message from MSSQL
"The function 'row_number' must have an OVER clause with ORDER BY."
If Firebird allow this structure, then this two queries should always numerate resultset not some internal order of records
e.g.
SLECT T.ID, row_number() over() FROM TEST T order by T.ID ASC <-- asc
[ID][ROW_NUMBER]
1 1
2 2
3 3
4 4
5 5
SELECT T.ID, row_number() over() FROM TEST T order by T.ID DESC <-- desc
[ID][ROW_NUMBER]
5 1
4 2
3 3
2 4
1 5
but now Firebird return row_number like this
SELECT T.ID, row_number() over() FROM TEST T order by T.ID ASC
[ID][ROW_NUMBER]
1 2
2 1
3 4
4 3
5 5
this should be fixed or disallowed by Firebird like MSSQL

According to the SQL Specification (as far as I read it correctly) you
are not entirely correct, it says SQL:2011 Foundation, 6.10 <window
"
7) If <ntile function>, <lead or lag function>, <rank function type> or
a) If <ntile function>, <lead or lag function>, RANK or DENSE_RANK is
specified, then the window ordering clause WOC of WDX shall be present.
...
"
In other words, ROW_NUMBER does not require a window ordering clause.
The specification also says that ROW_NUMBER is not deterministic. In
other words: you should not expect any specific ordering of row_number
if you did not specify an order by in the window and in the query itself.
"
4) Without Feature T612, “Advanced OLAP operations”, conforming SQL
language shall not contain a <window function> that simply contains
ROW_NUMBER and immediately contains a <window name or specification>
whose window structure descriptor does not contain a window ordering clause.
"
I am not sure how to read this though, because I believe they actually
mean: "to implement feature T612, you must have ...".
--
Mark Rotteveel

Hi,

i read now it also and find something line like this
row_number() over WNS i equivalent to:
count(*) over (WNS1 ROWS UNBOUNDED PRECEDING)

and i test this also against Oracle 11g
and try SELECT T.ID, row_Number() over() FROM TEST
i get error the same as in MSSQL
"Error: ORA-30485: missing ORDER BY expression in the window specification"

regards,
Karol Bieniaszewski

[Dmitry Yemanov]

Post by liviuslivius
and i test this also against Oracle 11g
and try SELECT T.ID, row_Number() over() FROM TEST
i get error the same as in MSSQL
"Error: ORA-30485: missing ORDER BY expression in the window specification"

PGSQL 9.1:

postgres=# select id from test;
id
----
2
1
4
3
5
(5 rows)

postgres=# select id, row_number() over() from test;
id | row_number
----+------------
2 | 1
1 | 2
4 | 3
3 | 4
5 | 5
(5 rows)

postgres=# select id, row_number() over() from test order by id asc;
id | row_number
----+------------
1 | 2
2 | 1
3 | 4
4 | 3
5 | 5
(5 rows)

postgres=# select id, row_number() over() from test order by id desc;
id | row_number
----+------------
5 | 5
4 | 3
3 | 4
2 | 1
1 | 2
(5 rows)

postgres=# select id, row_number() over(order by id asc) from test;
id | row_number
----+------------
1 | 1
2 | 2
3 | 3
4 | 4
5 | 5
(5 rows)

postgres=# select id, row_number() over(order by id desc) from test;
id | row_number
----+------------
5 | 1
4 | 2
3 | 3
2 | 4
1 | 5
(5 rows)

postgres=# select id, row_number() over(order by id asc) from test order
by id asc;
id | row_number
----+------------
1 | 1
2 | 2
3 | 3
4 | 4
5 | 5
(5 rows)

postgres=# select id, row_number() over(order by id asc) from test order
by id desc;
id | row_number
----+------------
5 | 5
4 | 4
3 | 3
2 | 2
1 | 1
(5 rows)

postgres=# select id, row_number() over(order by id desc) from test
order by id asc;
id | row_number
----+------------
1 | 5
2 | 4
3 | 3
4 | 2
5 | 1
(5 rows)

postgres=# select id, row_number() over(order by id desc) from test
order by id desc;
id | row_number
----+------------
5 | 1
4 | 2
3 | 3
2 | 4
1 | 5
(5 rows)


Dmitry