[Bug] Materialized view error: Aggregation type must be the same as base column

jlerebours commented 1 year ago

Search before asking

[X] I had searched in the issues and found no similar issues.

Version

1.2.1

What's Wrong?

Impossible to create the simplest materialized view having a group by for a table with aggregate model

What You Expected?

To be able to create a view on an Aggregate Key model, nothing in the documentation specifying it should not work. According to me that's the base use case of aggregate model no ?

How to Reproduce?

I just used the simplest example visible in the documentation and added a REPLACE on one column so that my model is of type Aggregate. Here are the steps to reproduce :

- create table sales_records(record_id int, seller_id int, store_id int, sale_date date, sale_amt bigint REPLACE) distributed by hash(record_id) ;
- show create table sales_records
- insert into sales_records values(1,1,1,"2020-02-02",1);
- create materialized view store_amt as select store_id, sum(sale_amt) from sales_records group by store_id;

The last query returns this error : ERROR 1105 (HY000): errCode = 2, detailMessage = The aggregation type of column[sale_amt] must be same as the aggregate type of base column in aggregate table

Anything Else?

If you just remove the REPLACE for the column sale_amt, then the model of table becomes Duplicate Key and materialized view can be created without problem.

I also tested without the sum, just with a simple select of a field and same problem :

- create table sales_records(record_id int, seller_id int, store_id int REPLACE, sale_date date REPLACE, sale_amt bigint REPLACE) distributed by hash(record_id);
- insert into sales_records values(1,1,1,"2020-02-02",1);
- create materialized view store_amt as select store_id, sum(sale_amt) from sales_records group by store_id;

Sorry if my issue is not very clean, that's my first one !

Are you willing to submit PR?

[ ] Yes I am willing to submit a PR!

Code of Conduct

[X] I agree to follow this project's Code of Conduct

jlerebours commented 1 year ago

Just giving some more information, after investigating a bit more : The problem is actually about the aggregation type and not as I said the data type (int, varchar, etc)

And more particularly one thing that I don't get is that we have to keep the same type as original/base column meaning that i have to use :

min(k1) in materialized view if k1 is MIN
max(k2) in materialized view if k2 is MAX
sum(k3) in materialized view if k3 is SUM
but what about REPLACE type ? that is my actual problem

And this leads to my question : why can't we do some sum(k1) if k1 is MIN ? The main purpose of views and aggregating is often to sum stuff even if model is MIN.

But my main question is : how to handle columns that are of type REPLACE ?

wypzj commented 9 months ago

so,cannot deal this problem now?

apache / doris