release/src/router/mysql/mysql-test/t/sp-dynamic.test

   1 delimiter |;
   2
   3 --disable_warnings
   4 drop procedure if exists p1|
   5 drop procedure if exists p2|
   6 --enable_warnings
   7
   8 ######################################################################
   9 # Test Dynamic SQL in stored procedures. #############################
  10 ######################################################################
  11 #
  12 # A. Basics
  13 #
  14 create procedure p1()
  15 begin
  16   prepare stmt from "select 1";
  17   execute stmt;
  18   execute stmt;
  19   execute stmt;
  20   deallocate prepare stmt;
  21 end|
  22 call p1()|
  23 call p1()|
  24 call p1()|
  25 drop procedure p1|
  26 #
  27 # B. Recursion. Recusion is disabled in SP, and recursive use of PS is not
  28 # possible as well.
  29 #
  30 create procedure p1()
  31 begin
  32   execute stmt;
  33 end|
  34 prepare stmt from "call p1()"|
  35 # Allow SP resursion to be show that it has not influence here
  36 set @SAVE_SP_RECURSION_LEVELS=@@max_sp_recursion_depth|
  37 set @@max_sp_recursion_depth=100|
  38 --error ER_PS_NO_RECURSION
  39 execute stmt|
  40 --error ER_PS_NO_RECURSION
  41 execute stmt|
  42 --error ER_PS_NO_RECURSION
  43 execute stmt|
  44 --error ER_PS_NO_RECURSION
  45 call p1()|
  46 --error ER_PS_NO_RECURSION
  47 call p1()|
  48 --error ER_PS_NO_RECURSION
  49 call p1()|
  50 set @@max_sp_recursion_depth=@SAVE_SP_RECURSION_LEVELS|
  51 --error ER_SP_RECURSION_LIMIT
  52 call p1()|
  53 --error ER_SP_RECURSION_LIMIT
  54 call p1()|
  55 --error ER_SP_RECURSION_LIMIT
  56 call p1()|
  57
  58 drop procedure p1|
  59 #
  60 # C. Create/drop a stored procedure in Dynamic SQL.
  61 # One cannot create stored procedure from a stored procedure because of
  62 # the way MySQL SP cache works: it's important that this limitation is not
  63 # possible to circumvent by means of Dynamic SQL.
  64 #
  65 create procedure p1()
  66 begin
  67   prepare stmt from "create procedure p2() begin select 1; end";
  68   execute stmt;
  69   deallocate prepare stmt;
  70 end|
  71 --error ER_UNSUPPORTED_PS
  72 call p1()|
  73 --error ER_UNSUPPORTED_PS
  74 call p1()|
  75 drop procedure p1|
  76 create procedure p1()
  77 begin
  78   prepare stmt from "drop procedure p2";
  79   execute stmt;
  80   deallocate prepare stmt;
  81 end|
  82 --error ER_UNSUPPORTED_PS
  83 call p1()|
  84 --error ER_UNSUPPORTED_PS
  85 call p1()|
  86 drop procedure p1|
  87 #
  88 # D. Create/Drop/Alter a table (a DDL that issues a commit) in Dynamic SQL.
  89 # (should work ok).
  90 #
  91 create procedure p1()
  92 begin
  93   prepare stmt_drop from "drop table if exists t1";
  94   execute stmt_drop;
  95   prepare stmt from "create table t1 (a int)";
  96   execute stmt;
  97   insert into t1 (a) values (1);
  98   select * from t1;
  99   prepare stmt_alter from "alter table t1 add (b int)";
 100   execute stmt_alter;
 101   insert into t1 (a,b) values (2,1);
 102   deallocate prepare stmt_alter;
 103   deallocate prepare stmt;
 104   deallocate prepare stmt_drop;
 105 end|
 106 call p1()|
 107 call p1()|
 108 drop procedure p1|
 109 #
 110 # A more real example (a case similar to submitted by 24/7).
 111 #
 112 create procedure p1()
 113 begin
 114   set @tab_name=concat("tab_", replace(curdate(), '-', '_'));
 115   set @drop_sql=concat("drop table if exists ", @tab_name);
 116   set @create_sql=concat("create table ", @tab_name, " (a int)");
 117   set @insert_sql=concat("insert into ", @tab_name, " values (1), (2), (3)");
 118   set @select_sql=concat("select * from ", @tab_name);
 119   select @tab_name;
 120   select @drop_sql;
 121   select @create_sql;
 122   select @insert_sql;
 123   select @select_sql;
 124   prepare stmt_drop from @drop_sql;
 125   execute stmt_drop;
 126   prepare stmt from @create_sql;
 127   execute stmt;
 128   prepare stmt from @insert_sql;
 129   execute stmt;
 130   prepare stmt from @select_sql;
 131   execute stmt;
 132   execute stmt_drop;
 133   deallocate prepare stmt;
 134   deallocate prepare stmt_drop;
 135 end|
 136 --disable_result_log
 137 call p1()|
 138 call p1()|
 139 --enable_result_log
 140 drop procedure p1|
 141 #
 142 # E. Calling a stored procedure with Dynamic SQL
 143 # from a stored function (currently disabled).
 144 #
 145 create procedure p1()
 146 begin
 147   prepare stmt_drop from "drop table if exists t1";
 148   execute stmt_drop;
 149   prepare stmt from "create table t1 (a int)";
 150   execute stmt;
 151   deallocate prepare stmt;
 152   deallocate prepare stmt_drop;
 153 end|
 154 --disable_warnings
 155 drop function if exists f1|
 156 --enable_warnings
 157 create function f1(a int) returns int
 158 begin
 159   call p1();
 160   return 1;
 161 end|
 162
 163 # Every stored procedure that contains Dynamic SQL is marked as
 164 # such. Stored procedures that contain Dynamic SQL are not
 165 # allowed in a stored function or trigger, and here we get the
 166 # corresponding error message.
 167
 168 --error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG
 169 select f1(0)|
 170 --error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG
 171 select f1(f1(0))|
 172 --error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG
 173 select f1(f1(f1(0)))|
 174 drop function f1|
 175 drop procedure p1|
 176 #
 177 # F. Rollback and cleanup lists management in Dynamic SQL.
 178 #
 179 create procedure p1()
 180 begin
 181   drop table if exists t1;
 182   create table t1 (id integer not null primary key,
 183                    name varchar(20) not null);
 184   insert into t1 (id, name) values (1, 'aaa'), (2, 'bbb'), (3, 'ccc');
 185   prepare stmt from "select name from t1";
 186   execute stmt;
 187   select name from t1;
 188   execute stmt;
 189   prepare stmt from
 190     "select name from t1 where name=(select name from t1 where id=2)";
 191   execute stmt;
 192   select name from t1 where name=(select name from t1 where id=2);
 193   execute stmt;
 194 end|
 195 call p1()|
 196 call p1()|
 197 drop procedure p1|
 198 #
 199 # H. Executing a statement prepared externally in SP.
 200 #
 201 prepare stmt from "select * from t1"|
 202 create procedure p1()
 203 begin
 204   execute stmt;
 205   deallocate prepare stmt;
 206 end|
 207 call p1()|
 208 --error ER_UNKNOWN_STMT_HANDLER
 209 call p1()|
 210 drop procedure p1|
 211 #
 212 # I. Use of an SP variable in Dynamic SQL is not possible and
 213 # this limitation is necessary for correct binary logging: prepared
 214 # statements do not substitute SP variables with their values for binlog, so
 215 # SP variables must be not accessible in Dynamic SQL.
 216 #
 217 create procedure p1()
 218 begin
 219   declare a char(10);
 220   set a="sp-variable";
 221   set @a="mysql-variable";
 222   prepare stmt from "select 'dynamic sql:', @a, a";
 223   execute stmt;
 224 end|
 225 --error ER_BAD_FIELD_ERROR
 226 call p1()|
 227 --error ER_BAD_FIELD_ERROR
 228 call p1()|
 229 drop procedure p1|
 230 #
 231 # J. Use of placeholders in Dynamic SQL.
 232 #
 233 create procedure p1()
 234 begin
 235   prepare stmt from 'select ? as a';
 236   execute stmt using @a;
 237 end|
 238 set @a=1|
 239 call p1()|
 240 call p1()|
 241 drop procedure p1|
 242 #
 243 # K. Use of continue handlers with Dynamic SQL.
 244 #
 245 drop table if exists t1|
 246 drop table if exists t2|
 247 create table t1 (id integer primary key auto_increment,
 248                  stmt_text char(35), status varchar(20))|
 249 insert into t1 (stmt_text) values
 250   ("select 1"), ("flush tables"), ("handler t1 open as ha"),
 251   ("analyze table t1"), ("check table t1"), ("checksum table t1"),
 252   ("check table t1"), ("optimize table t1"), ("repair table t1"),
 253   ("describe extended select * from t1"),
 254   ("help help"), ("show databases"), ("show tables"),
 255   ("show table status"), ("show open tables"), ("show storage engines"),
 256   ("insert into t1 (id) values (1)"), ("update t1 set status=''"),
 257   ("delete from t1"), ("truncate t1"), ("call p1()"), ("foo bar"),
 258   ("create view v1 as select 1"), ("alter view v1 as select 2"),
 259   ("drop view v1"),("create table t2 (a int)"),("alter table t2 add (b int)"),
 260   ("drop table t2")|
 261 create procedure p1()
 262 begin
 263   declare v_stmt_text varchar(255);
 264   declare v_id integer;
 265   declare done int default 0;
 266   declare c cursor for select id, stmt_text from t1;
 267   declare continue handler for 1295 -- ER_UNSUPPORTED_PS
 268     set @status='not supported';
 269   declare continue handler for 1064 -- ER_SYNTAX_ERROR
 270     set @status='syntax error';
 271   declare continue handler for sqlstate '02000' set done = 1;
 272
 273   prepare update_stmt from "update t1 set status=? where id=?";
 274   open c;
 275   repeat
 276     if not done then
 277       fetch c into v_id, v_stmt_text;
 278       set @id=v_id, @stmt_text=v_stmt_text;
 279       set @status="supported";
 280       prepare stmt from @stmt_text;
 281       execute update_stmt using @status, @id;
 282     end if;
 283   until done end repeat;
 284   deallocate prepare update_stmt;
 285 end|
 286 call p1()|
 287 select * from t1|
 288 drop procedure p1|
 289 drop table t1|
 290 #
 291 # Bug#7115 "Prepared Statements: packet error if execution within stored
 292 # procedure".
 293 #
 294 prepare stmt from 'select 1'|
 295 create procedure p1() execute stmt|
 296 call p1()|
 297 call p1()|
 298 drop procedure p1|
 299 #
 300 # Bug#10975 "Prepared statements: crash if function deallocates"
 301 # Check that a prepared statement that is currently in use
 302 # can't be deallocated.
 303 #
 304 # a) Prepared statements and stored procedure cache:
 305 #
 306 # TODO: add when the corresponding bug (Bug #12093 "SP not found on second
 307 # PS execution if another thread drops other SP in between") is fixed.
 308 #
 309 # b) attempt to deallocate a prepared statement that is being executed
 310 --error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG
 311 create function f1() returns int
 312 begin
 313   deallocate prepare stmt;
 314   return 1;
 315 end|
 316
 317 # b)-2 a crash (#1) spotted by Sergey Petrunia during code review
 318 create procedure p1()
 319 begin
 320   prepare stmt from 'select 1 A';
 321   execute stmt;
 322 end|
 323 prepare stmt from 'call p1()'|
 324 --error ER_PS_NO_RECURSION
 325 execute stmt|
 326 --error ER_PS_NO_RECURSION
 327 execute stmt|
 328 drop procedure p1|
 329
 330 #
 331 # Bug#10605 "Stored procedure with multiple SQL prepared statements
 332 # disconnects client"
 333 #
 334 --disable_warnings
 335 drop table if exists t1, t2|
 336 --enable_warnings
 337 create procedure p1 (a int) language sql deterministic
 338 begin
 339   declare rsql varchar(100);
 340   drop table if exists t1, t2;
 341   set @rsql= "create table t1 (a int)";
 342   select @rsql;
 343   prepare pst from @rsql;
 344   execute pst;
 345   set @rsql= null;
 346   set @rsql= "create table t2 (a int)";
 347   select @rsql;
 348   prepare pst from @rsql;
 349   execute pst;
 350   drop table if exists t1, t2;
 351 end|
 352 set @a:=0|
 353 call p1(@a)|
 354 select @a|
 355 call p1(@a)|
 356 select @a|
 357 drop procedure if exists p1|
 358
 359 # End of the test
 360 delimiter ;|