SQL Server中UNION子查询嵌套后ORDER BY..OFFSET语义异常问询

场景1：直接执行UNION+ORDER BY..OFFSET #

在SQL Server 2022及更早版本中，以下符合SQL标准的查询按预期执行：

select i
from (values (1)) as t (i)
union 
select i
from (values (2), (3)) as t (i)
order by i
offset 0 rows
fetch next 1 rows only

返回结果：

|i  |
|---|
|1  |

此时ORDER BY..OFFSET子句作用于UNION的整体结果集，正确返回排序后的第一条数据。

场景2：嵌套到派生表/CTE中执行 #

当将该查询嵌套到派生表或CTE中时，语义发生变化：

select *
from (
  select i
  from (values (1)) as t (i)
  union 
  select i
  from (values (2), (3)) as t (i)
  order by i
offset 0 rows
fetch next 1 rows only
) t;

返回结果：

|i  |
|---|
|1  |
|2  |

此时ORDER BY..OFFSET仅作用于UNION的第二个子查询，最终结果是第一个子查询的所有数据加上第二个子查询被限制后的1条数据。

结论：这是SQL Server的指定行为 #

这并非Bug，而是SQL Server语法解析规则导致的指定行为。在派生表/CTE内部，ORDER BY结合OFFSET/FETCH的语句会被解析为绑定到UNION的最后一个SELECT子句，而非整个UNION的结果集。若要让ORDER BY..OFFSET作用于整个UNION结果集，需将UNION部分单独作为子查询，再在外层应用排序和分页：

select *
from (
  select i
  from (values (1)) as t (i)
  union 
  select i
  from (values (2), (3)) as t (i)
) u
order by i
offset 0 rows
fetch next 1 rows only;

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