MySQL Continue Handler Problems inside a Function

Question

I am in process of writing a simple MySQL function that looks up a value in a table and returns it. In case there is not value found it returns null. However, even with a continue handler defined I still end up with a warning "No data - zero rows fetched, selected, or processed". My code is below, what am I doing wrong? I really want to get rid of this warning :)

DROP FUNCTION IF EXISTS `getCompanySetting`;
DELIMITER |
CREATE FUNCTION `getCompanySetting`(setting_company_id INT, setting_name VARCHAR(255))
 RETURNS TEXT
BEGIN
 DECLARE setting_value TEXT DEFAULT NULL;
 DECLARE CONTINUE HANDLER FOR SQLWARNING BEGIN END;
 DECLARE CONTINUE HANDLER FOR NOT FOUND SET setting_value = NULL;
 SELECT
 value
 FROM company_settings
 WHERE
 company_id = `setting_company_id`
 AND
 name = `setting_name`
 INTO setting_value;
 RETURN setting_value;
END|
DELIMITER ;
mysql> SELECT getCompanySetting(24, 'observers_active');
+-------------------------------------------+
| getCompanySetting(24, 'observers_active') |
+-------------------------------------------+
| NULL |
+-------------------------------------------+
1 row in set, 1 warning (0.00 sec)
mysql> show warnings;
+---------+------+-----------------------------------------------------+
| Level | Code | Message |
+---------+------+-----------------------------------------------------+
| Warning | 1329 | No data - zero rows fetched, selected, or processed |
+---------+------+-----------------------------------------------------+
1 row in set (0.00 sec)

Update: MySQL Version 5.5.25

Answer 1

Before MySQL 5.6.3, if a statement that generates a warning or error causes a condition handler to be invoked, the handler may not clear the diagnostic area. This might lead to the appearance that the handler was not invoked.

I have explained this kind of behavior and the solution in this post:

Event Scheduler: No data - zero rows fetched, selected, or processed

Answer 2

If you ask me there is no better way to fix this kind of issue with handlers or exceptional handling declarative statements.

But you may try to give a constant value that will return even if your answer should be NULL. Let that constant value be a wierdest one that you can expect from the fucntion that could return. Add that constant value to your return function.

Disclaimer: I haven't tested it, but you may give it a try. Let me know on your results.

In this case I'm mentioning 'clops' as the constant.

Try below changes to your function only under select clause and see.

 SELECT concat('clops',value) FROM company_settings WHERE
 company_id = `setting_company_id`
 AND
 name = `setting_name`
 INTO setting_value;

mysql> SELECT getCompanySetting(24, 'observers_active');
=====>clops

As only you are aware of the word you may keep that word as a seperator. And at your application end split this word, if you get any other data after seperating 'clops' from the return type then treat it as a real return data. By this way you can ignore/suppress the warning that you get.

• clops: Unfortunately the warning is still there :(

Answer 3

One possible way around it is to generate a query which always returns a value:

SELECT
 company_settings.value
FROM (SELECT 1 as VAL) AS V
LEFT OUTER JOIN company_settings
ON company_id = `setting_company_id`
 AND
 name = `setting_name`
INTO setting_value;

You treat the response as if it were part of an outer join and the empty set is now a non-warning NULL.