Adding upstream version 1:10.11.6.upstream/1%10.11.6upstream

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>

1 files changed, 1019 insertions, 0 deletions

diff --git a/mysql-test/main/rownum.result b/mysql-test/main/rownum.result
new file mode 100644
index 00000000..b61269b1
--- /dev/null
+++ b/mysql-test/main/rownum.result
@@ -0,0 +1,1019 @@
+CREATE OR REPLACE TABLE t1(a int, b int) engine=aria;
+CREATE OR REPLACE TABLE t2(a int, b int) engine=aria;
+insert into t1 values (1,10),(2,20),(3,30);
+insert into t2 values (2,21),(3,31),(4,41);
+#
+# Simple selects
+#
+select a,b,rownum() from t1;
+a	b	rownum()
+1	10	1
+2	20	2
+3	30	3
+select a,b,rownum() from t1 where rownum() < 2;
+a	b	rownum()
+1	10	1
+select a,b from t1 where rownum() <= 2;
+a	b
+1	10
+2	20
+select a,b from t1 where rownum() > 2;
+a	b
+#
+# Subqueries
+#
+select t1.a,rownum(),t3.a,t3.t2_rownum from t1, (select t2.a,rownum() as t2_rownum from t2 where rownum() <=2) t3;
+a	rownum()	a	t2_rownum
+1	1	2	1
+1	2	3	2
+2	3	2	1
+2	4	3	2
+3	5	2	1
+3	6	3	2
+select t1.a, (select t2.b from t2 where t1.a=t2.a and rownum() <= 1) 'b' from t1;
+a	b
+1	NULL
+2	21
+3	31
+select t1.a, t3.a from t1, (select * from t2 where rownum() <= 2) t3;
+a	a
+1	2
+1	3
+2	2
+2	3
+3	2
+3	3
+select * from (select tt.*,rownum() as id from (select * from t1) tt) t3 where id>2;
+a	b	id
+3	30	3
+#
+# Joins
+#
+select t1.a,t1.b,t2.a,t2.b,rownum() from t1,t2 where rownum() <= 4;
+a	b	a	b	rownum()
+1	10	2	21	1
+2	20	2	21	2
+3	30	2	21	3
+1	10	3	31	4
+select *,rownum() from t1,t2 where t1.a=t2.a and rownum()<=2;
+a	b	a	b	rownum()
+2	20	2	21	1
+3	30	3	31	2
+select * from t1 left join t2 on (t2.a=t1.a and rownum()=0);
+a	b	a	b
+1	10	NULL	NULL
+2	20	NULL	NULL
+3	30	NULL	NULL
+select * from t1 left join t2 on (t2.a=t1.a and rownum()>1);
+a	b	a	b
+1	10	NULL	NULL
+2	20	NULL	NULL
+3	30	NULL	NULL
+select * from t1 left join t2 on (t2.a=t1.a and rownum()<1);
+a	b	a	b
+1	10	NULL	NULL
+2	20	NULL	NULL
+3	30	NULL	NULL
+select * from t1 left join t2 on (t2.a=t1.a and rownum()=1);
+a	b	a	b
+2	20	2	21
+1	10	NULL	NULL
+3	30	NULL	NULL
+select * from t1 left join t2 on (t2.a=t1.a and rownum()>=1);
+a	b	a	b
+2	20	2	21
+3	30	3	31
+1	10	NULL	NULL
+#
+# Union
+#
+select * from t1 where rownum() <=2 union select * from t2 where rownum()<= 1;
+a	b
+1	10
+2	20
+2	21
+#
+# Order by
+#
+select * from t1 where rownum() <= 2 order by a desc;
+a	b
+2	20
+1	10
+explain select * from t1 where rownum() <= 2 order by a desc;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	Using where; Using filesort
+select t1.a,t1.b,rownum() from t1 where rownum() <= 2 order by a desc;
+a	b	rownum()
+2	20	2
+1	10	1
+explain select t1.a,t1.b,rownum() from t1 where rownum() <= 2 order by a desc;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	Using where; Using temporary; Using filesort
+select *,rownum() from t1,t2;
+a	b	a	b	rownum()
+1	10	2	21	1
+2	20	2	21	2
+3	30	2	21	3
+1	10	3	31	4
+2	20	3	31	5
+3	30	3	31	6
+1	10	4	41	7
+2	20	4	41	8
+3	30	4	41	9
+select *,rownum() from t1,t2 order by t2.a desc, t1.a desc;
+a	b	a	b	rownum()
+3	30	4	41	9
+2	20	4	41	8
+1	10	4	41	7
+3	30	3	31	6
+2	20	3	31	5
+1	10	3	31	4
+3	30	2	21	3
+2	20	2	21	2
+1	10	2	21	1
+select * from (select * from t1 order by a desc) as t where rownum() <= 2;
+a	b
+3	30
+2	20
+select * from t1,t2 where t1.a=t2.a and rownum()<=2 order by t1.a,t2.a;
+a	b	a	b
+2	20	2	21
+3	30	3	31
+create view v1 as
+select * from (select * from t1 order by a desc) as t where rownum() <= 2;
+select * from v1;
+a	b
+3	30
+2	20
+drop view v1;
+#
+# Having
+#
+select t1.a, sum(t1.b), rownum() as 'r' from t1 group by t1.a having r <= 2;
+a	sum(t1.b)	r
+1	10	1
+2	20	2
+select * from t1 having rownum() <= 2;
+a	b
+1	10
+2	20
+select t1.a, sum(t1.b), rownum() from t1 group by t1.a;
+a	sum(t1.b)	rownum()
+1	10	1
+2	20	2
+3	30	3
+select t1.a, sum(t1.b), rownum() as 'r' from t1 group by t1.a having r <= 2;
+a	sum(t1.b)	r
+1	10	1
+2	20	2
+select t1.a, sum(t1.b), rownum() as 'r' from t1 group by t1.a having rownum() <= 2;
+a	sum(t1.b)	r
+1	10	1
+2	20	2
+select t1.a, sum(t1.b), rownum() as 'r' from t1 group by t1.a having r <= 2 order by a desc;
+a	sum(t1.b)	r
+2	20	2
+1	10	1
+select t1.a, sum(t1.b), rownum() as 'r' from t1 group by t1.a having rownum() <= 2 order by a desc;
+a	sum(t1.b)	r
+2	20	2
+1	10	1
+#
+# Sum functions
+#
+select max(rownum()),min(rownum()) from t1;
+max(rownum())	min(rownum())
+3	1
+select sum(rownum()),avg(rownum()) from t1;
+sum(rownum())	avg(rownum())
+6	2.0000
+#
+# Group by
+#
+select t1.a,sum(t1.b) from t1 where rownum() < 2 group by t1.a;
+a	sum(t1.b)
+1	10
+select t1.a,sum(t2.b) from t1 JOIN t2 ON (t1.a=t2.a) where rownum() <= 2 group by t1.a;
+a	sum(t2.b)
+2	21
+3	31
+select * from (select t1.a,sum(t2.b) from t1 JOIN t2 ON (t1.a=t2.a) group by t1.a) as t where rownum() <= 1;
+a	sum(t2.b)
+2	21
+select t1.a,sum(rownum()),count(*) from t1 where rownum() <= 2 group by t1.a;
+a	sum(rownum())	count(*)
+1	1	1
+2	2	1
+select * from (select t1.a,sum(t1.b) from t1 group by t1.a) as t3 where rownum() < 2;
+a	sum(t1.b)
+1	10
+create table t3 (a int) engine=myisam;
+insert into t3 values (3),(5),(5),(3);
+select a, max(rownum()) from t3 group by a;
+a	max(rownum())
+3	4
+5	3
+drop table t3;
+CREATE TABLE t3 (
+a int(11) DEFAULT NULL,
+b varchar(1024) DEFAULT NULL
+);
+insert into t3 select mod(seq*3,20)+1, repeat(char(33+mod(seq,90)),mod(seq,10)*100) from seq_1_to_23;
+SELECT sq.a,length(sq.f) FROM (SELECT a, GROUP_CONCAT(b,b) AS f FROM t3 GROUP BY a ORDER BY a desc) as sq WHERE ROWNUM() <= 10;
+a	length(sq.f)
+20	600
+19	1200
+18	1800
+17	400
+16	1000
+15	1600
+14	200
+13	800
+12	1400
+11	0
+drop table t3;
+#
+# Prepared statements
+#
+PREPARE stmt1 from "select a,b,rownum() from t1 where rownum() <= 2";
+execute stmt1;
+a	b	rownum()
+1	10	1
+2	20	2
+execute stmt1;
+a	b	rownum()
+1	10	1
+2	20	2
+deallocate prepare stmt1;
+#
+# Views
+#
+create view v1 as select t1.a,rownum() from t1;
+select * from v1;
+a	rownum()
+1	1
+2	2
+3	3
+select t1.a,v1.* from t1,v1 where t1.a=v1.a;
+a	a	rownum()
+1	1	1
+2	2	2
+3	3	3
+drop view v1;
+CREATE TABLE t3 (a INT);
+INSERT INTO t3 VALUES (1),(2),(3);
+CREATE VIEW v1 AS SELECT a FROM t3 WHERE ROWNUM() <= 2;
+SELECT * FROM v1;
+a
+1
+2
+drop view v1;
+drop table t3;
+#
+# Reserved words
+#
+create table t4 (a int, rownum int);
+insert into t4 (a,rownum) values (1,2);
+select t4.a,t4.rownum from t4;
+a	rownum
+1	2
+drop table t4;
+#
+# Test Oracle mode
+#
+set SQL_MODE=ORACLE;
+select t1.a,rownum from t1 where rownum<=2;
+a	rownum
+1	1
+2	2
+select t1.a,rownum() from t1 where rownum()<=2;
+a	rownum()
+1	1
+2	2
+create table t4 (a int, rownum int);
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'rownum int)' at line 1
+DECLARE
+CURSOR c_cursor
+IS select a,b,rownum from t1 where rownum <= 2;
+v_a  t1.a%TYPE;
+v_b  t1.b%TYPE;
+v_rn t1.a%TYPE;
+BEGIN
+OPEN c_cursor;
+FETCH c_cursor INTO v_a, v_b, v_rn;
+WHILE c_cursor%FOUND LOOP
+SELECT concat(v_a,'--',v_b,'--',v_rn);
+FETCH c_cursor INTO v_a, v_b, v_rn;
+END LOOP;
+CLOSE c_cursor;
+END;|
+concat(v_a,'--',v_b,'--',v_rn)
+1--10--1
+concat(v_a,'--',v_b,'--',v_rn)
+2--20--2
+select a, rownum from t1 group by a, rownum having rownum < 3;
+a	rownum
+1	1
+2	2
+select a, rownum as r from t1 group by a, rownum having r < 3;
+a	r
+1	1
+2	2
+select a, rownum from t1 group by a, rownum having "rownum" < 3;
+a	rownum
+1	1
+2	2
+select a, rownum from t1 group by a, rownum having rownum < 3 order by a desc;
+a	rownum
+2	2
+1	1
+select a, rownum as r from t1 group by a, rownum having r < 3 order by a desc;
+a	r
+2	2
+1	1
+select a, rownum from t1 group by a, rownum having "rownum" < 3 order by a desc;
+a	rownum
+2	2
+1	1
+set SQL_MODE=DEFAULT;
+# Cleanup
+drop table t1,t2;
+#
+# INSERT
+#
+create table t1 (a int not null primary key, b int);
+insert into t1 values (1,rownum()),(2,rownum()),(3,rownum());
+select * from t1;
+a	b
+1	1
+2	2
+3	3
+drop table t1;
+#
+# INSERT DELAYED
+#
+create table t1 (a int not null primary key, b int);
+insert delayed into t1 values (1,rownum()),(2,rownum()),(3,rownum());
+select * from t1;
+a	b
+1	1
+2	2
+3	3
+drop table t1;
+#
+# INSERT IGNORED
+#
+create table t1 (a int not null primary key, b int);
+insert ignore into t1 values (1,rownum()),(2,rownum()),(2,rownum()),(3,rownum());
+Warnings:
+Warning	1062	Duplicate entry '2' for key 'PRIMARY'
+select * from t1;
+a	b
+1	1
+2	2
+3	4
+delete from t1;
+insert ignore into t1 select * from (values (1,rownum()),(2,rownum()),(2,rownum()),(3,rownum())) t;
+Warnings:
+Warning	1062	Duplicate entry '2' for key 'PRIMARY'
+select * from t1;
+a	b
+1	1
+2	2
+3	4
+drop table t1;
+#
+# INSERT ... RETURNING
+#
+create or replace table t1 (a int);
+insert into t1 values (1),(2) returning a, rownum();
+a	rownum()
+1	1
+2	2
+drop table t1;
+#
+# UPDATE
+#
+create table t1 (a int not null primary key, b int);
+insert into t1 values (1,1),(2,2),(3,3);
+update t1 set b=0;
+update t1 set b=rownum()+1;
+select * from t1;
+a	b
+1	2
+2	3
+3	4
+update t1 set b=0;
+update t1 set b=rownum() where a < 10 and rownum() < 2;
+select * from t1;
+a	b
+1	1
+2	0
+3	0
+drop table t1;
+create table t1 (a int);
+insert into t1 values (10),(20),(30);
+update t1 set a = rownum();
+select * from t1;
+a
+1
+2
+3
+update t1 set a = rownum();
+select * from t1;
+a
+1
+2
+3
+drop table t1;
+#
+# DELETE
+#
+create table t1 (a int not null primary key, b int);
+insert into t1 values (1,1),(2,0),(3,0);
+delete from t1 where a < 10 and rownum() < 2;
+select * from t1;
+a	b
+2	0
+3	0
+drop table t1;
+#
+# MULTI-TABLE-DELETE
+#
+create table t1 (a int not null primary key);
+insert into t1 values (1),(2),(3);
+create table t2 (a int not null primary key);
+insert into t2 values (1),(2),(3);
+delete t1,t2 from t1,t2 where t1.a=t2.a and rownum() <= 2;
+select * from t1;
+a
+3
+select * from t2;
+a
+3
+drop table t1,t2;
+#
+# MULTI-TABLE-UPDATE
+CREATE TABLE t1 (ID INT);
+CREATE TABLE t2 (ID INT,
+s1 TEXT, s2 TEXT, s3 VARCHAR(10), s4 TEXT, s5 VARCHAR(10));
+INSERT INTO t1 VALUES (1),(2);
+INSERT INTO t2 VALUES (1,'test', 'test', 'test', 'test', 'test'),
+(2,'test', 'test', 'test', 'test', 'test');
+SELECT * FROM t1 LEFT JOIN t2 USING(ID);
+ID	s1	s2	s3	s4	s5
+1	test	test	test	test	test
+2	test	test	test	test	test
+UPDATE t1 LEFT JOIN t2 USING(ID) SET s1 = 'changed';
+select * from t2;
+ID	s1	s2	s3	s4	s5
+1	changed	test	test	test	test
+2	changed	test	test	test	test
+update t2 set s1="";
+UPDATE t1 LEFT JOIN t2 USING(ID) SET s1 = 'changed' where rownum() <=1;
+select * from t2;
+ID	s1	s2	s3	s4	s5
+1	changed	test	test	test	test
+2		test	test	test	test
+drop table t1,t2;
+#
+# LOAD DATA
+#
+create table t1 (a int, b int, c int);
+load data infile '../../std_data/loaddata7.dat' into table t1 fields terminated by ',' lines terminated by "\r\n" (a,b) set c=rownum();
+select * from t1;
+a	b	c
+2	2	1
+3	3	2
+4	4	3
+5	5	4
+6	6	5
+drop table t1;
+#
+# LIMIT OPTIMIZATION
+#
+create table t1 (a int);
+insert into t1 select seq from seq_1_to_100;
+flush status;
+select * from t1 where rownum() <= 3;
+a
+1
+2
+3
+show status like "Rows_read";
+Variable_name	Value
+Rows_read	3
+flush status;
+select * from t1 where rownum() <= 4 and rownum() <= 3;
+a
+1
+2
+3
+show status like "Rows_read";
+Variable_name	Value
+Rows_read	3
+flush status;
+select * from t1 where rownum() < 4 and a > 10;
+a
+11
+12
+13
+show status like "Rows_read";
+Variable_name	Value
+Rows_read	13
+flush status;
+select * from t1 where 3 >= rownum();
+a
+1
+2
+3
+show status like "Rows_read";
+Variable_name	Value
+Rows_read	3
+flush status;
+select * from t1 where 4 > rownum() and a > 20;
+a
+21
+22
+23
+show status like "Rows_read";
+Variable_name	Value
+Rows_read	23
+flush status;
+select * from t1 where rownum() = 1 and a > 10;
+a
+11
+show status like "Rows_read";
+Variable_name	Value
+Rows_read	11
+flush status;
+select * from t1 where a > 30 && 1 = rownum();
+a
+31
+show status like "Rows_read";
+Variable_name	Value
+Rows_read	31
+flush status;
+# No limit optimization
+select * from t1 where rownum() > 10;
+a
+show status like "Rows_read";
+Variable_name	Value
+Rows_read	100
+flush status;
+select * from t1 where 10 < rownum();
+a
+show status like "Rows_read";
+Variable_name	Value
+Rows_read	100
+flush status;
+select * from t1 where rownum() >= 10;
+a
+show status like "Rows_read";
+Variable_name	Value
+Rows_read	100
+flush status;
+select * from t1 where 10 < rownum();
+a
+show status like "Rows_read";
+Variable_name	Value
+Rows_read	100
+flush status;
+select * from t1 where 10 <= rownum();
+a
+show status like "Rows_read";
+Variable_name	Value
+Rows_read	100
+flush status;
+select * from t1 where 2 = rownum();
+a
+show status like "Rows_read";
+Variable_name	Value
+Rows_read	100
+flush status;
+select * from t1 where rownum() = 2;
+a
+show status like "Rows_read";
+Variable_name	Value
+Rows_read	100
+flush status;
+select * from t1 where rownum() <= 0;
+a
+show status like "Rows_read";
+Variable_name	Value
+Rows_read	100
+flush status;
+select *,rownum() from t1 where rownum() < 10 limit 4, 4;
+a	rownum()
+5	5
+6	6
+7	7
+8	8
+show status like "Rows_read";
+Variable_name	Value
+Rows_read	8
+flush status;
+select * from t1 where rownum() < 10 order by a;
+a
+1
+2
+3
+4
+5
+6
+7
+8
+9
+show status like "Rows_read";
+Variable_name	Value
+Rows_read	100
+flush status;
+# rownum and limit
+select * from t1 where rownum() < 4 limit 10;
+a
+1
+2
+3
+show status like "Rows_read";
+Variable_name	Value
+Rows_read	3
+flush status;
+select * from t1 where rownum() < 10 limit 4;
+a
+1
+2
+3
+4
+show status like "Rows_read";
+Variable_name	Value
+Rows_read	4
+drop table t1;
+#
+# Rownum examples from Woqutech
+#
+set SQL_MODE=ORACLE;
+create table t1 (c1 int ,c2 varchar(20)) engine=myisam;
+insert into t1 values (1, 'aaa'),(2, 'bbb'),(3, 'ccc'),(4, 'ddd'),(5, 'eee');
+update t1 set c2 = 'xxx' where rownum = 2;
+select * from t1 where c2='xxx';
+c1	c2
+update t1 set c2 = 'xxx' where  rownum < 3;
+select * from t1 where c2='xxx';
+c1	c2
+1	xxx
+2	xxx
+delete from t1 where rownum = 2;
+select count(*) from t1;
+count(*)
+5
+delete from t1 where rownum < 3;
+select count(*) from t1;
+count(*)
+3
+delete from t1 where c1=rownum ;
+select count(*) from t1;
+count(*)
+3
+delete from t1 where c1=rownum+2 ;
+select count(*) from t1;
+count(*)
+0
+set SQL_MODE=DEFAULT;
+drop table t1;
+#
+# Rownum() used in not supported places (returns 0 or gives an error)
+#
+set @a=rownum();
+select @a;
+@a
+0
+create or replace table t (a int, b int as (rownum()) virtual);
+ERROR HY000: Function or expression 'rownum()' cannot be used in the GENERATED ALWAYS AS clause of `b`
+create table t1 (a int);
+insert into t1 values (3),(1),(5),(8),(4);
+handler t1 open;
+handler t1 read next where rownum() < 1;
+ERROR HY000: Function or expression 'rownum()' cannot be used in the WHERE clause of `HANDLER`
+handler t1 close;
+drop table t1;
+create table t1 (a int not null primary key, b int);
+insert into t1 values (1,1),(2,2),(3,3);
+create function f() returns int return rownum();
+select a, rownum(), f() from t1;
+a	rownum()	f()
+1	1	0
+2	2	0
+3	3	0
+drop function f;
+drop table t1;
+create or replace table t1 (a int, r int);
+create trigger tr before update on t1 for each row set NEW.r = rownum();
+insert into t1 (a) values (1),(2);
+select * from t1;
+a	r
+1	NULL
+2	NULL
+update t1 set a=a+10;
+select * from t1;
+a	r
+11	0
+12	0
+drop trigger tr;
+drop table t1;
+#
+# LIMIT optimisation
+#
+create table t1 (a int);
+insert into t1 values (1),(2),(3),(4),(5);
+flush status;
+select * from (select a from t1 where a < 1000) as tt where rownum() <= 2;
+a
+1
+2
+show status like "Rows_read";
+Variable_name	Value
+Rows_read	2
+explain extended select * from (select a from t1 where a < 1000) as tt where rownum() <= 2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
+2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	5	100.00	Using where
+Warnings:
+Note	1003	/* select#1 */ select `tt`.`a` AS `a` from (/* select#2 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` < 1000 limit 2) `tt` where rownum() <= 2 limit 2
+prepare stmt from "select * from (select a from t1 where a < 1000) as tt where rownum() <= 2";
+flush status;
+execute stmt;
+a
+1
+2
+show status like "Rows_read";
+Variable_name	Value
+Rows_read	2
+flush status;
+execute stmt;
+a
+1
+2
+show status like "Rows_read";
+Variable_name	Value
+Rows_read	2
+deallocate prepare stmt;
+flush status;
+select * from (select a from t1 where a < 1000 group by a) as tt where rownum() <= 2;
+a
+1
+2
+show status like "Rows_read";
+Variable_name	Value
+Rows_read	5
+explain extended select * from (select a from t1 where a < 1000 group by a) as tt where rownum() <= 2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
+2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	5	100.00	Using where; Using temporary; Using filesort
+Warnings:
+Note	1003	/* select#1 */ select `tt`.`a` AS `a` from (/* select#2 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` < 1000 group by `test`.`t1`.`a` limit 2) `tt` where rownum() <= 2 limit 2
+prepare stmt from "select * from (select a from t1 where a < 1000 group by a) as tt where rownum() <= 2";
+execute stmt;
+a
+1
+2
+execute stmt;
+a
+1
+2
+deallocate prepare stmt;
+flush status;
+select * from (select a from t1 where a < 1000 group by a order by 1) as tt where rownum() <= 2;
+a
+1
+2
+show status like "Rows_read";
+Variable_name	Value
+Rows_read	5
+explain extended select * from (select a from t1 where a < 1000 group by a order by 1) as tt where rownum() <= 2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
+2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	5	100.00	Using where; Using temporary; Using filesort
+Warnings:
+Note	1003	/* select#1 */ select `tt`.`a` AS `a` from (/* select#2 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` < 1000 group by `test`.`t1`.`a` order by 1 limit 2) `tt` where rownum() <= 2 limit 2
+prepare stmt from "select * from (select a from t1 where a < 1000 group by a order by 1) as tt where rownum() <= 2";
+execute stmt;
+a
+1
+2
+execute stmt;
+a
+1
+2
+deallocate prepare stmt;
+flush status;
+select * from (select a from t1 where a < 1000 union select 10) as tt where rownum() <= 2;
+a
+1
+2
+show status like "Rows_read";
+Variable_name	Value
+Rows_read	5
+explain extended select * from (select a from t1 where a < 1000 union select 10) as tt where rownum() <= 2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
+2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	5	100.00	Using where
+3	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+NULL	UNION RESULT	<union2,3>	ALL	NULL	NULL	NULL	NULL	NULL	NULL	
+Warnings:
+Note	1003	/* select#1 */ select `tt`.`a` AS `a` from (/* select#2 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` < 1000 union /* select#3 */ select 10 AS `10` limit 2) `tt` where rownum() <= 2 limit 2
+prepare stmt from "select * from (select a from t1 where a < 1000 union select 10) as tt where rownum() <= 2";
+execute stmt;
+a
+1
+2
+execute stmt;
+a
+1
+2
+deallocate prepare stmt;
+# Other limit
+select * from (select a from t1 where a < 1000 group by a order by 1 limit 1) as tt where rownum() <= 2;
+a
+1
+explain extended select * from (select a from t1 where a < 1000 group by a order by 1 limit 1) as tt where rownum() <= 2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
+2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	5	100.00	Using where; Using temporary; Using filesort
+Warnings:
+Note	1003	/* select#1 */ select `tt`.`a` AS `a` from (/* select#2 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` < 1000 group by `test`.`t1`.`a` order by 1 limit 1) `tt` where rownum() <= 2 limit 2
+prepare stmt from "select * from (select a from t1 where a < 1000 group by a order by 1 limit 1) as tt where rownum() <= 2";
+execute stmt;
+a
+1
+execute stmt;
+a
+1
+deallocate prepare stmt;
+# Other limit less
+select * from (select a from t1 where a < 1000 group by a order by 1 limit 10) as tt where rownum() <= 2;
+a
+1
+2
+explain extended select * from (select a from t1 where a < 1000 group by a order by 1 limit 10) as tt where rownum() <= 2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
+2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	5	100.00	Using where; Using temporary; Using filesort
+Warnings:
+Note	1003	/* select#1 */ select `tt`.`a` AS `a` from (/* select#2 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` < 1000 group by `test`.`t1`.`a` order by 1 limit 2) `tt` where rownum() <= 2 limit 2
+prepare stmt from "select * from (select a from t1 where a < 1000 group by a order by 1 limit 10) as tt where rownum() <= 2";
+execute stmt;
+a
+1
+2
+execute stmt;
+a
+1
+2
+deallocate prepare stmt;
+select * from (select a from t1 where a < 1000 union select 10 limit 1) as tt where rownum() <= 2;
+a
+1
+explain extended select * from (select a from t1 where a < 1000 union select 10 limit 1) as tt where rownum() <= 2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	5	100.00	Using where
+2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	5	100.00	Using where
+3	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+NULL	UNION RESULT	<union2,3>	ALL	NULL	NULL	NULL	NULL	NULL	NULL	
+Warnings:
+Note	1003	/* select#1 */ select `tt`.`a` AS `a` from (/* select#2 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` < 1000 union /* select#3 */ select 10 AS `10` limit 1) `tt` where rownum() <= 2 limit 2
+prepare stmt from "select * from (select a from t1 where a < 1000 union select 10 limit 1) as tt where rownum() <= 2";
+execute stmt;
+a
+1
+execute stmt;
+a
+1
+deallocate prepare stmt;
+# < rownum
+select * from (select a from t1 where a < 1000) as tt where rownum() < 2;
+a
+1
+explain extended select * from (select a from t1 where a < 1000) as tt where rownum() < 2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
+2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	5	100.00	Using where
+Warnings:
+Note	1003	/* select#1 */ select `tt`.`a` AS `a` from (/* select#2 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` < 1000 limit 1) `tt` where rownum() < 2 limit 1
+prepare stmt from "select * from (select a from t1 where a < 1000) as tt where rownum() < 2";
+execute stmt;
+a
+1
+execute stmt;
+a
+1
+deallocate prepare stmt;
+# Simple expression
+select * from (select a from t1 where a < 1000) as tt where rownum() <= 1+1;
+a
+1
+2
+explain extended select * from (select a from t1 where a < 1000) as tt where rownum() <= 1+1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
+2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	5	100.00	Using where
+Warnings:
+Note	1003	/* select#1 */ select `tt`.`a` AS `a` from (/* select#2 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` < 1000 limit 2) `tt` where rownum() <= <cache>(1 + 1) limit 2
+prepare stmt from "select * from (select a from t1 where a < 1000) as tt where rownum() <= 1+1";
+execute stmt;
+a
+1
+2
+execute stmt;
+a
+1
+2
+deallocate prepare stmt;
+# Simple expression reversed
+select * from (select a from t1 where a < 1000) as tt where  1+1 >= rownum();
+a
+1
+2
+explain extended select * from (select a from t1 where a < 1000) as tt where  1+1 >= rownum();
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
+2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	5	100.00	Using where
+Warnings:
+Note	1003	/* select#1 */ select `tt`.`a` AS `a` from (/* select#2 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` < 1000 limit 2) `tt` where <cache>(1 + 1) >= rownum() limit 2
+prepare stmt from "select * from (select a from t1 where a < 1000) as tt where  1+1 >= rownum()";
+execute stmt;
+a
+1
+2
+execute stmt;
+a
+1
+2
+deallocate prepare stmt;
+# expensive (no opt)
+select * from (select a from t1 where a < 1000) as tt where  (select max(a) from t1) >= rownum();
+a
+1
+2
+3
+4
+5
+explain extended select * from (select a from t1 where a < 1000) as tt where  (select max(a) from t1) >= rownum();
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	5	100.00	Using where
+3	SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	5	100.00	
+2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	5	100.00	Using where
+Warnings:
+Note	1003	/* select#1 */ select `tt`.`a` AS `a` from (/* select#2 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` < 1000 limit 5) `tt` where (/* select#3 */ select max(`test`.`t1`.`a`) from `test`.`t1`) >= rownum() limit 5
+prepare stmt from "select * from (select a from t1 where a < 1000) as tt where  (select max(a) from t1) >= rownum()";
+execute stmt;
+a
+1
+2
+3
+4
+5
+execute stmt;
+a
+1
+2
+3
+4
+5
+deallocate prepare stmt;
+drop table t1;
+#
+# Table value constructors
+#
+values ("first row"),("next row is 3"),(rownum()),("next row is 5"),(rownum());
+first row
+first row
+next row is 3
+3
+next row is 5
+5
+#
+# MDEV-31073: Server crash, assertion `table != 0 &&
+# view->field_translation != 0' failure with ROWNUM and view
+#
+CREATE TABLE t (f INT);
+INSERT INTO t VALUES (1),(2);
+CREATE VIEW v AS SELECT * FROM t;
+UPDATE v SET f = 10 WHERE ROWNUM() > 42 LIMIT 1;
+DROP VIEW v;
+DROP TABLE t;
+CREATE TABLE t (f INT);
+INSERT INTO t VALUES (1),(2);
+CREATE VIEW v AS SELECT f, 3 as e FROM t;
+UPDATE v SET f = 10 WHERE e > 42 LIMIT 1;
+DROP VIEW v;
+DROP TABLE t;
+CREATE TABLE t (f INT);
+INSERT INTO t VALUES (1),(2);
+CREATE VIEW v AS SELECT f, ROWNUM() as e FROM t;
+UPDATE v SET f = 10 WHERE e > 42 LIMIT 1;
+ERROR HY000: The target table v of the UPDATE is not updatable
+DROP VIEW v;
+DROP TABLE t;
+#
+# End of 10.6 tests
+#