Description:
Hello.
I have found 3 bugs developing stored procedures today:
BUG #1:
DEFAULT VALUES FOR VARIABLES DOESN'T WORK WITHIN PROCEDURES!!!
BUG #2:
PROCEDURE DO NOT NEED VARIABLE DECLARATION FOR COMPILE AND WORK!
BUG #3:
DIFFERENT PROCEDURES HAS SHARED MEMORY AND VALUES WITH NO ISOLATION BETWEEN THEM!!!
I attached SQL script as an example of problem. Script creates database, table, 3 procedures and finally executes two of them. You may look into table for results and read full descriptions put are as comments in script file.
Please fix this bugs.
Best regards
SM
How to repeat:
/* Execute following SQL script and look inside table 'logs'. */
/*
I HAVE FOUND 3 HUGE BUGS IN MySQL!!!!
Used MySQL database:
Server version: 5.5.9 MySQL Community Server (GPL)
Used MySQL Workbench:
5.2.32 CE Revision 7496
BUG #1:
DEFAULT VALUES FOR VARIABLES DOESN'T WORK WITHIN PROCEDURES!!!
BUG #2:
PROCEDURE DO NOT NEED VARIABLE DECLARATION FOR COMPILE AND WORK!
BUG #3:
PROCEDURES X() AND Y() HAS SHARED MEMORY AND VALUES WITH NO ISOLATION BETWEEN THEM!!!
*/
/* Execute following script - it's database structure for test */
CREATE SCHEMA `mytest` ;
USE `mytest`;
CREATE TABLE `mytest`.`logs` (
`idlogs` INT NOT NULL AUTO_INCREMENT ,
`log_text` TEXT NULL ,
PRIMARY KEY (`idlogs`) );
DROP procedure IF EXISTS `LOG_A`;
DELIMITER $$
USE `mytest`$$
CREATE PROCEDURE `mytest`.`LOG_A` ()
BEGIN
DECLARE a INT(10) DEFAULT 333;
INSERT INTO logs (log_text) VALUES (@a);
END
$$
DELIMITER ;
DROP procedure IF EXISTS `Y`;
DELIMITER $$
CREATE PROCEDURE `mytest`.`Y` ()
BEGIN
/* Note: The same variable name 'xxx' as in X() procedure! */
DECLARE xxx VARCHAR(100);
/* Note: There is no variable 'yyy' declaration! */
SET @xxx = 'No, no, no';
SET @yyy = 'Noooo x 3'; /* It works! With no declaration! */
INSERT INTO logs (log_text) VALUES (@xxx);
INSERT INTO logs (log_text) VALUES (@yyy);
END
$$
DELIMITER ;
DROP procedure IF EXISTS `X`;
DELIMITER $$
CREATE PROCEDURE `mytest`.`X` ()
BEGIN
DECLARE xxx VARCHAR(100);
DECLARE yyy VARCHAR(100);
SET @xxx = 'Yes';
SET @yyy = 'Y-too';
CALL Y();
/* See, what happens... */
INSERT INTO logs (log_text) VALUES (@xxx);
INSERT INTO logs (log_text) VALUES (@yyy);
END
$$
DELIMITER ;
/* Now, execute created procedure as follows: */
CALL LOG_A();
/* See into 'logs' table. If you expected value '333' in the record?
You are wrong!
You can see NULL value!
BUG #1:
DEFAULT VALUES FOR VARIABLES DOESN'T WORK WITHIN PROCEDURES!!!
Now, let's try something really weird... - execute following code: */
/* Execute X() procedure */
CALL X();
/*
Now, look to the table.....
You can see 4 records as follows:
2, No, no, no
3, Noooo x 3
4, No, no, no
5, Noooo x 3
As you can see, this is not what you expected!
BUG #2:
PROCEDURE DO NOT NEED VARIABLE DECLARATION FOR COMPILE AND WORK!
BUG #3:
PROCEDURES X() AND Y() HAS SHARED MEMORY AND VALUES!!!
There is no basic isolation between them!
No error raised during compilation, no error during execution, but their results ARE WRONG!!!!
Hint: Record 4 should be 'Yes' and record 5 should be 'Y-too'!
I've spent almost 20 hours for checking my stored procedures for errors made myself :(
I never thought, that MySQL "professional" SQL database may have bugs like that!
I worked with Oracle, InterBase and many more DB's, and I'm totally ruined by MySQL now :(
*/
Suggested fix:
Patch MySQL engine!
Description: Hello. I have found 3 bugs developing stored procedures today: BUG #1: DEFAULT VALUES FOR VARIABLES DOESN'T WORK WITHIN PROCEDURES!!! BUG #2: PROCEDURE DO NOT NEED VARIABLE DECLARATION FOR COMPILE AND WORK! BUG #3: DIFFERENT PROCEDURES HAS SHARED MEMORY AND VALUES WITH NO ISOLATION BETWEEN THEM!!! I attached SQL script as an example of problem. Script creates database, table, 3 procedures and finally executes two of them. You may look into table for results and read full descriptions put are as comments in script file. Please fix this bugs. Best regards SM How to repeat: /* Execute following SQL script and look inside table 'logs'. */ /* I HAVE FOUND 3 HUGE BUGS IN MySQL!!!! Used MySQL database: Server version: 5.5.9 MySQL Community Server (GPL) Used MySQL Workbench: 5.2.32 CE Revision 7496 BUG #1: DEFAULT VALUES FOR VARIABLES DOESN'T WORK WITHIN PROCEDURES!!! BUG #2: PROCEDURE DO NOT NEED VARIABLE DECLARATION FOR COMPILE AND WORK! BUG #3: PROCEDURES X() AND Y() HAS SHARED MEMORY AND VALUES WITH NO ISOLATION BETWEEN THEM!!! */ /* Execute following script - it's database structure for test */ CREATE SCHEMA `mytest` ; USE `mytest`; CREATE TABLE `mytest`.`logs` ( `idlogs` INT NOT NULL AUTO_INCREMENT , `log_text` TEXT NULL , PRIMARY KEY (`idlogs`) ); DROP procedure IF EXISTS `LOG_A`; DELIMITER $$ USE `mytest`$$ CREATE PROCEDURE `mytest`.`LOG_A` () BEGIN DECLARE a INT(10) DEFAULT 333; INSERT INTO logs (log_text) VALUES (@a); END $$ DELIMITER ; DROP procedure IF EXISTS `Y`; DELIMITER $$ CREATE PROCEDURE `mytest`.`Y` () BEGIN /* Note: The same variable name 'xxx' as in X() procedure! */ DECLARE xxx VARCHAR(100); /* Note: There is no variable 'yyy' declaration! */ SET @xxx = 'No, no, no'; SET @yyy = 'Noooo x 3'; /* It works! With no declaration! */ INSERT INTO logs (log_text) VALUES (@xxx); INSERT INTO logs (log_text) VALUES (@yyy); END $$ DELIMITER ; DROP procedure IF EXISTS `X`; DELIMITER $$ CREATE PROCEDURE `mytest`.`X` () BEGIN DECLARE xxx VARCHAR(100); DECLARE yyy VARCHAR(100); SET @xxx = 'Yes'; SET @yyy = 'Y-too'; CALL Y(); /* See, what happens... */ INSERT INTO logs (log_text) VALUES (@xxx); INSERT INTO logs (log_text) VALUES (@yyy); END $$ DELIMITER ; /* Now, execute created procedure as follows: */ CALL LOG_A(); /* See into 'logs' table. If you expected value '333' in the record? You are wrong! You can see NULL value! BUG #1: DEFAULT VALUES FOR VARIABLES DOESN'T WORK WITHIN PROCEDURES!!! Now, let's try something really weird... - execute following code: */ /* Execute X() procedure */ CALL X(); /* Now, look to the table..... You can see 4 records as follows: 2, No, no, no 3, Noooo x 3 4, No, no, no 5, Noooo x 3 As you can see, this is not what you expected! BUG #2: PROCEDURE DO NOT NEED VARIABLE DECLARATION FOR COMPILE AND WORK! BUG #3: PROCEDURES X() AND Y() HAS SHARED MEMORY AND VALUES!!! There is no basic isolation between them! No error raised during compilation, no error during execution, but their results ARE WRONG!!!! Hint: Record 4 should be 'Yes' and record 5 should be 'Y-too'! I've spent almost 20 hours for checking my stored procedures for errors made myself :( I never thought, that MySQL "professional" SQL database may have bugs like that! I worked with Oracle, InterBase and many more DB's, and I'm totally ruined by MySQL now :( */ Suggested fix: Patch MySQL engine!