is this usage of User-defined Variables guaranteed?
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.