crepererum commented on issue #264: URL: https://github.com/apache/arrow-rs/issues/264#issuecomment-833629444
# Prior Art ## PostgreSQL > In most implementations of the “not-a-number” concept, NaN is not considered equal to any other numeric value (including NaN). In order to allow numeric values to be sorted and used in tree-based indexes, PostgreSQL treats NaN values as equal, and greater than all non-NaN values. https://www.postgresql.org/docs/13/datatype-numeric.html **=> Follows this proposal** ## CockroachDB Follows PostgreSQL, see https://github.com/cockroachdb/cockroach/issues/18860 **=> Follows this proposal** ## Oracle > The floating-point value NaN is greater than any other numeric value and is equal to itself. https://docs.oracle.com/database/121/TTSQL/types.htm#TTSQL165 **=> Follows this proposal** ## Snowflake > Comparison semantics for `'NaN'` differ from the IEEE 754 standard in the following ways: > > Condition | Snowflake | IEEE 754 | Comment > -- | -- | -- | -- > `'NaN' = 'NaN'` | `TRUE` | `FALSE` | In Snowflake, `'NaN'` values are all equal. > `'NaN' > X` where `X` is any FLOAT value, including infinity (other than `NaN` itself). | `TRUE` | `FALSE` | Snowflake treats `'NaN'` as greater than any other FLOAT value, including infinity. https://docs.snowflake.com/en/sql-reference/data-types-numeric.html#float-float4-float8 **=> Follows this proposal** ## Vertica > Vertica follows the IEEE definition of NaNs (IEEE 754). The SQL standards do not specify how floating point works in detail. [...] > > ### Rules > - -0 == +0 > - 1/0 = Infinity > - 0/0 == Nan > - NaN != anything (even NaN) > > [...] > > ### Sort Order (Ascending) > - NaN > - -Inf > - numbers > - +Inf > - NULL https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/SQLReferenceManual/DataTypes/Numeric/DOUBLEPRECISIONFLOAT.htm **=> Does NOT follow this proposal** ## SQL Standard Not open, and scanning through the drafts I've found does not reveal more than "implementation defined". I trust Vertica when they say: > The SQL standards do not specify how floating point works in detail. https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/SQLReferenceManual/DataTypes/Numeric/DOUBLEPRECISIONFLOAT.htm **=> Undefined** -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. For queries about this service, please contact Infrastructure at: [email protected]
