SQL executor Error [8118] [HY000]: Failed to build executor

[superlzs0476]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

Step 1. Default TiUP Playground v6.1.0 Step 2. create tables and database

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

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

[jiangxiaom]

how to fit it?

[windtalker]

Seems it is fixed in master and release-6.2 branch, I can reproduce it with v6.1.0, but failed to reproduce it in master and v6.2.0

[windtalker]

Also it should be a planner bug instead of runtime bug.

[fixdb]

OK, we will take it. Can you kindly shed some light on how you found out it is planner bug? @windtalker

[aytrack]

the sql can execute after set tidb_enable_outer_join_reorder=OFF

MySQL root@127.0.0.1:test> 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
(8118, 'Failed to build executor')
MySQL root@127.0.0.1:test> set tidb_enable_outer_join_reorder=OFF;
Query OK, 0 rows affected
Time: 0.001s
MySQL root@127.0.0.1:test> 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
+--------+--------+--------+---------------+
| tid    | tid    | tid    | beforeYearAmt |
+--------+--------+--------+---------------+
| <null> | <null> | <null> | <null>        |
+--------+--------+--------+---------------+

[fixdb]

Can't reproduce this issue on master and v6.1.1. Closing this issue.

[aytrack]

The default value of tidb_enable_outer_join_reorder on v6.1.0 is true. From v6.1.1 and v6.2.0 the default is set to false.

[fixdb]

we already turned it on, still can't reproduce. The outer join bug has been fixed.