adonis0147 opened a new issue, #14929: URL: https://github.com/apache/doris/issues/14929
### Search before asking - [X] I had searched in the [issues](https://github.com/apache/doris/issues?q=is%3Aissue) and found no similar issues. ### Description When we create a materialized view for multiple tables, users may not figure out the partition rule for the materialized view, because the query result can be too complex. If the query result doesn't match one of the partition rules, the insertion will fail. We can resolve this issue by mapping the partition rule of base table to the materialized view. ### Use case ```shell mysql> CREATE TABLE t1 (pk INT NOT NULL, v1 INT SUM) PARTITION BY RANGE(pk) ( -> PARTITION p1 VALUES LESS THAN ('10'), -> PARTITION p2 VALUES LESS THAN ('90') -> ) -> DISTRIBUTED BY HASH(pk) -> PROPERTIES ('replication_num' = '1'); Query OK, 0 rows affected (0.04 sec) mysql> CREATE TABLE t2 (pk INT NOT NULL, v2 INT SUM) PARTITION BY LIST(pk) ( -> PARTITION odd VALUES IN ('10', '30', '50', '70', '90'), -> PARTITION even VALUES IN ('20', '40', '60', '80') -> ) -> DISTRIBUTED BY HASH(pk) -> PROPERTIES ('replication_num' = '1'); Query OK, 0 rows affected (0.02 sec) mysql> CREATE MATERIALIZED VIEW mv BUILD IMMEDIATE REFRESH COMPLETE -> KEY (mpk) PARTITION BY (t1.pk) DISTRIBUTED BY HASH(mpk) PROPERTIES ('replication_num' = '1') -> AS SELECT t1.pk AS mpk, v1, v2 FROM t1, t2 WHERE t1.pk = t2.pk; Query OK, 0 rows affected (0.10 sec) mysql> SHOW CREATE TABLE mv; +-------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Materialized View | Create Materialized View | +-------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | mv | CREATE MATERIALIZED VIEW `mv` BUILD IMMEDIATE REFRESH COMPLETE ON DEMAND KEY(`mpk`) PARTITION BY RANGE(`mpk`) (PARTITION p1 VALUES [("-2147483648"), ("10")), PARTITION p2 VALUES [("10"), ("90"))) DISTRIBUTED BY HASH(`mpk`) BUCKETS 10 PROPERTIES ( "replication_allocation" = "tag.location.default: 1", "in_memory" = "false", "storage_format" = "V2", "disable_auto_compaction" = "false" ) AS SELECT `t1`.`pk` AS `mpk`, `v1` AS `v1`, `v2` AS `v2` FROM `default_cluster:dev`.`t1` , `default_cluster:dev`.`t2` WHERE `t1`.`pk` = `t2`.`pk`; | +-------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) ``` ### Related issues #7503 ### 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) -- 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.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
