Chantry Cargill Chantry Cargill - 1 month ago 7
MySQL Question

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

I am getting some inconsistent results querying a table in MySQL. I have stripped down the code as far as possible for 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 DO
INSERT INTO
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)
from (select cast(round(rand()) as unsigned) as a from numberfill) k;


It seems that if I do not select from numberfill, the query gives consistent results. However, when I select from the numberfill table I get mixed results. Some rows give 0, 0 and others give 5,5, and others give 5, 0 or 0, 5.

Can anyone spot why this is the case? Is it a MySQL problem or am I doing something that causes undefined behavior? I'm thinking it might have something to do with rand() producing a float.

Answer Source

The issue occurs when you reference a more than once. Apparently MySql (v 5.7) decides to re-evaluate a according to its definition, i.e. it executes rand() again to retrieve the value for a.

It is not related to the Memory option, nor to the if function. The following query will return two different values in each row:

select a, a
from (select rand() as a from numberfill) k

You can avoid this by assigning rand() to a variable, and select that variable for the column alias a:

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

That query will always return records where the two values are the same.

Another way to force the evaluation to be materialised is to set a limit to the inner query (with a value higher than the number of rows in numberfill):

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

See also Bug #86624, Subquery's RAND() column re-evaluated at every reference.