Note that this question was originally posted on the Chinese TiDB Forum. We copied it here to help new users solve their problems quickly.
Application environment
Production environment
TiDB version
TiDB v6.1.0
Resource allocation
Create tables:
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;
Problem
An error was reported when I executed the following statement:
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
If selected items include sum(), and LEFT JOIN is followed by AND [aother table].[field], it will report an error. If it is an inner join, it will not report an error.
Error details: [8118] [HY000]: Failed to build executor
The SQL mode setting is
STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION