signum function incompatible with Postgres and Apache Spark

[andygrove]

Describe the bug

In Postgres and Apache Spark, signum returns -1 for negative integers, 0 for zero, and 1 for positive integers.

DataFusion uses Rust's signum function, which has different behavior.

❯ select signum(-1), signum(0), signum(1);
+-------------------+------------------+------------------+
| signum(Int64(-1)) | signum(Int64(0)) | signum(Int64(1)) |
+-------------------+------------------+------------------+
| -1.0              | 1.0              | 1.0              |
+-------------------+------------------+------------------+

For floating-point inputs, Apache Spark returns -1 for -0.0 and 1 for +0.0. I have not researched what Postgres does in this case.

[goldmedal]

take

[goldmedal]

I will solve another issue first. I'll take it back if no one else works on it after I finish.

[getChan]

take

[getChan]

Comparison of the signum function between DataFusion SQL and Rust

	datafusion	rust	spark	postgresql
-1	-1	-1 (-1_i32)	-1	-1
0	1	0 (0_i32)	0	0
+1	1	1 (1_i32)	1	1
-0.0	-1	-1 (-0.0_f32)	0	0
+0.0	1	1 (0.0_f32)	0	0

In Rust, the definition of the signum function for floating point types is as follows:

Returns a number that represents the sign of self.

• 1.0 if the number is positive, +0.0 or INFINITY
• -1.0 if the number is negative, -0.0 or NEG_INFINITY
• NaN if the number is NaN

My guess is that the implementation of the signum function in DataFusion only accepts floating point types as input. Consequently, if it receives 0 as input, it appears to convert it to Rust’s 0.0 and output 1

ref) https://github.com/apache/datafusion/blob/main/datafusion/functions/src/macros.rs#L225-L254

implementation

• I will implement a separate signum function without using the unary function macro.
• The output will match that of Spark and PostgreSQL.

If you have any other opinions, please let me know.