LIKE与NOT LIKE查询计数不匹配问题排查求助

问题背景 #

总记录数为x，LIKE查询计数为y时，理论上NOT LIKE查询计数应为x-y，但实际执行结果与预期不符，具体SQL及结果如下：

1. 总记录数查询 #

SELECT COUNT(DISTINCT(b.word)) 
FROM "hunspell"."oscar2_sorted" AS b

总计数：9597651

2. LIKE匹配查询 #

SELECT COUNT(distinct(b.word))
FROM "hunspell"."oscar2_sorted" as b 
INNER JOIN invalidswar AS a 
ON b.word LIKE (CONCAT('%', a.word,'%'))

LIKE计数：73116

3. NOT LIKE匹配查询 #

SELECT COUNT(distinct(b.word)) 
FROM "hunspell"."oscar2_sorted" AS b 
INNER JOIN invalidswar AS a
ON b.word NOT LIKE (CONCAT('%', a.word,'%'))

NOT LIKE计数：9597651
预期值：9524535

更新：左连接尝试 #

改用左连接筛选不匹配记录，结果接近预期但仍存在误差：

SELECT COUNT(DISTINCT(b.word))
FROM "hunspell"."oscar2_sorted" AS b 
LEFT JOIN (SELECT DISTINCT(b.word) AS dword 
           FROM "hunspell"."oscar2_sorted" AS b 
           INNER JOIN invalidswar AS a 
           ON b.word LIKE (CONCAT('%', a.word,'%'))) AS d 
ON d.dword = b.word 
WHERE d.dword IS NULL

左连接计数：9536539

更新2：差异根源定位 #

后续发现差值12004源于LIKE与regexp_like的执行逻辑差异：

SELECT count(distinct(b.word)) 
FROM "hunspell"."oscar2_sorted" as b
INNER JOIN invalidswar AS a 
ON regexp_like(b.word, a.word)

regexp_like计数：61112

内容的提问来源于stack exchange，提问作者shantanuo