apache / apache/doris

[Bug] Incorrect result of show create MTMV when partition by date_trunc alias

Open
#62,910 0 comments 0 reactions 0 assignees View on GitHub
Dominant language
Java
Stars
15.9k
Forks
3.9k
Avg merge
2d 21h
Merged PRs (30d)
531

Description

### Search before asking

- [x] I had searched in the [issues](https://github.com/apache/doris/issues?q=is%3Aissue) and found no similar issues.

### Version

master, latest version

### What's Wrong?

The result returned by `show create materialized view` is inconsistent with the initial creation statement.

### What You Expected?

The input and output of the creation statement shouled keep consistent.

### How to Reproduce?

create table
```
CREATE TABLE test (
`k1` LARGEINT NOT NULL,
`k2` DATE NOT NULL
) ENGINE=OLAP
DUPLICATE KEY(`k1`)
COMMENT 'OLAP'
PARTITION BY range(`k2`)
(
PARTITION p_20200101 VALUES [("2020-01-01"),("2020-01-02")),
PARTITION p_20200102 VALUES [("2020-01-02"),("2020-01-03")),
PARTITION p_20200201 VALUES [("2020-02-01"),("2020-02-02"))
)
DISTRIBUTED BY HASH(`k1`) BUCKETS 2
PROPERTIES ('replication_num' = '1') ;
```

create mv
```
CREATE MATERIALIZED VIEW mv_month
BUILD DEFERRED REFRESH AUTO ON MANUAL
partition by (month_dt)
DISTRIBUTED BY RANDOM BUCKETS 2
PROPERTIES (
'replication_num' = '1'
)
AS select date_trunc(`k2`, 'month') as month_dt, k1 from test;
```

show create mv
```
CREATE MATERIALIZED VIEW mv_month
(month_dt,k1)
BUILD DEFERRED REFRESH AUTO ON MANUAL
DUPLICATE KEY(`month_dt`, `k1`)
PARTITION BY (date_trunc(`month_dt`, 'month'))
DISTRIBUTED BY RANDOM BUCKETS 2
PROPERTIES (
"replication_allocation" = "tag.location.default: 1",
"min_load_replica_num" = "-1",
"is_being_synced" = "false",
"storage_medium" = "hdd",
"storage_format" = "V2",
"inverted_index_storage_format" = "V3",
"light_schema_change" = "true",
"disable_auto_compaction" = "false",
"enable_single_replica_compaction" = "false",
"group_commit_interval_ms" = "10000",
"group_commit_data_bytes" = "134217728"
)
AS select date_trunc(`internal`.`test`.`test`.`k2`, 'month') as `month_dt`, `internal`.`test`.`test`.`k1` from `internal`.`test`.`test`
```

The output is `PARTITION BY (date_trunc(month_dt, 'month'))` while the input is `partition by (month_dt)`.

### Anything Else?

_No response_

### Are you willing to submit PR?

- [x] Yes I am willing to submit a PR!

### Code of Conduct

- [x] I agree to follow this project's [Code of Conduct](https://www.apache.org/foundation/policies/conduct)

Contributor guide

Open the contributing guide

Research direction

Start with the reproduction SQL in this issue and trace the materialized-view SHOW CREATE serialization path. Compare the input partition expression with the emitted PARTITION BY clause, then verify that SHOW CREATE preserves the original alias-based partitioning statement.

Written by the indexing model from the issue text.

Assessment

Tech stack
java, sql
Domain
databases
Issue type
Bug
Difficulty
3/5
Estimated time
1-2 days
Activity status
Quiet
Clarity
Mostly clear
Newbie friendliness
58/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.