is this usage of User-defined Variables guaranteed?

You are viewing an old version of this question. View the current version here.

hi.

I need to always have the same results for the following query even if some execution plan is changed.(in mariaDB 5.5)

SELECT A.*

,IF(@PREV_JOB=JOB,@ROWNUMBER:=@ROWNUMBER+1,@ROWNUMBER:=1+LEAST(0,@PREV_JOB:=JOB)) AS ROWNUMBER

FROM (SELECT EMP.*

FROM EMP

,(SELECT @ROWNUMBER:=0, @PREV_JOB:='') X

ORDER BY JOB, SAL) A;

this query was "SELECT A.*, ROW_NUMBER() OVER(PARTITION BY JOB ORDER BY SAL) FROM EMP A;" in ORACLE DB.

I worry about that “It is unsafe to read a user-defined variable and set its value in the same statement (unless the command is SET), because the order of these actions is undefined.”(in document: User-defined Variables).

is this usage of User-defined Variables guaranteed?

If the execution plan was changed, can the result also be changed?

Thanks you.

Answer

Can you initialise the row number in a separate statement and avoid the warning. This URL may help http://www.xaprb.com/blog/2006/12/02/how-to-number-rows-in-mysql/ as it seem to provide an equivalent to ROW_NUMBER() OVER PARTITION.

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.