mysql - SELECT 中变量分配评估的顺序可能不同于返回行的顺序。在什么情况下会发生这种情况？

Question

我最近尝试使用用户定义的变量从我的结果集中返回的最后行捕获一些信息。

我的意思是，例如，如果我有一个从“Aaron”到“Zzarx”的名字列表，

SELECT @n:=Name FROM people ORDER BY Name;
SELECT @n;

第二个 SELECT 应该返回“Zzarx”。

这是最简单的情况。它按预期工作；变量赋值可靠地以与行发送到客户端相同的顺序发生，因此最后的赋值对应于最后返回的行。

但是当查询更复杂时似乎会发生奇怪的事情:

SELECT DISTINCT IFNULL(@n:=Name,'unknown') FROM people ORDER BY <some non-indexed expression> LIMIT 10;
SELECT @n;

在 MariaDB v10.3.16 上执行类似的操作我得到 @n 的最终值(来自第二个 SELECT)它不对应于第一个返回的任何行SELECT!。 (注意 Name 是一个 NOT NULL 列，所以 IFNULL() 实际上是多余的，但仍然是触发此行为所必需的)。

请注意，它似乎只有在满足以下所有条件时才会发生:

• 选择不同的
• ORDER BY 不能使用索引
• 变量赋值发生在一些表达式中

我的理论是:

• SELECT DISTINCT 强制对返回的列表达式进行早期评估。
• ORDER BY(非索引表达式) 在评估列数据后强制执行显式排序操作。
• SQL 引擎足够智能，可以识别简单的 SELECT @var := (expression) 模式并仅在行发送到客户端时评估 @var，但无法进行优化如果 @var:=... 赋值被嵌入到一个更大的表达式中，就像我示例中的 IFNULL() 一样。

然而，这一切都只是猜测。用户定义变量的手册页在这方面并没有真正说明任何有用的内容(MySQL 和 MariaDB 都没有)。

在我看来，在多行查询中使用@variable 从最后返回的行中捕获某些内容是一个有用且可能很常见的技巧，但现在我不确定是否或何时可以依赖它。类似地，对于许多行编号和其他聪明的方案，我已经看到在 SELECT 的结果集部分中使用 @variables。

SO 上有人有关于它应该如何工作的任何明确信息吗，具体来说，在什么条件下，行变量赋值表达式的评估顺序将保证与行的实际顺序相对应返回了？

...因为这似乎是一件非常重要的事情!

---

另一个稍微不那么病态的例子:

假设表 t 有 1000 行:

SET @n:=0;
SELECT @n:=@n+1 FROM t ORDER BY 1 DESC LIMIT 5;
SELECT @n;

返回的结果集是:

1000
 999
 998
 997
 996

和

1000

请再次注意，@n 的最终值不对应于返回的最后一行，而且确实考虑到查询的语义，在这种情况下它不能。

Answer 1

虽然您没有使用 8.0.13，但很快就会推出以下版本。你已经找到了它到来的原因。

----- 2018-10-22 8.0.13 General Availability -- -- Important Change -----

Setting user variables in statements other than SET is now deprecated due to issues that included those listed here:

The order of evaluation for expressions involving user variables was undefined.

The default result type of a variable is based on its type at the beginning of the statement, which could have unintended effects when a variable holding a value of one type at the beginning of a statement was assigned a new value of a different type in the same statement.

HAVING, GROUP BY, and ORDER BY clauses, when referring to a variable that was assigned a value in the select expression list, did not work as expected because the expression was evaluated on the client and so it was possible for stale column values from a previous row to be used.

Syntax such as SELECT @var, @var:=@var+1 is still accepted in MySQL 8.0 for backward compatibility, but is subject to removal in a future release.

-- 来自“变更日志”。

DISTINCT 类似于GROUP BY。

SELECT @v := ... FROM t ORDER BY x;

情况 1:INDEX(x) 但优化器可能会选择获取行，然后对它们进行排序。

情况 2:INDEX(x) 并且优化器选择根据索引获取行。

SELECT @v := ... FROM t  GROUP BY w  ORDER BY x;

这几乎肯定需要生成一个临时表(用于排序)，也许两个(一个用于分组，一个用于排序)。运行查询的唯一合理方法是计算 SELECT 中的表达式(包括 @v)，收集结果，然后进行分组和排序。因此，评估顺序不太可能是 x 的评估顺序。但它可能模仿w。

PARTITIONing 怎么样？目前，MySQL 对 SELECT 的计算没有并行性。但是，如果它出现了呢？让我们来看一个“明显”的情况——不同的线程在表的不同 PARTITIONs 上工作。所有赌注都按评估顺序关闭。

一旦实现，如何拆分甚至未分区的 SELECT 以获得一些并行性？

你不会赢得争论。

是的，它可能会在很长一段时间内保持“弃用”状态。或者可能会有一个 sql_mode 以“旧”方式运行查询。或者 @variables 的存在会抑制某些优化(有利于可预测性)。等等

我可以建议您在 bugs.mysql.com 上写一个“功能请求”，说明您希望看到的内容。 (你也可以在 mariadb.com 上做，但他们看的是前者。)