EmmyMiao87 opened a new issue #4586: URL: https://github.com/apache/incubator-doris/issues/4586
**Describe the bug**
The query does not select the best materialized view when there is a pre-agg
mv on dup table.
**To Reproduce**
Steps to reproduce the behavior:
1. create table
```
CREATE TABLE test_sys_materialized_view_2_test_create_dup_max_mv_tb_dup ( k0
BOOLEAN NOT NULL, k1 TINYINT NOT NULL, k2 SMALLINT NOT NULL, k3 INT NOT NULL,
k4 BIGINT NOT NULL, k5 LARGEINT NOT NULL, k6 DECIMAL(9,3) NOT NULL, k7 CHAR(5)
NOT NULL, k8 DATE NOT NULL, k9 DATETIME NOT NULL, k10 VARCHAR(20) NOT NULL, k11
DOUBLE NOT NULL, k12 FLOAT NOT NULL ) DISTRIBUTED BY HASH(k0) BUCKETS 2
```
2. load data
Import a few rows of data
3. create materialized view
```
CREATE MATERIALIZED VIEW max_mv AS select k0, max(k1), max(k2), max(k3),
max(k4), max(k5), max(k6), max(k7), max(k8), max(k9), max(k10), max(k11),
max(k12) from test_sys_materialized_view_2_test_create_dup_max_mv_tb_dup group
by k0
```
4. explain query
```
EXPLAIN select k0, max(k1), max(k2), max(k3), max(k4), max(k5), max(k6),
max(k7), max(k8), max(k9), max(k10), max(k11), max(k12) from
test_sys_materialized_view_2_test_create_dup_max_mv_tb_dup group by k0;
| 0:OlapScanNode
|
| TABLE: test_sys_materialized_view_2_test_create_dup_max_mv_tb_dup
|
| PREAGGREGATION: ON
|
| partitions=1/1
|
| rollup: test_sys_materialized_view_2_test_create_dup_max_mv_tb_dup
|
| tabletRatio=2/2
|
| tabletList=29049,29051
|
| cardinality=2
|
| avgRowSize=753.5
|
| numNodes=1
```
**Expected behavior**
rollup: max_mv
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
