Mysql stored procedure returns null only for the first time

Question

Here is a table called code .

p_code column is the parent code

And the stored procedure should return a record set joined by p_code as follows:

The procedure works well, except for the first time (after reconnecting MYSQL, it returns null!). What is wrong with him?

The procedure is stored here.

  BEGIN DECLARE _CODE bigint(20); DECLARE _P_CODE bigint(20); DECLARE _SORT bigint(20); DECLARE _pre_P_CODE bigint(20); DECLARE CONTINUE HANDLER FOR NOT FOUND SET @CODE = NULL; SET _P_CODE = @CODE; SET _CODE = ''; SET _SORT = 0; SET _pre_P_CODE = ''; IF @CODE IS NULL THEN RETURN NULL; END IF; LOOP SELECT CODE, P_CODE, SORT, CODE_NAME, CODE_LEVEL INTO @CODE, @P_CODE, @SORT, @CODE_NAME, @CODE_LEVEL FROM CODE WHERE LANGUAGE = @LANGUAGE AND P_CODE = _P_CODE AND SORT > _SORT ORDER BY SORT limit 1; IF @CODE IS NOT NULL OR _P_CODE = @start_with THEN SET @level = @level + 1; RETURN @CODE; END IF; SET @level := @level - 1; SELECT CODE, P_CODE, SORT INTO _CODE, _P_CODE, _SORT FROM CODE WHERE CODE = _P_CODE; END LOOP; END 

And the procedure is called like that. This SQL will return the second image above.

 SELECT menu_connect_by_p_code() AS CODE, @level as level, @P_CODE as p_code, @SORT as sort, @CODE_NAME as CODE_NAME, @CODE_LEVEL as CODE_LEVEL FROM ( SELECT @start_with := 6001,@LANGUAGE := 'en' , @CODE := @start_with, @level := 0 ) vars, code WHERE @CODE IS NOT NULL 

I do not understand why it returns null for the first time . Is there something wrong with the procedure or the kind of MYSQL error?

------------------------------- edit --------------- --- ------------------

RolandoMySQLDBA, rsanchez, I tried my version, but I remain the same. It returns multiple rows, and some columns have a null value.

------------------------------- edit2 --------------- --- ------------------

You can see here: http://sqlfiddle.com/#!2/aa033/1

Answer 1

From the MySQL Reference Guide :

Generally, you should never assign a value to a user variable and read the value within the same statement. You can get the expected results, but this is not guaranteed. The evaluation order of expressions with user variables is undefined and can be changed based on the elements contained in this statement; furthermore, this order is not guaranteed to be the same between versions of MySQL Server.

Therefore, you can change:

 SELECT @start_with := 6001,@LANGUAGE := 'en' , @CODE := @start_with, @level := 0 

by:

 SELECT @start_with := 6001,@LANGUAGE := 'en' , @CODE := 6001, @level := 0 

Answer 2

I see the natural order of progression

Here is your request

 SELECT menu_connect_by_p_code() AS CODE, @level as level, @P_CODE as p_code, @SORT as sort, @CODE_NAME as CODE_NAME, @CODE_LEVEL as CODE_LEVEL FROM ( SELECT @start_with := 6001,@LANGUAGE := 'en' , @CODE := @start_with, @level := 0 ) vars, code WHERE @CODE IS NOT NULL 

The only way that @CODE is NULL very early is to first evaluate the stored procedure before calling the internal SELECT query.

Perhaps you could change the initialization order of the variable

 SET @start_with := 6001; SET @LANGUAGE := 'en'; SET @CODE := @start_with; SET @level := 0; SELECT menu_connect_by_p_code() AS CODE, @level as level, @P_CODE as p_code, @SORT as sort, @CODE_NAME as CODE_NAME, @CODE_LEVEL as CODE_LEVEL FROM code WHERE @CODE IS NOT NULL; 

Give it a try !!!

UPDATE 2013-02-01 16:18 EDT

The problem is this part of the stored procedure:

 IF @CODE IS NULL THEN RETURN NULL; END IF; 

Try commenting on these lines and see what happens !!!

Answer 3

The first is that a function is not a procedure.

Well, I tried the fiddle - it seems like it skips every alternative performance.

@CODE is NULL, and so your output is null when the assignment happens after a function call. Most likely this is what happens.

If Rolando is not working, you can do it. Well, this will add a little execution time and really more because of a hack. This should work since @Code cannot go to null

 SELECT * FROM (SELECT menu_connect_by_p_code() AS CODE, @level as level, @P_CODE as p_code, @SORT as sort, @CODE_NAME as CODE_NAME, @CODE_LEVEL as CODE_LEVEL FROM ( SELECT @start_with := 6001, @CODE := @start_with, @level := 0 ) vars, code WHERE @CODE IS NOT NULL) InnerQuery 

This may be a really trivial problem, but more research can be used.

Answer 4

Based on the fact stated in the rsanchez answer, the function call is in the SAME statement with "@level as level, ...", so it does not guarantee that the function call will precede this part.

The following statement may work:

  select CODE, @level as level, @P_CODE as p_code, @SORT as sort, @CODE_NAME as CODE_NAME, @CODE_LEVEL as CODE_LEVEL from ( SELECT menu_connect_by_p_code() AS CODE, FROM ( SELECT @start_with := 6001,@LANGUAGE := 'en' , @CODE := @start_with, @level := 0 ) vars, code WHERE @CODE IS NOT NULL ) a