foxtail463 commented on PR #65393:
URL: https://github.com/apache/doris/pull/65393#issuecomment-4915154764
```sql
DROP DATABASE IF EXISTS mv_partition_unrelated_right_branch_repro;
CREATE DATABASE mv_partition_unrelated_right_branch_repro;
USE mv_partition_unrelated_right_branch_repro;
CREATE TABLE mv_pr_left (
sale_ord_id BIGINT NOT NULL,
reg_no VARCHAR(20),
dt DATE NOT NULL
)
DUPLICATE KEY(sale_ord_id, reg_no, dt)
PARTITION BY RANGE(dt) (
PARTITION p20260701 VALUES [('2026-07-01'), ('2026-07-02'))
)
DISTRIBUTED BY HASH(sale_ord_id) BUCKETS 1
PROPERTIES ("replication_num" = "1");
CREATE TABLE mv_pr_right (
order_id BIGINT NOT NULL,
erp VARCHAR(20),
dept_2_new VARCHAR(20),
create_time DATETIME,
dt DATE NOT NULL,
is_bypass_tp_ic_cmpl TINYINT
)
DUPLICATE KEY(order_id, erp, dept_2_new, create_time, dt)
PARTITION BY RANGE(dt) (
PARTITION p20260701 VALUES [('2026-07-01'), ('2026-07-02'))
)
DISTRIBUTED BY HASH(order_id) BUCKETS 1
PROPERTIES ("replication_num" = "1");
INSERT INTO mv_pr_left VALUES
(1, 'r1', '2026-07-01'),
(2, 'r2', '2026-07-01');
INSERT INTO mv_pr_right VALUES
(1, 'e1_old', 'd1', '2026-07-01 10:00:00', '2026-07-01', 1),
(1, 'e1_new', 'd2', '2026-07-01 11:00:00', '2026-07-01', 1),
(2, 'e2', 'd3', '2026-07-01 12:00:00', '2026-07-01', 1);
DROP MATERIALIZED VIEW IF EXISTS mv_pr_right_window_rn;
CREATE MATERIALIZED VIEW mv_pr_right_window_rn
BUILD IMMEDIATE REFRESH ON MANUAL
PARTITION BY (dt)
DISTRIBUTED BY RANDOM BUCKETS 1
PROPERTIES ("replication_num" = "1")
AS
SELECT
a.reg_no,
a.sale_ord_id,
a.dt,
r.erp,
r.dept_2_new
FROM mv_pr_left AS a
LEFT JOIN (
SELECT
order_id,
erp,
dept_2_new,
ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY create_time DESC
NULLS LAST) AS rn,
dt AS r_dt
FROM mv_pr_right
WHERE dt = '2026-07-01' AND is_bypass_tp_ic_cmpl = 1
) AS r
ON a.sale_ord_id = r.order_id
AND r.rn = 1
WHERE a.dt = '2026-07-01';
DROP MATERIALIZED VIEW IF EXISTS mv_pr_right_agg;
CREATE MATERIALIZED VIEW mv_pr_right_agg
BUILD IMMEDIATE REFRESH ON MANUAL
PARTITION BY (dt)
DISTRIBUTED BY RANDOM BUCKETS 1
PROPERTIES ("replication_num" = "1")
AS
SELECT
a.reg_no,
a.sale_ord_id,
a.dt,
r.max_create_time
FROM mv_pr_left AS a
LEFT JOIN (
SELECT
order_id,
MAX(create_time) AS max_create_time
FROM mv_pr_right
WHERE dt = '2026-07-01'
GROUP BY order_id
) AS r
ON a.sale_ord_id = r.order_id
WHERE a.dt = '2026-07-01';
SHOW CREATE MATERIALIZED VIEW mv_pr_right_window_rn;
SHOW CREATE MATERIALIZED VIEW mv_pr_right_agg;
```
--
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]
