Different results for multiple queries with the same SQL

说明：

• 版本：V6.5.3
• 3个子查询left join,查询结果会不一致，有的时候会丢失一条
• 参数优化：

set session tidb_isolation_read_engines='tiflash';
set @@session.tidb_enforce_mpp=1;
set @@tidb_opt_agg_push_down = ON;
set @@tidb_opt_distinct_agg_push_down = 1;
set @@tidb_broadcast_join_threshold_size = 10000000;
set @@tidb_broadcast_join_threshold_count = 100000;

• 执行sql的时候是强制走tiflash,最后3个子查询是在tiflash节点进行hash join，这个时候可能会丢失数据
• 如果让tidb自动选择查询引擎，不会有这个问题
• 将sql中的3个字查询left join,改写成两个字查询left join，然后再做union，不会有这个问题
• 将sql中无效的条件和没有的字查询去掉后，不会有这个问题
  原始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;

Start by reproducing the provided SQL with the listed TiFlash session settings and compare repeated executions with automatic engine selection and the query rewrites described. There are no source files or tests named; done means repeated executions return the same complete rows without the reported loss.