The Ultimate Guide to High Availability with MariaDB

Comments - User-Defined Variables

9 years, 9 months ago Dongjae Shin
is this usage of User-defined Variables guaranteed?
This comment has the status of 'removed' and can only be seen by you.

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;

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.”.

is this usage of User-defined Variables guaranteed?

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

it’s very important to me… comment please.

Thanks you.

 
9 years, 9 months ago Federico Razzoli
Re: is this usage of User-defined Variables guaranteed?

In your case, I don't see any potential problems.

  • The subquery in which you initialize the variables should be constant.
  • The order of expressions passed to IF() is always the same.

I think that the statement you quoted refers to things like:

SELECT @a := field1, @b := @a + 1 FROM table;

 
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.