[SPARK-29616] Bankers' rounding for double types

• Type: Sub-task
• Status: Resolved
• Priority: Trivial
• Resolution: Won't Fix
• Affects Version/s: 3.0.0
• Component/s: SQL
• Assignee: Unassigned
• Reporter: Takeshi Yamamuro
• Created: 27/Oct/19 23:57
• Updated: 29/Dec/19 09:09
• Resolved: 29/Dec/19 09:09

Description

PostgreSQL uses banker's rounding mode for double types;

postgres=# select * from t;
 a | b 
-----+-----
 0.5 | 0.5
 1.5 | 1.5
 2.5 | 2.5
 3.5 | 3.5
 4.5 | 4.5
(5 rows)

postgres=# \d t
 Table "public.t"
 Column | Type | Collation | Nullable | Default 
--------+------------------+-----------+----------+---------
 a | double precision | | | 
 b | numeric(2,1) | | |

postgres=# select round(a), round(b) from t;
 round | round 
-------+-------
 0 | 1
 2 | 2
 2 | 3
 4 | 4
 4 | 5
(5 rows)

 

In the master;

scala> sql("select * from t").show
+---+---+
| a| b|
+---+---+
|0.5|0.5|
|1.5|1.5|
|2.5|2.5|
|3.5|3.5|
|4.5|4.5|
+---+---+

scala> sql("select * from t").printSchema
root
 |-- a: double (nullable = true)
 |-- b: decimal(2,1) (nullable = true)

scala> sql("select round(a), round(b) from t").show()
+-----------+-----------+
|round(a, 0)|round(b, 0)|
+-----------+-----------+
| 1.0| 1|
| 2.0| 2|
| 3.0| 3|
| 4.0| 4|
| 5.0| 5|
+-----------+-----------+