【详述】问题详细描述
not in 查询,不应该出现的数据出现了.
【背景】做过哪些操作？
【业务影响】
数据结果异常
【StarRocks版本】例如：1.19.2
【集群规模】例如：3fe（1 follower+2observer）+3be（fe与be混部）
【机器信息】CPU虚拟核/内存/网卡，例如：48C/64G/万兆
SQL:
select *
from (
select ord.EQUITY_ORDER_ID
from mysql_external_rebuild20.T__EQUITYORDER ord
join mysql_external_rebuild20.T__EQUITYCODE code
on code.EQUITY_ORDER_ID = ord.EQUITY_ORDER_ID and IS_VERIFIED = ‘T’
where code.VERIFY_TIME between ‘2021-12-24 16:30:00’ and ‘2021-12-24 16:44:59’ ) t1
where t1.EQUITY_ORDER_ID not in
(
select ord.EQUITY_ORDER_ID
from mysql_rebuild20.T__EQUITYORDER_view ord
join mysql_rebuild20.T__EQUITYCODE_view code
on code.EQUITY_ORDER_ID = ord.EQUITY_ORDER_ID and IS_VERIFIED = ‘T’
where code.VERIFY_TIME between ‘2021-12-24 16:30:00’ and ‘2021-12-24 16:44:59’
);
查出存在ID 978-017de64bdXXXX
然后分别从子表查
select *
from mysql_rebuild20.T__EQUITYORDER_view
where EQUITY_ORDER_ID = ‘978-017de64bdXXXX’;
select *
from mysql_rebuild20.T__EQUITYCODE_view
where EQUITY_ORDER_ID = ‘978-017de64bdXXXX’;
发现并没有异常…
照理说第一个查询不应该查出这个ID
后关闭CBO优化…结果正常.