starocean999 commented on PR #28886: URL: https://github.com/apache/doris/pull/28886#issuecomment-1870895767
> > I don't understand why we need to support this case, it seems meaningless? > > If we create a rollup or a materialized view for data preaggregation, and then use `SUM(case ... when ... then)` or `SUM(if(... , ... , ...))`, Doris can't hit rollup or materialized view we created before. > > simple case: > > create table: CREATE TABLE test_tbl ( key1 INT NOT NULL, key2 INT NULL, key3 INT NULL, value BIGINT sum NULL ) ENGINE=OLAP AGGREGATE KEY(key1, key2, key3) COMMENT 'OLAP' DISTRIBUTED BY HASH(key1) BUCKETS 10 PROPERTIES ( "replication_num" = "1" ); > > create rollup: `alter table test_tbl add rollup test_rollup(key1,key2,value);` > > I hope this query can hit rollup **test_rollup** `select key1,sum(case when key2 > 0 then value else 0 end) as value from test_tbl group by key1;` Thanks for your effort. But this pr is not correct, because the sql above SHOULD NOT hit mv. The sql bellow is correct one which need to hit mv(but not supported now). `select key1,sum(case when key2 > 0 then value else 0 end) as value from test_tbl where key2 > 0 group by key1;` This sql is equivalent to `select key1,sum(value) as value from test_tbl where key2 > 0 group by key1;` As the mv selection logical is being refactoring in nereids. We can considering such optimization after the refactoring work done. ( This refactoring is scheduled, but not in near future) -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: commits-unsubscr...@doris.apache.org For queries about this service, please contact Infrastructure at: us...@infra.apache.org --------------------------------------------------------------------- To unsubscribe, e-mail: commits-unsubscr...@doris.apache.org For additional commands, e-mail: commits-h...@doris.apache.org
