Make Eq with negative float zero compat with other engines

[kazuyukitanimura]

Describe the bug

DF returns false below and is not compliant with major engines:

DataFusion CLI v39.0.0
> select 0.0 == -0.0;
+--------------------------+
| Float64(0) = Float64(-0) |
+--------------------------+
| false                    |
+--------------------------+

Similar issue #11051

To Reproduce

No response

Expected behavior

No response

Additional context

No response

[LorrensP-2158466]

Isn't this done in Arrow? Because Binary Eq calls arrow::compute::kernels::cmp::eq

So I think we should do exactly what the docs say and normalize zeros?

[wkalt]

Negative zero can result from ordinary multiplication operations, which means it is easy to encounter this bug in the course of common SQL analytics. This can result in incorrect query results, for example:

> create table x(a float, b float);
0 row(s) fetched. 
Elapsed 0.001 seconds.

> insert into x values (-1.0, 0.0), (1.0, 1.0);
+-------+
| count |
+-------+
| 2     |
+-------+
1 row(s) fetched. 
Elapsed 0.001 seconds.

> select * from x;
+------+-----+
| a    | b   |
+------+-----+
| -1.0 | 0.0 |
| 1.0  | 1.0 |
+------+-----+
2 row(s) fetched. 
Elapsed 0.000 seconds.

> select * from x where a * b >= 0;
+-----+-----+
| a   | b   |
+-----+-----+
| 1.0 | 1.0 |
+-----+-----+
1 row(s) fetched. 
Elapsed 0.001 seconds.

for that reason this seems like a severe bug to me.

[wkalt]

@alamb any thought on the above?

[alamb]

I tested duckdb and postgres and they seem to have the -0 == 0 behavior

andrewlamb@Andrews-MacBook-Pro-3:~/Downloads/burnin/aws_tester$ duckdb
DuckDB v1.5.2 (Variegata)
Enter ".help" for usage hints.
memory D  select 0.0 == -0.0;
┌─────────────┐
│ (0.0 = 0.0) │
│   boolean   │
├─────────────┤
│ true        │
└─────────────┘

postgres=#  select 0.0::float = -0.0::float;
 ?column?
----------
 t
(1 row)

postgres=#  select 0.0 = -0.0;
 ?column?
----------
 t

As long as we can fix this without causing performance regressions sounds good to me

[sweb]

@Jefffrey thank you for finding my PR and linking it to this issue - I was not aware of it. I gave this issue a shot in #22628 however it comes with a performance regression - I am not sure whether this is possible without it because you need to do an additional check / transform where you go from -0.0 to 0.0 - at least for floats.
@alamb I ran the in-list bench and my first attempt regressed by 30%, doing a pre-check whether transform is required at all brought it down to ~10%. However, I have to admit that the benchmark was not very stable on my machine, so if my general approach is valid, I'd have to spend more time on measuring the precise impact of this change.

[alamb]

Maybe it is worth researching how other engines implement floating point equality. I have to imagine there are hardware instructions / other tricks to treat -0 and 0 equally