BigQuery浮点数排序规则与直接比较结果不符的原因

BigQuery官方定义的浮点数排序顺序从最小到最大为：NULL、NaN（所有NaN排序时视为相等）、-inf、负数、0/-0（所有零值视为相等）、正数、+inf。你可以用以下查询验证这个排序规则：

with floats as (
  select 1.2 val union all select -1.2 union all select cast('-Infinity' as float64) union all select null
  union all select +0.0 union all select -0.0 union all select cast('+infinity' as float64) union all select cast('nan' as float64)
) select * from floats order by val

但直接执行浮点数比较时，比如select cast('nan' as float64) < cast('infinity' as float64)会返回false，和排序规则看起来矛盾，核心原因是这两个逻辑遵循的标准不一样：

• 直接浮点数比较遵循IEEE 754标准：根据这个标准，NaN和任何值（包括它自己）的比较结果都是false。所以不管是NaN < +inf还是NaN = NaN，返回的都是false，这是浮点数本身的语义规定。
• 排序规则是BigQuery的自定义逻辑：为了让所有浮点数能被稳定排序，BigQuery特意给NaN指定了一个固定的排序位置（排在-inf之前），这是排序阶段的特殊处理，目的是解决NaN在标准中无法参与排序的问题，但并没有改变浮点数底层的比较规则。

简单来说，排序是BigQuery为了实用性做的上层规则，而直接比较是严格遵循浮点数的国际标准，两者的设计目标不同，所以结果会有差异。

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