Subquery的rand()列对MySQL 5.7/8.0vs MySQL 5.6中的每一个重复选择进行重新评估

Question

我正在执行一个子查询，其中有一个包含随机数生成的计算列。在基本查询中，我选择了该列两次。MySQL 5.6和我预期的一样工作，计算值被调用一次并固定下来。5.7+/8.0+的执行似乎重新评估了每个选择的子查询的列值。这行为正确吗？我能做些什么来强制它在新版本的MySQL中按预期工作呢？

CREATE TABLE t (
  `id` BIGINT(20) NOT NULL PRIMARY KEY AUTO_INCREMENT
) ENGINE=InnoDB;

insert into t values();
insert into t values();
insert into t values();
insert into t values();
insert into t values();

SELECT  
        q.i,
        q.r,
        q.r
FROM    (
        SELECT  
                id AS i,
                (FLOOR(RAND(100) * 4)) AS r
        FROM t
        ) q;

MySQL 5.6收益率(值相同)：

+---+-----+-----+
| i |  r  |  r  |
+---+-----+-----+
| 1 |   0 |   0 |
| 2 |   2 |   2 |
| 3 |   3 |   3 |
| 4 |   2 |   2 |
| 5 |   1 |   1 |
+---+-----+-----+

5.7收益率(数值不同)：

+---+-----+-----+
| i |  r  |  r  |
+---+-----+-----+
| 1 |   0 |   2 |
| 2 |   3 |   2 |
| 3 |   1 |   1 |
| 4 |   2 |   1 |
| 5 |   2 |   0 |
+---+-----+-----+

Answer 1

正如MySQL 8.0.0里程碑版本可供使用中提到的，

在MySQL 5.6和更早版本中，派生表总是物化的。在5.7中，派生表在大多数情况下被合并到外部查询中，并在某些情况下物化。 ..。 通过优化器提示(WL#9307)支持合并派生表或视图-- Guilhem Bichot的这项工作允许用户控制派生表或视图是否将使用“合并”和“no_merge”提示进行合并或物化。

我认为这是我在较新版本的MySQL中观察到的行为的原因。上述提示可与MySQL 8.0一起使用，以强制只调用一次RAND()：

SELECT  /* NO_MERGE(q) */
        q.i,
        q.r,
        q.r
FROM    (
        SELECT 
                id AS i,
                (FLOOR(RAND(100) * 4)) AS r
        FROM t
        ) AS q;

+---+-----+-----+
| i |  r  |  r  |
+---+-----+-----+
| 1 |   0 |   0 |
| 2 |   2 |   2 |
| 3 |   3 |   3 |
| 4 |   2 |   2 |
| 5 |   1 |   1 |
+---+-----+-----+

但是，5.7中没有这方面的资料。要实现5.7所需的行为，请将LIMIT <a very high number>添加到派生表定义中(我在下面使用签名的LONG_MAX )。感谢罗伊·莱森的这次解决办法。

SELECT
        q.i,
        q.r,
        q.r
FROM    (
        SELECT 
                id AS i,
                (FLOOR(RAND(100) * 4)) AS r
        FROM t LIMIT 9223372036854775807
        ) AS q;

+---+-----+-----+
| i |  r  |  r  |
+---+-----+-----+
| 1 |   0 |   0 |
| 2 |   2 |   2 |
| 3 |   3 |   3 |
| 4 |   2 |   2 |
| 5 |   1 |   1 |
+---+-----+-----+

正如注释中提到的那样，无论应用什么优化，都必须严格定义查询表达式的结果。这意味着它是MySQL 5.7/8.0中的优化器错误。