1# Test file for stored procedure bugfixes 2 3--echo # 4--echo # Bug #47412: Valgrind warnings / user can read uninitialized memory 5--echo # using SP variables 6--echo # 7 8CREATE SCHEMA testdb; 9USE testdb; 10DELIMITER |; 11CREATE FUNCTION f2 () RETURNS INTEGER 12BEGIN 13 DECLARE CONTINUE HANDLER FOR SQLSTATE '42000' SET @aux = 1; 14 RETURN f_not_exists () ; 15END| 16CREATE PROCEDURE p3 ( arg1 VARCHAR(32) ) 17BEGIN 18 CALL p_not_exists ( ); 19END| 20DELIMITER ;| 21--echo # should not return valgrind warnings 22--error ER_SP_DOES_NOT_EXIST 23CALL p3 ( f2 () ); 24 25DROP SCHEMA testdb; 26 27CREATE SCHEMA testdb; 28USE testdb; 29DELIMITER |; 30CREATE FUNCTION f2 () RETURNS INTEGER 31BEGIN 32 DECLARE CONTINUE HANDLER FOR SQLSTATE '42000' SET @aux = 1; 33 RETURN f_not_exists () ; 34END| 35CREATE PROCEDURE p3 ( arg2 INTEGER ) 36BEGIN 37 CALL p_not_exists ( ); 38END| 39DELIMITER ;| 40--echo # should not return valgrind warnings 41--error ER_SP_DOES_NOT_EXIST 42CALL p3 ( f2 () ); 43 44DROP SCHEMA testdb; 45 46CREATE SCHEMA testdb; 47USE testdb; 48DELIMITER |; 49CREATE FUNCTION f2 () RETURNS INTEGER 50BEGIN 51 DECLARE CONTINUE HANDLER FOR SQLSTATE '42000' SET @aux = 1; 52 RETURN f_not_exists () ; 53END| 54DELIMITER ;| 55--echo # should not return valgrind warnings 56SELECT f2 (); 57 58DROP SCHEMA testdb; 59 60USE test; 61 62--echo # 63--echo # Bug#50423: Crash on second call of a procedure dropping a trigger 64--echo # 65 66--disable_warnings 67DROP TABLE IF EXISTS t1; 68DROP TRIGGER IF EXISTS tr1; 69DROP PROCEDURE IF EXISTS p1; 70--enable_warnings 71 72CREATE TABLE t1 (f1 INTEGER); 73CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW SET @aux = 1; 74CREATE PROCEDURE p1 () DROP TRIGGER tr1; 75 76CALL p1 (); 77--error ER_TRG_DOES_NOT_EXIST 78CALL p1 (); 79 80DROP TABLE t1; 81DROP PROCEDURE p1; 82 83--echo # 84--echo # Bug#54375: Error in stored procedure leaves connection 85--echo # in different default schema 86--echo # 87 88--disable_warnings 89SET @@SQL_MODE = 'STRICT_ALL_TABLES'; 90DROP DATABASE IF EXISTS db1; 91CREATE DATABASE db1; 92USE db1; 93DROP TABLE IF EXISTS t1; 94CREATE TABLE t1 (c1 int NOT NULL PRIMARY KEY); 95INSERT INTO t1 VALUES (1); 96DELIMITER $$; 97CREATE FUNCTION f1 ( 98 some_value int 99) 100RETURNS smallint 101DETERMINISTIC 102BEGIN 103 INSERT INTO t1 SET c1 = some_value; 104 RETURN(LAST_INSERT_ID()); 105END$$ 106DELIMITER ;$$ 107DROP DATABASE IF EXISTS db2; 108CREATE DATABASE db2; 109--enable_warnings 110USE db2; 111SELECT DATABASE(); 112--error ER_DUP_ENTRY 113SELECT db1.f1(1); 114SELECT DATABASE(); 115USE test; 116DROP FUNCTION db1.f1; 117DROP TABLE db1.t1; 118DROP DATABASE db1; 119DROP DATABASE db2; 120USE test; 121 122--echo # 123--echo # Bug#13105873:valgrind warning:possible crash in foreign 124--echo # key handling on subsequent create table if not exists 125--echo # 126 127--disable_warnings 128DROP DATABASE IF EXISTS testdb; 129--enable_warnings 130CREATE DATABASE testdb; 131USE testdb; 132CREATE TABLE t1 (id1 INT PRIMARY KEY); 133DELIMITER $; 134CREATE PROCEDURE `p1`() 135BEGIN 136 CREATE TABLE IF NOT EXISTS t2(id INT PRIMARY KEY, 137 CONSTRAINT FK FOREIGN KEY (id) REFERENCES t1( id1 )); 138END$ 139DELIMITER ;$ 140CALL p1(); 141--echo # below stmt should not return valgrind warnings 142CALL p1(); 143DROP DATABASE testdb; 144USE test; 145 146--echo # 147--echo # End of 5.1 tests 148--echo # 149 150--echo # 151--echo # BUG#13489996 valgrind:conditional jump or move depends on 152--echo # uninitialised values-field_blob 153--echo # 154 155CREATE FUNCTION sf() RETURNS BLOB RETURN ""; 156SELECT sf(); 157DROP FUNCTION sf; 158 159--echo # 160--echo # Bug#11763507 - 56224: FUNCTION NAME IS CASE-SENSITIVE 161--echo # 162SET @@SQL_MODE = ''; 163DELIMITER $; 164CREATE FUNCTION testf_bug11763507() RETURNS INT 165BEGIN 166 RETURN 0; 167END 168$ 169 170CREATE PROCEDURE testp_bug11763507() 171BEGIN 172 SELECT "PROCEDURE testp_bug11763507"; 173END 174$ 175 176DELIMITER ;$ 177 178# STORED FUNCTIONS 179SELECT testf_bug11763507(); 180SELECT TESTF_bug11763507(); 181 182--replace_column 5 # 6 # 183SHOW FUNCTION STATUS LIKE 'testf_bug11763507'; 184--replace_column 5 # 6 # 185SHOW FUNCTION STATUS WHERE NAME='testf_bug11763507'; 186--replace_column 5 # 6 # 187SHOW FUNCTION STATUS LIKE 'TESTF_bug11763507'; 188--replace_column 5 # 6 # 189SHOW FUNCTION STATUS WHERE NAME='TESTF_bug11763507'; 190 191SHOW CREATE FUNCTION testf_bug11763507; 192SHOW CREATE FUNCTION TESTF_bug11763507; 193 194# STORED PROCEDURE 195CALL testp_bug11763507(); 196CALL TESTP_bug11763507(); 197 198--replace_column 5 # 6 # 199SHOW PROCEDURE STATUS LIKE 'testp_bug11763507'; 200--replace_column 5 # 6 # 201SHOW PROCEDURE STATUS WHERE NAME='testp_bug11763507'; 202--replace_column 5 # 6 # 203SHOW PROCEDURE STATUS LIKE 'TESTP_bug11763507'; 204--replace_column 5 # 6 # 205SHOW PROCEDURE STATUS WHERE NAME='TESTP_bug11763507'; 206 207SHOW CREATE PROCEDURE testp_bug11763507; 208SHOW CREATE PROCEDURE TESTP_bug11763507; 209 210# INFORMATION SCHEMA 211SELECT specific_name FROM INFORMATION_SCHEMA.ROUTINES WHERE specific_name LIKE 'testf_bug11763507'; 212SELECT specific_name FROM INFORMATION_SCHEMA.ROUTINES WHERE specific_name LIKE 'TESTF_bug11763507'; 213 214SELECT specific_name FROM INFORMATION_SCHEMA.ROUTINES WHERE specific_name='testf_bug11763507'; 215SELECT specific_name FROM INFORMATION_SCHEMA.ROUTINES WHERE specific_name='TESTF_bug11763507'; 216 217DROP PROCEDURE testp_bug11763507; 218DROP FUNCTION testf_bug11763507; 219 220--echo #END OF BUG#11763507 test. 221 222--echo # 223--echo # MDEV-5531 double call procedure in one session 224--echo # 225 226CREATE TABLE `t1` ( 227 `id` int(10) unsigned NOT NULL AUTO_INCREMENT, 228 `create_ts` int(10) unsigned DEFAULT '0', 229 PRIMARY KEY (`id`) 230) ENGINE=MyISAM AUTO_INCREMENT=0 DEFAULT CHARSET=utf8; 231 232DELIMITER $$; 233 234CREATE PROCEDURE test_5531 (IN step TINYINT(1)) 235BEGIN 236 DECLARE counts INT DEFAULT 0; 237 DECLARE cur1 CURSOR FOR 238 239 SELECT ct.id 240 FROM (SELECT NULL) AS z 241 JOIN ( 242 SELECT id 243 FROM `t1` 244 LIMIT 10 245 ) AS ct 246 JOIN (SELECT NULL) AS x ON( 247 EXISTS( 248 SELECT 1 249 FROM `t1` 250 WHERE id=ct.id 251 LIMIT 1 252 ) 253 ); 254 255 IF step=1 THEN 256 TRUNCATE t1; 257 REPEAT 258 INSERT INTO `t1` 259 (create_ts) VALUES 260 (UNIX_TIMESTAMP()); 261 262 SET counts=counts+1; 263 UNTIL counts>150 END REPEAT; 264 265 SET max_sp_recursion_depth=1; 266 267 CALL test_5531(2); 268 SET max_sp_recursion_depth=2; 269 CALL test_5531(2); 270 ELSEIF step=2 THEN 271 OPEN cur1; CLOSE cur1; 272 END IF; 273END $$ 274DELIMITER ;$$ 275CALL test_5531(1); 276DROP PROCEDURE test_5531; 277DROP TABLE t1; 278 279# 280# MDEV-6601 Assertion `!thd->in_active_multi_stmt_transa ction() || thd->in_multi_stmt_transaction_mode()' failed on executing a stored procedure with commit 281# 282delimiter |; 283create procedure sp() begin 284 commit; 285end| 286delimiter ;| 287start transaction; 288call sp(); 289drop procedure sp; 290 291--echo # 292--echo # MDEV-11146 SP variables of the SET data type erroneously allow values with comma 293--echo # 294 295DELIMITER $$; 296--error ER_ILLEGAL_VALUE_FOR_TYPE 297CREATE PROCEDURE p1() 298BEGIN 299 DECLARE a SET('a','b','c','a,b'); 300 SET a='a,b'; 301 SELECT a, a+0; 302END; 303$$ 304DELIMITER ;$$ 305 306 307--echo # 308--echo # Start of 10.3 tests 309--echo # 310 311--echo # 312--echo # MDEV-16117 SP with a single FOR statement creates but further fails to load 313--echo # 314 315DELIMITER $$; 316CREATE PROCEDURE p1() 317 FOR i IN 1..10 DO 318 set @x = 5; 319 END FOR; 320$$ 321DELIMITER ;$$ 322CALL p1; 323SELECT body FROM mysql.proc WHERE db='test' AND specific_name='p1'; 324DROP PROCEDURE p1; 325 326 327DELIMITER $$; 328CREATE PROCEDURE p1() WITH t1 AS (SELECT 1) SELECT 1; 329$$ 330DELIMITER ;$$ 331CALL p1; 332SELECT body FROM mysql.proc WHERE db='test' AND specific_name='p1'; 333DROP PROCEDURE p1; 334 335 336DELIMITER $$; 337CREATE PROCEDURE p1() VALUES (1); 338$$ 339DELIMITER ;$$ 340CALL p1; 341SELECT body FROM mysql.proc WHERE db='test' AND specific_name='p1'; 342DROP PROCEDURE p1; 343 344 345DELIMITER $$; 346CREATE FUNCTION f1() RETURNS INT 347 FOR i IN 1..10 DO 348 RETURN 1; 349 END FOR; 350$$ 351DELIMITER ;$$ 352SELECT f1(); 353SELECT body FROM mysql.proc WHERE db='test' AND specific_name='f1'; 354DROP FUNCTION f1; 355 356--echo # 357--echo # End of 10.2 tests 358--echo # 359 360--echo # 361--echo # MDEV-25501 routine_definition in information_schema.routines loses tablename if it starts with an _ and is not backticked 362--echo # 363create table _t1 (a int); 364create procedure p1() select * from _t1; 365show create procedure p1; 366select routine_definition from information_schema.routines where routine_schema=database() and specific_name='p1'; 367select body, body_utf8 from mysql.proc where name='p1'; 368drop procedure p1; 369drop table _t1; 370 371--echo # 372--echo # End of 10.3 tests 373--echo # 374