summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--mysql-test/main/table_value_constr.result397
-rw-r--r--mysql-test/main/table_value_constr.test193
-rw-r--r--mysql-test/suite/compat/oracle/r/table_value_constr.result321
-rw-r--r--mysql-test/suite/compat/oracle/t/table_value_constr.test151
-rw-r--r--sql/item_subselect.cc2
-rw-r--r--sql/item_subselect.h2
-rw-r--r--sql/sql_lex.cc16
-rw-r--r--sql/sql_lex.h6
-rw-r--r--sql/sql_tvc.cc232
-rw-r--r--sql/sql_tvc.h5
-rw-r--r--sql/sql_union.cc21
-rw-r--r--sql/sql_yacc.yy20
-rw-r--r--sql/sql_yacc_ora.yy20
13 files changed, 1339 insertions, 47 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 );
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 );
diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc
index 99bba5a98d5..0e9b4a16c09 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 e0b09b9484b..a4bce34b315 100644
--- a/sql/item_subselect.h
+++ b/sql/item_subselect.h
@@ -267,7 +267,7 @@ public:
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 6ce778d03cf..7b4eb13a5fc 100644
--- a/sql/sql_lex.cc
+++ b/sql/sql_lex.cc
@@ -2292,6 +2292,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()
@@ -3428,6 +3429,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 ||
@@ -8238,6 +8252,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 4eaec7d062b..24383b1949c 100644
--- a/sql/sql_lex.h
+++ b/sql/sql_lex.h
@@ -801,6 +801,12 @@ public:
*/
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 a05673f2a6c..c9b55fe210e 100644
--- a/sql/sql_tvc.cc
+++ b/sql/sql_tvc.cc
@@ -263,6 +263,35 @@ bool table_value_constr::prepare(THD *thd, SELECT_LEX *sl,
if (result && result->prepare(sl->item_list, unit_arg))
DBUG_RETURN(true);
+ /*
+ setup_order() for a TVC is not called when the following is true
+ (thd->lex->context_analysis_only & CONTEXT_ANALYSIS_ONLY_VIEW)
+ */
+
+ thd->where="order clause";
+ ORDER *order= sl->order_list.first;
+ for (; order; order=order->next)
+ {
+ Item *order_item= *order->item;
+ if (order_item->type() == Item::INT_ITEM && order_item->basic_const_item())
+ {
+ uint count= 0;
+ if (order->counter_used)
+ count= order->counter; // counter was once resolved
+ else
+ count= (uint) order_item->val_int();
+ if (!count || count > first_elem->elements)
+ {
+ my_error(ER_BAD_FIELD_ERROR, MYF(0),
+ order_item->full_name(), thd->where);
+ DBUG_RETURN(true);
+ }
+ order->in_field_list= 1;
+ order->counter= count;
+ order->counter_used= 1;
+ }
+ }
+
select_lex->in_tvc= false;
DBUG_RETURN(false);
}
@@ -344,6 +373,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 +387,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 +472,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 +575,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 +585,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 +643,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 +666,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 +679,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 128cc883dd8..594a77af65c 100644
--- a/sql/sql_tvc.h
+++ b/sql/sql_tvc.h
@@ -57,6 +57,8 @@ public:
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 @@ public:
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 3fb5552c77a..c591e49d58a 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,23 @@ 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() &&
+ !(thd->lex->context_analysis_only & CONTEXT_ANALYSIS_ONLY_VIEW))
+
+ {
+ 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 b9f6a64b378..8910d7418fd 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 61f2426427e..1c83462e6ac 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);
}
View Lorry Controller Status