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: 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