Why does this MySQL function return null?

Question

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?

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. — Noldorin
– Noldorin, Commented May 27, 2009 at 17:48
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. — Dan Rosenstark
– Dan Rosenstark, Commented May 27, 2009 at 18:13

Quassnoi · Accepted Answer · 2009-05-27 20:04:30Z

3

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

edited May 27, 2009 at 20:04

answered May 27, 2009 at 17:50

Quassnoi

427k94 gold badges628 silver badges623 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

omg Over a year ago

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

VVS Over a year ago

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

Quassnoi Over a year ago

The query in the cursor selected variables, not columns

Bill Karwin · Accepted Answer · 2009-05-27 18:39:01Z

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.

C B · Accepted Answer · 2017-09-27 18:38:37Z

1

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.

edited Sep 27, 2017 at 18:38

C B

1,7176 gold badges20 silver badges20 bronze badges

answered Aug 26, 2009 at 23:10

Norman mckruger

Comments

VVS · Accepted Answer · 2009-05-27 18:39:21Z

0

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?

answered May 27, 2009 at 18:39

VVS

19.6k5 gold badges50 silver badges66 bronze badges

Collectives™ on Stack Overflow

Why does this MySQL function return null?

4 Answers 4

3 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related