MDEV-25430: ROW | ROWS should be a required keyword after OFFSET start

Implemented according to standard OFFSET start { ROW | ROWS},
ROW and ROWS is mandatory after OFFSET.

2 files changed, 156 insertions, 107 deletions

diff --git a/mysql-test/main/fetch_first.result b/mysql-test/main/fetch_first.result
index 88cd65123fa..1e63581e0a5 100644
--- a/mysql-test/main/fetch_first.result
+++ b/mysql-test/main/fetch_first.result
@@ -15,13 +15,13 @@ insert into t_keyword values (1), (1), (2), (3), (2);
 #
 select * from t1
 order by a
-offset 2;
+offset 2 rows;
 a
 2
 2
 3
 select * from t1
-offset 2;
+offset 2 rows;
 a
 2
 2
@@ -63,27 +63,51 @@ fetch next rows only;
 a
 1
 #
-# Include offset before fetch clause.
+# Row / rows are mandatory after offset <n>
 #
 select * from t1
 order by a
 offset 2
 fetch first row only;
+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 'fetch first row only' at line 4
+select * from t1
+order by a
+offset 2;
+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 '' at line 3
+select * from t1
+order by a
+offset 2 row
+fetch first row only;
 a
 2
 select * from t1
 order by a
-offset 2
+offset 2 rows
+fetch first row only;
+a
+2
+#
+# Include offset before fetch clause.
+#
+select * from t1
+order by a
+offset 2 rows
+fetch first row only;
+a
+2
+select * from t1
+order by a
+offset 2 rows
 fetch first rows only;
 a
 2
 select * from t1
-offset 2
+offset 2 rows
 fetch next row only;
 a
 2
 select * from t1
-offset 2
+offset 2 rows
 fetch next rows only;
 a
 2
@@ -132,28 +156,28 @@ a
 #
 select * from t1
 order by a
-offset 2
+offset 2 rows
 fetch first row with ties;
 a
 2
 2
 select * from t1
 order by a
-offset 2
+offset 2 rows
 fetch first rows with ties;
 a
 2
 2
 select * from t1
 order by a
-offset 2
+offset 2 rows
 fetch next row with ties;
 a
 2
 2
 select * from t1
 order by a
-offset 2
+offset 2 rows
 fetch next rows with ties;
 a
 2
@@ -190,7 +214,7 @@ a
 #
 select * from t1
 order by a
-offset 2
+offset 2 rows
 fetch first 3 row only;
 a
 2
@@ -198,21 +222,21 @@ a
 3
 select * from t1
 order by a
-offset 2
+offset 2 rows
 fetch first 3 rows only;
 a
 2
 2
 3
 select * from t1
-offset 2
+offset 2 rows
 fetch next 3 row only;
 a
 2
 3
 2
 select * from t1
-offset 2
+offset 2 rows
 fetch next 3 rows only;
 a
 2
@@ -270,12 +294,12 @@ a
 # Include offset before fetch clause.
 #
 select * from t1
-offset 2
+offset 2 rows
 fetch first row with ties;
 ERROR HY000: FETCH ... WITH TIES requires ORDER BY clause to be present
 select * from t1
 order by a
-offset 2
+offset 2 rows
 fetch first 3 row with ties;
 a
 2
@@ -283,7 +307,7 @@ a
 3
 select * from t1
 order by a
-offset 2
+offset 2 rows
 fetch first 3 rows with ties;
 a
 2
@@ -291,7 +315,7 @@ a
 3
 select * from t1
 order by a
-offset 2
+offset 2 rows
 fetch next 3 row with ties;
 a
 2
@@ -299,7 +323,7 @@ a
 3
 select * from t1
 order by a
-offset 2
+offset 2 rows
 fetch next 3 rows with ties;
 a
 2
@@ -389,7 +413,7 @@ id	first_name	last_name	score
 6	John	Elton	8.1
 select * from t1
 order by first_name, last_name
-offset 1
+offset 1 rows
 fetch first 3 rows with ties;
 id	first_name	last_name	score
 7	Bob	Trasc	9
@@ -397,7 +421,7 @@ id	first_name	last_name	score
 6	John	Elton	8.1
 select * from t1
 order by first_name, last_name
-offset 1
+offset 1 rows
 fetch first 3 rows only;
 id	first_name	last_name	score
 7	Bob	Trasc	9
@@ -405,7 +429,7 @@ id	first_name	last_name	score
 6	John	Elton	8.1
 select * from t1
 order by first_name, last_name
-offset 1
+offset 1 rows
 fetch first 3 rows with ties;
 id	first_name	last_name	score
 7	Bob	Trasc	9
@@ -413,7 +437,7 @@ id	first_name	last_name	score
 6	John	Elton	8.1
 select * from t1
 order by first_name, last_name
-offset 2
+offset 2 rows
 fetch first 3 rows only;
 id	first_name	last_name	score
 2	John	Doe	6
@@ -421,7 +445,7 @@ id	first_name	last_name	score
 5	John	Smith	7
 select * from t1
 order by first_name, last_name
-offset 2
+offset 2 rows
 fetch first 3 rows with ties;
 id	first_name	last_name	score
 2	John	Doe	6
@@ -431,7 +455,7 @@ id	first_name	last_name	score
 5	John	Smith	7
 select * from t1
 order by first_name, last_name
-offset 3
+offset 3 rows
 fetch first 3 rows only;
 id	first_name	last_name	score
 6	John	Elton	8.1
@@ -439,7 +463,7 @@ id	first_name	last_name	score
 4	John	Smith	6
 select * from t1
 order by first_name, last_name
-offset 3
+offset 3 rows
 fetch first 3 rows with ties;
 id	first_name	last_name	score
 6	John	Elton	8.1
@@ -448,7 +472,7 @@ id	first_name	last_name	score
 5	John	Smith	7
 select * from t1
 order by first_name, last_name
-offset 4
+offset 4 rows
 fetch first 3 rows only;
 id	first_name	last_name	score
 3	John	Smith	6
@@ -456,7 +480,7 @@ id	first_name	last_name	score
 5	John	Smith	7
 select * from t1
 order by first_name, last_name
-offset 4
+offset 4 rows
 fetch first 3 rows with ties;
 id	first_name	last_name	score
 3	John	Smith	6
@@ -467,7 +491,7 @@ id	first_name	last_name	score
 #
 select * from t1
 order by first_name, last_name
-offset 5
+offset 5 rows
 fetch first 3 rows with ties;
 id	first_name	last_name	score
 4	John	Smith	6
@@ -475,7 +499,7 @@ id	first_name	last_name	score
 8	Silvia	Ganush	10
 select * from t1
 order by first_name, last_name
-offset 5
+offset 5 rows
 fetch first 3 rows only;
 id	first_name	last_name	score
 4	John	Smith	6
@@ -539,14 +563,14 @@ id	location	fk
 7	L4	7
 select * from t2
 order by t2.location desc
-offset 1
+offset 1 rows
 fetch first 2 rows with ties;
 id	location	fk
 6	L4	6
 7	L4	7
 select * from t2
 order by t2.location desc
-offset 2
+offset 2 rows
 fetch first 2 rows with ties;
 id	location	fk
 3	L3	3
@@ -569,7 +593,7 @@ id	first_name	last_name	score	location
 select * from (
 select * from t2
 order by t2.location desc
-offset 2
+offset 2 rows
 fetch first 2 rows with ties
 ) temp;
 id	location	fk
@@ -578,7 +602,7 @@ id	location	fk
 4	L3	4
 select * from t2
 order by t2.location desc
-offset 0
+offset 0 rows
 fetch first 2 rows with ties;
 id	location	fk
 5	L4	5
@@ -587,19 +611,19 @@ id	location	fk
 create view v1 as (
 select * from t2
 order by t2.location desc
-offset 0
+offset 0 rows
 fetch first 2 rows with ties
 );
 create view v2 as (
 select * from t2
 order by t2.location desc
-offset 1
+offset 1 rows
 fetch first 2 rows with ties
 );
 create view v3 as (
 select * from t2
 order by t2.location desc
-offset 2
+offset 2 rows
 fetch first row with ties
 );
 select * from v1;
@@ -616,20 +640,20 @@ id	location	fk
 7	L4	7
 show create view v1;
 View	Create View	character_set_client	collation_connection
-v1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS (select `t2`.`id` AS `id`,`t2`.`location` AS `location`,`t2`.`fk` AS `fk` from `t2` order by `t2`.`location` desc offset 0 fetch first 2 rows with ties)	latin1	latin1_swedish_ci
+v1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS (select `t2`.`id` AS `id`,`t2`.`location` AS `location`,`t2`.`fk` AS `fk` from `t2` order by `t2`.`location` desc offset 0 rows  fetch first 2 rows with ties)	latin1	latin1_swedish_ci
 show create view v2;
 View	Create View	character_set_client	collation_connection
-v2	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v2` AS (select `t2`.`id` AS `id`,`t2`.`location` AS `location`,`t2`.`fk` AS `fk` from `t2` order by `t2`.`location` desc offset 1 fetch first 2 rows with ties)	latin1	latin1_swedish_ci
+v2	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v2` AS (select `t2`.`id` AS `id`,`t2`.`location` AS `location`,`t2`.`fk` AS `fk` from `t2` order by `t2`.`location` desc offset 1 rows  fetch first 2 rows with ties)	latin1	latin1_swedish_ci
 show create view v3;
 View	Create View	character_set_client	collation_connection
-v3	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v3` AS (select `t2`.`id` AS `id`,`t2`.`location` AS `location`,`t2`.`fk` AS `fk` from `t2` order by `t2`.`location` desc offset 2 fetch first 1 rows with ties)	latin1	latin1_swedish_ci
+v3	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v3` AS (select `t2`.`id` AS `id`,`t2`.`location` AS `location`,`t2`.`fk` AS `fk` from `t2` order by `t2`.`location` desc offset 2 rows  fetch first 1 rows with ties)	latin1	latin1_swedish_ci
 #
 # Test joins with views and order by referenced from the view.
 #
 select *
 from t1 inner join v1 on t1.id = v1.fk
 order by v1.location desc, t1.first_name
-offset 1
+offset 1 rows
 fetch first 1 rows with ties;
 id	first_name	last_name	score	id	location	fk
 5	John	Smith	7	5	L4	5
@@ -654,7 +678,7 @@ select first_name, last_name, sum(score)
 from t1
 group by first_name, last_name
 order by first_name
-offset 1
+offset 1 rows
 fetch first 2 rows with ties;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	8	Using temporary; Using filesort
@@ -662,7 +686,7 @@ select first_name, last_name, sum(score)
 from t1
 group by first_name, last_name
 order by first_name
-offset 1
+offset 1 rows
 fetch first 2 rows with ties;
 first_name	last_name	sum(score)
 Bob	Trasc	9
@@ -691,7 +715,7 @@ insert into t1 values
 explain select first_name, last_name
 from t1
 order by first_name
-offset 1
+offset 1 rows
 fetch first 2 rows with ties;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	index	NULL	t1_name	206	NULL	3	Using index
@@ -704,7 +728,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 select first_name, last_name
 from t1
 order by first_name
-offset 1
+offset 1 rows
 fetch first 2 rows with ties;
 first_name	last_name
 Bob	Trasc
@@ -727,7 +751,7 @@ John	Doe
 select first_name, last_name
 from t1
 order by first_name desc
-offset 1
+offset 1 rows
 fetch first 2 rows with ties;
 first_name	last_name
 John	Smith
@@ -738,7 +762,7 @@ John	Doe
 select first_name, last_name
 from t1
 order by first_name desc
-offset 4
+offset 4 rows
 fetch first 2 rows with ties;
 first_name	last_name
 John	Elton
@@ -746,7 +770,7 @@ John	Doe
 select first_name, last_name
 from t1
 order by first_name desc
-offset 4
+offset 4 rows
 fetch first 3 rows with ties;
 first_name	last_name
 John	Elton
@@ -846,7 +870,7 @@ fetch first 3 rows with ties
 )
 select * from temp_table
 order by first_name
-offset 1
+offset 1 rows
 fetch first 2 rows with ties;
 first_name	last_name
 Bob	Trasc
@@ -856,7 +880,7 @@ John	Smith
 select first_name, row_number() over () rn
 from t1
 order by rn
-offset 1
+offset 1 rows
 fetch first 2 rows with ties;
 first_name	rn
 Bob	2
@@ -864,7 +888,7 @@ John	3
 select first_name, row_number() over () rn
 from t1
 order by rn desc
-offset 1
+offset 1 rows
 fetch first 2 rows with ties;
 first_name	rn
 John	7
@@ -881,7 +905,7 @@ John	6	2
 select first_name, score, rank() over (ORDER BY score)
 from t1
 order by rank() over (ORDER BY score)
-offset 1
+offset 1 rows
 fetch first 2 rows with ties;
 first_name	score	rank() over (ORDER BY score)
 John	6	2
@@ -963,7 +987,7 @@ id	first_name	last_name	score	id	location	time	value	fk
 select *
 from t1 left join t2 on t1.id = t2.fk
 order by t2.location
-offset 2
+offset 2 rows
 fetch first 1 row with ties;
 id	first_name	last_name	score	id	location	time	value	fk
 1	Alice	Fowler	5	1	home	2020-01-01 10:00:00	10	1
@@ -1008,7 +1032,7 @@ id	first_name	last_name	score
 except
 (select * from t1 order by first_name desc fetch first 3 rows with ties)
 order by first_name
-offset 1
+offset 1 rows
 fetch first 1 row with ties;
 id	first_name	last_name	score
 select sum(score)
@@ -1083,7 +1107,7 @@ a	b
 set @a=-1;
 execute s using @a;
 ERROR HY000: Incorrect arguments to EXECUTE
-prepare s2 from "select a, b from t1 order by a offset ? fetch first ? rows with ties";
+prepare s2 from "select a, b from t1 order by a offset ? rows fetch first ? rows with ties";
 set @offset=1;
 set @limit_count= 2;
 execute s2 using @offset, @limit_count;
diff --git a/mysql-test/main/fetch_first.test b/mysql-test/main/fetch_first.test
index 34898ffe0e2..6995c1cb5b1 100644
--- a/mysql-test/main/fetch_first.test
+++ b/mysql-test/main/fetch_first.test
@@ -18,11 +18,12 @@ insert into t_keyword values (1), (1), (2), (3), (2);
 --echo #
 select * from t1
 order by a
-offset 2;
+offset 2 rows;
+
 
 --sorted_result
 select * from t1
-offset 2;
+offset 2 rows;
 
 
 --echo #
@@ -55,24 +56,48 @@ select * from t1
 fetch next rows only;
 
 --echo #
---echo # Include offset before fetch clause.
+--echo # Row / rows are mandatory after offset <n>
 --echo #
+--error 1064
 select * from t1
 order by a
 offset 2
 fetch first row only;
 
+--error 1064
+select * from t1
+order by a
+offset 2;
+
 select * from t1
 order by a
-offset 2
+offset 2 row
+fetch first row only;
+
+select * from t1
+order by a
+offset 2 rows
+fetch first row only;
+
+--echo #
+--echo # Include offset before fetch clause.
+--echo #
+select * from t1
+order by a
+offset 2 rows
+fetch first row only;
+
+select * from t1
+order by a
+offset 2 rows
 fetch first rows only;
 
 select * from t1
-offset 2
+offset 2 rows
 fetch next row only;
 
 select * from t1
-offset 2
+offset 2 rows
 fetch next rows only;
 
 
@@ -119,22 +144,22 @@ fetch next rows with ties;
 
 select * from t1
 order by a
-offset 2
+offset 2 rows
 fetch first row with ties;
 
 select * from t1
 order by a
-offset 2
+offset 2 rows
 fetch first rows with ties;
 
 select * from t1
 order by a
-offset 2
+offset 2 rows
 fetch next row with ties;
 
 select * from t1
 order by a
-offset 2
+offset 2 rows
 fetch next rows with ties;
 
 
@@ -158,20 +183,20 @@ fetch next 3 rows only;
 --echo #
 select * from t1
 order by a
-offset 2
+offset 2 rows
 fetch first 3 row only;
 
 select * from t1
 order by a
-offset 2
+offset 2 rows
 fetch first 3 rows only;
 
 select * from t1
-offset 2
+offset 2 rows
 fetch next 3 row only;
 
 select * from t1
-offset 2
+offset 2 rows
 fetch next 3 rows only;
 
 
@@ -218,27 +243,27 @@ fetch next 3 rows with ties;
 
 --error ER_WITH_TIES_NEEDS_ORDER
 select * from t1
-offset 2
+offset 2 rows
 fetch first row with ties;
 
 select * from t1
 order by a
-offset 2
+offset 2 rows
 fetch first 3 row with ties;
 
 select * from t1
 order by a
-offset 2
+offset 2 rows
 fetch first 3 rows with ties;
 
 select * from t1
 order by a
-offset 2
+offset 2 rows
 fetch next 3 row with ties;
 
 select * from t1
 order by a
-offset 2
+offset 2 rows
 fetch next 3 rows with ties;
 
 
@@ -309,47 +334,47 @@ fetch first 4 rows with ties;
 
 select * from t1
 order by first_name, last_name
-offset 1
+offset 1 rows
 fetch first 3 rows with ties;
 
 select * from t1
 order by first_name, last_name
-offset 1
+offset 1 rows
 fetch first 3 rows only;
 
 select * from t1
 order by first_name, last_name
-offset 1
+offset 1 rows
 fetch first 3 rows with ties;
 
 select * from t1
 order by first_name, last_name
-offset 2
+offset 2 rows
 fetch first 3 rows only;
 
 select * from t1
 order by first_name, last_name
-offset 2
+offset 2 rows
 fetch first 3 rows with ties;
 
 select * from t1
 order by first_name, last_name
-offset 3
+offset 3 rows
 fetch first 3 rows only;
 
 select * from t1
 order by first_name, last_name
-offset 3
+offset 3 rows
 fetch first 3 rows with ties;
 
 select * from t1
 order by first_name, last_name
-offset 4
+offset 4 rows
 fetch first 3 rows only;
 
 select * from t1
 order by first_name, last_name
-offset 4
+offset 4 rows
 fetch first 3 rows with ties;
 
 --echo #
@@ -357,12 +382,12 @@ fetch first 3 rows with ties;
 --echo #
 select * from t1
 order by first_name, last_name
-offset 5
+offset 5 rows
 fetch first 3 rows with ties;
 
 select * from t1
 order by first_name, last_name
-offset 5
+offset 5 rows
 fetch first 3 rows only;
 
 --echo #
@@ -407,13 +432,13 @@ fetch first 2 rows with ties;
 --sorted_result
 select * from t2
 order by t2.location desc
-offset 1
+offset 1 rows
 fetch first 2 rows with ties;
 
 --sorted_result
 select * from t2
 order by t2.location desc
-offset 2
+offset 2 rows
 fetch first 2 rows with ties;
 
 --echo #
@@ -430,34 +455,34 @@ fetch first 3 rows with ties;
 select * from (
   select * from t2
   order by t2.location desc
-  offset 2
+  offset 2 rows
   fetch first 2 rows with ties
 ) temp;
 
 
 select * from t2
 order by t2.location desc
-offset 0
+offset 0 rows
 fetch first 2 rows with ties;
 
 create view v1 as (
   select * from t2
   order by t2.location desc
-  offset 0
+  offset 0 rows
   fetch first 2 rows with ties
 );
 
 create view v2 as (
   select * from t2
   order by t2.location desc
-  offset 1
+  offset 1 rows
   fetch first 2 rows with ties
 );
 
 create view v3 as (
   select * from t2
   order by t2.location desc
-  offset 2
+  offset 2 rows
   fetch first row with ties
 );
 
@@ -479,7 +504,7 @@ show create view v3;
 select *
 from t1 inner join v1 on t1.id = v1.fk
 order by v1.location desc, t1.first_name
-offset 1
+offset 1 rows
 fetch first 1 rows with ties;
 
 select first_name, last_name, sum(score)
@@ -497,7 +522,7 @@ select first_name, last_name, sum(score)
 from t1
 group by first_name, last_name
 order by first_name
-offset 1
+offset 1 rows
 fetch first 2 rows with ties;
 
 --sorted_result
@@ -505,7 +530,7 @@ select first_name, last_name, sum(score)
 from t1
 group by first_name, last_name
 order by first_name
-offset 1
+offset 1 rows
 fetch first 2 rows with ties;
 
 
@@ -536,7 +561,7 @@ insert into t1 values
 explain select first_name, last_name
 from t1
 order by first_name
-offset 1
+offset 1 rows
 fetch first 2 rows with ties;
 
 explain select first_name, last_name
@@ -548,7 +573,7 @@ fetch first 2 rows with ties;
 select first_name, last_name
 from t1
 order by first_name
-offset 1
+offset 1 rows
 fetch first 2 rows with ties;
 
 select first_name, last_name
@@ -560,19 +585,19 @@ fetch first 2 rows with ties;
 select first_name, last_name
 from t1
 order by first_name desc
-offset 1
+offset 1 rows
 fetch first 2 rows with ties;
 
 select first_name, last_name
 from t1
 order by first_name desc
-offset 4
+offset 4 rows
 fetch first 2 rows with ties;
 
 select first_name, last_name
 from t1
 order by first_name desc
-offset 4
+offset 4 rows
 fetch first 3 rows with ties;
 
 explain select first_name, last_name
@@ -654,20 +679,20 @@ with temp_table as (
 )
 select * from temp_table
 order by first_name
-offset 1
+offset 1 rows
 fetch first 2 rows with ties;
 
 
 select first_name, row_number() over () rn
 from t1
 order by rn
-offset 1
+offset 1 rows
 fetch first 2 rows with ties;
 
 select first_name, row_number() over () rn
 from t1
 order by rn desc
-offset 1
+offset 1 rows
 fetch first 2 rows with ties;
 
 select first_name, score, rank() over (ORDER BY score)
@@ -678,7 +703,7 @@ fetch first 3 rows with ties;
 select first_name, score, rank() over (ORDER BY score)
 from t1
 order by rank() over (ORDER BY score)
-offset 1
+offset 1 rows
 fetch first 2 rows with ties;
 
 select first_name, score, rank() over (ORDER BY score)
@@ -732,7 +757,7 @@ fetch first 3 row with ties;
 select *
 from t1 left join t2 on t1.id = t2.fk
 order by t2.location
-offset 2
+offset 2 rows
 fetch first 1 row with ties;
 
 
@@ -761,7 +786,7 @@ fetch first 1 row with ties;
 except
 (select * from t1 order by first_name desc fetch first 3 rows with ties)
 order by first_name
-offset 1
+offset 1 rows
 fetch first 1 row with ties;
 
 --sorted_result
@@ -827,7 +852,7 @@ set @a=-1;
 --error ER_WRONG_ARGUMENTS
 execute s using @a;
 
-prepare s2 from "select a, b from t1 order by a offset ? fetch first ? rows with ties";
+prepare s2 from "select a, b from t1 order by a offset ? rows fetch first ? rows with ties";
 set @offset=1;
 set @limit_count= 2;
 execute s2 using @offset, @limit_count;