Throne3d commented on PR #11580:
URL: https://github.com/apache/datafusion/pull/11580#issuecomment-2244205992
Here are the values I get for Spark 3.5.1:
```python
data = ["-1", "1", "0", "-0.0", "0.0", "1.0", "-1.0"]
df = spark.createDataFrame([(datum,) for datum in data], "col1 string")
df.collect()
# [Row(col1='-1'), Row(col1='1'), Row(col1='0'), Row(col1='-0.0'),
Row(col1='0.0'), Row(col1='1.0'), Row(col1='-1.0')]
pprint.pprint(df.selectExpr("col1", "signum(cast(col1 as float)) as
result").collect())
# [Row(col1='-1', result=-1.0),
# Row(col1='1', result=1.0),
# Row(col1='0', result=0.0),
# Row(col1='-0.0', result=-0.0),
# Row(col1='0.0', result=0.0),
# Row(col1='1.0', result=1.0),
# Row(col1='-1.0', result=-1.0)]
```
So it seems like -0.0 in Spark should specifically return -0.0 in the signum
function, not -1, right?
Literals seem to be treated as Decimal instead, which doesn't make the
distinction between negative and positive zero, so those don't see the same
behavior - I'd guess that's what caused the specific results listed for Spark
in https://github.com/apache/datafusion/issues/11557#issuecomment-2241473321:
```py
spark.sql("""SELECT -1, 1, 0, -0.0, 0.0, 1.0, -1.0""").collect()
# [Row(-1=-1, 1=1, 0=0, 0.0=Decimal('0.0'), 0.0=Decimal('0.0'),
1.0=Decimal('1.0'), -1.0=Decimal('-1.0'))]
spark.sql("""SELECT signum(-1), signum(1), signum(0), signum(-0.0),
signum(0.0), signum(1.0), signum(-1.0)""").collect()
# [Row(SIGNUM(-1)=-1.0, SIGNUM(1)=1.0, SIGNUM(0)=0.0, SIGNUM(0.0)=0.0,
SIGNUM(0.0)=0.0, SIGNUM(1.0)=1.0, SIGNUM(-1.0)=-1.0)]
```
For Postgres 16.3, this behavior looks correct for both `decimal` and
`float` though!
```sql
postgres=# WITH tab(datum) AS (VALUES ('-1'), ('1'), ('0'), ('-0.0'),
('0.0'), ('1.0'), ('-1.0'))
SELECT *, cast(datum as decimal) decimal, cast(datum as float) float,
sign(cast(datum as decimal)) sign_decimal, sign(cast(datum as float))
sign_float FROM tab;
datum | decimal | float | sign_decimal | sign_float
-------+---------+-------+--------------+------------
-1 | -1 | -1 | -1 | -1
1 | 1 | 1 | 1 | 1
0 | 0 | 0 | 0 | 0
-0.0 | 0.0 | -0 | 0 | 0
0.0 | 0.0 | 0 | 0 | 0
1.0 | 1.0 | 1 | 1 | 1
-1.0 | -1.0 | -1 | -1 | -1
(7 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: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
