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, 472 insertions, 0 deletions

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 18fce086f6e..f0c7c4eebe1 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,324 @@ 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
+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'
+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 (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/suite/compat/oracle/t/table_value_constr.test b/mysql-test/suite/compat/oracle/t/table_value_constr.test
index 37d25218664..4e0dcc05514 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,154 @@ 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=
+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;
+
+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 );