MySQL Lists are EOL. Please join:

List:Commits« Previous MessageNext Message »
From:kroki Date:July 21 2006 4:13pm
Subject:bk commit into 5.0 tree (kroki:1.2237) BUG#20953
View as plain text  
Below is the list of changes that have just been committed into a local
5.0 repository of tomash. When tomash does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html

ChangeSet@stripped, 2006-07-21 20:13:42+04:00, kroki@stripped +8 -0
  BUG#20953: create proc with a create view that uses local vars/params
             should fail to create
  
  The problem was that this type of errors was checked during view
  creation, which doesn't happen when CREATE VIEW is a statement of
  a created stored routine.
  
  The solution is to perform the checks at parse time.
  
  The side effect of this change is that if the user already have
  such bogus routines, it will now get a error when trying to do
    SHOW CREATE PROCEDURE proc;
  (and some other) and when trying to execute such routine he will get
    ERROR 1457 (HY000): Failed to load routine test.p5. The table mysql.proc is missing, corrupt, or contains bad data (internal code -6)
  However there should be very few such users (if any), and they may
  (and should) drop these bogus routines.

  mysql-test/r/sp-error.result@stripped, 2006-07-21 20:13:37+04:00, kroki@stripped +22 -0
    Add result for bug#20953: create proc with a create view that uses
    local vars/params should fail to create.

  mysql-test/r/view.result@stripped, 2006-07-21 20:13:38+04:00, kroki@stripped +5 -5
    Update results.

  mysql-test/t/sp-error.test@stripped, 2006-07-21 20:13:38+04:00, kroki@stripped +40 -0
    Add test case for bug#20953: create proc with a create view that uses
    local vars/params should fail to create.

  mysql-test/t/view.test@stripped, 2006-07-21 20:13:38+04:00, kroki@stripped +6 -14
    Add second test for variable in a view.
    Remove SP variable in a view test, as it tests wrong behaviour.
    Add test for derived table in a view.

  sql/sql_lex.cc@stripped, 2006-07-21 20:13:38+04:00, kroki@stripped +0 -1
    Remove LEX::variables_used.

  sql/sql_lex.h@stripped, 2006-07-21 20:13:38+04:00, kroki@stripped +0 -1
    Remove LEX::variables_used.

  sql/sql_view.cc@stripped, 2006-07-21 20:13:38+04:00, kroki@stripped +0 -20
    Move error checking to sql/sql_yacc.yy.

  sql/sql_yacc.yy@stripped, 2006-07-21 20:13:38+04:00, kroki@stripped +56 -12
    Check for disallowed syntax in a CREATE VIEW at parse time to rise a
    error when it is used inside CREATE PROCEDURE and CREATE FUNCTION, as
    well as by itself.
    Remove LEX::variables_used.

# This is a BitKeeper patch.  What follows are the unified diffs for the
# set of deltas contained in the patch.  The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User:	kroki
# Host:	moonlight.intranet
# Root:	/home/tomash/src/mysql_ab/mysql-5.0-bug20953

--- 1.192/sql/sql_lex.cc	2006-07-21 20:13:51 +04:00
+++ 1.193/sql/sql_lex.cc	2006-07-21 20:13:51 +04:00
@@ -150,7 +150,6 @@ void lex_start(THD *thd, uchar *buf,uint
   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;

--- 1.222/sql/sql_lex.h	2006-07-21 20:13:51 +04:00
+++ 1.223/sql/sql_lex.h	2006-07-21 20:13:51 +04:00
@@ -944,7 +944,6 @@ typedef struct st_lex : public Query_tab
   bool stmt_prepare_mode;
   bool safe_to_cache_query;
   bool subqueries, ignore;
-  bool variables_used;
   ALTER_INFO alter_info;
   /* Prepared statements SQL syntax:*/
   LEX_STRING prepared_stmt_name; /* Statement name (in all queries) */

--- 1.474/sql/sql_yacc.yy	2006-07-21 20:13:51 +04:00
+++ 1.475/sql/sql_yacc.yy	2006-07-21 20:13:51 +04:00
@@ -4283,29 +4283,40 @@ simple_expr:
 	| param_marker
 	| '@' ident_or_text SET_VAR expr
 	  {
-	    $$= new Item_func_set_user_var($2,$4);
 	    LEX *lex= Lex;
+            if (lex->sql_command == SQLCOM_CREATE_VIEW)
+            {
+              my_error(ER_VIEW_SELECT_VARIABLE, MYF(0));
+              YYABORT;
+            }
+	    $$= new Item_func_set_user_var($2,$4);
 	    lex->uncacheable(UNCACHEABLE_RAND);
-	    lex->variables_used= 1;
 	  }
 	| '@' ident_or_text
 	  {
-	    $$= new Item_func_get_user_var($2);
 	    LEX *lex= Lex;
+            if (lex->sql_command == SQLCOM_CREATE_VIEW)
+            {
+              my_error(ER_VIEW_SELECT_VARIABLE, MYF(0));
+              YYABORT;
+            }
+	    $$= new Item_func_get_user_var($2);
 	    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 (Lex->sql_command == SQLCOM_CREATE_VIEW)
+            {
+              my_error(ER_VIEW_SELECT_VARIABLE, MYF(0));
+              YYABORT;
+            }
 	    if (!($$= get_system_var(YYTHD, $3, $4, $5)))
 	      YYABORT;
-	    Lex->variables_used= 1;
 	  }
 	| sum_expr
 	| simple_expr OR_OR_SYM simple_expr
@@ -5428,6 +5439,13 @@ select_derived_init:
           SELECT_SYM
           {
             LEX *lex= Lex;
+
+            if (lex->sql_command == SQLCOM_CREATE_VIEW)
+            {
+              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))
@@ -5787,6 +5805,13 @@ procedure_clause:
 	| PROCEDURE ident			/* Procedure name */
 	  {
 	    LEX *lex=Lex;
+
+            if (lex->sql_command == SQLCOM_CREATE_VIEW)
+            {
+              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");
@@ -5886,28 +5911,42 @@ select_var_ident:  
            ;
 
 into:
-        INTO OUTFILE TEXT_STRING_filesystem
+        INTO
+        {
+          LEX *lex= Lex;
+
+          if (lex->sql_command == SQLCOM_CREATE_VIEW)
+          {
+            my_error(ER_VIEW_SELECT_CLAUSE, MYF(0), "INTO");
+            YYABORT;
+          }
+        }
+        into2
+        ;
+
+into2:
+        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);
 	}
@@ -7188,6 +7227,12 @@ simple_ident:
 	  if (spc && (spv = spc->find_variable(&$1)))
 	  {
             /* We're compiling a stored procedure and found a variable */
+            if (lex->sql_command == SQLCOM_CREATE_VIEW)
+            {
+              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 - 
@@ -7197,7 +7242,6 @@ simple_ident:
               splocal->m_sp= lex->sphead;
 #endif
 	    $$ = (Item*) splocal;
-            lex->variables_used= 1;
 	    lex->safe_to_cache_query=0;
 	  }
 	  else

--- 1.162/mysql-test/r/view.result	2006-07-21 20:13:51 +04:00
+++ 1.163/mysql-test/r/view.result	2006-07-21 20:13:51 +04:00
@@ -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 i
 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 'I
 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;

--- 1.147/mysql-test/t/view.test	2006-07-21 20:13:51 +04:00
+++ 1.148/mysql-test/t/view.test	2006-07-21 20:13:51 +04:00
@@ -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 
 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;
 
 #

--- 1.89/sql/sql_view.cc	2006-07-21 20:13:52 +04:00
+++ 1.90/sql/sql_view.cc	2006-07-21 20:13:52 +04:00
@@ -186,26 +186,6 @@ 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;
-  }
-
   if (mode != VIEW_CREATE_NEW)
     sp_cache_invalidate();
 

--- 1.106/mysql-test/r/sp-error.result	2006-07-21 20:13:52 +04:00
+++ 1.107/mysql-test/r/sp-error.result	2006-07-21 20:13:52 +04:00
@@ -1174,3 +1174,25 @@ drop procedure bug15091;
 drop function if exists bug16896;
 create aggregate function bug16896() returns int return 1;
 ERROR 42000: AGGREGATE is not supported for stored functions
+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
+DROP TABLE t1;

--- 1.106/mysql-test/t/sp-error.test	2006-07-21 20:13:52 +04:00
+++ 1.107/mysql-test/t/sp-error.test	2006-07-21 20:13:52 +04:00
@@ -1706,6 +1706,46 @@ 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 ;|
+
+DROP TABLE t1;
+
+
 #
 # BUG#NNNN: New bug synopsis
 #
Thread
bk commit into 5.0 tree (kroki:1.2237) BUG#20953kroki21 Jul