diff options
| -rw-r--r-- | mysql-test/r/sp-error.result | 24 | ||||
| -rw-r--r-- | mysql-test/r/view.result | 10 | ||||
| -rw-r--r-- | mysql-test/t/sp-error.test | 42 | ||||
| -rw-r--r-- | mysql-test/t/view.test | 20 | ||||
| -rw-r--r-- | sql/sql_lex.cc | 1 | ||||
| -rw-r--r-- | sql/sql_lex.h | 21 | ||||
| -rw-r--r-- | sql/sql_view.cc | 22 | ||||
| -rw-r--r-- | sql/sql_yacc.yy | 139 |
8 files changed, 204 insertions, 75 deletions
diff --git a/mysql-test/r/sp-error.result b/mysql-test/r/sp-error.result index 85ea624ce2f..63fd1bfff6d 100644 --- a/mysql-test/r/sp-error.result +++ b/mysql-test/r/sp-error.result @@ -1226,3 +1226,27 @@ END; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF NOT EXISTS bug14702() BEGIN END' at line 1 +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 (i INT); +CREATE PROCEDURE bug20953() CREATE VIEW v AS SELECT 1 INTO @a; +ERROR HY000: View's SELECT contains a 'INTO' clause +CREATE PROCEDURE bug20953() CREATE VIEW v AS SELECT 1 INTO DUMPFILE "file"; +ERROR HY000: View's SELECT contains a 'INTO' clause +CREATE PROCEDURE bug20953() CREATE VIEW v AS SELECT 1 INTO OUTFILE "file"; +ERROR HY000: View's SELECT contains a 'INTO' clause +CREATE PROCEDURE bug20953() +CREATE VIEW v AS SELECT i FROM t1 PROCEDURE ANALYSE(); +ERROR HY000: View's SELECT contains a 'PROCEDURE' clause +CREATE PROCEDURE bug20953() CREATE VIEW v AS SELECT 1 FROM (SELECT 1) AS d1; +ERROR HY000: View's SELECT contains a subquery in the FROM clause +CREATE PROCEDURE bug20953(i INT) CREATE VIEW v AS SELECT i; +ERROR HY000: View's SELECT contains a variable or parameter +CREATE PROCEDURE bug20953() +BEGIN +DECLARE i INT; +CREATE VIEW v AS SELECT i; +END | +ERROR HY000: View's SELECT contains a variable or parameter +PREPARE stmt FROM "CREATE VIEW v AS SELECT ?"; +ERROR HY000: View's SELECT contains a variable or parameter +DROP TABLE t1; diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result index 1b2f20702b2..13921dc88d7 100644 --- a/mysql-test/r/view.result +++ b/mysql-test/r/view.result @@ -12,6 +12,9 @@ create table t1 (a int, b int); insert into t1 values (1,2), (1,3), (2,4), (2,5), (3,10); create view v1 (c,d) as select a,b+@@global.max_user_connections from t1; ERROR HY000: View's SELECT contains a variable or parameter +create view v1 (c,d) as select a,b from t1 +where a = @@global.max_user_connections; +ERROR HY000: View's SELECT contains a variable or parameter create view v1 (c) as select b+1 from t1; select c from v1; c @@ -596,11 +599,6 @@ ERROR HY000: View 'test.v1' references invalid table(s) or column(s) or function drop view v1; create view v1 (a,a) as select 'a','a'; ERROR 42S21: Duplicate column name 'a' -drop procedure if exists p1; -create procedure p1 () begin declare v int; create view v1 as select v; end;// -call p1(); -ERROR HY000: View's SELECT contains a variable or parameter -drop procedure p1; create table t1 (col1 int,col2 char(22)); insert into t1 values(5,'Hello, world of views'); create view v1 as select * from t1; @@ -886,6 +884,8 @@ ERROR HY000: View's SELECT contains a 'INTO' clause create table t1 (a int); create view v1 as select a from t1 procedure analyse(); ERROR HY000: View's SELECT contains a 'PROCEDURE' clause +create view v1 as select 1 from (select 1) as d1; +ERROR HY000: View's SELECT contains a subquery in the FROM clause drop table t1; create table t1 (s1 int, primary key (s1)); create view v1 as select * from t1; diff --git a/mysql-test/t/sp-error.test b/mysql-test/t/sp-error.test index abb36f040d2..058c45f7f2f 100644 --- a/mysql-test/t/sp-error.test +++ b/mysql-test/t/sp-error.test @@ -1745,6 +1745,48 @@ drop function if exists bug16896; create aggregate function bug16896() returns int return 1; + +# +# BUG#20953: create proc with a create view that uses local +# vars/params should fail to create +# +# See test case for what syntax is forbidden in a view. +# +--disable_warnings +DROP TABLE IF EXISTS t1; +--enable_warnings + +CREATE TABLE t1 (i INT); + +# We do not have to drop this procedure and view because they won't be +# created. +--error ER_VIEW_SELECT_CLAUSE +CREATE PROCEDURE bug20953() CREATE VIEW v AS SELECT 1 INTO @a; +--error ER_VIEW_SELECT_CLAUSE +CREATE PROCEDURE bug20953() CREATE VIEW v AS SELECT 1 INTO DUMPFILE "file"; +--error ER_VIEW_SELECT_CLAUSE +CREATE PROCEDURE bug20953() CREATE VIEW v AS SELECT 1 INTO OUTFILE "file"; +--error ER_VIEW_SELECT_CLAUSE +CREATE PROCEDURE bug20953() + CREATE VIEW v AS SELECT i FROM t1 PROCEDURE ANALYSE(); +--error ER_VIEW_SELECT_DERIVED +CREATE PROCEDURE bug20953() CREATE VIEW v AS SELECT 1 FROM (SELECT 1) AS d1; +--error ER_VIEW_SELECT_VARIABLE +CREATE PROCEDURE bug20953(i INT) CREATE VIEW v AS SELECT i; +delimiter |; +--error ER_VIEW_SELECT_VARIABLE +CREATE PROCEDURE bug20953() +BEGIN + DECLARE i INT; + CREATE VIEW v AS SELECT i; +END | +delimiter ;| +--error ER_VIEW_SELECT_VARIABLE +PREPARE stmt FROM "CREATE VIEW v AS SELECT ?"; + +DROP TABLE t1; + + # # BUG#14702: misleading error message when syntax error in CREATE # PROCEDURE diff --git a/mysql-test/t/view.test b/mysql-test/t/view.test index ded72335de4..d3dcd857e62 100644 --- a/mysql-test/t/view.test +++ b/mysql-test/t/view.test @@ -23,8 +23,11 @@ create table t1 (a int, b int); insert into t1 values (1,2), (1,3), (2,4), (2,5), (3,10); # view with variable --- error 1351 +-- error ER_VIEW_SELECT_VARIABLE create view v1 (c,d) as select a,b+@@global.max_user_connections from t1; +-- error ER_VIEW_SELECT_VARIABLE +create view v1 (c,d) as select a,b from t1 + where a = @@global.max_user_connections; # simple view create view v1 (c) as select b+1 from t1; @@ -487,19 +490,6 @@ drop view v1; create view v1 (a,a) as select 'a','a'; # -# SP variables inside view test -# ---disable_warnings -drop procedure if exists p1; ---enable_warnings -delimiter //; -create procedure p1 () begin declare v int; create view v1 as select v; end;// -delimiter ;// --- error 1351 -call p1(); -drop procedure p1; - -# # updatablity should be transitive # create table t1 (col1 int,col2 char(22)); @@ -820,6 +810,8 @@ create view v1 as select 5 into outfile 'ttt'; create table t1 (a int); -- error 1350 create view v1 as select a from t1 procedure analyse(); +-- error ER_VIEW_SELECT_DERIVED +create view v1 as select 1 from (select 1) as d1; drop table t1; # diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc index 788276ac654..405f576ac04 100644 --- a/sql/sql_lex.cc +++ b/sql/sql_lex.cc @@ -150,7 +150,6 @@ void lex_start(THD *thd, uchar *buf,uint length) lex->safe_to_cache_query= 1; lex->time_zone_tables_used= 0; lex->leaf_tables_insert= 0; - lex->variables_used= 0; lex->empty_field_list_on_rset= 0; lex->select_lex.select_number= 1; lex->next_state=MY_LEX_START; diff --git a/sql/sql_lex.h b/sql/sql_lex.h index fdf14c691e9..378f968118e 100644 --- a/sql/sql_lex.h +++ b/sql/sql_lex.h @@ -807,6 +807,25 @@ public: }; +/* + st_parsing_options contains the flags for constructions that are + allowed in the current statement. +*/ + +struct st_parsing_options +{ + bool allows_variable; + bool allows_select_into; + bool allows_select_procedure; + bool allows_derived; + + st_parsing_options() + : allows_variable(TRUE), allows_select_into(TRUE), + allows_select_procedure(TRUE), allows_derived(TRUE) + {} +}; + + /* The state of the lex parsing. This is saved in the THD struct */ typedef struct st_lex : public Query_tables_list @@ -950,7 +969,7 @@ typedef struct st_lex : public Query_tables_list bool stmt_prepare_mode; bool safe_to_cache_query; bool subqueries, ignore; - bool variables_used; + st_parsing_options parsing_options; ALTER_INFO alter_info; /* Prepared statements SQL syntax:*/ LEX_STRING prepared_stmt_name; /* Statement name (in all queries) */ diff --git a/sql/sql_view.cc b/sql/sql_view.cc index 94c5ad331dd..338db053791 100644 --- a/sql/sql_view.cc +++ b/sql/sql_view.cc @@ -236,25 +236,9 @@ bool mysql_create_view(THD *thd, bool res= FALSE; DBUG_ENTER("mysql_create_view"); - if (lex->proc_list.first || - lex->result) - { - my_error(ER_VIEW_SELECT_CLAUSE, MYF(0), (lex->result ? - "INTO" : - "PROCEDURE")); - res= TRUE; - goto err; - } - if (lex->derived_tables || - lex->variables_used || lex->param_list.elements) - { - int err= (lex->derived_tables ? - ER_VIEW_SELECT_DERIVED : - ER_VIEW_SELECT_VARIABLE); - my_message(err, ER(err), MYF(0)); - res= TRUE; - goto err; - } + /* This is ensured in the parser. */ + DBUG_ASSERT(!lex->proc_list.first && !lex->result && + !lex->param_list.elements && !lex->derived_tables); if (mode != VIEW_CREATE_NEW) { diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy index 06afbabbba4..6f24a42c07c 100644 --- a/sql/sql_yacc.yy +++ b/sql/sql_yacc.yy @@ -746,7 +746,7 @@ bool my_yyoverflow(short **a, YYSTYPE **b, ulong *yystacksize); %type <item> literal text_literal insert_ident order_ident simple_ident select_item2 expr opt_expr opt_else sum_expr in_sum_expr - bool_term bool_factor bool_test bool_pri + variable variable_aux bool_term bool_factor bool_test bool_pri predicate bit_expr bit_term bit_factor value_expr term factor table_wild simple_expr udf_expr expr_or_default set_expr_or_default interval_expr @@ -4327,32 +4327,7 @@ simple_expr: } | literal | param_marker - | '@' ident_or_text SET_VAR expr - { - $$= new Item_func_set_user_var($2,$4); - LEX *lex= Lex; - lex->uncacheable(UNCACHEABLE_RAND); - lex->variables_used= 1; - } - | '@' ident_or_text - { - $$= new Item_func_get_user_var($2); - LEX *lex= Lex; - lex->uncacheable(UNCACHEABLE_RAND); - lex->variables_used= 1; - } - | '@' '@' opt_var_ident_type ident_or_text opt_component - { - - if ($4.str && $5.str && check_reserved_words(&$4)) - { - yyerror(ER(ER_SYNTAX_ERROR)); - YYABORT; - } - if (!($$= get_system_var(YYTHD, $3, $4, $5))) - YYABORT; - Lex->variables_used= 1; - } + | variable | sum_expr | simple_expr OR_OR_SYM simple_expr { $$= new Item_func_concat($1, $3); } @@ -5062,6 +5037,46 @@ sum_expr: $5->empty(); }; +variable: + '@' + { + if (! Lex->parsing_options.allows_variable) + { + my_error(ER_VIEW_SELECT_VARIABLE, MYF(0)); + YYABORT; + } + } + variable_aux + { + $$= $3; + } + ; + +variable_aux: + ident_or_text SET_VAR expr + { + $$= new Item_func_set_user_var($1, $3); + LEX *lex= Lex; + lex->uncacheable(UNCACHEABLE_RAND); + } + | ident_or_text + { + $$= new Item_func_get_user_var($1); + LEX *lex= Lex; + lex->uncacheable(UNCACHEABLE_RAND); + } + | '@' opt_var_ident_type ident_or_text opt_component + { + if ($3.str && $4.str && check_reserved_words(&$3)) + { + yyerror(ER(ER_SYNTAX_ERROR)); + YYABORT; + } + if (!($$= get_system_var(YYTHD, $2, $3, $4))) + YYABORT; + } + ; + opt_distinct: /* empty */ { $$ = 0; } |DISTINCT { $$ = 1; }; @@ -5492,6 +5507,13 @@ select_derived_init: SELECT_SYM { LEX *lex= Lex; + + if (! lex->parsing_options.allows_derived) + { + my_error(ER_VIEW_SELECT_DERIVED, MYF(0)); + YYABORT; + } + SELECT_LEX *sel= lex->current_select; TABLE_LIST *embedding; if (!sel->embedding || sel->end_nested_join(lex->thd)) @@ -5851,6 +5873,13 @@ procedure_clause: | PROCEDURE ident /* Procedure name */ { LEX *lex=Lex; + + if (! lex->parsing_options.allows_select_procedure) + { + my_error(ER_VIEW_SELECT_CLAUSE, MYF(0), "PROCEDURE"); + YYABORT; + } + if (&lex->select_lex != lex->current_select) { my_error(ER_WRONG_USAGE, MYF(0), "PROCEDURE", "subquery"); @@ -5950,28 +5979,40 @@ select_var_ident: ; into: - INTO OUTFILE TEXT_STRING_filesystem + INTO + { + if (! Lex->parsing_options.allows_select_into) + { + my_error(ER_VIEW_SELECT_CLAUSE, MYF(0), "INTO"); + YYABORT; + } + } + into_destination + ; + +into_destination: + OUTFILE TEXT_STRING_filesystem { LEX *lex= Lex; lex->uncacheable(UNCACHEABLE_SIDEEFFECT); - if (!(lex->exchange= new sql_exchange($3.str, 0)) || + if (!(lex->exchange= new sql_exchange($2.str, 0)) || !(lex->result= new select_export(lex->exchange))) YYABORT; } opt_field_term opt_line_term - | INTO DUMPFILE TEXT_STRING_filesystem + | DUMPFILE TEXT_STRING_filesystem { LEX *lex=Lex; if (!lex->describe) { lex->uncacheable(UNCACHEABLE_SIDEEFFECT); - if (!(lex->exchange= new sql_exchange($3.str,1))) + if (!(lex->exchange= new sql_exchange($2.str,1))) YYABORT; if (!(lex->result= new select_dump(lex->exchange))) YYABORT; } } - | INTO select_var_list_init + | select_var_list_init { Lex->uncacheable(UNCACHEABLE_SIDEEFFECT); } @@ -7131,8 +7172,13 @@ param_marker: { THD *thd=YYTHD; LEX *lex= thd->lex; - Item_param *item= new Item_param((uint) (lex->tok_start - - (uchar *) thd->query)); + Item_param *item; + if (! lex->parsing_options.allows_variable) + { + my_error(ER_VIEW_SELECT_VARIABLE, MYF(0)); + YYABORT; + } + item= new Item_param((uint) (lex->tok_start - (uchar *) thd->query)); if (!($$= item) || lex->param_list.push_back(item)) { my_message(ER_OUT_OF_RESOURCES, ER(ER_OUT_OF_RESOURCES), MYF(0)); @@ -7252,6 +7298,12 @@ simple_ident: if (spc && (spv = spc->find_variable(&$1))) { /* We're compiling a stored procedure and found a variable */ + if (! lex->parsing_options.allows_variable) + { + my_error(ER_VIEW_SELECT_VARIABLE, MYF(0)); + YYABORT; + } + Item_splocal *splocal; splocal= new Item_splocal($1, spv->offset, spv->type, lex->tok_start_prev - @@ -7261,7 +7313,6 @@ simple_ident: splocal->m_sp= lex->sphead; #endif $$ = (Item*) splocal; - lex->variables_used= 1; lex->safe_to_cache_query=0; } else @@ -9109,6 +9160,24 @@ view_list: ; view_select: + { + LEX *lex= Lex; + lex->parsing_options.allows_variable= FALSE; + lex->parsing_options.allows_select_into= FALSE; + lex->parsing_options.allows_select_procedure= FALSE; + lex->parsing_options.allows_derived= FALSE; + } + view_select_aux + { + LEX *lex= Lex; + lex->parsing_options.allows_variable= TRUE; + lex->parsing_options.allows_select_into= TRUE; + lex->parsing_options.allows_select_procedure= TRUE; + lex->parsing_options.allows_derived= TRUE; + } + ; + +view_select_aux: SELECT_SYM remember_name select_init2 { THD *thd=YYTHD; |