【 TiDB 使用环境】生产环境
【 TiDB 版本】v6.5.3
【情况说明】
- 通过参数设置，强制走tiflash mpp
- 多次查询结果不一致，少一条数据，并且少的是固定的一条
- 如果让集群自动选择查询引擎，结果正常
- 如果将sql中3个left join改写成两个最后在union，结果正常
【explain analyze执行计划对比】
【sql如下】
SELECT
full_data.field1 AS field1,
full_data.field2 AS field2,
(CASE WHEN t1.total IS NULL THEN 0 ELSE t1.total END) +(CASE WHEN t2.total IS NULL THEN 0 ELSE t2.total END) AS total
FROM
(SELECT
CASE
WHEN group0.group_id IS NOT NULL THEN CONCAT(group0.group_id, '(组)')
ELSE dimension0.`patent_type`
END AS field1,
CASE
WHEN group1.group_id IS NOT NULL THEN CONCAT(group1.group_id, '(组)')
ELSE dimension1.`country`
END AS field2
FROM dw_ext.`t_1692603597355` t
INNER JOIN ads_tab11 dimension0 ON dimension0.pdoc_id = t.pdoc_id
LEFT JOIN biz.ads_tab22 group0 ON group0.group_value = dimension0.`patent_type` and group0.field = 'PATENT_TYPE' and group0.identifier_id = '0fb4b7e7fe0642618a1e7288faf7d8ae'
INNER JOIN ads_tab33 dimension1 ON dimension1.pdoc_id = t.pdoc_id
LEFT JOIN biz.ads_tab22 group1 ON group1.group_value = dimension1.`country` and group1.field = 'COUNTRY' and group1.identifier_id = '0fb4b7e7fe0642618a1e7288faf7d8ae'
WHERE 1=1
AND dimension0.`patent_type` in ('A','B','U','D') AND dimension0.`patent_type` IS NOT NULL
AND dimension1.`country` in ('US','CN') AND dimension1.`country` IS NOT NULL
GROUP BY field1,field2
HAVING COUNT(t.pdoc_id) > 0
) full_data
LEFT JOIN
(
SELECT field1,field2,COUNT(`pn`) AS total
FROM (
SELECT
CASE
WHEN group0.group_id IS NOT NULL THEN CONCAT(group0.group_id, '(组)')
ELSE dimension0.`patent_type`
END AS field1,
CASE
WHEN group1.group_id IS NOT NULL THEN CONCAT(group1.group_id, '(组)')
ELSE dimension1.`country`
END AS field2,dimension1.`pn`
FROM dw_ext.`t_1692603597355` t
INNER JOIN ads_tab11 dimension0 ON dimension0.pdoc_id = t.pdoc_id
LEFT JOIN biz.ads_tab22 group0 ON group0.group_value = dimension0.`patent_type` and group0.field = 'PATENT_TYPE' and group0.identifier_id = '0fb4b7e7fe0642618a1e7288faf7d8ae'
INNER JOIN ads_tab33 dimension1 ON dimension1.pdoc_id = t.pdoc_id
LEFT JOIN biz.ads_tab22 group1 ON group1.group_value = dimension1.`country` and group1.field = 'COUNTRY' and group1.identifier_id = '0fb4b7e7fe0642618a1e7288faf7d8ae'
WHERE 1=1
AND dimension0.`patent_type` in ('A','B','U','D') AND dimension0.`patent_type` IS NOT NULL
AND dimension1.`country` in ('US','CN') AND dimension1.`country` IS NOT NULL
AND dimension1.`pn` IS NOT NULL
AND dimension1.`country` IN ('US')
GROUP BY t.pdoc_id, dimension1.`pn`,field1,field2
) T
GROUP BYfield1,field2
ORDER BY total DESC,field1 ASC,field2 ASC
) t1 ON
t1.field1 = full_data.field1 AND
t1.field2 = full_data.field2
LEFT JOIN
(
SELECT field1,field2,COUNT(`pn`) AS total
FROM (
SELECT
CASE
WHEN group0.group_id IS NOT NULL THEN CONCAT(group0.group_id, '(组)')
ELSE dimension0.`patent_type`
END AS field1,
CASE
WHEN group1.group_id IS NOT NULL THEN CONCAT(group1.group_id, '(组)')
ELSE dimension1.`country`
END AS field2,dimension1.`pn`
FROM
dw_ext.`t_1692603597355` t
INNER JOIN ads_tab11 dimension0 ON dimension0.pdoc_id = t.pdoc_id
LEFT JOIN biz.ads_tab22 group0 ON group0.group_value = dimension0.`patent_type` and group0.field = 'PATENT_TYPE' and group0.identifier_id = '0fb4b7e7fe0642618a1e7288faf7d8ae'
INNER JOIN ads_tab33 dimension1 ON dimension1.pdoc_id = t.pdoc_id
LEFT JOIN biz.ads_tab22 group1 ON group1.group_value = dimension1.`country` and group1.field = 'COUNTRY' and group1.identifier_id = '0fb4b7e7fe0642618a1e7288faf7d8ae'
WHERE 1=1
AND dimension0.`patent_type` in ('A','B','U','D') AND dimension0.`patent_type` IS NOT NULL
AND dimension1.`country` in ('US','CN') AND dimension1.`country` IS NOT NULL
AND dimension1.`pn` IS NOT NULL
AND dimension1.`country` IN ('CN')
GROUP BY t.pdoc_id, dimension1.`pn`,field1,field2
) T
GROUP BY field1,field2
ORDER BY total DESC,field1 ASC,field2 ASC
) t2 ON
t2.field1 = full_data.field1 AND
t2.field2 = full_data.field2
GROUP BY full_data.field1,full_data.field2
HAVING total > 0
ORDER BY total DESC,field1 ASC,field2 ASC
LIMIT 30000;
那倒是可以提个bug了，提bug有积分哒 