ansaurus

Question

Why does this MySQL function return null?

Answer 1

+1 A:

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

Quassnoi 2009-05-27 17:50:51

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

Shore 2009-05-27 17:54:19

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

VVS 2009-05-27 20:10:10

The query in the cursor selected variables, not columns

Quassnoi 2009-05-27 20:28:53

Answer 2

+1 A:

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.

Bill Karwin 2009-05-27 18:39:01

Answer 3

A:

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?

VVS 2009-05-27 18:39:21

Answer 4

A:

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.

2009-08-26 23:10:33

Answer 5

A:

Listen to Bill - He knows why you should care about NULL ;o)

Mike 2010-06-05 11:05:11

ansaurus

tags:

views:

answers:

Why does this MySQL function return null?

related questions