使用 RAND() 和 INNER JOIN 的 MySQL 子查询不返回任何行

Question

我有一个对象列表和一个位置列表，我想将一个对象随机放置在一个位置。

CREATE TABLE so_object (
    `id` INT,
    `name` TINYTEXT,
    PRIMARY KEY (`id`)
);
CREATE TABLE so_place (
    `id` INT,
    `name` TINYTEXT,
    PRIMARY KEY (`id`)
);

TRUNCATE TABLE `so_object`;
TRUNCATE TABLE `so_place`;

INSERT INTO `so_object` VALUES (1, 'banana'), (2, 'apple'), (3, 'chocolate'), (4, 'milk'), (5, 'phone');
INSERT INTO `so_place` VALUES (1, 'room'), (4, 'kitchen'), (7, 'living'), (8, 'cave');

然后，我选择对象表的最大 id，并为该对象分配一个随机整数。

SET @idMax := (SELECT MAX(id) FROM so_place);
SELECT @idMax;

SELECT
    FLOOR(RAND()*@idMax+1) AS id_place, id, name
FROM
    so_object
;

然后，我检查指定的整数是否存在于该位置

SELECT
    *
FROM (
    SELECT
        FLOOR(RAND()*@idMax+1) AS id_place, id, name
    FROM
        so_object
) AS t
INNER JOIN so_place AS p
    ON p.id = t.id_place
;

so_place 表中存在漏洞，因此我执行 INNER JOIN 来确保该位置存在。我希望一个对象不会被放置在任何地方(即:如果它选择的随机数位于 so_place 洞中)。我希望一个地方可以是空的，或者可以包含两个对象。

当你在像这两个这样的小 table 上尝试时，它看起来工作得很好。但是我添加的位置越多，返回的行就越少:

INSERT INTO so_place VALUES
    (9, 'room 9'),
    (10, 'room 10'),
    (11, 'room 11'),
    (12, 'room 12'),
    (13, 'room 13'),
    (14, 'room 14'),
    (15, 'room 15'),
    (16, 'room 16'),
    (17, 'room 17'),
    (18, 'room 18'),
    (19, 'room 19'),
    (20, 'room 20'),
    (21, 'room 21'),
    (22, 'room 22'),
    (23, 'room 23'),
    (24, 'room 24'),
    (25, 'room 25'),
    (26, 'room 26'),
    (27, 'room 27'),
    (28, 'room 28'),
    (29, 'room 29'),
    (30, 'room 30'),
    (31, 'room 31'),
    (32, 'room 32'),
    (33, 'room 33'),
    (34, 'room 34'),
    (35, 'room 35'),
    (36, 'room 36'),
    (37, 'room 37'),
    (38, 'room 38'),
    (39, 'room 39'),
    (40, 'room 40'),
    (41, 'room 41'),
    (42, 'room 42'),
    (43, 'room 43'),
    (44, 'room 44'),
    (45, 'room 45'),
    (46, 'room 46'),
    (47, 'room 47'),
    (48, 'room 48'),
    (49, 'room 49'),
    (50, 'room 50'),
    (51, 'room 51'),
    (52, 'room 52'),
    (53, 'room 53'),
    (54, 'room 54'),
    (55, 'room 55'),
    (56, 'room 56'),
    (57, 'room 57'),
    (58, 'room 58'),
    (59, 'room 59');

这没有任何意义，因为 so_place 没有更多的漏洞。事实上，我怀疑 MySQL 引擎首先解析地点表，然后选择随机整数，并且仅在随机整数与地点的 id 匹配时才保留该行(其中随着添加的位置越多，真实的可能性就越小)。

此查询在 MySQL 5.6.25 和 MySQL 5.5.24 中“工作正常”(即:MySQL 首先解析嵌套表，然后进行内部联接，并且仅保留内部表中的行(如果它们与某个位置匹配))但在MySQL 5.7.10，它不再工作。

我不知道这是否是一个“MySQL 5.7.10 bug”，或者是否是一个预期的 SQL 结果(然后，以前的版本出现了 bug，新版本被“修复”)。我不知道如何恢复 MySQL 5.5/5.6 的行为，因此欢迎任何查询修复或其他具有相同含义的查询。

<小时/>

在那晚 sleep 之后，EXPLAIN 显示 MySQL 进行了中间简化:*

id  select_type table   type    rows    filtered    Extra
1   SIMPLE  so_object   ALL     5       100.00      \N
1   SIMPLE  p           ALL     55      10.00       Using where; Using join buffer (Block Nested Loop)

t 表不会出现。那么如何强制MySQL做中间表，因为这里的查询优化器优化太多而破坏了查询结果？

更新:根据MySQL 5.7 Doc ，查询优化器现在不再实现子查询(生成我需要的临时表)。因此，我可以通过使用 SET Optimizer_switch = '衍生_merge=off'; 停用此行为来解决问题，但我有点不喜欢这样做，因为我需要在执行查询后重新激活此选项。

Answer 1

我会回答自己，即使这个解决方案看起来更像是一个黑客。至少，解释很清楚:

As of MySQL 5.7.6, the optimizer handles derived tables and view references the same way: It avoids unnecessary materialization whenever possible. [...] Before MySQL 5.7.6, derived tables were always materialized

https://dev.mysql.com/doc/refman/5.7/en/subquery-optimization.html#derived-table-optimization

因此，查询在内部被翻译为“为每个对象选择所有地点，选择随机数，并且它与地点的 id 匹配，然后为该对象保留该地点”。名额越多，比赛的机会就越少，因此出现“不排有时排”的现象。 EXPLAIN 非常清楚地显示了这一点:

id  select_type table   type    rows    filtered    Extra
1   SIMPLE  so_object   ALL     5       100.00      \N
1   SIMPLE  p           ALL     55      10.00       Using where; Using join buffer (Block Nested Loop)

子查询不会生成临时表(未具体化)，而以前的版本会生成临时表。

强制具体化子查询(因此，仅评估 RAND() 一次)的唯一方法是使其 DISTINCT:

Constructs that prevent merging are the same as those that prevent merging in views. Examples are SELECT DISTINCT or LIMIT in the subquery.

现在是查询

SET @idMax := (SELECT MAX(id) FROM so_place);
SELECT
    *
FROM (
    SELECT DISTINCT
        FLOOR(RAND()*@idMax+1) AS id_place, id, name
    FROM
        so_object
) AS t
INNER JOIN so_place AS p
    ON p.id = t.id_place
;

它返回一个对象列表，其中“几乎每个对象”都与一个位置匹配，并且位置可以与 0、1 个或多个对象匹配。

id_place    id  name        id  name
16          1   banana      16  room 16
25          3   chocolate   25  room 25
16          4   milk        31  room 16
22          5   phone       22  room 22