MySQL子查询ORDER BY执行逻辑及HackerRank SQL题疑问咨询

我正在尝试解决HackerRank上的SQL项目题，需求如下：
输入为Projects表，表结构及样例数据见下图：

要求输出：

2015-10-28 2015-10-29
2015-10-30 2015-10-31
2015-10-13 2015-10-15
2015-10-01 2015-10-04

题目要求将结束日期连续的任务归为同一个项目，返回各项目的起止日期，最终结果按项目日期间差升序排列。如上示例中任务1、2、3属于同一个项目，任务4、5属于同一个项目，任务7、8各为独立项目。

找到的参考解法如下：

set @sdate = null;
set @nextdate = null;

select sd, max(ed) ed2
from (
    select if(@nextdate = start_date,  @sdate, @sdate := start_date) as sd,
           @nextdate := end_date as ed
    from Projects 
    order by start_date
) tmp
group by sd
order by datediff(max(ed), sd)

该解法通过变量存储上一行的结束日期，和当前行起始日期对比实现分组，针对子查询中的order by子句有两点疑问，解答如下：

1. 为什么去掉子查询中的order by start_date结果会出错？ #

你之前了解的「MySQL中子查询的排序会被忽略」是有前提限制的：当外层查询对派生表（即FROM后的子查询）存在聚合、分组、关联等需要重新整理结果集的操作，且子查询未加LIMIT子句时，优化器可能判定子查询的排序无意义，主动丢弃排序步骤。

但在这个场景中，子查询的order by直接决定了用户变量的计算逻辑，属于语义强相关的操作，MySQL优化器不会直接忽略该排序规则。如果去掉order by start_date，MySQL会按数据存储的天然顺序返回行，行顺序打乱后，变量比对上一行结束日期的逻辑就完全失效，因此返回结果出错。

2. 子查询是否是先对源表排序再执行SELECT逻辑？ #

这个判断是正确的。

标准SQL的执行顺序中确实是SELECT阶段早于ORDER BY阶段，但MySQL对用户自定义变量的使用场景做了非标准的扩展适配：当SELECT列表中存在用户变量赋值，且当前查询块有明确的ORDER BY规则时，MySQL会先按ORDER BY的规则对源表行进行排序，再按排序后的顺序逐行执行SELECT中的变量计算与赋值，以此保证变量计算的顺序符合预期。如果按标准顺序先执行SELECT赋值再排序，变量存储的上一行数据就失去了意义，因此MySQL针对这类连续序列计算、排名计算的常用变量场景做了执行逻辑的调整。

内容的提问来源于stack exchange，提问作者Liumx31