MySQL 5.7.5+ 获取组的第一行

Question

我有一个遗留应用程序，其中 Group By 与非聚合列一起用于获取每个组的第一行。查询如下:

SELECT
    columnPrimaryKey,
    column1,
    column2,
    column3
FROM
    (SELECT
        columnPrimaryKey,
        column1,
        column2,
        column3
    FROM testTable
    ORDER BY column2
) AS tbl
GROUP BY column3

最近，版本更新到 5.7.22，现在即使禁用 ONLY_FULL_GROUP_BY 模式，上述查询也不会返回预期结果。

是的，我可以按如下方式重写查询以根据新行为工作:

SELECT
    x.columnPrimaryKey,
    x.column1,
    x.column2,
    x.column3
FROM tableName AS x INNER JOIN (
    SELECT
        MIN( column2 ) AS column2,
        column3
    FROM tableName
    GROUP BY column3
) AS y ON x.column2 = y.column2 AND x.column3 = y.column3;

不幸的是，目前这不是一个选项。我看到的唯一选择是提前降级到 5.7.5。

禁用“ONLY_FULL_GROUP_BY”和意外结果的 Fiddle 5.7:

https://www.db-fiddle.com/f/8VjB7XpkobWVyXpPvUaGt2/0

Fiddle 5.6 的默认模式和预期结果:

https://www.db-fiddle.com/f/8VjB7XpkobWVyXpPvUaGt2/1

我的问题是:是否有任何方法可以禁用这种随机选择行为，以便遗留代码无需重写或降级即可工作？

非常感谢任何建议!

Answer 1

派生表子查询中的 ORDER BY 在 MySQL 5.7 中被忽略。

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

The optimizer propagates an ORDER BY clause in a derived table or view reference to the outer query block if these conditions are all true:

• The outer query is not grouped or aggregated.

• The outer query does not specify DISTINCT, HAVING, or ORDER BY.

• The outer query has this derived table or view reference as the only source in the FROM clause.

Otherwise, the optimizer ignores the ORDER BY clause.

您的外部查询有一个 JOIN 和一个 GROUP BY，因此它不符合传播 ORDER BY 的条件，因此它会忽略 ORDER BY。

此优化器行为由优化器开关 derived_merge 控制。您可以禁用它。

演示:

mysql [localhost] {msandbox} (test) > select @@version;
+-----------+
| @@version |
+-----------+
| 5.7.21    |
+-----------+

mysql [localhost] {msandbox} (test) > SELECT     columnPrimaryKey,     column1,     column2,     column3 FROM     (SELECT         columnPrimaryKey,         column1,         column2,         column3     FROM testTable     ORDER BY column2 ) AS tbl GROUP BY column3;
+------------------+----------------+---------+---------+
| columnPrimaryKey | column1        | column2 | column3 |
+------------------+----------------+---------+---------+
|                1 | Some Name 8-4  |       4 |       8 |
|                6 | Some Name 9-1  |       1 |       9 |
|                8 | Some Name 10-2 |       2 |      10 |
+------------------+----------------+---------+---------+

mysql [localhost] {msandbox} (test) > set optimizer_switch = 'derived_merge=off';
Query OK, 0 rows affected (0.00 sec)

mysql [localhost] {msandbox} (test) > SELECT     columnPrimaryKey,     column1,     column2,     column3 FROM     (SELECT         columnPrimaryKey,         column1,         column2,         column3     FROM testTable     ORDER BY column2 ) AS tbl GROUP BY column3;
+------------------+----------------+---------+---------+
| columnPrimaryKey | column1        | column2 | column3 |
+------------------+----------------+---------+---------+
|                5 | Some Name 8-1  |       1 |       8 |
|                6 | Some Name 9-1  |       1 |       9 |
|                8 | Some Name 10-2 |       2 |      10 |
+------------------+----------------+---------+---------+