MySQL Bugs: #9963: Two separate stored procedures are treated as identical

Bug #9963	Two separate stored procedures are treated as identical
Submitted:	17 Apr 2005 21:42	Modified:	17 Apr 2005 22:24
Reporter:	Justinas Balciunas	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	5.0.3-beta	OS:	Linux (Linux)
Assigned to:		CPU Architecture:	Any

Description:
When creating two tables, a view on these tables and two stored procedures to operate the data of that view result a wierd situation when two differently formed and named stored procedures - get_all_cars() and get_user_car() act like the first one ( get_all_cars() ).

Subsequently, calls to stored procedures (although they do not exist !!!) such as get(), get_(), get_anything_from_anywhere(), could_you_be_any_more_specific_please() produce same results as the call to procedure get_all_cars().

How to repeat:
DELIMITER $

CREATE TABLE users (
  user_id INT(11) NOT NULL AUTO_INCREMENT,
  user_name VARCHAR(255) NOT NULL,
  PRIMARY KEY  (user_id)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
$

CREATE TABLE cars (
  car_id INT(11) NOT NULL AUTO_INCREMENT,
  user_id INT(11) NOT NULL,
  car_name VARCHAR(255) NOT NULL,
  PRIMARY KEY  (car_id),
  KEY user_id (user_id)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
$

CREATE VIEW user_cars AS
SELECT
 u.user_id
 , c.car_id
 , u.user_name
 , c.car_name
FROM
 users u,
 cars c
WHERE
 c.user_id = u.user_id
$

INSERT INTO users (user_id, user_name) VALUES ('', 'john')
$

INSERT INTO users (user_id, user_name) VALUES ('', 'pete')
$

INSERT INTO users (user_id, user_name) VALUES ('', 'ann')
$

INSERT INTO users (user_id, user_name) VALUES ('', 'mary')
$

INSERT INTO cars (car_id, user_id, car_name) VALUES ('', 4, 'Toyota Corolla, 2003')
$

INSERT INTO cars (car_id, user_id, car_name) VALUES ('', 3, 'VW Golf, 1988')
$

INSERT INTO cars (car_id, user_id, car_name) VALUES ('', 2, 'Mazda 121, 1995')
$

INSERT INTO cars (car_id, user_id, car_name) VALUES ('', 1, 'Honda Civic, 1998')
$
  
CREATE PROCEDURE get_all_cars()
COMMENT 'Procedure to show all cars'
BEGIN
	SELECT * FROM user_cars;
END
$

CREATE PROCEDURE get_user_car( IN i_user_name VARCHAR(255) )
COMMENT 'Procedure to show cars by username'
BEGIN
	SELECT * FROM user_cars WHERE user_name = i_user_name;
END
$

DELIMITER ;

call get_all_cars();
call get_user_car();
call get_user_car('john');
call get();
call get_();
call get_anything_from_anywhere();
call could_you_be_any_more_specific_please();

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

miguel@light:~$ dbs/5.0/bin/mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 5.0.5-beta-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create database t0;
Query OK, 1 row affected (0.02 sec)

mysql> use t0;
Database changed
mysql> DELIMITER $
mysql>
mysql> CREATE TABLE users (
    ->   user_id INT(11) NOT NULL AUTO_INCREMENT,
    ->   user_name VARCHAR(255) NOT NULL,
    ->   PRIMARY KEY  (user_id)
    -> ) ENGINE=MyISAM DEFAULT CHARSET=utf8
    -> $
Query OK, 0 rows affected (0.03 sec)

mysql>
mysql> CREATE TABLE cars (
    ->   car_id INT(11) NOT NULL AUTO_INCREMENT,
    ->   user_id INT(11) NOT NULL,
    ->   car_name VARCHAR(255) NOT NULL,
    ->   PRIMARY KEY  (car_id),
    ->   KEY user_id (user_id)
    -> ) ENGINE=MyISAM DEFAULT CHARSET=utf8
    -> $
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> CREATE VIEW user_cars AS
    -> SELECT
    ->  u.user_id
    ->  , c.car_id
    ->  , u.user_name
    ->  , c.car_name
    -> FROM
    ->  users u,
    ->  cars c
    -> WHERE
    ->  c.user_id = u.user_id
    -> $
Query OK, 0 rows affected (0.04 sec)

mysql>
mysql> INSERT INTO users (user_id, user_name) VALUES ('', 'john')
    -> $
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql>
mysql> INSERT INTO users (user_id, user_name) VALUES ('', 'pete')
    -> $
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql>
mysql> INSERT INTO users (user_id, user_name) VALUES ('', 'ann')
    -> $
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql>
mysql> INSERT INTO users (user_id, user_name) VALUES ('', 'mary')
    -> $
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql>
mysql> INSERT INTO cars (car_id, user_id, car_name) VALUES ('', 4, 'Toyota Corolla,
    '> 2003')
    -> $
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql>
mysql> INSERT INTO cars (car_id, user_id, car_name) VALUES ('', 3, 'VW Golf, 1988')
    -> $
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql>
mysql> INSERT INTO cars (car_id, user_id, car_name) VALUES ('', 2, 'Mazda 121, 1995')
    -> $
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql>
mysql> INSERT INTO cars (car_id, user_id, car_name) VALUES ('', 1, 'Honda Civic,
    '> 1998')
    -> $
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql>
mysql> CREATE PROCEDURE get_all_cars()
    -> COMMENT 'Procedure to show all cars'
    -> BEGIN
    -> SELECT * FROM user_cars;
    -> END
    -> $
Query OK, 0 rows affected (0.02 sec)

mysql>
mysql> CREATE PROCEDURE get_user_car( IN i_user_name VARCHAR(255) )
    -> COMMENT 'Procedure to show cars by username'
    -> BEGIN
    -> SELECT * FROM user_cars WHERE user_name = i_user_name;
    -> END
    -> $
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> DELIMITER ;
mysql>
mysql> call get_all_cars();
+---------+--------+-----------+----------------------+
| user_id | car_id | user_name | car_name             |
+---------+--------+-----------+----------------------+
|       4 |      1 | mary      | Toyota Corolla,
2003 |
|       3 |      2 | ann       | VW Golf, 1988        |
|       2 |      3 | pete      | Mazda 121, 1995      |
|       1 |      4 | john      | Honda Civic,
1998    |
+---------+--------+-----------+----------------------+
4 rows in set (0.03 sec)

Query OK, 0 rows affected (0.03 sec)

mysql> call get_user_car();
ERROR 1318 (42000): Incorrect number of arguments for PROCEDURE t0.get_user_car; expected 1, got 0
mysql> call get_user_car("mary");
+---------+--------+-----------+----------------------+
| user_id | car_id | user_name | car_name             |
+---------+--------+-----------+----------------------+
|       4 |      1 | mary      | Toyota Corolla,
2003 |
+---------+--------+-----------+----------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> call get_user_car('john');
+---------+--------+-----------+-------------------+
| user_id | car_id | user_name | car_name          |
+---------+--------+-----------+-------------------+
|       1 |      4 | john      | Honda Civic,
1998 |
+---------+--------+-----------+-------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> call get();
ERROR 1305 (42000): PROCEDURE t0.get does not exist
mysql> call get_();
ERROR 1305 (42000): PROCEDURE t0.get_ does not exist
mysql> call get_anything_from_anywhere();
ERROR 1305 (42000): PROCEDURE t0.get_anything_from_anywhere does not exist
mysql> call could_you_be_any_more_specific_please();
ERROR 1305 (42000): PROCEDURE t0.could_you_be_any_more_specific_please does not exist
mysql>

Also the queries per second avg is already fixed.

Thank you for the bug report.