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