对 MySQL 5.7/8.0 与 MySQL 5.6 中的每个重复选择重新评估子查询的 rand() 列

2022-01-23 00:00:00 sql subquery mysql materialized-views derived-table

我正在做一个子查询，其中我有一个涉及随机数生成的计算列.在基本查询中，我选择了此列两次.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 8.0.0 Milestone Release 可用,

在 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 的这项工作允许用户使用merge"和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.

相关文章