bug: the group function does not return the correct result

[cdmikechen]

Search before asking

• I had searched in the issues and found no similar issues.

Version

v1.2.615

What's Wrong?

The cumulative (like for annual) sum function does not return the expected value:

databend: wrong result and not sure what to do with the result.

postgresql: right result we expected.

How to Reproduce?

databend:

drop table test_group;
create table test_group (
  input_date date,
  value decimal(10,2)
);
insert into test_group values ('2024-01-01', 100),('2024-01-01', 200),('2024-01-02', 400),('2024-01-02', 800);

select input_date, sum(value), 
(select sum(value)
	    from test_group b
	    where b.input_date >= DATE_TRUNC(YEAR, input_date)
	      and b.input_date <= a.input_date) as total
from test_group a
group by input_date;

postgresql:

drop table test_group;
create table test_group (
  input_date date,
  value decimal(10,2)
);
insert into test_group values ('2024-01-01', 100),('2024-01-01', 200),('2024-01-02', 400),('2024-01-02', 800);

select input_date, sum(value), 
(select sum(value)
    from test_group b
    where b.input_date >= date_trunc('year', input_date)
      and b.input_date <= a.input_date) as total
from test_group a
group by input_date;

Are you willing to submit PR?

• Yes I am willing to submit a PR!

[cdmikechen]

After validation of more row values, it appears that the result of sum is multiplied by the number of same data rows.