Bug #94813 Variable assignment in select taking the previous value of the variable
Submitted: 28 Mar 2019 10:57 Modified: 28 Mar 2019 13:46
Reporter: Arjun Ramachandra Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version: OS:Any
Assigned to: CPU Architecture:Any

[28 Mar 2019 10:57] Arjun Ramachandra
Description:
When i try to do operations on an assigned variable in select query, it is taking the value of previous time i executed the query in the same session.

I am trying to find the nearest multiple of 15 table as below.

select @avrt:= ceil(avg(COLUMN1))  ,if((@avrt%15 = 0) , (@avrt),  (@avrt + (15 -(@avrt%15))) ) as nearestMultiple 
from TABLE_NAME where COLUMN1 is not null;

How to repeat:
Add multiple numbers to a column and try to find the nearest number divisible by 15 by executing the below query. It does not execute properly the first time. Second time , it does display the value correctly

select @avrt:= ceil(avg(COLUMN1))  ,if((@avrt%15 = 0) , (@avrt),  (@avrt + (15 -(@avrt%15))) ) as nearestMultiple 
from TABLE_NAME where COLUMN1 is not null;
[28 Mar 2019 13:46] MySQL Verification Team
Hi,

Thank you for your bug report.

However, this is not a bug, but a behaviour described in our Reference Manual. Simply, the order of expression evaluation in the SELECT list is not guaranteed, which is stipulated by SQL standard.

Try first to SET the variable and then use it in the SELECT query.