SQL 错误 [8118] [HY000]: Failed to build executor 。版本tidb-v6.1.0

🌞 建议反馈 产品缺陷
18

现在的现象是：

  1. 使用默认 sql_mode 设定
TiDB> select d.tid,smi.tid ,b.tid , SUM(CASE WHEN d.period < '2020-01' THEN d.pay ELSE 0 END) AS
beforeYearAmt FROM testdbs.m_info smi INNER JOIN testdbs.b2_info d ON smi.code = d.code1 AND d.type1 = 03 AND smi.code3 = 'S21011' LEFT JOIN testdbs.h_info a ON smi.code4 = a.code AND smi.type3 = 01 LEFT JOIN testdbs.b1_info b ON a.btestcode = b.code WHERE d.code2 = 'S21011' AND d.period <= '2021-12' LIMIT 10;
ERROR 8123 (HY000): In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'tid'; this is incompatible with sql_mode=only_full_group_by
  1. 修改 sql_mode 之后
TiDB> set sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

TiDB> select d.tid,smi.tid ,b.tid , SUM(CASE WHEN d.period < '2020-01' THEN d.pay ELSE 0 END) AS
beforeYearAmt FROM testdbs.m_info smi INNER JOIN testdbs.b2_info d ON smi.code = d.code1 AND d.type1 = 03 AND smi.code3 = 'S21011' LEFT JOIN testdbs.h_info a ON smi.code4 = a.code AND smi.type3 = 01 LEFT JOIN testdbs.b1_info b ON a.btestcode = b.code WHERE d.code2 = 'S21011' AND d.period <= '2021-12' LIMIT 10;
ERROR 8118 (HY000): Failed to build executor
  1. 修改 sql_mode ， 且去掉 AND smi.type3 = '01' 之后
TiDB> select d.tid,smi.tid ,b.tid , SUM(CASE WHEN d.period < '2020-01' THEN d.pay ELSE 0 END) AS
beforeYearAmt FROM testdbs.m_info smi INNER JOIN testdbs.b2_info d ON smi.code = d.code1 AND d.type1 = 03 AND smi.code3 = 'S21011' LEFT JOIN testdbs.h_info a ON smi.code4 = a.code  LEFT JOIN testdbs.b1_info b ON a.btestcode = b.code WHERE d.code2 = 'S21011' AND d.period <= '2021-12' LIMIT 10;
+------+------+------+---------------+
| tid  | tid  | tid  | beforeYearAmt |
+------+------+------+---------------+
| NULL | NULL | NULL |          NULL |
+------+------+------+---------------+
1 row in set (0.01 sec)