foxtail463 commented on PR #64036:
URL: https://github.com/apache/doris/pull/64036#issuecomment-4604232449
### **minimal reproducible case：**
```sql
DROP DATABASE IF EXISTS mv_id_conflict_demo;
CREATE DATABASE mv_id_conflict_demo;
USE mv_id_conflict_demo;
SET enable_nereids_planner = true;
SET enable_fallback_to_original_planner = false;
SET enable_materialized_view_rewrite = true;
SET enable_materialized_view_nest_rewrite = true;
SET enable_nereids_timeout = false;
SET materialized_view_rewrite_duration_threshold_ms = 1800000;
CREATE TABLE fact_src (
dt DATE NOT NULL,
k VARCHAR(32) NOT NULL,
is_dyn VARCHAR(8),
sku_type VARCHAR(8)
) DUPLICATE KEY(dt, k)
PARTITION BY RANGE(dt) (PARTITION p1 VALUES LESS THAN ('2026-02-05'))
DISTRIBUTED BY HASH(k) BUCKETS 1
PROPERTIES ("replication_allocation" = "tag.location.default: 1");
CREATE TABLE dim_full (
dt DATE NOT NULL,
k VARCHAR(32) NOT NULL,
sku_type VARCHAR(8),
is_dyn VARCHAR(8),
bu VARCHAR(32),
mode_flag VARCHAR(8),
double_flag VARCHAR(8)
) UNIQUE KEY(dt, k, sku_type, is_dyn)
PARTITION BY RANGE(dt) (PARTITION p1 VALUES LESS THAN ('2026-02-05'))
DISTRIBUTED BY HASH(k) BUCKETS 1
PROPERTIES ("replication_allocation" = "tag.location.default: 1");
CREATE VIEW v_dim_full_non_double AS
SELECT dt, k, mode_flag, sku_type FROM dim_full WHERE double_flag = '0';
INSERT INTO fact_src VALUES ('2026-02-04', 'K1', '0', '1');
INSERT INTO dim_full VALUES ('2026-02-04', 'K1', '1', '0', 'D1', 'M', '0');
DROP MATERIALIZED VIEW IF EXISTS mv_fact;
CREATE MATERIALIZED VIEW mv_fact
BUILD IMMEDIATE REFRESH COMPLETE ON MANUAL
PARTITION BY (dt)
DISTRIBUTED BY HASH(k) BUCKETS 1
PROPERTIES ("replication_allocation" = "tag.location.default: 1")
AS
SELECT dt, k, is_dyn, sku_type
FROM fact_src
WHERE sku_type = '1';
DROP MATERIALIZED VIEW IF EXISTS mv_dim_full;
CREATE MATERIALIZED VIEW mv_dim_full
BUILD IMMEDIATE REFRESH COMPLETE ON MANUAL
PARTITION BY (dt)
DISTRIBUTED BY HASH(k) BUCKETS 1
PROPERTIES ("replication_allocation" = "tag.location.default: 1")
AS
SELECT dt, k, bu, is_dyn, sku_type
FROM dim_full;
DROP MATERIALIZED VIEW IF EXISTS mv_dim_full_view_non_double;
CREATE MATERIALIZED VIEW mv_dim_full_view_non_double
BUILD IMMEDIATE REFRESH COMPLETE ON MANUAL
PARTITION BY (dt)
DISTRIBUTED BY HASH(k) BUCKETS 1
PROPERTIES ("replication_allocation" = "tag.location.default: 1")
AS
SELECT dt, k, mode_flag, sku_type
FROM v_dim_full_non_double;
DROP MATERIALIZED VIEW IF EXISTS mv_target;
CREATE MATERIALIZED VIEW mv_target
BUILD IMMEDIATE REFRESH COMPLETE ON MANUAL
PARTITION BY (dt)
DISTRIBUTED BY HASH(k) BUCKETS 1
PROPERTIES ("replication_allocation" = "tag.location.default: 1")
AS
SELECT
t.dt,
t.k,
d0.bu AS out_bu,
d1.mode_flag AS out_mode
FROM mv_fact t
LEFT JOIN mv_dim_full d0
ON t.dt = d0.dt
AND t.k = d0.k
AND t.sku_type = d0.sku_type
AND t.is_dyn = d0.is_dyn
LEFT JOIN mv_dim_full_view_non_double d1
ON t.dt = d1.dt
AND t.k = d1.k
AND t.sku_type = d1.sku_type;
REFRESH MATERIALIZED VIEW mv_fact COMPLETE;
REFRESH MATERIALIZED VIEW mv_dim_full COMPLETE;
REFRESH MATERIALIZED VIEW mv_dim_full_view_non_double COMPLETE;
--等几秒
REFRESH MATERIALIZED VIEW mv_target COMPLETE;
EXPLAIN
SELECT
t.dt,
t.k,
d0.bu AS out_bu,
d1.mode_flag AS out_mode
FROM fact_src t
LEFT JOIN dim_full d0
ON t.dt = d0.dt
AND t.k = d0.k
AND t.sku_type = d0.sku_type
AND t.is_dyn = d0.is_dyn
LEFT JOIN v_dim_full_non_double d1
ON t.dt = d1.dt
AND t.k = d1.k
AND t.sku_type = d1.sku_type
WHERE t.dt = '2026-02-04'
AND t.sku_type = '1'
ORDER BY t.k;
```
--
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: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
