[MCOL-5215] UNION on DECIMAL returns incorrect results Created: 2022-09-06  Updated: 2023-02-28  Resolved: 2023-02-28

Status: Closed
Project: MariaDB ColumnStore
Component/s: PrimProc
Affects Version/s: None
Fix Version/s: 23.02.1

Type: Bug Priority: Major
Reporter: Jigao Luo Assignee: Gagan Goel (Inactive)
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Relates
relates to MCOL-5205 UNION on signed and unsigned integer ... Closed
relates to MCOL-5417 Full support for DECIMAL(65, 38) in C... Open
Assigned for Review: Gagan Goel Gagan Goel (Inactive)
Assigned for Testing: Daniel Lee Daniel Lee (Inactive)

 Description   

Here is an example SQL script with wrong outputs when UNOIN of MCS computes with DECIMAL with 38 digits precision when the precision is saturated.

An example wrong case is a UNION with DECIMAL(38) and DECIMAL(38,38). The result type in the system is a DECIMAL(38,38), which is wrong and can not represent any number with digits before the `.`.

SET default_storage_engine=ColumnStore;
 
CREATE TABLE t1 (a DECIMAL(38), b DECIMAL(38,10), c DECIMAL(38,38));
 
INSERT INTO t1 VALUES (-125, -1.25, -0.125);
 
SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT c FROM t1) tu ORDER BY a


+-------------------------------------------+
 
| a                                         |
 
+-------------------------------------------+
 
| -0.12500000000000000000000000000000000000 |
 
|  0.90447576074723148144860474975423823872 |
 
+-------------------------------------------+
 
2 rows in set (0.031 sec)

The correct behaviour is to throw a warning when the precision of DECIMAL is saturated. However, there are some cases to be discussed.

 Comments   
Comment by Gagan Goel (Inactive) [ 2023-02-28 ]

For QA:

We have added a new error message if the UNION operation exceeds the currently supported maximum DECIMAL precision of 38 digits:

ERROR 1178 (42000): The storage engine for the table doesn't support MCS-2060: Union operation exceeds maximum DECIMAL precision of 38

Once the support for MCOL-5417 is added, the above query in the issue description should execute as expected and this error message will be removed.

Comment by Daniel Lee (Inactive) [ 2023-02-28 ]

Build verified:

engine: 4d4e4ad30dd2ec494ea9d323e1fa7fa69e97243e
server: 1916028f898cf672a8b79d2b585b1d74f8bcd7db
buildNo: 6965

Verified with the test case in the description.

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