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.

2 files changed, 590 insertions, 0 deletions

diff --git a/mysql-test/main/table_value_constr.result b/mysql-test/main/table_value_constr.result
index 1d485af4a4d..318d0a76663 100644
--- a/mysql-test/main/table_value_constr.result
+++ b/mysql-test/main/table_value_constr.result
@@ -2189,3 +2189,400 @@ 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
+values (5,90), (7,20), (1,70), (3,50), (4,10) order by 2;
+5	90
+4	10
+7	20
+3	50
+1	70
+5	90
+explain extended values (5,90), (7,20), (1,70), (3,50), (4,10) order by 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,90),(7,20),(1,70),(3,50),(4,10) order by 2
+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
+values (5,90), (7,20), (1,70), (3,50), (4,10) order by 3;
+ERROR 42S22: Unknown column '3' in 'order clause'
+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;
+prepare stmt from "
+( values (5), (7), (1), (3), (4) limit 2 offset 1 )
+  union
+( values (5), (7), (1), (3), (4) order by 1 limit 2 );
+";
+execute stmt;
+5
+7
+1
+3
+execute stmt;
+5
+7
+1
+3
+deallocate prepare stmt;
+prepare stmt from "
+values (5,90), (7,20), (1,70), (3,50), (4,10) order by 3;
+";
+ERROR 42S22: Unknown column '3' in 'order clause'
+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 (values (5),(7),(1),(3),(4) order by 1 limit 2)	latin1	latin1_swedish_ci
+select * from v1;
+5
+7
+1
+3
+drop view v1;
+create view v1 as values (5,90), (7,20), (1,70), (3,50), (4,10) order by 3;
+ERROR 42S22: Unknown column '3' in 'order clause'
+create view v1 as
+( values (5), (7), (1), (3), (4) limit 2 offset 1 )
+union
+( values (5), (7), (1), (3), (4) order by 2 limit 2 );
+ERROR 42S22: Unknown column '2' in 'order clause'
diff --git a/mysql-test/main/table_value_constr.test b/mysql-test/main/table_value_constr.test
index 0dd0a7a04b0..6b89816cc0c 100644
--- a/mysql-test/main/table_value_constr.test
+++ b/mysql-test/main/table_value_constr.test
@@ -1123,3 +1123,196 @@ 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=
+values (5,90), (7,20), (1,70), (3,50), (4,10) order by 2;
+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;
+
+--error ER_BAD_FIELD_ERROR
+values (5,90), (7,20), (1,70), (3,50), (4,10) order by 3;
+
+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;
+
+prepare stmt from "
+( values (5), (7), (1), (3), (4) limit 2 offset 1 )
+  union
+( values (5), (7), (1), (3), (4) order by 1 limit 2 );
+";
+execute stmt;
+execute stmt;
+deallocate prepare stmt;
+
+--error ER_BAD_FIELD_ERROR
+prepare stmt from "
+values (5,90), (7,20), (1,70), (3,50), (4,10) order by 3;
+";
+
+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;
+
+--error ER_BAD_FIELD_ERROR
+create view v1 as values (5,90), (7,20), (1,70), (3,50), (4,10) order by 3;
+
+--error ER_BAD_FIELD_ERROR
+create view v1 as
+( values (5), (7), (1), (3), (4) limit 2 offset 1 )
+  union
+( values (5), (7), (1), (3), (4) order by 2 limit 2 );