liukun4515 commented on pull request #941:
URL: https://github.com/apache/arrow-rs/pull/941#issuecomment-968576937
Hi @alamb , I have checked the mysql and pg database.
We have two problems which should be discussed.
**The first is about scale, if the input string doesn't have the same value
for the declared scale, how do we to handle this?**
In the PG:
```
postgres=# select cast('123' as decimal(10,2));
numeric
---------
123.00
(1 row)
postgres=# select cast('123.123213321' as decimal(10,2));
numeric
---------
123.12
(1 row)
```
In the MYSQL:
```
mysql> select cast('123' as decimal(10,2));
+------------------------------+
| cast('123' as decimal(10,2)) |
+------------------------------+
| 123.00 |
+------------------------------+
1 row in set (0.00 sec)
mysql> select cast('123.1' as decimal(10,2));
+--------------------------------+
| cast('123.1' as decimal(10,2)) |
+--------------------------------+
| 123.10 |
+--------------------------------+
1 row in set (0.00 sec)
mysql> select cast('123.12345' as decimal(10,2));
+------------------------------------+
| cast('123.12345' as decimal(10,2)) |
+------------------------------------+
| 123.12 |
+------------------------------------+
1 row in set (0.00 sec)
```
From the two database, we can know that the number of value after the
decimal must match the scale parameter.
If the number of value is less than the scale, we should fill the `0` in the
tail.
If the number of value is greater than the scale, we should truncate the
tail to meet the scale.
**The second is about precision, if the input string value is out of bounds
for the declared precision, how do we to handle this?**
This is the result for PG
```
postgres=# select cast('123' as decimal(2,2));
ERROR: numeric field overflow
DETAIL: A field with precision 2, scale 2 must round to an absolute value
less than 1.
```
This is the result for mysql
```
mysql> select cast('123.12345' as decimal(2,2));
+-----------------------------------+
| cast('123.12345' as decimal(2,2)) |
+-----------------------------------+
| 0.99 |
+-----------------------------------+
1 row in set, 1 warning (0.00 sec)
mysql> show warnings;
+---------+------+----------------------------------------------------------------------------+
| Level | Code | Message
|
+---------+------+----------------------------------------------------------------------------+
| Warning | 1264 | Out of range value for column 'cast('123.12345' as
decimal(2,2))' at row 1 |
+---------+------+----------------------------------------------------------------------------+
1 row in set (0.00 sec)
```
In the PG and mysql, if the input string value is out of bounds for the
precision, we will get some error.
--
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]
