MDEV-23908: Implement SELECT ... OFFSET ... FETCH ...

This commit implements the standard SQL extension
OFFSET start { ROW | ROWS }
[FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } { ONLY | WITH TIES }]

To achieve this a reserved keyword OFFSET is introduced.

The general logic for WITH TIES implies:
1. The number of rows a query returns is no longer known during optimize
   phase. Adjust optimizations to no longer consider this.
2. During end_send make use of an "order Cached_item"to compare if the
   ORDER BY columns changed. Keep returning rows until there is a
   change. This happens only after we reached the row limit.
3. Within end_send_group, the order by clause was eliminated. It is
   still possible to keep the optimization of using end_send_group for
   producing the final result set.

8 files changed, 2326 insertions, 6 deletions

diff --git a/mysql-test/main/fetch_first.result b/mysql-test/main/fetch_first.result
new file mode 100644
index 00000000000..88cd65123fa
--- /dev/null
+++ b/mysql-test/main/fetch_first.result
@@ -0,0 +1,1312 @@
+#
+# The following entries are meant for testing the parser, ensuring
+# the right values are passed down to the executor, for all possible
+# syntax combinations.
+#
+# Test basic syntax.
+#
+create table t1 (a int);
+create table t_keyword (`offset` int);
+insert into t1 values (1), (1), (2), (3), (2);
+insert into t_keyword values (1), (1), (2), (3), (2);
+#
+# Make sure the FETCH clause addition didn't introduce problems with
+# the offset keyword.
+#
+select * from t1
+order by a
+offset 2;
+a
+2
+2
+3
+select * from t1
+offset 2;
+a
+2
+2
+3
+#
+# Offset is now a reserved keyword. Column names can not have that name
+# without escaping the identifier.
+#
+select * from t_keyword
+order by offset;
+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 'offset' at line 2
+select * from t_keyword
+order by `offset`;
+offset
+1
+1
+2
+2
+3
+#
+# Test syntax without a specific limit. (implicit one row)
+# Test the alias between row / rows and first / next.
+# Test ONLY vs WITH TIES.
+#
+select * from t1
+fetch first row only;
+a
+1
+select * from t1
+fetch first rows only;
+a
+1
+select * from t1
+fetch next row only;
+a
+1
+select * from t1
+fetch next rows only;
+a
+1
+#
+# Include offset before fetch clause.
+#
+select * from t1
+order by a
+offset 2
+fetch first row only;
+a
+2
+select * from t1
+order by a
+offset 2
+fetch first rows only;
+a
+2
+select * from t1
+offset 2
+fetch next row only;
+a
+2
+select * from t1
+offset 2
+fetch next rows only;
+a
+2
+#
+# Repeat the tests, but now with WITH TIES.
+# WITH TIES requires order by.
+#
+select * from t1
+fetch first row with ties;
+ERROR HY000: FETCH ... WITH TIES requires ORDER BY clause to be present
+select * from t1
+fetch first rows with ties;
+ERROR HY000: FETCH ... WITH TIES requires ORDER BY clause to be present
+select * from t1
+fetch next row with ties;
+ERROR HY000: FETCH ... WITH TIES requires ORDER BY clause to be present
+select * from t1
+fetch next rows with ties;
+ERROR HY000: FETCH ... WITH TIES requires ORDER BY clause to be present
+select * from t1
+order by a
+fetch first row with ties;
+a
+1
+1
+select * from t1
+order by a
+fetch first rows with ties;
+a
+1
+1
+select * from t1
+order by a
+fetch next row with ties;
+a
+1
+1
+select * from t1
+order by a
+fetch next rows with ties;
+a
+1
+1
+#
+# Include offset before fetch clause.
+#
+select * from t1
+order by a
+offset 2
+fetch first row with ties;
+a
+2
+2
+select * from t1
+order by a
+offset 2
+fetch first rows with ties;
+a
+2
+2
+select * from t1
+order by a
+offset 2
+fetch next row with ties;
+a
+2
+2
+select * from t1
+order by a
+offset 2
+fetch next rows with ties;
+a
+2
+2
+#
+# Test syntax with a specific limit
+#
+select * from t1
+fetch first 3 row only;
+a
+1
+1
+2
+select * from t1
+fetch first 3 rows only;
+a
+1
+1
+2
+select * from t1
+fetch next 3 row only;
+a
+1
+1
+2
+select * from t1
+fetch next 3 rows only;
+a
+1
+1
+2
+#
+# Include offset before fetch clause.
+#
+select * from t1
+order by a
+offset 2
+fetch first 3 row only;
+a
+2
+2
+3
+select * from t1
+order by a
+offset 2
+fetch first 3 rows only;
+a
+2
+2
+3
+select * from t1
+offset 2
+fetch next 3 row only;
+a
+2
+3
+2
+select * from t1
+offset 2
+fetch next 3 rows only;
+a
+2
+3
+2
+#
+# Repeat the tests, but now with WITH TIES.
+# WITH TIES requires order by.
+#
+select * from t1
+fetch first 3 row with ties;
+ERROR HY000: FETCH ... WITH TIES requires ORDER BY clause to be present
+select * from t1
+fetch first 3 rows with ties;
+ERROR HY000: FETCH ... WITH TIES requires ORDER BY clause to be present
+select * from t1
+fetch next 3 row with ties;
+ERROR HY000: FETCH ... WITH TIES requires ORDER BY clause to be present
+select * from t1
+fetch next 3 rows with ties;
+ERROR HY000: FETCH ... WITH TIES requires ORDER BY clause to be present
+select * from t1
+order by a
+fetch first 3 row with ties;
+a
+1
+1
+2
+2
+select * from t1
+order by a
+fetch first 3 rows with ties;
+a
+1
+1
+2
+2
+select * from t1
+order by a
+fetch next 3 row with ties;
+a
+1
+1
+2
+2
+select * from t1
+order by a
+fetch next 3 rows with ties;
+a
+1
+1
+2
+2
+#
+# Include offset before fetch clause.
+#
+select * from t1
+offset 2
+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
+fetch first 3 row with ties;
+a
+2
+2
+3
+select * from t1
+order by a
+offset 2
+fetch first 3 rows with ties;
+a
+2
+2
+3
+select * from t1
+order by a
+offset 2
+fetch next 3 row with ties;
+a
+2
+2
+3
+select * from t1
+order by a
+offset 2
+fetch next 3 rows with ties;
+a
+2
+2
+3
+drop table t1;
+drop table t_keyword;
+#
+# Test behaviour with a simple select.
+#
+create table t1 (id int, first_name varchar(100), last_name varchar(100), score double);
+insert into t1 values
+(1, 'Alice', 'Fowler', 5),
+(2, 'John', 'Doe', 6),
+(3, 'John', 'Smith', 6),
+(4, 'John', 'Smith', 6),
+(5, 'John', 'Smith', 7),
+(6, 'John', 'Elton', 8.1),
+(7, 'Bob',  'Trasc', 9),
+(8, 'Silvia', 'Ganush', 10);
+create table t2
+(id int,
+location varchar(100),
+fk int,
+constraint `fk_t1`
+     FOREIGN KEY (fk) REFERENCES t1 (id)
+ON DELETE CASCADE
+ON UPDATE RESTRICT);
+insert into t2 values
+(1, 'L1', 1),
+(2, 'L2', 2),
+(3, 'L3', 3),
+(4, 'L3', 4),
+(5, 'L4', 5),
+(6, 'L4', 6),
+(7, 'L4', 7),
+(7, null, 8);
+select * from t1
+order by id
+fetch first 3 rows only;
+id	first_name	last_name	score
+1	Alice	Fowler	5
+2	John	Doe	6
+3	John	Smith	6
+select * from t1
+order by id
+fetch first 3 rows with ties;
+id	first_name	last_name	score
+1	Alice	Fowler	5
+2	John	Doe	6
+3	John	Smith	6
+select * from t1
+order by first_name
+fetch first 3 rows only;
+id	first_name	last_name	score
+1	Alice	Fowler	5
+7	Bob	Trasc	9
+3	John	Smith	6
+select * from t1
+order by first_name
+fetch first 3 rows with ties;
+id	first_name	last_name	score
+1	Alice	Fowler	5
+7	Bob	Trasc	9
+2	John	Doe	6
+3	John	Smith	6
+4	John	Smith	6
+5	John	Smith	7
+6	John	Elton	8.1
+#
+# Test multi-part order by.
+#
+select * from t1
+order by first_name, last_name
+fetch first 3 rows with ties;
+id	first_name	last_name	score
+1	Alice	Fowler	5
+7	Bob	Trasc	9
+2	John	Doe	6
+select * from t1
+order by first_name, last_name
+fetch first 4 rows with ties;
+id	first_name	last_name	score
+1	Alice	Fowler	5
+7	Bob	Trasc	9
+2	John	Doe	6
+6	John	Elton	8.1
+select * from t1
+order by first_name, last_name
+offset 1
+fetch first 3 rows with ties;
+id	first_name	last_name	score
+7	Bob	Trasc	9
+2	John	Doe	6
+6	John	Elton	8.1
+select * from t1
+order by first_name, last_name
+offset 1
+fetch first 3 rows only;
+id	first_name	last_name	score
+7	Bob	Trasc	9
+2	John	Doe	6
+6	John	Elton	8.1
+select * from t1
+order by first_name, last_name
+offset 1
+fetch first 3 rows with ties;
+id	first_name	last_name	score
+7	Bob	Trasc	9
+2	John	Doe	6
+6	John	Elton	8.1
+select * from t1
+order by first_name, last_name
+offset 2
+fetch first 3 rows only;
+id	first_name	last_name	score
+2	John	Doe	6
+6	John	Elton	8.1
+5	John	Smith	7
+select * from t1
+order by first_name, last_name
+offset 2
+fetch first 3 rows with ties;
+id	first_name	last_name	score
+2	John	Doe	6
+6	John	Elton	8.1
+3	John	Smith	6
+4	John	Smith	6
+5	John	Smith	7
+select * from t1
+order by first_name, last_name
+offset 3
+fetch first 3 rows only;
+id	first_name	last_name	score
+6	John	Elton	8.1
+3	John	Smith	6
+4	John	Smith	6
+select * from t1
+order by first_name, last_name
+offset 3
+fetch first 3 rows with ties;
+id	first_name	last_name	score
+6	John	Elton	8.1
+3	John	Smith	6
+4	John	Smith	6
+5	John	Smith	7
+select * from t1
+order by first_name, last_name
+offset 4
+fetch first 3 rows only;
+id	first_name	last_name	score
+3	John	Smith	6
+4	John	Smith	6
+5	John	Smith	7
+select * from t1
+order by first_name, last_name
+offset 4
+fetch first 3 rows with ties;
+id	first_name	last_name	score
+3	John	Smith	6
+4	John	Smith	6
+5	John	Smith	7
+#
+# Test offset crossing into a new peer-group.
+#
+select * from t1
+order by first_name, last_name
+offset 5
+fetch first 3 rows with ties;
+id	first_name	last_name	score
+4	John	Smith	6
+5	John	Smith	7
+8	Silvia	Ganush	10
+select * from t1
+order by first_name, last_name
+offset 5
+fetch first 3 rows only;
+id	first_name	last_name	score
+4	John	Smith	6
+5	John	Smith	7
+8	Silvia	Ganush	10
+#
+# Simple join with 2 tables, order by without columns in the
+# second table and also with columns in the second table.
+#
+# Cover both only and with ties.
+#
+select t1.id, t1.first_name, t1.last_name, t1.score, t2.location
+from t1 inner join t2 on t1.id = t2.fk
+order by t1.first_name, t1.last_name
+fetch first 3 rows only;
+id	first_name	last_name	score	location
+1	Alice	Fowler	5	L1
+7	Bob	Trasc	9	L4
+2	John	Doe	6	L2
+select t1.id, t1.first_name, t1.last_name, t1.score, t2.location
+from t1 inner join t2 on t1.id = t2.fk
+order by t2.location, t1.first_name, t1.last_name
+fetch first 3 rows only;
+id	first_name	last_name	score	location
+8	Silvia	Ganush	10	NULL
+1	Alice	Fowler	5	L1
+2	John	Doe	6	L2
+select t1.id, t1.first_name, t1.last_name, t1.score, t2.location
+from t1 inner join t2 on t1.id = t2.fk
+order by t1.first_name, t1.last_name
+fetch first 3 rows with ties;
+id	first_name	last_name	score	location
+1	Alice	Fowler	5	L1
+7	Bob	Trasc	9	L4
+2	John	Doe	6	L2
+select t1.id, t1.first_name, t1.last_name, t1.score, t2.location
+from t1 inner join t2 on t1.id = t2.fk
+order by t2.location, t1.first_name, t1.last_name
+fetch first 3 rows with ties;
+id	first_name	last_name	score	location
+8	Silvia	Ganush	10	NULL
+1	Alice	Fowler	5	L1
+2	John	Doe	6	L2
+#
+# Test descending order by.
+#
+select t1.id, t1.first_name, t1.last_name, t1.score, t2.location
+from t1 inner join t2 on t1.id = t2.fk
+order by t2.location desc, t1.first_name, t1.last_name
+fetch first 3 rows only;
+id	first_name	last_name	score	location
+7	Bob	Trasc	9	L4
+6	John	Elton	8.1	L4
+5	John	Smith	7	L4
+select * from t2
+order by t2.location desc
+fetch first 2 rows with ties;
+id	location	fk
+5	L4	5
+6	L4	6
+7	L4	7
+select * from t2
+order by t2.location desc
+offset 1
+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
+fetch first 2 rows with ties;
+id	location	fk
+3	L3	3
+4	L3	4
+7	L4	7
+#
+# Test a join with descending order by.
+#
+select t1.id, t1.first_name, t1.last_name, t1.score, t2.location
+from t1 inner join t2 on t1.id = t2.fk
+order by t2.location desc, t1.first_name, t1.last_name
+fetch first 3 rows with ties;
+id	first_name	last_name	score	location
+7	Bob	Trasc	9	L4
+6	John	Elton	8.1	L4
+5	John	Smith	7	L4
+#
+# Test subqueries.
+#
+select * from (
+select * from t2
+order by t2.location desc
+offset 2
+fetch first 2 rows with ties
+) temp;
+id	location	fk
+7	L4	7
+3	L3	3
+4	L3	4
+select * from t2
+order by t2.location desc
+offset 0
+fetch first 2 rows with ties;
+id	location	fk
+5	L4	5
+6	L4	6
+7	L4	7
+create view v1 as (
+select * from t2
+order by t2.location desc
+offset 0
+fetch first 2 rows with ties
+);
+create view v2 as (
+select * from t2
+order by t2.location desc
+offset 1
+fetch first 2 rows with ties
+);
+create view v3 as (
+select * from t2
+order by t2.location desc
+offset 2
+fetch first row with ties
+);
+select * from v1;
+id	location	fk
+5	L4	5
+6	L4	6
+7	L4	7
+select * from v2;
+id	location	fk
+6	L4	6
+7	L4	7
+select * from v3;
+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
+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
+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
+#
+# 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
+fetch first 1 rows with ties;
+id	first_name	last_name	score	id	location	fk
+5	John	Smith	7	5	L4	5
+6	John	Elton	8.1	6	L4	6
+select first_name, last_name, sum(score)
+from t1
+group by first_name, last_name
+order by first_name;
+first_name	last_name	sum(score)
+Alice	Fowler	5
+Bob	Trasc	9
+John	Doe	6
+John	Elton	8.1
+John	Smith	19
+Silvia	Ganush	10
+#
+# Test with ties with group by. Make sure that if order by is a less
+# specific sort of group by, that WITH TIES still gets executed.
+#
+explain
+select first_name, last_name, sum(score)
+from t1
+group by first_name, last_name
+order by first_name
+offset 1
+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
+select first_name, last_name, sum(score)
+from t1
+group by first_name, last_name
+order by first_name
+offset 1
+fetch first 2 rows with ties;
+first_name	last_name	sum(score)
+Bob	Trasc	9
+John	Doe	6
+John	Elton	8.1
+John	Smith	19
+drop table t1;
+drop table t2;
+drop view v1;
+drop view v2;
+drop view v3;
+#
+# Test with ties when an index is used to provide the order by.
+#
+create table t1 (id int, first_name varchar(100), last_name varchar(100), score double);
+create index t1_name on t1 (first_name, last_name);
+insert into t1 values
+(1, 'Alice', 'Fowler', 5),
+(2, 'John', 'Doe', 6),
+(3, 'John', 'Smith', 6),
+(4, 'John', 'Smith', 6),
+(5, 'John', 'Smith', 7),
+(6, 'John', 'Elton', 8.1),
+(7, 'Bob',  'Trasc', 9),
+(8, 'Silvia', 'Ganush', 10);
+explain select first_name, last_name
+from t1
+order by first_name
+offset 1
+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
+explain select first_name, last_name
+from t1
+order by first_name desc
+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	2	Using index
+select first_name, last_name
+from t1
+order by first_name
+offset 1
+fetch first 2 rows with ties;
+first_name	last_name
+Bob	Trasc
+John	Doe
+John	Elton
+John	Smith
+John	Smith
+John	Smith
+select first_name, last_name
+from t1
+order by first_name desc
+fetch first 2 rows with ties;
+first_name	last_name
+Silvia	Ganush
+John	Smith
+John	Smith
+John	Smith
+John	Elton
+John	Doe
+select first_name, last_name
+from t1
+order by first_name desc
+offset 1
+fetch first 2 rows with ties;
+first_name	last_name
+John	Smith
+John	Smith
+John	Smith
+John	Elton
+John	Doe
+select first_name, last_name
+from t1
+order by first_name desc
+offset 4
+fetch first 2 rows with ties;
+first_name	last_name
+John	Elton
+John	Doe
+select first_name, last_name
+from t1
+order by first_name desc
+offset 4
+fetch first 3 rows with ties;
+first_name	last_name
+John	Elton
+John	Doe
+Bob	Trasc
+explain select first_name, last_name
+from t1
+where first_name != 'John'
+order by first_name
+fetch first 2 rows with ties;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	t1_name	t1_name	103	NULL	3	Using where; Using index
+select first_name, last_name
+from t1
+where first_name != 'John'
+order by first_name
+fetch first 2 rows with ties;
+first_name	last_name
+Alice	Fowler
+Bob	Trasc
+select first_name, last_name
+from t1
+where first_name != 'John'
+group by first_name, last_name
+order by first_name
+fetch first 2 rows with ties;
+first_name	last_name
+Alice	Fowler
+Bob	Trasc
+#
+# Test CTE support.
+#
+explain with temp_table as (
+select first_name, last_name
+from t1
+where first_name != 'John'
+  group by first_name, last_name
+order by first_name
+fetch first 2 rows with ties
+)
+select * from temp_table
+order by first_name, last_name;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	2	Using filesort
+2	DERIVED	t1	range	t1_name	t1_name	103	NULL	3	Using where; Using index
+with temp_table as (
+select first_name, last_name
+from t1
+group by first_name, last_name
+order by first_name
+fetch first 3 rows with ties
+)
+select * from temp_table
+order by first_name, last_name;
+first_name	last_name
+Alice	Fowler
+Bob	Trasc
+John	Doe
+John	Elton
+John	Smith
+with temp_table as (
+select first_name, last_name
+from t1
+group by first_name, last_name
+order by first_name
+fetch first 3 rows with ties
+)
+select * from temp_table
+order by last_name
+fetch first 3 rows with ties;
+first_name	last_name
+John	Doe
+John	Elton
+Alice	Fowler
+with temp_table as (
+select first_name, last_name
+from t1
+group by first_name, last_name
+order by first_name
+fetch first 3 rows with ties
+)
+select * from temp_table
+order by first_name
+fetch first 3 rows with ties;
+first_name	last_name
+Alice	Fowler
+Bob	Trasc
+John	Doe
+John	Elton
+John	Smith
+with temp_table as (
+select first_name, last_name
+from t1
+group by first_name, last_name
+order by first_name
+fetch first 3 rows with ties
+)
+select * from temp_table
+order by first_name
+offset 1
+fetch first 2 rows with ties;
+first_name	last_name
+Bob	Trasc
+John	Doe
+John	Elton
+John	Smith
+select first_name, row_number() over () rn
+from t1
+order by rn
+offset 1
+fetch first 2 rows with ties;
+first_name	rn
+Bob	2
+John	3
+select first_name, row_number() over () rn
+from t1
+order by rn desc
+offset 1
+fetch first 2 rows with ties;
+first_name	rn
+John	7
+John	6
+select first_name, score, rank() over (ORDER BY score)
+from t1
+order by rank() over (ORDER BY score)
+fetch first 3 rows with ties;
+first_name	score	rank() over (ORDER BY score)
+Alice	5	1
+John	6	2
+John	6	2
+John	6	2
+select first_name, score, rank() over (ORDER BY score)
+from t1
+order by rank() over (ORDER BY score)
+offset 1
+fetch first 2 rows with ties;
+first_name	score	rank() over (ORDER BY score)
+John	6	2
+John	6	2
+John	6	2
+select first_name, score, rank() over (ORDER BY score)
+from t1
+order by rank() over (ORDER BY score)
+fetch first 6 rows with ties;
+first_name	score	rank() over (ORDER BY score)
+Alice	5	1
+John	6	2
+John	6	2
+John	6	2
+John	7	5
+John	8.1	6
+#
+# Test nulls.
+#
+create table t2 (id int, location varchar(100), time datetime, value int, fk int);
+insert into t2 values (1, 'home', '2020-01-01 10:00', 10, 1);
+insert into t2 values (2, 'home', '2020-01-01 11:00', 11, 2);
+insert into t2 values (3, 'home', '2020-01-01 12:00', 12, 3);
+insert into t2 values (4, 'home', '2020-01-01 13:00', 13, 3);
+insert into t2 values (5, 'home', '2020-01-01 14:00', 13, 3);
+insert into t2 values (6, 'home', '2020-01-01 15:00', 13, 2);
+insert into t2 values (7, 'home', '2020-01-01 16:00', 13, 6);
+insert into t2 values (8, 'outside', '2020-01-01 17:00', 17, 6);
+insert into t2 values (9, 'outside', '2020-01-01 18:00', 17, 6);
+insert into t2 values (10, 'outside', '2020-01-01 19:00', 17, 8);
+insert into t2 values (11, 'outside', '2020-01-01 20:00', 16, 7);
+insert into t2 values (12, 'outside', '2020-01-01 21:00', 16, 7);
+select *
+from t1 left join t2 on t1.id = t2.fk
+order by t2.location;
+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
+2	John	Doe	6	2	home	2020-01-01 11:00:00	11	2
+2	John	Doe	6	6	home	2020-01-01 15:00:00	13	2
+3	John	Smith	6	3	home	2020-01-01 12:00:00	12	3
+3	John	Smith	6	4	home	2020-01-01 13:00:00	13	3
+3	John	Smith	6	5	home	2020-01-01 14:00:00	13	3
+4	John	Smith	6	NULL	NULL	NULL	NULL	NULL
+5	John	Smith	7	NULL	NULL	NULL	NULL	NULL
+6	John	Elton	8.1	7	home	2020-01-01 16:00:00	13	6
+6	John	Elton	8.1	8	outside	2020-01-01 17:00:00	17	6
+6	John	Elton	8.1	9	outside	2020-01-01 18:00:00	17	6
+7	Bob	Trasc	9	11	outside	2020-01-01 20:00:00	16	7
+7	Bob	Trasc	9	12	outside	2020-01-01 21:00:00	16	7
+8	Silvia	Ganush	10	10	outside	2020-01-01 19:00:00	17	8
+select *
+from t1 left join t2 on t1.id = t2.fk
+order by t2.location
+fetch first 1 row with ties;
+id	first_name	last_name	score	id	location	time	value	fk
+4	John	Smith	6	NULL	NULL	NULL	NULL	NULL
+5	John	Smith	7	NULL	NULL	NULL	NULL	NULL
+select *
+from t1 left join t2 on t1.id = t2.fk
+order by t2.location
+fetch first 2 row with ties;
+id	first_name	last_name	score	id	location	time	value	fk
+4	John	Smith	6	NULL	NULL	NULL	NULL	NULL
+5	John	Smith	7	NULL	NULL	NULL	NULL	NULL
+select *
+from t1 left join t2 on t1.id = t2.fk
+order by t2.location
+fetch first 3 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
+2	John	Doe	6	2	home	2020-01-01 11:00:00	11	2
+2	John	Doe	6	6	home	2020-01-01 15:00:00	13	2
+3	John	Smith	6	3	home	2020-01-01 12:00:00	12	3
+3	John	Smith	6	4	home	2020-01-01 13:00:00	13	3
+3	John	Smith	6	5	home	2020-01-01 14:00:00	13	3
+4	John	Smith	6	NULL	NULL	NULL	NULL	NULL
+5	John	Smith	7	NULL	NULL	NULL	NULL	NULL
+6	John	Elton	8.1	7	home	2020-01-01 16:00:00	13	6
+select *
+from t1 left join t2 on t1.id = t2.fk
+order by t2.location
+offset 2
+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
+2	John	Doe	6	2	home	2020-01-01 11:00:00	11	2
+2	John	Doe	6	6	home	2020-01-01 15:00:00	13	2
+3	John	Smith	6	3	home	2020-01-01 12:00:00	12	3
+3	John	Smith	6	4	home	2020-01-01 13:00:00	13	3
+3	John	Smith	6	5	home	2020-01-01 14:00:00	13	3
+6	John	Elton	8.1	7	home	2020-01-01 16:00:00	13	6
+#
+# Test union-like operator with multiple fetch first clauses.
+#
+select * from t1 order by 1 fetch first 3 rows with ties;
+id	first_name	last_name	score
+1	Alice	Fowler	5
+2	John	Doe	6
+3	John	Smith	6
+select * from t1 order by first_name desc fetch first 3 rows with ties;
+id	first_name	last_name	score
+8	Silvia	Ganush	10
+2	John	Doe	6
+3	John	Smith	6
+4	John	Smith	6
+5	John	Smith	7
+6	John	Elton	8.1
+(select * from t1 order by 1 fetch first 3 rows with ties)
+intersect
+(select * from t1 order by first_name desc fetch first 3 rows with ties)
+order by first_name
+fetch first 1 row with ties;
+id	first_name	last_name	score
+2	John	Doe	6
+3	John	Smith	6
+(select * from t1 order by 1 fetch first 3 rows with ties)
+except
+(select * from t1 order by first_name desc fetch first 3 rows with ties)
+order by first_name
+fetch first 1 row with ties;
+id	first_name	last_name	score
+1	Alice	Fowler	5
+(select * from t1 order by 1 fetch first 3 rows with ties)
+except
+(select * from t1 order by first_name desc fetch first 3 rows with ties)
+order by first_name
+offset 1
+fetch first 1 row with ties;
+id	first_name	last_name	score
+select sum(score)
+from t1
+order by 1
+fetch first 2 rows with ties;
+sum(score)
+57.1
+select sum(score)
+from t1
+group by id
+order by 1
+fetch first 2 rows with ties;
+sum(score)
+5
+6
+6
+6
+drop table t1;
+drop table t2;
+#
+# Test SQL_CALC_FOUND_ROWS
+#
+create table t1 (a int, b int, index (a,b));
+insert into t1 values (1,1), (1,2), (1,3), (2,1), (2,2), (3,1);
+select SQL_CALC_FOUND_ROWS a, b, count(*)
+from t1
+group by a, b
+order by a
+fetch first 1 rows with ties;
+a	b	count(*)
+1	1	1
+1	2	1
+1	3	1
+SELECT FOUND_ROWS();
+FOUND_ROWS()
+6
+select SQL_CALC_FOUND_ROWS a, b
+from t1
+order by a
+fetch first 1 rows with ties;
+a	b
+1	1
+1	2
+1	3
+SELECT FOUND_ROWS();
+FOUND_ROWS()
+6
+#
+# Test index read optimization with ORDER BY as sub part of GROUP BY
+#
+explain select a, b, count(*)
+from t1
+group by a, b
+order by a;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index	NULL	a	10	NULL	6	Using index
+explain select a, b, count(*)
+from t1
+group by a, b
+order by a
+fetch first 1 rows with ties;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index	NULL	a	10	NULL	1	Using index
+#
+# Test FETCH ... WITH TIES / ONLY with prepared statements
+#
+prepare s from "select a, b from t1 order by a fetch first ? rows with ties";
+set @a='qwe';
+execute s using @a;
+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";
+set @offset=1;
+set @limit_count= 2;
+execute s2 using @offset, @limit_count;
+a	b
+1	2
+1	3
+execute s2 using @offset, 3;
+a	b
+1	2
+1	3
+2	1
+2	2
+execute s2 using -1, 3;
+ERROR HY000: Incorrect arguments to EXECUTE
+#
+# Test with ties with a constant order by.
+#
+select a, b
+from t1
+order by 'x'
+fetch first 2 rows with ties;
+a	b
+1	1
+1	2
+1	3
+2	1
+2	2
+3	1
+select b, sum(a)
+from t1
+group by b
+order by 'x', 'y', 'z'
+fetch first 2 rows with ties;
+b	sum(a)
+1	6
+2	3
+3	1
+#
+# Test with ties when group by is used and order by gets eliminated.
+#
+select b, sum(a)
+from t1
+group by b
+order by 'x', 'y', 'z'
+fetch first 2 rows with ties;
+b	sum(a)
+1	6
+2	3
+3	1
+#
+# Test an ill formed SELECT using MySQL/MariaDBs group by extension
+# of not requiring all non-aggregate fields to be part of group
+# by.
+#
+select b, a, sum(a)
+from t1
+group by a
+order by b, 'x', 'y', 'z'
+fetch first 2 rows with ties;
+b	a	sum(a)
+1	1	3
+1	2	4
+1	3	3
+delete from t1;
+insert into t1 values (1,1), (1,2), (1,3), (2,1), (2,2), (3,2), (3, 3);
+select b, a, sum(a)
+from t1
+group by a
+order by b, 'x', 'y', 'z'
+fetch first 1 rows with ties;
+b	a	sum(a)
+1	1	3
+1	2	4
+select b, a, sum(a)
+from t1
+group by a
+order by 'x', b, 'x', 'y', 'z'
+fetch first 1 rows with ties;
+b	a	sum(a)
+1	1	3
+1	2	4
+select b, a, sum(a)
+from t1
+group by a
+order by 'x', 'y', b
+fetch first 1 rows with ties;
+b	a	sum(a)
+1	1	3
+1	2	4
+#
+# Test with ties when order by a const column is used.
+#
+select a, b
+from t1
+order by a, 'a'
+fetch first 2 rows with ties;
+a	b
+1	1
+1	2
+1	3
+#
+# Order by aggregates.
+#
+select a, b, sum(a)
+from t1
+group by a, b
+order by sum(a)
+fetch first 1 rows with ties;
+a	b	sum(a)
+1	1	1
+1	2	1
+1	3	1
+# Test different sum function in order by, not present in select list.
+select a, b, sum(a)
+from t1
+group by a, b
+order by sum(b)
+fetch first 1 rows with ties;
+a	b	sum(a)
+1	1	1
+2	1	2
+# Test aggregates with constant columns in order by.
+select a, b, sum(a)
+from t1
+group by a, b
+order by a
+fetch first 1 rows with ties;
+a	b	sum(a)
+1	1	1
+1	2	1
+1	3	1
+select a, b, sum(a)
+from t1
+group by a, b
+order by a, 'x'
+fetch first 1 rows with ties;
+a	b	sum(a)
+1	1	1
+1	2	1
+1	3	1
+select a, b, sum(a)
+from t1
+group by a, b
+order by a, b
+fetch first 1 rows with ties;
+a	b	sum(a)
+1	1	1
+select a, b, sum(a)
+from t1
+group by a, b
+order by a, b
+fetch first 2 rows with ties;
+a	b	sum(a)
+1	1	1
+1	2	1
+select a, b, sum(a)
+from t1
+group by a, b
+order by a, b, 'x'
+fetch first 1 rows with ties;
+a	b	sum(a)
+1	1	1
+select a, b, sum(a)
+from t1
+group by a, 'x', b
+order by a, b, 'x'
+fetch first 1 rows with ties;
+a	b	sum(a)
+1	1	1
+select a, b, sum(a)
+from t1
+group by 'x', a, b
+order by a, b, 'x'
+fetch first 1 rows with ties;
+a	b	sum(a)
+1	1	1
+select a, b, sum(a)
+from t1
+group by a, b
+order by 'x', a, 'x', b, 't', b
+fetch first 1 rows with ties;
+a	b	sum(a)
+1	1	1
+select a, b, sum(a)
+from t1
+group by a, b
+order by b, 't', b
+fetch first 1 rows with ties;
+a	b	sum(a)
+1	1	1
+2	1	2
+#
+# Test with subqueries in order by
+#
+select a, b, sum(a)
+from t1
+group by a, b
+order by (select 1)
+fetch first 1 rows with ties;
+a	b	sum(a)
+1	1	1
+1	2	1
+1	3	1
+2	1	2
+2	2	2
+3	2	3
+3	3	3
+select a, b, sum(a)
+from t1
+group by a, b
+order by (select 1), a
+fetch first 1 rows with ties;
+a	b	sum(a)
+1	1	1
+1	2	1
+1	3	1
+select a, b, sum(a)
+from t1
+group by a, b
+order by (select 1), a
+fetch first 1 rows with ties;
+a	b	sum(a)
+1	1	1
+1	2	1
+1	3	1
+drop table t1;
diff --git a/mysql-test/main/fetch_first.test b/mysql-test/main/fetch_first.test
new file mode 100644
index 00000000000..34898ffe0e2
--- /dev/null
+++ b/mysql-test/main/fetch_first.test
@@ -0,0 +1,1008 @@
+--echo #
+--echo # The following entries are meant for testing the parser, ensuring
+--echo # the right values are passed down to the executor, for all possible
+--echo # syntax combinations.
+--echo #
+--echo # Test basic syntax.
+--echo #
+
+create table t1 (a int);
+create table t_keyword (`offset` int);
+
+insert into t1 values (1), (1), (2), (3), (2);
+insert into t_keyword values (1), (1), (2), (3), (2);
+
+--echo #
+--echo # Make sure the FETCH clause addition didn't introduce problems with
+--echo # the offset keyword.
+--echo #
+select * from t1
+order by a
+offset 2;
+
+--sorted_result
+select * from t1
+offset 2;
+
+
+--echo #
+--echo # Offset is now a reserved keyword. Column names can not have that name
+--echo # without escaping the identifier.
+--echo #
+--error 1064
+select * from t_keyword
+order by offset;
+
+select * from t_keyword
+order by `offset`;
+
+
+--echo #
+--echo # Test syntax without a specific limit. (implicit one row)
+--echo # Test the alias between row / rows and first / next.
+--echo # Test ONLY vs WITH TIES.
+--echo #
+select * from t1
+fetch first row only;
+
+select * from t1
+fetch first rows only;
+
+select * from t1
+fetch next row only;
+
+select * from t1
+fetch next rows only;
+
+--echo #
+--echo # Include offset before fetch clause.
+--echo #
+select * from t1
+order by a
+offset 2
+fetch first row only;
+
+select * from t1
+order by a
+offset 2
+fetch first rows only;
+
+select * from t1
+offset 2
+fetch next row only;
+
+select * from t1
+offset 2
+fetch next rows only;
+
+
+--echo #
+--echo # Repeat the tests, but now with WITH TIES.
+--echo # WITH TIES requires order by.
+--echo #
+
+--error ER_WITH_TIES_NEEDS_ORDER
+select * from t1
+fetch first row with ties;
+
+--error ER_WITH_TIES_NEEDS_ORDER
+select * from t1
+fetch first rows with ties;
+
+--error ER_WITH_TIES_NEEDS_ORDER
+select * from t1
+fetch next row with ties;
+
+--error ER_WITH_TIES_NEEDS_ORDER
+select * from t1
+fetch next rows with ties;
+
+select * from t1
+order by a
+fetch first row with ties;
+
+select * from t1
+order by a
+fetch first rows with ties;
+
+select * from t1
+order by a
+fetch next row with ties;
+
+select * from t1
+order by a
+fetch next rows with ties;
+
+--echo #
+--echo # Include offset before fetch clause.
+--echo #
+
+select * from t1
+order by a
+offset 2
+fetch first row with ties;
+
+select * from t1
+order by a
+offset 2
+fetch first rows with ties;
+
+select * from t1
+order by a
+offset 2
+fetch next row with ties;
+
+select * from t1
+order by a
+offset 2
+fetch next rows with ties;
+
+
+--echo #
+--echo # Test syntax with a specific limit
+--echo #
+select * from t1
+fetch first 3 row only;
+
+select * from t1
+fetch first 3 rows only;
+
+select * from t1
+fetch next 3 row only;
+
+select * from t1
+fetch next 3 rows only;
+
+--echo #
+--echo # Include offset before fetch clause.
+--echo #
+select * from t1
+order by a
+offset 2
+fetch first 3 row only;
+
+select * from t1
+order by a
+offset 2
+fetch first 3 rows only;
+
+select * from t1
+offset 2
+fetch next 3 row only;
+
+select * from t1
+offset 2
+fetch next 3 rows only;
+
+
+--echo #
+--echo # Repeat the tests, but now with WITH TIES.
+--echo # WITH TIES requires order by.
+--echo #
+
+--error ER_WITH_TIES_NEEDS_ORDER
+select * from t1
+fetch first 3 row with ties;
+
+--error ER_WITH_TIES_NEEDS_ORDER
+select * from t1
+fetch first 3 rows with ties;
+
+--error ER_WITH_TIES_NEEDS_ORDER
+select * from t1
+fetch next 3 row with ties;
+
+--error ER_WITH_TIES_NEEDS_ORDER
+select * from t1
+fetch next 3 rows with ties;
+
+select * from t1
+order by a
+fetch first 3 row with ties;
+
+select * from t1
+order by a
+fetch first 3 rows with ties;
+
+select * from t1
+order by a
+fetch next 3 row with ties;
+
+select * from t1
+order by a
+fetch next 3 rows with ties;
+
+--echo #
+--echo # Include offset before fetch clause.
+--echo #
+
+--error ER_WITH_TIES_NEEDS_ORDER
+select * from t1
+offset 2
+fetch first row with ties;
+
+select * from t1
+order by a
+offset 2
+fetch first 3 row with ties;
+
+select * from t1
+order by a
+offset 2
+fetch first 3 rows with ties;
+
+select * from t1
+order by a
+offset 2
+fetch next 3 row with ties;
+
+select * from t1
+order by a
+offset 2
+fetch next 3 rows with ties;
+
+
+drop table t1;
+drop table t_keyword;
+
+--echo #
+--echo # Test behaviour with a simple select.
+--echo #
+
+create table t1 (id int, first_name varchar(100), last_name varchar(100), score double);
+insert into t1 values
+  (1, 'Alice', 'Fowler', 5),
+  (2, 'John', 'Doe', 6),
+  (3, 'John', 'Smith', 6),
+  (4, 'John', 'Smith', 6),
+  (5, 'John', 'Smith', 7),
+  (6, 'John', 'Elton', 8.1),
+  (7, 'Bob',  'Trasc', 9),
+  (8, 'Silvia', 'Ganush', 10);
+
+create table t2
+  (id int,
+   location varchar(100),
+   fk int,
+   constraint `fk_t1`
+     FOREIGN KEY (fk) REFERENCES t1 (id)
+     ON DELETE CASCADE
+     ON UPDATE RESTRICT);
+insert into t2 values
+  (1, 'L1', 1),
+  (2, 'L2', 2),
+  (3, 'L3', 3),
+  (4, 'L3', 4),
+  (5, 'L4', 5),
+  (6, 'L4', 6),
+  (7, 'L4', 7),
+  (7, null, 8);
+
+
+select * from t1
+order by id
+fetch first 3 rows only;
+
+select * from t1
+order by id
+fetch first 3 rows with ties;
+
+select * from t1
+order by first_name
+fetch first 3 rows only;
+
+select * from t1
+order by first_name
+fetch first 3 rows with ties;
+
+--echo #
+--echo # Test multi-part order by.
+--echo #
+select * from t1
+order by first_name, last_name
+fetch first 3 rows with ties;
+
+select * from t1
+order by first_name, last_name
+fetch first 4 rows with ties;
+
+
+select * from t1
+order by first_name, last_name
+offset 1
+fetch first 3 rows with ties;
+
+select * from t1
+order by first_name, last_name
+offset 1
+fetch first 3 rows only;
+
+select * from t1
+order by first_name, last_name
+offset 1
+fetch first 3 rows with ties;
+
+select * from t1
+order by first_name, last_name
+offset 2
+fetch first 3 rows only;
+
+select * from t1
+order by first_name, last_name
+offset 2
+fetch first 3 rows with ties;
+
+select * from t1
+order by first_name, last_name
+offset 3
+fetch first 3 rows only;
+
+select * from t1
+order by first_name, last_name
+offset 3
+fetch first 3 rows with ties;
+
+select * from t1
+order by first_name, last_name
+offset 4
+fetch first 3 rows only;
+
+select * from t1
+order by first_name, last_name
+offset 4
+fetch first 3 rows with ties;
+
+--echo #
+--echo # Test offset crossing into a new peer-group.
+--echo #
+select * from t1
+order by first_name, last_name
+offset 5
+fetch first 3 rows with ties;
+
+select * from t1
+order by first_name, last_name
+offset 5
+fetch first 3 rows only;
+
+--echo #
+--echo # Simple join with 2 tables, order by without columns in the
+--echo # second table and also with columns in the second table.
+--echo #
+--echo # Cover both only and with ties.
+--echo #
+select t1.id, t1.first_name, t1.last_name, t1.score, t2.location
+from t1 inner join t2 on t1.id = t2.fk
+order by t1.first_name, t1.last_name
+fetch first 3 rows only;
+
+select t1.id, t1.first_name, t1.last_name, t1.score, t2.location
+from t1 inner join t2 on t1.id = t2.fk
+order by t2.location, t1.first_name, t1.last_name
+fetch first 3 rows only;
+
+select t1.id, t1.first_name, t1.last_name, t1.score, t2.location
+from t1 inner join t2 on t1.id = t2.fk
+order by t1.first_name, t1.last_name
+fetch first 3 rows with ties;
+
+select t1.id, t1.first_name, t1.last_name, t1.score, t2.location
+from t1 inner join t2 on t1.id = t2.fk
+order by t2.location, t1.first_name, t1.last_name
+fetch first 3 rows with ties;
+
+--echo #
+--echo # Test descending order by.
+--echo #
+select t1.id, t1.first_name, t1.last_name, t1.score, t2.location
+from t1 inner join t2 on t1.id = t2.fk
+order by t2.location desc, t1.first_name, t1.last_name
+fetch first 3 rows only;
+
+--sorted_result
+select * from t2
+order by t2.location desc
+fetch first 2 rows with ties;
+
+--sorted_result
+select * from t2
+order by t2.location desc
+offset 1
+fetch first 2 rows with ties;
+
+--sorted_result
+select * from t2
+order by t2.location desc
+offset 2
+fetch first 2 rows with ties;
+
+--echo #
+--echo # Test a join with descending order by.
+--echo #
+select t1.id, t1.first_name, t1.last_name, t1.score, t2.location
+from t1 inner join t2 on t1.id = t2.fk
+order by t2.location desc, t1.first_name, t1.last_name
+fetch first 3 rows with ties;
+
+--echo #
+--echo # Test subqueries.
+--echo #
+select * from (
+  select * from t2
+  order by t2.location desc
+  offset 2
+  fetch first 2 rows with ties
+) temp;
+
+
+select * from t2
+order by t2.location desc
+offset 0
+fetch first 2 rows with ties;
+
+create view v1 as (
+  select * from t2
+  order by t2.location desc
+  offset 0
+  fetch first 2 rows with ties
+);
+
+create view v2 as (
+  select * from t2
+  order by t2.location desc
+  offset 1
+  fetch first 2 rows with ties
+);
+
+create view v3 as (
+  select * from t2
+  order by t2.location desc
+  offset 2
+  fetch first row with ties
+);
+
+
+
+select * from v1;
+select * from v2;
+select * from v3;
+
+show create view v1;
+show create view v2;
+show create view v3;
+
+--echo #
+--echo # Test joins with views and order by referenced from the view.
+--echo #
+
+--sorted_result
+select *
+from t1 inner join v1 on t1.id = v1.fk
+order by v1.location desc, t1.first_name
+offset 1
+fetch first 1 rows with ties;
+
+select first_name, last_name, sum(score)
+from t1
+group by first_name, last_name
+order by first_name;
+
+--echo #
+--echo # Test with ties with group by. Make sure that if order by is a less
+--echo # specific sort of group by, that WITH TIES still gets executed.
+--echo #
+
+explain
+select first_name, last_name, sum(score)
+from t1
+group by first_name, last_name
+order by first_name
+offset 1
+fetch first 2 rows with ties;
+
+--sorted_result
+select first_name, last_name, sum(score)
+from t1
+group by first_name, last_name
+order by first_name
+offset 1
+fetch first 2 rows with ties;
+
+
+
+drop table t1;
+drop table t2;
+drop view v1;
+drop view v2;
+drop view v3;
+
+--echo #
+--echo # Test with ties when an index is used to provide the order by.
+--echo #
+create table t1 (id int, first_name varchar(100), last_name varchar(100), score double);
+
+create index t1_name on t1 (first_name, last_name);
+
+insert into t1 values
+  (1, 'Alice', 'Fowler', 5),
+  (2, 'John', 'Doe', 6),
+  (3, 'John', 'Smith', 6),
+  (4, 'John', 'Smith', 6),
+  (5, 'John', 'Smith', 7),
+  (6, 'John', 'Elton', 8.1),
+  (7, 'Bob',  'Trasc', 9),
+  (8, 'Silvia', 'Ganush', 10);
+
+explain select first_name, last_name
+from t1
+order by first_name
+offset 1
+fetch first 2 rows with ties;
+
+explain select first_name, last_name
+from t1
+order by first_name desc
+fetch first 2 rows with ties;
+
+
+select first_name, last_name
+from t1
+order by first_name
+offset 1
+fetch first 2 rows with ties;
+
+select first_name, last_name
+from t1
+order by first_name desc
+fetch first 2 rows with ties;
+
+
+select first_name, last_name
+from t1
+order by first_name desc
+offset 1
+fetch first 2 rows with ties;
+
+select first_name, last_name
+from t1
+order by first_name desc
+offset 4
+fetch first 2 rows with ties;
+
+select first_name, last_name
+from t1
+order by first_name desc
+offset 4
+fetch first 3 rows with ties;
+
+explain select first_name, last_name
+from t1
+where first_name != 'John'
+order by first_name
+fetch first 2 rows with ties;
+
+select first_name, last_name
+from t1
+where first_name != 'John'
+order by first_name
+fetch first 2 rows with ties;
+
+
+
+select first_name, last_name
+from t1
+where first_name != 'John'
+group by first_name, last_name
+order by first_name
+fetch first 2 rows with ties;
+
+
+--echo #
+--echo # Test CTE support.
+--echo #
+explain with temp_table as (
+  select first_name, last_name
+  from t1
+  where first_name != 'John'
+  group by first_name, last_name
+  order by first_name
+  fetch first 2 rows with ties
+)
+select * from temp_table
+order by first_name, last_name;
+
+with temp_table as (
+  select first_name, last_name
+  from t1
+  group by first_name, last_name
+  order by first_name
+  fetch first 3 rows with ties
+)
+select * from temp_table
+order by first_name, last_name;
+
+with temp_table as (
+  select first_name, last_name
+  from t1
+  group by first_name, last_name
+  order by first_name
+  fetch first 3 rows with ties
+)
+select * from temp_table
+order by last_name
+fetch first 3 rows with ties;
+
+--sorted_result
+with temp_table as (
+  select first_name, last_name
+  from t1
+  group by first_name, last_name
+  order by first_name
+  fetch first 3 rows with ties
+)
+select * from temp_table
+order by first_name
+fetch first 3 rows with ties;
+
+--sorted_result
+with temp_table as (
+  select first_name, last_name
+  from t1
+  group by first_name, last_name
+  order by first_name
+  fetch first 3 rows with ties
+)
+select * from temp_table
+order by first_name
+offset 1
+fetch first 2 rows with ties;
+
+
+select first_name, row_number() over () rn
+from t1
+order by rn
+offset 1
+fetch first 2 rows with ties;
+
+select first_name, row_number() over () rn
+from t1
+order by rn desc
+offset 1
+fetch first 2 rows with ties;
+
+select first_name, score, rank() over (ORDER BY score)
+from t1
+order by rank() over (ORDER BY score)
+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
+fetch first 2 rows with ties;
+
+select first_name, score, rank() over (ORDER BY score)
+from t1
+order by rank() over (ORDER BY score)
+fetch first 6 rows with ties;
+
+
+--echo #
+--echo # Test nulls.
+--echo #
+create table t2 (id int, location varchar(100), time datetime, value int, fk int);
+
+insert into t2 values (1, 'home', '2020-01-01 10:00', 10, 1);
+insert into t2 values (2, 'home', '2020-01-01 11:00', 11, 2);
+insert into t2 values (3, 'home', '2020-01-01 12:00', 12, 3);
+insert into t2 values (4, 'home', '2020-01-01 13:00', 13, 3);
+insert into t2 values (5, 'home', '2020-01-01 14:00', 13, 3);
+insert into t2 values (6, 'home', '2020-01-01 15:00', 13, 2);
+insert into t2 values (7, 'home', '2020-01-01 16:00', 13, 6);
+insert into t2 values (8, 'outside', '2020-01-01 17:00', 17, 6);
+insert into t2 values (9, 'outside', '2020-01-01 18:00', 17, 6);
+insert into t2 values (10, 'outside', '2020-01-01 19:00', 17, 8);
+insert into t2 values (11, 'outside', '2020-01-01 20:00', 16, 7);
+insert into t2 values (12, 'outside', '2020-01-01 21:00', 16, 7);
+
+--sorted_result
+select *
+from t1 left join t2 on t1.id = t2.fk
+order by t2.location;
+
+--sorted_result
+select *
+from t1 left join t2 on t1.id = t2.fk
+order by t2.location
+fetch first 1 row with ties;
+
+--sorted_result
+select *
+from t1 left join t2 on t1.id = t2.fk
+order by t2.location
+fetch first 2 row with ties;
+
+--sorted_result
+select *
+from t1 left join t2 on t1.id = t2.fk
+order by t2.location
+fetch first 3 row with ties;
+
+--sorted_result
+select *
+from t1 left join t2 on t1.id = t2.fk
+order by t2.location
+offset 2
+fetch first 1 row with ties;
+
+
+--echo #
+--echo # Test union-like operator with multiple fetch first clauses.
+--echo #
+select * from t1 order by 1 fetch first 3 rows with ties;
+select * from t1 order by first_name desc fetch first 3 rows with ties;
+
+--sorted_result
+(select * from t1 order by 1 fetch first 3 rows with ties)
+intersect
+(select * from t1 order by first_name desc fetch first 3 rows with ties)
+order by first_name
+fetch first 1 row with ties;
+
+--sorted_result
+(select * from t1 order by 1 fetch first 3 rows with ties)
+except
+(select * from t1 order by first_name desc fetch first 3 rows with ties)
+order by first_name
+fetch first 1 row with ties;
+
+--sorted_result
+(select * from t1 order by 1 fetch first 3 rows with ties)
+except
+(select * from t1 order by first_name desc fetch first 3 rows with ties)
+order by first_name
+offset 1
+fetch first 1 row with ties;
+
+--sorted_result
+select sum(score)
+from t1
+order by 1
+fetch first 2 rows with ties;
+
+--sorted_result
+select sum(score)
+from t1
+group by id
+order by 1
+fetch first 2 rows with ties;
+
+drop table t1;
+drop table t2;
+
+--echo #
+--echo # Test SQL_CALC_FOUND_ROWS
+--echo #
+create table t1 (a int, b int, index (a,b));
+insert into t1 values (1,1), (1,2), (1,3), (2,1), (2,2), (3,1);
+
+select SQL_CALC_FOUND_ROWS a, b, count(*)
+from t1
+group by a, b
+order by a
+fetch first 1 rows with ties;
+SELECT FOUND_ROWS();
+
+--sorted_result
+select SQL_CALC_FOUND_ROWS a, b
+from t1
+order by a
+fetch first 1 rows with ties;
+SELECT FOUND_ROWS();
+
+--echo #
+--echo # Test index read optimization with ORDER BY as sub part of GROUP BY
+--echo #
+explain select a, b, count(*)
+from t1
+group by a, b
+order by a;
+
+explain select a, b, count(*)
+from t1
+group by a, b
+order by a
+fetch first 1 rows with ties;
+
+--echo #
+--echo # Test FETCH ... WITH TIES / ONLY with prepared statements
+--echo #
+
+prepare s from "select a, b from t1 order by a fetch first ? rows with ties";
+
+set @a='qwe';
+execute s using @a;
+
+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";
+set @offset=1;
+set @limit_count= 2;
+execute s2 using @offset, @limit_count;
+
+execute s2 using @offset, 3;
+
+--error ER_WRONG_ARGUMENTS
+execute s2 using -1, 3;
+
+--echo #
+--echo # Test with ties with a constant order by.
+--echo #
+select a, b
+from t1
+order by 'x'
+fetch first 2 rows with ties;
+
+select b, sum(a)
+from t1
+group by b
+order by 'x', 'y', 'z'
+fetch first 2 rows with ties;
+
+--echo #
+--echo # Test with ties when group by is used and order by gets eliminated.
+--echo #
+--sorted_result
+select b, sum(a)
+from t1
+group by b
+order by 'x', 'y', 'z'
+fetch first 2 rows with ties;
+
+--echo #
+--echo # Test an ill formed SELECT using MySQL/MariaDBs group by extension
+--echo # of not requiring all non-aggregate fields to be part of group
+--echo # by.
+--echo #
+--sorted_result
+select b, a, sum(a)
+from t1
+group by a
+order by b, 'x', 'y', 'z'
+fetch first 2 rows with ties;
+
+delete from t1;
+insert into t1 values (1,1), (1,2), (1,3), (2,1), (2,2), (3,2), (3, 3);
+
+--sorted_result
+select b, a, sum(a)
+from t1
+group by a
+order by b, 'x', 'y', 'z'
+fetch first 1 rows with ties;
+
+--sorted_result
+select b, a, sum(a)
+from t1
+group by a
+order by 'x', b, 'x', 'y', 'z'
+fetch first 1 rows with ties;
+
+--sorted_result
+select b, a, sum(a)
+from t1
+group by a
+order by 'x', 'y', b
+fetch first 1 rows with ties;
+
+
+--echo #
+--echo # Test with ties when order by a const column is used.
+--echo #
+select a, b
+from t1
+order by a, 'a'
+fetch first 2 rows with ties;
+
+--echo #
+--echo # Order by aggregates.
+--echo #
+--sorted_result
+select a, b, sum(a)
+from t1
+group by a, b
+order by sum(a)
+fetch first 1 rows with ties;
+
+--echo # Test different sum function in order by, not present in select list.
+--sorted_result
+select a, b, sum(a)
+from t1
+group by a, b
+order by sum(b)
+fetch first 1 rows with ties;
+
+--echo # Test aggregates with constant columns in order by.
+--sorted_result
+select a, b, sum(a)
+from t1
+group by a, b
+order by a
+fetch first 1 rows with ties;
+
+select a, b, sum(a)
+from t1
+group by a, b
+order by a, 'x'
+fetch first 1 rows with ties;
+
+select a, b, sum(a)
+from t1
+group by a, b
+order by a, b
+fetch first 1 rows with ties;
+
+select a, b, sum(a)
+from t1
+group by a, b
+order by a, b
+fetch first 2 rows with ties;
+
+select a, b, sum(a)
+from t1
+group by a, b
+order by a, b, 'x'
+fetch first 1 rows with ties;
+
+select a, b, sum(a)
+from t1
+group by a, 'x', b
+order by a, b, 'x'
+fetch first 1 rows with ties;
+
+select a, b, sum(a)
+from t1
+group by 'x', a, b
+order by a, b, 'x'
+fetch first 1 rows with ties;
+
+
+select a, b, sum(a)
+from t1
+group by a, b
+order by 'x', a, 'x', b, 't', b
+fetch first 1 rows with ties;
+
+--sorted_result
+select a, b, sum(a)
+from t1
+group by a, b
+order by b, 't', b
+fetch first 1 rows with ties;
+
+--echo #
+--echo # Test with subqueries in order by
+--echo #
+select a, b, sum(a)
+from t1
+group by a, b
+order by (select 1)
+fetch first 1 rows with ties;
+
+select a, b, sum(a)
+from t1
+group by a, b
+order by (select 1), a
+fetch first 1 rows with ties;
+
+select a, b, sum(a)
+from t1
+group by a, b
+order by (select 1), a
+fetch first 1 rows with ties;
+
+
+
+drop table t1;
diff --git a/mysql-test/main/system_mysql_db_fix40123.result b/mysql-test/main/system_mysql_db_fix40123.result
index 29365c258e8..f467f7a1c53 100644
--- a/mysql-test/main/system_mysql_db_fix40123.result
+++ b/mysql-test/main/system_mysql_db_fix40123.result
@@ -29,7 +29,7 @@ Warning	1280	Name 'TzId' ignored for PRIMARY key.
 CREATE TABLE time_zone_transition ( Time_zone_id int unsigned NOT NULL, Transition_time bigint signed NOT NULL, Transition_type_id int unsigned NOT NULL, PRIMARY KEY TzIdTranTime (Time_zone_id, Transition_time) ) engine=MyISAM CHARACTER SET utf8 comment='Time zone transitions';
 Warnings:
 Warning	1280	Name 'TzIdTranTime' ignored for PRIMARY key.
-CREATE TABLE time_zone_transition_type ( Time_zone_id int unsigned NOT NULL, Transition_type_id int unsigned NOT NULL, Offset int signed DEFAULT 0 NOT NULL, Is_DST tinyint unsigned DEFAULT 0 NOT NULL, Abbreviation char(8) DEFAULT '' NOT NULL, PRIMARY KEY TzIdTrTId (Time_zone_id, Transition_type_id) ) engine=MyISAM CHARACTER SET utf8 comment='Time zone transition types';
+CREATE TABLE time_zone_transition_type ( Time_zone_id int unsigned NOT NULL, Transition_type_id int unsigned NOT NULL, `Offset` int signed DEFAULT 0 NOT NULL, Is_DST tinyint unsigned DEFAULT 0 NOT NULL, Abbreviation char(8) DEFAULT '' NOT NULL, PRIMARY KEY TzIdTrTId (Time_zone_id, Transition_type_id) ) engine=MyISAM CHARACTER SET utf8 comment='Time zone transition types';
 Warnings:
 Warning	1280	Name 'TzIdTrTId' ignored for PRIMARY key.
 CREATE TABLE time_zone_leap_second ( Transition_time bigint signed NOT NULL, Correction int signed NOT NULL, PRIMARY KEY TranTime (Transition_time) ) engine=MyISAM CHARACTER SET utf8 comment='Leap seconds information for time zones';
diff --git a/mysql-test/main/system_mysql_db_fix40123.test b/mysql-test/main/system_mysql_db_fix40123.test
index 2e7ebd151d2..08aac433433 100644
--- a/mysql-test/main/system_mysql_db_fix40123.test
+++ b/mysql-test/main/system_mysql_db_fix40123.test
@@ -42,7 +42,7 @@ CREATE TABLE help_keyword ( help_keyword_id int unsigned not null, name varchar(
 CREATE TABLE time_zone_name ( Name char(64) NOT NULL, Time_zone_id int unsigned NOT NULL, PRIMARY KEY Name (Name) ) engine=MyISAM CHARACTER SET utf8 comment='Time zone names';
 CREATE TABLE time_zone ( Time_zone_id int unsigned NOT NULL auto_increment, Use_leap_seconds enum('Y','N') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, PRIMARY KEY TzId (Time_zone_id) ) engine=MyISAM CHARACTER SET utf8 comment='Time zones';
 CREATE TABLE time_zone_transition ( Time_zone_id int unsigned NOT NULL, Transition_time bigint signed NOT NULL, Transition_type_id int unsigned NOT NULL, PRIMARY KEY TzIdTranTime (Time_zone_id, Transition_time) ) engine=MyISAM CHARACTER SET utf8 comment='Time zone transitions';
-CREATE TABLE time_zone_transition_type ( Time_zone_id int unsigned NOT NULL, Transition_type_id int unsigned NOT NULL, Offset int signed DEFAULT 0 NOT NULL, Is_DST tinyint unsigned DEFAULT 0 NOT NULL, Abbreviation char(8) DEFAULT '' NOT NULL, PRIMARY KEY TzIdTrTId (Time_zone_id, Transition_type_id) ) engine=MyISAM CHARACTER SET utf8 comment='Time zone transition types';
+CREATE TABLE time_zone_transition_type ( Time_zone_id int unsigned NOT NULL, Transition_type_id int unsigned NOT NULL, `Offset` int signed DEFAULT 0 NOT NULL, Is_DST tinyint unsigned DEFAULT 0 NOT NULL, Abbreviation char(8) DEFAULT '' NOT NULL, PRIMARY KEY TzIdTrTId (Time_zone_id, Transition_type_id) ) engine=MyISAM CHARACTER SET utf8 comment='Time zone transition types';
 CREATE TABLE time_zone_leap_second ( Transition_time bigint signed NOT NULL, Correction int signed NOT NULL, PRIMARY KEY TranTime (Transition_time) ) engine=MyISAM CHARACTER SET utf8 comment='Leap seconds information for time zones';
 
 # Run the mysql_fix_privilege_tables.sql using "mysql --force"
diff --git a/mysql-test/main/system_mysql_db_fix50030.result b/mysql-test/main/system_mysql_db_fix50030.result
index b9fd226a7e0..84482e4c74f 100644
--- a/mysql-test/main/system_mysql_db_fix50030.result
+++ b/mysql-test/main/system_mysql_db_fix50030.result
@@ -29,7 +29,7 @@ Warning	1280	Name 'TzId' ignored for PRIMARY key.
 CREATE TABLE time_zone_transition ( Time_zone_id int unsigned NOT NULL, Transition_time bigint signed NOT NULL, Transition_type_id int unsigned NOT NULL, PRIMARY KEY TzIdTranTime (Time_zone_id, Transition_time) ) engine=MyISAM CHARACTER SET utf8 comment='Time zone transitions';
 Warnings:
 Warning	1280	Name 'TzIdTranTime' ignored for PRIMARY key.
-CREATE TABLE time_zone_transition_type ( Time_zone_id int unsigned NOT NULL, Transition_type_id int unsigned NOT NULL, Offset int signed DEFAULT 0 NOT NULL, Is_DST tinyint unsigned DEFAULT 0 NOT NULL, Abbreviation char(8) DEFAULT '' NOT NULL, PRIMARY KEY TzIdTrTId (Time_zone_id, Transition_type_id) ) engine=MyISAM CHARACTER SET utf8 comment='Time zone transition types';
+CREATE TABLE time_zone_transition_type ( Time_zone_id int unsigned NOT NULL, Transition_type_id int unsigned NOT NULL, `Offset` int signed DEFAULT 0 NOT NULL, Is_DST tinyint unsigned DEFAULT 0 NOT NULL, Abbreviation char(8) DEFAULT '' NOT NULL, PRIMARY KEY TzIdTrTId (Time_zone_id, Transition_type_id) ) engine=MyISAM CHARACTER SET utf8 comment='Time zone transition types';
 Warnings:
 Warning	1280	Name 'TzIdTrTId' ignored for PRIMARY key.
 CREATE TABLE time_zone_leap_second ( Transition_time bigint signed NOT NULL, Correction int signed NOT NULL, PRIMARY KEY TranTime (Transition_time) ) engine=MyISAM CHARACTER SET utf8 comment='Leap seconds information for time zones';
diff --git a/mysql-test/main/system_mysql_db_fix50030.test b/mysql-test/main/system_mysql_db_fix50030.test
index 77f1a97b1aa..a9830471988 100644
--- a/mysql-test/main/system_mysql_db_fix50030.test
+++ b/mysql-test/main/system_mysql_db_fix50030.test
@@ -42,7 +42,7 @@ CREATE TABLE help_keyword ( help_keyword_id int unsigned not null, name char(64)
 CREATE TABLE time_zone_name ( Name char(64) NOT NULL, Time_zone_id int unsigned NOT NULL, PRIMARY KEY Name (Name) ) engine=MyISAM CHARACTER SET utf8 comment='Time zone names';
 CREATE TABLE time_zone ( Time_zone_id int unsigned NOT NULL auto_increment, Use_leap_seconds enum('Y','N') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, PRIMARY KEY TzId (Time_zone_id) ) engine=MyISAM CHARACTER SET utf8 comment='Time zones';
 CREATE TABLE time_zone_transition ( Time_zone_id int unsigned NOT NULL, Transition_time bigint signed NOT NULL, Transition_type_id int unsigned NOT NULL, PRIMARY KEY TzIdTranTime (Time_zone_id, Transition_time) ) engine=MyISAM CHARACTER SET utf8 comment='Time zone transitions';
-CREATE TABLE time_zone_transition_type ( Time_zone_id int unsigned NOT NULL, Transition_type_id int unsigned NOT NULL, Offset int signed DEFAULT 0 NOT NULL, Is_DST tinyint unsigned DEFAULT 0 NOT NULL, Abbreviation char(8) DEFAULT '' NOT NULL, PRIMARY KEY TzIdTrTId (Time_zone_id, Transition_type_id) ) engine=MyISAM CHARACTER SET utf8 comment='Time zone transition types';
+CREATE TABLE time_zone_transition_type ( Time_zone_id int unsigned NOT NULL, Transition_type_id int unsigned NOT NULL, `Offset` int signed DEFAULT 0 NOT NULL, Is_DST tinyint unsigned DEFAULT 0 NOT NULL, Abbreviation char(8) DEFAULT '' NOT NULL, PRIMARY KEY TzIdTrTId (Time_zone_id, Transition_type_id) ) engine=MyISAM CHARACTER SET utf8 comment='Time zone transition types';
 CREATE TABLE time_zone_leap_second ( Transition_time bigint signed NOT NULL, Correction int signed NOT NULL, PRIMARY KEY TranTime (Transition_time) ) engine=MyISAM CHARACTER SET utf8 comment='Leap seconds information for time zones';
 CREATE TABLE proc ( db char(64) collate utf8_bin DEFAULT '' NOT NULL, name char(64) DEFAULT '' NOT NULL, type enum('FUNCTION','PROCEDURE') NOT NULL, specific_name char(64) DEFAULT '' NOT NULL, language enum('SQL') DEFAULT 'SQL' NOT NULL, sql_data_access enum('CONTAINS_SQL',			 'NO_SQL',			 'READS_SQL_DATA',			 'MODIFIES_SQL_DATA' ) DEFAULT 'CONTAINS_SQL' NOT NULL, is_deterministic enum('YES','NO') DEFAULT 'NO' NOT NULL, security_type enum('INVOKER','DEFINER') DEFAULT 'DEFINER' NOT NULL, param_list blob DEFAULT '' NOT NULL, returns char(64) DEFAULT '' NOT NULL, body longblob DEFAULT '' NOT NULL, definer char(77) collate utf8_bin DEFAULT '' NOT NULL, created timestamp, modified timestamp, sql_mode set( 'REAL_AS_FLOAT', 'PIPES_AS_CONCAT', 'ANSI_QUOTES', 'IGNORE_SPACE', 'NOT_USED', 'ONLY_FULL_GROUP_BY', 'NO_UNSIGNED_SUBTRACTION', 'NO_DIR_IN_CREATE', 'POSTGRESQL', 'ORACLE', 'MSSQL', 'DB2', 'MAXDB', 'NO_KEY_OPTIONS', 'NO_TABLE_OPTIONS', 'NO_FIELD_OPTIONS', 'MYSQL323', 'MYSQL40', 'ANSI', 'NO_AUTO_VALUE_ON_ZERO', 'NO_BACKSLASH_ESCAPES', 'STRICT_TRANS_TABLES', 'STRICT_ALL_TABLES', 'NO_ZERO_IN_DATE', 'NO_ZERO_DATE', 'INVALID_DATES', 'ERROR_FOR_DIVISION_BY_ZERO', 'TRADITIONAL', 'NO_AUTO_CREATE_USER', 'HIGH_NOT_PRECEDENCE' ) DEFAULT '' NOT NULL, comment char(64) collate utf8_bin DEFAULT '' NOT NULL, PRIMARY KEY (db,name,type) ) engine=MyISAM character set utf8 comment='Stored Procedures';
 CREATE TABLE procs_priv ( Host char(60) binary DEFAULT '' NOT NULL, Db char(64) binary DEFAULT '' NOT NULL, User char(16) binary DEFAULT '' NOT NULL, Routine_name char(64) binary DEFAULT '' NOT NULL, Routine_type enum('FUNCTION','PROCEDURE') NOT NULL, Grantor char(77) DEFAULT '' NOT NULL, Proc_priv set('Execute','Alter Routine','Grant') COLLATE utf8_general_ci DEFAULT '' NOT NULL, Timestamp timestamp, PRIMARY KEY (Host,Db,User,Routine_name,Routine_type), KEY Grantor (Grantor) ) engine=MyISAM CHARACTER SET utf8 COLLATE utf8_bin comment='Procedure privileges';
diff --git a/mysql-test/main/system_mysql_db_fix50117.result b/mysql-test/main/system_mysql_db_fix50117.result
index c81892806c8..73ba79e2cee 100644
--- a/mysql-test/main/system_mysql_db_fix50117.result
+++ b/mysql-test/main/system_mysql_db_fix50117.result
@@ -14,7 +14,7 @@ CREATE TABLE IF NOT EXISTS help_keyword ( help_keyword_id int unsigned not null,
 CREATE TABLE IF NOT EXISTS time_zone_name ( Name char(64) NOT NULL, Time_zone_id int unsigned NOT NULL, PRIMARY KEY (Name) ) engine=MyISAM CHARACTER SET utf8 comment='Time zone names';
 CREATE TABLE IF NOT EXISTS time_zone ( Time_zone_id int unsigned NOT NULL auto_increment, Use_leap_seconds enum('Y','N') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, PRIMARY KEY (Time_zone_id) ) engine=MyISAM CHARACTER SET utf8 comment='Time zones';
 CREATE TABLE IF NOT EXISTS time_zone_transition ( Time_zone_id int unsigned NOT NULL, Transition_time bigint signed NOT NULL, Transition_type_id int unsigned NOT NULL, PRIMARY KEY (Time_zone_id, Transition_time) ) engine=MyISAM CHARACTER SET utf8 comment='Time zone transitions';
-CREATE TABLE IF NOT EXISTS time_zone_transition_type ( Time_zone_id int unsigned NOT NULL, Transition_type_id int unsigned NOT NULL, Offset int signed DEFAULT 0 NOT NULL, Is_DST tinyint unsigned DEFAULT 0 NOT NULL, Abbreviation char(8) DEFAULT '' NOT NULL, PRIMARY KEY (Time_zone_id, Transition_type_id) ) engine=MyISAM CHARACTER SET utf8 comment='Time zone transition types';
+CREATE TABLE IF NOT EXISTS time_zone_transition_type ( Time_zone_id int unsigned NOT NULL, Transition_type_id int unsigned NOT NULL, `Offset` int signed DEFAULT 0 NOT NULL, Is_DST tinyint unsigned DEFAULT 0 NOT NULL, Abbreviation char(8) DEFAULT '' NOT NULL, PRIMARY KEY (Time_zone_id, Transition_type_id) ) engine=MyISAM CHARACTER SET utf8 comment='Time zone transition types';
 CREATE TABLE IF NOT EXISTS time_zone_leap_second ( Transition_time bigint signed NOT NULL, Correction int signed NOT NULL, PRIMARY KEY (Transition_time) ) engine=MyISAM CHARACTER SET utf8 comment='Leap seconds information for time zones';
 CREATE TABLE IF NOT EXISTS proc ( db char(64) collate utf8_bin DEFAULT '' NOT NULL, name char(64) DEFAULT '' NOT NULL, type  enum('FUNCTION','PROCEDURE') NOT NULL, specific_name char(64) DEFAULT '' NOT NULL, language enum('SQL') DEFAULT 'SQL' NOT NULL, sql_data_access enum('CONTAINS_SQL', 'NO_SQL', 'READS_SQL_DATA', 'MODIFIES_SQL_DATA' ) DEFAULT 'CONTAINS_SQL' NOT NULL, is_deterministic enum('YES','NO') DEFAULT 'NO' NOT NULL, security_type enum('INVOKER','DEFINER') DEFAULT 'DEFINER' NOT NULL, param_list  blob NOT NULL, returns char(64) DEFAULT '' NOT NULL, body longblob NOT NULL, definer char(77) collate utf8_bin DEFAULT '' NOT NULL, created timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, modified timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', sql_mode set( 'REAL_AS_FLOAT', 'PIPES_AS_CONCAT', 'ANSI_QUOTES', 'IGNORE_SPACE', 'NOT_USED', 'ONLY_FULL_GROUP_BY', 'NO_UNSIGNED_SUBTRACTION', 'NO_DIR_IN_CREATE', 'POSTGRESQL', 'ORACLE', 'MSSQL', 'DB2', 'MAXDB', 'NO_KEY_OPTIONS', 'NO_TABLE_OPTIONS', 'NO_FIELD_OPTIONS', 'MYSQL323', 'MYSQL40', 'ANSI', 'NO_AUTO_VALUE_ON_ZERO', 'NO_BACKSLASH_ESCAPES', 'STRICT_TRANS_TABLES', 'STRICT_ALL_TABLES', 'NO_ZERO_IN_DATE', 'NO_ZERO_DATE', 'INVALID_DATES', 'ERROR_FOR_DIVISION_BY_ZERO', 'TRADITIONAL', 'NO_AUTO_CREATE_USER', 'HIGH_NOT_PRECEDENCE' ) DEFAULT '' NOT NULL, comment char(64) collate utf8_bin DEFAULT '' NOT NULL, PRIMARY KEY (db,name,type) ) engine=MyISAM character set utf8 comment='Stored Procedures';
 CREATE TABLE IF NOT EXISTS procs_priv ( Host char(60) binary DEFAULT '' NOT NULL, Db char(64) binary DEFAULT '' NOT NULL, User char(16) binary DEFAULT '' NOT NULL, Routine_name char(64) binary DEFAULT '' NOT NULL, Routine_type enum('FUNCTION','PROCEDURE') NOT NULL, Grantor char(77) DEFAULT '' NOT NULL, Proc_priv set('Execute','Alter Routine','Grant') COLLATE utf8_general_ci DEFAULT '' NOT NULL, Timestamp timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (Host,Db,User,Routine_name,Routine_type), KEY Grantor (Grantor) ) engine=MyISAM CHARACTER SET utf8 COLLATE utf8_bin comment='Procedure privileges';
diff --git a/mysql-test/main/system_mysql_db_fix50117.test b/mysql-test/main/system_mysql_db_fix50117.test
index 28b8621a638..510cabf08a6 100644
--- a/mysql-test/main/system_mysql_db_fix50117.test
+++ b/mysql-test/main/system_mysql_db_fix50117.test
@@ -37,7 +37,7 @@ CREATE TABLE IF NOT EXISTS help_keyword ( help_keyword_id int unsigned not null,
 CREATE TABLE IF NOT EXISTS time_zone_name ( Name char(64) NOT NULL, Time_zone_id int unsigned NOT NULL, PRIMARY KEY (Name) ) engine=MyISAM CHARACTER SET utf8 comment='Time zone names';
 CREATE TABLE IF NOT EXISTS time_zone ( Time_zone_id int unsigned NOT NULL auto_increment, Use_leap_seconds enum('Y','N') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, PRIMARY KEY (Time_zone_id) ) engine=MyISAM CHARACTER SET utf8 comment='Time zones';
 CREATE TABLE IF NOT EXISTS time_zone_transition ( Time_zone_id int unsigned NOT NULL, Transition_time bigint signed NOT NULL, Transition_type_id int unsigned NOT NULL, PRIMARY KEY (Time_zone_id, Transition_time) ) engine=MyISAM CHARACTER SET utf8 comment='Time zone transitions';
-CREATE TABLE IF NOT EXISTS time_zone_transition_type ( Time_zone_id int unsigned NOT NULL, Transition_type_id int unsigned NOT NULL, Offset int signed DEFAULT 0 NOT NULL, Is_DST tinyint unsigned DEFAULT 0 NOT NULL, Abbreviation char(8) DEFAULT '' NOT NULL, PRIMARY KEY (Time_zone_id, Transition_type_id) ) engine=MyISAM CHARACTER SET utf8 comment='Time zone transition types';
+CREATE TABLE IF NOT EXISTS time_zone_transition_type ( Time_zone_id int unsigned NOT NULL, Transition_type_id int unsigned NOT NULL, `Offset` int signed DEFAULT 0 NOT NULL, Is_DST tinyint unsigned DEFAULT 0 NOT NULL, Abbreviation char(8) DEFAULT '' NOT NULL, PRIMARY KEY (Time_zone_id, Transition_type_id) ) engine=MyISAM CHARACTER SET utf8 comment='Time zone transition types';
 CREATE TABLE IF NOT EXISTS time_zone_leap_second ( Transition_time bigint signed NOT NULL, Correction int signed NOT NULL, PRIMARY KEY (Transition_time) ) engine=MyISAM CHARACTER SET utf8 comment='Leap seconds information for time zones';
 CREATE TABLE IF NOT EXISTS proc ( db char(64) collate utf8_bin DEFAULT '' NOT NULL, name char(64) DEFAULT '' NOT NULL, type  enum('FUNCTION','PROCEDURE') NOT NULL, specific_name char(64) DEFAULT '' NOT NULL, language enum('SQL') DEFAULT 'SQL' NOT NULL, sql_data_access enum('CONTAINS_SQL', 'NO_SQL', 'READS_SQL_DATA', 'MODIFIES_SQL_DATA' ) DEFAULT 'CONTAINS_SQL' NOT NULL, is_deterministic enum('YES','NO') DEFAULT 'NO' NOT NULL, security_type enum('INVOKER','DEFINER') DEFAULT 'DEFINER' NOT NULL, param_list  blob NOT NULL, returns char(64) DEFAULT '' NOT NULL, body longblob NOT NULL, definer char(77) collate utf8_bin DEFAULT '' NOT NULL, created timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, modified timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', sql_mode set( 'REAL_AS_FLOAT', 'PIPES_AS_CONCAT', 'ANSI_QUOTES', 'IGNORE_SPACE', 'NOT_USED', 'ONLY_FULL_GROUP_BY', 'NO_UNSIGNED_SUBTRACTION', 'NO_DIR_IN_CREATE', 'POSTGRESQL', 'ORACLE', 'MSSQL', 'DB2', 'MAXDB', 'NO_KEY_OPTIONS', 'NO_TABLE_OPTIONS', 'NO_FIELD_OPTIONS', 'MYSQL323', 'MYSQL40', 'ANSI', 'NO_AUTO_VALUE_ON_ZERO', 'NO_BACKSLASH_ESCAPES', 'STRICT_TRANS_TABLES', 'STRICT_ALL_TABLES', 'NO_ZERO_IN_DATE', 'NO_ZERO_DATE', 'INVALID_DATES', 'ERROR_FOR_DIVISION_BY_ZERO', 'TRADITIONAL', 'NO_AUTO_CREATE_USER', 'HIGH_NOT_PRECEDENCE' ) DEFAULT '' NOT NULL, comment char(64) collate utf8_bin DEFAULT '' NOT NULL, PRIMARY KEY (db,name,type) ) engine=MyISAM character set utf8 comment='Stored Procedures';
 CREATE TABLE IF NOT EXISTS procs_priv ( Host char(60) binary DEFAULT '' NOT NULL, Db char(64) binary DEFAULT '' NOT NULL, User char(16) binary DEFAULT '' NOT NULL, Routine_name char(64) binary DEFAULT '' NOT NULL, Routine_type enum('FUNCTION','PROCEDURE') NOT NULL, Grantor char(77) DEFAULT '' NOT NULL, Proc_priv set('Execute','Alter Routine','Grant') COLLATE utf8_general_ci DEFAULT '' NOT NULL, Timestamp timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (Host,Db,User,Routine_name,Routine_type), KEY Grantor (Grantor) ) engine=MyISAM CHARACTER SET utf8 COLLATE utf8_bin comment='Procedure privileges';