MySQL if(cond, a, b) inconsistent when using memory table? -

i getting inconsistent results querying table in mysql. have stripped down code far possible purpose of demonstration.

drop table if exists numberfill; create table numberfill (     id int not null primary key auto_increment ) engine=memory;  drop procedure if exists populate; delimiter $$ create procedure populate(numberrows int) begin     declare counter int;     set counter = 1;     while counter <= numberrows         insert             numberfill         select             counter;         set counter = counter + 1;     end while; end $$ delimiter ;  start transaction; call populate(5000); commit;  select if(a = 1, 5, 0), if(a = 1, 0, 5) (select cast(round(rand()) unsigned) numberfill) k; 

it seems if not select numberfill, query gives consistent results. however, when select numberfill table mixed results. rows give 0, 0 , others give 5,5, , others give 5, 0 or 0, 5.

can spot why case? mysql problem or doing causes undefined behavior? i'm thinking might have rand() producing float.

the issue occurs when reference a more once. apparently mysql (v 5.7) decides re-evaluate a according definition, i.e. executes rand() again retrieve value a.

it not related memory option, nor if function. following query return 2 different values in each row:

select a, (select rand() numberfill) k 

you can avoid assigning rand() variable, , select variable column alias a:

select a, (select @a:=rand() numberfill) k 

that query return records 2 values same.

another way force evaluation materialised set limit inner query (with value higher number of rows in numberfill):

select a, (select rand() numberfill limit 9999999999) k 

see bug #86624, subquery's rand() column re-evaluated @ every reference.