MySQL Join 给出不一致的结果

Question

我正在尝试计划我的膳食:

select * from 
    (select floor(rand() * 3) + 1 as rand_id, days1.* from (
          select 'Monday' as dy from dual
    union select 'Tuesday' from dual
    union select 'Wednesday' from dual
    union select 'Thursday' from dual
    union select 'Friday' from dual
    ) days1) days
left join 
    (select id as rand_id, meals1.* from  (
          select 1 as id, 'Pizza' as dinner  from dual
    union select 2, 'Hotdogs' from dual
    union select 3, 'Spaghetti' from dual)meals1) meals
on days.rand_id = meals.rand_id;

当我在 SQL Fiddle it works fine 上运行此查询时，但是当我用我的本地 mysql 实例尝试它时，我得到了完全乱码的结果:随机加入的随机行数:

+---------+-----------+---------+------+---------+
| rand_id | dy        | rand_id | id   | dinner  |
+---------+-----------+---------+------+---------+
|       1 | Wednesday |       2 |    2 | Hotdogs |
|       1 | Monday    |    NULL | NULL | NULL    |
|       3 | Tuesday   |    NULL | NULL | NULL    |
|       3 | Friday    |    NULL | NULL | NULL    |
+---------+-----------+---------+------+---------+

或

+---------+-----------+---------+------+-----------+
| rand_id | dy        | rand_id | id   | dinner    |
+---------+-----------+---------+------+-----------+
|       3 | Wednesday |       1 |    1 | Pizza     |
|       1 | Wednesday |       3 |    3 | Spaghetti |
|       2 | Thursday  |    NULL | NULL | NULL      |
|       3 | Friday    |    NULL | NULL | NULL      |
+---------+-----------+---------+------+-----------+

我希望看到的是 5 行，每行在 rand_id 中有一个 1 到 3 之间的随机数，days.rand_id 和 meals.rand_id 是相同的。我希望每次运行查询时，我都会在一周中的每一天得到一行随机选择的一餐。给我这个输出的本地 mysql(但不是 sqlfiddle 的 mysql)可能出了什么问题？

(注意:最初的目标是将真实客户记录与虚假客户数据随机链接以生成测试数据，但我已针对此示例进行了简化)

Answer 1

这似乎是与使用 rand() 加入有关的错误。这可能与 Bug #84573 Call to rand() in a [condition] can cause an empty set to be erroneously returned 中描述的错误相同从 2017 年 1 月开始，虽然我不确定它是否得到了适当的关注，所以也许重新报告它。您可以使用下面的代码。

减少代码以在 MySQL 5.6、5.7 和 8.0(但不是 5.5 和更早版本)中重现错误:

create table a (id int primary key);
insert into a values (1), (2);

create table b (id int primary key);
insert into b values (1);

select * from a left join b on rand(0) > 0.5;
+----+------+
| id | id   |
+----+------+
|  2 |    1 |
|  1 | NULL |
+----+------+
2 rows in set (0.00 sec)    

select * from a left join b on rand(1) > 0.5;
+----+------+
| id | id   |
+----+------+
|  1 | NULL |
+----+------+
1 row in set (0.00 sec)

select * from a left join b on rand(14) > 0.5;
Empty set (0.00 sec)

所有查询的预期结果是始终获得两个(左)行并在第二列中随机获得 null 或 1。

减少的代码也会在 SQL Fiddle(使用 MySQL 5.6)上产生错误。您的查询在 SQL Fiddle 上运行的原因似乎是 MySQL 5.6 将具体化您的子查询(而不是合并它)，而 5.7 将默认合并它。

因此，在您的情况下，解决方法可以是实现使用 rand() 的子查询的任何方法(尽管该错误可能与实现没有直接关系)。设置/切换该行为的一种简单方法是使用 View ，因此请尝试例如在 MySQL 5.7 中(否则你不能在 View 中使用子查询):

create algorithm=merge view view_days1
as select floor(rand() * 3) + 1 as rand_id, days1.* from (
          select 'Monday' as dy from dual
    union select 'Tuesday' from dual
    union select 'Wednesday' from dual
    union select 'Thursday' from dual
    union select 'Friday' from dual
    ) days1;

select * from view_days1 days
left join 
    (select id as rand_id, meals1.* from  (
          select 1 as id, 'Pizza' as dinner  from dual
    union select 2, 'Hotdogs' from dual
    union select 3, 'Spaghetti' from dual)meals1) meals
on days.rand_id = meals.rand_id;

在使用时会出现同样的错误行为

create algorithm=temptable view view_days1 as ...

应该可以正常工作。