MDEV-10080 Derived tables allow double LIMIT clause

1. Moving the "| get_select_lex_derived select_derived_init" part of
select_derived into a separate new rule derived_query_specification.
2. Using derived_query_specification directly in select_derived_union
rather than in select_derived.
3. Moving the sequence "opt_order_clause opt_limit_clause opt_select_lock_type"
from select_derived2 to select_derived_union,
after derived_query_specification.

Effectively, the parser now does not go through the sequence
"opt_order_clause opt_limit_clause ... opt_union_order_or_limit" any more.

This fixes the problem with double LIMIT clause and removes 2 shift/reduce
conflicts.

3 files changed, 35 insertions, 9 deletions

diff --git a/mysql-test/r/parser.result b/mysql-test/r/parser.result
index 1a39bca40db..5d1e89e6116 100644
--- a/mysql-test/r/parser.result
+++ b/mysql-test/r/parser.result
@@ -840,7 +840,7 @@ ERROR 42000: You have an error in your SQL syntax; check the manual that corresp
 SELECT (SELECT 1 FROM t1 LIMIT 1 ORDER BY 1 UNION SELECT 1 FROM t1);
 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'ORDER BY 1 UNION SELECT 1 FROM t1)' at line 1
 SELECT 1 FROM (SELECT 1 FROM t1 LIMIT 1 ORDER BY 1 UNION SELECT 1 FROM t1) a;
-ERROR HY000: Incorrect usage of UNION and ORDER BY
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'ORDER BY 1 UNION SELECT 1 FROM t1) a' at line 1
 SELECT 1 FROM t1 ORDER BY 1 UNION SELECT 1 FROM t1 ORDER BY 1;
 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'UNION SELECT 1 FROM t1 ORDER BY 1' at line 1
 SELECT (SELECT 1 FROM t1 ORDER BY 1 UNION SELECT 1 FROM t1 ORDER BY 1);
@@ -878,3 +878,11 @@ a
 2
 1
 DROP TABLE t1;
+#
+# MDEV-10080 Derived tables allow double LIMIT clause
+#
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (1),(2),(3);
+SELECT * FROM  (SELECT * FROM t1 LIMIT 1 LIMIT 2) t1;
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'LIMIT 2) t1' at line 1
+DROP TABLE t1;
diff --git a/mysql-test/t/parser.test b/mysql-test/t/parser.test
index a143a61f995..ae456574c39 100644
--- a/mysql-test/t/parser.test
+++ b/mysql-test/t/parser.test
@@ -975,7 +975,7 @@ let $q=SELECT 1 FROM t1 LIMIT 1 ORDER BY 1 UNION SELECT 1 FROM t1;
 eval $q;
 --error ER_PARSE_ERROR
 eval SELECT ($q);
---error ER_WRONG_USAGE
+--error ER_PARSE_ERROR
 eval SELECT 1 FROM ($q) a;
 
 let $q=SELECT 1 FROM t1 ORDER BY 1 UNION SELECT 1 FROM t1 ORDER BY 1;
@@ -1021,3 +1021,12 @@ SELECT *
 FROM ( (SELECT a FROM t1 ORDER BY a) UNION (SELECT 1 as b ORDER BY b ) ) AS a1
 WHERE a1.a = 1 OR a1.a = 2;
 DROP TABLE t1;
+
+--echo #
+--echo # MDEV-10080 Derived tables allow double LIMIT clause
+--echo #
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (1),(2),(3);
+--error ER_PARSE_ERROR
+SELECT * FROM  (SELECT * FROM t1 LIMIT 1 LIMIT 2) t1;
+DROP TABLE t1;
diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy
index 2214c6e7f99..c6815058f2a 100644
--- a/sql/sql_yacc.yy
+++ b/sql/sql_yacc.yy
@@ -1037,10 +1037,10 @@ bool my_yyoverflow(short **a, YYSTYPE **b, ulong *yystacksize);
 %parse-param { THD *thd }
 %lex-param { THD *thd }
 /*
-  Currently there are 104 shift/reduce conflicts.
+  Currently there are 102 shift/reduce conflicts.
   We should not introduce new conflicts any more.
 */
-%expect 104
+%expect 102
 
 /*
    Comments for TOKENS.
@@ -11121,6 +11121,11 @@ select_derived_union:
               MYSQL_YYABORT;
             }
           }
+        | derived_query_specification
+          opt_order_clause
+          opt_limit_clause
+          opt_select_lock_type
+          { $$= NULL; }
         | select_derived_union union_head_non_top query_term
           {
             /*
@@ -11181,7 +11186,15 @@ select_derived:
               MYSQL_YYABORT;
             }
           }
-        | get_select_lex_derived select_derived_init
+        ;
+
+/*
+  Similar to query_specification, but for derived tables.
+  Example: the inner parenthesized SELECT in this query:
+    SELECT * FROM (SELECT * FROM t1);
+*/
+derived_query_specification:
+          get_select_lex_derived select_derived_init
           {
             // Now we have the same st_select_lex that we had in the beginning
             DBUG_ASSERT($1 == Lex->current_select);
@@ -11198,7 +11211,6 @@ select_derived:
                 MYSQL_YYABORT;
               }
             }
-            $$= NULL;
           }
         ;
 
@@ -11224,9 +11236,6 @@ select_derived2:
             Select->parsing_place= NO_MATTER;
           }
           opt_table_expression
-          opt_order_clause
-          opt_limit_clause
-          opt_select_lock_type
         ;
 
 get_select_lex: