来源 https://boringsql.com/posts/order-by-jungle/欢迎来到 ORDER BY 丛林作者:Radim Marek日期:2026-05-15阅读时间:11 分钟目录SQL 很有趣一点也不枯燥。Markus Winand 的最新文章《Order by 已经走过了漫长的道路》让我开启了一段相当精彩的旅程。首先创建一个名为nums的表包含一个整数列和四行数据CREATETABLEnums(aint);INSERTINTOnumsVALUES(0),(1),(2),(3);试着猜猜下面两个查询返回什么结果。SELECT-aASaFROMnumsORDERBYa;SELECT-aASaFROMnumsORDERBY-a;我们大多数人会猜测它们返回相同的行但顺序不同。实际的答案是它们以完全相同的顺序返回完全相同的行。按照同样的逻辑你可能会期望SELECTaAScFROMnumsORDERBY-c;做完全相同的事情。但它没有。它会报错说列 “c” 不存在尽管别名就在语句中。欢迎来到 ORDER BY 丛林。名称和表达式不是一回事如果你问大多数开发人员 ORDER BY 是如何工作的他们会说“你在那里放一个列名它就会对行进行排序”。在 99% 的查询中这正是发生的事情。人们根据created_at或id排序然后继续做其他事情。严格来说如果你算上ORDER BY 1其实有三种。位置引用是另一回事不在本文讨论范围之内。但ORDER BY接受两种不同类型的东西SELECTcreated_at,user_idFROMeventsORDERBYcreated_at;SELECTcreated_at,user_idFROMeventsORDERBYdate(created_at);两者都感觉很自然。但没人告诉你的是它们在解析器中走的是完全不同的代码路径。不同的作用域规则不同的查找方式不同的错误消息。第一个查看你的 SELECT 列表。第二个查看你的 FROM 子句。它们从不查看同一个地方。相同的结果两种不同的排序再看第一个查询。SELECT-aASaFROMnumsORDERBYa;你写了ORDER BY a。一个裸标识符没有任何修饰。Postgres 走名称路径。它在 SELECT 列表中扫描名为a的东西找到别名为-a AS a的列并按它的输出值排序。取反后的值是 -3, -2, -1, 0升序是 -3, -2, -1, 0。这就是输出的结果。现在看它的孪生兄弟。SELECT-aASaFROMnumsORDERBY-a;你写了ORDER BY -a。这不再是一个标识符。它是一个表达式围绕列引用的单目减号。解析器根本不会尝试相同的逻辑。相反它会切换到表达式路径其中它知道的唯一a是nums表中的列并对输入值取反后排序。算术上的巧合是这两个查询最终得到了相同的行顺序。相同的输出完全不同的逻辑。如果你不相信这只是巧合可以将 SELECT 列表中的取反去掉但保留 ORDER BY 中的SELECTaAScFROMnumsORDERBY-a;c---3210(4rows)ORDER BY -a是一个表达式所以它按-input_a升序排序也就是input_a降序。别名c从未被使用。结果与c是什么毫无关系。而ORDER BY -c现在就很明显了。-c是一个表达式所以解析器在 FROM 中查找列c找不到然后报错。别名存在但在这条代码路径无法看到的作用域中。标识符之上或环绕其周围一旦规则清晰了裸标识符命中 SELECT 列表其他任何东西命中表其余的惊喜也就随之而来。SELECThelloASxFROMnumsORDERBYx::text;-- 错误: 列 x 不存在类型转换算作表达式并将查找推送到表这大概并不令人惊讶。令人惊讶的可能是这个SELECTaAScFROMnumsORDERBYcDESCNULLSFIRST;这将按预期工作。DESC和NULLS FIRST都是排序子句本身的一部分而不是排序表达式的一部分。它们在解析树中位于标识符之上因此它们从不触及标识符。解析器仍然看到一个裸的c走快速路径找到别名按它排序然后在解析出的键之上应用“降序空值优先”。但排序规则Collation就不能这么说了。SELECTA::textASxFROMnumsORDERBYxCOLLATEC;-- 错误: 列 x 不存在这是一个非常糟糕的情况。COLLATE可能看起来和排序修饰符一样但它不是。它在解析树中包裹了表达式。括号是一个特例。SELECT-aASaFROMnumsORDERBY(a);-- 有效按别名排序Postgres 在裸标识符检查之前会折叠多余的括号所以(a)仍然是裸的a。这种不对称性以最大化混淆的方式存在着COLLATE对人是“仍然是一个名称”对解析器是“一个表达式”而(a)对人是“一个表达式”对解析器“仍然是一个名称”。你在这里同时得到了两种错误的直觉。一元加号。a和a计算出的值相同但它们解析成的节点不同。SELECT-aASaFROMnumsORDERBYa;SELECT-aASaFROMnumsORDERBYa;一个你甚至不会想到的加号会改变哪些行以什么顺序输出。解析器将列引用存储为名称部分的列表未限定时为一部分添加表或模式后为两个或更多部分。快速路径仅在列表长度为一时触发。最后模式和表限定的引用。ORDER BY nums.a看起来像一个标识符但它不是。SELECT-aASaFROMnumsORDERBYa;SELECT-aASaFROMnumsORDERBYnums.a;第一个查询输出 -3, -2, -1, 0第二个查询输出 0, -1, -2, -3。结果不同不是你想象的那个名字的别名有一个问题曾花掉我一个下午的时间。一旦 ORM 或生成的视图为你声明了别名就很容易遇到。SQLAlchemy、Hibernate、jOOQ 以及大多数代码生成器会引用任何不是纯小写的标识符。两个查询除了一个中的别名被引号括起来之外完全相同。两个不同的结果集。SELECT-aASAFROMnumsORDERBYa;-- 按别名排序 (-3,-2,-1,0)SELECT-aASAFROMnumsORDERBYa;-- 按输入排序 (0,-1,-2,-3)裸标识符检查使用strcmp比较名称。未引用的A折叠为小写a并匹配。引用的A保留大小写保持为A并且与 ORDER BY 中的小写a不匹配。查找失败解析器回退到表达式路径表达式路径在nums中找到列a查询成功运行但执行的操作与你的意图不同。GROUP BY 首先检查相反的作用域GROUP BY和ORDER BY都接受裸标识符并且两者都可以通过两种方式解析它到表列或到 SELECT 列表别名。区别在于它们检查的顺序ORDER BY a首先查看 SELECT 列表然后查看表。GROUP BY a首先查看表然后查看 SELECT 列表。对于大多数查询这无关紧要。两个子句最终会选择相同的东西因为没有东西被遮蔽。当别名与基列同名但值不同时意外就会发生SELECTa/2ASa,count(*)FROMnumsGROUPBYaORDERBYa;现在两个子句对a的含义产生了分歧。GROUP BY a选择输入列四个不同的值四个组每行一组。ORDER BY a选择别名即a/2。结果有四行因为分组是基于比投影更细粒度的键a|count----------0|10|11|11|1两行a/2 0来自输入 0 和 1两行a/2 1来自输入 2 和 3。重复是真实的。同一个标识符在同一个查询的两个相邻子句中表示两个不同的列。窗口 ORDER BY 甚至不假装这一点会让人困扰因为它看起来不像一个不同的子句。SELECTa,-aASneg,row_number()OVER(ORDERBYneg)FROMnums;-- 错误: 列 neg 不存在OVER (ORDER BY ...)完全是一个不同的解析路径。它根本不检查目标列表只检查 FROM 作用域。裸名称快速路径在这里根本不存在。SELECTa,-aASneg,row_number()OVER(ORDERBY-a)FROMnums;-- 这个有效同一个查询中的两个 ORDER BY 子句两种不同的作用域规则。UNION ORDER BY 仅限名称当 ORDER BY 跟在 UNION 后面时两条路径都没有完全打开。-- 有效(SELECTaFROMnums)UNIONALL(SELECT9)ORDERBYa;-- 错误(SELECTaFROMnums)UNIONALL(SELECT9)ORDERBY-a;-- 错误(SELECTaFROMnums)UNIONALL(SELECT9)ORDERBYaCOLLATEC;错误信息异常有用只能使用结果列名不能使用表达式或函数。提示将表达式/函数添加到每个 SELECT 中或者将 UNION 移到 FROM 子句中。集合操作没有单一的 FROM 作用域可以回退因此表达式路径完全关闭。要么是裸名称要么什么都没有。源代码中的接缝完全披露在我让 Claude Code 帮助我追踪实际的解析树之前这一节我写错了三次。整晚沉迷于 ORDER BY 而睡眠不足是另一种合理的解释。打开src/backend/parser/parse_clause.c并找到findTargetlistEntrySQL92。它有四十行注释两个if块以及一个最终的return。SQL92 的两种解析规则首先被尝试SQL99 是后备方案。第一块裸名称路径。入口是一个恰好包含一个名称部分的 ColumnRef 节点并且该部分必须是一个字符串标识符不是**也是 ColumnRef 但带有 A_Star 字段。如果节点通过该函数会遍历目标列表寻找一个非resjunk的、其resname等于该标识符的条目。循环会越过第一个匹配项以检测歧义相同的表达式没问题这就是SELECT a, a FROM nums ORDER BY a有效的原因不同的表达式会报错。如果是唯一匹配则返回。如果循环没有找到任何东西该块不会返回。控制权向下传递。这就是前面文章中引用别名意外背后的情况AS A存储resname AORDER BY a查找resname astrcmp失败函数继续执行好像没有应用 SQL92 快速路径一样。GROUP BY是这个块内部的一个小例外。名称首先会针对 FROM 作用域进行测试如果在那里命中则跳过目标列表循环。这就是GROUP BY最终优先选择输入列的方式。第二块位置路径。入口是IsA(node, A_Const)。非整数常量会立即报错“ORDER BY 中的非整数常量 …”这捕获了ORDER BY NULL、ORDER BY a、ORDER BY TRUE。整数被用作进入非resjunk目标列表的基于 1 的位置超出范围的值会报错“位置 %d 不在选择列表中”。第二块从不向下传递。1 和 -1 都会作为整数A_Const到达这里。语法中的doNegate在函数运行之前将-Iconst折叠成一个单一的整数常量因此ORDER BY 1和ORDER BY -1走相同的代码只有整数值以及位置查找的结果不同。向下传递。任何未被上述情况捕获的内容都会到达最后一行/* * Otherwise, we have an expression, so process it per SQL99 rules. */returnfindTargetlistEntrySQL99(pstate,node,tlist,exprKind);这就是接缝。SQL92 在两种狭窄的形状下成功一个具有匹配别名的裸标识符或者一个范围内的正整数。其他一切包括别名查找未找到任何东西的裸标识符都变成了针对 FROM 解析的 SQL99 表达式。一个有用的变通方法如果你想在 ORDER BY 的表达式内部使用别名可移植的技巧是将查询包装在一个子查询中SELECT*FROM(SELECT-aASxFROMnums)sORDERBYx0;现在x是外部查询的 FROM 作用域中的一个真实列。表达式路径找到了它。接缝被移开了。从概念上讲这就是当你直接写ORDER BY x 0时你希望引擎为你做的事情。然而SQL-99 标准实际上并没有要求这样做而且 Postgres以及 SQL Server明确记录了 ORDER BY 表达式内部不支持别名。所以你需要手动完成。枯燥的要点大多数时候这些都无关紧要。你按你刚刚选择的列排序别名和输入列具有相同的名称和相同的值两条解析器路径给出相同的结果。接缝是不可见的。当别名和输入列在表达式、值、大小写或任何包裹在标识符周围的东西上不一致时解析器会根据一条比大多数在职程序员都年长的规则静默地选择一个或另一个。存在两个解析器。裸名称路径是 SQL-92表达式路径是 SQL-99它们是在 1990 年代末拼接在一起的。它们仍然对你的标识符存在于哪个作用域存在分歧而知道触发了哪一个就能告诉你该使用哪个作用域。如果在阅读完这篇文章后你仍然需要停下来思考一分钟才能预测SELECT-aASaFROMnumsORDERBYaCOLLATEC;会做什么那是正确的反应。这意味着你已经有了心智模型。开头的谜题查询来自 Jamie Brandon 在 Lobsters 帖子上的评论该帖子讨论了 Markus Winand 在 modern-sql.com 上发表的 ORDER BY 历史。本文的其余内容是该评论没有给出的解释。这两篇文章本身都值得一读。