search

elastic / elasticsearch

Free and Open Source, Distributed, RESTful Search Engine
https://www.elastic.co/products/elasticsearch
Other
1.09k stars 24.83k forks source link

SQL: ROUND(NaN) results in -0.0 #65863

Open palesz opened 3 years ago

palesz commented 3 years ago

The following SQL query:

SELECT ROUND('NaN'::double)

results in:

ROUND('NaN'::double)
--------------------
-0.0

Other major relational DBs behave differently.

MySQL and MSSQL does not have NaN (only NULL ), ROUND(NULL) is NULL. PostgreSQL and Oracle has NaN and ROUND(NaN) returns NaN.

Was it intentional that we kept this in-sync with the Math.round() behaviour and different from the relational DBs above?

Note: H2 (that we check against in integration tests) also follows the behaviour of Math.round().

elasticmachine commented 3 years ago

Pinging @elastic/es-ql (Team:QL)

costin commented 3 years ago

Potentially related to #3516

palesz commented 3 years ago

3516

I think you meant https://github.com/elastic/elasticsearch/pull/35164

elasticsearchmachine commented 9 months ago

Pinging @elastic/es-analytical-engine (Team:Analytics)