Different results for multiple queries with the same SQL

[lee1988well]

说明：

• 版本：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;

[windtalker]

Hi, could you please give some detail information on this?

• The explain result of both tidb_enforce_mpp is on and off
• The explain analyze result of two run that with different result when tidb_enforce_mpp is on
• The explain analyze result of the stable result with tidb_enforce_mpp on and no invalid filter condition.

[lee1988well]

执行计划的不同可以看一下：https://asktug.com/t/topic/1013225 我没有比较tidb_enforce_mpp这个参数打开与不打开的情况，我只比较了tidb_isolation_read_engines='tiflash';与tidb_isolation_read_engines='tiflash,tikv,tidb';的情况