MySQL Stored Procedure Not Working as Expected Puzzler
Posted by: kent in mysql, tags: mysql, parameter names, stored proceduresI recently spent 3 hours pulling my hair out trying to fix a MySQL stored procedure that wasn’t working as expected. I’ve boiled the ultimate problem down to a simple example below. Can you spot the problem?
# First, let's create some sample data
CREATE TABLE TrendicsTest (
DateTime DATETIME NOT NULL,
Value INT NOT NULL,
UNIQUE KEY DateTime (DateTime)
);
INSERT INTO TrendicsTest VALUES ('2008-01-01 01:00', 1);
INSERT INTO TrendicsTest VALUES ('2008-01-01 02:00', 2);
# Next, lets' define a stored procedure to query the sample data
DELIMITER |
DROP PROCEDURE IF EXISTS summarizeTrendicsTest|
CREATE PROCEDURE summarizeTrendicsTest(dateTime DATETIME)
BEGIN
SELECT @endDateTime := DATE_ADD(dateTime, INTERVAL 1 HOUR);
SELECT * FROM TrendicsTest WHERE DateTime=@endDateTime;
END;
|
DELIMITER ;
# Now, let's call the stored procedure and expect
# to get back the first row of data
call summarizeTrendicsTest('2008-01-01 00:00');
What the hell? The first SELECT that sets @endDateTime variable echoes a result but the main SELECT returns an empty set. Just to check our query, let's make sure the query runs ok...
# Add one hour to the time just like the stored procedure
SELECT * FROM TrendicsTest WHERE DateTime='2008-01-01 01:00';
Yes, running the query returns data so why doesn't the stored procedure return any data that appears to be running the same SELECT? Evidently, using "dateTime" for the stored procedure param and using "DateTime" for the field in the WHERE clause confuses MySQL. The stored procedure can be fixed by renaming the param name to something else...
DELIMITER |
DROP PROCEDURE IF EXISTS summarizeTrendicsTest|
CREATE PROCEDURE summarizeTrendicsTest(dateTimeXyz DATETIME)
BEGIN
SELECT @endDateTime := DATE_ADD(dateTimeXyz, INTERVAL 1 HOUR);
SELECT * FROM TrendicsTest WHERE DateTime=@endDateTime;
END;
|
DELIMITER ;
# Now, let's call the stored procedure and expect
# to get back the second row of data
call summarizeTrendicsTest('2008-01-01 00:00');
Bah, in a complex stored procedure that is really not so easy to figure out. So, be careful with your choice of stored procedure parameter names — avoid using a parameter name that is the same as a field name in your tables. Seems like the MySQL stored procedure compiler should at least warn you when this happens if not throw an error.
Bookmark at:StumbleUpon | Digg | Del.icio.us | Dzone | Newsvine | Spurl | Simpy | Furl | Reddit | Yahoo! MyWeb
Entries (RSS)
Hi,
It has been some time since i’ve played around with mysql stored procedures, but I think that is not a problem - when using conflicting names, you should allways provide the table/object context upon use - eg, Select * from TrendicsTest as t where t.DateTime=@endDateTime
There are a whole bunch of situations when you have to do some renaming and/or specify explicitly the fields context, such as multiple table selects or multiple table views with conflicting field names. AFAIK this is not a real issue, and even if it was, it wasn’t mysql-specific.