You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@spark.apache.org by "Yuming Wang (JIRA)" <ji...@apache.org> on 2019/07/10 01:40:00 UTC
[jira] [Commented] (SPARK-28316) Decimal precision issue
[ https://issues.apache.org/jira/browse/SPARK-28316?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16881648#comment-16881648 ]
Yuming Wang commented on SPARK-28316:
-------------------------------------
cc [~joshrosen] [~cloud_fan] [~Gengliang.Wang]
> Decimal precision issue
> -----------------------
>
> Key: SPARK-28316
> URL: https://issues.apache.org/jira/browse/SPARK-28316
> Project: Spark
> Issue Type: Sub-task
> Components: SQL
> Affects Versions: 3.0.0
> Reporter: Yuming Wang
> Priority: Major
>
> Multiply check:
> {code:sql}
> -- Spark SQL
> spark-sql> select cast(-34338492.215397047 as decimal(38, 10)) * cast(-34338492.215397047 as decimal(38, 10));
> 1179132047626883.596862
> -- PostgreSQL
> postgres=# select cast(-34338492.215397047 as numeric(38, 10)) * cast(-34338492.215397047 as numeric(38, 10));
> ?column?
> ---------------------------------------
> 1179132047626883.59686213585632020900
> (1 row)
> {code}
> Division check:
> {code:sql}
> -- Spark SQL
> spark-sql> select cast(93901.57763026 as decimal(38, 10)) / cast(4.31 as decimal(38, 10));
> 21786.908963
> -- PostgreSQL
> postgres=# select cast(93901.57763026 as numeric(38, 10)) / cast(4.31 as numeric(38, 10));
> ?column?
> --------------------
> 21786.908962937355
> (1 row)
> {code}
> POWER(10, LN(value)) check:
> {code:sql}
> -- Spark SQL
> spark-sql> SELECT CAST(POWER(cast('10' as decimal(38, 18)), LN(ABS(round(cast(-24926804.04504742 as decimal(38, 10)),200)))) AS decimal(38, 10));
> 107511333880051856
> -- PostgreSQL
> postgres=# SELECT CAST(POWER(cast('10' as numeric(38, 18)), LN(ABS(round(cast(-24926804.04504742 as numeric(38, 10)),200)))) AS numeric(38, 10));
> power
> -------------------------------
> 107511333880052007.0414112467
> (1 row)
> {code}
> AVG, STDDEV and VARIANCE returns double type:
> {code:sql}
> -- Spark SQL
> spark-sql> create temporary view t1 as select * from values
> > (cast(-24926804.04504742 as decimal(38, 10))),
> > (cast(16397.038491 as decimal(38, 10))),
> > (cast(7799461.4119 as decimal(38, 10)))
> > as t1(t);
> spark-sql> SELECT AVG(t), STDDEV(t), VARIANCE(t) FROM t1;
> -5703648.53155214 1.7096528995154984E7 2.922913036821751E14
> -- PostgreSQL
> postgres=# SELECT AVG(t), STDDEV(t), VARIANCE(t) from (values (cast(-24926804.04504742 as decimal(38, 10))), (cast(16397.038491 as decimal(38, 10))), (cast(7799461.4119 as decimal(38, 10)))) t1(t);
> avg | stddev | variance
> -----------------------+-------------------------------+--------------------------------------
> -5703648.531552140000 | 17096528.99515498420743029415 | 292291303682175.09401722225695880000
> (1 row)
> {code}
> EXP returns double type:
> {code:sql}
> -- Spark SQL
> spark-sql> select exp(cast(1.0 as decimal(31,30)));
> 2.718281828459045
> -- PostgreSQL
> postgres=# select exp(cast(1.0 as decimal(31,30)));
> exp
> ----------------------------------
> 2.718281828459045235360287471353
> (1 row)
> {code}
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
---------------------------------------------------------------------
To unsubscribe, e-mail: issues-unsubscribe@spark.apache.org
For additional commands, e-mail: issues-help@spark.apache.org