[MCOL-4492] Aggregated DOUBLES (AVG and SUM) do not compare properly, especially with constants and non aggregated doubles. Created: 2021-01-12  Updated: 2021-03-31  Resolved: 2021-03-22

Status: Closed
Project: MariaDB ColumnStore
Component/s: ExeMgr
Affects Version/s: 1.5.2
Fix Version/s: 6.1.1

Type: Bug Priority: Blocker
Reporter: NaHuangYi Assignee: David Hall (Inactive)
Resolution: Duplicate Votes: 0
Labels: bug
Environment:

Ubuntu 20.04.1 LTS

Issue Links:
Duplicate
duplicates MCOL-4603 Replace long double with wide/narrow-... Closed
PartOf
is part of MCOL-641 Full DECIMAL support in ColumnStore Closed
Sprint: 2021-4, 2021-5

 Description   

BUG测试
create table test_t
(
id int,
sv dec(10,2)
)ENGINE=ColumnStore;

create table test_t1
(
id int,
sv dec(10,2)
)ENGINE=ColumnStore;
insert into test_t(id,sv) values(1,4.90);
insert into test_t(id,sv) values(1,4.90);
insert into test_t(id,sv) values(1,4.90);
insert into test_t1(id,sv) values(1,14.70);

select * from (select id,sum(sv) sv from test_t group by 1) a,
(select id,sum(sv) sv from test_t1 group by 1) b
where a.id=b.id and a.sv<>b.sv;

返回:
--------------------+

id sv id sv

--------------------+

1 14.70 1 14.70

--------------------+
1 row in set (0.09 sec)

修改引擎为innodb后正常
alter table test_t engine=innodb;
alter table test_t1 engine=innodb;
再改为columnstore后bug再现
alter table test_t engine=columnstore;
alter table test_t1 engine=columnstore;

 Comments   
Comment by Todd Stoffel (Inactive) [ 2021-01-13 ] 

SET columnstore_select_handler=OFF;
 
 
 
select * from (select id,sum(sv) sv from test_t group by 1) a,
 
(select id,sum(sv) sv from test_t1 group by 1) b
 
where a.id=b.id and a.sv<>b.sv;
 
 
 
Empty set (0.000 sec)


SET columnstore_select_handler=ON;
 
 
 
select * from (select id,sum(sv) sv from test_t group by 1) a,
 
(select id,sum(sv) sv from test_t1 group by 1) b
 
where a.id=b.id and a.sv<>b.sv;
 
 
 
+------+-------+------+-------+
 
| id   | sv    | id   | sv    |
 
+------+-------+------+-------+
 
|    1 | 14.70 |    1 | 14.70 |
 
+------+-------+------+-------+

Comment by Gregory Dorman (Inactive) [ 2021-02-22 ]

The theory is that this problem will go away with LONG DECIMAL. Please verify the theory. If wrong - EMERGENCY. If right - handle the ticket transitions.

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