[MCOL-4112] ROUND(d1,38)/TRUNCATE(d1,38) where d1 is DECIMAL(38)/DECIMAL(38,10) gives wrong results. Created: 2020-06-25  Updated: 2022-06-27  Resolved: 2022-03-10

Status: Closed
Project: MariaDB ColumnStore
Component/s: MDB Plugin, PrimProc
Affects Version/s: N/A
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Gagan Goel (Inactive) Assignee: Gagan Goel (Inactive)
Resolution: Won't Fix Votes: 0
Labels: None

Issue Links:
PartOf
is part of MCOL-641 Full DECIMAL support in ColumnStore Closed
is part of MCOL-4820 Umbrella for tasks related to the dec... Open
Sprint: 2021-17

 Description    

MariaDB [test]> drop table if exists cs1;
 
Query OK, 0 rows affected (0.293 sec)
 
 
 
MariaDB [test]> create table cs1(d1 decimal(38,10))engine=columnstore;
 
Query OK, 0 rows affected (0.258 sec)
 
 
 
MariaDB [test]> insert into cs1 values (9999999999999999999999999999.9999999999), (1234567890123456789012345678.9012345678);
 
Query OK, 2 rows affected (0.293 sec)
 
Records: 2  Duplicates: 0  Warnings: 0
 
 
 
MariaDB [test]> select round(d1, 38), truncate(d1, 38) from cs1;
 
+--------------------------------------------------------------------+--------------------------------------------------------------------+
 
| round(d1, 38)                                                      | truncate(d1, 38)                                                   |
 
+--------------------------------------------------------------------+--------------------------------------------------------------------+
 
| 999999999999999999999999999.99999999999999999999999999999999999999 | 999999999999999999999999999.99999999999999999999999999999999999999 |
 
| 999999999999999999999999999.99999999999999999999999999999999999999 | 999999999999999999999999999.99999999999999999999999999999999999999 |
 
+--------------------------------------------------------------------+--------------------------------------------------------------------+
 
2 rows in set (0.093 sec)
 
 
 
The expected results are:
 
MariaDB [test]> select round(d1, 38), truncate(d1, 38) from cs1;
 
+---------------------------------------------------------------------+---------------------------------------------------------------------+
 
| round(d1, 38)                                                       | truncate(d1, 38)                                                    |
 
+---------------------------------------------------------------------+---------------------------------------------------------------------+
 
| 9999999999999999999999999999.99999999990000000000000000000000000000 | 9999999999999999999999999999.99999999990000000000000000000000000000 |
 
| 1234567890123456789012345678.90123456780000000000000000000000000000 | 1234567890123456789012345678.90123456780000000000000000000000000000 |
 
+---------------------------------------------------------------------+---------------------------------------------------------------------+
 
2 rows in set (0.001 sec)
 
 
 
MariaDB [test]> drop table if exists cs1;
 
Query OK, 0 rows affected (0.235 sec)
 
 
 
MariaDB [test]> CREATE TABLE cs1 (d1 DECIMAL(38)) ENGINE=columnstore;
 
Query OK, 0 rows affected (0.166 sec)
 
 
 
MariaDB [test]> INSERT INTO cs1 VALUES (9999999999999999999999999999999999999);
 
Query OK, 1 row affected (0.171 sec)
 
 
 
MariaDB [test]> select round(d1, 38), truncate(d1, 38) from cs1;
 
+--------------------------------------------------------------------+--------------------------------------------------------------------+
 
| round(d1, 38)                                                      | truncate(d1, 38)                                                   |
 
+--------------------------------------------------------------------+--------------------------------------------------------------------+
 
| 999999999999999999999999999.99999999999999999999999999999999999999 | 999999999999999999999999999.99999999999999999999999999999999999999 |
 
+--------------------------------------------------------------------+--------------------------------------------------------------------+
 
1 row in set (0.114 sec)
 
 
 
MariaDB [test]> select round(d1, 38), truncate(d1, 38) from cs1;
 
+----------------------------------------------------------------------------+----------------------------------------------------------------------------+
 
| round(d1, 38)                                                              | truncate(d1, 38)                                                           |
 
+----------------------------------------------------------------------------+----------------------------------------------------------------------------+
 
| 9999999999999999999999999999999999999.000000000000000000000000000000000000 | 9999999999999999999999999999999999999.000000000000000000000000000000000000 |
 
+----------------------------------------------------------------------------+----------------------------------------------------------------------------+
 
1 row in set (0.001 sec)



 Comments   
Comment by Gagan Goel (Inactive) [ 2022-03-10 ]

The incorrect results are due to overflow.

ROUND(d1, 38)/TRUNCATE(d1, 38) where d1 is DECIMAL(38, 10) will result in a DECIMAL(M, D) where M=28 and D=38. This is 66 digits of precision. However, the maximum precision that MariaDB supports is 65, hence the resulting values are saturated. If we instead use ROUND(d1, 37)/TRUNCATE(d1, 37), we get the expected result:

MariaDB [test]> show create table cs1;
 
+-------+-----------------------------------------------------------------------------------------------------+
 
| Table | Create Table                                                                                        |
 
+-------+-----------------------------------------------------------------------------------------------------+
 
| cs1   | CREATE TABLE `cs1` (
 
  `d1` decimal(38,10) DEFAULT NULL
 
) ENGINE=Columnstore DEFAULT CHARSET=latin1 |
 
+-------+-----------------------------------------------------------------------------------------------------+
 
1 row in set (0.001 sec)
 
 
 
MariaDB [test]> select d1, round(d1, 38), truncate(d1, 38) from cs1;
 
+-----------------------------------------+--------------------------------------------------------------------+--------------------------------------------------------------------+
 
| d1                                      | round(d1, 38)                                                      | truncate(d1, 38)                                                   |
 
+-----------------------------------------+--------------------------------------------------------------------+--------------------------------------------------------------------+
 
| 9999999999999999999999999999.9999999999 | 999999999999999999999999999.99999999999999999999999999999999999999 | 999999999999999999999999999.99999999999999999999999999999999999999 |
 
| 1234567890123456789012345678.9012345678 | 999999999999999999999999999.99999999999999999999999999999999999999 | 999999999999999999999999999.99999999999999999999999999999999999999 |
 
|           123456789012345678.9012345678 |          123456789012345678.90123456780000000000000000000000000000 |          123456789012345678.90123456780000000000000000000000000000 |
 
+-----------------------------------------+--------------------------------------------------------------------+--------------------------------------------------------------------+
 
3 rows in set (0.081 sec)
 
 
 
MariaDB [test]> select d1, round(d1, 37), truncate(d1, 37) from cs1;
 
+-----------------------------------------+--------------------------------------------------------------------+--------------------------------------------------------------------+
 
| d1                                      | round(d1, 37)                                                      | truncate(d1, 37)                                                   |
 
+-----------------------------------------+--------------------------------------------------------------------+--------------------------------------------------------------------+
 
| 9999999999999999999999999999.9999999999 | 9999999999999999999999999999.9999999999000000000000000000000000000 | 9999999999999999999999999999.9999999999000000000000000000000000000 |
 
| 1234567890123456789012345678.9012345678 | 1234567890123456789012345678.9012345678000000000000000000000000000 | 1234567890123456789012345678.9012345678000000000000000000000000000 |
 
|           123456789012345678.9012345678 |           123456789012345678.9012345678000000000000000000000000000 |           123456789012345678.9012345678000000000000000000000000000 |
 
+-----------------------------------------+--------------------------------------------------------------------+--------------------------------------------------------------------+
 
3 rows in set (0.038 sec)

Similarly for a field d1 which is DECIMAL(38), the maximum number of decimal places D in ROUND(X, D)/TRUNCATE(X, D) that can be correctly handled would be 27:

MariaDB [test]> show create table cs2;
 
+-------+----------------------------------------------------------------------------------------------------+
 
| Table | Create Table                                                                                       |
 
+-------+----------------------------------------------------------------------------------------------------+
 
| cs2   | CREATE TABLE `cs2` (
 
  `d1` decimal(38,0) DEFAULT NULL
 
) ENGINE=Columnstore DEFAULT CHARSET=latin1 |
 
+-------+----------------------------------------------------------------------------------------------------+
 
1 row in set (0.001 sec)
 
 
 
MariaDB [test]> select d1, round(d1, 28), truncate(d1, 28) from cs2;
 
+----------------------------------------+--------------------------------------------------------------------+--------------------------------------------------------------------+
 
| d1                                     | round(d1, 28)                                                      | truncate(d1, 28)                                                   |
 
+----------------------------------------+--------------------------------------------------------------------+--------------------------------------------------------------------+
 
| 99999999999999999999999999999999999999 | 9999999999999999999999999999999999999.9999999999999999999999999999 | 9999999999999999999999999999999999999.9999999999999999999999999999 |
 
| 12345678901234567890123456789012345678 | 9999999999999999999999999999999999999.9999999999999999999999999999 | 9999999999999999999999999999999999999.9999999999999999999999999999 |
 
+----------------------------------------+--------------------------------------------------------------------+--------------------------------------------------------------------+
 
2 rows in set (0.113 sec)
 
 
 
MariaDB [test]> select d1, round(d1, 27), truncate(d1, 27) from cs2;
 
+----------------------------------------+--------------------------------------------------------------------+--------------------------------------------------------------------+
 
| d1                                     | round(d1, 27)                                                      | truncate(d1, 27)                                                   |
 
+----------------------------------------+--------------------------------------------------------------------+--------------------------------------------------------------------+
 
| 99999999999999999999999999999999999999 | 99999999999999999999999999999999999999.000000000000000000000000000 | 99999999999999999999999999999999999999.000000000000000000000000000 |
 
| 12345678901234567890123456789012345678 | 12345678901234567890123456789012345678.000000000000000000000000000 | 12345678901234567890123456789012345678.000000000000000000000000000 |
 
+----------------------------------------+--------------------------------------------------------------------+--------------------------------------------------------------------+
 
2 rows in set (0.040 sec)

The user should appropriately apply a valid D for ROUND(d1, D)/TRUNCATE(d1, D) based on the DECIMAL field d1 and keeping in mind the maximum allowable precision of 65 for DECIMAL fields.

Generated at Thu Feb 08 02:47:53 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.