Re: [Maria-developers] [Commits] 93a7c11: MDEV-8380: Subquery parse error
Hi, Sanja! On Aug 31, sanja@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
Hi, Sanja!
On Aug 31, sanja@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. They have no list of procedures (it looks like they have onlu one
On 12.09.15 21:12, Sergei Golubchik wrote: 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?
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
sorry, yes 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
Hi, Oleksandr! On Sep 14, Oleksandr Byelkin wrote:
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.
Of course. All syntax constructs are "sometimes allowed, sometimes not". But normally the parser issues the sytnax error, not the code after it.
@@ -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.
So, why the parser cannot check for duplicate INTO?
+ { + 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; + } + }
Regards, Sergei
participants (2)
-
Oleksandr Byelkin -
Sergei Golubchik