SQL语句TiDB执行报错 Wrong plan type for dataReaderBuilder

[DianFengYang]

1105 - Wrong plan type for dataReaderBuilder

[yilong (yi888long)]

请提供建表语句和 执行的sql，先复现下报错，多谢。

[DianFengYang]

SHOW CREATE TABLE m_user;

CREATE TABLE m_user ( sid int(11) NOT NULL AUTO_INCREMENT, user_code varchar(64) DEFAULT NULL, archives_no varchar(64) DEFAULT NULL, account varchar(64) DEFAULT NULL, pwd varchar(64) DEFAULT NULL, salt varchar(32) DEFAULT NULL, control_status varchar(11) DEFAULT NULL, wx_open_id varchar(64) DEFAULT NULL, create_user int(11) DEFAULT NULL, create_time timestamp NULL DEFAULT NULL, update_user int(11) DEFAULT NULL, update_time timestamp NULL DEFAULT NULL, device_id varchar(50) DEFAULT NULL, type varchar(10) DEFAULT NULL COMMENT ‘用户类型（0000）根据状态位来确定是否多平台’, status int(1) DEFAULT ‘0’, is_club tinyint(4) DEFAULT ‘-1’, is_first_login int(1) DEFAULT ‘1’, PRIMARY KEY (sid), KEY m_user_user_code_index (user_code), KEY idx_account_type (account,type), KEY idx_type (type), KEY idx_deviceid (device_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

SHOW CREATE TABLE m_user_info;

CREATE TABLE m_user_info ( sid int(11) NOT NULL AUTO_INCREMENT, user_sid int(11) DEFAULT NULL, legal_person varchar(32) DEFAULT NULL, name varchar(64) DEFAULT NULL, user_type_sid int(11) DEFAULT NULL , address varchar(128) DEFAULT NULL, warehouse_address varchar(140) DEFAULT NULL COMMENT ‘仓库地址’, contactor varchar(32) DEFAULT NULL COMMENT ‘联系人’, contactor_phone varchar(32) DEFAULT NULL COMMENT ‘联系人电话’, erp_comp_sid varchar(32) DEFAULT NULL COMMENT ‘单位ID’, erp_parent_comp_sid varchar(32) DEFAULT NULL COMMENT ‘单位父ID’, erp_comp_no varchar(32) DEFAULT NULL COMMENT ‘单位编号’, erp_king_sid int(11) DEFAULT NULL, archive_code varchar(255) DEFAULT NULL, province_sid int(11) DEFAULT NULL COMMENT ‘省’, city_sid int(11) DEFAULT NULL COMMENT ‘市’, area_sid int(11) DEFAULT NULL COMMENT ‘区’, level int(1) DEFAULT ‘0’ COMMENT ‘等级（预留）’, is_general int(11) DEFAULT NULL COMMENT ‘是否一般纳税人’, email varchar(32) DEFAULT NULL COMMENT ‘邮箱地址’, tax_number varchar(32) DEFAULT NULL, is_new int(11) DEFAULT NULL, create_time timestamp NULL DEFAULT NULL COMMENT ‘创建时间’, update_time timestamp NULL DEFAULT NULL COMMENT ‘修改时间’, create_user int(11) DEFAULT NULL, update_user int(11) DEFAULT NULL, zjm varchar(100) DEFAULT NULL, is_gsp int(1) DEFAULT ‘0’, is_bind_supply int(1) DEFAULT ‘0’, is_bind_price int(1) DEFAULT ‘0’ , is_main int(1) DEFAULT ‘2’ , remarks varchar(255) DEFAULT NULL, is_auto_check_order int(1) DEFAULT ‘0’ , PRIMARY KEY (sid), KEY index_user_sid (user_sid), KEY idx_areasid (area_sid), KEY rds_idx_0 (erp_parent_comp_sid), KEY is_bind_supply (is_bind_supply), KEY idx_city_sid (city_sid), KEY idx_usersid_isbindsupply (user_sid,is_bind_supply), KEY idx_erpcompsid (erp_comp_sid) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=107114 COMMENT=‘会员信息表’

SHOW CREATE TABLE m_supply_user_ref;

CREATE TABLE m_supply_user_ref ( sid int(11) NOT NULL AUTO_INCREMENT, supply_sid int(11) DEFAULT NULL, user_sid int(11) DEFAULT NULL, status int(1) DEFAULT ‘0’ COMMENT '状态 0可用 1停用 ', create_user varchar(45) DEFAULT NULL, create_time date DEFAULT NULL, update_user varchar(45) DEFAULT NULL, update_time date DEFAULT NULL, PRIMARY KEY (sid), KEY supply_sid (supply_sid) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

SQL语句如下：

SELECT a.sid,a.user_code, a.account,a.type, a.STATUS, b.erp_parent_comp_sid, b.LEVEL, b.address, b.contactor, b.contactor_phone, b.legal_person, b.is_main, b.user_type_sid, b.NAME AS business_name, b.email, b.archive_code AS archiveCode, b.zjm, b.is_general, b.is_gsp, b.province_sid, b.city_sid, b.area_sid, c.supply_sid, a.create_time FROM m_user a, m_user_info b, m_supply_user_ref c WHERE a.sid = b.user_sid AND b.is_bind_supply = 1 AND a.sid = c.user_sid AND ( a.type = 0 OR ( substr( a.type, 1, 1 ) = 1 )) AND c.supply_sid IN ( 1 ) UNION ALL SELECT a.sid, a.user_code, a.account, a.type, a.STATUS, b.erp_parent_comp_sid, b.LEVEL, b.address, b.contactor, b.contactor_phone, b.legal_person, b.is_main, b.user_type_sid, b.NAME AS business_name, b.email, b.archive_code AS archiveCode, b.zjm, b.is_general, b.is_gsp, b.province_sid, b.city_sid, b.area_sid, c.supply_sid, a.create_time FROM m_user a, m_user_info b, m_supply_area_ref c WHERE a.sid = b.user_sid AND b.is_bind_supply = 0 AND b.area_sid = c.area_sid AND supply_sid IN ( 1 ) AND ( a.type = 0 OR ( substr( a.type, 1, 1 ) = 1 )) ORDER BY sid DESC

[来了老弟]

你好，

1、 select 语句中。表 ‘m_supply_area_ref’ 请提供下表结构。

2、 tidb.log 麻烦提供下

[DianFengYang]

tidb.zip (773.2 KB) CREATE TABLE m_supply_user_ref ( sid int(11) NOT NULL AUTO_INCREMENT, supply_sid int(11) DEFAULT NULL, user_sid int(11) DEFAULT NULL, status int(1) DEFAULT ‘0’ COMMENT '状态 0可用 1停用 ', create_user varchar(45) DEFAULT NULL, create_time date DEFAULT NULL, update_user varchar(45) DEFAULT NULL, update_time date DEFAULT NULL, PRIMARY KEY ( sid ), KEY supply_sid ( supply_sid ) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

[wjhuang2016-PINGCAP]

@DianFengYang
您好， 麻烦用 explain select xxx 提供下执行计划。

[DianFengYang]

[DianFengYang]

一屏截不下。2屏一起

[wjhuang2016-PINGCAP]

@DianFengYang 麻烦提供下 TiDB 的版本

[YangDianFeng (Hacker Je Exuv1 H)]

TiDB 4.0 RC版本

[wjhuang2016-PINGCAP]

该 BUG 已经在 4.0.0 RC2 中修复，请问您是否方便升级到 4.0.0RC2 ？ 修复 pr：https://github.com/pingcap/tidb/pull/17028

[DianFengYang]

请问使用TiUP可以直接升级吗

[wjhuang2016-PINGCAP]

可以的