Why does this MySQL function return null?
- by Shore
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?
