【 使用环境 】测试环境
【 OB or 其他组件 】Oceanbase 4.3.5 bp2
【 使用版本 】Oceanbase 4.3.5 bp2
【问题描述】清晰明确描述问题
我们尝试将测试系统迁移从MySQL迁移至Oceanbase，但是，部分业务调度上的SQL，发现在Oceanbase环境中难以跑出结果。
执行计划上看，感觉有部分异常。
原SQL：
select kh.id_ khKhxxId,
htxx.id_ htHtxxId,
IF(htxx.fwqx_yxyf_ between DATE_FORMAT((CURDATE() - INTERVAL 2 MONTH), '%Y%m') and DATE_FORMAT((CURDATE() + INTERVAL 4 MONTH), '%Y%m'), 2,
IF(htxx.fwqx_yxyf_ > DATE_FORMAT((CURDATE() + INTERVAL 4 MONTH), '%Y%m'), 1, 3)) hzzt,
'crmadmin',
now()
from ftsp_kh_khxx kh
join ftsp_ht_htxx htxx on htxx.id_ = IFNULL((
SELECT id_
FROM ftsp_ht_htxx dqht
WHERE dqht.kh_khxx_id_ = kh.id_
AND dqht.htlx_ IN ('20')
AND dqht.status_ IN ('4', '8')
and fwqx_yxyf_ >= DATE_FORMAT((CURDATE() - INTERVAL 2 MONTH), '%Y%m') and DATE_FORMAT((CURDATE() + INTERVAL 4 MONTH), '%Y%m') >= fwqx_yxyf_
AND dqht.sfxf_ != '2' and (dqht.fwqx_q_ is not null and dqht.fwqx_q_ != '')
and (dqht.fwqx_z_ is not null and dqht.fwqx_z_ != '')
ORDER BY fwqx_yxyf_ DESC
LIMIT 1
), (SELECT id_
FROM ftsp_ht_htxx dqht
WHERE dqht.kh_khxx_id_ = kh.id_
AND dqht.htlx_ IN ('20')
AND dqht.status_ IN ('4', '8')
AND dqht.sfxf_ != '2' and (dqht.fwqx_q_ is not null and dqht.fwqx_q_ != '')
and (dqht.fwqx_z_ is not null and dqht.fwqx_z_ != '')
ORDER BY fwqx_yxyf_ DESC
LIMIT 1))
where exists(select * from ftsp_ht_khfw where kh_khxx_id_ = kh.id_ AND fw_lx_ = 1 and yjjz_ = 1);
MySQL上执行结果：
Oceanbase上一直出不来结果。
从执行计划上看，感觉估算成本有点异常。
==================================================================================================
|ID|OPERATOR |NAME |EST.ROWS |EST.TIME(us) |
--------------------------------------------------------------------------------------------------
|0 |SUBPLAN FILTER | |696527 |160020259673594|
|1 |├─NESTED-LOOP JOIN CARTESIAN | |2504633316573|62824297386 |
|2 |│ ├─TABLE FULL SCAN |htxx(idx_ftsp_ht_htxx_13) |3595890 |48091 |
|3 |│ └─MATERIAL | |696527 |1154759 |
|4 |│ └─HASH RIGHT SEMI JOIN | |696527 |1093643 |
|5 |│ ├─SUBPLAN SCAN |VIEW1 |816411 |273285 |
|6 |│ │ └─TABLE FULL SCAN |ftsp_ht_khfw |816411 |271124 |
|7 |│ └─TABLE FULL SCAN |kh |1643730 |44055 |
|8 |├─TABLE RANGE SCAN |dqht(idx_ftsp_ht_htxx_13,Reverse)|1 |31 |
|9 |└─TABLE RANGE SCAN |dqht(idx_ftsp_ht_htxx_13,Reverse)|1 |34 |
==================================================================================================
Outputs & filters:
-------------------------------------
0 - output([kh.id_], [htxx.id_], [CASE WHEN htxx.fwqx_yxyf_ >= DATE_FORMAT(date_sub(cur_date(), cast(2, VARCHAR(1048576)), 6), '%Y%m') AND htxx.fwqx_yxyf_
<= DATE_FORMAT(date_add(cur_date(), cast(4, VARCHAR(1048576)), 6), '%Y%m') THEN 2 ELSE CASE WHEN htxx.fwqx_yxyf_ > DATE_FORMAT(date_add(cur_date(), cast(4,
VARCHAR(1048576)), 6), '%Y%m') THEN 1 ELSE 3 END END], ['crmadmin'], [current_timestamp()]), filter([htxx.id_ = IFNULL(subquery(1), subquery(2))]), rowset=256
exec_params_([kh.id_(:0)], [kh.id_(:1)]), onetime_exprs_(nil), init_plan_idxs_(nil), use_batch=false
1 - output([kh.id_], [htxx.id_], [htxx.fwqx_yxyf_]), filter(nil), rowset=256
conds(nil), nl_params_(nil), use_batch=false
2 - output([htxx.id_], [htxx.fwqx_yxyf_]), filter(nil), rowset=256
access([htxx.id_], [htxx.fwqx_yxyf_]), partitions(p0)
is_index_back=false, is_global_index=false,
range_key([htxx.kh_khxx_id_], [htxx.fwqx_yxyf_], [htxx.status_], [htxx.id_]), range(MIN,MIN,MIN,MIN ; MAX,MAX,MAX,MAX)always true
3 - output([kh.id_]), filter(nil), rowset=256
4 - output([kh.id_]), filter(nil), rowset=256
equal_conds([VIEW1.ftsp_ht_khfw.kh_khxx_id_ = kh.id_]), other_conds(nil)
5 - output([VIEW1.ftsp_ht_khfw.kh_khxx_id_]), filter(nil), rowset=256
access([VIEW1.ftsp_ht_khfw.kh_khxx_id_])
6 - output([ftsp_ht_khfw.kh_khxx_id_]), filter([ftsp_ht_khfw.yjjz_ = 1], [ftsp_ht_khfw.fw_lx_ = 1]), rowset=256
access([ftsp_ht_khfw.kh_khxx_id_], [ftsp_ht_khfw.fw_lx_], [ftsp_ht_khfw.yjjz_]), partitions(p0)
is_index_back=false, is_global_index=false, filter_before_indexback[false,false],
range_key([ftsp_ht_khfw.id_]), range(MIN ; MAX)always true
7 - output([kh.id_]), filter(nil), rowset=256
access([kh.id_]), partitions(p0)
is_index_back=false, is_global_index=false,
range_key([kh.id_]), range(MIN ; MAX)always true
8 - output([dqht.id_]), filter([dqht.fwqx_z_ != ''], [dqht.fwqx_q_ != ''], [dqht.fwqx_q_ IS NOT NULL], [dqht.fwqx_z_ IS NOT NULL], [dqht.status_ IN ('4',
'8')], [dqht.htlx_ = '20'], [dqht.sfxf_ != demote_cast('2', TINYINT(4, 0))]), rowset=256
access([dqht.id_], [dqht.htlx_], [dqht.status_], [dqht.sfxf_], [dqht.fwqx_q_], [dqht.fwqx_z_]), partitions(p0)
limit(1), offset(nil), is_index_back=true, is_global_index=false, filter_before_indexback[false,false,false,false,true,false,false],
range_key([dqht.kh_khxx_id_], [dqht.fwqx_yxyf_], [dqht.status_], [dqht.id_]), range(MIN,MIN,MIN,MIN ; MAX,MAX,MAX,MAX)always true,
range_cond([dqht.kh_khxx_id_ = :0], [dqht.fwqx_yxyf_ >= DATE_FORMAT(date_sub(cur_date(), cast(2, VARCHAR(1048576)), 6), '%Y%m')], [DATE_FORMAT(date_add(cur_date(),
cast(4, VARCHAR(1048576)), 6), '%Y%m') >= dqht.fwqx_yxyf_])
9 - output([dqht.id_]), filter([dqht.fwqx_z_ != ''], [dqht.fwqx_q_ != ''], [dqht.fwqx_q_ IS NOT NULL], [dqht.fwqx_z_ IS NOT NULL], [dqht.htlx_ = '20'],
[dqht.status_ IN ('4', '8')], [dqht.sfxf_ != demote_cast('2', TINYINT(4, 0))]), rowset=256
access([dqht.id_], [dqht.htlx_], [dqht.status_], [dqht.sfxf_], [dqht.fwqx_q_], [dqht.fwqx_z_]), partitions(p0)
limit(1), offset(nil), is_index_back=true, is_global_index=false, filter_before_indexback[false,false,false,false,false,true,false],
range_key([dqht.kh_khxx_id_], [dqht.fwqx_yxyf_], [dqht.status_], [dqht.id_]), range(MIN,MIN,MIN,MIN ; MAX,MAX,MAX,MAX)always true,
range_cond([dqht.kh_khxx_id_ = :1])
【复现路径】问题出现前后相关操作
【附件及日志】推荐使用OceanBase敏捷诊断工具obdiag收集诊断信息,详情参见链接（右键跳转查看）：
【备注】基于 LLM 和开源文档 RAG 的论坛小助手已开放测试，在发帖时输入 [@论坛小助手] 即可召唤小助手，欢迎试用！