[MDEV-24089] support oracle syntax: rownum - Jira

woqutech created issue - 2020-11-02 07:16

woqutech made changes - 2020-11-02 07:16

Field	Original Value	New Value
Link		This issue blocks MDEV-20020 [ MDEV-20020 ]

woqutech made changes - 2020-11-02 07:16

Link

This issue blocks ~~MDEV-19782~~ [ ~~MDEV-19782~~ ]

woqutech added a comment - 2020-11-02 08:07

we have implement a version of rownum, but it is not suitable for the following scenarios:

in the case of sub-query, we must set condition_pushdown_for_derived=off, derived_merge=off to keep the result the same with mariadb.
the current implementation has much restrictions in Nested subquery. like `select * from ( select t1.* , rownum row_id from t1 where rownum <= 5 ) where row_id > 2 `;
The current query result order is unstable
The current implementation with return error `ERROR 1052 (23000): Column'rownum' in field list is ambiguous` if there are multiple tables join. For example: `select t1.c1, t1.c2 ,rownum from t1 left join t2 on t1.c1=t2.c1 inner join t3 on t2.c1=t3.c1;` 10.5.6-rownum.patch

woqutech added a comment - 2020-11-02 08:07 we have implement a version of rownum, but it is not suitable for the following scenarios: in the case of sub-query, we must set condition_pushdown_for_derived=off, derived_merge=off to keep the result the same with mariadb. the current implementation has much restrictions in Nested subquery. like `select * from ( select t1.* , rownum row_id from t1 where rownum <= 5 ) where row_id > 2 `; The current query result order is unstable The current implementation with return error `ERROR 1052 (23000): Column'rownum' in field list is ambiguous` if there are multiple tables join. For example: `select t1.c1, t1.c2 ,rownum from t1 left join t2 on t1.c1=t2.c1 inner join t3 on t2.c1=t3.c1;` 10.5.6-rownum.patch

woqutech made changes - 2020-11-02 08:07

Attachment

10.5.6-rownum.patch [ 54563 ]

woqutech made changes - 2020-11-02 08:56

Description

oracle rownum is a pseudocolumn (not a real column) that is available in a query. ROWNUM will be assigned the numbers 1, 2, 3, 4, ... N. For each row returned by a query, the ROWNUM pseudocolumn returns a number indicating the order in which Oracle selects the row from a table or set of joined rows. The first row selected has a ROWNUM of 1, the second has 2, and so on.
Can we implement it on sql_mode=oracle

* oracle rownum reference: https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/ROWNUM-Pseudocolumn.html#GUID-2E40EC12-3FCF-4A4F-B5F2-6BC669021726

oracle rownum is a pseudocolumn (not a real column) that is available in a query. ROWNUM will be assigned the numbers 1, 2, 3, 4, ... N. For each row returned by a query, the ROWNUM pseudocolumn returns a number indicating the order in which Oracle selects the row from a table or set of joined rows. The first row selected has a ROWNUM of 1, the second has 2, and so on.
Can we implement it on sql_mode=oracle

oracle rownum reference: https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/ROWNUM-Pseudocolumn.html#GUID-2E40EC12-3FCF-4A4F-B5F2-6BC669021726

woqutech added a comment - 2020-11-03 11:33 - edited

test result on oracle

create table t1(c1 number, c2 number);

create table t2(c1 number, c2 number);

create table t3(c1 number, c2 number);

insert into t1 values(1,1);

insert into t1 values(2,2);

insert into t1 values(3,3);

insert into t2 values(1,1);

insert into t2 values(2,2);

insert into t2 values(3,3);

insert into t3 values(1,1);

insert into t3 values(2,2);

insert into t3 values(3,3);

SQL> select t1.c1, t1.c2 ,rownum from t1 left join t2 on t1.c1=t2.c1 inner join t3 on t2.c1=t3.c1;

C1 C2 ROWNUM
---------- ---------- ----------
1 1 1
2 2 2
3 3 3

SQL> select * from ( select t1.* , rownum row_id from t1 where rownum <= 5 ) where row_id > 2;

C1 C2 ROW_ID
---------- ---------- ----------
3 3 3


SQL> select t1.c1, (select t2.c2 from t2 where t1.c1=t2.c2 and rownum < t1.c1) from t1;

C1 (SELECTT2.C2FROMT2WHERET1.C1=T2.C2ANDROWNUM<T1.C1)
---------- --------------------------------------------------
1
2 2
3 3

woqutech added a comment - 2020-11-03 11:33 - edited test result on oracle create table t1(c1 number, c2 number); create table t2(c1 number, c2 number); create table t3(c1 number, c2 number); insert into t1 values (1,1); insert into t1 values (2,2); insert into t1 values (3,3); insert into t2 values (1,1); insert into t2 values (2,2); insert into t2 values (3,3); insert into t3 values (1,1); insert into t3 values (2,2); insert into t3 values (3,3); SQL> select t1.c1, t1.c2 ,rownum from t1 left join t2 on t1.c1=t2.c1 inner join t3 on t2.c1=t3.c1; C1 C2 ROWNUM ---------- ---------- ---------- 1 1 1 2 2 2 3 3 3 SQL> select * from ( select t1.* , rownum row_id from t1 where rownum <= 5 ) where row_id > 2; C1 C2 ROW_ID ---------- ---------- ---------- 3 3 3 SQL> select t1.c1, (select t2.c2 from t2 where t1.c1=t2.c2 and rownum < t1.c1) from t1; C1 (SELECTT2.C2FROMT2WHERET1.C1=T2.C2ANDROWNUM<T1.C1) ---------- -------------------------------------------------- 1 2 2 3 3

woqutech added a comment - 2020-11-03 11:34

add testcase file
rownum.result rownum.test

woqutech added a comment - 2020-11-03 11:34 add testcase file rownum.result rownum.test

woqutech made changes - 2020-11-03 11:34

Attachment		rownum.result [ 54613 ]
Attachment		rownum.test [ 54614 ]

woqutech added a comment - 2020-11-03 11:43 - edited

The most common usage of rownum (paste from rownum.result) is:

SET SQL_MODE='oracle';

set optimizer_switch='condition_pushdown_for_derived=off,derived_merge=off';

create database test_db1;

create table t1(c1 int, c2 varchar2(20), c3 timestamp(6), c4 varchar2(2));

create table t2(c1 int, c2 char(20), c3 int, c4 varchar2(2));

insert into t1 values(1, 'jim', '1984-08-21 18:00:00', '1');

insert into t1 values(2, 'menny','1986-10-02 20:20:00', '1');

insert into t1 values(3, 'linda', '1986-11-02 20:00:00', '1');

insert into t1 values(4, 'shory','1986-10-01 00:20:00', '0');

insert into t1 values(5, 'xuying', '1986-10-02 10:00:00', '1');

insert into t1 values(6, 'xujun', '1988-10-02 00:00:00', '0');

insert into t1 values(7, 'taoju', '1981-10-02 11:20:00', '1');

insert into t1 values(8, 'beibei', '1981-11-02 00:00:00', '1');

insert into t2 values(1, 'jim', 3, '0');

insert into t2 values(1, 'jim', 2, '1');

insert into t2 values(1, 'jim', 1, '1');

insert into t2 values(2, 'menny', 2, '1');

insert into t2 values(2, 'menny', 2, '1');

insert into t2 values(2, 'menny', 2, '1');

insert into t2 values(2, 'menny', 3, '0');

insert into t2 values(3, 'linda', 3, '1');

insert into t2 values(3, 'linda', 4, '1');

insert into t2 values(4, 'shory', 2, '1');

insert into t2 values(4, 'shory', 3, '0');

insert into t2 values(5, 'xuying', 1, '0');

insert into t2 values(5, 'xuying', 1, '0');

insert into t2 values(7, 'taoju', 3, '0');

select * from t1;

c1	c2	c3	c4

1	jim	x.000000	1

2	menny	x.000000	1

3	linda	x.000000	1

4	shory	x.000000	0

5	xuying	x.000000	1

6	xujun	x.000000	0

7	taoju	x.000000	1

8	beibei	x.000000	1

select * from t2;

c1	c2	c3	c4

1	jim	3	0

1	jim	2	1

1	jim	1	1

2	menny	2	1

2	menny	2	1

2	menny	2	1

2	menny	3	0

3	linda	3	1

3	linda	4	1

4	shory	2	1

4	shory	3	0

5	xuying	1	0

5	xuying	1	0

7	taoju	3	0

###############################################

"common test for rownum"

select c1, c2, rownum from t1;

c1	c2	rownum

1	jim	1

2	menny	2

3	linda	3

4	shory	4

5	xuying	5

6	xujun	6

7	taoju	7

8	beibei	8

select c1, c2, rownum from t1 where rownum = 1;

c1	c2	rownum

1	jim	1

select c1, c2, rownum from t1 where rownum >= 1;

c1	c2	rownum

1	jim	1

2	menny	2

3	linda	3

4	shory	4

5	xuying	5

6	xujun	6

7	taoju	7

8	beibei	8

select c1, c2, rownum from t1 where 5 >= rownum ;

c1	c2	rownum

1	jim	1

2	menny	2

3	linda	3

4	shory	4

5	xuying	5

select c1, c2, rownum from t1 where rownum != 1;

c1	c2	rownum

select c1, c2, rownum from t1 where rownum < 1;

c1	c2	rownum

select c1, c2, rownum from t1 where rownum < 5;

c1	c2	rownum

1	jim	1

2	menny	2

3	linda	3

4	shory	4

###############################################

"test rownum when join and subquery"

select * from (select rownum row_id, c1, c2 from t1) where row_id < 3 and row_id >5;

row_id	c1	c2

select * from (select rownum row_id, c1, c2 from t1) where row_id > 1 and row_id <5;

row_id	c1	c2

2	2	menny

3	3	linda

4	4	shory

select * from (select rownum row_id, c1, c2 from t1) where row_id < 3 or row_id > 5;

row_id	c1	c2

1	1	jim

2	2	menny

6	6	xujun

7	7	taoju

8	8	beibei

select * from (select rownum row_id, c1, c2, c4 from t1) where row_id < 7 and c4=1;

row_id	c1	c2	c4

1	1	jim	1

2	2	menny	1

3	3	linda	1

5	5	xuying	1

select * from (select tmp_page.*, rownum row_id from (select d.c1 as c1, d.c2 as c2, to_char(d.c3, 'YYYY-MM-DD HH:MI:SS') as xxtime, so.c3 as status from t1 d left join t2 so on d.c1=so.c1 and so.c4=1 where 1=1 and d.c4=1 order by d.c4) tmp_page where rownum <=1) where row_id > 1;

c1	c2	xxtime	status	row_id

select * from (select tmp_page.*, rownum row_id from (select d.c1 as c1, d.c2 as c2, to_char(d.c3, 'YYYY-MM-DD HH:MI:SS') as xxtime, so.c3 as status from t1 d left join t2 so on d.c1=so.c1 and so.c4=1 where 1=1 and d.c4=1 order by d.c3) tmp_page where rownum <=4) where row_id > 1;

c1	c2	xxtime	status	row_id

8	beibei	x	NULL	2

1	jim	x	2	3

1	jim	x	1	4

select * from (select tmp_page.*, rownum row_id from (select d.c1 as c1, d.c2 as c2, to_char(d.c3, 'YYYY-MM-DD HH:MI:SS') as xxtime, so.c3 as status from t1 d left join t2 so on d.c1=so.c1 and so.c4=1 where 1=1 and d.c4=1 order by d.c3) tmp_page where rownum <=4) where row_id <= 3;

c1	c2	xxtime	status	row_id

7	taoju	x	NULL	1

8	beibei	x	NULL	2

1	jim	x	2	3

select * from (select tmp_page.*, rownum row_id from (select d.c1 as c1, d.c2 as c2, to_char(d.c3, 'YYYY-MM-DD HH:MI:SS') as xxtime, so.c3 as status from t1 d left join t2 so on d.c1=so.c1 and so.c4=1 where 1=1 and d.c4=1 order by d.c3) tmp_page where rownum <=19) where row_id >=1;

c1	c2	xxtime	status	row_id

7	taoju	x	NULL	1

8	beibei	x	NULL	2

1	jim	x	2	3

1	jim	x	1	4

5	xuying	x	NULL	5

2	menny	x	2	6

2	menny	x	2	7

2	menny	x	2	8

3	linda	x	3	9

3	linda	x	4	10

###############################################

"test for update/delete"

update t2 set c3 = rownum;

select c1,c3 from t2;

c1	c3

1	1

1	2

1	3

2	4

2	5

2	6

2	7

3	8

3	9

4	10

4	11

5	12

5	13

7	14

delete from t2 where c3=rownum;

select c1,c3 from t2;

c1	c3

drop table t1;

drop table t2;

drop database test_db1;

###############################################

"test for many field in a table storge in hash"

create table tx(c1 int, c2 int, c3 int, c4 int, c5 int, c6 int, c7 int, c8 int, c9 int, c11 int, c12 int, c13 int, c14 int, c15 int, c16 int, c17 int, c18 int, c19 int,c21 int, c22 int, c23 int, c24 int, c25 int, c26 int, c27 int, c28 int, c29 int,c31 int, c32 int, c33 int, c34 int, c35 int, c36 int, c37 int, c38 int, c39 int,c41 int, c42 int, c43 int, c44 int, c45 int, c46 int, c47 int, c48 int, c49 int);

insert into tx (c1, c2) values (1,2);

insert into tx (c1, c2) values (2,2);

insert into tx (c1, c2) values (3,2);

select c1, rownum from tx;

c1	rownum

1	1

2	2

3	3

select c1, rownum from tx where rownum = 1;

c1	rownum

1	1

select c1, rownum from tx where rownum < 1;

c1	rownum

drop table tx;

woqutech added a comment - 2020-11-03 11:43 - edited The most common usage of rownum (paste from rownum.result) is: SET SQL_MODE= 'oracle' ; set optimizer_switch= 'condition_pushdown_for_derived=off,derived_merge=off' ; create database test_db1; create table t1(c1 int , c2 varchar2(20), c3 timestamp (6), c4 varchar2(2)); create table t2(c1 int , c2 char (20), c3 int , c4 varchar2(2)); insert into t1 values (1, 'jim' , '1984-08-21 18:00:00' , '1' ); insert into t1 values (2, 'menny' , '1986-10-02 20:20:00' , '1' ); insert into t1 values (3, 'linda' , '1986-11-02 20:00:00' , '1' ); insert into t1 values (4, 'shory' , '1986-10-01 00:20:00' , '0' ); insert into t1 values (5, 'xuying' , '1986-10-02 10:00:00' , '1' ); insert into t1 values (6, 'xujun' , '1988-10-02 00:00:00' , '0' ); insert into t1 values (7, 'taoju' , '1981-10-02 11:20:00' , '1' ); insert into t1 values (8, 'beibei' , '1981-11-02 00:00:00' , '1' ); insert into t2 values (1, 'jim' , 3, '0' ); insert into t2 values (1, 'jim' , 2, '1' ); insert into t2 values (1, 'jim' , 1, '1' ); insert into t2 values (2, 'menny' , 2, '1' ); insert into t2 values (2, 'menny' , 2, '1' ); insert into t2 values (2, 'menny' , 2, '1' ); insert into t2 values (2, 'menny' , 3, '0' ); insert into t2 values (3, 'linda' , 3, '1' ); insert into t2 values (3, 'linda' , 4, '1' ); insert into t2 values (4, 'shory' , 2, '1' ); insert into t2 values (4, 'shory' , 3, '0' ); insert into t2 values (5, 'xuying' , 1, '0' ); insert into t2 values (5, 'xuying' , 1, '0' ); insert into t2 values (7, 'taoju' , 3, '0' ); select * from t1; c1 c2 c3 c4 1 jim x.000000 1 2 menny x.000000 1 3 linda x.000000 1 4 shory x.000000 0 5 xuying x.000000 1 6 xujun x.000000 0 7 taoju x.000000 1 8 beibei x.000000 1 select * from t2; c1 c2 c3 c4 1 jim 3 0 1 jim 2 1 1 jim 1 1 2 menny 2 1 2 menny 2 1 2 menny 2 1 2 menny 3 0 3 linda 3 1 3 linda 4 1 4 shory 2 1 4 shory 3 0 5 xuying 1 0 5 xuying 1 0 7 taoju 3 0 ############################################### "common test for rownum" select c1, c2, rownum from t1; c1 c2 rownum 1 jim 1 2 menny 2 3 linda 3 4 shory 4 5 xuying 5 6 xujun 6 7 taoju 7 8 beibei 8 select c1, c2, rownum from t1 where rownum = 1; c1 c2 rownum 1 jim 1 select c1, c2, rownum from t1 where rownum >= 1; c1 c2 rownum 1 jim 1 2 menny 2 3 linda 3 4 shory 4 5 xuying 5 6 xujun 6 7 taoju 7 8 beibei 8 select c1, c2, rownum from t1 where 5 >= rownum ; c1 c2 rownum 1 jim 1 2 menny 2 3 linda 3 4 shory 4 5 xuying 5 select c1, c2, rownum from t1 where rownum != 1; c1 c2 rownum select c1, c2, rownum from t1 where rownum < 1; c1 c2 rownum select c1, c2, rownum from t1 where rownum < 5; c1 c2 rownum 1 jim 1 2 menny 2 3 linda 3 4 shory 4 ############################################### "test rownum when join and subquery" select * from ( select rownum row_id, c1, c2 from t1) where row_id < 3 and row_id >5; row_id c1 c2 select * from ( select rownum row_id, c1, c2 from t1) where row_id > 1 and row_id <5; row_id c1 c2 2 2 menny 3 3 linda 4 4 shory select * from ( select rownum row_id, c1, c2 from t1) where row_id < 3 or row_id > 5; row_id c1 c2 1 1 jim 2 2 menny 6 6 xujun 7 7 taoju 8 8 beibei select * from ( select rownum row_id, c1, c2, c4 from t1) where row_id < 7 and c4=1; row_id c1 c2 c4 1 1 jim 1 2 2 menny 1 3 3 linda 1 5 5 xuying 1 select * from ( select tmp_page.*, rownum row_id from ( select d.c1 as c1, d.c2 as c2, to_char(d.c3, 'YYYY-MM-DD HH:MI:SS' ) as xxtime, so.c3 as status from t1 d left join t2 so on d.c1=so.c1 and so.c4=1 where 1=1 and d.c4=1 order by d.c4) tmp_page where rownum <=1) where row_id > 1; c1 c2 xxtime status row_id select * from ( select tmp_page.*, rownum row_id from ( select d.c1 as c1, d.c2 as c2, to_char(d.c3, 'YYYY-MM-DD HH:MI:SS' ) as xxtime, so.c3 as status from t1 d left join t2 so on d.c1=so.c1 and so.c4=1 where 1=1 and d.c4=1 order by d.c3) tmp_page where rownum <=4) where row_id > 1; c1 c2 xxtime status row_id 8 beibei x NULL 2 1 jim x 2 3 1 jim x 1 4 select * from ( select tmp_page.*, rownum row_id from ( select d.c1 as c1, d.c2 as c2, to_char(d.c3, 'YYYY-MM-DD HH:MI:SS' ) as xxtime, so.c3 as status from t1 d left join t2 so on d.c1=so.c1 and so.c4=1 where 1=1 and d.c4=1 order by d.c3) tmp_page where rownum <=4) where row_id <= 3; c1 c2 xxtime status row_id 7 taoju x NULL 1 8 beibei x NULL 2 1 jim x 2 3 select * from ( select tmp_page.*, rownum row_id from ( select d.c1 as c1, d.c2 as c2, to_char(d.c3, 'YYYY-MM-DD HH:MI:SS' ) as xxtime, so.c3 as status from t1 d left join t2 so on d.c1=so.c1 and so.c4=1 where 1=1 and d.c4=1 order by d.c3) tmp_page where rownum <=19) where row_id >=1; c1 c2 xxtime status row_id 7 taoju x NULL 1 8 beibei x NULL 2 1 jim x 2 3 1 jim x 1 4 5 xuying x NULL 5 2 menny x 2 6 2 menny x 2 7 2 menny x 2 8 3 linda x 3 9 3 linda x 4 10 ############################################### "test for update/delete" update t2 set c3 = rownum; select c1,c3 from t2; c1 c3 1 1 1 2 1 3 2 4 2 5 2 6 2 7 3 8 3 9 4 10 4 11 5 12 5 13 7 14 delete from t2 where c3=rownum; select c1,c3 from t2; c1 c3 drop table t1; drop table t2; drop database test_db1; ############################################### "test for many field in a table storge in hash" create table tx(c1 int , c2 int , c3 int , c4 int , c5 int , c6 int , c7 int , c8 int , c9 int , c11 int , c12 int , c13 int , c14 int , c15 int , c16 int , c17 int , c18 int , c19 int ,c21 int , c22 int , c23 int , c24 int , c25 int , c26 int , c27 int , c28 int , c29 int ,c31 int , c32 int , c33 int , c34 int , c35 int , c36 int , c37 int , c38 int , c39 int ,c41 int , c42 int , c43 int , c44 int , c45 int , c46 int , c47 int , c48 int , c49 int ); insert into tx (c1, c2) values (1,2); insert into tx (c1, c2) values (2,2); insert into tx (c1, c2) values (3,2); select c1, rownum from tx; c1 rownum 1 1 2 2 3 3 select c1, rownum from tx where rownum = 1; c1 rownum 1 1 select c1, rownum from tx where rownum < 1; c1 rownum drop table tx;

Michael Widenius made changes - 2020-11-03 13:35

Description

oracle rownum is a pseudocolumn (not a real column) that is available in a query. ROWNUM will be assigned the numbers 1, 2, 3, 4, ... N. For each row returned by a query, the ROWNUM pseudocolumn returns a number indicating the order in which Oracle selects the row from a table or set of joined rows. The first row selected has a ROWNUM of 1, the second has 2, and so on.
Can we implement it on sql_mode=oracle

oracle rownum reference: https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/ROWNUM-Pseudocolumn.html#GUID-2E40EC12-3FCF-4A4F-B5F2-6BC669021726

Oracle ROWNUM is a pseudocolumn (not a real column) that is generated
when running a select.

ROWNUM will be assigned the numbers 1, 2, 3, 4, ... N.

For each row combination returned by a query, ROWNUM returns a number indicating the order in which Oracle selects the rows from the table (tables)

The first row combination selected has a ROWNUM of 1, the second has 2, and so
on.

When used in a sub query, the subquery will generate a new ROWNUM sequence for
each sub query execution.

The ROWNUM psedou column should be implemented under SQL_MODE=ORACLE.

In many cases one could also replace WHERE ROWNUM <= X with LIMIT X.

oracle rownum reference: https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/ROWNUM-Pseudocolumn.html#GUID-2E40EC12-3FCF-4A4F-B5F2-6BC669021726

The most common usage of rownum is to limit the number of rows in the query:
SELECT * from table where rownum < 10;

Examples:
create table t1 (a int primary key);
create table t2 (b int primary key);
insert into t1 values(1),(2),(3);
insert into t2 values(1),(2),(3);

select t1.a, (select t2.b from t2 where t1.a=t2.b and rownum < t1.a) from t1;

The following query is undefined (can return any rows) as rownum is generated before ORDER BY is done and there is no guarantee in which order the database is returning rows.

Select * from t1 order by t1.a desc having rownum <= 2;

create table t1(c1 number, c2 number);
create table t2(c1 number, c2 number);
create table t3(c1 number, c2 number);

insert into t1 values(1,1);
insert into t1 values(2,2);
insert into t1 values(3,3);

insert into t2 values(1,1);
insert into t2 values(2,2);
insert into t2 values(3,3);

insert into t3 values(1,1);
insert into t3 values(2,2);
insert into t3 values(3,3);

SQL> select t1.c1, t1.c2 ,rownum from t1 left join t2 on t1.c1=t2.c1 inner join t3 on t2.c1=t3.c1;

        C1 C2 ROWNUM
---------- ---------- ----------
         1 1 1
         2 2 2
         3 3 3

SQL> select * from ( select t1.* , rownum row_id from t1 where rownum <= 5 ) where row_id > 2;

        C1 C2 ROW_ID
---------- ---------- ----------
         3 3 3

SQL> select t1.c1, (select t2.c2 from t2 where t1.c1=t2.c2 and rownum < t1.c1) from t1;

        C1 (SELECTT2.C2FROMT2WHERET1.C1=T2.C2ANDROWNUM<T1.C1)
---------- --------------------------------------------------
         1
         2 2
         3 3

One can also use ROWNUM with UPDATE:
UPDATE table SET column = ROWNUM;

oracle rownum reference: https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/ROWNUM-Pseudocolumn.html#GUID-2E40EC12-3FCF-4A4F-B5F2-6BC669021726

Sergei Golubchik made changes - 2020-11-03 14:23

Component/s

Virtual Columns [ 10803 ]

Michael Widenius made changes - 2020-11-03 16:43

Description

Oracle ROWNUM is a pseudocolumn (not a real column) that is generated
when running a select.

ROWNUM will be assigned the numbers 1, 2, 3, 4, ... N.

For each row combination returned by a query, ROWNUM returns a number indicating the order in which Oracle selects the rows from the table (tables)

The first row combination selected has a ROWNUM of 1, the second has 2, and so
on.

When used in a sub query, the subquery will generate a new ROWNUM sequence for
each sub query execution.

The ROWNUM psedou column should be implemented under SQL_MODE=ORACLE.

In many cases one could also replace WHERE ROWNUM <= X with LIMIT X.

oracle rownum reference: https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/ROWNUM-Pseudocolumn.html#GUID-2E40EC12-3FCF-4A4F-B5F2-6BC669021726

The most common usage of rownum is to limit the number of rows in the query:
SELECT * from table where rownum < 10;

Examples:
create table t1 (a int primary key);
create table t2 (b int primary key);
insert into t1 values(1),(2),(3);
insert into t2 values(1),(2),(3);

select t1.a, (select t2.b from t2 where t1.a=t2.b and rownum < t1.a) from t1;

The following query is undefined (can return any rows) as rownum is generated before ORDER BY is done and there is no guarantee in which order the database is returning rows.

Select * from t1 order by t1.a desc having rownum <= 2;

create table t1(c1 number, c2 number);
create table t2(c1 number, c2 number);
create table t3(c1 number, c2 number);

insert into t1 values(1,1);
insert into t1 values(2,2);
insert into t1 values(3,3);

insert into t2 values(1,1);
insert into t2 values(2,2);
insert into t2 values(3,3);

insert into t3 values(1,1);
insert into t3 values(2,2);
insert into t3 values(3,3);

SQL> select t1.c1, t1.c2 ,rownum from t1 left join t2 on t1.c1=t2.c1 inner join t3 on t2.c1=t3.c1;

        C1 C2 ROWNUM
---------- ---------- ----------
         1 1 1
         2 2 2
         3 3 3

SQL> select * from ( select t1.* , rownum row_id from t1 where rownum <= 5 ) where row_id > 2;

        C1 C2 ROW_ID
---------- ---------- ----------
         3 3 3

SQL> select t1.c1, (select t2.c2 from t2 where t1.c1=t2.c2 and rownum < t1.c1) from t1;

        C1 (SELECTT2.C2FROMT2WHERET1.C1=T2.C2ANDROWNUM<T1.C1)
---------- --------------------------------------------------
         1
         2 2
         3 3

One can also use ROWNUM with UPDATE:
UPDATE table SET column = ROWNUM;

oracle rownum reference: https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/ROWNUM-Pseudocolumn.html#GUID-2E40EC12-3FCF-4A4F-B5F2-6BC669021726

Oracle ROWNUM is a pseudocolumn (not a real column) that is generated
when running a select.

ROWNUM will be assigned the numbers 1, 2, 3, 4, ... N.

For each row combination returned by a query, ROWNUM returns a number indicating the order in which Oracle selects the rows from the table (tables)

The first row combination selected has a ROWNUM of 1, the second has 2, and so
on.

When used in a sub query, the subquery will generate a new ROWNUM sequence for
each sub query execution.

The ROWNUM psedou column should be implemented under SQL_MODE=ORACLE.

In many cases one could also replace WHERE ROWNUM <= X with LIMIT X.

oracle rownum reference: https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/ROWNUM-Pseudocolumn.html#GUID-2E40EC12-3FCF-4A4F-B5F2-6BC669021726

The most common usage of rownum is to limit the number of rows in the query:
SELECT * from table where rownum < 10;

Examples:
create table t1 (a int primary key);
create table t2 (b int primary key);
insert into t1 values(1),(2),(3);
insert into t2 values(1),(2),(3);

select t1.a, (select t2.b from t2 where t1.a=t2.b and rownum < t1.a) from t1;

The following query is undefined (can return any rows) as rownum is generated before ORDER BY is done and there is no guarantee in which order the database is returning rows.

Select * from t1 order by t1.a desc having rownum <= 2;

create table t1(c1 number, c2 number);
create table t2(c1 number, c2 number);
create table t3(c1 number, c2 number);

insert into t1 values(1,1);
insert into t1 values(2,2);
insert into t1 values(3,3);

insert into t2 values(1,1);
insert into t2 values(2,2);
insert into t2 values(3,3);

insert into t3 values(1,1);
insert into t3 values(2,2);
insert into t3 values(3,3);

SQL> select t1.c1, t1.c2 ,rownum from t1 left join t2 on t1.c1=t2.c1 inner join t3 on t2.c1=t3.c1;

        C1 C2 ROWNUM
---------- ---------- ----------
         1 1 1
         2 2 2
         3 3 3

SQL> select * from ( select t1.* , rownum row_id from t1 where rownum <= 5 ) where row_id > 2;

        C1 C2 ROW_ID
---------- ---------- ----------
         3 3 3

SQL> select t1.c1, (select t2.c2 from t2 where t1.c1=t2.c2 and rownum < t1.c1) from t1;

        C1 (SELECTT2.C2FROMT2WHERET1.C1=T2.C2ANDROWNUM<T1.C1)
---------- --------------------------------------------------
         1
         2 2
         3 3

One can also use ROWNUM with UPDATE:
UPDATE table SET column = ROWNUM;

In case of group by queries, the rownum will work on the originally found rows, not the result rows. Here it works different from limit that works on the end result rows:
SELECT sum(a) FROM t1 WHERE rownum<=2;

oracle rownum reference: https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/ROWNUM-Pseudocolumn.html#GUID-2E40EC12-3FCF-4A4F-B5F2-6BC669021726

Michael Widenius made changes - 2020-11-03 16:47

Description

Oracle ROWNUM is a pseudocolumn (not a real column) that is generated
when running a select.

ROWNUM will be assigned the numbers 1, 2, 3, 4, ... N.

For each row combination returned by a query, ROWNUM returns a number indicating the order in which Oracle selects the rows from the table (tables)

The first row combination selected has a ROWNUM of 1, the second has 2, and so
on.

When used in a sub query, the subquery will generate a new ROWNUM sequence for
each sub query execution.

The ROWNUM psedou column should be implemented under SQL_MODE=ORACLE.

In many cases one could also replace WHERE ROWNUM <= X with LIMIT X.

oracle rownum reference: https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/ROWNUM-Pseudocolumn.html#GUID-2E40EC12-3FCF-4A4F-B5F2-6BC669021726

The most common usage of rownum is to limit the number of rows in the query:
SELECT * from table where rownum < 10;

Examples:
create table t1 (a int primary key);
create table t2 (b int primary key);
insert into t1 values(1),(2),(3);
insert into t2 values(1),(2),(3);

select t1.a, (select t2.b from t2 where t1.a=t2.b and rownum < t1.a) from t1;

The following query is undefined (can return any rows) as rownum is generated before ORDER BY is done and there is no guarantee in which order the database is returning rows.

Select * from t1 order by t1.a desc having rownum <= 2;

create table t1(c1 number, c2 number);
create table t2(c1 number, c2 number);
create table t3(c1 number, c2 number);

insert into t1 values(1,1);
insert into t1 values(2,2);
insert into t1 values(3,3);

insert into t2 values(1,1);
insert into t2 values(2,2);
insert into t2 values(3,3);

insert into t3 values(1,1);
insert into t3 values(2,2);
insert into t3 values(3,3);

SQL> select t1.c1, t1.c2 ,rownum from t1 left join t2 on t1.c1=t2.c1 inner join t3 on t2.c1=t3.c1;

        C1 C2 ROWNUM
---------- ---------- ----------
         1 1 1
         2 2 2
         3 3 3

SQL> select * from ( select t1.* , rownum row_id from t1 where rownum <= 5 ) where row_id > 2;

        C1 C2 ROW_ID
---------- ---------- ----------
         3 3 3

SQL> select t1.c1, (select t2.c2 from t2 where t1.c1=t2.c2 and rownum < t1.c1) from t1;

        C1 (SELECTT2.C2FROMT2WHERET1.C1=T2.C2ANDROWNUM<T1.C1)
---------- --------------------------------------------------
         1
         2 2
         3 3

One can also use ROWNUM with UPDATE:
UPDATE table SET column = ROWNUM;

In case of group by queries, the rownum will work on the originally found rows, not the result rows. Here it works different from limit that works on the end result rows:
SELECT sum(a) FROM t1 WHERE rownum<=2;

oracle rownum reference: https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/ROWNUM-Pseudocolumn.html#GUID-2E40EC12-3FCF-4A4F-B5F2-6BC669021726

Oracle ROWNUM is a pseudocolumn (not a real column) that is generated
when running a select.

ROWNUM will be assigned the numbers 1, 2, 3, 4, ... N.

For each row combination returned by a query, ROWNUM returns a number indicating the order in which Oracle selects the rows from the table (tables)

The first row combination selected has a ROWNUM of 1, the second has 2, and so
on.

When used in a sub query, the subquery will generate a new ROWNUM sequence for
each sub query execution.

The ROWNUM psedou column should be implemented under SQL_MODE=ORACLE.

In many cases one could also replace WHERE ROWNUM <= X with LIMIT X.

The most common usage of rownum is to limit the number of rows in the query:
SELECT * from table where rownum < 10;

Examples:
create table t1 (a int primary key);
create table t2 (b int primary key);
insert into t1 values(1),(2),(3);
insert into t2 values(1),(2),(3);

select t1.a, (select t2.b from t2 where t1.a=t2.b and rownum < t1.a) from t1;

The following query is undefined (can return any rows) as rownum is generated before ORDER BY is done and there is no guarantee in which order the database is returning rows.

Select * from t1 order by t1.a desc having rownum <= 2;

create table t1(c1 number, c2 number);
create table t2(c1 number, c2 number);
create table t3(c1 number, c2 number);

insert into t1 values(1,1);
insert into t1 values(2,2);
insert into t1 values(3,3);

insert into t2 values(1,1);
insert into t2 values(2,2);
insert into t2 values(3,3);

insert into t3 values(1,1);
insert into t3 values(2,2);
insert into t3 values(3,3);

SQL> select t1.c1, t1.c2 ,rownum from t1 left join t2 on t1.c1=t2.c1 inner join t3 on t2.c1=t3.c1;

        C1 C2 ROWNUM
---------- ---------- ----------
         1 1 1
         2 2 2
         3 3 3

SQL> select * from ( select t1.* , rownum row_id from t1 where rownum <= 5 ) where row_id > 2;

        C1 C2 ROW_ID
---------- ---------- ----------
         3 3 3

SQL> select t1.c1, (select t2.c2 from t2 where t1.c1=t2.c2 and rownum < t1.c1) from t1;

        C1 (SELECTT2.C2FROMT2WHERET1.C1=T2.C2ANDROWNUM<T1.C1)
---------- --------------------------------------------------
         1
         2 2
         3 3

One can also use ROWNUM with UPDATE:
UPDATE table SET column = ROWNUM;

In case of group by queries, the rownum will work on the originally found rows, not the result rows like LIMIT
SELECT sum(c1) FROM t1 WHERE rownum<=2;
This will return 3 (or at least not 6)

oracle rownum reference: https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/ROWNUM-Pseudocolumn.html#GUID-2E40EC12-3FCF-4A4F-B5F2-6BC669021726

Michael Widenius made changes - 2020-11-03 16:51

Description

Oracle ROWNUM is a pseudocolumn (not a real column) that is generated
when running a select.

ROWNUM will be assigned the numbers 1, 2, 3, 4, ... N.

For each row combination returned by a query, ROWNUM returns a number indicating the order in which Oracle selects the rows from the table (tables)

The first row combination selected has a ROWNUM of 1, the second has 2, and so
on.

When used in a sub query, the subquery will generate a new ROWNUM sequence for
each sub query execution.

The ROWNUM psedou column should be implemented under SQL_MODE=ORACLE.

In many cases one could also replace WHERE ROWNUM <= X with LIMIT X.

The most common usage of rownum is to limit the number of rows in the query:
SELECT * from table where rownum < 10;

Examples:
create table t1 (a int primary key);
create table t2 (b int primary key);
insert into t1 values(1),(2),(3);
insert into t2 values(1),(2),(3);

select t1.a, (select t2.b from t2 where t1.a=t2.b and rownum < t1.a) from t1;

The following query is undefined (can return any rows) as rownum is generated before ORDER BY is done and there is no guarantee in which order the database is returning rows.

Select * from t1 order by t1.a desc having rownum <= 2;

create table t1(c1 number, c2 number);
create table t2(c1 number, c2 number);
create table t3(c1 number, c2 number);

insert into t1 values(1,1);
insert into t1 values(2,2);
insert into t1 values(3,3);

insert into t2 values(1,1);
insert into t2 values(2,2);
insert into t2 values(3,3);

insert into t3 values(1,1);
insert into t3 values(2,2);
insert into t3 values(3,3);

SQL> select t1.c1, t1.c2 ,rownum from t1 left join t2 on t1.c1=t2.c1 inner join t3 on t2.c1=t3.c1;

        C1 C2 ROWNUM
---------- ---------- ----------
         1 1 1
         2 2 2
         3 3 3

SQL> select * from ( select t1.* , rownum row_id from t1 where rownum <= 5 ) where row_id > 2;

        C1 C2 ROW_ID
---------- ---------- ----------
         3 3 3

SQL> select t1.c1, (select t2.c2 from t2 where t1.c1=t2.c2 and rownum < t1.c1) from t1;

        C1 (SELECTT2.C2FROMT2WHERET1.C1=T2.C2ANDROWNUM<T1.C1)
---------- --------------------------------------------------
         1
         2 2
         3 3

One can also use ROWNUM with UPDATE:
UPDATE table SET column = ROWNUM;

In case of group by queries, the rownum will work on the originally found rows, not the result rows like LIMIT
SELECT sum(c1) FROM t1 WHERE rownum<=2;
This will return 3 (or at least not 6)

oracle rownum reference: https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/ROWNUM-Pseudocolumn.html#GUID-2E40EC12-3FCF-4A4F-B5F2-6BC669021726

Oracle ROWNUM is a pseudocolumn (not a real column) that is generated
when running a select.

ROWNUM will be assigned the numbers 1, 2, 3, 4, ... N.

For each row combination returned by a query, ROWNUM returns a number indicating the order in which Oracle selects the rows from the table (tables)

The first row combination selected has a ROWNUM of 1, the second has 2, and so
on.

When used in a sub query, the subquery will generate a new ROWNUM sequence for
each sub query execution.

The ROWNUM psedou column should be implemented under SQL_MODE=ORACLE.

In many cases one could also replace WHERE ROWNUM <= X with LIMIT X.

The most common usage of rownum is to limit the number of rows in the query:
SELECT * from table where rownum < 10;

Examples:
create table t1 (a int primary key);
create table t2 (b int primary key);
insert into t1 values(1),(2),(3);
insert into t2 values(1),(2),(3);

select t1.a, (select t2.b from t2 where t1.a=t2.b and rownum < t1.a) from t1;

The following query is undefined (can return any rows) as rownum is generated before ORDER BY is done and there is no guarantee in which order the database is returning rows.

Select * from t1 order by t1.a desc having rownum <= 2;

create table t1(c1 number, c2 number);
create table t2(c1 number, c2 number);
create table t3(c1 number, c2 number);

insert into t1 values(1,1);
insert into t1 values(2,2);
insert into t1 values(3,3);

insert into t2 values(1,1);
insert into t2 values(2,2);
insert into t2 values(3,3);

insert into t3 values(1,1);
insert into t3 values(2,2);
insert into t3 values(3,3);

SQL> select t1.c1, t1.c2 ,rownum from t1 left join t2 on t1.c1=t2.c1 inner join t3 on t2.c1=t3.c1;

        C1 C2 ROWNUM
---------- ---------- ----------
         1 1 1
         2 2 2
         3 3 3

SQL> select * from ( select t1.* , rownum row_id from t1 where rownum <= 5 ) where row_id > 2;

        C1 C2 ROW_ID
---------- ---------- ----------
         3 3 3

SQL> select t1.c1, (select t2.c2 from t2 where t1.c1=t2.c2 and rownum < t1.c1) from t1;

        C1 (SELECTT2.C2FROMT2WHERET1.C1=T2.C2ANDROWNUM<T1.C1)
---------- --------------------------------------------------
         1
         2 2
         3 3

One can also use ROWNUM with UPDATE:
UPDATE table SET column = ROWNUM;

In case of group by queries, the rownum will work on the originally found rows, not the result rows like LIMIT
SELECT sum(c1) FROM t1 WHERE rownum<=2;
This will return 3 (or at least not 6)

ROWNUM cannot (or should not) be used in these cases as the result set is not determinstic:
- In HAVING
- In ON expressions
- With GROUP BY
- SELECT without an ORDER BY (This is a common case how rownum is used and in this case we can replace it with LIMIT)

oracle rownum reference: https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/ROWNUM-Pseudocolumn.html#GUID-2E40EC12-3FCF-4A4F-B5F2-6BC669021726

Michael Widenius made changes - 2020-11-03 16:57

Description

Oracle ROWNUM is a pseudocolumn (not a real column) that is generated
when running a select.

ROWNUM will be assigned the numbers 1, 2, 3, 4, ... N.

For each row combination returned by a query, ROWNUM returns a number indicating the order in which Oracle selects the rows from the table (tables)

The first row combination selected has a ROWNUM of 1, the second has 2, and so
on.

When used in a sub query, the subquery will generate a new ROWNUM sequence for
each sub query execution.

The ROWNUM psedou column should be implemented under SQL_MODE=ORACLE.

In many cases one could also replace WHERE ROWNUM <= X with LIMIT X.

The most common usage of rownum is to limit the number of rows in the query:
SELECT * from table where rownum < 10;

Examples:
create table t1 (a int primary key);
create table t2 (b int primary key);
insert into t1 values(1),(2),(3);
insert into t2 values(1),(2),(3);

select t1.a, (select t2.b from t2 where t1.a=t2.b and rownum < t1.a) from t1;

The following query is undefined (can return any rows) as rownum is generated before ORDER BY is done and there is no guarantee in which order the database is returning rows.

Select * from t1 order by t1.a desc having rownum <= 2;

create table t1(c1 number, c2 number);
create table t2(c1 number, c2 number);
create table t3(c1 number, c2 number);

insert into t1 values(1,1);
insert into t1 values(2,2);
insert into t1 values(3,3);

insert into t2 values(1,1);
insert into t2 values(2,2);
insert into t2 values(3,3);

insert into t3 values(1,1);
insert into t3 values(2,2);
insert into t3 values(3,3);

SQL> select t1.c1, t1.c2 ,rownum from t1 left join t2 on t1.c1=t2.c1 inner join t3 on t2.c1=t3.c1;

        C1 C2 ROWNUM
---------- ---------- ----------
         1 1 1
         2 2 2
         3 3 3

SQL> select * from ( select t1.* , rownum row_id from t1 where rownum <= 5 ) where row_id > 2;

        C1 C2 ROW_ID
---------- ---------- ----------
         3 3 3

SQL> select t1.c1, (select t2.c2 from t2 where t1.c1=t2.c2 and rownum < t1.c1) from t1;

        C1 (SELECTT2.C2FROMT2WHERET1.C1=T2.C2ANDROWNUM<T1.C1)
---------- --------------------------------------------------
         1
         2 2
         3 3

One can also use ROWNUM with UPDATE:
UPDATE table SET column = ROWNUM;

In case of group by queries, the rownum will work on the originally found rows, not the result rows like LIMIT
SELECT sum(c1) FROM t1 WHERE rownum<=2;
This will return 3 (or at least not 6)

ROWNUM cannot (or should not) be used in these cases as the result set is not determinstic:
- In HAVING
- In ON expressions
- With GROUP BY
- SELECT without an ORDER BY (This is a common case how rownum is used and in this case we can replace it with LIMIT)

oracle rownum reference: https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/ROWNUM-Pseudocolumn.html#GUID-2E40EC12-3FCF-4A4F-B5F2-6BC669021726

Oracle ROWNUM is a pseudocolumn (not a real column) that is generated
when running a select.

ROWNUM will be assigned the numbers 1, 2, 3, 4, ... N.

For each row combination returned by a query, ROWNUM returns a number indicating the order in which Oracle selects the rows from the table (tables)

The first row combination selected has a ROWNUM of 1, the second has 2, and so
on.

When used in a sub query, the subquery will generate a new ROWNUM sequence for
each sub query execution.

The ROWNUM psedou column should be implemented under SQL_MODE=ORACLE.

In many cases one could also replace WHERE ROWNUM <= X with LIMIT X.

The most common usage of rownum is to limit the number of rows in the query:
SELECT * from table where rownum < 10;

Examples:
create table t1 (a int primary key);
create table t2 (b int primary key);
insert into t1 values(1),(2),(3);
insert into t2 values(1),(2),(3);

select t1.a, (select t2.b from t2 where t1.a=t2.b and rownum < t1.a) from t1;

The following query is undefined (can return any rows) as rownum is generated before ORDER BY is done and there is no guarantee in which order the database is returning rows.

Select * from t1 order by t1.a desc having rownum <= 2;

create table t1(c1 number, c2 number);
create table t2(c1 number, c2 number);
create table t3(c1 number, c2 number);

insert into t1 values(1,1);
insert into t1 values(2,2);
insert into t1 values(3,3);

insert into t2 values(1,1);
insert into t2 values(2,2);
insert into t2 values(3,3);

insert into t3 values(1,1);
insert into t3 values(2,2);
insert into t3 values(3,3);

SQL> select * from t1 where rownum<2;
C1 C2
1 1

SQL> select t1.c1, t1.c2 ,rownum from t1 left join t2 on t1.c1=t2.c1 inner join t3 on t2.c1=t3.c1;

        C1 C2 ROWNUM
---------- ---------- ----------
         1 1 1
         2 2 2
         3 3 3

SQL> select * from ( select t1.* , rownum row_id from t1 where rownum <= 5 ) where row_id > 2;

        C1 C2 ROW_ID
---------- ---------- ----------
         3 3 3

SQL> select t1.c1, (select t2.c2 from t2 where t1.c1=t2.c2 and rownum < t1.c1) from t1;

        C1 (SELECTT2.C2FROMT2WHERET1.C1=T2.C2ANDROWNUM<T1.C1)
---------- --------------------------------------------------
         1
         2 2
         3 3

One can also use ROWNUM with UPDATE:
UPDATE table SET column = ROWNUM;

In case of group by queries, the rownum will work on the originally found rows, not the result rows like LIMIT
SELECT sum(c1) FROM t1 WHERE rownum<=2;
This will return 3 (or at least not 6)

ROWNUM cannot (or should not) be used in these cases as the result set is not determinstic:
- In HAVING
- In ON expressions
- With GROUP BY
- SELECT without an ORDER BY (This is a common case how rownum is used and in this case we can replace it with LIMIT)

oracle rownum reference: https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/ROWNUM-Pseudocolumn.html#GUID-2E40EC12-3FCF-4A4F-B5F2-6BC669021726

Michael Widenius made changes - 2020-11-05 11:36

Description

Oracle ROWNUM is a pseudocolumn (not a real column) that is generated
when running a select.

ROWNUM will be assigned the numbers 1, 2, 3, 4, ... N.

For each row combination returned by a query, ROWNUM returns a number indicating the order in which Oracle selects the rows from the table (tables)

The first row combination selected has a ROWNUM of 1, the second has 2, and so
on.

When used in a sub query, the subquery will generate a new ROWNUM sequence for
each sub query execution.

The ROWNUM psedou column should be implemented under SQL_MODE=ORACLE.

In many cases one could also replace WHERE ROWNUM <= X with LIMIT X.

The most common usage of rownum is to limit the number of rows in the query:
SELECT * from table where rownum < 10;

Examples:
create table t1 (a int primary key);
create table t2 (b int primary key);
insert into t1 values(1),(2),(3);
insert into t2 values(1),(2),(3);

select t1.a, (select t2.b from t2 where t1.a=t2.b and rownum < t1.a) from t1;

The following query is undefined (can return any rows) as rownum is generated before ORDER BY is done and there is no guarantee in which order the database is returning rows.

Select * from t1 order by t1.a desc having rownum <= 2;

create table t1(c1 number, c2 number);
create table t2(c1 number, c2 number);
create table t3(c1 number, c2 number);

insert into t1 values(1,1);
insert into t1 values(2,2);
insert into t1 values(3,3);

insert into t2 values(1,1);
insert into t2 values(2,2);
insert into t2 values(3,3);

insert into t3 values(1,1);
insert into t3 values(2,2);
insert into t3 values(3,3);

SQL> select * from t1 where rownum<2;
C1 C2
1 1

SQL> select t1.c1, t1.c2 ,rownum from t1 left join t2 on t1.c1=t2.c1 inner join t3 on t2.c1=t3.c1;

        C1 C2 ROWNUM
---------- ---------- ----------
         1 1 1
         2 2 2
         3 3 3

SQL> select * from ( select t1.* , rownum row_id from t1 where rownum <= 5 ) where row_id > 2;

        C1 C2 ROW_ID
---------- ---------- ----------
         3 3 3

SQL> select t1.c1, (select t2.c2 from t2 where t1.c1=t2.c2 and rownum < t1.c1) from t1;

        C1 (SELECTT2.C2FROMT2WHERET1.C1=T2.C2ANDROWNUM<T1.C1)
---------- --------------------------------------------------
         1
         2 2
         3 3

One can also use ROWNUM with UPDATE:
UPDATE table SET column = ROWNUM;

In case of group by queries, the rownum will work on the originally found rows, not the result rows like LIMIT
SELECT sum(c1) FROM t1 WHERE rownum<=2;
This will return 3 (or at least not 6)

ROWNUM cannot (or should not) be used in these cases as the result set is not determinstic:
- In HAVING
- In ON expressions
- With GROUP BY
- SELECT without an ORDER BY (This is a common case how rownum is used and in this case we can replace it with LIMIT)

oracle rownum reference: https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/ROWNUM-Pseudocolumn.html#GUID-2E40EC12-3FCF-4A4F-B5F2-6BC669021726

Oracle ROWNUM is a pseudocolumn (not a real column) that is generated
when running a select.

ROWNUM will be assigned the numbers 1, 2, 3, 4, ... N.

For each row combination returned by a query, ROWNUM returns a number indicating the order in which Oracle selects the rows from the table (tables)

The first row combination selected has a ROWNUM of 1, the second has 2, and so
on.

When used in a sub query, the subquery will generate a new ROWNUM sequence for
each sub query execution.

The ROWNUM psedou column should be implemented under SQL_MODE=ORACLE.

In many cases one could also replace WHERE ROWNUM <= X with LIMIT X.

The most common usage of rownum is to limit the number of rows in the query:
SELECT * from table where rownum < 10;

Examples:
create table t1 (a int primary key);
create table t2 (b int primary key);
insert into t1 values(1),(2),(3);
insert into t2 values(1),(2),(3);

select t1.a, (select t2.b from t2 where t1.a=t2.b and rownum < t1.a) from t1;

The following query is undefined (can return any rows) as rownum is generated before ORDER BY is done and there is no guarantee in which order the database is returning rows.

Select * from t1 order by t1.a desc having rownum <= 2;

create table t1(c1 number, c2 number);
create table t2(c1 number, c2 number);
create table t3(c1 number, c2 number);

insert into t1 values(1,1);
insert into t1 values(2,2);
insert into t1 values(3,3);

insert into t2 values(1,1);
insert into t2 values(2,2);
insert into t2 values(3,3);

insert into t3 values(1,1);
insert into t3 values(2,2);
insert into t3 values(3,3);

SQL> select * from t1 where rownum<2;
C1 C2
1 1

SQL> select t1.c1, t1.c2 ,rownum from t1 left join t2 on t1.c1=t2.c1 inner join t3 on t2.c1=t3.c1;

        C1 C2 ROWNUM
---------- ---------- ----------
         1 1 1
         2 2 2
         3 3 3

SQL> select * from ( select t1.* , rownum row_id from t1 where rownum <= 5 ) where row_id > 2;

        C1 C2 ROW_ID
---------- ---------- ----------
         3 3 3

SQL> select t1.c1, (select t2.c2 from t2 where t1.c1=t2.c2 and rownum < t1.c1) from t1;

        C1 (SELECTT2.C2FROMT2WHERET1.C1=T2.C2ANDROWNUM<T1.C1)
---------- --------------------------------------------------
         1
         2 2
         3 3

One can also use ROWNUM with UPDATE:
UPDATE table SET column = ROWNUM;

In case of group by queries, the rownum will work on the originally found rows, not the result rows like LIMIT
SELECT sum(c1) FROM t1 WHERE rownum<=2;
This will return 3 (or at least not 6)

ROWNUM cannot (or should not) be used in these cases as the result set is not deterministic:
- In HAVING
- In ON expressions
- With GROUP BY
- SELECT without an ORDER BY (This is a common case how rownum is used and in this case we can replace it with LIMIT)

oracle rownum reference: https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/ROWNUM-Pseudocolumn.html#GUID-2E40EC12-3FCF-4A4F-B5F2-6BC669021726

Architecture:
Add function ROWNUM() (Item_rownum()).
- For not GROUP BY, map the result of ROWNUM() to 'last_join->send_records'
- If GROUP BY, we have to use a new variable 'last_join->approved_records'
  that is incremented in end_send_group(), end_update() and end_unique_update()

In oracle mode, if field with name 'rownum' is used and there is no match in
any of the tables, map the field to Item_rownum()

Things to consider and test in addition to the test in the suggested patch:

- Limit optimization
  - If we have ROWNUM < # or ROWNUM <= # on the top level of the WHERE clause
    this should be converted to LIMIT. This is to be done in the
    JOIN::prepare phase(), probably after optimize_cond() which does
    propagate_cond_constants()
- When using sum functions, it's not sent rows, it's examined rows we have to
  limit against
- Filesort, when done over single table needs it's own handling
  (as there are no 'send_records' here). Limit is done according to found rows,
  not sorted order.
- Ensure that ROWNUM also works with UPDATE and DELETE
- UNION
  - ROWNUM should work independently for each UNION

Michael Widenius made changes - 2020-11-06 08:48

Assignee

Michael Widenius [ monty ]

Michael Widenius made changes - 2020-11-09 12:09

Fix Version/s

10.6 [ 24028 ]

Michael Widenius made changes - 2020-11-09 12:09

Status

Open [ 1 ]

In Progress [ 3 ]

Sergei Golubchik made changes - 2020-11-10 13:12

Description

Oracle ROWNUM is a pseudocolumn (not a real column) that is generated
when running a select.

ROWNUM will be assigned the numbers 1, 2, 3, 4, ... N.

For each row combination returned by a query, ROWNUM returns a number indicating the order in which Oracle selects the rows from the table (tables)

The first row combination selected has a ROWNUM of 1, the second has 2, and so
on.

When used in a sub query, the subquery will generate a new ROWNUM sequence for
each sub query execution.

The ROWNUM psedou column should be implemented under SQL_MODE=ORACLE.

In many cases one could also replace WHERE ROWNUM <= X with LIMIT X.

The most common usage of rownum is to limit the number of rows in the query:
SELECT * from table where rownum < 10;

Examples:
create table t1 (a int primary key);
create table t2 (b int primary key);
insert into t1 values(1),(2),(3);
insert into t2 values(1),(2),(3);

select t1.a, (select t2.b from t2 where t1.a=t2.b and rownum < t1.a) from t1;

The following query is undefined (can return any rows) as rownum is generated before ORDER BY is done and there is no guarantee in which order the database is returning rows.

Select * from t1 order by t1.a desc having rownum <= 2;

create table t1(c1 number, c2 number);
create table t2(c1 number, c2 number);
create table t3(c1 number, c2 number);

insert into t1 values(1,1);
insert into t1 values(2,2);
insert into t1 values(3,3);

insert into t2 values(1,1);
insert into t2 values(2,2);
insert into t2 values(3,3);

insert into t3 values(1,1);
insert into t3 values(2,2);
insert into t3 values(3,3);

SQL> select * from t1 where rownum<2;
C1 C2
1 1

SQL> select t1.c1, t1.c2 ,rownum from t1 left join t2 on t1.c1=t2.c1 inner join t3 on t2.c1=t3.c1;

        C1 C2 ROWNUM
---------- ---------- ----------
         1 1 1
         2 2 2
         3 3 3

SQL> select * from ( select t1.* , rownum row_id from t1 where rownum <= 5 ) where row_id > 2;

        C1 C2 ROW_ID
---------- ---------- ----------
         3 3 3

SQL> select t1.c1, (select t2.c2 from t2 where t1.c1=t2.c2 and rownum < t1.c1) from t1;

        C1 (SELECTT2.C2FROMT2WHERET1.C1=T2.C2ANDROWNUM<T1.C1)
---------- --------------------------------------------------
         1
         2 2
         3 3

One can also use ROWNUM with UPDATE:
UPDATE table SET column = ROWNUM;

In case of group by queries, the rownum will work on the originally found rows, not the result rows like LIMIT
SELECT sum(c1) FROM t1 WHERE rownum<=2;
This will return 3 (or at least not 6)

ROWNUM cannot (or should not) be used in these cases as the result set is not deterministic:
- In HAVING
- In ON expressions
- With GROUP BY
- SELECT without an ORDER BY (This is a common case how rownum is used and in this case we can replace it with LIMIT)

oracle rownum reference: https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/ROWNUM-Pseudocolumn.html#GUID-2E40EC12-3FCF-4A4F-B5F2-6BC669021726

Architecture:
Add function ROWNUM() (Item_rownum()).
- For not GROUP BY, map the result of ROWNUM() to 'last_join->send_records'
- If GROUP BY, we have to use a new variable 'last_join->approved_records'
  that is incremented in end_send_group(), end_update() and end_unique_update()

In oracle mode, if field with name 'rownum' is used and there is no match in
any of the tables, map the field to Item_rownum()

Things to consider and test in addition to the test in the suggested patch:

- Limit optimization
  - If we have ROWNUM < # or ROWNUM <= # on the top level of the WHERE clause
    this should be converted to LIMIT. This is to be done in the
    JOIN::prepare phase(), probably after optimize_cond() which does
    propagate_cond_constants()
- When using sum functions, it's not sent rows, it's examined rows we have to
  limit against
- Filesort, when done over single table needs it's own handling
  (as there are no 'send_records' here). Limit is done according to found rows,
  not sorted order.
- Ensure that ROWNUM also works with UPDATE and DELETE
- UNION
  - ROWNUM should work independently for each UNION

Oracle ROWNUM is a pseudocolumn (not a real column) that is generated
when running a select.

ROWNUM will be assigned the numbers 1, 2, 3, 4, ... N.

For each row combination returned by a query, ROWNUM returns a number indicating the order in which Oracle selects the rows from the table (tables)

The first row combination selected has a ROWNUM of 1, the second has 2, and so
on.

When used in a sub query, the subquery will generate a new ROWNUM sequence for
each sub query execution.

The ROWNUM psedou column should be implemented under SQL_MODE=ORACLE.

In many cases one could also replace WHERE ROWNUM <= X with LIMIT X.

The most common usage of rownum is to limit the number of rows in the query:
SELECT * from table where rownum < 10;

Examples:
{code:sql}
create table t1 (a int primary key);
create table t2 (b int primary key);
insert into t1 values(1),(2),(3);
insert into t2 values(1),(2),(3);

select t1.a, (select t2.b from t2 where t1.a=t2.b and rownum < t1.a) from t1;
{code}
The following query is undefined (can return any rows) as rownum is generated before ORDER BY is done and there is no guarantee in which order the database is returning rows.
{code:sql}
Select * from t1 order by t1.a desc having rownum <= 2;

create table t1(c1 number, c2 number);
create table t2(c1 number, c2 number);
create table t3(c1 number, c2 number);

insert into t1 values(1,1);
insert into t1 values(2,2);
insert into t1 values(3,3);

insert into t2 values(1,1);
insert into t2 values(2,2);
insert into t2 values(3,3);

insert into t3 values(1,1);
insert into t3 values(2,2);
insert into t3 values(3,3);
{code}
{noformat}
SQL> select * from t1 where rownum<2;
C1 C2
1 1

SQL> select t1.c1, t1.c2 ,rownum from t1 left join t2 on t1.c1=t2.c1 inner join t3 on t2.c1=t3.c1;

        C1 C2 ROWNUM
---------- ---------- ----------
         1 1 1
         2 2 2
         3 3 3

SQL> select * from ( select t1.* , rownum row_id from t1 where rownum <= 5 ) where row_id > 2;

        C1 C2 ROW_ID
---------- ---------- ----------
         3 3 3

SQL> select t1.c1, (select t2.c2 from t2 where t1.c1=t2.c2 and rownum < t1.c1) from t1;

        C1 (SELECTT2.C2FROMT2WHERET1.C1=T2.C2ANDROWNUM<T1.C1)
---------- --------------------------------------------------
         1
         2 2
         3 3
{noformat}
One can also use ROWNUM with UPDATE:
{code:sql}
UPDATE table SET column = ROWNUM;
{code}
In case of group by queries, the rownum will work on the originally found rows, not the result rows like LIMIT
{code:sql}
SELECT sum(c1) FROM t1 WHERE rownum<=2;
{code}
This will return 3 (or at least not 6)

ROWNUM cannot (or should not) be used in these cases as the result set is not deterministic:
- In HAVING
- In ON expressions
- With GROUP BY
- SELECT without an ORDER BY (This is a common case how rownum is used and in this case we can replace it with LIMIT)

oracle rownum reference: https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/ROWNUM-Pseudocolumn.html#GUID-2E40EC12-3FCF-4A4F-B5F2-6BC669021726

Architecture:
Add function ROWNUM() (Item_rownum()).
- For not GROUP BY, map the result of ROWNUM() to 'last_join->send_records'
- If GROUP BY, we have to use a new variable 'last_join->approved_records'
  that is incremented in end_send_group(), end_update() and end_unique_update()

In oracle mode, if field with name 'rownum' is used and there is no match in
any of the tables, map the field to Item_rownum()

Things to consider and test in addition to the test in the suggested patch:

- Limit optimization
  -- If we have ROWNUM < # or ROWNUM <= # on the top level of the WHERE clause
    this should be converted to LIMIT. This is to be done in the
    JOIN::prepare phase(), probably after optimize_cond() which does
    propagate_cond_constants()
- When using sum functions, it's not sent rows, it's examined rows we have to
  limit against
- Filesort, when done over single table needs it's own handling
  (as there are no 'send_records' here). Limit is done according to found rows,
  not sorted order.
- Ensure that ROWNUM also works with UPDATE and DELETE
- UNION
  -- ROWNUM should work independently for each UNION

Michael Widenius made changes - 2020-11-17 11:16

Description

Oracle ROWNUM is a pseudocolumn (not a real column) that is generated
when running a select.

ROWNUM will be assigned the numbers 1, 2, 3, 4, ... N.

For each row combination returned by a query, ROWNUM returns a number indicating the order in which Oracle selects the rows from the table (tables)

The first row combination selected has a ROWNUM of 1, the second has 2, and so
on.

When used in a sub query, the subquery will generate a new ROWNUM sequence for
each sub query execution.

The ROWNUM psedou column should be implemented under SQL_MODE=ORACLE.

In many cases one could also replace WHERE ROWNUM <= X with LIMIT X.

The most common usage of rownum is to limit the number of rows in the query:
SELECT * from table where rownum < 10;

Examples:
{code:sql}
create table t1 (a int primary key);
create table t2 (b int primary key);
insert into t1 values(1),(2),(3);
insert into t2 values(1),(2),(3);

select t1.a, (select t2.b from t2 where t1.a=t2.b and rownum < t1.a) from t1;
{code}
The following query is undefined (can return any rows) as rownum is generated before ORDER BY is done and there is no guarantee in which order the database is returning rows.
{code:sql}
Select * from t1 order by t1.a desc having rownum <= 2;

create table t1(c1 number, c2 number);
create table t2(c1 number, c2 number);
create table t3(c1 number, c2 number);

insert into t1 values(1,1);
insert into t1 values(2,2);
insert into t1 values(3,3);

insert into t2 values(1,1);
insert into t2 values(2,2);
insert into t2 values(3,3);

insert into t3 values(1,1);
insert into t3 values(2,2);
insert into t3 values(3,3);
{code}
{noformat}
SQL> select * from t1 where rownum<2;
C1 C2
1 1

SQL> select t1.c1, t1.c2 ,rownum from t1 left join t2 on t1.c1=t2.c1 inner join t3 on t2.c1=t3.c1;

        C1 C2 ROWNUM
---------- ---------- ----------
         1 1 1
         2 2 2
         3 3 3

SQL> select * from ( select t1.* , rownum row_id from t1 where rownum <= 5 ) where row_id > 2;

        C1 C2 ROW_ID
---------- ---------- ----------
         3 3 3

SQL> select t1.c1, (select t2.c2 from t2 where t1.c1=t2.c2 and rownum < t1.c1) from t1;

        C1 (SELECTT2.C2FROMT2WHERET1.C1=T2.C2ANDROWNUM<T1.C1)
---------- --------------------------------------------------
         1
         2 2
         3 3
{noformat}
One can also use ROWNUM with UPDATE:
{code:sql}
UPDATE table SET column = ROWNUM;
{code}
In case of group by queries, the rownum will work on the originally found rows, not the result rows like LIMIT
{code:sql}
SELECT sum(c1) FROM t1 WHERE rownum<=2;
{code}
This will return 3 (or at least not 6)

ROWNUM cannot (or should not) be used in these cases as the result set is not deterministic:
- In HAVING
- In ON expressions
- With GROUP BY
- SELECT without an ORDER BY (This is a common case how rownum is used and in this case we can replace it with LIMIT)

oracle rownum reference: https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/ROWNUM-Pseudocolumn.html#GUID-2E40EC12-3FCF-4A4F-B5F2-6BC669021726

Architecture:
Add function ROWNUM() (Item_rownum()).
- For not GROUP BY, map the result of ROWNUM() to 'last_join->send_records'
- If GROUP BY, we have to use a new variable 'last_join->approved_records'
  that is incremented in end_send_group(), end_update() and end_unique_update()

In oracle mode, if field with name 'rownum' is used and there is no match in
any of the tables, map the field to Item_rownum()

Things to consider and test in addition to the test in the suggested patch:

- Limit optimization
  -- If we have ROWNUM < # or ROWNUM <= # on the top level of the WHERE clause
    this should be converted to LIMIT. This is to be done in the
    JOIN::prepare phase(), probably after optimize_cond() which does
    propagate_cond_constants()
- When using sum functions, it's not sent rows, it's examined rows we have to
  limit against
- Filesort, when done over single table needs it's own handling
  (as there are no 'send_records' here). Limit is done according to found rows,
  not sorted order.
- Ensure that ROWNUM also works with UPDATE and DELETE
- UNION
  -- ROWNUM should work independently for each UNION

Oracle ROWNUM is a pseudocolumn (not a real column) that is generated
when running a select.

ROWNUM will be assigned the numbers 1, 2, 3, 4, ... N.

For each row combination returned by a query, ROWNUM returns a number indicating the order in which Oracle selects the rows from the table (tables)

The first row combination selected has a ROWNUM of 1, the second has 2, and so
on.

When used in a sub query, the subquery will generate a new ROWNUM sequence for
each sub query execution.

The ROWNUM psedou column should be implemented under SQL_MODE=ORACLE.

In many cases one could also replace WHERE ROWNUM <= X with LIMIT X.

The most common usage of rownum is to limit the number of rows in the query:
SELECT * from table where rownum < 10;

Examples:
{code:sql}
create table t1 (a int primary key);
create table t2 (b int primary key);
insert into t1 values(1),(2),(3);
insert into t2 values(1),(2),(3);

select t1.a, (select t2.b from t2 where t1.a=t2.b and rownum < t1.a) from t1;
{code}
The following query is undefined (can return any rows) as rownum is generated before ORDER BY is done and there is no guarantee in which order the database is returning rows.
{code:sql}
Select * from t1 order by t1.a desc having rownum <= 2;

create table t1(c1 number, c2 number);
create table t2(c1 number, c2 number);
create table t3(c1 number, c2 number);

insert into t1 values(1,1);
insert into t1 values(2,2);
insert into t1 values(3,3);

insert into t2 values(1,1);
insert into t2 values(2,2);
insert into t2 values(3,3);

insert into t3 values(1,1);
insert into t3 values(2,2);
insert into t3 values(3,3);
{code}
{noformat}
SQL> select * from t1 where rownum<2;
C1 C2
1 1

SQL> select t1.c1, t1.c2 ,rownum from t1 left join t2 on t1.c1=t2.c1 inner join t3 on t2.c1=t3.c1;

        C1 C2 ROWNUM
---------- ---------- ----------
         1 1 1
         2 2 2
         3 3 3

SQL> select * from ( select t1.* , rownum row_id from t1 where rownum <= 5 ) where row_id > 2;

        C1 C2 ROW_ID
---------- ---------- ----------
         3 3 3

SQL> select t1.c1, (select t2.c2 from t2 where t1.c1=t2.c2 and rownum < t1.c1) from t1;

        C1 (SELECTT2.C2FROMT2WHERET1.C1=T2.C2ANDROWNUM<T1.C1)
---------- --------------------------------------------------
         1
         2 2
         3 3
{noformat}
One can also use ROWNUM with UPDATE:
{code:sql}
UPDATE table SET column = ROWNUM;
{code}
In case of group by queries, the rownum will work on the originally found rows, not the result rows like LIMIT
{code:sql}
SELECT sum(c1) FROM t1 WHERE rownum<=2;
{code}
This will return 3 (or at least not 6)

ROWNUM cannot be used with HAVING (Oracle has the same restriction)
ROWNUM should not be used in applications with the following constructs as the result will not be deterministic (it's legal to use it, but the rows used in the result set will differ based on engine or used optimizations):
- In ON expressions
- With GROUP BY
- SELECT without an ORDER BY (This is a common case how ROWNUM is used and in this case we can replace it with LIMIT)

oracle rownum reference: https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/ROWNUM-Pseudocolumn.html#GUID-2E40EC12-3FCF-4A4F-B5F2-6BC669021726

Architecture:
Add function ROWNUM() (Item_rownum()).
- Map the result of ROWNUM() to 'last_join->accepted_records', which is incremented for each row added to the result set.
  This variable will be incremented in end_send(), end_send_group(), end_update() and end_unique_update()
- Filesort() needs each own variable that is incremented for each accepted row.

In oracle mode, if field with name 'rownum' is used and there is no match in
any of the tables, map the field to Item_rownum()

Things to consider and test in addition to the test in the suggested patch:

- Limit optimization
  -- If we have ROWNUM < # or ROWNUM <= # on the top level of the WHERE clause
    this should be converted to LIMIT. This is to be done in the
    JOIN::prepare phase(), probably after optimize_cond() which does
    propagate_cond_constants()
- When using sum functions, it's not sent rows, it's examined rows we have to
  limit against
- Filesort, when done over single table needs it's own handling
  (as there are no 'send_records' here). Limit is done according to found rows,
  not sorted order.
- Ensure that ROWNUM also works with UPDATE and DELETE
- UNION
  -- ROWNUM should work independently for each UNION

Michael Widenius added a comment - 2020-11-17 13:15

I will send you the patch to review

Michael Widenius added a comment - 2020-11-17 13:15 I will send you the patch to review

Michael Widenius made changes - 2020-11-17 13:15

Assignee	Michael Widenius [ monty ]	Sergei Golubchik [ serg ]
Status	In Progress [ 3 ]	In Review [ 10002 ]

Sergei Golubchik made changes - 2021-04-05 21:27

Description

Oracle ROWNUM is a pseudocolumn (not a real column) that is generated
when running a select.

ROWNUM will be assigned the numbers 1, 2, 3, 4, ... N.

For each row combination returned by a query, ROWNUM returns a number indicating the order in which Oracle selects the rows from the table (tables)

The first row combination selected has a ROWNUM of 1, the second has 2, and so
on.

When used in a sub query, the subquery will generate a new ROWNUM sequence for
each sub query execution.

The ROWNUM psedou column should be implemented under SQL_MODE=ORACLE.

In many cases one could also replace WHERE ROWNUM <= X with LIMIT X.

The most common usage of rownum is to limit the number of rows in the query:
SELECT * from table where rownum < 10;

Examples:
{code:sql}
create table t1 (a int primary key);
create table t2 (b int primary key);
insert into t1 values(1),(2),(3);
insert into t2 values(1),(2),(3);

select t1.a, (select t2.b from t2 where t1.a=t2.b and rownum < t1.a) from t1;
{code}
The following query is undefined (can return any rows) as rownum is generated before ORDER BY is done and there is no guarantee in which order the database is returning rows.
{code:sql}
Select * from t1 order by t1.a desc having rownum <= 2;

create table t1(c1 number, c2 number);
create table t2(c1 number, c2 number);
create table t3(c1 number, c2 number);

insert into t1 values(1,1);
insert into t1 values(2,2);
insert into t1 values(3,3);

insert into t2 values(1,1);
insert into t2 values(2,2);
insert into t2 values(3,3);

insert into t3 values(1,1);
insert into t3 values(2,2);
insert into t3 values(3,3);
{code}
{noformat}
SQL> select * from t1 where rownum<2;
C1 C2
1 1

SQL> select t1.c1, t1.c2 ,rownum from t1 left join t2 on t1.c1=t2.c1 inner join t3 on t2.c1=t3.c1;

        C1 C2 ROWNUM
---------- ---------- ----------
         1 1 1
         2 2 2
         3 3 3

SQL> select * from ( select t1.* , rownum row_id from t1 where rownum <= 5 ) where row_id > 2;

        C1 C2 ROW_ID
---------- ---------- ----------
         3 3 3

SQL> select t1.c1, (select t2.c2 from t2 where t1.c1=t2.c2 and rownum < t1.c1) from t1;

        C1 (SELECTT2.C2FROMT2WHERET1.C1=T2.C2ANDROWNUM<T1.C1)
---------- --------------------------------------------------
         1
         2 2
         3 3
{noformat}
One can also use ROWNUM with UPDATE:
{code:sql}
UPDATE table SET column = ROWNUM;
{code}
In case of group by queries, the rownum will work on the originally found rows, not the result rows like LIMIT
{code:sql}
SELECT sum(c1) FROM t1 WHERE rownum<=2;
{code}
This will return 3 (or at least not 6)

ROWNUM cannot be used with HAVING (Oracle has the same restriction)
ROWNUM should not be used in applications with the following constructs as the result will not be deterministic (it's legal to use it, but the rows used in the result set will differ based on engine or used optimizations):
- In ON expressions
- With GROUP BY
- SELECT without an ORDER BY (This is a common case how ROWNUM is used and in this case we can replace it with LIMIT)

oracle rownum reference: https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/ROWNUM-Pseudocolumn.html#GUID-2E40EC12-3FCF-4A4F-B5F2-6BC669021726

Architecture:
Add function ROWNUM() (Item_rownum()).
- Map the result of ROWNUM() to 'last_join->accepted_records', which is incremented for each row added to the result set.
  This variable will be incremented in end_send(), end_send_group(), end_update() and end_unique_update()
- Filesort() needs each own variable that is incremented for each accepted row.

In oracle mode, if field with name 'rownum' is used and there is no match in
any of the tables, map the field to Item_rownum()

Things to consider and test in addition to the test in the suggested patch:

- Limit optimization
  -- If we have ROWNUM < # or ROWNUM <= # on the top level of the WHERE clause
    this should be converted to LIMIT. This is to be done in the
    JOIN::prepare phase(), probably after optimize_cond() which does
    propagate_cond_constants()
- When using sum functions, it's not sent rows, it's examined rows we have to
  limit against
- Filesort, when done over single table needs it's own handling
  (as there are no 'send_records' here). Limit is done according to found rows,
  not sorted order.
- Ensure that ROWNUM also works with UPDATE and DELETE
- UNION
  -- ROWNUM should work independently for each UNION

Oracle ROWNUM is a pseudocolumn (not a real column) that is generated
when running a select.

ROWNUM will be assigned the numbers 1, 2, 3, 4, ... N.

For each row combination returned by a query, ROWNUM returns a number indicating the order in which Oracle selects the rows from the table (tables)

The first row combination selected has a ROWNUM of 1, the second has 2, and so
on.

When used in a sub query, the subquery will generate a new ROWNUM sequence for
each sub query execution.

The ROWNUM pseudocolumn should be implemented under SQL_MODE=ORACLE.

In many cases one could also replace WHERE ROWNUM <= X with LIMIT X.

The most common usage of rownum is to limit the number of rows in the query:
SELECT * from table where rownum < 10;

Examples:
{code:sql}
create table t1 (a int primary key);
create table t2 (b int primary key);
insert into t1 values(1),(2),(3);
insert into t2 values(1),(2),(3);

select t1.a, (select t2.b from t2 where t1.a=t2.b and rownum < t1.a) from t1;
{code}
The following query is undefined (can return any rows) as rownum is generated before ORDER BY is done and there is no guarantee in which order the database is returning rows.
{code:sql}
Select * from t1 order by t1.a desc having rownum <= 2;

create table t1(c1 number, c2 number);
create table t2(c1 number, c2 number);
create table t3(c1 number, c2 number);

insert into t1 values(1,1);
insert into t1 values(2,2);
insert into t1 values(3,3);

insert into t2 values(1,1);
insert into t2 values(2,2);
insert into t2 values(3,3);

insert into t3 values(1,1);
insert into t3 values(2,2);
insert into t3 values(3,3);
{code}
{noformat}
SQL> select * from t1 where rownum<2;
C1 C2
1 1

SQL> select t1.c1, t1.c2 ,rownum from t1 left join t2 on t1.c1=t2.c1 inner join t3 on t2.c1=t3.c1;

        C1 C2 ROWNUM
---------- ---------- ----------
         1 1 1
         2 2 2
         3 3 3

SQL> select * from ( select t1.* , rownum row_id from t1 where rownum <= 5 ) where row_id > 2;

        C1 C2 ROW_ID
---------- ---------- ----------
         3 3 3

SQL> select t1.c1, (select t2.c2 from t2 where t1.c1=t2.c2 and rownum < t1.c1) from t1;

        C1 (SELECTT2.C2FROMT2WHERET1.C1=T2.C2ANDROWNUM<T1.C1)
---------- --------------------------------------------------
         1
         2 2
         3 3
{noformat}
One can also use ROWNUM with UPDATE:
{code:sql}
UPDATE table SET column = ROWNUM;
{code}
In case of group by queries, the rownum will work on the originally found rows, not the result rows like LIMIT
{code:sql}
SELECT sum(c1) FROM t1 WHERE rownum<=2;
{code}
This will return 3 (or at least not 6)

ROWNUM cannot be used with HAVING (Oracle has the same restriction)
ROWNUM should not be used in applications with the following constructs as the result will not be deterministic (it's legal to use it, but the rows used in the result set will differ based on engine or used optimizations):
- In ON expressions
- With GROUP BY
- SELECT without an ORDER BY (This is a common case how ROWNUM is used and in this case we can replace it with LIMIT)

oracle rownum reference: https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/ROWNUM-Pseudocolumn.html#GUID-2E40EC12-3FCF-4A4F-B5F2-6BC669021726

Architecture:
Add function ROWNUM() (Item_rownum()).
- Map the result of ROWNUM() to 'last_join->accepted_records', which is incremented for each row added to the result set.
  This variable will be incremented in end_send(), end_send_group(), end_update() and end_unique_update()
- Filesort() needs each own variable that is incremented for each accepted row.

In oracle mode, if field with name 'rownum' is used and there is no match in
any of the tables, map the field to Item_rownum()

Things to consider and test in addition to the test in the suggested patch:

- Limit optimization
  -- If we have ROWNUM < # or ROWNUM <= # on the top level of the WHERE clause
    this should be converted to LIMIT. This is to be done in the
    JOIN::prepare phase(), probably after optimize_cond() which does
    propagate_cond_constants()
- When using sum functions, it's not sent rows, it's examined rows we have to
  limit against
- Filesort, when done over single table needs it's own handling
  (as there are no 'send_records' here). Limit is done according to found rows,
  not sorted order.
- Ensure that ROWNUM also works with UPDATE and DELETE
- UNION
  -- ROWNUM should work independently for each UNION

Sergei Golubchik made changes - 2021-04-06 16:45

Description

Oracle ROWNUM is a pseudocolumn (not a real column) that is generated
when running a select.

ROWNUM will be assigned the numbers 1, 2, 3, 4, ... N.

For each row combination returned by a query, ROWNUM returns a number indicating the order in which Oracle selects the rows from the table (tables)

The first row combination selected has a ROWNUM of 1, the second has 2, and so
on.

When used in a sub query, the subquery will generate a new ROWNUM sequence for
each sub query execution.

The ROWNUM pseudocolumn should be implemented under SQL_MODE=ORACLE.

In many cases one could also replace WHERE ROWNUM <= X with LIMIT X.

The most common usage of rownum is to limit the number of rows in the query:
SELECT * from table where rownum < 10;

Examples:
{code:sql}
create table t1 (a int primary key);
create table t2 (b int primary key);
insert into t1 values(1),(2),(3);
insert into t2 values(1),(2),(3);

select t1.a, (select t2.b from t2 where t1.a=t2.b and rownum < t1.a) from t1;
{code}
The following query is undefined (can return any rows) as rownum is generated before ORDER BY is done and there is no guarantee in which order the database is returning rows.
{code:sql}
Select * from t1 order by t1.a desc having rownum <= 2;

create table t1(c1 number, c2 number);
create table t2(c1 number, c2 number);
create table t3(c1 number, c2 number);

insert into t1 values(1,1);
insert into t1 values(2,2);
insert into t1 values(3,3);

insert into t2 values(1,1);
insert into t2 values(2,2);
insert into t2 values(3,3);

insert into t3 values(1,1);
insert into t3 values(2,2);
insert into t3 values(3,3);
{code}
{noformat}
SQL> select * from t1 where rownum<2;
C1 C2
1 1

SQL> select t1.c1, t1.c2 ,rownum from t1 left join t2 on t1.c1=t2.c1 inner join t3 on t2.c1=t3.c1;

        C1 C2 ROWNUM
---------- ---------- ----------
         1 1 1
         2 2 2
         3 3 3

SQL> select * from ( select t1.* , rownum row_id from t1 where rownum <= 5 ) where row_id > 2;

        C1 C2 ROW_ID
---------- ---------- ----------
         3 3 3

SQL> select t1.c1, (select t2.c2 from t2 where t1.c1=t2.c2 and rownum < t1.c1) from t1;

        C1 (SELECTT2.C2FROMT2WHERET1.C1=T2.C2ANDROWNUM<T1.C1)
---------- --------------------------------------------------
         1
         2 2
         3 3
{noformat}
One can also use ROWNUM with UPDATE:
{code:sql}
UPDATE table SET column = ROWNUM;
{code}
In case of group by queries, the rownum will work on the originally found rows, not the result rows like LIMIT
{code:sql}
SELECT sum(c1) FROM t1 WHERE rownum<=2;
{code}
This will return 3 (or at least not 6)

ROWNUM cannot be used with HAVING (Oracle has the same restriction)
ROWNUM should not be used in applications with the following constructs as the result will not be deterministic (it's legal to use it, but the rows used in the result set will differ based on engine or used optimizations):
- In ON expressions
- With GROUP BY
- SELECT without an ORDER BY (This is a common case how ROWNUM is used and in this case we can replace it with LIMIT)

oracle rownum reference: https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/ROWNUM-Pseudocolumn.html#GUID-2E40EC12-3FCF-4A4F-B5F2-6BC669021726

Architecture:
Add function ROWNUM() (Item_rownum()).
- Map the result of ROWNUM() to 'last_join->accepted_records', which is incremented for each row added to the result set.
  This variable will be incremented in end_send(), end_send_group(), end_update() and end_unique_update()
- Filesort() needs each own variable that is incremented for each accepted row.

In oracle mode, if field with name 'rownum' is used and there is no match in
any of the tables, map the field to Item_rownum()

Things to consider and test in addition to the test in the suggested patch:

- Limit optimization
  -- If we have ROWNUM < # or ROWNUM <= # on the top level of the WHERE clause
    this should be converted to LIMIT. This is to be done in the
    JOIN::prepare phase(), probably after optimize_cond() which does
    propagate_cond_constants()
- When using sum functions, it's not sent rows, it's examined rows we have to
  limit against
- Filesort, when done over single table needs it's own handling
  (as there are no 'send_records' here). Limit is done according to found rows,
  not sorted order.
- Ensure that ROWNUM also works with UPDATE and DELETE
- UNION
  -- ROWNUM should work independently for each UNION

Oracle ROWNUM is a pseudocolumn (not a real column) that is generated
when running a select.

ROWNUM will be assigned the numbers 1, 2, 3, 4, ... N.

For each row combination returned by a query, ROWNUM returns a number indicating the order in which Oracle selects the rows from the table (tables)

The first row combination selected has a ROWNUM of 1, the second has 2, and so
on.

When used in a sub query, the subquery will generate a new ROWNUM sequence for
each sub query execution.

The ROWNUM pseudocolumn should be implemented under SQL_MODE=ORACLE.

In many cases one could also replace WHERE ROWNUM <= X with LIMIT X.

The most common usage of rownum is to limit the number of rows in the query:
{code:sql}
SELECT * from table where rownum < 10;
{code}
Examples:
{code:sql}
create table t1 (a int primary key);
create table t2 (b int primary key);
insert into t1 values(1),(2),(3);
insert into t2 values(1),(2),(3);

select t1.a, (select t2.b from t2 where t1.a=t2.b and rownum < t1.a) from t1;
{code}
The following query is undefined (can return any rows) as rownum is generated before ORDER BY is done and there is no guarantee in which order the database is returning rows.
{code:sql}
Select * from t1 order by t1.a desc having rownum <= 2;

create table t1(c1 number, c2 number);
create table t2(c1 number, c2 number);
create table t3(c1 number, c2 number);

insert into t1 values(1,1);
insert into t1 values(2,2);
insert into t1 values(3,3);

insert into t2 values(1,1);
insert into t2 values(2,2);
insert into t2 values(3,3);

insert into t3 values(1,1);
insert into t3 values(2,2);
insert into t3 values(3,3);
{code}
{noformat:title=Examples of Oracle behavior}
SQL> select * from t1 where rownum<2;
C1 C2
1 1

SQL> select t1.c1, t1.c2 ,rownum from t1 left join t2 on t1.c1=t2.c1 inner join t3 on t2.c1=t3.c1;

        C1 C2 ROWNUM
---------- ---------- ----------
         1 1 1
         2 2 2
         3 3 3

SQL> select * from ( select t1.* , rownum row_id from t1 where rownum <= 5 ) where row_id > 2;

        C1 C2 ROW_ID
---------- ---------- ----------
         3 3 3

SQL> select t1.c1, (select t2.c2 from t2 where t1.c1=t2.c2 and rownum < t1.c1) from t1;

        C1 (SELECTT2.C2FROMT2WHERET1.C1=T2.C2ANDROWNUM<T1.C1)
---------- --------------------------------------------------
         1
         2 2
         3 3
{noformat}
One can also use ROWNUM with UPDATE:
{code:sql}
UPDATE table SET column = ROWNUM;
{code}
In case of group by queries, the rownum will work on the originally found rows, not the result rows like LIMIT
{code:sql}
SELECT sum(c1) FROM t1 WHERE rownum<=2;
{code}
This will return 3 (or at least not 6)

ROWNUM cannot be used with HAVING (Oracle has the same restriction)
ROWNUM should not be used in applications with the following constructs as the result will not be deterministic (it's legal to use it, but the rows used in the result set will differ based on engine or used optimizations):
- In ON expressions
- With GROUP BY
- SELECT without an ORDER BY (This is a common case how ROWNUM is used and in this case we can replace it with LIMIT)

oracle rownum reference: https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/ROWNUM-Pseudocolumn.html#GUID-2E40EC12-3FCF-4A4F-B5F2-6BC669021726

Architecture:
Add function ROWNUM() (Item_rownum()).
- Map the result of ROWNUM() to 'last_join->accepted_records', which is incremented for each row added to the result set.
  This variable will be incremented in end_send(), end_send_group(), end_update() and end_unique_update()
- Filesort() needs each own variable that is incremented for each accepted row.

In oracle mode, if field with name 'rownum' is used and there is no match in
any of the tables, map the field to Item_rownum()

Things to consider and test in addition to the test in the suggested patch:

- Limit optimization
  -- If we have ROWNUM < # or ROWNUM <= # on the top level of the WHERE clause
    this should be converted to LIMIT. This is to be done in the
    JOIN::prepare phase(), probably after optimize_cond() which does
    propagate_cond_constants()
- When using sum functions, it's not sent rows, it's examined rows we have to
  limit against
- Filesort, when done over single table needs it's own handling
  (as there are no 'send_records' here). Limit is done according to found rows,
  not sorted order.
- Ensure that ROWNUM also works with UPDATE and DELETE
- UNION
  -- ROWNUM should work independently for each UNION

Sergei Golubchik made changes - 2021-04-06 17:03

Description

Oracle ROWNUM is a pseudocolumn (not a real column) that is generated
when running a select.

ROWNUM will be assigned the numbers 1, 2, 3, 4, ... N.

For each row combination returned by a query, ROWNUM returns a number indicating the order in which Oracle selects the rows from the table (tables)

The first row combination selected has a ROWNUM of 1, the second has 2, and so
on.

When used in a sub query, the subquery will generate a new ROWNUM sequence for
each sub query execution.

The ROWNUM pseudocolumn should be implemented under SQL_MODE=ORACLE.

In many cases one could also replace WHERE ROWNUM <= X with LIMIT X.

The most common usage of rownum is to limit the number of rows in the query:
{code:sql}
SELECT * from table where rownum < 10;
{code}
Examples:
{code:sql}
create table t1 (a int primary key);
create table t2 (b int primary key);
insert into t1 values(1),(2),(3);
insert into t2 values(1),(2),(3);

select t1.a, (select t2.b from t2 where t1.a=t2.b and rownum < t1.a) from t1;
{code}
The following query is undefined (can return any rows) as rownum is generated before ORDER BY is done and there is no guarantee in which order the database is returning rows.
{code:sql}
Select * from t1 order by t1.a desc having rownum <= 2;

create table t1(c1 number, c2 number);
create table t2(c1 number, c2 number);
create table t3(c1 number, c2 number);

insert into t1 values(1,1);
insert into t1 values(2,2);
insert into t1 values(3,3);

insert into t2 values(1,1);
insert into t2 values(2,2);
insert into t2 values(3,3);

insert into t3 values(1,1);
insert into t3 values(2,2);
insert into t3 values(3,3);
{code}
{noformat:title=Examples of Oracle behavior}
SQL> select * from t1 where rownum<2;
C1 C2
1 1

SQL> select t1.c1, t1.c2 ,rownum from t1 left join t2 on t1.c1=t2.c1 inner join t3 on t2.c1=t3.c1;

        C1 C2 ROWNUM
---------- ---------- ----------
         1 1 1
         2 2 2
         3 3 3

SQL> select * from ( select t1.* , rownum row_id from t1 where rownum <= 5 ) where row_id > 2;

        C1 C2 ROW_ID
---------- ---------- ----------
         3 3 3

SQL> select t1.c1, (select t2.c2 from t2 where t1.c1=t2.c2 and rownum < t1.c1) from t1;

        C1 (SELECTT2.C2FROMT2WHERET1.C1=T2.C2ANDROWNUM<T1.C1)
---------- --------------------------------------------------
         1
         2 2
         3 3
{noformat}
One can also use ROWNUM with UPDATE:
{code:sql}
UPDATE table SET column = ROWNUM;
{code}
In case of group by queries, the rownum will work on the originally found rows, not the result rows like LIMIT
{code:sql}
SELECT sum(c1) FROM t1 WHERE rownum<=2;
{code}
This will return 3 (or at least not 6)

ROWNUM cannot be used with HAVING (Oracle has the same restriction)
ROWNUM should not be used in applications with the following constructs as the result will not be deterministic (it's legal to use it, but the rows used in the result set will differ based on engine or used optimizations):
- In ON expressions
- With GROUP BY
- SELECT without an ORDER BY (This is a common case how ROWNUM is used and in this case we can replace it with LIMIT)

oracle rownum reference: https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/ROWNUM-Pseudocolumn.html#GUID-2E40EC12-3FCF-4A4F-B5F2-6BC669021726

Architecture:
Add function ROWNUM() (Item_rownum()).
- Map the result of ROWNUM() to 'last_join->accepted_records', which is incremented for each row added to the result set.
  This variable will be incremented in end_send(), end_send_group(), end_update() and end_unique_update()
- Filesort() needs each own variable that is incremented for each accepted row.

In oracle mode, if field with name 'rownum' is used and there is no match in
any of the tables, map the field to Item_rownum()

Things to consider and test in addition to the test in the suggested patch:

- Limit optimization
  -- If we have ROWNUM < # or ROWNUM <= # on the top level of the WHERE clause
    this should be converted to LIMIT. This is to be done in the
    JOIN::prepare phase(), probably after optimize_cond() which does
    propagate_cond_constants()
- When using sum functions, it's not sent rows, it's examined rows we have to
  limit against
- Filesort, when done over single table needs it's own handling
  (as there are no 'send_records' here). Limit is done according to found rows,
  not sorted order.
- Ensure that ROWNUM also works with UPDATE and DELETE
- UNION
  -- ROWNUM should work independently for each UNION

Oracle ROWNUM is a pseudocolumn (not a real column) that is generated
when running a select.

ROWNUM will be assigned the numbers 1, 2, 3, 4, ... N.

For each row combination returned by a query, ROWNUM returns a number indicating the order in which Oracle selects the rows from the table (tables)

The first row combination selected has a ROWNUM of 1, the second has 2, and so
on.

When used in a sub query, the subquery will generate a new ROWNUM sequence for
each sub query execution.

The ROWNUM pseudocolumn should be implemented under SQL_MODE=ORACLE.

In many cases one could also replace WHERE ROWNUM <= X with LIMIT X.

The most common usage of rownum is to limit the number of rows in the query:
{code:sql}
SELECT * from table where rownum < 10;
{code}
Examples:
{code:sql}
create table t1 (a int primary key);
create table t2 (b int primary key);
insert into t1 values(1),(2),(3);
insert into t2 values(1),(2),(3);

select t1.a, (select t2.b from t2 where t1.a=t2.b and rownum < t1.a) from t1;
{code}
The following query is undefined (can return any rows) as rownum is generated before ORDER BY is done and there is no guarantee in which order the database is returning rows.
{code:sql}
Select * from t1 order by t1.a desc having rownum <= 2;
{code}
Authentic Oracle behavior:
{code:sql|title=Prepare the data}
create table t1(c1 number, c2 number);
create table t2(c1 number, c2 number);
create table t3(c1 number, c2 number);

insert into t1 values(1,1);
insert into t1 values(2,2);
insert into t1 values(3,3);

insert into t2 values(1,1);
insert into t2 values(2,2);
insert into t2 values(3,3);

insert into t3 values(1,1);
insert into t3 values(2,2);
insert into t3 values(3,3);
{code}
{noformat:title=Run queries}
SQL> select * from t1 where rownum<2;
C1 C2
1 1

SQL> select t1.c1, t1.c2 ,rownum from t1 left join t2 on t1.c1=t2.c1 inner join t3 on t2.c1=t3.c1;

        C1 C2 ROWNUM
---------- ---------- ----------
         1 1 1
         2 2 2
         3 3 3

SQL> select * from ( select t1.* , rownum row_id from t1 where rownum <= 5 ) where row_id > 2;

        C1 C2 ROW_ID
---------- ---------- ----------
         3 3 3

SQL> select t1.c1, (select t2.c2 from t2 where t1.c1=t2.c2 and rownum < t1.c1) from t1;

        C1 (SELECTT2.C2FROMT2WHERET1.C1=T2.C2ANDROWNUM<T1.C1)
---------- --------------------------------------------------
         1
         2 2
         3 3
{noformat}
One can also use ROWNUM with UPDATE:
{code:sql}
UPDATE table SET column = ROWNUM;
{code}
In case of group by queries, the rownum will work on the originally found rows, not the result rows like LIMIT
{code:sql}
SELECT sum(c1) FROM t1 WHERE rownum<=2;
{code}
This will return 3 (or at least not 6)

ROWNUM cannot be used with HAVING (Oracle has the same restriction)
ROWNUM should not be used in applications with the following constructs as the result will not be deterministic (it's legal to use it, but the rows used in the result set will differ based on engine or used optimizations):
- In ON expressions
- With GROUP BY
- SELECT without an ORDER BY (This is a common case how ROWNUM is used and in this case we can replace it with LIMIT)

oracle rownum reference: https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/ROWNUM-Pseudocolumn.html#GUID-2E40EC12-3FCF-4A4F-B5F2-6BC669021726

Architecture:
Add function ROWNUM() (Item_rownum()).
- Map the result of ROWNUM() to 'last_join->accepted_records', which is incremented for each row added to the result set.
  This variable will be incremented in end_send(), end_send_group(), end_update() and end_unique_update()
- Filesort() needs each own variable that is incremented for each accepted row.

In oracle mode, if field with name 'rownum' is used and there is no match in
any of the tables, map the field to Item_rownum()

Things to consider and test in addition to the test in the suggested patch:

- Limit optimization
  -- If we have ROWNUM < # or ROWNUM <= # on the top level of the WHERE clause
    this should be converted to LIMIT. This is to be done in the
    JOIN::prepare phase(), probably after optimize_cond() which does
    propagate_cond_constants()
- When using sum functions, it's not sent rows, it's examined rows we have to
  limit against
- Filesort, when done over single table needs it's own handling
  (as there are no 'send_records' here). Limit is done according to found rows,
  not sorted order.
- Ensure that ROWNUM also works with UPDATE and DELETE
- UNION
  -- ROWNUM should work independently for each UNION

Sergei Golubchik made changes - 2021-05-10 16:10

Assignee	Sergei Golubchik [ serg ]	Michael Widenius [ monty ]
Status	In Review [ 10002 ]	Stalled [ 10000 ]

Ralf Gebhardt made changes - 2021-05-11 07:57

Link

This issue is duplicated by ~~MDEV-19782~~ [ ~~MDEV-19782~~ ]

Ralf Gebhardt made changes - 2021-05-11 07:58

Link

This issue blocks ~~MDEV-19782~~ [ ~~MDEV-19782~~ ]

Sergei Golubchik made changes - 2021-05-20 09:27

Component/s		Optimizer [ 10200 ]
Component/s		Server [ 13907 ]
Fix Version/s		10.6.1 [ 24437 ]
Fix Version/s	10.6 [ 24028 ]
Resolution		Fixed [ 1 ]
Status	Stalled [ 10000 ]	Closed [ 6 ]

Geoff Montee (Inactive) made changes - 2021-08-09 22:34

Link

This issue causes ~~MDEV-26329~~ [ ~~MDEV-26329~~ ]

Sergei Golubchik made changes - 2021-12-06 21:24

Workflow

MariaDB v3 [ 115186 ]

MariaDB v4 [ 134354 ]

Alice Sherepa made changes - 2022-02-04 15:56

Link

This issue relates to ~~MDEV-27745~~ [ ~~MDEV-27745~~ ]

Oleg Smirnov made changes - 2022-09-14 15:02

Link

This issue causes ~~MDEV-29129~~ [ ~~MDEV-29129~~ ]

MariaDB Server

support oracle syntax: rownum

Details

Description

Attachments

Attachments

Issue Links

Activity

People

Dates

Git Integration