【详述】最近业务
【背景】详见sql
【业务影响】
【StarRocks版本】2.0.0-GA
【集群规模】3fe+9be (fe与be混部)
【机器信息】16C64G
sql1：
SELECT a.msg_id ,
a.dp,
a.company_id,
a.day ,
msg_content ,
a.msg_time,
b.time_int,
diff_second,
reply_detail,
sender_type,
b.room_id
FROM
(SELECT msg_id,
dp,
company_id,
day,
diff_second,
reply_detail,
msg_time,
is_group,
rn_id
FROM dwd.dwd_crm_timeout_chat_msg_compare_middle_i_h
WHERE is_group = TRUE and reply_detail != ‘no’
and day=‘2022-02-18’ and company_id=‘W00000000001’ and rn_id like ‘%wrJawBCQAAaci3KbIX5c6Ad388oMWOKQ%’ ) a
inner JOIN
(SELECT msg_id,
dp,
company_id,
day,
msg_content,
sender_type,
room_id,
TIME_TO_SEC(date_format(msg_time, ‘%H:%i:%s’)) as time_int
FROM dwd.dwd_crm_timeout_group_chat_msg_middle_i_h
WHERE NOT is_revoke and day=‘2022-02-18’ and company_id=‘W00000000001’
and room_id=‘wrJawBCQAAaci3KbIX5c6Ad388oMWOKQ’ ) b
ON
a.company_id=b.company_id
and a.dp=b.dp
and a.msg_id=b.msg_id
AND a.day=b.day
ORDER BY a.msg_time;
join的结果不对，那么，我们将join on的条件变为
and trim(a.msg_id)=trim(b.msg_id)
相关结果就正确了。
猜想：
msg_id 作为join的条件，是否存在长度或者内容上的要求，比如不能包含空格内容？或者有长度上的要求