Hi, Sanja!
On Aug 31, sanja(a)mariadb.com 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.
>
> 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.
> +
> +
> /*
> ** 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?
> 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?
> 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?
> 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?
> + {
> + 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.
> }
> ;
>
> @@ -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
