SQL标准（ISO/IEC 9075）中浮点列能否存储合法非空NaN值？

Great question! Let’s break down the rules across different SQL standard versions, focusing strictly on the ISO/IEC 9075 specs (no specific DBMS quirks here):

Pre-SQL:2003 Standards #

Before SQL:2003, the standard didn’t align with IEEE 754 floating-point semantics. NaN (Not-a-Number) was not recognized as a valid value for floating-point columns (like REAL, FLOAT, or DOUBLE PRECISION). Attempting to store NaN would result in an invalid value error, as the standard only permitted finite numeric values (and in some edge cases, infinity, but even that wasn’t consistent).

SQL:2003 and Later (Including the Latest ISO/IEC 9075 Revisions) #

Starting with SQL:2003, the standard adopted IEEE 754 floating-point behavior, which changed everything for NaN:

• NaN is now a valid value for floating-point columns.
• For NOT NULL constraints: Since NaN is a non-null value (it’s not the SQL NULL marker), it fully satisfies a NOT NULL requirement. The constraint only checks for the presence of NULL—it doesn’t validate whether the value is a "normal" number.

A key caveat to keep in mind: While NaN is valid to store, standard SQL comparison rules treat NaN as unequal to every value (including another NaN). Using = to compare NaN to anything returns UNKNOWN, which acts like FALSE in most constraint contexts. But this comparison behavior doesn’t negate NaN’s validity as a stored non-null value.

Later revisions (like SQL:2016, SQL:2023) have maintained this IEEE 754 alignment, with no major changes to the core rule that NaN is a valid non-null floating-point value.

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