主页 / dba / 问题 / 184149
Accepted
Vlad Mihalcea
Asked: 2017-08-24 01:11:22 +0800 CST

对于这个特定的冗余派生表，是否真的有可能无法保证订单？

  • 772

我在与 Lukas Eder 的 Twitter 对话中偶然发现了这个问题。

虽然正确的行为是在最外层查询中应用 ORDER BY 子句，因为在这里，我们没有在最外层查询中使用 DISTINCT、GROUP BY、JOIN 或任何其他 WHERE 子句，为什么 RDBMS 不只是通过传入数据，因为它是由内部查询排序的？

SELECT * 
FROM (
    SELECT * FROM table ORDER BY time DESC
) AS t

至少，在 PostgreSQL 上运行此示例时，您会为内部查询和此派生表示例获得相同的执行计划，以及相同的结果集。

因此，我假设 Planner 将简单地丢弃最外层的查询，因为它是多余的，或者只是传递来自内部表的结果。

有没有人认为情况可能并非如此？

mysql postgresql

4 个回答

  1. Best Answer

    大多数数据库都非常清楚ORDER BY子查询中的 an 是：

    • 不允许：例如 SQL Server、Sybase SQL Anywhere（除非用TOPor补充OFFSET .. FETCH）
    • OFFSET .. FETCH无意义：例如 PostgreSQL、DB2（同样，除非用or补充LIMIT）

    这是 DB2 LUW 手册中的一个示例（重点是我的）

    子选择中的 ORDER BY 子句不会影响查询返回的行的顺序。如果在最外层的全查询中指定了 ORDER BY 子句，它只会影响返回的行的顺序。

    措辞非常明确，就像 PostgreSQL 的一样：

    如果未选择排序，则将按未指定的顺序返回行。这种情况下的实际顺序将取决于扫描和连接计划类型以及磁盘上的顺序，但不能依赖它。只有明确选择了排序步骤，才能保证特定的输出排序。

    从这个规范中，可以看出ORDER BY派生表中的子句产生的任何排序仅仅是偶然的，并且可能巧合地匹配您的预期排序（在您的简单示例中的大多数数据库中都是如此），但是依赖它是不明智的这个。

    关于 DB2 的旁注：

    特别是，DB2 有一个鲜为人知的特性，称为ORDER BY ORDER OF <table-designator>，可以按如下方式使用：

    SELECT C1 FROM
   (SELECT C1 FROM T1
      UNION
    SELECT C1 FROM T2
    ORDER BY C1 ) AS UTABLE
ORDER BY ORDER OF UTABLE

    在这种特殊情况下，派生表的顺序可以在最外层的 SELECT 中显式重用

    关于甲骨文的旁注：

    多年来，Oracle 的一种做法是OFFSET使用 实现分页，只有在对派生表排序后ROWNUM才能合理计算：

    SELECT *
FROM (
  SELECT rownum AS rn, t.* -- ROWNUM here depends on the derived table's ordering
  FROM (
    SELECT * FROM table ORDER BY time DESC
  ) t
) t
WHERE rn BETWEEN 10 AND 20

    可以合理地预期，至少在ROWNUM查询中，未来的 Oracle 版本不会破坏这种行为，以免破坏几乎所有遗留的 Oracle SQL，这些遗留 SQL 尚未迁移到更理想的和可读的 SQL 标准OFFSET .. FETCH语法：

    SELECT * FROM table ORDER BY time DESC OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY
    • 20

  2. 是的。如果没有ORDER BY子句，则输出顺序是未定义的，查询规划器完全在其权限范围内，假设您知道并理解这一点。

    它可能会决定，因为外部查询没有指定顺序，它可以在内部查询中删除排序以避免排序操作，特别是在没有聚集索引或根本没有索引来支持排序的情况下。如果现在没有，它可能会在未来的版本中使用。

    永远不要依赖未定义的行为。如果您需要特定的顺序，请ORDER BY在适当的位置给出一个子句。

    • 12

  3. 它是未定义行为的真正问题 - 为您工作，为我工作，在产品中重新格式化 HDD ;)

    我们可以退后一步说，在某种意义上你是对的——没有任何理智的 RDBMS 会重新排列内部选择中的行的世俗理由。但它不能保证 - 这意味着将来可能会有一个原因，供应商可以自由地这样做。这意味着任何依赖此行为的代码都受供应商可能做出的更改的支配，他们没有义务公开，因为这不是 API POV 的重大更改。

    • 6

  4. 对于这个特定的冗余派生表，是否真的有可能无法保证订单？

    所有当前存在的Postgres（您正在测试的）版本的答案是：否。对于给定的查询，排序顺序是有保证的。

    SQL 服务器的人会对此感到不舒服，因为微软甚至不允许ORDER BY子查询。尽管如此，Postgres 中的这个简单查询的排序顺序是有保证的。ORDER BY在子查询中应用，外部查询不做任何可能改变顺序的事情。

    该手册甚至在Aggregate Functions一章中也有同样的提示：

    或者，从排序的子查询中提供输入值通常会起作用。

    请注意，这仅适用于外部查询级别不添加可能更改顺序的操作。所以它只是简单情况下的“保证”，并且不受 SQL 标准的支持。如果适合进行其他操作，Postgres 可以自由重新排序。如有疑问ORDER BY，请在外部添加另一个SELECT。（在这种情况下，ORDER BY对于这个简单的查询，内部将是多余的噪音。）

    • 3

相关问题