[MCOL-4940] Round function fail - query with case statements and subselect Created: 2021-12-07  Updated: 2022-10-03  Resolved: 2022-02-22

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

Type: Bug Priority: Critical
Reporter: Allen Herrera Assignee: Daniel Lee (Inactive)
Resolution: Fixed Votes: 0
Labels: None

Attachments: File RoundingIssue.sql     File UMTS_ALL_SECTOR_HOURLY.ddl     File UMTS_HIST_SECTOR_HOURLY.ddl    
Sprint: 2021-16, 2021-17

 Description   

Summary
There appears to be a bug with ROUND() on doubles in case statements in conjunction with subqueries. The workaround is to use round(, 3) - or anything but round(,2). Given recent full decimal support there might be a bug or default value that's overriding the round( ,2).

Simplified Reproduction Steps:

create table rounding_table ( a int, b double, c double) engine=columnstore;
 
insert into rounding_table values (26805, 1252, -9647);
 
insert into rounding_table values (26806, 573, -2804.5);
 
 
 
SELECT
 
    CASE a
 
    WHEN 26805 THEN ROUND(c/b, 2)
 
    WHEN 26806 THEN b
 
END wrongNumberOfDecimals,
 
ROUND(c/b, 2) as correctNumberOfDecimals
 
FROM (
 
    SELECT a, SUM(b) b, SUM(c) c FROM rounding_table
 
    GROUP BY a
 
) abc ;

Results:

+-----------------------+-------------------------+
 
| wrongNumberOfDecimals | correctNumberOfDecimals |
 
+-----------------------+-------------------------+
 
|                   573                  |                   -4.89             |
 
|    -7.709999999999999 |                   -7.71              |
 
+-----------------------+-------------------------+

Expected: -7.71

Actual: -7.709999999999999

 Comments   
Comment by Daniel Lee (Inactive) [ 2022-02-22 ]

Build verified: 6.2.4-1 (b3993)

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