findepi commented on PR #16745: URL: https://github.com/apache/datafusion/pull/16745#issuecomment-3109033105
> I mean SQL spec and what DataFusion should be implementing. I wish DataFusion followed SQL spec in everything, but that's not the project design philosophy AFAICT. That was supposed to be made more explicit in https://github.com/apache/datafusion/issues/13704 / @alamb's https://github.com/apache/datafusion/pull/13706 if we want to follow the SQL spec, you have my full support, but I'd encourage codifying it in a form of a referencible documentation. BTW, last time I checked SQL spec didn't know about NaN values and I don't think it really distinguishes between positive or negative zeros. following https://github.com/apache/datafusion/pull/13706 proposal, here is behavior check with PostgreSQL it clearly compares negative and positive zero as equal in both `=` and `<` operators: ``` postgres=# WITH fs AS (SELECT t::float AS f FROM (values ('0'), ('-0'), ('NaN')) _(t)) SELECT f1, f2, f1 = f2, f1 < f2, f2 < f1 FROM fs t(f1), fs u(f2); f1 | f2 | ?column? | ?column? | ?column? -----+-----+----------+----------+---------- 0 | 0 | t | f | f 0 | -0 | t | f | f 0 | NaN | f | t | f -0 | 0 | t | f | f -0 | -0 | t | f | f -0 | NaN | f | t | f NaN | 0 | f | f | t NaN | -0 | f | f | t NaN | NaN | t | f | f (9 rows) ``` here is the output from Trino it clearly compares negative and positive zero as equal in both `=` and `<` operators it also follows IEEE 754 for comparisons involving NaN (returning false even for "NaN = NaN") ``` trino> WITH fs AS (SELECT CAST(t AS real) AS f FROM (values ('0'), ('-0'), ('NaN')) _(t)) -> SELECT f1, f2, f1 = f2, f1 < f2, f2 < f1 -> FROM fs _(f1), fs _(f2); f1 | f2 | _col2 | _col3 | _col4 ------+------+-------+-------+------- 0.0 | 0.0 | true | false | false -0.0 | 0.0 | true | false | false NaN | 0.0 | false | false | false 0.0 | -0.0 | true | false | false -0.0 | -0.0 | true | false | false NaN | -0.0 | false | false | false 0.0 | NaN | false | false | false -0.0 | NaN | false | false | false NaN | NaN | false | false | false (9 rows) ``` -- 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. To unsubscribe, e-mail: github-unsubscr...@datafusion.apache.org For queries about this service, please contact Infrastructure at: us...@infra.apache.org --------------------------------------------------------------------- To unsubscribe, e-mail: github-unsubscr...@datafusion.apache.org For additional commands, e-mail: github-h...@datafusion.apache.org
