opened 12:48PM - 05 Aug 22 UTC
closed 02:43AM - 08 Sep 22 UTC
type/bug
sig/planner
severity/major
affects-6.1
affects-6.2
## Bug Report
Please answer these questions before submitting your issue. Tha…nks!
### 1. Minimal reproduce step (Required)
Step 1. Default TiUP Playground v6.1.0
Step 2. create tables and database
```sql
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;
```
Step 3
Executor SQL
```sql
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
```
### 2. What did you expect to see? (Required)
Select Query is executor success.
### 3. What did you see instead (Required)
Executor error log on the terminal client "Error [8118] [HY000]: Failed to build executor "
### 4. What is your TiDB version? (Required)
v6.1.0