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.

19 files changed, 2621 insertions, 81 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';
diff --git a/scripts/mysql_system_tables.sql b/scripts/mysql_system_tables.sql
index 6abcb81cab7..cee47a0e5d6 100644
--- a/scripts/mysql_system_tables.sql
+++ b/scripts/mysql_system_tables.sql
@@ -144,7 +144,7 @@ CREATE TABLE IF NOT EXISTS time_zone (   Time_zone_id int unsigned NOT NULL auto
 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 /*TzIdTranTime*/ (Time_zone_id, Transition_time) ) engine=Aria transactional=1 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 /*TzIdTrTId*/ (Time_zone_id, Transition_type_id) ) engine=Aria transactional=1 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 /*TzIdTrTId*/ (Time_zone_id, Transition_type_id) ) engine=Aria transactional=1 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 /*TranTime*/ (Transition_time) ) engine=Aria transactional=1 CHARACTER SET utf8   comment='Leap seconds information for time zones';
diff --git a/scripts/mysql_test_data_timezone.sql b/scripts/mysql_test_data_timezone.sql
index b4902c358ea..8d07d413cef 100644
--- a/scripts/mysql_test_data_timezone.sql
+++ b/scripts/mysql_test_data_timezone.sql
@@ -17,5 +17,5 @@
 INSERT INTO time_zone_name (Name, Time_Zone_id) VALUES   ('MET', 1), ('UTC', 2), ('Universal', 2),    ('Europe/Moscow',3), ('leap/Europe/Moscow',4),    ('Japan', 5);
 INSERT INTO time_zone (Time_zone_id, Use_leap_seconds)   VALUES (1,'N'), (2,'N'), (3,'N'), (4,'Y'), (5,'N');
 INSERT INTO time_zone_transition   (Time_zone_id, Transition_time, Transition_type_id) VALUES   (1, -1693706400, 0) ,(1, -1680483600, 1)  ,(1, -1663455600, 2) ,(1, -1650150000, 3)  ,(1, -1632006000, 2) ,(1, -1618700400, 3)  ,(1, -938905200, 2) ,(1, -857257200, 3)  ,(1, -844556400, 2) ,(1, -828226800, 3)  ,(1, -812502000, 2) ,(1, -796777200, 3)  ,(1, 228877200, 2) ,(1, 243997200, 3)  ,(1, 260326800, 2) ,(1, 276051600, 3)  ,(1, 291776400, 2) ,(1, 307501200, 3)  ,(1, 323830800, 2) ,(1, 338950800, 3)  ,(1, 354675600, 2) ,(1, 370400400, 3)  ,(1, 386125200, 2) ,(1, 401850000, 3)  ,(1, 417574800, 2) ,(1, 433299600, 3)  ,(1, 449024400, 2) ,(1, 465354000, 3)  ,(1, 481078800, 2) ,(1, 496803600, 3)  ,(1, 512528400, 2) ,(1, 528253200, 3)  ,(1, 543978000, 2) ,(1, 559702800, 3)  ,(1, 575427600, 2) ,(1, 591152400, 3)  ,(1, 606877200, 2) ,(1, 622602000, 3)  ,(1, 638326800, 2) ,(1, 654656400, 3)  ,(1, 670381200, 2) ,(1, 686106000, 3)  ,(1, 701830800, 2) ,(1, 717555600, 3)  ,(1, 733280400, 2) ,(1, 749005200, 3)  ,(1, 764730000, 2) ,(1, 780454800, 3)  ,(1, 796179600, 2) ,(1, 811904400, 3)  ,(1, 828234000, 2) ,(1, 846378000, 3)  ,(1, 859683600, 2) ,(1, 877827600, 3)  ,(1, 891133200, 2) ,(1, 909277200, 3)  ,(1, 922582800, 2) ,(1, 941331600, 3)  ,(1, 954032400, 2) ,(1, 972781200, 3)  ,(1, 985482000, 2) ,(1, 1004230800, 3)  ,(1, 1017536400, 2) ,(1, 1035680400, 3)  ,(1, 1048986000, 2) ,(1, 1067130000, 3)  ,(1, 1080435600, 2) ,(1, 1099184400, 3)  ,(1, 1111885200, 2) ,(1, 1130634000, 3)  ,(1, 1143334800, 2) ,(1, 1162083600, 3)  ,(1, 1174784400, 2) ,(1, 1193533200, 3)  ,(1, 1206838800, 2) ,(1, 1224982800, 3)  ,(1, 1238288400, 2) ,(1, 1256432400, 3)  ,(1, 1269738000, 2) ,(1, 1288486800, 3)  ,(1, 1301187600, 2) ,(1, 1319936400, 3)  ,(1, 1332637200, 2) ,(1, 1351386000, 3)  ,(1, 1364691600, 2) ,(1, 1382835600, 3)  ,(1, 1396141200, 2) ,(1, 1414285200, 3)  ,(1, 1427590800, 2) ,(1, 1445734800, 3)  ,(1, 1459040400, 2) ,(1, 1477789200, 3)  ,(1, 1490490000, 2) ,(1, 1509238800, 3)  ,(1, 1521939600, 2) ,(1, 1540688400, 3)  ,(1, 1553994000, 2) ,(1, 1572138000, 3)  ,(1, 1585443600, 2) ,(1, 1603587600, 3)  ,(1, 1616893200, 2) ,(1, 1635642000, 3)  ,(1, 1648342800, 2) ,(1, 1667091600, 3)  ,(1, 1679792400, 2) ,(1, 1698541200, 3)  ,(1, 1711846800, 2) ,(1, 1729990800, 3)  ,(1, 1743296400, 2) ,(1, 1761440400, 3)  ,(1, 1774746000, 2) ,(1, 1792890000, 3)  ,(1, 1806195600, 2) ,(1, 1824944400, 3)  ,(1, 1837645200, 2) ,(1, 1856394000, 3)  ,(1, 1869094800, 2) ,(1, 1887843600, 3)  ,(1, 1901149200, 2) ,(1, 1919293200, 3)  ,(1, 1932598800, 2) ,(1, 1950742800, 3)  ,(1, 1964048400, 2) ,(1, 1982797200, 3)  ,(1, 1995498000, 2) ,(1, 2014246800, 3)  ,(1, 2026947600, 2) ,(1, 2045696400, 3)  ,(1, 2058397200, 2) ,(1, 2077146000, 3)  ,(1, 2090451600, 2) ,(1, 2108595600, 3)  ,(1, 2121901200, 2) ,(1, 2140045200, 3)  ,(3, -1688265000, 2) ,(3, -1656819048, 1)  ,(3, -1641353448, 2) ,(3, -1627965048, 3)  ,(3, -1618716648, 1) ,(3, -1596429048, 3)  ,(3, -1593829848, 5) ,(3, -1589860800, 4)  ,(3, -1542427200, 5) ,(3, -1539493200, 6)  ,(3, -1525323600, 5) ,(3, -1522728000, 4)  ,(3, -1491188400, 7) ,(3, -1247536800, 4)  ,(3, 354920400, 5) ,(3, 370728000, 4)  ,(3, 386456400, 5) ,(3, 402264000, 4)  ,(3, 417992400, 5) ,(3, 433800000, 4)  ,(3, 449614800, 5) ,(3, 465346800, 8)  ,(3, 481071600, 9) ,(3, 496796400, 8)  ,(3, 512521200, 9) ,(3, 528246000, 8)  ,(3, 543970800, 9) ,(3, 559695600, 8)  ,(3, 575420400, 9) ,(3, 591145200, 8)  ,(3, 606870000, 9) ,(3, 622594800, 8)  ,(3, 638319600, 9) ,(3, 654649200, 8)  ,(3, 670374000, 10) ,(3, 686102400, 11)  ,(3, 695779200, 8) ,(3, 701812800, 5)  ,(3, 717534000, 4) ,(3, 733273200, 9)  ,(3, 748998000, 8) ,(3, 764722800, 9)  ,(3, 780447600, 8) ,(3, 796172400, 9)  ,(3, 811897200, 8) ,(3, 828226800, 9)  ,(3, 846370800, 8) ,(3, 859676400, 9)  ,(3, 877820400, 8) ,(3, 891126000, 9)  ,(3, 909270000, 8) ,(3, 922575600, 9)  ,(3, 941324400, 8) ,(3, 954025200, 9)  ,(3, 972774000, 8) ,(3, 985474800, 9)  ,(3, 1004223600, 8) ,(3, 1017529200, 9)  ,(3, 1035673200, 8) ,(3, 1048978800, 9)  ,(3, 1067122800, 8) ,(3, 1080428400, 9)  ,(3, 1099177200, 8) ,(3, 1111878000, 9)  ,(3, 1130626800, 8) ,(3, 1143327600, 9)  ,(3, 1162076400, 8) ,(3, 1174777200, 9)  ,(3, 1193526000, 8) ,(3, 1206831600, 9)  ,(3, 1224975600, 8) ,(3, 1238281200, 9)  ,(3, 1256425200, 8) ,(3, 1269730800, 9)  ,(3, 1288479600, 8) ,(3, 1301180400, 9)  ,(3, 1319929200, 8) ,(3, 1332630000, 9)  ,(3, 1351378800, 8) ,(3, 1364684400, 9)  ,(3, 1382828400, 8) ,(3, 1396134000, 9)  ,(3, 1414278000, 8) ,(3, 1427583600, 9)  ,(3, 1445727600, 8) ,(3, 1459033200, 9)  ,(3, 1477782000, 8) ,(3, 1490482800, 9)  ,(3, 1509231600, 8) ,(3, 1521932400, 9)  ,(3, 1540681200, 8) ,(3, 1553986800, 9)  ,(3, 1572130800, 8) ,(3, 1585436400, 9)  ,(3, 1603580400, 8) ,(3, 1616886000, 9)  ,(3, 1635634800, 8) ,(3, 1648335600, 9)  ,(3, 1667084400, 8) ,(3, 1679785200, 9)  ,(3, 1698534000, 8) ,(3, 1711839600, 9)  ,(3, 1729983600, 8) ,(3, 1743289200, 9)  ,(3, 1761433200, 8) ,(3, 1774738800, 9)  ,(3, 1792882800, 8) ,(3, 1806188400, 9)  ,(3, 1824937200, 8) ,(3, 1837638000, 9)  ,(3, 1856386800, 8) ,(3, 1869087600, 9)  ,(3, 1887836400, 8) ,(3, 1901142000, 9)  ,(3, 1919286000, 8) ,(3, 1932591600, 9)  ,(3, 1950735600, 8) ,(3, 1964041200, 9)  ,(3, 1982790000, 8) ,(3, 1995490800, 9)  ,(3, 2014239600, 8) ,(3, 2026940400, 9)  ,(3, 2045689200, 8) ,(3, 2058390000, 9)  ,(3, 2077138800, 8) ,(3, 2090444400, 9)  ,(3, 2108588400, 8) ,(3, 2121894000, 9)  ,(3, 2140038000, 8)  ,(4, -1688265000, 2) ,(4, -1656819048, 1)  ,(4, -1641353448, 2) ,(4, -1627965048, 3)  ,(4, -1618716648, 1) ,(4, -1596429048, 3)  ,(4, -1593829848, 5) ,(4, -1589860800, 4)  ,(4, -1542427200, 5) ,(4, -1539493200, 6)  ,(4, -1525323600, 5) ,(4, -1522728000, 4)  ,(4, -1491188400, 7) ,(4, -1247536800, 4)  ,(4, 354920409, 5) ,(4, 370728010, 4)  ,(4, 386456410, 5) ,(4, 402264011, 4)  ,(4, 417992411, 5) ,(4, 433800012, 4)  ,(4, 449614812, 5) ,(4, 465346812, 8)  ,(4, 481071612, 9) ,(4, 496796413, 8)  ,(4, 512521213, 9) ,(4, 528246013, 8)  ,(4, 543970813, 9) ,(4, 559695613, 8)  ,(4, 575420414, 9) ,(4, 591145214, 8)  ,(4, 606870014, 9) ,(4, 622594814, 8)  ,(4, 638319615, 9) ,(4, 654649215, 8)  ,(4, 670374016, 10) ,(4, 686102416, 11)  ,(4, 695779216, 8) ,(4, 701812816, 5)  ,(4, 717534017, 4) ,(4, 733273217, 9)  ,(4, 748998018, 8) ,(4, 764722818, 9)  ,(4, 780447619, 8) ,(4, 796172419, 9)  ,(4, 811897219, 8) ,(4, 828226820, 9)  ,(4, 846370820, 8) ,(4, 859676420, 9)  ,(4, 877820421, 8) ,(4, 891126021, 9)  ,(4, 909270021, 8) ,(4, 922575622, 9)  ,(4, 941324422, 8) ,(4, 954025222, 9)  ,(4, 972774022, 8) ,(4, 985474822, 9)  ,(4, 1004223622, 8) ,(4, 1017529222, 9)  ,(4, 1035673222, 8) ,(4, 1048978822, 9)  ,(4, 1067122822, 8) ,(4, 1080428422, 9)  ,(4, 1099177222, 8) ,(4, 1111878022, 9)  ,(4, 1130626822, 8) ,(4, 1143327622, 9)  ,(4, 1162076422, 8) ,(4, 1174777222, 9)  ,(4, 1193526022, 8) ,(4, 1206831622, 9)  ,(4, 1224975622, 8) ,(4, 1238281222, 9)  ,(4, 1256425222, 8) ,(4, 1269730822, 9)  ,(4, 1288479622, 8) ,(4, 1301180422, 9)  ,(4, 1319929222, 8) ,(4, 1332630022, 9)  ,(4, 1351378822, 8) ,(4, 1364684422, 9)  ,(4, 1382828422, 8) ,(4, 1396134022, 9)  ,(4, 1414278022, 8) ,(4, 1427583622, 9)  ,(4, 1445727622, 8) ,(4, 1459033222, 9)  ,(4, 1477782022, 8) ,(4, 1490482822, 9)  ,(4, 1509231622, 8) ,(4, 1521932422, 9)  ,(4, 1540681222, 8) ,(4, 1553986822, 9)  ,(4, 1572130822, 8) ,(4, 1585436422, 9)  ,(4, 1603580422, 8) ,(4, 1616886022, 9)  ,(4, 1635634822, 8) ,(4, 1648335622, 9)  ,(4, 1667084422, 8) ,(4, 1679785222, 9)  ,(4, 1698534022, 8) ,(4, 1711839622, 9)  ,(4, 1729983622, 8) ,(4, 1743289222, 9)  ,(4, 1761433222, 8) ,(4, 1774738822, 9)  ,(4, 1792882822, 8) ,(4, 1806188422, 9)  ,(4, 1824937222, 8) ,(4, 1837638022, 9)  ,(4, 1856386822, 8) ,(4, 1869087622, 9)  ,(4, 1887836422, 8) ,(4, 1901142022, 9)  ,(4, 1919286022, 8) ,(4, 1932591622, 9)  ,(4, 1950735622, 8) ,(4, 1964041222, 9)  ,(4, 1982790022, 8) ,(4, 1995490822, 9)  ,(4, 2014239622, 8) ,(4, 2026940422, 9)  ,(4, 2045689222, 8) ,(4, 2058390022, 9)  ,(4, 2077138822, 8) ,(4, 2090444422, 9)  ,(4, 2108588422, 8) ,(4, 2121894022, 9)  ,(4, 2140038022, 8)  ,(5, -1009875600, 1);
-INSERT INTO time_zone_transition_type (Time_zone_id,  Transition_type_id, Offset, Is_DST, Abbreviation) VALUES   (1, 0, 7200, 1, 'MEST') ,(1, 1, 3600, 0, 'MET')  ,(1, 2, 7200, 1, 'MEST') ,(1, 3, 3600, 0, 'MET')  ,(2, 0, 0, 0, 'UTC')  ,(3, 0, 9000, 0, 'MMT') ,(3, 1, 12648, 1, 'MST')  ,(3, 2, 9048, 0, 'MMT') ,(3, 3, 16248, 1, 'MDST')  ,(3, 4, 10800, 0, 'MSK') ,(3, 5, 14400, 1, 'MSD')  ,(3, 6, 18000, 1, 'MSD') ,(3, 7, 7200, 0, 'EET')  ,(3, 8, 10800, 0, 'MSK') ,(3, 9, 14400, 1, 'MSD')  ,(3, 10, 10800, 1, 'EEST') ,(3, 11, 7200, 0, 'EET')  ,(4, 0, 9000, 0, 'MMT') ,(4, 1, 12648, 1, 'MST')  ,(4, 2, 9048, 0, 'MMT') ,(4, 3, 16248, 1, 'MDST')  ,(4, 4, 10800, 0, 'MSK') ,(4, 5, 14400, 1, 'MSD')  ,(4, 6, 18000, 1, 'MSD') ,(4, 7, 7200, 0, 'EET')  ,(4, 8, 10800, 0, 'MSK') ,(4, 9, 14400, 1, 'MSD')  ,(4, 10, 10800, 1, 'EEST') ,(4, 11, 7200, 0, 'EET')  ,(5, 0, 32400, 0, 'CJT') ,(5, 1, 32400, 0, 'JST');
+INSERT INTO time_zone_transition_type (Time_zone_id,  Transition_type_id, `Offset`, Is_DST, Abbreviation) VALUES   (1, 0, 7200, 1, 'MEST') ,(1, 1, 3600, 0, 'MET')  ,(1, 2, 7200, 1, 'MEST') ,(1, 3, 3600, 0, 'MET')  ,(2, 0, 0, 0, 'UTC')  ,(3, 0, 9000, 0, 'MMT') ,(3, 1, 12648, 1, 'MST')  ,(3, 2, 9048, 0, 'MMT') ,(3, 3, 16248, 1, 'MDST')  ,(3, 4, 10800, 0, 'MSK') ,(3, 5, 14400, 1, 'MSD')  ,(3, 6, 18000, 1, 'MSD') ,(3, 7, 7200, 0, 'EET')  ,(3, 8, 10800, 0, 'MSK') ,(3, 9, 14400, 1, 'MSD')  ,(3, 10, 10800, 1, 'EEST') ,(3, 11, 7200, 0, 'EET')  ,(4, 0, 9000, 0, 'MMT') ,(4, 1, 12648, 1, 'MST')  ,(4, 2, 9048, 0, 'MMT') ,(4, 3, 16248, 1, 'MDST')  ,(4, 4, 10800, 0, 'MSK') ,(4, 5, 14400, 1, 'MSD')  ,(4, 6, 18000, 1, 'MSD') ,(4, 7, 7200, 0, 'EET')  ,(4, 8, 10800, 0, 'MSK') ,(4, 9, 14400, 1, 'MSD')  ,(4, 10, 10800, 1, 'EEST') ,(4, 11, 7200, 0, 'EET')  ,(5, 0, 32400, 0, 'CJT') ,(5, 1, 32400, 0, 'JST');
 INSERT INTO time_zone_leap_second   (Transition_time, Correction) VALUES   (78796800, 1) ,(94694401, 2) ,(126230402, 3) ,(157766403, 4) ,(189302404, 5) ,(220924805, 6) ,(252460806, 7) ,(283996807, 8) ,(315532808, 9) ,(362793609, 10) ,(394329610, 11) ,(425865611, 12) ,(489024012, 13) ,(567993613, 14) ,(631152014, 15) ,(662688015, 16) ,(709948816, 17) ,(741484817, 18) ,(773020818, 19) ,(820454419, 20) ,(867715220, 21) ,(915148821, 22);
diff --git a/sql/share/errmsg-utf8.txt b/sql/share/errmsg-utf8.txt
index 293dede8d29..4cb89ed3422 100644
--- a/sql/share/errmsg-utf8.txt
+++ b/sql/share/errmsg-utf8.txt
@@ -7983,3 +7983,5 @@ ER_JSON_TABLE_SCALAR_EXPECTED
         eng "Can't store an array or an object in the scalar column '%s' of JSON_TABLE '%s'."
 ER_JSON_TABLE_MULTIPLE_MATCHES
         eng "Can't store multiple matches of the path in the column '%s' of JSON_TABLE '%s'."
+ER_WITH_TIES_NEEDS_ORDER
+        eng "FETCH ... WITH TIES requires ORDER BY clause to be present"
diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc
index 69eaf5e174b..2d652a194d6 100644
--- a/sql/sql_lex.cc
+++ b/sql/sql_lex.cc
@@ -3776,13 +3776,34 @@ void st_select_lex::print_limit(THD *thd,
   if (limit_params.explicit_limit &&
       limit_params.select_limit)
   {
-    str->append(STRING_WITH_LEN(" limit "));
-    if (limit_params.offset_limit)
+    /*
+      [OFFSET n]
+      FETCH FIRST n ROWS WITH TIES
+
+      For FETCH FIRST n ROWS ONLY we fall back to the "limit" specification
+      as it's identical.
+    */
+    if (limit_params.with_ties)
     {
-      limit_params.offset_limit->print(str, query_type);
-      str->append(',');
+      if (limit_params.offset_limit)
+      {
+        str->append(STRING_WITH_LEN(" offset "));
+        limit_params.offset_limit->print(str, query_type);
+      }
+      str->append(STRING_WITH_LEN(" fetch first "));
+      limit_params.select_limit->print(str, query_type);
+      str->append(STRING_WITH_LEN(" rows with ties"));
+    }
+    else
+    {
+      str->append(STRING_WITH_LEN(" limit "));
+      if (limit_params.offset_limit)
+      {
+        limit_params.offset_limit->print(str, query_type);
+        str->append(',');
+      }
+      limit_params.select_limit->print(str, query_type);
     }
-    limit_params.select_limit->print(str, query_type);
   }
 }
 
@@ -4195,7 +4216,7 @@ void st_select_lex_unit::set_limit(st_select_lex *sl)
 {
   DBUG_ASSERT(!thd->stmt_arena->is_stmt_prepare());
 
-  lim.set_limit(sl->get_limit(), sl->get_offset());
+  lim.set_limit(sl->get_limit(), sl->get_offset(), sl->limit_params.with_ties);
 }
 
 
diff --git a/sql/sql_limit.h b/sql/sql_limit.h
index 60034201a50..b51ebf72493 100644
--- a/sql/sql_limit.h
+++ b/sql/sql_limit.h
@@ -23,20 +23,23 @@
 class Select_limit_counters
 {
   ha_rows select_limit_cnt, offset_limit_cnt;
+  bool with_ties;
 
   public:
     Select_limit_counters():
-       select_limit_cnt(0), offset_limit_cnt(0)
+       select_limit_cnt(0), offset_limit_cnt(0), with_ties(false)
        {};
-    Select_limit_counters(Select_limit_counters &orig):
+    Select_limit_counters(const Select_limit_counters &orig):
        select_limit_cnt(orig.select_limit_cnt),
-       offset_limit_cnt(orig.offset_limit_cnt)
+       offset_limit_cnt(orig.offset_limit_cnt),
+       with_ties(orig.with_ties)
        {};
 
-   void set_limit(ha_rows limit, ha_rows offset)
+   void set_limit(ha_rows limit, ha_rows offset, bool with_ties_arg)
    {
       offset_limit_cnt= offset;
       select_limit_cnt= limit;
+      with_ties= with_ties_arg;
       /*
         Guard against an overflow condition, where limit + offset exceede
         ha_rows value range. This case covers unreasonably large parameter
@@ -53,6 +56,7 @@ class Select_limit_counters
    {
      offset_limit_cnt= 0;
      select_limit_cnt= 1;
+     with_ties= false;
    }
 
    bool is_unlimited() const
@@ -67,7 +71,7 @@ class Select_limit_counters
 
    /* Reset the limit entirely. */
    void clear()
-   { select_limit_cnt= HA_POS_ERROR; offset_limit_cnt= 0; }
+   { select_limit_cnt= HA_POS_ERROR; offset_limit_cnt= 0; with_ties= false;}
 
    bool check_offset(ha_rows sent) const
    {
@@ -79,6 +83,8 @@ class Select_limit_counters
    { return select_limit_cnt; }
    ha_rows get_offset_limit() const
    { return offset_limit_cnt; }
+   bool is_with_ties() const
+   { return with_ties; }
 };
 
 #endif // INCLUDES_MARIADB_SQL_LIMIT_H
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 5cd3f88352a..7dc3612c011 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -260,7 +260,8 @@ static TABLE *get_sort_by_table(ORDER *a,ORDER *b,List<TABLE_LIST> &tables,
 static void calc_group_buffer(JOIN *join, ORDER *group);
 static bool make_group_fields(JOIN *main_join, JOIN *curr_join);
 static bool alloc_group_fields(JOIN *join, ORDER *group);
-static bool alloc_order_fields(JOIN *join, ORDER *group);
+static bool alloc_order_fields(JOIN *join, ORDER *group,
+                               uint max_number_of_elements);
 // Create list for using with tempory table
 static bool change_to_use_tmp_fields(THD *thd, Ref_ptr_array ref_pointer_array,
 				     List<Item> &new_list1,
@@ -1393,9 +1394,23 @@ JOIN::prepare(TABLE_LIST *tables_init, COND *conds_init, uint og_num,
 
   if (order)
   {
-    bool real_order= FALSE;
-    ORDER *ord;
-    for (ord= order; ord; ord= ord->next)
+    bool requires_sorting= FALSE;
+    /*
+      WITH TIES forces the results to be sorted, even if it's not sanely
+      sortable.
+    */
+    if (select_lex->limit_params.with_ties)
+      requires_sorting= true;
+
+    /*
+      Go through each ORDER BY item and perform the following:
+      1. Detect if none of the items contain meaningful data, which means we
+         can drop the sorting altogether.
+      2. Split any columns with aggregation functions or window functions into
+         their base components and store them as separate fields.
+         (see split_sum_func) for more details.
+    */
+    for (ORDER *ord= order; ord; ord= ord->next)
     {
       Item *item= *ord->item;
       /*
@@ -1404,7 +1419,7 @@ JOIN::prepare(TABLE_LIST *tables_init, COND *conds_init, uint og_num,
         zero length NOT NULL string functions there.
         Such tuples don't contain any data to sort.
       */
-      if (!real_order &&
+      if (!requires_sorting &&
            /* Not a zero length NOT NULL field */
           ((item->type() != Item::FIELD_ITEM ||
             ((Item_field *) item)->field->maybe_null() ||
@@ -1414,15 +1429,26 @@ JOIN::prepare(TABLE_LIST *tables_init, COND *conds_init, uint og_num,
             item->maybe_null ||
             item->result_type() != STRING_RESULT ||
             item->max_length)))
-        real_order= TRUE;
+        requires_sorting= TRUE;
 
       if ((item->with_sum_func() && item->type() != Item::SUM_FUNC_ITEM) ||
           item->with_window_func)
         item->split_sum_func(thd, ref_ptrs, all_fields, SPLIT_SUM_SELECT);
     }
-    if (!real_order)
+    /* Drop the ORDER BY clause if none of the columns contain any data that
+       can produce a meaningful sorted set. */
+    if (!requires_sorting)
       order= NULL;
   }
+  else
+  {
+    /* The current select does not have an ORDER BY */
+    if (select_lex->limit_params.with_ties)
+    {
+      my_error(ER_WITH_TIES_NEEDS_ORDER, MYF(0));
+      DBUG_RETURN(-1);
+    }
+  }
 
   if (having && having->with_sum_func())
     having->split_sum_func2(thd, ref_ptrs, all_fields,
@@ -2615,6 +2641,19 @@ int JOIN::optimize_stage2()
     if (!order && org_order)
       skip_sort_order= 1;
   }
+
+  /*
+    For FETCH ... WITH TIES save how many items order by had, after we've
+    removed constant items that have no relevance on the final sorting.
+  */
+  if (unit->lim.is_with_ties())
+  {
+    DBUG_ASSERT(with_ties_order_count == 0);
+    for (ORDER *it= order; it; it= it->next)
+      with_ties_order_count+= 1;
+  }
+
+
   /*
      Check if we can optimize away GROUP BY/DISTINCT.
      We can do that if there are no aggregate functions, the
@@ -2802,7 +2841,7 @@ int JOIN::optimize_stage2()
   if (test_if_subpart(group_list, order) ||
       (!group_list && tmp_table_param.sum_func_count))
   {
-    order=0;
+    order= 0;
     if (is_indexed_agg_distinct(this, NULL))
       sort_and_group= 0;
   }
@@ -3738,6 +3777,9 @@ bool JOIN::make_aggr_tables_info()
       sort_tab->filesort->limit=
         (has_group_by || (join_tab + top_join_tab_count > curr_tab + 1)) ?
          select_limit : unit->lim.get_select_limit();
+
+      if (unit->lim.is_with_ties())
+        sort_tab->filesort->limit= HA_POS_ERROR;
     }
     if (!only_const_tables() &&
         !join_tab[const_tables].filesort &&
@@ -3774,6 +3816,18 @@ bool JOIN::make_aggr_tables_info()
   if (select_lex->custom_agg_func_used())
     status_var_increment(thd->status_var.feature_custom_aggregate_functions);
 
+  /*
+    Allocate Cached_items of ORDER BY for FETCH FIRST .. WITH TIES.
+    The order list might have been modified prior to this, but we are
+    only interested in the initial order by columns, after all const
+    elements are removed.
+  */
+  if (unit->lim.is_with_ties())
+  {
+    if (alloc_order_fields(this, order, with_ties_order_count))
+      DBUG_RETURN(true);
+  }
+
   fields= curr_fields_list;
   // Reset before execution
   set_items_ref_array(items0);
@@ -21982,6 +22036,19 @@ end_send(JOIN *join, JOIN_TAB *join_tab, bool end_of_records)
       DBUG_RETURN(NESTED_LOOP_ERROR);
     DBUG_RETURN(NESTED_LOOP_OK);
   }
+
+  if (join->send_records >= join->unit->lim.get_select_limit() &&
+      join->unit->lim.is_with_ties())
+  {
+    /*
+      Stop sending rows if the order fields corresponding to WITH TIES
+      have changed.
+    */
+    int idx= test_if_item_cache_changed(join->order_fields);
+    if (idx >= 0)
+      join->do_send_rows= false;
+  }
+
   if (join->do_send_rows)
   {
     int error;
@@ -21998,27 +22065,36 @@ end_send(JOIN *join, JOIN_TAB *join_tab, bool end_of_records)
   }
 
   ++join->send_records;
-  if (join->send_records >= join->unit->lim.get_select_limit() &&
-      !join->do_send_rows)
+  if (join->send_records >= join->unit->lim.get_select_limit())
   {
-    /*
-      If we have used Priority Queue for optimizing order by with limit,
-      then stop here, there are no more records to consume.
-      When this optimization is used, end_send is called on the next
-      join_tab.
-    */
-    if (join->order &&
-        join->select_options & OPTION_FOUND_ROWS &&
-        join_tab > join->join_tab &&
-        (join_tab - 1)->filesort && (join_tab - 1)->filesort->using_pq)
+    if (!join->do_send_rows)
     {
-      DBUG_PRINT("info", ("filesort NESTED_LOOP_QUERY_LIMIT"));
-      DBUG_RETURN(NESTED_LOOP_QUERY_LIMIT);
+      /*
+        If we have used Priority Queue for optimizing order by with limit,
+        then stop here, there are no more records to consume.
+        When this optimization is used, end_send is called on the next
+        join_tab.
+      */
+      if (join->order &&
+          join->select_options & OPTION_FOUND_ROWS &&
+          join_tab > join->join_tab &&
+          (join_tab - 1)->filesort && (join_tab - 1)->filesort->using_pq)
+      {
+        DBUG_PRINT("info", ("filesort NESTED_LOOP_QUERY_LIMIT"));
+        DBUG_RETURN(NESTED_LOOP_QUERY_LIMIT);
+      }
+      DBUG_RETURN(NESTED_LOOP_OK);
+    }
+
+    /* For WITH TIES we keep sending rows until a group has changed. */
+    if (join->unit->lim.is_with_ties())
+    {
+      /* Prepare the order_fields comparison for with ties. */
+      if (join->send_records == join->unit->lim.get_select_limit())
+        (void) test_if_group_changed(join->order_fields);
+      /* One more loop, to check if the next row matches with_ties or not. */
+      DBUG_RETURN(NESTED_LOOP_OK);
     }
-  }
-  if (join->send_records >= join->unit->lim.get_select_limit() &&
-      join->do_send_rows)
-  {
     if (join->select_options & OPTION_FOUND_ROWS)
     {
       JOIN_TAB *jt=join->join_tab;
@@ -22095,6 +22171,7 @@ end_send_group(JOIN *join, JOIN_TAB *join_tab, bool end_of_records)
     {
       if (join->procedure)
 	join->procedure->end_group();
+      /* Test if there was a group change. */
       if (idx < (int) join->send_group_parts)
       {
 	int error=0;
@@ -22113,6 +22190,7 @@ end_send_group(JOIN *join, JOIN_TAB *join_tab, bool end_of_records)
 	}
 	else
 	{
+          /* Reset all sum functions on group change. */
 	  if (!join->first_record)
 	  {
             List_iterator_fast<Item> it(*join->fields);
@@ -22152,21 +22230,27 @@ end_send_group(JOIN *join, JOIN_TAB *join_tab, bool end_of_records)
           DBUG_RETURN(NESTED_LOOP_ERROR);        /* purecov: inspected */
 	if (end_of_records)
 	  DBUG_RETURN(NESTED_LOOP_OK);
-	if (join->send_records >= join->unit->lim.get_select_limit() &&
-	    join->do_send_rows)
-	{
-	  if (!(join->select_options & OPTION_FOUND_ROWS))
-	    DBUG_RETURN(NESTED_LOOP_QUERY_LIMIT); // Abort nicely
-	  join->do_send_rows=0;
-	  join->unit->lim.set_unlimited();
+        if (join->send_records >= join->unit->lim.get_select_limit() &&
+            join->do_send_rows)
+        {
+          /* WITH TIES can be computed during end_send_group if
+             the order by is a subset of group by and we had an index
+             available to compute group by order directly. */
+          if (!join->unit->lim.is_with_ties() ||
+              idx < (int)join->with_ties_order_count)
+          {
+            if (!(join->select_options & OPTION_FOUND_ROWS))
+              DBUG_RETURN(NESTED_LOOP_QUERY_LIMIT); // Abort nicely
+            join->do_send_rows= 0;
+            join->unit->lim.set_unlimited();
+          }
         }
         else if (join->send_records >= join->fetch_limit)
         {
           /*
             There is a server side cursor and all rows
             for this fetch request are sent.
-          */
-          /*
+
             Preventing code duplication. When finished with the group reset
             the group functions and copy_fields. We fall through. bug #11904
           */
@@ -25221,6 +25305,19 @@ make_group_fields(JOIN *main_join, JOIN *curr_join)
   return (0);
 }
 
+static bool
+fill_cached_item_list(THD *thd, List<Cached_item> *list, ORDER *order,
+                      uint max_number_of_elements = UINT_MAX)
+{
+  for (; order && max_number_of_elements ;
+       order= order->next, max_number_of_elements--)
+  {
+    Cached_item *tmp= new_Cached_item(thd, *order->item, true);
+    if (!tmp || list->push_front(tmp))
+      return true;
+  }
+  return false;
+}
 
 /**
   Get a list of buffers for saving last group.
@@ -25229,21 +25326,20 @@ make_group_fields(JOIN *main_join, JOIN *curr_join)
 */
 
 static bool
-alloc_group_fields(JOIN *join,ORDER *group)
+alloc_group_fields(JOIN *join, ORDER *group)
 {
-  if (group)
-  {
-    for (; group ; group=group->next)
-    {
-      Cached_item *tmp=new_Cached_item(join->thd, *group->item, TRUE);
-      if (!tmp || join->group_fields.push_front(tmp))
-	return TRUE;
-    }
-  }
+  if (fill_cached_item_list(join->thd, &join->group_fields, group))
+    return true;
   join->sort_and_group=1;			/* Mark for do_select */
-  return FALSE;
+  return false;
 }
 
+static bool
+alloc_order_fields(JOIN *join, ORDER *order, uint max_number_of_elements)
+{
+  return fill_cached_item_list(join->thd, &join->order_fields, order,
+                               max_number_of_elements);
+}
 
 
 /*
diff --git a/sql/sql_select.h b/sql/sql_select.h
index ff2b24fdbf7..bd7c0c361c6 100644
--- a/sql/sql_select.h
+++ b/sql/sql_select.h
@@ -1178,6 +1178,16 @@ public:
   uint     aggr_tables;     ///< Number of post-join tmp tables 
   uint	   send_group_parts;
   /*
+    This represents the number of items in ORDER BY *after* removing
+    all const items. This is computed before other optimizations take place,
+    such as removal of ORDER BY when it is a prefix of GROUP BY, for example:
+    GROUP BY a, b ORDER BY a
+
+    This is used when deciding to send rows, by examining the correct number
+    of items in the group_fields list when ORDER BY was previously eliminated.
+  */
+  uint with_ties_order_count;
+  /*
     True if the query has GROUP BY.
     (that is, if group_by != NULL. when DISTINCT is converted into GROUP BY, it
      will set this, too. It is not clear why we need a separate var from 
@@ -1306,6 +1316,10 @@ public:
   */
   double   join_record_count;
   List<Item> *fields;
+
+  /* Used only for FETCH ... WITH TIES to identify peers. */
+  List<Cached_item> order_fields;
+  /* Used during GROUP BY operations to identify when a group has changed. */
   List<Cached_item> group_fields, group_fields_cache;
   THD	   *thd;
   Item_sum  **sum_funcs, ***sum_funcs_end;
@@ -1609,6 +1623,8 @@ public:
     sjm_lookup_tables= 0;
     sjm_scan_tables= 0;
     is_orig_degenerated= false;
+
+    with_ties_order_count= 0;
   }
 
   /* True if the plan guarantees that it will be returned zero or one row */
diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy
index fa8be38e7a2..e62a0484d6a 100644
--- a/sql/sql_yacc.yy
+++ b/sql/sql_yacc.yy
@@ -347,7 +347,7 @@ static_assert(sizeof(YYSTYPE) == sizeof(void*)*2+8, "%union size check");
 bool my_yyoverflow(short **a, YYSTYPE **b, size_t *yystacksize);
 %}
 
-%pure-parser                                    /* We have threads */
+%define api.pure                                    /* We have threads */
 %parse-param { THD *thd }
 %lex-param { THD *thd }
 /*
@@ -1625,6 +1625,7 @@ End SQL_MODE_ORACLE_SPECIFIC */
         opt_lock_wait_timeout_new
 
 %type <select_limit> opt_limit_clause limit_clause limit_options
+                     fetch_first_clause
 
 %type <order_limit_lock>
         query_expression_tail
@@ -1731,6 +1732,7 @@ End SQL_MODE_ORACLE_SPECIFIC */
 
 %type <num> view_algorithm view_check_option
 %type <view_suid> view_suid opt_view_suid
+%type <num> only_or_with_ties
 
 %type <plsql_cursor_attr> plsql_cursor_attr
 %type <sp_suid> sp_suid
@@ -12484,8 +12486,77 @@ limit_clause:
             $$.clear();
             Lex->set_stmt_unsafe(LEX::BINLOG_STMT_UNSAFE_LIMIT);
           }
+        | fetch_first_clause
+          {
+            $$= $1;
+            if (!$$.select_limit ||
+                !$$.select_limit->basic_const_item() ||
+                 $$.select_limit->val_int() > 0)
+              Lex->set_stmt_unsafe(LEX::BINLOG_STMT_UNSAFE_LIMIT);
+          }
         ;
 
+fetch_first_clause:
+          FETCH_SYM first_or_next row_or_rows only_or_with_ties
+          {
+            Item *one= new (thd->mem_root) Item_int(thd, (int32) 1);
+            if (unlikely(one == NULL))
+              MYSQL_YYABORT;
+            $$.select_limit= one;
+            $$.offset_limit= 0;
+            $$.explicit_limit= true;
+            $$.with_ties= $4;
+          }
+        | OFFSET_SYM limit_option
+          FETCH_SYM first_or_next row_or_rows only_or_with_ties
+          {
+            Item *one= new (thd->mem_root) Item_int(thd, (int32) 1);
+            if (unlikely(one == NULL))
+              MYSQL_YYABORT;
+            $$.select_limit= one;
+            $$.offset_limit= $2;
+            $$.explicit_limit= true;
+            $$.with_ties= $6;
+          }
+        | FETCH_SYM first_or_next limit_option row_or_rows only_or_with_ties
+          {
+            $$.select_limit= $3;
+            $$.offset_limit= 0;
+            $$.explicit_limit= true;
+            $$.with_ties= $5;
+          }
+        | OFFSET_SYM limit_option
+          FETCH_SYM first_or_next limit_option row_or_rows only_or_with_ties
+          {
+            $$.select_limit= $5;
+            $$.offset_limit= $2;
+            $$.explicit_limit= true;
+            $$.with_ties= $7;
+          }
+        | OFFSET_SYM limit_option
+          {
+            $$.select_limit= 0;
+            $$.offset_limit= $2;
+            $$.explicit_limit= true;
+            $$.with_ties= false;
+          }
+        ;
+
+first_or_next:
+          FIRST_SYM
+        | NEXT_SYM
+        ;
+row_or_rows:
+          ROW_SYM
+        | ROWS_SYM
+        ;
+
+only_or_with_ties:
+          ONLY_SYM      { $$= 0; }
+        | WITH TIES_SYM { $$= 1; }
+        ;
+
+
 opt_global_limit_clause:
           opt_limit_clause
           {
@@ -12497,20 +12568,23 @@ limit_options:
           limit_option
           {
             $$.select_limit= $1;
-            $$.offset_limit= 0;
-            $$.explicit_limit= 1;
+            $$.offset_limit= NULL;
+            $$.explicit_limit= true;
+            $$.with_ties= false;
           }
         | limit_option ',' limit_option
           {
             $$.select_limit= $3;
             $$.offset_limit= $1;
-            $$.explicit_limit= 1;
+            $$.explicit_limit= true;
+            $$.with_ties= false;
           }
         | limit_option OFFSET_SYM limit_option
           {
             $$.select_limit= $1;
             $$.offset_limit= $3;
-            $$.explicit_limit= 1;
+            $$.explicit_limit= true;
+            $$.with_ties= false;
           }
         ;
 
@@ -12633,6 +12707,7 @@ opt_procedure_or_into:
           }
         ;
 
+
 order_or_limit:
           order_clause opt_limit_clause
           {
@@ -15554,6 +15629,7 @@ keyword_sysvar_name:
         | WINDOW_SYM
         | EXCEPTION_ORACLE_SYM
         | IGNORED_SYM
+        | OFFSET_SYM
         ;
 
 keyword_set_usual_case:
@@ -15569,6 +15645,7 @@ keyword_set_usual_case:
         | WINDOW_SYM
         | EXCEPTION_ORACLE_SYM
         | IGNORED_SYM
+        | OFFSET_SYM
         ;
 
 non_reserved_keyword_udt:
@@ -15579,6 +15656,7 @@ non_reserved_keyword_udt:
         | keyword_sp_block_section
         | keyword_sysvar_type
         | keyword_sp_var_and_label
+        | OFFSET_SYM
         ;
 
 /*
@@ -15965,7 +16043,6 @@ keyword_sp_var_and_label:
         | NONE_SYM
         | NOTFOUND_SYM
         | OF_SYM
-        | OFFSET_SYM
         | OLD_PASSWORD_SYM
         | ONE_SYM
         | ONLINE_SYM
diff --git a/sql/structs.h b/sql/structs.h
index adb1fec119b..d7568e02e29 100644
--- a/sql/structs.h
+++ b/sql/structs.h
@@ -826,11 +826,13 @@ class Lex_select_limit
 public:
   /* explicit LIMIT clause was used */
   bool explicit_limit;
+  bool with_ties;
   Item *select_limit, *offset_limit;
 
   void clear()
   {
     explicit_limit= FALSE;    // No explicit limit given by user
+    with_ties= FALSE;         // No use of WITH TIES operator
     select_limit= NULL;       // denotes the default limit = HA_POS_ERROR
     offset_limit= NULL;       // denotes the default offset = 0
   }
diff --git a/storage/connect/mysql-test/connect/r/dbf.result b/storage/connect/mysql-test/connect/r/dbf.result
index d7b3fe0f114..7296d73a258 100644
--- a/storage/connect/mysql-test/connect/r/dbf.result
+++ b/storage/connect/mysql-test/connect/r/dbf.result
@@ -34,7 +34,7 @@ END//
 CREATE PROCEDURE test.dbf_header(in fname VARCHAR(1024)) DETERMINISTIC
 BEGIN
 DECLARE content BLOB;
-DECLARE offset INT;
+DECLARE `offset` INT;
 DECLARE fieldno INT;
 SELECT '--------';
 SELECT LOAD_FILE(fname) INTO content;
@@ -45,11 +45,11 @@ SELECT CONV(HEX(REVERSE(SUBSTRING(content,9,2))),16,10) AS FirstRecPos;
 SELECT CONV(HEX(REVERSE(SUBSTRING(content,11,2))),16,10) AS RecLength;
 SELECT HEX(REVERSE(SUBSTRING(content,29,2))) AS TableFlags;
 SELECT HEX(REVERSE(SUBSTRING(content,30,1))) AS CodePageMark;
-SET offset=33;
+SET `offset`=33;
 SET fieldno=0;
-WHILE SUBSTR(content, offset, 1) <> 0x0D AND offset + 32 < LENGTH(content) DO
-CALL dbf_field(fieldno, SUBSTRING(content, offset, 32));
-SET offset=offset + 32;
+WHILE SUBSTR(content, `offset`, 1) <> 0x0D AND `offset` + 32 < LENGTH(content) DO
+CALL dbf_field(fieldno, SUBSTRING(content, `offset`, 32));
+SET `offset`=`offset` + 32;
 SET fieldno=fieldno + 1;
 END WHILE;
 SELECT '--------';
diff --git a/storage/connect/mysql-test/connect/t/dbf.test b/storage/connect/mysql-test/connect/t/dbf.test
index b798b1a2bc5..ca5124f1a81 100644
--- a/storage/connect/mysql-test/connect/t/dbf.test
+++ b/storage/connect/mysql-test/connect/t/dbf.test
@@ -34,7 +34,7 @@ END//
 CREATE PROCEDURE test.dbf_header(in fname VARCHAR(1024)) DETERMINISTIC
 BEGIN
   DECLARE content BLOB;
-  DECLARE offset INT;
+  DECLARE `offset` INT;
   DECLARE fieldno INT;
   SELECT '--------';
   SELECT LOAD_FILE(fname) INTO content;
@@ -45,11 +45,11 @@ BEGIN
   SELECT CONV(HEX(REVERSE(SUBSTRING(content,11,2))),16,10) AS RecLength;
   SELECT HEX(REVERSE(SUBSTRING(content,29,2))) AS TableFlags;
   SELECT HEX(REVERSE(SUBSTRING(content,30,1))) AS CodePageMark;
-  SET offset=33;
+  SET `offset`=33;
   SET fieldno=0;
-  WHILE SUBSTR(content, offset, 1) <> 0x0D AND offset + 32 < LENGTH(content) DO
-    CALL dbf_field(fieldno, SUBSTRING(content, offset, 32));
-    SET offset=offset + 32;
+  WHILE SUBSTR(content, `offset`, 1) <> 0x0D AND `offset` + 32 < LENGTH(content) DO
+    CALL dbf_field(fieldno, SUBSTRING(content, `offset`, 32));
+    SET `offset`=`offset` + 32;
     SET fieldno=fieldno + 1;
   END WHILE;
   SELECT '--------';