Why does this MySQL function return null?

Question 1

Description: the query actually run have 4 results returned,as can be see from below, what I did is just concate the items then return, but unexpectedly,it's null.

I think the code is self-explanatory:

DELIMITER |
DROP FUNCTION IF EXISTS get_idiscussion_ask|
CREATE FUNCTION get_idiscussion_ask(iask_id INT UNSIGNED) RETURNS TEXT DETERMINISTIC
BEGIN
 DECLARE done INT DEFAULT 0;
 DECLARE body varchar(600);
 DECLARE created DATETIME;
 DECLARE anonymous TINYINT(1);
 DECLARE screen_name varchar(64);
 DECLARE result TEXT;
 DECLARE cur1 CURSOR FOR SELECT body,created,anonymous,screen_name from idiscussion left join users on idiscussion.uid=users.id where idiscussion.iask_id=iask_id;
 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
 SET result = '';
 OPEN cur1;
 REPEAT
 FETCH cur1 INTO body, created, anonymous, screen_name;
 SET result = CONCAT(result,'<comment><body><![CDATA[',body,']]></body>','<replier>',if(screen_name is not null and !anonymous,screen_name,''),'</replier>','<created>',created,'</created></comment>');
 UNTIL done END REPEAT;
 CLOSE cur1;
 RETURN result;
END |
DELIMITER ;
mysql> DELIMITER ;
mysql> select get_idiscussion_ask(1);
+------------------------+
| get_idiscussion_ask(1) |
+------------------------+
| NULL |
+------------------------+
1 row in set (0.01 sec)
mysql> SELECT body,created,anonymous,screen_name from idiscussion left join users on idiscussion.uid=users.id where idiscussion.iask_id=1;
+------+---------------------+-----------+-------------+
| body | created | anonymous | screen_name |
+------+---------------------+-----------+-------------+
| haha | 2009年05月27日 04:57:51 | 0 | NULL |
| haha | 2009年05月27日 04:57:52 | 0 | NULL |
| haha | 2009年05月27日 04:57:52 | 0 | NULL |
| haha | 2009年05月27日 04:57:53 | 0 | NULL |
+------+---------------------+-----------+-------------+
4 rows in set (0.00 sec)

For those who don't think the code is self-explanatory:

Why the function returns NULL?

Question 2

You should write a description with a proper question.

Question 3

Yeah, I really don't think the code is self-explanatory. Even if people can figure it out eventually, it's much more helpful to actually explain in words when you've got this much code.

Question 4

You guys are absolutely right,I've corrected my fault.

Question 5

The answer is: simplify the question. Not for us, but for yourself. Working in a complex situation you will spend days instead of hours trying to figure out what's wrong. Make a simple version of the same thing with only the relevant bits and test your assumptions. That's the only way to figure out what's wrong.

Question 6

Seems I found the case, see my updated post.

Question 7

Rename your variables and the input parameter, they're ambiguous.

This query:

SELECT body, created, anonymous, screen_name
FROM idiscussion
LEFT JOIN
 users
ON idiscussion.uid = users.id
WHERE idiscussion.iask_id = iask_id

returns your previously declared variables (which are NULL), not the table columns.

Prepend the variable names and the input parameter name with an underscore.

Also you make an extra assignment to result:

FETCH cur1 INTO body, created, anonymous, screen_name;
SET result = CONCAT(result,'<comment><body><![CDATA[',body,']]></body>','<replier>',if(screen_name is not null and !anonymous,screen_name,''),'</replier>','<created>',created,'</created></comment>');

The handler sets done after the FETCH fails, but the result gets assigned nevertheless.

Change your handler to:

DECLARE EXIT HANDLER FOR SQLSTATE '02000' RETURN result;

Finally: in MySQL, this can be done with a single query. There is no need to do it with a function.

SELECT GROUP_CONCAT(CONCAT(result,'<comment><body><![CDATA[',body,']]></body>','<replier>',if(screen_name is not null and !anonymous,screen_name,''),'</replier>','<created>',created,'</created></comment>') SEPARATOR '')
FROM idiscussion
LEFT JOIN
 users
ON idiscussion.uid=users.id
WHERE idiscussion.iask_id = @_iask_id

Question 8

Thank you for you reply,tried, but with no progress,what a pity!

Question 9

So why is this the answer then? What was reason for your problem?

Question 10

The query in the cursor selected variables, not columns

Question 11

Keep in mind that concatenating any string together with a NULL returns NULL. Try this test:

mysql> SET @s = 'test string';
mysql> SET @s = CONCAT(@s, '<tag>', NULL, '</tag>');
mysql> SELECT @s;

This returns NULL.

So as you loop through your cursor, if the body or created columns are NULL on any row, the result becomes NULL. Then on subsequent iterations of the loop anything concatenated with a NULL result has no effect; it stays NULL.

Try something like this:

REPEAT
 FETCH cur1 INTO body, created, anonymous, screen_name;
 SET result = CONCAT(result, 
 '<comment><body><![CDATA[', 
 COALESCE(body, ''),
 ']]></body>', 
 '<replier>', 
 IF(COALESCE(anonymous, 0) != 0, COALESCE(screen_name, ''), ''), 
 '</replier>',
 '<created>',
 COALESCE(created, ''),
 '</created></comment>'
 );
UNTIL done END REPEAT;

The COALESCE() function is a useful function in standard SQL. It returns its first non-NULL argument.

Question 12

CONCAT_WS(separator, str1, str2,...) CONCAT_WS() stands for CONCAT With Separator and is a special form of CONCAT(). The first argument is the separator for the rest of the arguments. The separator is added between the strings to be concatenated: The separator can be a string as can the rest of the arguments. If the separator is NULL, the result is NULL. The function skips any NULL values after the separator argument.

mysql>

SELECT CONCAT_WS(",","First name","Second name","Last Name");
 -> 'First name,Second name,Last Name'

mysql>

SELECT CONCAT_WS(",","First name",NULL,"Last Name");
 -> 'First name,Last Name'

Before MySQL 4.0.14, CONCAT_WS() skips empty strings as well as NULL values.

Question 13

I try to be verbose because your question isn't ;)

You're expecting the return value of the function to be non-NULL since you're creating the return value by concatenating only non-NULL strings.

Only if one of the strings was NULL, the whole return value would be NULL. Your demo data only contains NULL values in screen_name, but you respected that case.

But somehow (atm I have no idea how) one of the values must be NULL and the relevant line to look at ist the one with the big CONCAT.

What if you shorten the relevant line for debug reasons to:

SET result = if(screen_name is not null,screen_name,'')

Does it still return NULL?

Quassnoi 428k94 gold badges628 silver badges624 bronze badges · Accepted Answer · 2009-05-27 17:50:51Z

Rename your variables and the input parameter, they're ambiguous.

This query:

SELECT body, created, anonymous, screen_name
FROM idiscussion
LEFT JOIN
 users
ON idiscussion.uid = users.id
WHERE idiscussion.iask_id = iask_id

returns your previously declared variables (which are NULL), not the table columns.

Prepend the variable names and the input parameter name with an underscore.

Also you make an extra assignment to result:

FETCH cur1 INTO body, created, anonymous, screen_name;
SET result = CONCAT(result,'<comment><body><![CDATA[',body,']]></body>','<replier>',if(screen_name is not null and !anonymous,screen_name,''),'</replier>','<created>',created,'</created></comment>');

The handler sets done after the FETCH fails, but the result gets assigned nevertheless.

Change your handler to:

DECLARE EXIT HANDLER FOR SQLSTATE '02000' RETURN result;

Finally: in MySQL, this can be done with a single query. There is no need to do it with a function.

SELECT GROUP_CONCAT(CONCAT(result,'<comment><body><![CDATA[',body,']]></body>','<replier>',if(screen_name is not null and !anonymous,screen_name,''),'</replier>','<created>',created,'</created></comment>') SEPARATOR '')
FROM idiscussion
LEFT JOIN
 users
ON idiscussion.uid=users.id
WHERE idiscussion.iask_id = @_iask_id

Thank you for you reply,tried, but with no progress,what a pity!
So why is this the answer then? What was reason for your problem?

CollectivesTM on Stack Overflow

Why does this MySQL function return null?

4 Answers 4

3 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Post as a guest

Hot Network Questions

CollectivesTM on Stack Overflow

4 Answers 4

3 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Post as a guest

Related