对于MySQL 5.7/8.0和MySQL 5.6中的每个重复选择，子查询的rand()列都会重新评估 [英] Subquery's rand() column re-evaluated for every repeated selection in MySQL 5.7/8.0 vs MySQL 5.6

问题描述

我正在做一个子查询，其中有一个涉及随机数生成的计算列.在基本查询中，我两次选择此列. MySQL 5.6可以正常工作，计算值被调用一次并固定. 5.7 +/8.0 +执行似乎为每个选择单独重新评估了子查询的列值.这是正确的行为吗?我该怎么做才能使其在新版本的MySQL中按预期工作?

I am doing a subquery in which I have a calculated column involving random number generation. In the base query I select this column twice. MySQL 5.6 works as I expect, the calculated value being called once and fixed. The 5.7+/8.0+ execution seems to re-evaluate the subquery's column value individually for each selection. Is this correct behavior? What can I do to force it work as expected in newer versions of 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产生(值相同):

MySQL 5.6 yields (values are the same):

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

而5.7会产生收益(值有所不同):

while 5.7 yields (values are different):

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

推荐答案

如

在MySQL 5.6和更早的版本中，派生表始终是实体化的.在 5.7，在大多数情况下，派生表会合并到外部查询中，在某些情况下会实现.

In MySQL 5.6 and earlier, derived tables were always materialized. In 5.7, derived tables are merged into the outer query in most cases, and materialized in some cases.

...

启用通过优化器提示合并派生表或视图(WL#9307)-Guilhem Bichot的这项工作允许用户使用合并"和"no_merge"提示来控制是否将派生表或视图合并或实现. .

Enabling merging a derived table or view through a optimizer hint (WL#9307) — This work by Guilhem Bichot allows users to control whether a derived table or view will be merged or materialized using the "merge" and "no_merge" hints.

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

I suppose that this is the cause of the behavior I am observing in newer versions of MySQL. The mentioned hint can be used with MySQL 8.0 to force RAND() be called only once:

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).感谢Roy Lyseng的解决方法.

This however is not available in 5.7. To achieve the desired behavior with 5.7, add LIMIT <a very high number> to the derived table definition (I'm using signed LONG_MAX below). Thanks to Roy Lyseng for this workaround.

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 |
+---+-----+-----+

正如注释中提到的 philipxy 一样，无论是否应用任何优化，都必须严格定义查询表达式的结果.这意味着它是MySQL 5.7/8.0中的优化程序错误.

As philipxy mentioned in the comment, the result of a query expression must be strictly defined regardless of any optimizations being applied. Which means it is an optimizer bug in MySQL 5.7/8.0.

这篇关于对于MySQL 5.7/8.0和MySQL 5.6中的每个重复选择，子查询的rand()列都会重新评估的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！