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

🌞 建议反馈 产品缺陷
1

【 TiDB 使用环境`】生产环境
【 TiDB 版本】v6.1.0
【遇到的问题】tidb提示：SQL 错误 [8118] [HY000]: Failed to build executor
【复现路径】1) select 中含有sum 2) left join B on A.id=B.aid and A.num=2 ，查询SQL 报以上错误
【问题现象及影响】tidb提示：SQL 错误 [8118] [HY000]: Failed to build executor

2

感觉像个bug:https://github.com/pingcap/tidb/issues/27501

3

具体是执行了什么语句？grant xxxx？

4

就grant select on xxx.* to user@’%’

5

这是在dbeaver里执行的？引用的哪个mysql connector版本？

6

我在命令行执行也是报这个错
~]# mysql -V
mysql Ver 8.0.20-11 for Linux on x86_64 (Percona Server (GPL), Release 11, Revision 159f0eb)

7

背景信息还是太少，麻烦提供下最小场景复现语句？

8

image
image1093×278 20.7 KB

10

bug很清楚了：left join 后边加了’and 别的表.字段’ ，select 中有sum()，会报：[8118] [HY000]: Failed to build executor。

如果是 inner join 则不会报错

11

看到报错一样，应该是上面的这个 BUG ~居然修复进度可见 github 链接

12

我不知道是不是同一处，但是场景不一样。v5.4.0版本没有报错

13

按你的说法构造了一个简单的案例，还是不能复现。如果可能的话还是最好能提供建表语句和尽量短的sql

14

CREATE DATABASE testdbs DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

CREATE TABLE testdbs.h_info (
tid bigint(20) NOT NULL AUTO_INCREMENT,
code varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL ,
btestcode varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL ,
PRIMARY KEY (tid) /*T![clustered_index] CLUSTERED */,
UNIQUE KEY code_UNIQUE (code)
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ;

CREATE TABLE testdbs.b1_info (
tid bigint(20) NOT NULL AUTO_INCREMENT,
code varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL ,
PRIMARY KEY (tid) /*T![clustered_index] CLUSTERED */,
UNIQUE KEY code_UNIQUE (code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ;

CREATE TABLE testdbs.b2_info (
tid bigint(20) NOT NULL AUTO_INCREMENT,
code varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL ,
period varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL ,
pay decimal(11,2) NOT NULL DEFAULT ‘0.00’ ,
code1 varchar(45) COLLATE utf8mb4_unicode_ci NOT NULL ,
type1 char(2) COLLATE utf8mb4_unicode_ci NOT NULL ,
code2 varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL ,
PRIMARY KEY (tid) /*T![clustered_index] CLUSTERED */,
UNIQUE KEY code_UNIQUE (code),
KEY ix_period (period,code1),
KEY ix_code12 (code1,code2),
KEY ix_code1 (code1),
KEY ix_code2 (code2)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT=’’;

CREATE TABLE testdbs.m_info (
tid bigint(20) NOT NULL AUTO_INCREMENT COMMENT ‘主键’,
code varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL ,
code3 varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL ,
type3 char(2) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ‘00’,
code4 varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL ,
PRIMARY KEY (tid) /*T![clustered_index] CLUSTERED */,
UNIQUE KEY code_UNIQUE (code),
KEY code4 (code4,type3),
KEY idx_type3 (type3,code,code4),
KEY code3 (code3)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

执行以下语句报错

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

15

我已经把问题复现步骤贴到楼上的回复了，麻烦请看下

16

查下当前 sql mode 设定是什么样的?

17

STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

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)
19

步骤2 设置的sql_mode，不是跟我的一样吗？

步骤3 为啥要去掉 AND smi.type3 = '01'？

20

如果觉得SQL不标准，可以这样执行：
select d.code2,a.code,b.code,
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’
group by d.code2,a.code,b.code
LIMIT 10

21

大概看了下，能找到是因为master更改了 PhysicalPlan 里头 basePhysicalJoin 里的一些计算方式，我这几天再瞄瞄看。