bk commit into 5.0 tree (bell:1.1961) BUG#13343

[sanja]

Below is the list of changes that have just been committed into a local
5.0 repository of bell. When bell 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
  1.1961 05/11/16 14:09:06 bell@stripped +5 -0
  Issuing error about presence of commit/rollback statements in stored functions and
triggers added to SP parsing procedure (BUG#13627)
  
  The crash mentioned in original bug report is already prevented by one
  of previous patches (fix for bug #13343 "CREATE|etc TRIGGER|VIEW|USER
  don't commit the transaction (inconsistency)"), this patch only improve
  error returning.

  sql/sql_yacc.yy
    1.441 05/11/16 14:08:54 bell@stripped +2 -55
    Removed commit/rollback-statement-present errors spread by this file, only one check
left which check flags of a SP

  sql/sp_head.h
    1.75 05/11/16 14:08:54 bell@stripped +11 -2
    The new flag about commit/rollback presence added
    A comment fixed

  sql/sp_head.cc
    1.196 05/11/16 14:08:54 bell@stripped +39 -0
    We set the new flag about commit/rollback statements presence

  mysql-test/t/sp-error.test
    1.94 05/11/16 14:08:54 bell@stripped +138 -0
    Test that statements which implicitly commit transaction

  mysql-test/r/sp-error.result
    1.90 05/11/16 14:08:54 bell@stripped +103 -0
    Test that statements which implicitly commit transaction

# 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:	bell
# Host:	sanja.is.com.ua
# Root:	/home/bell/mysql/bk/work-bug5-5.0

--- 1.440/sql/sql_yacc.yy	2005-11-10 21:24:54 +02:00
+++ 1.441/sql/sql_yacc.yy	2005-11-16 14:08:54 +02:00
@@ -1159,11 +1159,6 @@
 	| CREATE opt_unique_or_fulltext INDEX_SYM ident key_alg ON table_ident
 	  {
 	    LEX *lex=Lex;
-            if (lex->sphead && lex->sphead->m_type !=
TYPE_ENUM_PROCEDURE)
-            {
-              my_error(ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG, MYF(0));
-              YYABORT;
-            }
 	    lex->sql_command= SQLCOM_CREATE_INDEX;
 	    if (!lex->current_select->add_table_to_list(lex->thd, $7, NULL,
 							TL_OPTION_UPDATING))
@@ -3299,11 +3294,6 @@
 	{
 	  THD *thd= YYTHD;
 	  LEX *lex= thd->lex;
-          if (lex->sphead && lex->sphead->m_type != TYPE_ENUM_PROCEDURE)
-          {
-            my_error(ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG, MYF(0));
-            YYABORT;
-          }
 	  lex->sql_command= SQLCOM_ALTER_TABLE;
 	  lex->name= 0;
 	  lex->duplicates= DUP_ERROR; 
@@ -3614,11 +3604,6 @@
 	START_SYM TRANSACTION_SYM start_transaction_opts
         {
           LEX *lex= Lex;
-          if (lex->sphead && lex->sphead->m_type != TYPE_ENUM_PROCEDURE)
-          {
-            my_error(ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG, MYF(0));
-            YYABORT;
-          }
           lex->sql_command= SQLCOM_BEGIN;
           lex->start_transaction_opt= $3;
         }
@@ -3803,13 +3788,7 @@
 rename:
 	RENAME table_or_tables
 	{
-          LEX *lex= Lex;
-          if (lex->sphead && lex->sphead->m_type != TYPE_ENUM_PROCEDURE)
-          {
-            my_error(ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG, MYF(0));
-            YYABORT;
-          }
-          lex->sql_command=SQLCOM_RENAME_TABLE;
+          Lex->sql_command= SQLCOM_RENAME_TABLE;
 	}
 	table_to_table_list
 	{}
@@ -5946,21 +5925,10 @@
 	  lex->sql_command = SQLCOM_DROP_TABLE;
 	  lex->drop_temporary= $2;
 	  lex->drop_if_exists= $4;
-          if (!lex->drop_temporary && lex->sphead &&
-              lex->sphead->m_type != TYPE_ENUM_PROCEDURE)
-          {
-            my_error(ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG, MYF(0));
-            YYABORT;
-          }
 	}
 	| DROP INDEX_SYM ident ON table_ident {}
 	  {
 	     LEX *lex=Lex;
-             if (lex->sphead && lex->sphead->m_type !=
TYPE_ENUM_PROCEDURE)
-             {
-               my_error(ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG, MYF(0));
-               YYABORT;
-             }
 	     lex->sql_command= SQLCOM_DROP_INDEX;
 	     lex->alter_info.drop_list.empty();
 	     lex->alter_info.drop_list.push_back(new Alter_drop(Alter_drop::KEY,
@@ -6006,13 +5974,7 @@
           }
 	| DROP VIEW_SYM if_exists table_list opt_restrict
 	  {
-	    THD *thd= YYTHD;
-	    LEX *lex= thd->lex;
-            if (lex->sphead && lex->sphead->m_type !=
TYPE_ENUM_PROCEDURE)
-            {
-              my_error(ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG, MYF(0));
-              YYABORT;
-            }
+	    LEX *lex= Lex;
 	    lex->sql_command= SQLCOM_DROP_VIEW;
 	    lex->drop_if_exists= $3;
 	  }
@@ -8652,11 +8614,6 @@
 	BEGIN_SYM  
         {
 	  LEX *lex=Lex;
-          if (lex->sphead && lex->sphead->m_type != TYPE_ENUM_PROCEDURE)
-          {
-            my_error(ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG, MYF(0));
-            YYABORT;
-          }
           lex->sql_command = SQLCOM_BEGIN;
           lex->start_transaction_opt= 0;
         }
@@ -8689,11 +8646,6 @@
 	COMMIT_SYM opt_work opt_chain opt_release
 	{
 	  LEX *lex=Lex;
-          if (lex->sphead && lex->sphead->m_type != TYPE_ENUM_PROCEDURE)
-          {
-            my_error(ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG, MYF(0));
-            YYABORT;
-          }
 	  lex->sql_command= SQLCOM_COMMIT;
 	  lex->tx_chain= $3; 
 	  lex->tx_release= $4;
@@ -8704,11 +8656,6 @@
 	ROLLBACK_SYM opt_work opt_chain opt_release
 	{
 	  LEX *lex=Lex;
-          if (lex->sphead && lex->sphead->m_type != TYPE_ENUM_PROCEDURE)
-          {
-            my_error(ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG, MYF(0));
-            YYABORT;
-          }
 	  lex->sql_command= SQLCOM_ROLLBACK;
 	  lex->tx_chain= $3; 
 	  lex->tx_release= $4;

--- 1.89/mysql-test/r/sp-error.result	2005-11-11 20:01:37 +02:00
+++ 1.90/mysql-test/r/sp-error.result	2005-11-16 14:08:54 +02:00
@@ -872,6 +872,109 @@
 foo4
 drop procedure bug13510_3|
 drop procedure bug13510_4|
+drop function if exists bug_13627_f|
+CREATE TABLE t1 (a int)|
+CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN DROP TRIGGER test1; END |
+ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.
+CREATE FUNCTION bug_13627_f() returns int BEGIN DROP TRIGGER test1; return 1; END |
+ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.
+CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN load table t1 from master; END
|
+ERROR 0A000: LOAD TABLE is not allowed in stored procedures
+CREATE FUNCTION bug_13627_f() returns int BEGIN load table t1 from master; return 1; END
|
+ERROR 0A000: LOAD TABLE is not allowed in stored procedures
+CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN create table t2 (a int); END |
+ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.
+CREATE FUNCTION bug_13627_f() returns int BEGIN create table t2 (a int); return 1; END |
+ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.
+CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN create index t1_i on t1 (a);
END |
+ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.
+CREATE FUNCTION bug_13627_f() returns int BEGIN create index t1_i on t1 (a); return 1;
END |
+ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.
+CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN alter table t1 add column  b
int; END |
+ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.
+CREATE FUNCTION bug_13627_f() returns int BEGIN alter table t1 add column  b int; return
1; END |
+ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.
+CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN rename table t1 to t2; END |
+ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.
+CREATE FUNCTION bug_13627_f() returns int BEGIN rename table t1 to t2; return 1; END |
+ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.
+CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN truncate table t1; END |
+ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.
+CREATE FUNCTION bug_13627_f() returns int BEGIN truncate table t1; return 1; END |
+ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.
+CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN drop table t1; END |
+ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.
+CREATE FUNCTION bug_13627_f() returns int BEGIN drop table t1; return 1; END |
+ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.
+CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN drop index t1_i on t1; END |
+ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.
+CREATE FUNCTION bug_13627_f() returns int BEGIN drop index t1_i on t1; return 1; END |
+ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.
+CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN unlock tables; END |
+ERROR 0A000: UNLOCK is not allowed in stored procedures
+CREATE FUNCTION bug_13627_f() returns int BEGIN unlock tables; return 1; END |
+ERROR 0A000: UNLOCK is not allowed in stored procedures
+CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN LOCK TABLE t1 READ; END |
+ERROR 0A000: LOCK is not allowed in stored procedures
+CREATE FUNCTION bug_13627_f() returns int BEGIN LOCK TABLE t1 READ; return 1; END |
+ERROR 0A000: LOCK is not allowed in stored procedures
+CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN create database mysqltest; END
|
+ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.
+CREATE FUNCTION bug_13627_f() returns int BEGIN create database mysqltest; return 1; END
|
+ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.
+CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN drop database mysqltest; END |
+ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.
+CREATE FUNCTION bug_13627_f() returns int BEGIN drop database mysqltest; return 1; END |
+ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.
+CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN create user 'mysqltest_1'; END
|
+ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.
+CREATE FUNCTION bug_13627_f() returns int BEGIN create user 'mysqltest_1'; return 1; END
|
+ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.
+CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN drop user 'mysqltest_1'; END |
+ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.
+CREATE FUNCTION bug_13627_f() returns int BEGIN drop user 'mysqltest_1'; return 1; END |
+ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.
+CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN rename user 'mysqltest_2' to
'mysqltest_1'; END |
+ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.
+CREATE FUNCTION bug_13627_f() returns int BEGIN rename user 'mysqltest_2' to
'mysqltest_1'; return 1; END |
+ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.
+CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN create view v1 as select 1; END
|
+ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.
+CREATE FUNCTION bug_13627_f() returns int BEGIN create view v1 as select 1; return 1; END
|
+ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.
+CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN alter view v1 as select 1; END
|
+ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.
+CREATE FUNCTION bug_13627_f() returns int BEGIN alter view v1 as select 1; return 1; END
|
+ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.
+CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN drop view v1; END |
+ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.
+CREATE FUNCTION bug_13627_f() returns int BEGIN drop view v1; return 1; END |
+ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.
+CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN create trigger tr2 before
insert on t1 for each row do select 1; END |
+ERROR 2F003: Can't create a TRIGGER from within another stored routine
+CREATE FUNCTION bug_13627_f() returns int BEGIN create trigger tr2 before insert on t1
for each row do select 1; return 1; END |
+ERROR 2F003: Can't create a TRIGGER from within another stored routine
+CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN drop function bug_13627_f; END
|
+ERROR HY000: Can't drop or alter a FUNCTION from within another stored routine
+CREATE FUNCTION bug_13627_f() returns int BEGIN drop function bug_13627_f; return 1; END
|
+ERROR HY000: Can't drop or alter a FUNCTION from within another stored routine
+CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN create function f2 () returns
int return 1; END |
+ERROR 2F003: Can't create a FUNCTION from within another stored routine
+CREATE FUNCTION bug_13627_f() returns int BEGIN create function f2 () returns int return
1; return 1; END |
+ERROR 2F003: Can't create a FUNCTION from within another stored routine
+CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW
+BEGIN
+CREATE TEMPORARY TABLE t2 (a int);
+DROP TEMPORARY TABLE t2;
+END |
+CREATE FUNCTION bug_13627_f() returns int
+BEGIN
+CREATE TEMPORARY TABLE t2 (a int);
+DROP TEMPORARY TABLE t2;
+return 1;
+END |
+drop table t1|
+drop function bug_13627_f|
 create database mysqltest1;
 use mysqltest1;
 drop database mysqltest1;

--- 1.93/mysql-test/t/sp-error.test	2005-11-11 20:01:37 +02:00
+++ 1.94/mysql-test/t/sp-error.test	2005-11-16 14:08:54 +02:00
@@ -1263,6 +1263,144 @@
 
 drop procedure bug13510_3|
 drop procedure bug13510_4|
+
+
+#
+# Test that statements which implicitly commit transaction are prohibited
+# in stored function and triggers. Attempt to create function or trigger
+# containing such statement should produce error (includes test for
+# bug #13627).
+#
+--disable_warnings
+drop function if exists bug_13627_f|
+--enable_warnings
+
+CREATE TABLE t1 (a int)|
+-- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
+CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN DROP TRIGGER test1; END |
+-- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
+CREATE FUNCTION bug_13627_f() returns int BEGIN DROP TRIGGER test1; return 1; END |
+
+-- error ER_SP_BADSTATEMENT
+CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN load table t1 from master; END
|
+-- error ER_SP_BADSTATEMENT
+CREATE FUNCTION bug_13627_f() returns int BEGIN load table t1 from master; return 1; END
|
+
+-- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
+CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN create table t2 (a int); END |
+-- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
+CREATE FUNCTION bug_13627_f() returns int BEGIN create table t2 (a int); return 1; END |
+
+-- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
+CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN create index t1_i on t1 (a);
END |
+-- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
+CREATE FUNCTION bug_13627_f() returns int BEGIN create index t1_i on t1 (a); return 1;
END |
+
+-- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
+CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN alter table t1 add column  b
int; END |
+-- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
+CREATE FUNCTION bug_13627_f() returns int BEGIN alter table t1 add column  b int; return
1; END |
+
+-- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
+CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN rename table t1 to t2; END |
+-- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
+CREATE FUNCTION bug_13627_f() returns int BEGIN rename table t1 to t2; return 1; END |
+
+-- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
+CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN truncate table t1; END |
+-- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
+CREATE FUNCTION bug_13627_f() returns int BEGIN truncate table t1; return 1; END |
+
+-- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
+CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN drop table t1; END |
+-- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
+CREATE FUNCTION bug_13627_f() returns int BEGIN drop table t1; return 1; END |
+
+-- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
+CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN drop index t1_i on t1; END |
+-- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
+CREATE FUNCTION bug_13627_f() returns int BEGIN drop index t1_i on t1; return 1; END |
+
+-- error ER_SP_BADSTATEMENT
+CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN unlock tables; END |
+-- error ER_SP_BADSTATEMENT
+CREATE FUNCTION bug_13627_f() returns int BEGIN unlock tables; return 1; END |
+
+-- error ER_SP_BADSTATEMENT
+CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN LOCK TABLE t1 READ; END |
+-- error ER_SP_BADSTATEMENT
+CREATE FUNCTION bug_13627_f() returns int BEGIN LOCK TABLE t1 READ; return 1; END |
+
+-- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
+CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN create database mysqltest; END
|
+-- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
+CREATE FUNCTION bug_13627_f() returns int BEGIN create database mysqltest; return 1; END
|
+
+-- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
+CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN drop database mysqltest; END |
+-- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
+CREATE FUNCTION bug_13627_f() returns int BEGIN drop database mysqltest; return 1; END |
+
+-- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
+CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN create user 'mysqltest_1'; END
|
+-- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
+CREATE FUNCTION bug_13627_f() returns int BEGIN create user 'mysqltest_1'; return 1; END
|
+
+-- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
+CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN drop user 'mysqltest_1'; END |
+-- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
+CREATE FUNCTION bug_13627_f() returns int BEGIN drop user 'mysqltest_1'; return 1; END |
+
+-- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
+CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN rename user 'mysqltest_2' to
'mysqltest_1'; END |
+-- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
+CREATE FUNCTION bug_13627_f() returns int BEGIN rename user 'mysqltest_2' to
'mysqltest_1'; return 1; END |
+
+-- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
+CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN create view v1 as select 1; END
|
+-- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
+CREATE FUNCTION bug_13627_f() returns int BEGIN create view v1 as select 1; return 1; END
|
+
+-- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
+CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN alter view v1 as select 1; END
|
+-- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
+CREATE FUNCTION bug_13627_f() returns int BEGIN alter view v1 as select 1; return 1; END
|
+
+-- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
+CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN drop view v1; END |
+-- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
+CREATE FUNCTION bug_13627_f() returns int BEGIN drop view v1; return 1; END |
+
+-- error ER_SP_NO_RECURSIVE_CREATE
+CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN create trigger tr2 before
insert on t1 for each row do select 1; END |
+-- error ER_SP_NO_RECURSIVE_CREATE
+CREATE FUNCTION bug_13627_f() returns int BEGIN create trigger tr2 before insert on t1
for each row do select 1; return 1; END |
+
+-- error ER_SP_NO_DROP_SP
+CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN drop function bug_13627_f; END
|
+-- error ER_SP_NO_DROP_SP
+CREATE FUNCTION bug_13627_f() returns int BEGIN drop function bug_13627_f; return 1; END
|
+
+-- error ER_SP_NO_RECURSIVE_CREATE
+CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN create function f2 () returns
int return 1; END |
+-- error ER_SP_NO_RECURSIVE_CREATE
+CREATE FUNCTION bug_13627_f() returns int BEGIN create function f2 () returns int return
1; return 1; END |
+
+CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW
+  BEGIN
+    CREATE TEMPORARY TABLE t2 (a int);
+    DROP TEMPORARY TABLE t2;
+  END |
+CREATE FUNCTION bug_13627_f() returns int
+  BEGIN
+    CREATE TEMPORARY TABLE t2 (a int);
+    DROP TEMPORARY TABLE t2;
+    return 1;
+  END |
+
+drop table t1|
+drop function bug_13627_f|
+
 delimiter ;|
 
 #

--- 1.195/sql/sp_head.cc	2005-11-11 19:12:30 +02:00
+++ 1.196/sql/sp_head.cc	2005-11-16 14:08:54 +02:00
@@ -120,6 +120,45 @@
   case SQLCOM_DEALLOCATE_PREPARE:
     flags= sp_head::CONTAINS_DYNAMIC_SQL;
     break;
+  case SQLCOM_CREATE_TABLE:
+    if (lex->create_info.options & HA_LEX_CREATE_TMP_TABLE)
+      flags= 0;
+    else
+      flags= sp_head::HAS_COMMIT_OR_ROLLBACK;
+    break;
+  case SQLCOM_DROP_TABLE:
+    if (lex->drop_temporary)
+      flags= 0;
+    else
+      flags= sp_head::HAS_COMMIT_OR_ROLLBACK;
+    break;
+  case SQLCOM_CREATE_INDEX:
+  case SQLCOM_CREATE_DB:
+  case SQLCOM_CREATE_VIEW:
+  case SQLCOM_CREATE_TRIGGER:
+  case SQLCOM_CREATE_USER:
+  case SQLCOM_ALTER_TABLE:
+  case SQLCOM_BEGIN:
+  case SQLCOM_RENAME_TABLE:
+  case SQLCOM_RENAME_USER:
+  case SQLCOM_DROP_INDEX:
+  case SQLCOM_DROP_DB:
+  case SQLCOM_DROP_USER:
+  case SQLCOM_DROP_VIEW:
+  case SQLCOM_DROP_TRIGGER:
+  case SQLCOM_TRUNCATE:
+  case SQLCOM_COMMIT:
+  case SQLCOM_ROLLBACK:
+  case SQLCOM_LOAD_MASTER_DATA:
+  case SQLCOM_LOCK_TABLES:
+  case SQLCOM_CREATE_PROCEDURE:
+  case SQLCOM_CREATE_SPFUNCTION:
+  case SQLCOM_ALTER_PROCEDURE:
+  case SQLCOM_ALTER_FUNCTION:
+  case SQLCOM_DROP_PROCEDURE:
+  case SQLCOM_DROP_FUNCTION:
+    flags= sp_head::HAS_COMMIT_OR_ROLLBACK;
+    break;
   default:
     flags= 0;
     break;

--- 1.74/sql/sp_head.h	2005-11-10 21:24:52 +02:00
+++ 1.75/sql/sp_head.h	2005-11-16 14:08:54 +02:00
@@ -115,10 +115,13 @@
     MULTI_RESULTS= 8,           // Is set if a procedure with SELECT(s)
     CONTAINS_DYNAMIC_SQL= 16,   // Is set if a procedure with PREPARE/EXECUTE
     IS_INVOKED= 32,             // Is set if this sp_head is being used
-    HAS_SET_AUTOCOMMIT_STMT = 64 // Is set if a procedure with 'set autocommit'
+    HAS_SET_AUTOCOMMIT_STMT= 64,// Is set if a procedure with 'set autocommit'
+    /* Is set if a procedure with COMMIT (implicit or explicit) | ROLLBACK */
+    HAS_COMMIT_OR_ROLLBACK= 128
   };
 
-  int m_type;			// TYPE_ENUM_FUNCTION or TYPE_ENUM_PROCEDURE
+  /* TYPE_ENUM_FUNCTION, TYPE_ENUM_PROCEDURE or TYPE_ENUM_TRIGGER */
+  int m_type;
   uint m_flags;                 // Boolean attributes of a stored routine
   enum enum_field_types m_returns; // For FUNCTIONs only
   Field::geometry_type m_geom_returns;
@@ -292,6 +295,12 @@
       my_error(ER_SP_NO_RETSET, MYF(0), where);
     else if (m_flags & HAS_SET_AUTOCOMMIT_STMT)
       my_error(ER_SP_CANT_SET_AUTOCOMMIT, MYF(0));
+    else if (m_type != TYPE_ENUM_PROCEDURE &&
+             (m_flags & sp_head::HAS_COMMIT_OR_ROLLBACK))
+    {
+      my_error(ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG, MYF(0));
+      return TRUE;
+    }
     return test(m_flags &
 		(CONTAINS_DYNAMIC_SQL|MULTI_RESULTS|HAS_SET_AUTOCOMMIT_STMT));
   }