← Back to team overview

maria-developers team mailing list archive

  1. maria-developers team
  2. Mailing list archive
  3. Message #08891

Re: [Commits] 93a7c11: MDEV-8380: Subquery parse error

  Thread Previous • Date Previous • • Thread Next 
On 12.09.15 21:12, Sergei Golubchik wrote:

Hi, Sanja!

On Aug 31, sanja@xxxxxxxxxxx wrote:

revision-id: 93a7c11c51e2f5c0ca98600924172e452b6188b6 (mariadb-10.1.6-24-g93a7c11)
parent(s): d07b780db239a909e667edf5657e3633e80d1451
committer: Oleksandr Byelkin
timestamp: 2015-08-31 13:10:01 +0200
message:

MDEV-8380: Subquery parse error

backport mysql parser fixes
0034963fbf199696792491bcb79d5f0731c98804
5948561812bc691bd0c13cf518a3fe77d9daf920

diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc
index 6c835c4..1d9aca9 100644
--- a/sql/sql_lex.cc
+++ b/sql/sql_lex.cc
@@ -546,6 +546,7 @@ void lex_start(THD *thd)
    lex->limit_rows_examined_cnt= ULONGLONG_MAX;
    lex->var_list.empty();
    lex->stmt_var_list.empty();
+  lex->proc_list.elements=0;

why?
this wasn't in the patches you've backported.
They have no list of procedures (it looks like they have onlu one procedure - Analyse). So I have to initialize it to detect was there something.
lex->is_lex_started= TRUE; 
    DBUG_VOID_RETURN;
@@ -852,6 +853,29 @@ bool Lex_input_stream::get_text(LEX_STRING *dst, int pre_skip, int post_skip)
  }
+uint Lex_input_stream::get_lineno(const char *raw_ptr) 
+{
+  DBUG_ASSERT(m_buf <= raw_ptr && raw_ptr < m_end_of_query);
+  if (!(m_buf <= raw_ptr && raw_ptr < m_end_of_query))
+    return 1;
+
+  uint ret= 1;
+  const CHARSET_INFO *cs= m_thd->charset();
+  for (const char *c= m_buf; c < raw_ptr; c++)
+  {
+    uint mb_char_len;
+    if (use_mb(cs) && (mb_char_len= my_ismbchar(cs, c, m_end_of_query)))
+    {
+      c+= mb_char_len - 1; // skip the rest of the multibyte character
+      continue; // we don't expect '\n' there
+    }
+    if (*c == '\n')
+      ret++;
+  }
+  return ret;
+}

why? this doesn't seem to be used anywhere.
yes, it was used in parse error message, but I did not ported it, because there was problems with parser functions we use (versions???). 
I'll remove it, sorry.



+
+
  /*
  ** Calc type of integer; long integer, longlong integer or real.
  ** Returns smallest type that match the string.
diff --git a/sql/sql_lex.h b/sql/sql_lex.h
index 09a3d5e..529c4d5 100644
--- a/sql/sql_lex.h
+++ b/sql/sql_lex.h
@@ -2087,6 +2087,8 @@ class Lex_input_stream
                                  CHARSET_INFO *txt_cs,
                                  const char *end_ptr);
+ uint get_lineno(const char *raw_ptr); 
+
    /** Current thread. */
    THD *m_thd;
diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc 
index 5e58ceb..f7eed09 100644
--- a/sql/sql_parse.cc
+++ b/sql/sql_parse.cc
@@ -6932,11 +6932,28 @@ mysql_new_select(LEX *lex, bool move_down)
    }
    else
    {
+    bool const outer_most= (lex->current_select->master_unit() == &lex->unit);
+    if (outer_most && lex->result)
+    {
+      my_error(ER_WRONG_USAGE, MYF(0), "UNION", "INTO");
+      DBUG_RETURN(TRUE);
+    }
+    if (lex->proc_list.elements!=0)
+    {
+      my_error(ER_WRONG_USAGE, MYF(0), "UNION",
+               "SELECT ... PROCEDURE ANALYSE()");
+      DBUG_RETURN(TRUE);
+    }

why couldn't parser do it?

because sometimes it allowed, sometimes no.



      if (lex->current_select->order_list.first && !lex->current_select->braces)
      {
        my_error(ER_WRONG_USAGE, MYF(0), "UNION", "ORDER BY");
        DBUG_RETURN(1);
      }
+    if (lex->current_select->explicit_limit && !lex->current_select->braces)
+    {
+      my_error(ER_WRONG_USAGE, MYF(0), "UNION", "LIMIT");
+      DBUG_RETURN(1);
+    }

why couldn't parser do it?

again I think the same cause.



      select_lex->include_neighbour(lex->current_select);
      SELECT_LEX_UNIT *unit= select_lex->master_unit();
      if (!unit->fake_select_lex && unit->add_fake_select_lex(lex->thd))
diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy
index 0e2db26..b79c2ce 100644
--- a/sql/sql_yacc.yy
+++ b/sql/sql_yacc.yy
@@ -1,5 +1,5 @@
  /*
-   Copyright (c) 2000, 2015, Oracle and/or its affiliates.
+   Copyrig ht(c) 2000, 2015, Oracle and/or its affiliates.

typo?

sorry, yes

     Copyright (c) 2010, 2015, MariaDB
This program is free software; you can redistribute it and/or modify 
@@ -1001,10 +1001,10 @@ bool my_yyoverflow(short **a, YYSTYPE **b, ulong *yystacksize);
  %parse-param { THD *thd }
  %lex-param { THD *thd }
  /*
-  Currently there are 164 shift/reduce conflicts.
+  Currently there are 160 shift/reduce conflicts.
    We should not introduce new conflicts any more.
  */
-%expect 164
+%expect 160
/* 
     Comments for TOKENS.
@@ -1731,6 +1731,7 @@ bool my_yyoverflow(short **a, YYSTYPE **b, ulong *yystacksize);
          opt_default_time_precision
          case_stmt_body opt_bin_mod
          opt_if_exists_table_element opt_if_not_exists_table_element
+        opt_into opt_procedure_clause
%type <object_ddl_options> 
          create_or_replace
@@ -5548,7 +5549,7 @@ create_select:
            {
              Select->parsing_place= NO_MATTER;
            }
-          opt_select_from
+          table_expression
            {
              /*
                The following work only with the local list, the global list
@@ -5943,11 +5944,6 @@ merge_insert_types:
         | LAST_SYM        { $$= MERGE_INSERT_TO_LAST; }
         ;
-opt_select_from: 
-          opt_limit_clause {}
-        | select_from opt_select_lock_type
-        ;
-
  udf_type:
            STRING_SYM {$$ = (int) STRING_RESULT; }
          | REAL {$$ = (int) REAL_RESULT; }
@@ -8397,6 +8393,7 @@ select_paren_derived:
              Lex->current_select->set_braces(true);
            }
            SELECT_SYM select_part2_derived
+          table_expression
            {
              if (setup_select_in_parentheses(Lex))
                MYSQL_YYABORT;
@@ -8414,7 +8411,45 @@ select_init2:
            union_clause
          ;
+/* 
+  Theoretically we can merge all 3 right hand sides of the select_part2
+  rule into one, however such a transformation adds one shift/reduce
+  conflict more.
+*/
  select_part2:
+          select_options_and_item_list
+          opt_order_clause
+          opt_limit_clause
+          opt_select_lock_type
+        | select_options_and_item_list into opt_select_lock_type
+        | select_options_and_item_list
+          opt_into
+          from_clause
+          opt_where_clause
+          opt_group_clause
+          opt_having_clause
+          opt_order_clause
+          opt_limit_clause
+          opt_procedure_clause
+          opt_into
+          opt_select_lock_type

why not table_expression here?
Maybe to make the following check possible (to avoid double INTO). Now they rewrited this part made rules returning syntax constructions, but it s too much changes for bugfix. 
+          {
+            if ($2 && $10)
+            {
+              /* double "INTO" clause */
+              my_error(ER_WRONG_USAGE, MYF(0), "INTO", "INTO");
+              MYSQL_YYABORT;
+            }
+            if ($9 && ($2 || $10))
+            {
+              /* "INTO" with "PROCEDURE ANALYSE" */
+              my_error(ER_WRONG_USAGE, MYF(0), "PROCEDURE", "INTO");
+              MYSQL_YYABORT;
+            }
+          }

Why couldn't parser do that?


+        ;
+
+select_options_and_item_list:
            {
              LEX *lex= Lex;
              SELECT_LEX *sel= lex->current_select;
@@ -8426,27 +8461,36 @@ select_part2:
            {
              Select->parsing_place= NO_MATTER;
            }
-          select_into opt_select_lock_type
          ;
-select_into: 
-          opt_order_clause opt_limit_clause {}
-        | into
-        | select_from
-        | into select_from
-        | select_from into
+table_expression:
+          opt_from_clause
+          opt_where_clause
+          opt_group_clause
+          opt_having_clause
+          opt_order_clause
+          opt_limit_clause
+          opt_procedure_clause
+          opt_select_lock_type
+        ;
+
+from_clause:
+          FROM table_reference_list
+        ;
+
+opt_from_clause:
+          /* empty */
+        | from_clause
          ;
-select_from: 
-          FROM join_table_list
+table_reference_list:
+          join_table_list
            {
              Select->context.table_list=
                Select->context.first_name_resolution_table=
                  Select->table_list.first;
            }
-          opt_where_clause opt_group_clause opt_having_clause
-          opt_order_clause opt_limit_clause opt_procedure_clause
-        | FROM DUAL_SYM opt_where_clause opt_limit_clause
+        | DUAL_SYM
            /* oracle compatibility: oracle always requires FROM clause,
               and DUAL is system table without fields.
               Is "SELECT 1 FROM DUAL" any better than "SELECT 1" ?
@@ -10888,9 +10932,7 @@ select_derived_union:
                last select in the union.
               */
              Lex->pop_context();
-          }
-          opt_union_order_or_limit
-          {
+
              if ($1 != NULL)
              {
                my_parse_error(ER_THD(thd, ER_SYNTAX_ERROR));
@@ -10910,12 +10952,14 @@ select_init2_derived:
                my_parse_error(ER_THD(thd, ER_SYNTAX_ERROR));
                MYSQL_YYABORT;
              }
+            /*
              if (sel->linkage == UNION_TYPE &&
                  sel->master_unit()->first_select()->braces)
              {
                my_parse_error(ER_THD(thd, ER_SYNTAX_ERROR));
                MYSQL_YYABORT;
              }
+            */

what was wrong with that?
if it's not needed anymore, delete it, don't just comment it out.

incorrectly reported errors because rules are changed.

            }
          ;
@@ -10932,7 +10976,6 @@ select_part2_derived: 
            {
              Select->parsing_place= NO_MATTER;
            }
-          opt_select_from opt_select_lock_type
          ;
/* handle contents of parentheses in join expression */ 
@@ -10980,7 +11023,7 @@ select_derived2:
            {
              Select->parsing_place= NO_MATTER;
            }
-          opt_select_from
+          table_expression
          ;
get_select_lex: 
@@ -11556,7 +11599,7 @@ choice:
  	;
opt_procedure_clause: 
-          /* empty */
+          /* empty */ { $$= false; }
          | PROCEDURE_SYM ident /* Procedure name */
            {
              LEX *lex=Lex;
@@ -11585,6 +11628,9 @@ opt_procedure_clause:
              Lex->uncacheable(UNCACHEABLE_SIDEEFFECT);
            }
            '(' procedure_list ')'
+          {
+            $$= true;
+          }
          ;
procedure_list: 
@@ -11661,6 +11707,11 @@ select_outvar:
            }
          ;
+opt_into: 
+          /* empty */ { $$= false; }
+        | into        { $$= true; }
+        ;
+
  into:
            INTO
            {
@@ -12496,7 +12547,8 @@ show_param:
            {
              LEX *lex= Lex;
              lex->sql_command= SQLCOM_SHOW_BINLOG_EVENTS;
-          } opt_limit_clause
+          }
+          opt_limit_clause
          | RELAYLOG_SYM optional_connection_name EVENTS_SYM binlog_in binlog_from
            {
              LEX *lex= Lex;
@@ -15836,17 +15888,19 @@ union_option:
query_specification: 
            SELECT_SYM select_init2_derived
-          {
+          table_expression
+          {
              $$= Lex->current_select->master_unit()->first_select();
            }
          | '(' select_paren_derived ')'
+          opt_union_order_or_limit
            {
              $$= Lex->current_select->master_unit()->first_select();
            }
          ;
query_expression_body: 
-          query_specification opt_union_order_or_limit
+          query_specification
          | query_expression_body
            UNION_SYM union_option
            {
@@ -15854,7 +15908,6 @@ query_expression_body:
                MYSQL_YYABORT;
            }
            query_specification
-          opt_union_order_or_limit
            {
              Lex->pop_context();
              $$= $1;
diff --git a/mysql-test/r/parser.result b/mysql-test/r/parser.result
index 1d29f2c..01cc9d7 100644
--- a/mysql-test/r/parser.result
+++ b/mysql-test/r/parser.result
@@ -650,3 +650,230 @@ CREATE TABLE t1(a INT);
  SELECT * FROM t1 JOIN ((SELECT 1 AS b) UNION ALL (SELECT 2 AS b) ORDER BY b DESC) s1 WHERE a=1;
  a	b
  DROP TABLE t1;
+#
+# Test of collective fix for three parser bugs:
+#
+# Bug #17727401, Bug #17426017, Bug #17473479:
+#   The server accepts wrong syntax and then fails in different ways
+#
+CREATE TABLE t1 (i INT);
+# bug #17426017
+SELECT (SELECT EXISTS(SELECT * LIMIT 1 ORDER BY VALUES (c00)));
+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 VALUES (c00)))' at line 1
+# bug#17473479
+CREATE TABLE a(a int);
+CREATE TABLE b(a int);
+DELETE FROM b ORDER BY(SELECT 1 FROM a ORDER BY a ORDER BY a);
+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 a)' at line 1
+DROP TABLE a, b;
+# bug #17727401
+SELECT '' IN (SELECT '1' c FROM t1 ORDER BY '' ORDER BY '') 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 '') FROM t1' at line 1
+# regression & coverage tests
+# uniform syntax for FROM DUAL clause:
+SELECT 1 FROM DUAL WHERE 1 GROUP BY 1 HAVING 1 ORDER BY 1
+FOR UPDATE;
+1
+1
+SELECT 1 FROM DUAL WHERE 1 GROUP BY 1 HAVING 1 ORDER BY 1
+PROCEDURE ANALYSE() FOR UPDATE;
+ERROR HY000: Can't use ORDER clause with this procedure
+SELECT 1 FROM
+(SELECT 1 FROM DUAL WHERE 1 GROUP BY 1 HAVING 1 ORDER BY 1
+FOR UPDATE) a;
+1
+1
+SELECT 1 FROM
+(SELECT 1 FROM DUAL WHERE 1 GROUP BY 1 HAVING 1 ORDER BY 1
+PROCEDURE ANALYSE() FOR UPDATE) a;
+ERROR HY000: Incorrect usage of PROCEDURE and subquery
+SELECT 1 FROM t1
+WHERE EXISTS(SELECT 1 FROM DUAL WHERE 1 GROUP BY 1 HAVING 1 ORDER BY 1
+FOR UPDATE);
+1
+SELECT 1 FROM t1
+WHERE EXISTS(SELECT 1 FROM DUAL WHERE 1 GROUP BY 1 HAVING 1 ORDER BY 1
+PROCEDURE ANALYSE() FOR UPDATE);
+ERROR HY000: Incorrect usage of PROCEDURE and subquery
+SELECT 1 FROM t1
+UNION
+SELECT 1 FROM DUAL WHERE 1 GROUP BY 1 HAVING 1 ORDER BY 1
+FOR UPDATE;
+1
+1
+SELECT 1 FROM t1
+UNION
+SELECT 1 FROM DUAL WHERE 1 GROUP BY 1 HAVING 1 ORDER BY 1
+PROCEDURE ANALYSE() FOR UPDATE;
+ERROR HY000: Incorrect usage of PROCEDURE and subquery
+SELECT 1 FROM DUAL PROCEDURE ANALYSE()
+UNION
+SELECT 1 FROM t1;
+ERROR HY000: Incorrect usage of UNION and SELECT ... PROCEDURE ANALYSE()
+(SELECT 1 FROM t1)
+UNION
+(SELECT 1 FROM DUAL WHERE 1 GROUP BY 1 HAVING 1 ORDER BY 1
+FOR UPDATE);
+1
+1
+(SELECT 1 FROM t1)
+UNION
+(SELECT 1 FROM DUAL WHERE 1 GROUP BY 1 HAVING 1 ORDER BY 1
+PROCEDURE ANALYSE() FOR UPDATE);
+ERROR HY000: Incorrect usage of PROCEDURE and subquery
+# "FOR UPDATE" tests
+SELECT 1 FROM t1 UNION SELECT 1 FROM t1 ORDER BY 1 LIMIT 1;
+1
+SELECT 1 FROM t1 FOR UPDATE UNION SELECT 1 FROM t1 ORDER BY 1 LIMIT 1;
+1
+SELECT 1 FROM t1 UNION SELECT 1 FROM t1 ORDER BY 1 LIMIT 1 FOR UPDATE;
+1
+# "INTO" clause tests
+SELECT 1 FROM t1 INTO @var17727401;
+Warnings:
+Warning	1329	No data - zero rows fetched, selected, or processed
+SELECT 1 FROM DUAL INTO @var17727401;
+SELECT 1 INTO @var17727401;
+SELECT 1 INTO @var17727401 FROM t1;
+Warnings:
+Warning	1329	No data - zero rows fetched, selected, or processed
+SELECT 1 INTO @var17727401 FROM DUAL;
+SELECT 1 INTO @var17727401_1 FROM t1 INTO @var17727401_2;
+ERROR HY000: Incorrect usage of INTO and INTO
+SELECT 1 INTO @var17727401_1 FROM DUAL
+INTO @var17727401_2;
+ERROR HY000: Incorrect usage of INTO and INTO
+SELECT 1 INTO @var17727401 FROM t1 WHERE 1 GROUP BY 1 HAVING 1 ORDER BY 1 LIMIT 1;
+Warnings:
+Warning	1329	No data - zero rows fetched, selected, or processed
+SELECT 1 FROM t1 WHERE 1 GROUP BY 1 HAVING 1 ORDER BY 1 LIMIT 1 INTO @var17727401;
+Warnings:
+Warning	1329	No data - zero rows fetched, selected, or processed
+SELECT 1 FROM t1 WHERE 1 INTO @var17727401 GROUP BY 1 HAVING 1 ORDER BY 1 LIMIT 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 'GROUP BY 1 HAVING 1 ORDER BY 1 LIMIT 1' at line 1
+SELECT 1 INTO @var17727401_1
+FROM t1 WHERE 1 GROUP BY 1 HAVING 1 ORDER BY 1 LIMIT 1
+INTO @var17727401_2;
+ERROR HY000: Incorrect usage of INTO and INTO
+SELECT (SELECT 1 FROM t1 INTO @var17727401);
+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 'INTO @var17727401)' at line 1
+SELECT 1 FROM (SELECT 1 FROM t1 INTO @var17727401) a;
+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 'INTO @var17727401) a' at line 1
+SELECT EXISTS(SELECT 1 FROM t1 INTO @var17727401);
+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 'INTO @var17727401)' at line 1
+SELECT 1 FROM t1 INTO @var17727401 UNION SELECT 1 FROM t1 INTO t1;
+ERROR HY000: Incorrect usage of UNION and INTO
+(SELECT 1 FROM t1 INTO @var17727401) UNION (SELECT 1 FROM t1 INTO t1);
+ERROR HY000: Incorrect usage of UNION and INTO
+SELECT 1 FROM t1 UNION SELECT 1 FROM t1 INTO @var17727401;
+Warnings:
+Warning	1329	No data - zero rows fetched, selected, or processed
+SELECT 1 INTO @var17727401 FROM t1 PROCEDURE ANALYSE();
+ERROR HY000: Incorrect usage of PROCEDURE and INTO
+SELECT 1 FROM t1 PROCEDURE ANALYSE() INTO @var17727401;
+ERROR HY000: Incorrect usage of PROCEDURE and INTO
+# ORDER and LIMIT clause combinations
+(SELECT 1 FROM t1 ORDER BY 1) ORDER BY 1;
+1
+(SELECT 1 FROM t1 LIMIT 1) LIMIT 1;
+1
+((SELECT 1 FROM t1 ORDER BY 1) ORDER BY 1) 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 'ORDER BY 1) ORDER BY 1' at line 1
+((SELECT 1 FROM t1 LIMIT 1) LIMIT 1) LIMIT 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 'LIMIT 1) LIMIT 1' at line 1
+(SELECT 1 FROM t1 ORDER BY 1) LIMIT 1;
+1
+(SELECT 1 FROM t1 LIMIT 1) ORDER BY 1;
+1
+((SELECT 1 FROM t1 ORDER BY 1) LIMIT 1) 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 'LIMIT 1) ORDER BY 1)' at line 1
+((SELECT 1 FROM t1 LIMIT 1) ORDER BY 1) LIMIT 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 'ORDER BY 1) LIMIT 1)' at line 1
+SELECT 1 FROM t1 UNION SELECT 1 FROM t1 ORDER BY 1;
+1
+SELECT (SELECT 1 FROM t1 UNION SELECT 1 FROM t1 ORDER BY 1);
+(SELECT 1 FROM t1 UNION SELECT 1 FROM t1 ORDER BY 1)
+NULL
+SELECT 1 FROM (SELECT 1 FROM t1 UNION SELECT 1 FROM t1 ORDER BY 1) a;
+1
+SELECT 1 FROM t1 UNION SELECT 1 FROM t1 LIMIT 1;
+1
+SELECT (SELECT 1 FROM t1 UNION SELECT 1 FROM t1 LIMIT 1);
+(SELECT 1 FROM t1 UNION SELECT 1 FROM t1 LIMIT 1)
+NULL
+SELECT 1 FROM (SELECT 1 FROM t1 UNION SELECT 1 FROM t1 LIMIT 1) a;
+1
+SELECT 1 FROM t1 UNION SELECT 1 FROM t1 ORDER BY 1 LIMIT 1;
+1
+SELECT (SELECT 1 FROM t1 UNION SELECT 1 FROM t1 ORDER BY 1 LIMIT 1);
+(SELECT 1 FROM t1 UNION SELECT 1 FROM t1 ORDER BY 1 LIMIT 1)
+NULL
+SELECT 1 FROM (SELECT 1 FROM t1 UNION SELECT 1 FROM t1 ORDER BY 1 LIMIT 1) a;
+1
+SELECT 1 FROM t1 UNION SELECT 1 FROM t1 LIMIT 1 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 'ORDER BY 1' at line 1
+SELECT (SELECT 1 FROM t1 UNION SELECT 1 FROM t1 LIMIT 1 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 'ORDER BY 1)' at line 1
+SELECT 1 FROM (SELECT 1 FROM t1 UNION SELECT 1 FROM t1 LIMIT 1 ORDER BY 1) a;
+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) a' at line 1
+SELECT 1 FROM t1 ORDER BY 1 UNION SELECT 1 FROM t1;
+ERROR HY000: Incorrect usage of UNION and ORDER BY
+SELECT (SELECT 1 FROM t1 ORDER BY 1 UNION SELECT 1 FROM t1);
+ERROR HY000: Incorrect usage of UNION and ORDER BY
+SELECT 1 FROM (SELECT 1 FROM t1 ORDER BY 1 UNION SELECT 1 FROM t1) a;
+ERROR HY000: Incorrect usage of UNION and ORDER BY
+SELECT 1 FROM t1 LIMIT 1 UNION SELECT 1 FROM t1;
+ERROR HY000: Incorrect usage of UNION and LIMIT
+SELECT (SELECT 1 FROM t1 LIMIT 1 UNION SELECT 1 FROM t1);
+ERROR HY000: Incorrect usage of UNION and LIMIT
+SELECT 1 FROM (SELECT 1 FROM t1 LIMIT 1 UNION SELECT 1 FROM t1) a;
+ERROR HY000: Incorrect usage of UNION and LIMIT
+SELECT 1 FROM t1 ORDER BY 1 LIMIT 1 UNION SELECT 1 FROM t1;
+ERROR HY000: Incorrect usage of UNION and ORDER BY
+SELECT (SELECT 1 FROM t1 ORDER BY 1 LIMIT 1 UNION SELECT 1 FROM t1);
+ERROR HY000: Incorrect usage of UNION and ORDER BY
+SELECT 1 FROM (SELECT 1 FROM t1 ORDER BY 1 LIMIT 1 UNION SELECT 1 FROM t1) a;
+ERROR HY000: Incorrect usage of UNION and ORDER BY
+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 (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
+SELECT 1 FROM t1 ORDER BY 1 UNION SELECT 1 FROM t1 ORDER BY 1;
+ERROR HY000: Incorrect usage of UNION and ORDER BY
+SELECT (SELECT 1 FROM t1 ORDER BY 1 UNION SELECT 1 FROM t1 ORDER BY 1);
+ERROR HY000: Incorrect usage of UNION and ORDER BY
+SELECT 1 FROM (SELECT 1 FROM t1 ORDER BY 1 UNION SELECT 1 FROM t1 ORDER BY 1) a;
+ERROR HY000: Incorrect usage of UNION and ORDER BY
+SELECT 1 FROM t1 LIMIT 1 UNION SELECT 1 FROM t1 LIMIT 1;
+ERROR HY000: Incorrect usage of UNION and LIMIT
+SELECT (SELECT 1 FROM t1 LIMIT 1 UNION SELECT 1 FROM t1 LIMIT 1);
+ERROR HY000: Incorrect usage of UNION and LIMIT
+SELECT 1 FROM (SELECT 1 FROM t1 LIMIT 1 UNION SELECT 1 FROM t1 LIMIT 1) a;
+ERROR HY000: Incorrect usage of UNION and LIMIT
+SELECT 1 FROM t1 LIMIT 1 UNION SELECT 1 FROM t1 ORDER BY 1;
+ERROR HY000: Incorrect usage of UNION and LIMIT
+SELECT (SELECT 1 FROM t1 LIMIT 1 UNION SELECT 1 FROM t1 ORDER BY 1);
+ERROR HY000: Incorrect usage of UNION and LIMIT
+SELECT 1 FROM (SELECT 1 FROM t1 LIMIT 1 UNION SELECT 1 FROM t1 ORDER BY 1) a;
+ERROR HY000: Incorrect usage of UNION and LIMIT
+SELECT 1 FROM t1 ORDER BY 1 UNION SELECT 1 FROM t1 LIMIT 1;
+ERROR HY000: Incorrect usage of UNION and ORDER BY
+SELECT (SELECT 1 FROM t1 ORDER BY 1 UNION SELECT 1 FROM t1 LIMIT 1);
+ERROR HY000: Incorrect usage of UNION and ORDER BY
+SELECT 1 FROM (SELECT 1 FROM t1 ORDER BY 1 UNION SELECT 1 FROM t1 LIMIT 1) a;
+ERROR HY000: Incorrect usage of UNION and ORDER BY
+DROP TABLE t1;
+#
+# MDEV-8380: Subquery parse error
+#
+CREATE TABLE t1 ( a INT);
+INSERT INTO t1 VALUES ( 2 );
+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;
+a
+2
+1
+DROP TABLE t1;
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index 3732f62..b9f9fb0 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -5132,8 +5132,9 @@ SELECT a FROM t1 WHERE a = 2
  a
  1
  2
-SELECT * FROM ((SELECT 1 a) UNION SELECT 1 a);
-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 ')' at line 1
+SELECT * FROM ((SELECT 1 a) UNION SELECT 1 a) q;
+a
+1
  SELECT * FROM (SELECT 1 a UNION (SELECT 1 a)) alias;
  a
  1
@@ -5175,7 +5176,7 @@ a	1
  1	1
  2	1
  SELECT * FROM t1 JOIN ((SELECT 1 UNION SELECT 1)) ON 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 ')) ON 1' at line 1
+ERROR 42000: Every derived table must have its own alias
  SELECT * FROM t1 JOIN  (t1 t1a UNION SELECT 1)  ON 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 ')  ON 1' at line 1
  SELECT * FROM t1 JOIN ((t1 t1a UNION SELECT 1)) ON 1;
@@ -5294,7 +5295,10 @@ SELECT ((SELECT 1 UNION SELECT 1 UNION SELECT 1));
  SELECT * FROM ( SELECT 1 UNION ( SELECT 1 UNION SELECT 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 ) )' at line 1
  SELECT * FROM ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 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 )' at line 1
+ERROR 42000: Every derived table must have its own alias
+SELECT * FROM ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1 ) a;
+1
+1
  SELECT * FROM ( SELECT 1 UNION SELECT 1 UNION SELECT 1 ) a;
  1
  1
diff --git a/mysql-test/r/subselect_no_exists_to_in.result b/mysql-test/r/subselect_no_exists_to_in.result
index 140b790..902c45f 100644
--- a/mysql-test/r/subselect_no_exists_to_in.result
+++ b/mysql-test/r/subselect_no_exists_to_in.result
@@ -5134,8 +5134,9 @@ SELECT a FROM t1 WHERE a = 2
  a
  1
  2
-SELECT * FROM ((SELECT 1 a) UNION SELECT 1 a);
-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 ')' at line 1
+SELECT * FROM ((SELECT 1 a) UNION SELECT 1 a) q;
+a
+1
  SELECT * FROM (SELECT 1 a UNION (SELECT 1 a)) alias;
  a
  1
@@ -5177,7 +5178,7 @@ a	1
  1	1
  2	1
  SELECT * FROM t1 JOIN ((SELECT 1 UNION SELECT 1)) ON 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 ')) ON 1' at line 1
+ERROR 42000: Every derived table must have its own alias
  SELECT * FROM t1 JOIN  (t1 t1a UNION SELECT 1)  ON 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 ')  ON 1' at line 1
  SELECT * FROM t1 JOIN ((t1 t1a UNION SELECT 1)) ON 1;
@@ -5296,7 +5297,10 @@ SELECT ((SELECT 1 UNION SELECT 1 UNION SELECT 1));
  SELECT * FROM ( SELECT 1 UNION ( SELECT 1 UNION SELECT 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 ) )' at line 1
  SELECT * FROM ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 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 )' at line 1
+ERROR 42000: Every derived table must have its own alias
+SELECT * FROM ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1 ) a;
+1
+1
  SELECT * FROM ( SELECT 1 UNION SELECT 1 UNION SELECT 1 ) a;
  1
  1
diff --git a/mysql-test/r/subselect_no_mat.result b/mysql-test/r/subselect_no_mat.result
index 99ac9f4..89034b7 100644
--- a/mysql-test/r/subselect_no_mat.result
+++ b/mysql-test/r/subselect_no_mat.result
@@ -5132,8 +5132,9 @@ SELECT a FROM t1 WHERE a = 2
  a
  1
  2
-SELECT * FROM ((SELECT 1 a) UNION SELECT 1 a);
-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 ')' at line 1
+SELECT * FROM ((SELECT 1 a) UNION SELECT 1 a) q;
+a
+1
  SELECT * FROM (SELECT 1 a UNION (SELECT 1 a)) alias;
  a
  1
@@ -5175,7 +5176,7 @@ a	1
  1	1
  2	1
  SELECT * FROM t1 JOIN ((SELECT 1 UNION SELECT 1)) ON 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 ')) ON 1' at line 1
+ERROR 42000: Every derived table must have its own alias
  SELECT * FROM t1 JOIN  (t1 t1a UNION SELECT 1)  ON 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 ')  ON 1' at line 1
  SELECT * FROM t1 JOIN ((t1 t1a UNION SELECT 1)) ON 1;
@@ -5294,7 +5295,10 @@ SELECT ((SELECT 1 UNION SELECT 1 UNION SELECT 1));
  SELECT * FROM ( SELECT 1 UNION ( SELECT 1 UNION SELECT 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 ) )' at line 1
  SELECT * FROM ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 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 )' at line 1
+ERROR 42000: Every derived table must have its own alias
+SELECT * FROM ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1 ) a;
+1
+1
  SELECT * FROM ( SELECT 1 UNION SELECT 1 UNION SELECT 1 ) a;
  1
  1
diff --git a/mysql-test/r/subselect_no_opts.result b/mysql-test/r/subselect_no_opts.result
index 7ecd40c..6e90aac 100644
--- a/mysql-test/r/subselect_no_opts.result
+++ b/mysql-test/r/subselect_no_opts.result
@@ -5128,8 +5128,9 @@ SELECT a FROM t1 WHERE a = 2
  a
  1
  2
-SELECT * FROM ((SELECT 1 a) UNION SELECT 1 a);
-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 ')' at line 1
+SELECT * FROM ((SELECT 1 a) UNION SELECT 1 a) q;
+a
+1
  SELECT * FROM (SELECT 1 a UNION (SELECT 1 a)) alias;
  a
  1
@@ -5171,7 +5172,7 @@ a	1
  1	1
  2	1
  SELECT * FROM t1 JOIN ((SELECT 1 UNION SELECT 1)) ON 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 ')) ON 1' at line 1
+ERROR 42000: Every derived table must have its own alias
  SELECT * FROM t1 JOIN  (t1 t1a UNION SELECT 1)  ON 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 ')  ON 1' at line 1
  SELECT * FROM t1 JOIN ((t1 t1a UNION SELECT 1)) ON 1;
@@ -5290,7 +5291,10 @@ SELECT ((SELECT 1 UNION SELECT 1 UNION SELECT 1));
  SELECT * FROM ( SELECT 1 UNION ( SELECT 1 UNION SELECT 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 ) )' at line 1
  SELECT * FROM ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 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 )' at line 1
+ERROR 42000: Every derived table must have its own alias
+SELECT * FROM ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1 ) a;
+1
+1
  SELECT * FROM ( SELECT 1 UNION SELECT 1 UNION SELECT 1 ) a;
  1
  1
diff --git a/mysql-test/r/subselect_no_scache.result b/mysql-test/r/subselect_no_scache.result
index c70db6f..1c18817 100644
--- a/mysql-test/r/subselect_no_scache.result
+++ b/mysql-test/r/subselect_no_scache.result
@@ -5138,8 +5138,9 @@ SELECT a FROM t1 WHERE a = 2
  a
  1
  2
-SELECT * FROM ((SELECT 1 a) UNION SELECT 1 a);
-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 ')' at line 1
+SELECT * FROM ((SELECT 1 a) UNION SELECT 1 a) q;
+a
+1
  SELECT * FROM (SELECT 1 a UNION (SELECT 1 a)) alias;
  a
  1
@@ -5181,7 +5182,7 @@ a	1
  1	1
  2	1
  SELECT * FROM t1 JOIN ((SELECT 1 UNION SELECT 1)) ON 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 ')) ON 1' at line 1
+ERROR 42000: Every derived table must have its own alias
  SELECT * FROM t1 JOIN  (t1 t1a UNION SELECT 1)  ON 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 ')  ON 1' at line 1
  SELECT * FROM t1 JOIN ((t1 t1a UNION SELECT 1)) ON 1;
@@ -5300,7 +5301,10 @@ SELECT ((SELECT 1 UNION SELECT 1 UNION SELECT 1));
  SELECT * FROM ( SELECT 1 UNION ( SELECT 1 UNION SELECT 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 ) )' at line 1
  SELECT * FROM ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 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 )' at line 1
+ERROR 42000: Every derived table must have its own alias
+SELECT * FROM ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1 ) a;
+1
+1
  SELECT * FROM ( SELECT 1 UNION SELECT 1 UNION SELECT 1 ) a;
  1
  1
diff --git a/mysql-test/r/subselect_no_semijoin.result b/mysql-test/r/subselect_no_semijoin.result
index 6a7d6b3..de9c4e8 100644
--- a/mysql-test/r/subselect_no_semijoin.result
+++ b/mysql-test/r/subselect_no_semijoin.result
@@ -5128,8 +5128,9 @@ SELECT a FROM t1 WHERE a = 2
  a
  1
  2
-SELECT * FROM ((SELECT 1 a) UNION SELECT 1 a);
-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 ')' at line 1
+SELECT * FROM ((SELECT 1 a) UNION SELECT 1 a) q;
+a
+1
  SELECT * FROM (SELECT 1 a UNION (SELECT 1 a)) alias;
  a
  1
@@ -5171,7 +5172,7 @@ a	1
  1	1
  2	1
  SELECT * FROM t1 JOIN ((SELECT 1 UNION SELECT 1)) ON 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 ')) ON 1' at line 1
+ERROR 42000: Every derived table must have its own alias
  SELECT * FROM t1 JOIN  (t1 t1a UNION SELECT 1)  ON 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 ')  ON 1' at line 1
  SELECT * FROM t1 JOIN ((t1 t1a UNION SELECT 1)) ON 1;
@@ -5290,7 +5291,10 @@ SELECT ((SELECT 1 UNION SELECT 1 UNION SELECT 1));
  SELECT * FROM ( SELECT 1 UNION ( SELECT 1 UNION SELECT 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 ) )' at line 1
  SELECT * FROM ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 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 )' at line 1
+ERROR 42000: Every derived table must have its own alias
+SELECT * FROM ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1 ) a;
+1
+1
  SELECT * FROM ( SELECT 1 UNION SELECT 1 UNION SELECT 1 ) a;
  1
  1
diff --git a/mysql-test/r/union.result b/mysql-test/r/union.result
index ef1749e..9b7a1c5 100644
--- a/mysql-test/r/union.result
+++ b/mysql-test/r/union.result
@@ -89,6 +89,10 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
  NULL	UNION RESULT	<union1,2>	ALL	NULL	NULL	NULL	NULL	NULL	NULL	Using filesort
  Warnings:
  Note	1003	(select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` limit 2) union all (select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` order by `test`.`t2`.`a` limit 1) order by `b` desc
+select count(*) from (
+(select                      a,b from t1 limit 2)  union all (select a,b from t2 order by a)) q;
+count(*)
+6
  (select sql_calc_found_rows  a,b from t1 limit 2)  union all (select a,b from t2 order by a) limit 2;
  a	b
  1	a
@@ -96,6 +100,10 @@ a	b
  select found_rows();
  found_rows()
  6
+select count(*) from (
+select                      a,b from t1  union all select a,b from t2) q;
+count(*)
+8
  select sql_calc_found_rows  a,b from t1  union all select a,b from t2 limit 2;
  a	b
  1	a
@@ -308,12 +316,20 @@ create table t1 (a int);
  insert into t1 values (1),(2),(3);
  create table t2 (a int);
  insert into t2 values (3),(4),(5);
+SELECT COUNT(*) FROM (
+(SELECT                     * FROM t1) UNION all (SELECT * FROM t2)) q;
+COUNT(*)
+6
  (SELECT SQL_CALC_FOUND_ROWS * FROM t1) UNION all (SELECT * FROM t2) LIMIT 1;
  a
  1
  select found_rows();
  found_rows()
  6
+SELECT COUNT(*) FROM (
+(SELECT                     * FROM t1 LIMIT 1) UNION all (SELECT * FROM t2)) q;
+COUNT(*)
+4
  (SELECT SQL_CALC_FOUND_ROWS * FROM t1 LIMIT 1) UNION all (SELECT * FROM t2) LIMIT 2;
  a
  1
@@ -321,6 +337,10 @@ a
  select found_rows();
  found_rows()
  4
+SELECT COUNT(*) FROM (
+(SELECT                     * FROM t1 LIMIT 1) UNION all (SELECT * FROM t2)) q;
+COUNT(*)
+4
  (SELECT SQL_CALC_FOUND_ROWS * FROM t1 LIMIT 1) UNION all (SELECT * FROM t2);
  a
  1
@@ -330,6 +350,10 @@ a
  select found_rows();
  found_rows()
  4
+SELECT COUNT(*) FROM (
+(SELECT                     * FROM t1) UNION all (SELECT * FROM t2 LIMIT 1)) q;
+COUNT(*)
+4
  (SELECT SQL_CALC_FOUND_ROWS * FROM t1) UNION all (SELECT * FROM t2 LIMIT 1);
  a
  1
@@ -345,6 +369,16 @@ a
  select found_rows();
  found_rows()
  4
+SELECT COUNT(*) FROM (
+(SELECT                     * FROM t1 LIMIT 1) UNION SELECT * FROM t2) q;
+COUNT(*)
+4
+(SELECT SQL_CALC_FOUND_ROWS * FROM t1 LIMIT 1) UNION SELECT * FROM t2 LIMIT 1;
+a
+1
+select found_rows();
+found_rows()
+4
  SELECT SQL_CALC_FOUND_ROWS * FROM t1 LIMIT 1 UNION all SELECT * FROM t2 LIMIT 2;
  ERROR HY000: Incorrect usage of UNION and LIMIT
  SELECT SQL_CALC_FOUND_ROWS * FROM t1 UNION all SELECT * FROM t2 LIMIT 2;
@@ -354,13 +388,29 @@ a
  select found_rows();
  found_rows()
  6
+SELECT COUNT(*) FROM (
+SELECT                     * FROM t1 UNION SELECT * FROM t2) q;
+COUNT(*)
+5
  SELECT SQL_CALC_FOUND_ROWS * FROM t1 UNION SELECT * FROM t2 LIMIT 2;
  a
  1
  2
+SELECT COUNT(*) FROM (
+(SELECT                     * FROM t1 LIMIT 1) UNION all SELECT * FROM t2) q;
+COUNT(*)
+4
+(SELECT SQL_CALC_FOUND_ROWS * FROM t1 LIMIT 1) UNION all SELECT * FROM t2 LIMIT 2;
+a
+1
+3
  select found_rows();
  found_rows()
-5
+4
+SELECT COUNT(*) FROM (
+SELECT                     * FROM t1 UNION all SELECT * FROM t2) q;
+COUNT(*)
+6
  SELECT SQL_CALC_FOUND_ROWS * FROM t1 UNION SELECT * FROM t2 LIMIT 100;
  a
  1
@@ -373,10 +423,39 @@ found_rows()
  5
  SELECT SQL_CALC_FOUND_ROWS * FROM t1 LIMIT 100 UNION SELECT * FROM t2;
  ERROR HY000: Incorrect usage of UNION and LIMIT
+SELECT COUNT(*) FROM (
+(SELECT                     * FROM t1 LIMIT 100) UNION SELECT * FROM t2) q;
+COUNT(*)
+5
+(SELECT SQL_CALC_FOUND_ROWS * FROM t1 LIMIT 100) UNION SELECT * FROM t2;
+a
+1
+2
+3
+4
+5
  SELECT SQL_CALC_FOUND_ROWS * FROM t1 LIMIT 1 UNION SELECT * FROM t2;
  ERROR HY000: Incorrect usage of UNION and LIMIT
+SELECT COUNT(*) FROM (
+(SELECT                     * FROM t1 LIMIT 1) UNION SELECT * FROM t2) q;
+COUNT(*)
+4
+(SELECT SQL_CALC_FOUND_ROWS * FROM t1 LIMIT 1) UNION SELECT * FROM t2;
+a
+1
+3
+4
+5
  SELECT SQL_CALC_FOUND_ROWS * FROM t1 LIMIT 1 UNION SELECT * FROM t2 LIMIT 2;
  ERROR HY000: Incorrect usage of UNION and LIMIT
+SELECT COUNT(*) FROM (
+(SELECT                     * FROM t1 LIMIT 1) UNION SELECT * FROM t2) q;
+COUNT(*)
+4
+(SELECT SQL_CALC_FOUND_ROWS * FROM t1 LIMIT 1) UNION SELECT * FROM t2 LIMIT 2;
+a
+1
+3
  SELECT SQL_CALC_FOUND_ROWS * FROM t1 UNION SELECT * FROM t2 LIMIT 2,2;
  a
  3
@@ -384,8 +463,21 @@ a
  select found_rows();
  found_rows()
  5
+SELECT COUNT(*) FROM (
+SELECT                     * FROM t1 UNION SELECT * FROM t2) q;
+COUNT(*)
+5
  SELECT SQL_CALC_FOUND_ROWS * FROM t1 limit 2,2 UNION SELECT * FROM t2;
  ERROR HY000: Incorrect usage of UNION and LIMIT
+SELECT COUNT(*) FROM (
+(SELECT                     * FROM t1 limit 2,2) UNION SELECT * FROM t2) q;
+COUNT(*)
+3
+(SELECT SQL_CALC_FOUND_ROWS * FROM t1 limit 2,2) UNION SELECT * FROM t2;
+a
+3
+4
+5
  SELECT * FROM t1 UNION SELECT * FROM t2 ORDER BY a desc LIMIT 1;
  a
  5
diff --git a/mysql-test/t/parser.test b/mysql-test/t/parser.test
index 31c6ab9..0a19b03 100644
--- a/mysql-test/t/parser.test
+++ b/mysql-test/t/parser.test
@@ -765,3 +765,259 @@ DROP TABLE t1;
  CREATE TABLE t1(a INT);
  SELECT * FROM t1 JOIN ((SELECT 1 AS b) UNION ALL (SELECT 2 AS b) ORDER BY b DESC) s1 WHERE a=1;
  DROP TABLE t1;
+
+
+--echo #
+--echo # Test of collective fix for three parser bugs:
+--echo #
+--echo # Bug #17727401, Bug #17426017, Bug #17473479:
+--echo #   The server accepts wrong syntax and then fails in different ways
+--echo #
+
+CREATE TABLE t1 (i INT);
+
+--echo # bug #17426017
+--error ER_PARSE_ERROR
+SELECT (SELECT EXISTS(SELECT * LIMIT 1 ORDER BY VALUES (c00)));
+
+--echo # bug#17473479
+CREATE TABLE a(a int);
+CREATE TABLE b(a int);
+--error ER_PARSE_ERROR
+DELETE FROM b ORDER BY(SELECT 1 FROM a ORDER BY a ORDER BY a);
+DROP TABLE a, b;
+
+--echo # bug #17727401
+--error ER_PARSE_ERROR
+SELECT '' IN (SELECT '1' c FROM t1 ORDER BY '' ORDER BY '') FROM t1;
+
+--echo # regression & coverage tests
+
+--echo # uniform syntax for FROM DUAL clause:
+
+SELECT 1 FROM DUAL WHERE 1 GROUP BY 1 HAVING 1 ORDER BY 1
+  FOR UPDATE;
+
+--error ER_ORDER_WITH_PROC
+SELECT 1 FROM DUAL WHERE 1 GROUP BY 1 HAVING 1 ORDER BY 1
+  PROCEDURE ANALYSE() FOR UPDATE;
+
+SELECT 1 FROM
+  (SELECT 1 FROM DUAL WHERE 1 GROUP BY 1 HAVING 1 ORDER BY 1
+   FOR UPDATE) a;
+
+--error ER_WRONG_USAGE
+SELECT 1 FROM
+  (SELECT 1 FROM DUAL WHERE 1 GROUP BY 1 HAVING 1 ORDER BY 1
+   PROCEDURE ANALYSE() FOR UPDATE) a;
+
+SELECT 1 FROM t1
+  WHERE EXISTS(SELECT 1 FROM DUAL WHERE 1 GROUP BY 1 HAVING 1 ORDER BY 1
+               FOR UPDATE);
+
+--error ER_WRONG_USAGE
+SELECT 1 FROM t1
+  WHERE EXISTS(SELECT 1 FROM DUAL WHERE 1 GROUP BY 1 HAVING 1 ORDER BY 1
+               PROCEDURE ANALYSE() FOR UPDATE);
+
+SELECT 1 FROM t1
+UNION
+SELECT 1 FROM DUAL WHERE 1 GROUP BY 1 HAVING 1 ORDER BY 1
+  FOR UPDATE;
+
+--error ER_WRONG_USAGE
+SELECT 1 FROM t1
+UNION
+SELECT 1 FROM DUAL WHERE 1 GROUP BY 1 HAVING 1 ORDER BY 1
+  PROCEDURE ANALYSE() FOR UPDATE;
+
+--error ER_WRONG_USAGE
+SELECT 1 FROM DUAL PROCEDURE ANALYSE()
+UNION
+SELECT 1 FROM t1;
+
+(SELECT 1 FROM t1)
+UNION
+(SELECT 1 FROM DUAL WHERE 1 GROUP BY 1 HAVING 1 ORDER BY 1
+ FOR UPDATE);
+
+--error ER_WRONG_USAGE
+(SELECT 1 FROM t1)
+UNION
+(SELECT 1 FROM DUAL WHERE 1 GROUP BY 1 HAVING 1 ORDER BY 1
+ PROCEDURE ANALYSE() FOR UPDATE);
+
+--echo # "FOR UPDATE" tests
+
+SELECT 1 FROM t1 UNION SELECT 1 FROM t1 ORDER BY 1 LIMIT 1;
+SELECT 1 FROM t1 FOR UPDATE UNION SELECT 1 FROM t1 ORDER BY 1 LIMIT 1;
+SELECT 1 FROM t1 UNION SELECT 1 FROM t1 ORDER BY 1 LIMIT 1 FOR UPDATE;
+
+
+--echo # "INTO" clause tests
+
+SELECT 1 FROM t1 INTO @var17727401;
+SELECT 1 FROM DUAL INTO @var17727401;
+SELECT 1 INTO @var17727401;
+
+SELECT 1 INTO @var17727401 FROM t1;
+SELECT 1 INTO @var17727401 FROM DUAL;
+
+--error ER_WRONG_USAGE
+SELECT 1 INTO @var17727401_1 FROM t1 INTO @var17727401_2;
+
+--error ER_WRONG_USAGE
+SELECT 1 INTO @var17727401_1 FROM DUAL
+  INTO @var17727401_2;
+
+SELECT 1 INTO @var17727401 FROM t1 WHERE 1 GROUP BY 1 HAVING 1 ORDER BY 1 LIMIT 1;
+SELECT 1 FROM t1 WHERE 1 GROUP BY 1 HAVING 1 ORDER BY 1 LIMIT 1 INTO @var17727401;
+
+--error ER_PARSE_ERROR
+SELECT 1 FROM t1 WHERE 1 INTO @var17727401 GROUP BY 1 HAVING 1 ORDER BY 1 LIMIT 1;
+
+--error ER_WRONG_USAGE
+SELECT 1 INTO @var17727401_1
+  FROM t1 WHERE 1 GROUP BY 1 HAVING 1 ORDER BY 1 LIMIT 1
+  INTO @var17727401_2;
+
+--error ER_PARSE_ERROR
+SELECT (SELECT 1 FROM t1 INTO @var17727401);
+--error ER_PARSE_ERROR
+SELECT 1 FROM (SELECT 1 FROM t1 INTO @var17727401) a;
+--error ER_PARSE_ERROR
+SELECT EXISTS(SELECT 1 FROM t1 INTO @var17727401);
+
+--error ER_WRONG_USAGE
+SELECT 1 FROM t1 INTO @var17727401 UNION SELECT 1 FROM t1 INTO t1;
+--error ER_WRONG_USAGE
+(SELECT 1 FROM t1 INTO @var17727401) UNION (SELECT 1 FROM t1 INTO t1);
+
+SELECT 1 FROM t1 UNION SELECT 1 FROM t1 INTO @var17727401;
+
+--error ER_WRONG_USAGE
+SELECT 1 INTO @var17727401 FROM t1 PROCEDURE ANALYSE();
+
+--error ER_WRONG_USAGE
+SELECT 1 FROM t1 PROCEDURE ANALYSE() INTO @var17727401;
+
+--echo # ORDER and LIMIT clause combinations
+
+# Limited support for (SELECT ...) ORDER/LIMIT:
+
+(SELECT 1 FROM t1 ORDER BY 1) ORDER BY 1;
+(SELECT 1 FROM t1 LIMIT 1) LIMIT 1;
+
+--error ER_PARSE_ERROR
+((SELECT 1 FROM t1 ORDER BY 1) ORDER BY 1) ORDER BY 1;
+--error ER_PARSE_ERROR
+((SELECT 1 FROM t1 LIMIT 1) LIMIT 1) LIMIT 1;
+
+(SELECT 1 FROM t1 ORDER BY 1) LIMIT 1;
+(SELECT 1 FROM t1 LIMIT 1) ORDER BY 1;
+
+--error ER_PARSE_ERROR
+((SELECT 1 FROM t1 ORDER BY 1) LIMIT 1) ORDER BY 1);
+--error ER_PARSE_ERROR
+((SELECT 1 FROM t1 LIMIT 1) ORDER BY 1) LIMIT 1);
+
+# ORDER/LIMIT and UNION:
+
+let $q=SELECT 1 FROM t1 UNION SELECT 1 FROM t1 ORDER BY 1;
+eval $q;
+eval SELECT ($q);
+eval SELECT 1 FROM ($q) a;
+
+let $q=SELECT 1 FROM t1 UNION SELECT 1 FROM t1 LIMIT 1;
+eval $q;
+eval SELECT ($q);
+eval SELECT 1 FROM ($q) a;
+
+let $q=SELECT 1 FROM t1 UNION SELECT 1 FROM t1 ORDER BY 1 LIMIT 1;
+eval $q;
+eval SELECT ($q);
+eval SELECT 1 FROM ($q) a;
+
+let $q=SELECT 1 FROM t1 UNION SELECT 1 FROM t1 LIMIT 1 ORDER BY 1;
+--error ER_PARSE_ERROR
+eval $q;
+--error ER_PARSE_ERROR
+eval SELECT ($q);
+--error ER_PARSE_ERROR
+eval SELECT 1 FROM ($q) a;
+
+let $q=SELECT 1 FROM t1 ORDER BY 1 UNION SELECT 1 FROM t1;
+--error ER_WRONG_USAGE
+eval $q;
+--error ER_WRONG_USAGE
+eval SELECT ($q);
+--error ER_WRONG_USAGE
+eval SELECT 1 FROM ($q) a;
+
+let $q=SELECT 1 FROM t1 LIMIT 1 UNION SELECT 1 FROM t1;
+--error ER_WRONG_USAGE
+eval $q;
+--error ER_WRONG_USAGE
+eval SELECT ($q);
+--error ER_WRONG_USAGE
+eval SELECT 1 FROM ($q) a;
+
+let $q=SELECT 1 FROM t1 ORDER BY 1 LIMIT 1 UNION SELECT 1 FROM t1;
+--error ER_WRONG_USAGE
+eval $q;
+--error ER_WRONG_USAGE
+eval SELECT ($q);
+--error ER_WRONG_USAGE
+eval SELECT 1 FROM ($q) a;
+
+let $q=SELECT 1 FROM t1 LIMIT 1 ORDER BY 1 UNION SELECT 1 FROM t1;
+--error ER_PARSE_ERROR
+eval $q;
+--error ER_PARSE_ERROR
+eval SELECT ($q);
+--error ER_WRONG_USAGE
+eval SELECT 1 FROM ($q) a;
+
+let $q=SELECT 1 FROM t1 ORDER BY 1 UNION SELECT 1 FROM t1 ORDER BY 1;
+--error ER_WRONG_USAGE
+eval $q;
+--error ER_WRONG_USAGE
+eval SELECT ($q);
+--error ER_WRONG_USAGE
+eval SELECT 1 FROM ($q) a;
+
+let $q=SELECT 1 FROM t1 LIMIT 1 UNION SELECT 1 FROM t1 LIMIT 1;
+--error ER_WRONG_USAGE
+eval $q;
+--error ER_WRONG_USAGE
+eval SELECT ($q);
+--error ER_WRONG_USAGE
+eval SELECT 1 FROM ($q) a;
+
+let $q=SELECT 1 FROM t1 LIMIT 1 UNION SELECT 1 FROM t1 ORDER BY 1;
+--error ER_WRONG_USAGE
+eval $q;
+--error ER_WRONG_USAGE
+eval SELECT ($q);
+--error ER_WRONG_USAGE
+eval SELECT 1 FROM ($q) a;
+
+let $q=SELECT 1 FROM t1 ORDER BY 1 UNION SELECT 1 FROM t1 LIMIT 1;
+--error ER_WRONG_USAGE
+eval $q;
+--error ER_WRONG_USAGE
+eval SELECT ($q);
+--error ER_WRONG_USAGE
+eval SELECT 1 FROM ($q) a;
+
+DROP TABLE t1;
+
+--echo #
+--echo # MDEV-8380: Subquery parse error
+--echo #
+CREATE TABLE t1 ( a INT);
+INSERT INTO t1 VALUES ( 2 );
+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;
diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test
index 80efe7a..8fb9658 100644
--- a/mysql-test/t/subselect.test
+++ b/mysql-test/t/subselect.test
@@ -4274,8 +4274,7 @@ SELECT * FROM (
# This was not allowed previously. Possibly, it should be allowed on the future. 
  # For now, the intent is to keep the fix as non-intrusive as possible.
---error ER_PARSE_ERROR
-SELECT * FROM ((SELECT 1 a) UNION SELECT 1 a);
+SELECT * FROM ((SELECT 1 a) UNION SELECT 1 a) q;
  SELECT * FROM (SELECT 1 a UNION (SELECT 1 a)) alias;
  SELECT * FROM (SELECT 1 UNION SELECT 1) t1a;
  --error ER_PARSE_ERROR
@@ -4310,7 +4309,7 @@ SELECT * FROM (SELECT 1 a UNION SELECT 1 a ORDER BY a LIMIT 1) t1a;
  # aliases after.
  #
  SELECT * FROM t1 JOIN  (SELECT 1 UNION SELECT 1) alias ON 1;
---error ER_PARSE_ERROR
+--error ER_DERIVED_MUST_HAVE_ALIAS
  SELECT * FROM t1 JOIN ((SELECT 1 UNION SELECT 1)) ON 1;
  --error ER_PARSE_ERROR
  SELECT * FROM t1 JOIN  (t1 t1a UNION SELECT 1)  ON 1;
@@ -4402,8 +4401,9 @@ SELECT ((SELECT 1 UNION SELECT 1 UNION SELECT 1));
--error ER_PARSE_ERROR 
  SELECT * FROM ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) );
---error ER_PARSE_ERROR
+--error ER_DERIVED_MUST_HAVE_ALIAS
  SELECT * FROM ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1 );
+SELECT * FROM ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1 ) a;
  SELECT * FROM ( SELECT 1 UNION SELECT 1 UNION SELECT 1 ) a;
--error ER_PARSE_ERROR 
diff --git a/mysql-test/t/union.test b/mysql-test/t/union.test
index b8b040b..56fb2e3 100644
--- a/mysql-test/t/union.test
+++ b/mysql-test/t/union.test
@@ -26,8 +26,12 @@ select 't1',b,count(*) from t1 group by b UNION select 't2',b,count(*) from t2 g
  --error 1250
  (select a,b from t1 limit 2)  union all (select a,b from t2 order by a limit 1) order by t1.b;
  explain extended (select a,b from t1 limit 2)  union all (select a,b from t2 order by a limit 1) order by b desc;
+select count(*) from (
+(select                      a,b from t1 limit 2)  union all (select a,b from t2 order by a)) q;
  (select sql_calc_found_rows  a,b from t1 limit 2)  union all (select a,b from t2 order by a) limit 2;
  select found_rows();
+select count(*) from (
+select                      a,b from t1  union all select a,b from t2) q;
  select sql_calc_found_rows  a,b from t1  union all select a,b from t2 limit 2;
  select found_rows();
@@ -206,18 +210,30 @@ create table t2 (a int); 
  insert into t2 values (3),(4),(5);
# Test global limits 
+SELECT COUNT(*) FROM (
+(SELECT                     * FROM t1) UNION all (SELECT * FROM t2)) q;
  (SELECT SQL_CALC_FOUND_ROWS * FROM t1) UNION all (SELECT * FROM t2) LIMIT 1;
  select found_rows();
+SELECT COUNT(*) FROM (
+(SELECT                     * FROM t1 LIMIT 1) UNION all (SELECT * FROM t2)) q;
  (SELECT SQL_CALC_FOUND_ROWS * FROM t1 LIMIT 1) UNION all (SELECT * FROM t2) LIMIT 2;
  select found_rows();
# Test cases where found_rows() should return number of returned rows 
+SELECT COUNT(*) FROM (
+(SELECT                     * FROM t1 LIMIT 1) UNION all (SELECT * FROM t2)) q;
  (SELECT SQL_CALC_FOUND_ROWS * FROM t1 LIMIT 1) UNION all (SELECT * FROM t2);
  select found_rows();
+SELECT COUNT(*) FROM (
+(SELECT                     * FROM t1) UNION all (SELECT * FROM t2 LIMIT 1)) q;
  (SELECT SQL_CALC_FOUND_ROWS * FROM t1) UNION all (SELECT * FROM t2 LIMIT 1);
  select found_rows();
  (SELECT SQL_CALC_FOUND_ROWS * FROM t1 LIMIT 1) UNION SELECT * FROM t2 LIMIT 1;
  select found_rows();
+SELECT COUNT(*) FROM (
+(SELECT                     * FROM t1 LIMIT 1) UNION SELECT * FROM t2) q;
+(SELECT SQL_CALC_FOUND_ROWS * FROM t1 LIMIT 1) UNION SELECT * FROM t2 LIMIT 1;
+select found_rows();
# In these case found_rows() should work 
  --error ER_WRONG_USAGE
@@ -226,20 +242,41 @@ SELECT SQL_CALC_FOUND_ROWS * FROM t1 UNION all SELECT * FROM t2 LIMIT 2;
  select found_rows();
# The following examples will not be exact 
+SELECT COUNT(*) FROM (
+SELECT                     * FROM t1 UNION SELECT * FROM t2) q;
  SELECT SQL_CALC_FOUND_ROWS * FROM t1 UNION SELECT * FROM t2 LIMIT 2;
+SELECT COUNT(*) FROM (
+(SELECT                     * FROM t1 LIMIT 1) UNION all SELECT * FROM t2) q;
+(SELECT SQL_CALC_FOUND_ROWS * FROM t1 LIMIT 1) UNION all SELECT * FROM t2 LIMIT 2;
  select found_rows();
+SELECT COUNT(*) FROM (
+SELECT                     * FROM t1 UNION all SELECT * FROM t2) q;
  SELECT SQL_CALC_FOUND_ROWS * FROM t1 UNION SELECT * FROM t2 LIMIT 100;
  select found_rows();
  --error ER_WRONG_USAGE
  SELECT SQL_CALC_FOUND_ROWS * FROM t1 LIMIT 100 UNION SELECT * FROM t2;
+SELECT COUNT(*) FROM (
+(SELECT                     * FROM t1 LIMIT 100) UNION SELECT * FROM t2) q;
+(SELECT SQL_CALC_FOUND_ROWS * FROM t1 LIMIT 100) UNION SELECT * FROM t2;
  --error ER_WRONG_USAGE
  SELECT SQL_CALC_FOUND_ROWS * FROM t1 LIMIT 1 UNION SELECT * FROM t2;
+SELECT COUNT(*) FROM (
+(SELECT                     * FROM t1 LIMIT 1) UNION SELECT * FROM t2) q;
+(SELECT SQL_CALC_FOUND_ROWS * FROM t1 LIMIT 1) UNION SELECT * FROM t2;
  --error ER_WRONG_USAGE
  SELECT SQL_CALC_FOUND_ROWS * FROM t1 LIMIT 1 UNION SELECT * FROM t2 LIMIT 2;
+SELECT COUNT(*) FROM (
+(SELECT                     * FROM t1 LIMIT 1) UNION SELECT * FROM t2) q;
+(SELECT SQL_CALC_FOUND_ROWS * FROM t1 LIMIT 1) UNION SELECT * FROM t2 LIMIT 2;
  SELECT SQL_CALC_FOUND_ROWS * FROM t1 UNION SELECT * FROM t2 LIMIT 2,2;
  select found_rows();
+SELECT COUNT(*) FROM (
+SELECT                     * FROM t1 UNION SELECT * FROM t2) q;
  --error ER_WRONG_USAGE
  SELECT SQL_CALC_FOUND_ROWS * FROM t1 limit 2,2 UNION SELECT * FROM t2;
+SELECT COUNT(*) FROM (
+(SELECT                     * FROM t1 limit 2,2) UNION SELECT * FROM t2) q;
+(SELECT SQL_CALC_FOUND_ROWS * FROM t1 limit 2,2) UNION SELECT * FROM t2;
# Test some limits with ORDER BY 
  SELECT * FROM t1 UNION SELECT * FROM t2 ORDER BY a desc LIMIT 1;

Regards,
Sergei

Follow ups

References

  Thread Previous • Date Previous • • Thread Next