tidb-v5 ，加了索引，查询表数据时，就会报错

harveyor · 2021 年5 月 21 日 11:45

【TiDB 版本】
tidb v5.0.1

【问题描述】
(1)在tidb v5.0.1建立表：
DROP TABLE IF EXISTS ecp_task_plan_sub_2;
CREATE TABLE ecp_task_plan_sub_2 (
task_plan_sub_id varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
unit varchar(6) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
unitprice decimal(38, 2) NULL DEFAULT NULL COMMENT ‘单价’,
remark varchar(150) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
totalprice decimal(38, 2) NULL DEFAULT NULL COMMENT ‘总价’,
purchase_name varchar(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
purchase_id varchar(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
list_status varchar(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
purchase_model varchar(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
use_status char(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT ‘使用状态’,
create_user varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
create_time datetime(0) NULL DEFAULT NULL COMMENT ‘创建时间’,
modify_user varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
modify_time datetime(0) NULL DEFAULT NULL COMMENT ‘修改时间’,
budget_name varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
redundancy1 varchar(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
redundancy2 varchar(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
redundancy3 varchar(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
fin_goods_total decimal(38, 2) NULL DEFAULT NULL COMMENT ‘完成金额’,
fin_good_sqty decimal(38, 2) NULL DEFAULT NULL COMMENT ‘已完成数量’,
purchase_plan_id varchar(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
purchase_detail_id varchar(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
listno varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
task_plan_id varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
source_task_plan_sub_id varchar(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
shorttext varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
matnr varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
matnr_flief varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
matnr_type varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
ebuy_method varchar(4) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT ‘00’,
purchase_group varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
factory varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
storage_address varchar(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
purchase_org varchar(8) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
tracking_no varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
finishdate datetime(0) NULL DEFAULT NULL COMMENT ‘交货日期\ ’,
evaluation_price decimal(38, 2) NULL DEFAULT NULL COMMENT ‘评价价格’,
currency_type varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
base_unit varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
price_unit varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
account_type varchar(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
project_type varchar(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
tunck_address varchar(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
tec_remark varchar(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
gen_subject varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
funds_center varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
wbs varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
cost_center varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
budget_code varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
agreement varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
agreement_sup varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
fipos_project varchar(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
qa_grade varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
sup_matnr varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
is_emergencypur varchar(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT ‘00’,
frozen_text varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
is_frozen varchar(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT ‘00’,
document_type varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
document_type_code varchar(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
listname varchar(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
assign_status varchar(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
assign_user varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
assign_time timestamp(6) NULL DEFAULT NULL COMMENT ‘分发时间’,
second_assign_status varchar(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
second_assign_user varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
second_assign_time timestamp(6) NULL DEFAULT NULL COMMENT ‘分发时间’,
is_approve varchar(6) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT ‘01’,
info_record varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
mpn_material varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
total_value varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
is_create varchar(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT ‘01’,
change_date datetime(0) NULL DEFAULT NULL COMMENT ‘更改日期’,
mrp_controller varchar(6) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
order_quar decimal(38, 2) NULL DEFAULT NULL COMMENT ‘订货数量’,
outstanding_quar decimal(38, 2) NULL DEFAULT NULL COMMENT ‘未清数量’,
approve_date datetime(0) NULL DEFAULT NULL COMMENT ‘批准日期’,
plan_delivery_time datetime(0) NULL DEFAULT NULL COMMENT ‘计划交货时间’,
receive_time datetime(0) NULL DEFAULT NULL COMMENT ‘收货处理时间’,
delivery_time_type varchar(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
deal_status varchar(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
is_reserve varchar(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT ‘01’,
reason_code varchar(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
project_name varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
supply_factory varchar(8) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
expect_supplier varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
fixed_supplier varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
project_comment varchar(1000) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
transfer_text varchar(1000) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
matnr_purchase_text varchar(1000) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
purchase_method_reason varchar(2000) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
approve_opinion varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
tender_identify varchar(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT ‘00’,
plan_sub_matnr_id varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
is_allcollect varchar(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT ‘00’,
is_delete varchar(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT ‘00’,
order_org varchar(6) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
reason_dec varchar(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
sap_flag varchar(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
accept_status varchar(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
accept_user varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
accept_time timestamp(6) NULL DEFAULT NULL COMMENT ‘接收时间’,
zcgfs varchar(3) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
zprmo varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
zfebl varchar(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
zfygs varchar(60) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
loekz varchar(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
contract_id varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
contract_no varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
depart_id varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
depart_name varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
wbs_owner varchar(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
cost_center_name varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
report_url varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
overhaul_code varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
book_attr varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
agreement_listno varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
pur_type varchar(4) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
applymethod varchar(4) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT ‘00’,
audit_time timestamp(6) NULL DEFAULT NULL,
ukurs decimal(16, 5) NULL DEFAULT NULL,
ffact varchar(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
pur_model varchar(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
persion_id varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
ebuy_method_old varchar(4) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
person_id varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
agen_status varchar(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
has_agr varchar(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
is_other_back char(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT ‘是否等备件 00否 01是’,
is_change varchar(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT ‘00’,
recomm_sup varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
project_text varchar(2000) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
agr_type char(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT ‘是否备件类 00是 01否’,
quantity decimal(38, 3) NULL DEFAULT NULL,
apply_status varchar(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
track_confirm_date datetime(0) NULL DEFAULT NULL COMMENT ‘跟踪人最后确认时间’,
is_back_agr varchar(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
require_plan_id varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
relevance_gx varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
main_person varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
main_person_name varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
special_purchase varchar(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
first_accept_user varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
first_accept_time datetime(0) NULL DEFAULT NULL,
use_depart_id varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
use_depart_name varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
is_norm varchar(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT ‘0’,
sale_status varchar(4) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
sale_content varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
sale_price decimal(38, 2) NULL DEFAULT NULL COMMENT ‘销售单价（含税）’,
sale_quantity decimal(38, 3) NULL DEFAULT NULL COMMENT ‘销售数量’,
sale_total_price decimal(38, 2) NULL DEFAULT NULL COMMENT ‘销售总价’,
sale_currency varchar(6) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
sale_unit varchar(6) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
sale_duty varchar(4) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
sale_plan_date datetime(0) NULL DEFAULT NULL COMMENT ‘计划交货日期’,
id varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
zprtxt11 longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL,
zprtxt12 longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL,
send_status varchar(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
tps_modify_time datetime(0) NULL DEFAULT NULL,
is_build varchar(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT ‘0’ COMMENT ‘1:建档 0:未建档’,
PRIMARY KEY (task_plan_sub_id) USING BTREE,
INDEX idx_sub_pur_model(pur_model, task_plan_sub_id) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin COMMENT = ‘采购申报书明细’ ROW_FORMAT = Compact;

DROP TABLE IF EXISTS ecp_tend_m_task_p_2;
CREATE TABLE ecp_tend_m_task_p_2 (
tend_m_task_p_id varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
tenderid varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
sub_tenderid varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
quantity decimal(38, 2) NULL DEFAULT NULL COMMENT ‘数量’,
buy_main_body varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
task_plan_id varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
task_plan_sub_id varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
budget_money decimal(38, 2) NULL DEFAULT NULL COMMENT ‘拆分出的预算金额’,
is_effective char(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT ‘1’ COMMENT ‘是否有效[0:无效;1:有效;]’,
order_number varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
totalmoney decimal(38, 2) NULL DEFAULT NULL COMMENT ‘采购申请行对应的总金额’,
budgettotalmoney decimal(38, 2) NULL DEFAULT NULL COMMENT ‘采购申请行对应的总价值换算成的人民币数值’,
shorttext varchar(1000) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
PRIMARY KEY (tend_m_task_p_id) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin COMMENT = ‘招标项目与申报书条目中间表’ ROW_FORMAT = Compact;

(2)在tidb v5.0.1,执行下面的查询语句：
SELECT DISTINCT
P.PUR_MODEL
FROM
ECP_TASK_PLAN_SUB_2 P
INNER JOIN ECP_TEND_M_TASK_P_2 ETMTP ON P.TASK_PLAN_SUB_ID = ETMTP.TASK_PLAN_SUB_ID
AND ETMTP.TENDERID = ‘3c17d3a4f6a748cdab668a8766ec95eb’
UNION
SELECT DISTINCT
P.PUR_MODEL
FROM
ECP_TASK_PLAN_SUB_2 P
WHERE
P.TASK_PLAN_SUB_ID = ‘ceee0820d18c41d9a8f94faf3535bf8f’;

报错：
> 1105 - runtime error: index out of range [-1]
> 时间: 0.006s

（3）在tidb v5.0.1,注释掉第一个distinct执行查询，返回结果是正常的，不会报错：
SELECT – DISTINCT
P.PUR_MODEL
FROM
ECP_TASK_PLAN_SUB_2 P
INNER JOIN ECP_TEND_M_TASK_P_2 ETMTP ON P.TASK_PLAN_SUB_ID = ETMTP.TASK_PLAN_SUB_ID
AND ETMTP.TENDERID = ‘3c17d3a4f6a748cdab668a8766ec95eb’
UNION
SELECT DISTINCT
P.PUR_MODEL
FROM
ECP_TASK_PLAN_SUB_2 P
WHERE
P.TASK_PLAN_SUB_ID = ‘ceee0820d18c41d9a8f94faf3535bf8f’;

（4）在tidb v5.0.1,注释掉INNER JOIN部分执行查询，返回结果是正常的，不会报错：
SELECT DISTINCT
P.PUR_MODEL
FROM
ECP_TASK_PLAN_SUB_2 P
– INNER JOIN ECP_TEND_M_TASK_P_2 ETMTP ON P.TASK_PLAN_SUB_ID = ETMTP.TASK_PLAN_SUB_ID
– AND ETMTP.TENDERID = ‘3c17d3a4f6a748cdab668a8766ec95eb’
UNION
SELECT DISTINCT
P.PUR_MODEL
FROM
ECP_TASK_PLAN_SUB_2 P
WHERE
P.TASK_PLAN_SUB_ID = ‘ceee0820d18c41d9a8f94faf3535bf8f’;

（5）在tidb v4.0.12,执行以上（1）、（2）、（3）、（4），返回结果都是正常的，不会报错。

（6）在tidb v5.0.1，改下（1）中的建表语句，不加索引建表：
DROP TABLE IF EXISTS ecp_task_plan_sub_2;
CREATE TABLE ecp_task_plan_sub_2 (
task_plan_sub_id varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
unit varchar(6) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
unitprice decimal(38, 2) NULL DEFAULT NULL COMMENT ‘单价’,
remark varchar(150) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
totalprice decimal(38, 2) NULL DEFAULT NULL COMMENT ‘总价’,
purchase_name varchar(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
purchase_id varchar(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
list_status varchar(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
purchase_model varchar(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
use_status char(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT ‘使用状态’,
create_user varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
create_time datetime(0) NULL DEFAULT NULL COMMENT ‘创建时间’,
modify_user varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
modify_time datetime(0) NULL DEFAULT NULL COMMENT ‘修改时间’,
budget_name varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
redundancy1 varchar(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
redundancy2 varchar(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
redundancy3 varchar(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
fin_goods_total decimal(38, 2) NULL DEFAULT NULL COMMENT ‘完成金额’,
fin_good_sqty decimal(38, 2) NULL DEFAULT NULL COMMENT ‘已完成数量’,
purchase_plan_id varchar(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
purchase_detail_id varchar(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
listno varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
task_plan_id varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
source_task_plan_sub_id varchar(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
shorttext varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
matnr varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
matnr_flief varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
matnr_type varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
ebuy_method varchar(4) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT ‘00’,
purchase_group varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
factory varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
storage_address varchar(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
purchase_org varchar(8) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
tracking_no varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
finishdate datetime(0) NULL DEFAULT NULL COMMENT ‘交货日期\ ’,
evaluation_price decimal(38, 2) NULL DEFAULT NULL COMMENT ‘评价价格’,
currency_type varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
base_unit varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
price_unit varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
account_type varchar(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
project_type varchar(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
tunck_address varchar(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
tec_remark varchar(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
gen_subject varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
funds_center varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
wbs varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
cost_center varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
budget_code varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
agreement varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
agreement_sup varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
fipos_project varchar(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
qa_grade varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
sup_matnr varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
is_emergencypur varchar(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT ‘00’,
frozen_text varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
is_frozen varchar(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT ‘00’,
document_type varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
document_type_code varchar(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
listname varchar(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
assign_status varchar(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
assign_user varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
assign_time timestamp(6) NULL DEFAULT NULL COMMENT ‘分发时间’,
second_assign_status varchar(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
second_assign_user varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
second_assign_time timestamp(6) NULL DEFAULT NULL COMMENT ‘分发时间’,
is_approve varchar(6) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT ‘01’,
info_record varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
mpn_material varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
total_value varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
is_create varchar(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT ‘01’,
change_date datetime(0) NULL DEFAULT NULL COMMENT ‘更改日期’,
mrp_controller varchar(6) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
order_quar decimal(38, 2) NULL DEFAULT NULL COMMENT ‘订货数量’,
outstanding_quar decimal(38, 2) NULL DEFAULT NULL COMMENT ‘未清数量’,
approve_date datetime(0) NULL DEFAULT NULL COMMENT ‘批准日期’,
plan_delivery_time datetime(0) NULL DEFAULT NULL COMMENT ‘计划交货时间’,
receive_time datetime(0) NULL DEFAULT NULL COMMENT ‘收货处理时间’,
delivery_time_type varchar(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
deal_status varchar(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
is_reserve varchar(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT ‘01’,
reason_code varchar(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
project_name varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
supply_factory varchar(8) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
expect_supplier varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
fixed_supplier varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
project_comment varchar(1000) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
transfer_text varchar(1000) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
matnr_purchase_text varchar(1000) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
purchase_method_reason varchar(2000) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
approve_opinion varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
tender_identify varchar(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT ‘00’,
plan_sub_matnr_id varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
is_allcollect varchar(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT ‘00’,
is_delete varchar(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT ‘00’,
order_org varchar(6) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
reason_dec varchar(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
sap_flag varchar(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
accept_status varchar(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
accept_user varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
accept_time timestamp(6) NULL DEFAULT NULL COMMENT ‘接收时间’,
zcgfs varchar(3) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
zprmo varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
zfebl varchar(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
zfygs varchar(60) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
loekz varchar(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
contract_id varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
contract_no varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
depart_id varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
depart_name varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
wbs_owner varchar(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
cost_center_name varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
report_url varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
overhaul_code varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
book_attr varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
agreement_listno varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
pur_type varchar(4) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
applymethod varchar(4) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT ‘00’,
audit_time timestamp(6) NULL DEFAULT NULL,
ukurs decimal(16, 5) NULL DEFAULT NULL,
ffact varchar(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
pur_model varchar(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
persion_id varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
ebuy_method_old varchar(4) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
person_id varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
agen_status varchar(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
has_agr varchar(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
is_other_back char(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT ‘是否等备件 00否 01是’,
is_change varchar(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT ‘00’,
recomm_sup varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
project_text varchar(2000) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
agr_type char(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT ‘是否备件类 00是 01否’,
quantity decimal(38, 3) NULL DEFAULT NULL,
apply_status varchar(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
track_confirm_date datetime(0) NULL DEFAULT NULL COMMENT ‘跟踪人最后确认时间’,
is_back_agr varchar(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
require_plan_id varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
relevance_gx varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
main_person varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
main_person_name varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
special_purchase varchar(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
first_accept_user varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
first_accept_time datetime(0) NULL DEFAULT NULL,
use_depart_id varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
use_depart_name varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
is_norm varchar(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT ‘0’,
sale_status varchar(4) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
sale_content varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
sale_price decimal(38, 2) NULL DEFAULT NULL COMMENT ‘销售单价（含税）’,
sale_quantity decimal(38, 3) NULL DEFAULT NULL COMMENT ‘销售数量’,
sale_total_price decimal(38, 2) NULL DEFAULT NULL COMMENT ‘销售总价’,
sale_currency varchar(6) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
sale_unit varchar(6) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
sale_duty varchar(4) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
sale_plan_date datetime(0) NULL DEFAULT NULL COMMENT ‘计划交货日期’,
id varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
zprtxt11 longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL,
zprtxt12 longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL,
send_status varchar(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
tps_modify_time datetime(0) NULL DEFAULT NULL,
is_build varchar(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT ‘0’ COMMENT ‘1:建档 0:未建档’,
PRIMARY KEY (task_plan_sub_id) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin COMMENT = ‘采购申报书明细’ ROW_FORMAT = Compact;

DROP TABLE IF EXISTS ecp_tend_m_task_p_2;
CREATE TABLE ecp_tend_m_task_p_2 (
tend_m_task_p_id varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
tenderid varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
sub_tenderid varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
quantity decimal(38, 2) NULL DEFAULT NULL COMMENT ‘数量’,
buy_main_body varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
task_plan_id varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
task_plan_sub_id varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
budget_money decimal(38, 2) NULL DEFAULT NULL COMMENT ‘拆分出的预算金额’,
is_effective char(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT ‘1’ COMMENT ‘是否有效[0:无效;1:有效;]’,
order_number varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
totalmoney decimal(38, 2) NULL DEFAULT NULL COMMENT ‘采购申请行对应的总金额’,
budgettotalmoney decimal(38, 2) NULL DEFAULT NULL COMMENT ‘采购申请行对应的总价值换算成的人民币数值’,
shorttext varchar(1000) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
PRIMARY KEY (tend_m_task_p_id) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin COMMENT = ‘招标项目与申报书条目中间表’ ROW_FORMAT = Compact;

（7）在v5.0.1中，依据第（6）的重新建的表，再来执行与第（2）步同样的查询，执行正常，不会报错：
SELECT DISTINCT
P.PUR_MODEL
FROM
ECP_TASK_PLAN_SUB_2 P
INNER JOIN ECP_TEND_M_TASK_P_2 ETMTP ON P.TASK_PLAN_SUB_ID = ETMTP.TASK_PLAN_SUB_ID
AND ETMTP.TENDERID = ‘3c17d3a4f6a748cdab668a8766ec95eb’
UNION
SELECT DISTINCT
P.PUR_MODEL
FROM
ECP_TASK_PLAN_SUB_2 P
WHERE
P.TASK_PLAN_SUB_ID = ‘ceee0820d18c41d9a8f94faf3535bf8f’;

blacktear23 · 2021 年5 月 21 日 12:40

能否贴一下出问题的查询语句的执行计划？

GangShen · 2021 年5 月 21 日 13:59

提供一下执行报错时的 tidb.log ，看下堆栈日志

Cong-PingCAP · 2021 年5 月 21 日 14:30

您好！非常感谢提供了详细的复现步骤，以及相关说明。

已经可以确定这个问题的原因与 https://github.com/pingcap/tidb/issues/24045 一致，已经由 https://github.com/pingcap/tidb/pull/24204 在开发分支修复。

没有意外的话，会通过 https://github.com/pingcap/tidb/pull/24489 在下一个 5.0 的小版本发布。

Kongdom · 2021 年5 月 22 日 02:15

研发响应迅速

system · 2022 年10 月 31 日 19:12

此话题已在最后回复的 1 分钟后被自动关闭。不再允许新回复。