[Commits] 45e2f89: MDEV-17894 Assertion `(thd->lex)->current_select' failed in MYSQLparse(),

6 May 2019

revision-id: 45e2f8906d5e8e4e6c6085c32d8d6245d0016b1f (mariadb-10.3.12-86-g45e2f89)
parent(s): 51e48b9f8981986257a1cfbdf75e4fc29a5959c1
author: Igor Babaev
committer: Igor Babaev
timestamp: 2019-05-06 14:58:41 -0700
message:

MDEV-17894 Assertion `(thd->lex)->current_select' failed in MYSQLparse(),
           query with VALUES()

A table value constructor can be used in all contexts where a select
can be used. In particular an ORDER BY clause or a LIMIT clause or both
of them can be attached to a table value constructor to produce a new
query. Unfortunately execution of such queries was not supported.
This patch fixes the problem.

---
 mysql-test/main/table_value_constr.result          | 355 +++++++++++++++++++++
 mysql-test/main/table_value_constr.test            | 162 ++++++++++
 .../compat/oracle/r/table_value_constr.result      | 299 +++++++++++++++++
 .../suite/compat/oracle/t/table_value_constr.test  | 134 ++++++++
 sql/item_subselect.cc                              |   2 +-
 sql/item_subselect.h                               |   2 +-
 sql/sql_lex.cc                                     |  16 +
 sql/sql_lex.h                                      |   6 +
 sql/sql_tvc.cc                                     | 203 ++++++++++--
 sql/sql_tvc.h                                      |   5 +
 sql/sql_union.cc                                   |  19 +-
 sql/sql_yacc.yy                                    |  20 +-
 sql/sql_yacc_ora.yy                                |  20 +-
 13 files changed, 1196 insertions(+), 47 deletions(-)

diff --git a/mysql-test/main/table_value_constr.result b/mysql-test/main/table_value_constr.result
index 1d485af..e5d43d4 100644
--- a/mysql-test/main/table_value_constr.result
+++ b/mysql-test/main/table_value_constr.result
@@ -2189,3 +2189,358 @@ EXECUTE stmt;
 1 + 1	2	abc
 2	2	abc
 DEALLOCATE PREPARE stmt;
+#
+# MDEV-17894: tvc with ORDER BY ... LIMIT
+#
+values (5), (7), (1), (3), (4) limit 2;
+5
+5
+7
+explain extended values (5), (7), (1), (3), (4) limit 2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+Warnings:
+Note	1003	values (5),(7),(1),(3),(4) limit 2
+values (5), (7), (1), (3), (4) limit 2 offset 1;
+5
+7
+1
+explain extended values (5), (7), (1), (3), (4) limit 2 offset 1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+Warnings:
+Note	1003	values (5),(7),(1),(3),(4) limit 1,2
+values (5), (7), (1), (3), (4) order by 1 limit 2;
+5
+1
+3
+explain extended values (5), (7), (1), (3), (4) order by 1 limit 2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+NULL	UNIT RESULT	<unit1>	ALL	NULL	NULL	NULL	NULL	NULL	NULL	Using filesort
+Warnings:
+Note	1003	values (5),(7),(1),(3),(4) order by 1 limit 2
+values (5), (7), (1), (3), (4) order by 1 limit 2 offset 1;
+5
+3
+4
+explain extended values (5), (7), (1), (3), (4) order by 1 limit 2 offset 1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+NULL	UNIT RESULT	<unit1>	ALL	NULL	NULL	NULL	NULL	NULL	NULL	Using filesort
+Warnings:
+Note	1003	values (5),(7),(1),(3),(4) order by 1 limit 1,2
+values (5), (7), (1), (3), (4) order by 1;
+5
+1
+3
+4
+5
+7
+explain extended values (5), (7), (1), (3), (4) order by 1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+NULL	UNIT RESULT	<unit1>	ALL	NULL	NULL	NULL	NULL	NULL	NULL	Using filesort
+Warnings:
+Note	1003	values (5),(7),(1),(3),(4) order by 1
+select 2 union (values (5), (7), (1), (3), (4) limit 2);
+2
+2
+5
+7
+explain extended select 2 union (values (5), (7), (1), (3), (4) limit 2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+2	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+NULL	UNION RESULT	<union1,2>	ALL	NULL	NULL	NULL	NULL	NULL	NULL	
+Warnings:
+Note	1003	/* select#1 */ select 2 AS `2` union (values (5),(7),(1),(3),(4) limit 2)
+select 2 union (values (5), (7), (1), (3), (4) limit 2 offset 1);
+2
+2
+7
+1
+explain extended select 2 union (values (5), (7), (1), (3), (4) limit 2 offset 1);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+2	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+NULL	UNION RESULT	<union1,2>	ALL	NULL	NULL	NULL	NULL	NULL	NULL	
+Warnings:
+Note	1003	/* select#1 */ select 2 AS `2` union (values (5),(7),(1),(3),(4) limit 1,2)
+select 2 union (values (5), (7), (1), (3), (4) order by 1 limit 2);
+2
+2
+1
+3
+explain extended select 2 union (values (5), (7), (1), (3), (4) order by 1 limit 2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+3	UNION	<derived2>	ALL	NULL	NULL	NULL	NULL	5	100.00	Using filesort
+2	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+NULL	UNION RESULT	<union1,3>	ALL	NULL	NULL	NULL	NULL	NULL	NULL	
+Warnings:
+Note	1003	/* select#1 */ select 2 AS `2` union (/* select#3 */ select `tvc_0`.`5` AS `5` from (values (5),(7),(1),(3),(4)) `tvc_0` order by 1 limit 2)
+select 2 union (values (5), (7), (1), (3), (4) order by 1 limit 2 offset 1);
+2
+2
+3
+4
+explain extended select 2 union (values (5), (7), (1), (3), (4) order by 1 limit 2 offset 1);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+3	UNION	<derived2>	ALL	NULL	NULL	NULL	NULL	5	100.00	Using filesort
+2	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+NULL	UNION RESULT	<union1,3>	ALL	NULL	NULL	NULL	NULL	NULL	NULL	
+Warnings:
+Note	1003	/* select#1 */ select 2 AS `2` union (/* select#3 */ select `tvc_0`.`5` AS `5` from (values (5),(7),(1),(3),(4)) `tvc_0` order by 1 limit 1,2)
+(values (5), (7), (1), (3), (4) limit 2) union select 2;
+5
+5
+7
+2
+explain extended (values (5), (7), (1), (3), (4) limit 2) union select 2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+2	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+NULL	UNION RESULT	<union1,2>	ALL	NULL	NULL	NULL	NULL	NULL	NULL	
+Warnings:
+Note	1003	(values (5),(7),(1),(3),(4) limit 2) union /* select#2 */ select 2 AS `2`
+(values (5), (7), (1), (3), (4) limit 2 offset 1) union select 2;
+5
+7
+1
+2
+explain extended (values (5), (7), (1), (3), (4) limit 2 offset 1) union select 2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+2	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+NULL	UNION RESULT	<union1,2>	ALL	NULL	NULL	NULL	NULL	NULL	NULL	
+Warnings:
+Note	1003	(values (5),(7),(1),(3),(4) limit 1,2) union /* select#2 */ select 2 AS `2`
+(values (5), (7), (1), (3), (4) order by 1 limit 2) union select 2;
+5
+1
+3
+2
+explain extended (values (5), (7), (1), (3), (4) order by 1 limit 2) union select 2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SUBQUERY	<derived3>	ALL	NULL	NULL	NULL	NULL	5	100.00	Using filesort
+3	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+2	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+NULL	UNION RESULT	<union1,2>	ALL	NULL	NULL	NULL	NULL	NULL	NULL	
+Warnings:
+Note	1003	(/* select#1 */ select `tvc_0`.`5` AS `5` from (values (5),(7),(1),(3),(4)) `tvc_0` order by 1 limit 2) union /* select#2 */ select 2 AS `2`
+(values (5), (7), (1), (3), (4) order by 1 limit 2 offset 1) union select 2;
+5
+3
+4
+2
+explain extended (values (5), (7), (1), (3), (4) order by 1 limit 2 offset 1) union select 2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SUBQUERY	<derived3>	ALL	NULL	NULL	NULL	NULL	5	100.00	Using filesort
+3	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+2	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+NULL	UNION RESULT	<union1,2>	ALL	NULL	NULL	NULL	NULL	NULL	NULL	
+Warnings:
+Note	1003	(/* select#1 */ select `tvc_0`.`5` AS `5` from (values (5),(7),(1),(3),(4)) `tvc_0` order by 1 limit 1,2) union /* select#2 */ select 2 AS `2`
+select 3 union all (values (5), (7), (1), (3), (4) limit 2 offset 3);
+3
+3
+3
+4
+explain extended select 3 union all (values (5), (7), (1), (3), (4) limit 2 offset 3);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+2	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+Warnings:
+Note	1003	/* select#1 */ select 3 AS `3` union all (values (5),(7),(1),(3),(4) limit 3,2)
+(values (5), (7), (1), (3), (4) limit 2 offset 3) union all select 3;
+5
+3
+4
+3
+explain extended (values (5), (7), (1), (3), (4) limit 2 offset 3) union all select 3;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+2	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+Warnings:
+Note	1003	(values (5),(7),(1),(3),(4) limit 3,2) union all /* select#2 */ select 3 AS `3`
+select 3 union all (values (5), (7), (1), (3), (4) order by 1 limit 2);
+3
+3
+1
+3
+explain extended select 3 union all (values (5), (7), (1), (3), (4) order by 1 limit 2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+3	UNION	<derived2>	ALL	NULL	NULL	NULL	NULL	5	100.00	Using filesort
+2	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+NULL	UNION RESULT	<union1,3>	ALL	NULL	NULL	NULL	NULL	NULL	NULL	
+Warnings:
+Note	1003	/* select#1 */ select 3 AS `3` union all (/* select#3 */ select `tvc_0`.`5` AS `5` from (values (5),(7),(1),(3),(4)) `tvc_0` order by 1 limit 2)
+(values (5), (7), (1), (3), (4) order by 1 limit 2) union all select 3;
+5
+1
+3
+3
+explain extended (values (5), (7), (1), (3), (4) order by 1 limit 2) union all select 3;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SUBQUERY	<derived3>	ALL	NULL	NULL	NULL	NULL	5	100.00	Using filesort
+3	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+2	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+NULL	UNION RESULT	<union1,2>	ALL	NULL	NULL	NULL	NULL	NULL	NULL	
+Warnings:
+Note	1003	(/* select#1 */ select `tvc_0`.`5` AS `5` from (values (5),(7),(1),(3),(4)) `tvc_0` order by 1 limit 2) union all /* select#2 */ select 3 AS `3`
+( values (5), (7), (1), (3), (4) limit 2 offset 1 )
+union
+( values (5), (7), (1), (3), (4) order by 1 limit 2 );
+5
+7
+1
+3
+explain extended ( values (5), (7), (1), (3), (4) limit 2 offset 1 )
+union
+( values (5), (7), (1), (3), (4) order by 1 limit 2 );
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+3	UNION	<derived2>	ALL	NULL	NULL	NULL	NULL	5	100.00	Using filesort
+2	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+NULL	UNION RESULT	<union1,3>	ALL	NULL	NULL	NULL	NULL	NULL	NULL	
+Warnings:
+Note	1003	(values (5),(7),(1),(3),(4) limit 1,2) union (/* select#3 */ select `tvc_0`.`5` AS `5` from (values (5),(7),(1),(3),(4)) `tvc_0` order by 1 limit 2)
+( values (5), (7), (1), (3), (4) limit 2 offset 1 )
+union all
+( values (5), (7), (1), (3), (4) order by 1 limit 2 );
+5
+7
+1
+1
+3
+explain extended ( values (5), (7), (1), (3), (4) limit 2 offset 1 )
+union all
+( values (5), (7), (1), (3), (4) order by 1 limit 2 );
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+3	UNION	<derived2>	ALL	NULL	NULL	NULL	NULL	5	100.00	Using filesort
+2	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+NULL	UNION RESULT	<union1,3>	ALL	NULL	NULL	NULL	NULL	NULL	NULL	
+Warnings:
+Note	1003	(values (5),(7),(1),(3),(4) limit 1,2) union all (/* select#3 */ select `tvc_0`.`5` AS `5` from (values (5),(7),(1),(3),(4)) `tvc_0` order by 1 limit 2)
+(values (5), (7), (1), (3), (4) limit 2 offset 3) union all select 3 order by 1;
+5
+3
+3
+4
+explain extended (values (5), (7), (1), (3), (4) limit 2 offset 3) union all select 3 order by 1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+2	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+NULL	UNION RESULT	<union1,2>	ALL	NULL	NULL	NULL	NULL	NULL	NULL	Using filesort
+Warnings:
+Note	1003	(values (5),(7),(1),(3),(4) limit 3,2) union all /* select#2 */ select 3 AS `3` order by 1
+(values (5), (7), (1), (3), (4) order by 1 limit 3 offset 1) union all select 3 order by 1;
+5
+3
+3
+4
+5
+explain extended (values (5), (7), (1), (3), (4) order by 1 limit 3 offset 1) union all select 3 order by 1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SUBQUERY	<derived3>	ALL	NULL	NULL	NULL	NULL	5	100.00	Using filesort
+3	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+2	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+NULL	UNION RESULT	<union1,2>	ALL	NULL	NULL	NULL	NULL	NULL	NULL	Using filesort
+Warnings:
+Note	1003	(/* select#1 */ select `tvc_0`.`5` AS `5` from (values (5),(7),(1),(3),(4)) `tvc_0` order by 1 limit 1,3) union all /* select#2 */ select 3 AS `3` order by 1
+(values (5), (7), (1), (3), (4) order by 1 limit 3 offset 1) union all select 3
+order by 1 limit 2 offset 1;
+5
+3
+4
+explain extended (values (5), (7), (1), (3), (4) order by 1 limit 3 offset 1) union all select 3
+order by 1 limit 2 offset 1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SUBQUERY	<derived3>	ALL	NULL	NULL	NULL	NULL	5	100.00	Using filesort
+3	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+2	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+NULL	UNION RESULT	<union1,2>	ALL	NULL	NULL	NULL	NULL	NULL	NULL	Using filesort
+Warnings:
+Note	1003	(/* select#1 */ select `tvc_0`.`5` AS `5` from (values (5),(7),(1),(3),(4)) `tvc_0` order by 1 limit 1,3) union all /* select#2 */ select 3 AS `3` order by 1 limit 1,2
+prepare stmt from "
+select 2 union (values (5), (7), (1), (3), (4) limit 2)
+";
+execute stmt;
+2
+2
+5
+7
+execute stmt;
+2
+2
+5
+7
+deallocate prepare stmt;
+prepare stmt from "
+select 2 union (values (5), (7), (1), (3), (4) order by 1 limit 2)
+";
+execute stmt;
+2
+2
+1
+3
+execute stmt;
+2
+2
+1
+3
+deallocate prepare stmt;
+prepare stmt from "
+select 3 union all (values (5), (7), (1), (3), (4) limit 2)
+";
+execute stmt;
+3
+3
+5
+7
+execute stmt;
+3
+3
+5
+7
+deallocate prepare stmt;
+prepare stmt from "
+select 3 union all (values (5), (7), (1), (3), (4) order by 1 limit 2)
+";
+execute stmt;
+3
+3
+1
+3
+execute stmt;
+3
+3
+1
+3
+deallocate prepare stmt;
+create view v1 as values (5), (7), (1), (3), (4) order by 1 limit 2;
+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 values (5),(7),(1),(3),(4) order by 1 limit 2	latin1	latin1_swedish_ci
+select * from v1;
+5
+1
+3
+drop view v1;
+create view v1 as
+( values (5), (7), (1), (3), (4) limit 2 offset 1 )
+union
+( values (5), (7), (1), (3), (4) order by 1 limit 2 );
+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 (values (5),(7),(1),(3),(4) limit 1,2) union (select `tvc_0`.`5` AS `5` from (values (5),(7),(1),(3),(4)) `tvc_0` order by 1 limit 2)	latin1	latin1_swedish_ci
+select * from v1;
+5
+7
+1
+3
+drop view v1;
diff --git a/mysql-test/main/table_value_constr.test b/mysql-test/main/table_value_constr.test
index 0dd0a7a..3980774 100644
--- a/mysql-test/main/table_value_constr.test
+++ b/mysql-test/main/table_value_constr.test
@@ -1123,3 +1123,165 @@ PREPARE stmt FROM "SELECT * FROM (VALUES(1 + 1,2,'abc')) t";
 EXECUTE stmt;
 EXECUTE stmt;
 DEALLOCATE PREPARE stmt;
+
+--echo #
+--echo # MDEV-17894: tvc with ORDER BY ... LIMIT
+--echo #
+
+let $q=
+values (5), (7), (1), (3), (4) limit 2;
+eval $q;
+eval explain extended $q;
+
+let $q=
+values (5), (7), (1), (3), (4) limit 2 offset 1;
+eval $q;
+eval explain extended $q;
+
+let $q=
+values (5), (7), (1), (3), (4) order by 1 limit 2;
+eval $q;
+eval explain extended $q;
+
+let $q=
+values (5), (7), (1), (3), (4) order by 1 limit 2 offset 1;
+eval $q;
+eval explain extended $q;
+
+let $q=
+values (5), (7), (1), (3), (4) order by 1;
+eval $q;
+eval explain extended $q;
+
+let $q=
+select 2 union (values (5), (7), (1), (3), (4) limit 2);
+eval $q;
+eval explain extended $q;
+
+let $q=
+select 2 union (values (5), (7), (1), (3), (4) limit 2 offset 1);
+eval $q;
+eval explain extended $q;
+
+let $q=
+select 2 union (values (5), (7), (1), (3), (4) order by 1 limit 2);
+eval $q;
+eval explain extended $q;
+
+let $q=
+select 2 union (values (5), (7), (1), (3), (4) order by 1 limit 2 offset 1);
+eval $q;
+eval explain extended $q;
+
+
+let $q=
+(values (5), (7), (1), (3), (4) limit 2) union select 2;
+eval $q;
+eval explain extended $q;
+
+let $q=
+(values (5), (7), (1), (3), (4) limit 2 offset 1) union select 2;
+eval $q;
+eval explain extended $q;
+
+let $q=
+(values (5), (7), (1), (3), (4) order by 1 limit 2) union select 2;
+eval $q;
+eval explain extended $q;
+
+let $q=
+(values (5), (7), (1), (3), (4) order by 1 limit 2 offset 1) union select 2;
+eval $q;
+eval explain extended $q;
+
+
+let $q=
+select 3 union all (values (5), (7), (1), (3), (4) limit 2 offset 3);
+eval $q;
+eval explain extended $q;
+
+let $q=
+(values (5), (7), (1), (3), (4) limit 2 offset 3) union all select 3;
+eval $q;
+eval explain extended $q;
+
+let $q=
+select 3 union all (values (5), (7), (1), (3), (4) order by 1 limit 2);
+eval $q;
+eval explain extended $q;
+
+let $q=
+(values (5), (7), (1), (3), (4) order by 1 limit 2) union all select 3;
+eval $q;
+eval explain extended $q;
+
+let $q=
+( values (5), (7), (1), (3), (4) limit 2 offset 1 )
+  union
+( values (5), (7), (1), (3), (4) order by 1 limit 2 );
+eval $q;
+eval explain extended $q;
+
+let $q=
+( values (5), (7), (1), (3), (4) limit 2 offset 1 )
+  union all
+( values (5), (7), (1), (3), (4) order by 1 limit 2 );
+eval $q;
+eval explain extended $q;
+
+let $q=
+(values (5), (7), (1), (3), (4) limit 2 offset 3) union all select 3 order by 1;
+eval $q;
+eval explain extended $q;
+
+let $q=
+(values (5), (7), (1), (3), (4) order by 1 limit 3 offset 1) union all select 3 order by 1;
+eval $q;
+eval explain extended $q;
+
+let $q=
+(values (5), (7), (1), (3), (4) order by 1 limit 3 offset 1) union all select 3
+ order by 1 limit 2 offset 1;
+eval $q;
+eval explain extended $q;
+
+prepare stmt from "
+select 2 union (values (5), (7), (1), (3), (4) limit 2)
+";
+execute stmt;
+execute stmt;
+deallocate prepare stmt;
+
+prepare stmt from "
+select 2 union (values (5), (7), (1), (3), (4) order by 1 limit 2)
+";
+execute stmt;
+execute stmt;
+deallocate prepare stmt;
+
+prepare stmt from "
+select 3 union all (values (5), (7), (1), (3), (4) limit 2)
+";
+execute stmt;
+execute stmt;
+deallocate prepare stmt;
+
+prepare stmt from "
+select 3 union all (values (5), (7), (1), (3), (4) order by 1 limit 2)
+";
+execute stmt;
+execute stmt;
+deallocate prepare stmt;
+
+create view v1 as values (5), (7), (1), (3), (4) order by 1 limit 2;
+show create view v1;
+select * from v1;
+drop view v1;
+
+create view v1 as
+( values (5), (7), (1), (3), (4) limit 2 offset 1 )
+  union
+( values (5), (7), (1), (3), (4) order by 1 limit 2 );
+show create view v1;
+select * from v1;
+drop view v1;
diff --git a/mysql-test/suite/compat/oracle/r/table_value_constr.result b/mysql-test/suite/compat/oracle/r/table_value_constr.result
index 18fce08..9c881c3 100644
--- a/mysql-test/suite/compat/oracle/r/table_value_constr.result
+++ b/mysql-test/suite/compat/oracle/r/table_value_constr.result
@@ -2183,3 +2183,302 @@ VALUES(1 + 1,2,'abc');
 SELECT * FROM (VALUES(1 + 1,2,'abc')) t;
 1 + 1	2	abc
 2	2	abc
+#
+# MDEV-17894: tvc with ORDER BY ... LIMIT
+#
+values (5), (7), (1), (3), (4) limit 2;
+5
+5
+7
+explain extended values (5), (7), (1), (3), (4) limit 2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+Warnings:
+Note	1003	values (5),(7),(1),(3),(4) limit 2
+values (5), (7), (1), (3), (4) limit 2 offset 1;
+5
+7
+1
+explain extended values (5), (7), (1), (3), (4) limit 2 offset 1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+Warnings:
+Note	1003	values (5),(7),(1),(3),(4) limit 1,2
+values (5), (7), (1), (3), (4) order by 1 limit 2;
+5
+1
+3
+explain extended values (5), (7), (1), (3), (4) order by 1 limit 2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+NULL	UNIT RESULT	<unit1>	ALL	NULL	NULL	NULL	NULL	NULL	NULL	Using filesort
+Warnings:
+Note	1003	values (5),(7),(1),(3),(4) order by 1 limit 2
+values (5), (7), (1), (3), (4) order by 1 limit 2 offset 1;
+5
+3
+4
+explain extended values (5), (7), (1), (3), (4) order by 1 limit 2 offset 1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+NULL	UNIT RESULT	<unit1>	ALL	NULL	NULL	NULL	NULL	NULL	NULL	Using filesort
+Warnings:
+Note	1003	values (5),(7),(1),(3),(4) order by 1 limit 1,2
+values (5), (7), (1), (3), (4) order by 1;
+5
+1
+3
+4
+5
+7
+explain extended values (5), (7), (1), (3), (4) order by 1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+NULL	UNIT RESULT	<unit1>	ALL	NULL	NULL	NULL	NULL	NULL	NULL	Using filesort
+Warnings:
+Note	1003	values (5),(7),(1),(3),(4) order by 1
+select 2 union (values (5), (7), (1), (3), (4) limit 2);
+2
+2
+5
+7
+explain extended select 2 union (values (5), (7), (1), (3), (4) limit 2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+2	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+NULL	UNION RESULT	<union1,2>	ALL	NULL	NULL	NULL	NULL	NULL	NULL	
+Warnings:
+Note	1003	/* select#1 */ select 2 AS "2" union (values (5),(7),(1),(3),(4) limit 2)
+select 2 union (values (5), (7), (1), (3), (4) limit 2 offset 1);
+2
+2
+7
+1
+explain extended select 2 union (values (5), (7), (1), (3), (4) limit 2 offset 1);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+2	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+NULL	UNION RESULT	<union1,2>	ALL	NULL	NULL	NULL	NULL	NULL	NULL	
+Warnings:
+Note	1003	/* select#1 */ select 2 AS "2" union (values (5),(7),(1),(3),(4) limit 1,2)
+select 2 union (values (5), (7), (1), (3), (4) order by 1 limit 2);
+2
+2
+1
+3
+explain extended select 2 union (values (5), (7), (1), (3), (4) order by 1 limit 2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+3	UNION	<derived2>	ALL	NULL	NULL	NULL	NULL	5	100.00	Using filesort
+2	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+NULL	UNION RESULT	<union1,3>	ALL	NULL	NULL	NULL	NULL	NULL	NULL	
+Warnings:
+Note	1003	/* select#1 */ select 2 AS "2" union (/* select#3 */ select "tvc_0"."5" AS "5" from (values (5),(7),(1),(3),(4)) "tvc_0" order by 1 limit 2)
+select 2 union (values (5), (7), (1), (3), (4) order by 1 limit 2 offset 1);
+2
+2
+3
+4
+explain extended select 2 union (values (5), (7), (1), (3), (4) order by 1 limit 2 offset 1);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+3	UNION	<derived2>	ALL	NULL	NULL	NULL	NULL	5	100.00	Using filesort
+2	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+NULL	UNION RESULT	<union1,3>	ALL	NULL	NULL	NULL	NULL	NULL	NULL	
+Warnings:
+Note	1003	/* select#1 */ select 2 AS "2" union (/* select#3 */ select "tvc_0"."5" AS "5" from (values (5),(7),(1),(3),(4)) "tvc_0" order by 1 limit 1,2)
+(values (5), (7), (1), (3), (4) limit 2) union select 2;
+5
+5
+7
+2
+explain extended (values (5), (7), (1), (3), (4) limit 2) union select 2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+2	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+NULL	UNION RESULT	<union1,2>	ALL	NULL	NULL	NULL	NULL	NULL	NULL	
+Warnings:
+Note	1003	(values (5),(7),(1),(3),(4) limit 2) union /* select#2 */ select 2 AS "2"
+(values (5), (7), (1), (3), (4) limit 2 offset 1) union select 2;
+5
+7
+1
+2
+explain extended (values (5), (7), (1), (3), (4) limit 2 offset 1) union select 2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+2	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+NULL	UNION RESULT	<union1,2>	ALL	NULL	NULL	NULL	NULL	NULL	NULL	
+Warnings:
+Note	1003	(values (5),(7),(1),(3),(4) limit 1,2) union /* select#2 */ select 2 AS "2"
+(values (5), (7), (1), (3), (4) order by 1 limit 2) union select 2;
+5
+1
+3
+2
+explain extended (values (5), (7), (1), (3), (4) order by 1 limit 2) union select 2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SUBQUERY	<derived3>	ALL	NULL	NULL	NULL	NULL	5	100.00	Using filesort
+3	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+2	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+NULL	UNION RESULT	<union1,2>	ALL	NULL	NULL	NULL	NULL	NULL	NULL	
+Warnings:
+Note	1003	(/* select#1 */ select "tvc_0"."5" AS "5" from (values (5),(7),(1),(3),(4)) "tvc_0" order by 1 limit 2) union /* select#2 */ select 2 AS "2"
+(values (5), (7), (1), (3), (4) order by 1 limit 2 offset 1) union select 2;
+5
+3
+4
+2
+explain extended (values (5), (7), (1), (3), (4) order by 1 limit 2 offset 1) union select 2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SUBQUERY	<derived3>	ALL	NULL	NULL	NULL	NULL	5	100.00	Using filesort
+3	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+2	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+NULL	UNION RESULT	<union1,2>	ALL	NULL	NULL	NULL	NULL	NULL	NULL	
+Warnings:
+Note	1003	(/* select#1 */ select "tvc_0"."5" AS "5" from (values (5),(7),(1),(3),(4)) "tvc_0" order by 1 limit 1,2) union /* select#2 */ select 2 AS "2"
+select 3 union all (values (5), (7), (1), (3), (4) limit 2 offset 3);
+3
+3
+3
+4
+explain extended select 3 union all (values (5), (7), (1), (3), (4) limit 2 offset 3);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+2	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+Warnings:
+Note	1003	/* select#1 */ select 3 AS "3" union all (values (5),(7),(1),(3),(4) limit 3,2)
+(values (5), (7), (1), (3), (4) limit 2 offset 3) union all select 3;
+5
+3
+4
+3
+explain extended (values (5), (7), (1), (3), (4) limit 2 offset 3) union all select 3;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+2	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+Warnings:
+Note	1003	(values (5),(7),(1),(3),(4) limit 3,2) union all /* select#2 */ select 3 AS "3"
+select 3 union all (values (5), (7), (1), (3), (4) order by 1 limit 2);
+3
+3
+1
+3
+explain extended select 3 union all (values (5), (7), (1), (3), (4) order by 1 limit 2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+3	UNION	<derived2>	ALL	NULL	NULL	NULL	NULL	5	100.00	Using filesort
+2	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+NULL	UNION RESULT	<union1,3>	ALL	NULL	NULL	NULL	NULL	NULL	NULL	
+Warnings:
+Note	1003	/* select#1 */ select 3 AS "3" union all (/* select#3 */ select "tvc_0"."5" AS "5" from (values (5),(7),(1),(3),(4)) "tvc_0" order by 1 limit 2)
+(values (5), (7), (1), (3), (4) order by 1 limit 2) union all select 3;
+5
+1
+3
+3
+explain extended (values (5), (7), (1), (3), (4) order by 1 limit 2) union all select 3;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SUBQUERY	<derived3>	ALL	NULL	NULL	NULL	NULL	5	100.00	Using filesort
+3	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+2	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+NULL	UNION RESULT	<union1,2>	ALL	NULL	NULL	NULL	NULL	NULL	NULL	
+Warnings:
+Note	1003	(/* select#1 */ select "tvc_0"."5" AS "5" from (values (5),(7),(1),(3),(4)) "tvc_0" order by 1 limit 2) union all /* select#2 */ select 3 AS "3"
+( values (5), (7), (1), (3), (4) limit 2 offset 1 )
+union
+( values (5), (7), (1), (3), (4) order by 1 limit 2 );
+5
+7
+1
+3
+explain extended ( values (5), (7), (1), (3), (4) limit 2 offset 1 )
+union
+( values (5), (7), (1), (3), (4) order by 1 limit 2 );
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+3	UNION	<derived2>	ALL	NULL	NULL	NULL	NULL	5	100.00	Using filesort
+2	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+NULL	UNION RESULT	<union1,3>	ALL	NULL	NULL	NULL	NULL	NULL	NULL	
+Warnings:
+Note	1003	(values (5),(7),(1),(3),(4) limit 1,2) union (/* select#3 */ select "tvc_0"."5" AS "5" from (values (5),(7),(1),(3),(4)) "tvc_0" order by 1 limit 2)
+( values (5), (7), (1), (3), (4) limit 2 offset 1 )
+union all
+( values (5), (7), (1), (3), (4) order by 1 limit 2 );
+5
+7
+1
+1
+3
+explain extended ( values (5), (7), (1), (3), (4) limit 2 offset 1 )
+union all
+( values (5), (7), (1), (3), (4) order by 1 limit 2 );
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+3	UNION	<derived2>	ALL	NULL	NULL	NULL	NULL	5	100.00	Using filesort
+2	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+NULL	UNION RESULT	<union1,3>	ALL	NULL	NULL	NULL	NULL	NULL	NULL	
+Warnings:
+Note	1003	(values (5),(7),(1),(3),(4) limit 1,2) union all (/* select#3 */ select "tvc_0"."5" AS "5" from (values (5),(7),(1),(3),(4)) "tvc_0" order by 1 limit 2)
+(values (5), (7), (1), (3), (4) limit 2 offset 3) union all select 3 order by 1;
+5
+3
+3
+4
+explain extended (values (5), (7), (1), (3), (4) limit 2 offset 3) union all select 3 order by 1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+2	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+NULL	UNION RESULT	<union1,2>	ALL	NULL	NULL	NULL	NULL	NULL	NULL	Using filesort
+Warnings:
+Note	1003	(values (5),(7),(1),(3),(4) limit 3,2) union all /* select#2 */ select 3 AS "3" order by 1
+(values (5), (7), (1), (3), (4) order by 1 limit 3 offset 1) union all select 3 order by 1;
+5
+3
+3
+4
+5
+explain extended (values (5), (7), (1), (3), (4) order by 1 limit 3 offset 1) union all select 3 order by 1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SUBQUERY	<derived3>	ALL	NULL	NULL	NULL	NULL	5	100.00	Using filesort
+3	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+2	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+NULL	UNION RESULT	<union1,2>	ALL	NULL	NULL	NULL	NULL	NULL	NULL	Using filesort
+Warnings:
+Note	1003	(/* select#1 */ select "tvc_0"."5" AS "5" from (values (5),(7),(1),(3),(4)) "tvc_0" order by 1 limit 1,3) union all /* select#2 */ select 3 AS "3" order by 1
+(values (5), (7), (1), (3), (4) order by 1 limit 3 offset 1) union all select 3
+order by 1 limit 2 offset 1;
+5
+3
+4
+explain extended (values (5), (7), (1), (3), (4) order by 1 limit 3 offset 1) union all select 3
+order by 1 limit 2 offset 1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SUBQUERY	<derived3>	ALL	NULL	NULL	NULL	NULL	5	100.00	Using filesort
+3	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+2	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+NULL	UNION RESULT	<union1,2>	ALL	NULL	NULL	NULL	NULL	NULL	NULL	Using filesort
+Warnings:
+Note	1003	(/* select#1 */ select "tvc_0"."5" AS "5" from (values (5),(7),(1),(3),(4)) "tvc_0" order by 1 limit 1,3) union all /* select#2 */ select 3 AS "3" order by 1 limit 1,2
+create view v1 as values (5), (7), (1), (3), (4) order by 1 limit 2;
+show create view v1;
+View	Create View	character_set_client	collation_connection
+v1	CREATE VIEW "v1" AS values (5),(7),(1),(3),(4) order by 1 limit 2	latin1	latin1_swedish_ci
+select * from v1;
+5
+1
+3
+drop view v1;
+create view v1 as
+( values (5), (7), (1), (3), (4) limit 2 offset 1 )
+union
+( values (5), (7), (1), (3), (4) order by 1 limit 2 );
+show create view v1;
+View	Create View	character_set_client	collation_connection
+v1	CREATE VIEW "v1" AS (values (5),(7),(1),(3),(4) limit 1,2) union (select "tvc_0"."5" AS "5" from (values (5),(7),(1),(3),(4)) "tvc_0" order by 1 limit 2)	latin1	latin1_swedish_ci
+select * from v1;
+5
+7
+1
+3
+drop view v1;
diff --git a/mysql-test/suite/compat/oracle/t/table_value_constr.test b/mysql-test/suite/compat/oracle/t/table_value_constr.test
index 37d2521..808ce58 100644
--- a/mysql-test/suite/compat/oracle/t/table_value_constr.test
+++ b/mysql-test/suite/compat/oracle/t/table_value_constr.test
@@ -1125,3 +1125,137 @@ DROP VIEW v1;
 
 VALUES(1 + 1,2,'abc');
 SELECT * FROM (VALUES(1 + 1,2,'abc')) t;
+
+--echo #
+--echo # MDEV-17894: tvc with ORDER BY ... LIMIT
+--echo #
+
+let $q=
+values (5), (7), (1), (3), (4) limit 2;
+eval $q;
+eval explain extended $q;
+
+let $q=
+values (5), (7), (1), (3), (4) limit 2 offset 1;
+eval $q;
+eval explain extended $q;
+
+let $q=
+values (5), (7), (1), (3), (4) order by 1 limit 2;
+eval $q;
+eval explain extended $q;
+
+let $q=
+values (5), (7), (1), (3), (4) order by 1 limit 2 offset 1;
+eval $q;
+eval explain extended $q;
+
+let $q=
+values (5), (7), (1), (3), (4) order by 1;
+eval $q;
+eval explain extended $q;
+
+let $q=
+select 2 union (values (5), (7), (1), (3), (4) limit 2);
+eval $q;
+eval explain extended $q;
+
+let $q=
+select 2 union (values (5), (7), (1), (3), (4) limit 2 offset 1);
+eval $q;
+eval explain extended $q;
+
+let $q=
+select 2 union (values (5), (7), (1), (3), (4) order by 1 limit 2);
+eval $q;
+eval explain extended $q;
+
+let $q=
+select 2 union (values (5), (7), (1), (3), (4) order by 1 limit 2 offset 1);
+eval $q;
+eval explain extended $q;
+
+
+let $q=
+(values (5), (7), (1), (3), (4) limit 2) union select 2;
+eval $q;
+eval explain extended $q;
+
+let $q=
+(values (5), (7), (1), (3), (4) limit 2 offset 1) union select 2;
+eval $q;
+eval explain extended $q;
+
+let $q=
+(values (5), (7), (1), (3), (4) order by 1 limit 2) union select 2;
+eval $q;
+eval explain extended $q;
+
+let $q=
+(values (5), (7), (1), (3), (4) order by 1 limit 2 offset 1) union select 2;
+eval $q;
+eval explain extended $q;
+
+
+let $q=
+select 3 union all (values (5), (7), (1), (3), (4) limit 2 offset 3);
+eval $q;
+eval explain extended $q;
+
+let $q=
+(values (5), (7), (1), (3), (4) limit 2 offset 3) union all select 3;
+eval $q;
+eval explain extended $q;
+
+let $q=
+select 3 union all (values (5), (7), (1), (3), (4) order by 1 limit 2);
+eval $q;
+eval explain extended $q;
+
+let $q=
+(values (5), (7), (1), (3), (4) order by 1 limit 2) union all select 3;
+eval $q;
+eval explain extended $q;
+
+let $q=
+( values (5), (7), (1), (3), (4) limit 2 offset 1 )
+  union
+( values (5), (7), (1), (3), (4) order by 1 limit 2 );
+eval $q;
+eval explain extended $q;
+
+let $q=
+( values (5), (7), (1), (3), (4) limit 2 offset 1 )
+  union all
+( values (5), (7), (1), (3), (4) order by 1 limit 2 );
+eval $q;
+eval explain extended $q;
+
+let $q=
+(values (5), (7), (1), (3), (4) limit 2 offset 3) union all select 3 order by 1;
+eval $q;
+eval explain extended $q;
+
+let $q=
+(values (5), (7), (1), (3), (4) order by 1 limit 3 offset 1) union all select 3 order by 1;
+eval $q;
+eval explain extended $q;
+
+let $q=
+(values (5), (7), (1), (3), (4) order by 1 limit 3 offset 1) union all select 3
+ order by 1 limit 2 offset 1;
+eval $q;
+eval explain extended $q;
+
+create view v1 as values (5), (7), (1), (3), (4) order by 1 limit 2;
+show create view v1;
+select * from v1;
+drop view v1;
+
+create view v1 as
+( values (5), (7), (1), (3), (4) limit 2 offset 1 )
+  union
+( values (5), (7), (1), (3), (4) order by 1 limit 2 );
+show create view v1;
+select * from v1;
+drop view v1;
diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc
index 207aa9a..475e74d 100644
--- a/sql/item_subselect.cc
+++ b/sql/item_subselect.cc
@@ -269,7 +269,7 @@ bool Item_subselect::fix_fields(THD *thd_param, Item **ref)
   {
     if (sl->tvc)
     {
-      wrap_tvc_in_derived_table(thd, sl);
+      wrap_tvc_into_select(thd, sl);
     }
   }
   
diff --git a/sql/item_subselect.h b/sql/item_subselect.h
index 363dbba..5a9968b 100644
--- a/sql/item_subselect.h
+++ b/sql/item_subselect.h
@@ -267,7 +267,7 @@ class Item_subselect :public Item_result_field,
   Item* build_clone(THD *thd) { return 0; }
   Item* get_copy(THD *thd) { return 0; }
 
-  bool wrap_tvc_in_derived_table(THD *thd, st_select_lex *tvc_sl);
+  bool wrap_tvc_into_select(THD *thd, st_select_lex *tvc_sl);
 
   friend class select_result_interceptor;
   friend class Item_in_optimizer;
diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc
index d6cc62c..638530d 100644
--- a/sql/sql_lex.cc
+++ b/sql/sql_lex.cc
@@ -2275,6 +2275,7 @@ void st_select_lex_unit::init_query()
   with_element= 0;
   columns_are_renamed= false;
   intersect_mark= NULL;
+  with_wrapped_tvc= false;
 }
 
 void st_select_lex::init_query()
@@ -3411,6 +3412,19 @@ bool st_select_lex_unit::union_needs_tmp_table()
 {
   if (with_element && with_element->is_recursive)
     return true;
+  if (!with_wrapped_tvc)
+  {
+    for (st_select_lex *sl= first_select(); sl; sl=sl->next_select())
+    {
+      if (sl->tvc && sl->tvc->to_be_wrapped_as_with_tail())
+      {
+        with_wrapped_tvc= true;
+        break;
+      }
+    }
+  }
+  if (with_wrapped_tvc)
+    return true;
   return union_distinct != NULL ||
     global_parameters()->order_list.elements != 0 ||
     thd->lex->sql_command == SQLCOM_INSERT_SELECT ||
@@ -8236,6 +8250,8 @@ bool LEX::tvc_finalize()
                                   current_select->options))))
     return true;
   many_values.empty();
+  if (!current_select->master_unit()->fake_select_lex)
+    current_select->master_unit()->add_fake_select_lex(thd);
   return false;
 }
 
diff --git a/sql/sql_lex.h b/sql/sql_lex.h
index 926b09e..4123275 100644
--- a/sql/sql_lex.h
+++ b/sql/sql_lex.h
@@ -806,6 +806,12 @@ class st_select_lex_unit: public st_select_lex_node {
   */
   Item_int *intersect_mark;
   /**
+     TRUE if the unit contained TVC at the top level that has been wrapped
+     into SELECT:
+     VALUES (v1) ... (vn) => SELECT * FROM (VALUES (v1) ... (vn)) as tvc
+  */
+  bool with_wrapped_tvc;
+  /**
     Pointer to 'last' select, or pointer to select where we stored
     global parameters for union.
 
diff --git a/sql/sql_tvc.cc b/sql/sql_tvc.cc
index a5085fd..c400264 100644
--- a/sql/sql_tvc.cc
+++ b/sql/sql_tvc.cc
@@ -344,6 +344,7 @@ bool table_value_constr::exec(SELECT_LEX *sl)
   DBUG_ENTER("table_value_constr::exec");
   List_iterator_fast<List_item> li(lists_of_values);
   List_item *elem;
+  ha_rows send_records= 0;
   
   if (select_options & SELECT_DESCRIBE)
     DBUG_RETURN(false);
@@ -357,7 +358,13 @@ bool table_value_constr::exec(SELECT_LEX *sl)
 
   while ((elem= li++))
   {
-    result->send_data(*elem);
+    if (send_records >= sl->master_unit()->select_limit_cnt)
+      break;
+    int rc= result->send_data(*elem);
+    if (!rc)
+      send_records++;
+    else if (rc > 0)
+      DBUG_RETURN(true);
   }
 
   if (result->send_eof())
@@ -436,6 +443,12 @@ void table_value_constr::print(THD *thd, String *str,
 
     print_list_item(str, list, query_type);
   }
+  if (select_lex->order_list.elements)
+  {
+    str->append(STRING_WITH_LEN(" order by "));
+    select_lex->print_order(str, select_lex->order_list.first, query_type);
+  }
+  select_lex->print_limit(thd, str, query_type);
 }
 
 
@@ -533,7 +546,8 @@ static bool create_tvc_name(THD *thd, st_select_lex *parent_select,
   char buff[6];
 
   alias->length= my_snprintf(buff, sizeof(buff),
-                            "tvc_%u", parent_select->curr_tvc_name);
+                            "tvc_%u",
+			     parent_select ? parent_select->curr_tvc_name : 0);
   alias->str= thd->strmake(buff, alias->length);
   if (!alias->str)
     return true;
@@ -542,19 +556,57 @@ static bool create_tvc_name(THD *thd, st_select_lex *parent_select,
 }
 
 
-bool Item_subselect::wrap_tvc_in_derived_table(THD *thd,
-					       st_select_lex *tvc_sl)
+/**
+  @brief
+  Check whether TVC used in unit is to be wrapped into select
+
+  @details
+    TVC used in unit that contains more than one members is to be wrapped
+    into select if it is tailed with ORDER BY ... LIMIT n [OFFSET m]
+
+  @retval
+    true     if TVC is to be wrapped
+    false    otherwise
+*/
+
+bool table_value_constr::to_be_wrapped_as_with_tail()
+{
+  return select_lex->master_unit()->first_select()->next_select() &&
+         select_lex->order_list.elements && select_lex->explicit_limit;
+}
+
+
+/**
+  @brief
+  Wrap table value constructor into a select
+
+  @param thd               The context handler
+  @param tvc_sl            The TVC to wrap
+  @parent_select           The parent select if tvc_sl used in a subquery
+
+  @details
+    The function wraps the TVC tvc_sl into a select:
+    the function transforms the TVC of the form VALUES (v1), ... (vn) into
+    the select of the form
+    SELECT * FROM (VALUES (v1), ... (vn)) tvc_x
+
+  @retval pointer to the result of of the transformation if successful
+          NULL - otherwise
+*/
+
+static
+st_select_lex *wrap_tvc(THD *thd, st_select_lex *tvc_sl,
+                        st_select_lex *parent_select)
 {
   LEX *lex= thd->lex;
-  /* SELECT_LEX object where the transformation is performed */
-  SELECT_LEX *parent_select= lex->current_select;
+  select_result *save_result= thd->lex->result;
   uint8 save_derived_tables= lex->derived_tables;
+  thd->lex->result= NULL;
 
   Query_arena backup;
   Query_arena *arena= thd->activate_stmt_arena_if_needed(&backup);
-
   /*
-    Create SELECT_LEX of the subquery SQ used in the result of transformation
+    Create SELECT_LEX of the select used in the result of transformation
   */
   lex->current_select= tvc_sl;
   if (mysql_new_select(lex, 0, NULL))
@@ -562,15 +614,15 @@ bool Item_subselect::wrap_tvc_in_derived_table(THD *thd,
   mysql_init_select(lex);
   /* Create item list as '*' for the subquery SQ */
   Item *item;
-  SELECT_LEX *sq_select; // select for IN subquery;
-  sq_select= lex->current_select;
-  sq_select->linkage= tvc_sl->linkage;
-  sq_select->parsing_place= SELECT_LIST;
-  item= new (thd->mem_root) Item_field(thd, &sq_select->context,
+  SELECT_LEX *wrapper_sl;
+  wrapper_sl= lex->current_select;
+  wrapper_sl->linkage= tvc_sl->linkage;
+  wrapper_sl->parsing_place= SELECT_LIST;
+  item= new (thd->mem_root) Item_field(thd, &wrapper_sl->context,
                                        NULL, NULL, &star_clex_str);
   if (item == NULL || add_item_to_list(thd, item))
     goto err;
-  (sq_select->with_wild)++;
+  (wrapper_sl->with_wild)++;
   
   /* Exclude SELECT with TVC */
   tvc_sl->exclude();
@@ -585,11 +637,11 @@ bool Item_subselect::wrap_tvc_in_derived_table(THD *thd,
   derived_unit= tvc_select->master_unit();
   tvc_select->linkage= DERIVED_TABLE_TYPE;
 
-  lex->current_select= sq_select;
+  lex->current_select= wrapper_sl;
 
   /*
     Create the name of the wrapping derived table and
-    add it to the FROM list of the subquery SQ
+    add it to the FROM list of the wrapper
    */
   Table_ident *ti;
   LEX_CSTRING alias;
@@ -598,35 +650,120 @@ bool Item_subselect::wrap_tvc_in_derived_table(THD *thd,
       create_tvc_name(thd, parent_select, &alias))
     goto err;
   if (!(derived_tab=
-          sq_select->add_table_to_list(thd,
-				       ti, &alias, 0,
-                                       TL_READ, MDL_SHARED_READ)))
+          wrapper_sl->add_table_to_list(thd,
+				        ti, &alias, 0,
+                                        TL_READ, MDL_SHARED_READ)))
     goto err;
-  sq_select->add_joined_table(derived_tab);
-  sq_select->add_where_field(derived_unit->first_select());
-  sq_select->context.table_list= sq_select->table_list.first;
-  sq_select->context.first_name_resolution_table= sq_select->table_list.first;
-  sq_select->table_list.first->derived_type= DTYPE_TABLE | DTYPE_MATERIALIZE;
+  wrapper_sl->add_joined_table(derived_tab);
+  wrapper_sl->add_where_field(derived_unit->first_select());
+  wrapper_sl->context.table_list= wrapper_sl->table_list.first;
+  wrapper_sl->context.first_name_resolution_table= wrapper_sl->table_list.first;
+  wrapper_sl->table_list.first->derived_type= DTYPE_TABLE | DTYPE_MATERIALIZE;
   lex->derived_tables|= DERIVED_SUBQUERY;
 
-  sq_select->where= 0;
-  sq_select->set_braces(false);
+  wrapper_sl->where= 0;
+  wrapper_sl->set_braces(false);
   derived_unit->set_with_clause(0);
 
-  if (engine->engine_type() == subselect_engine::SINGLE_SELECT_ENGINE)
-    ((subselect_single_select_engine *) engine)->change_select(sq_select);
-
   if (arena)
     thd->restore_active_arena(arena, &backup);
-  lex->current_select= sq_select;
-  return false;
+  thd->lex->result= save_result;
+  return wrapper_sl;
 
 err:
   if (arena)
     thd->restore_active_arena(arena, &backup);
+  thd->lex->result= save_result;
   lex->derived_tables= save_derived_tables;
-  lex->current_select= parent_select;
-  return true;
+  return 0;
+}
+
+
+/**
+  @brief
+  Wrap TVC with ORDER BY ... LIMIT tail into a select
+
+  @param thd               The context handler
+  @param tvc_sl            The TVC to wrap
+
+  @details
+    The function wraps the TVC tvc_sl into a select:
+    the function transforms the TVC with tail of the form
+    VALUES (v1), ... (vn) ORDER BY ... LIMIT n [OFFSET m]
+    into the select with the same tail of the form
+    SELECT * FROM (VALUES (v1), ... (vn)) tvc_x
+      ORDER BY ... LIMIT n [OFFSET m]
+
+  @retval pointer to the result of of the transformation if successful
+          NULL - otherwise
+*/
+
+st_select_lex *wrap_tvc_with_tail(THD *thd, st_select_lex *tvc_sl)
+{
+  st_select_lex *wrapper_sl= wrap_tvc(thd, tvc_sl, NULL);
+  if (!wrapper_sl)
+    return NULL;
+
+  wrapper_sl->order_list= tvc_sl->order_list;
+  wrapper_sl->select_limit= tvc_sl->select_limit;
+  wrapper_sl->offset_limit= tvc_sl->offset_limit;
+  wrapper_sl->braces= tvc_sl->braces;
+  wrapper_sl->explicit_limit= tvc_sl->explicit_limit;
+  tvc_sl->order_list.empty();
+  tvc_sl->select_limit= NULL;
+  tvc_sl->offset_limit= NULL;
+  tvc_sl->braces= 0;
+  tvc_sl->explicit_limit= false;
+  if (tvc_sl->select_number == 1)
+  {
+    tvc_sl->select_number= wrapper_sl->select_number;
+    wrapper_sl->select_number= 1;
+  }
+  if (tvc_sl->master_unit()->union_distinct == tvc_sl)
+  {
+    wrapper_sl->master_unit()->union_distinct= wrapper_sl;
+  }
+  thd->lex->current_select= wrapper_sl;
+  return wrapper_sl;
+}
+
+
+/**
+  @brief
+  Wrap TVC in a subselect into a select
+
+  @param thd               The context handler
+  @param tvc_sl            The TVC to wrap
+
+  @details
+    The function wraps the TVC tvc_sl used in a subselect into a select
+    the function transforms the TVC of the form VALUES (v1), ... (vn)
+    into the select the form
+    SELECT * FROM (VALUES (v1), ... (vn)) tvc_x
+    and replaces the subselect with the result of the transformation.
+
+  @retval false if successfull
+          true  otherwise
+*/
+
+bool Item_subselect::wrap_tvc_into_select(THD *thd, st_select_lex *tvc_sl)
+{
+  LEX *lex= thd->lex;
+  /* SELECT_LEX object where the transformation is performed */
+  SELECT_LEX *parent_select= lex->current_select;
+  SELECT_LEX *wrapper_sl= wrap_tvc(thd, tvc_sl, parent_select);
+  if (wrapper_sl)
+  {
+    if (engine->engine_type() == subselect_engine::SINGLE_SELECT_ENGINE)
+      ((subselect_single_select_engine *) engine)->change_select(wrapper_sl);
+    lex->current_select= wrapper_sl;
+    return false;
+  }
+  else
+  {
+    lex->current_select= parent_select;
+    return true;
+  }
 }
 
 
diff --git a/sql/sql_tvc.h b/sql/sql_tvc.h
index 128cc88..594a77a 100644
--- a/sql/sql_tvc.h
+++ b/sql/sql_tvc.h
@@ -57,6 +57,8 @@ class table_value_constr : public Sql_alloc
 	       select_result *tmp_result,
 	       st_select_lex_unit *unit_arg);
 
+  bool to_be_wrapped_as_with_tail();
+
   int save_explain_data_intern(THD *thd_arg,
 			       Explain_query *output);
   bool optimize(THD *thd_arg);
@@ -64,4 +66,7 @@ class table_value_constr : public Sql_alloc
 
   void print(THD *thd_arg, String *str, enum_query_type query_type);
 };
+
+st_select_lex *wrap_tvc_with_tail(THD *thd, st_select_lex *tvc_sl);
+
 #endif /* SQL_TVC_INCLUDED */
diff --git a/sql/sql_union.cc b/sql/sql_union.cc
index 7b0e796..bcca27c 100644
--- a/sql/sql_union.cc
+++ b/sql/sql_union.cc
@@ -831,7 +831,8 @@ bool st_select_lex_unit::prepare(TABLE_LIST *derived_arg,
   bool is_union_select;
   bool have_except= FALSE, have_intersect= FALSE;
   bool instantiate_tmp_table= false;
-  bool single_tvc= !first_sl->next_select() && first_sl->tvc;
+  bool single_tvc= !first_sl->next_select() && first_sl->tvc &&
+                   !fake_select_lex;
   DBUG_ENTER("st_select_lex_unit::prepare");
   DBUG_ASSERT(thd == current_thd);
 
@@ -986,7 +987,21 @@ bool st_select_lex_unit::prepare(TABLE_LIST *derived_arg,
   {
     if (sl->tvc)
     {
-      if (sl->tvc->prepare(thd, sl, tmp_result, this))
+      if (sl->tvc->to_be_wrapped_as_with_tail())
+      {
+        st_select_lex *wrapper_sl= wrap_tvc_with_tail(thd, sl);
+        if (!wrapper_sl)
+          goto err;
+
+        if (sl == first_sl)
+          first_sl= wrapper_sl;
+        sl= wrapper_sl;
+
+        if (prepare_join(thd, sl, tmp_result, additional_options,
+                         is_union_select))
+	  goto err;
+      }
+      else if (sl->tvc->prepare(thd, sl, tmp_result, this))
 	goto err;
     }
     else if (prepare_join(thd, sl, tmp_result, additional_options,
diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy
index 98ead67..b2f597c 100644
--- a/sql/sql_yacc.yy
+++ b/sql/sql_yacc.yy
@@ -9192,7 +9192,7 @@ select_paren:
           {
             Lex->current_select->set_braces(true);
           }
-          table_value_constructor
+          table_value_constructor select_part3
           {
             DBUG_ASSERT(Lex->current_select->braces);
           }
@@ -9212,6 +9212,12 @@ select_paren:
         | '(' select_paren ')'
         ;
 
+select_parent_union_query_term_proper:
+          SELECT_SYM select_options_and_item_list select_part3_union_query_term
+          opt_select_lock_type
+        | table_value_constructor select_part3_union_query_term
+        ;
+
 select_paren_union_query_term:
           {
             /*
@@ -9220,14 +9226,19 @@ select_paren_union_query_term:
             */
             Lex->current_select->set_braces(true);
           }
-          SELECT_SYM select_options_and_item_list select_part3_union_query_term
-          opt_select_lock_type
+          select_parent_union_query_term_proper
           {
             DBUG_ASSERT(Lex->current_select->braces);
           }
         | '(' select_paren_union_query_term ')'
         ;
 
+select_parent_view_proper:
+          SELECT_SYM select_options_and_item_list select_part3_view
+          opt_select_lock_type
+        | table_value_constructor select_part3_view
+        ;
+
 select_paren_view:
           {
             /*
@@ -9236,8 +9247,7 @@ select_paren_view:
             */
             Lex->current_select->set_braces(true);
           }
-          SELECT_SYM select_options_and_item_list select_part3_view
-          opt_select_lock_type
+          select_parent_view_proper
           {
             DBUG_ASSERT(Lex->current_select->braces);
           }
diff --git a/sql/sql_yacc_ora.yy b/sql/sql_yacc_ora.yy
index f7aa1c9..233882e 100644
--- a/sql/sql_yacc_ora.yy
+++ b/sql/sql_yacc_ora.yy
@@ -9129,7 +9129,7 @@ select_paren:
           {
             Lex->current_select->set_braces(true);
           }
-          table_value_constructor
+          table_value_constructor select_part3
           {
             DBUG_ASSERT(Lex->current_select->braces);
           }
@@ -9149,6 +9149,12 @@ select_paren:
         | '(' select_paren ')'
         ;
 
+select_parent_union_query_term_proper:
+          SELECT_SYM select_options_and_item_list select_part3_union_query_term
+          opt_select_lock_type
+        | table_value_constructor select_part3_union_query_term
+        ;
+
 select_paren_union_query_term:
           {
             /*
@@ -9157,14 +9163,19 @@ select_paren_union_query_term:
             */
             Lex->current_select->set_braces(true);
           }
-          SELECT_SYM select_options_and_item_list select_part3_union_query_term
-          opt_select_lock_type
+          select_parent_union_query_term_proper
           {
             DBUG_ASSERT(Lex->current_select->braces);
           }
         | '(' select_paren_union_query_term ')'
         ;
 
+select_parent_view_proper:
+          SELECT_SYM select_options_and_item_list select_part3_view
+          opt_select_lock_type
+        | table_value_constructor select_part3_view
+        ;
+
 select_paren_view:
           {
             /*
@@ -9173,8 +9184,7 @@ select_paren_view:
             */
             Lex->current_select->set_braces(true);
           }
-          SELECT_SYM select_options_and_item_list select_part3_view
-          opt_select_lock_type
+          select_parent_view_proper
           {
             DBUG_ASSERT(Lex->current_select->braces);
           }

    

[Commits] 45e2f89: MDEV-17894 Assertion `(thd->lex)->current_select' failed in MYSQLparse(),

IgorBabaev