[MCOL-5248] Truncate on char and varchar columns with numeric digits returned incorrect results Created: 2022-10-04  Updated: 2023-02-08  Resolved: 2023-01-17

Status: Closed
Project: MariaDB ColumnStore
Component/s: None
Affects Version/s: 22.08.2
Fix Version/s: 22.08.8

Type: Bug Priority: Major
Reporter: Daniel Lee (Inactive) Assignee: David Hall (Inactive)
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Relates
relates to MCOL-5283 Add conversion from char to decimal Open
Sprint: 2022-22, 2022-23
Assigned for Review: Leonid Fedorov Leonid Fedorov
Assigned for Testing: Daniel Lee Daniel Lee (Inactive)

 Description   

Build tested: 22.08.2

It seems to be a data type overflow issue.

The issue was uncovered by MTR test case devregression/r/mcs7085_regression_bug3483.test. Actually, the reference result is also incorrect. The reference result needs to be updated when the issue is fixed.

ColumnStore

MariaDB [mytest]> create table t1 (c1 char(120), c2 varchar(120)) engine=columnstore;
 
Query OK, 0 rows affected (0.228 sec)
 
 
 
MariaDB [mytest]> insert into t1 values
 
 ('12345678901234567890', '12345678901234567890'), 
 ('12345678901234567890123456789012345678901234567890', '1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890'),
 
 ('-12345678901234567890', '-12345678901234567890'),
 
 ('-12345678901234567890123456789012345678901234567890', '-1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890');
 
Query OK, 4 rows affected (0.175 sec)
 
Records: 4  Duplicates: 0  Warnings: 0
 
 
 
MariaDB [mytest]> 
MariaDB [mytest]> select c1, truncate(c1, 2) from t1;
 
+-----------------------------------------------------+-----------------------+
 
| c1                                                  | truncate(c1, 2)       |
 
+-----------------------------------------------------+-----------------------+
 
| 12345678901234567890                                |  92233720368547760.00 |
 
| 12345678901234567890123456789012345678901234567890  |  92233720368547760.00 |
 
| -12345678901234567890                               | -92233720368547760.00 |
 
| -12345678901234567890123456789012345678901234567890 | -92233720368547760.00 |
 
+-----------------------------------------------------+-----------------------+
 
4 rows in set (0.026 sec)
 
 
 
MariaDB [mytest]> select c2, truncate(c2, 2) from t1;
 
+-------------------------------------------------------------------------------------------------------+-----------------------+
 
| c2                                                                                                    | truncate(c2, 2)       |
 
+-------------------------------------------------------------------------------------------------------+-----------------------+
 
| 12345678901234567890                                                                                  |  92233720368547760.00 |
 
| 1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890  |  92233720368547760.00 |
 
| -12345678901234567890                                                                                 | -92233720368547760.00 |
 
| -1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890 | -92233720368547760.00 |
 
+-------------------------------------------------------------------------------------------------------+-----------------------+

InnoDB

MariaDB [mytest]> create table t2 (c1 char(120), c2 varchar(120)) engine=innodb;
 
Query OK, 0 rows affected (0.012 sec)
 
 
 
MariaDB [mytest]> insert into t2 values 
    -> ('12345678901234567890', '12345678901234567890'), 
    -> ('12345678901234567890123456789012345678901234567890', '1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890'),
 
    -> ('-12345678901234567890', '-12345678901234567890'),
 
    -> ('-12345678901234567890123456789012345678901234567890', '-1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890');
 
Query OK, 4 rows affected (0.000 sec)
 
Records: 4  Duplicates: 0  Warnings: 0
 
 
 
MariaDB [mytest]> 
MariaDB [mytest]> 
MariaDB [mytest]> select c1, truncate(c1, 2) from t2;
 
+-----------------------------------------------------+--------------------------------------------------------+
 
| c1                                                  | truncate(c1, 2)                                        |
 
+-----------------------------------------------------+--------------------------------------------------------+
 
| 12345678901234567890                                |                                12345678901234567000.00 |
 
| 12345678901234567890123456789012345678901234567890  |  12345678901234567000000000000000000000000000000000.00 |
 
| -12345678901234567890                               |                               -12345678901234567000.00 |
 
| -12345678901234567890123456789012345678901234567890 | -12345678901234567000000000000000000000000000000000.00 |
 
+-----------------------------------------------------+--------------------------------------------------------+
 
4 rows in set (0.000 sec)
 
 
 
MariaDB [mytest]> select c2, truncate(c2, 2) from t2;
 
+-------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------+
 
| c2                                                                                                    | truncate(c2, 2)                                                                                          |
 
+-------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------+
 
| 12345678901234567890                                                                                  |                                                                                  12345678901234567000.00 |
 
| 1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890  |  1234567890123456700000000000000000000000000000000000000000000000000000000000000000000000000000000000.00 |
 
| -12345678901234567890                                                                                 |                                                                                 -12345678901234567000.00 |
 
| -1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890 | -1234567890123456700000000000000000000000000000000000000000000000000000000000000000000000000000000000.00 |
 
+-------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------+
 
4 rows in set (0.000 sec)



 Comments   
Comment by David Hall (Inactive) [ 2022-10-28 ]

The code tries to convert the string to a decimal type. But we don't support that conversion directly, so it converts to double, then to decimal. However, it the value is larger in magnitude than can fit into a 64 bit int (including the right of the decimal after truncation), it saturates.

Investigation shows that MDB converts strings to double and truncates there. I modified MCS to behave similarly.

Comment by Daniel Lee (Inactive) [ 2023-01-17 ]

Build verified: 23.02

engine: 35c8359d90cefef25794bdc908aac9afc94b22a3
server: 689041a5344614e8e3416659b71a6754bb65e490
buildNo: 6492

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