MySQL if(cond, a, b) 使用内存表时不一致？

Question

我在查询 MySQL 中的表时得到了一些不一致的结果。为了演示的目的，我已经尽可能地精简了代码。

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;

似乎如果我不从 numberfill 中选择，查询会给出一致的结果。但是，当我从 numberfill 表中进行选择时，我得到了不同的结果。有些行给出 0, 0，有些行给出 5,5，有些行给出 5, 0 或 0, 5。

谁能看出这是为什么？这是一个 MySQL 问题还是我在做一些导致未定义行为的事情？我认为这可能与 rand() 产生 float 有关。

Answer 1

当您多次引用 a 时会出现此问题。显然 MySql (v 5.7) 决定根据其定义重新评估 a，即它再次执行 rand() 以检索 a.

它与 Memory 选项无关，也与 if 函数无关。以下查询将在每一行中返回两个不同的值:

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

您可以通过将 rand() 分配给一个变量来避免这种情况，并为列别名 a 选择该变量:

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

该查询将始终返回两个值相同的记录。

另一种强制实现评估的方法是对内部查询设置限制(值高于 numberfill 中的行数):

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

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