2.1.6，外部数据源是oracle，测试物化视图失效

Question

doris版本：2.1.6
以下流程，用mysql的数据源，都能改写到物化视图上，但oracle出现以下问题：
DROP MATERIALIZED VIEW mv1;
CREATE MATERIALIZED VIEW mv1
BUILD IMMEDIATE REFRESH AUTO ON SCHEDULE EVERY 1 DAY STARTS "2024-11-21 19:10:00"
DISTRIBUTED BY RANDOM BUCKETS 2
PROPERTIES ('replication_num' = '1')
AS
select
a.ID --oracle数据类型NUMBER(18,0)
,a.DATE_ID --oracle数据类型NUMBER(18,0)
,b.cal_date --oracle数据类型varchar2
,c.office_name --oracle数据类型varchar2
from oracle_test.TEST.test01 a
left join oracle_test.TEST.test02 b on a.date_id=b.id
left join oracle_test.TEST.test03 c on a.BILLING_OFFICE_ID=c.id;

--会话设置：
SET enable_nereids_planner = true;
SET enable_materialized_view_rewrite = true;
SET materialized_view_rewrite_enable_contain_external_table = true;
SET materialized_view_rewrite_success_candidate_num = 3;
SET enable_materialized_view_union_rewrite = true;
SET enable_materialized_view_nest_rewrite = true;
SET materialized_view_relation_mapping_max_count = 8;

----------(走了物化视图）
explain
select
a.ID
,a.DATE_ID
,b.cal_date
,c.office_name
from oracle_test.TEST.test01 a
left join oracle_test.TEST.test02 b on a.date_id=b.id
left join oracle_test.TEST.test03 c on a.BILLING_OFFICE_ID=c.id;

----------(不走了物化视图）和第一个sql的差异就是去掉了：c.office_name
explain
select
a.ID
,a.DATE_ID
,b.cal_date
from oracle_test.TEST.test01 a
left join oracle_test.TEST.test02 b on a.date_id=b.id
left join oracle_test.TEST.test03 c on a.BILLING_OFFICE_ID=c.id;

----------(不走了物化视图）
explain
select
c.office_name,count(1) as total
from oracle_test.TEST.test01 a
left join oracle_test.TEST.test02 b on a.date_id=b.id
left join oracle_test.TEST.test03 c on a.BILLING_OFFICE_ID=c.id;
group by c.office_name;

2.1.6，外部数据源是oracle，测试物化视图失效

0 Answers