更改了1秒的时间范围，查询结果是空的

🪐 TiDB 技术问题 性能调优
1

为提高效率，提问时请尽量提供详细背景信息，问题描述清晰可优先响应。以下信息点请尽量提供：

  • 【系统版本 & kernel 版本】
  • 【TiDB 版本】
  • 【磁盘型号】
  • 【集群节点分布】
  • 【数据量 & region 数量 & 副本数】
  • 【问题描述（我做了什么）】
  • 【关键词】更改了1秒的时间范围，无法查询出结果

sql： select ec.code as lots_code,
ec.name as lots_name,
sc.code as scenic_code,
sc.name as scenic_name,
od.pay_method as pay_method,
od.quantity as quantity,
od.already_check_num as already_check_num, od.return_num as return_num, od.order_type as order_type, od.price as price, od.close_price as close_price,
od.total_price as total_price,
od.close_total_price as close_total_price,
od.return_fee_category as return_fee_category, od.return_fee_rate as return_fee_rate, od.return_fee as return_fee,
od.corp_code as corp_code,
od.sale_corp_code as sale_corp_code, od.goods_code as goods_code, od.goods_name as goods_name, od.pay_status as pay_status, od.create_time as create_time, od.create_by as create_by, od.occ_date as occ_date, od.check_time as check_time, od.status as status, od.is_vm as is_vm, od.deleted as deleted, oi.check_status as check_status, oi.src as src, oi.third_code as third_code, oi.order_code as order_code, oi.link_name as link_name, oi.tel as tel, oi.certificate_no as certificate_no,
p.provice as province,
p.city2 as city, fx.name as corp_name, gy.name as sale_corp_name, fx.label_id as corp_label_id, gy.label_id as sale_corp_label_id, fx.corp_group_code as corp_group_code, gy.corp_group_code as sale_corp_group_code, fx.sys_area_code as corp_area_code, gy.sys_area_code as sale_corp_area_code, od.modify_time as modify_time from zhiyoubao2_mq_dw.order_detail od left join zhiyoubao2_mq_dw.order_info oi on od.order_info_id = oi.id inner join zhiyoubao2_mq_dw.corp fx on od.corp_code = fx.code inner join zhiyoubao2_mq_dw.corp gy on od.sale_corp_code = gy.code inner JOIN zhiyoubao_zhiyoubao.ecological_company_corp ecc ON ecc.corp_id = gy.id inner JOIN zhiyoubao_zhiyoubao.ecological_company ec ON ecc.ecological_company_id = ec.id left JOIN zybpd_zyb_product.scenic_ticket_type sttm ON od.goods_code = sttm.code left JOIN zhiyoubao2_mq_dw.scenic sc ON sttm.scenic_id = sc.id left join zhiyoubao_zhiyoubao.phone_area_new p on substr(oi.tel, 1, 7) = p.code WHERE od.create_time >= ‘2019-10-31 15:50:33’ and od.create_time <= ‘2019-10-31 16:05:00’ and ec.code = ‘guizhgszhlyyxgs’

执行计划：

id count task operator info
Projection_25 246.71 root zhiyoubao_zhiyoubao.ec.code, zhiyoubao_zhiyoubao.ec.name, zhiyoubao2_mq_dw.sc.code, zhiyoubao2_mq_dw.sc.name, zhiyoubao2_mq_dw.od.pay_method, zhiyoubao2_mq_dw.od.quantity, zhiyoubao2_mq_dw.od.already_check_num, zhiyoubao2_mq_dw.od.return_num, zhiyoubao2_m
└─IndexMergeJoin_34 246.71 root left outer join, inner:IndexLookUp_32, outer key:substr(zhiyoubao2_mq_dw.oi.tel, 1, 7), inner key:zhiyoubao_zhiyoubao.p.code
├─Projection_36 241.40 root zhiyoubao2_mq_dw.od.order_info_id, zhiyoubao2_mq_dw.od.order_type, zhiyoubao2_mq_dw.od.sale_corp_code, zhiyoubao2_mq_dw.od.already_check_num, zhiyoubao2_mq_dw.od.check_time, zhiyoubao2_mq_dw.od.goods_name, zhiyoubao2_mq_dw.od.total_price, zhiyoubao2_mq_dw
│ └─IndexMergeJoin_45 241.40 root left outer join, inner:IndexLookUp_43, outer key:zybpd_zyb_product.sttm.scenic_id, inner key:zhiyoubao2_mq_dw.sc.id
│ ├─HashLeftJoin_47 238.74 root left outer join, inner:TableReader_165, equal:[eq(zhiyoubao2_mq_dw.od.goods_code, zybpd_zyb_product.sttm.code)]
│ │ ├─IndexMergeJoin_59 237.01 root inner join, inner:IndexLookUp_57, outer key:zhiyoubao2_mq_dw.od.corp_code, inner key:zhiyoubao2_mq_dw.fx.code
│ │ │ ├─HashRightJoin_65 234.20 root inner join, inner:IndexMergeJoin_84, equal:[eq(zhiyoubao2_mq_dw.gy.code, zhiyoubao2_mq_dw.od.sale_corp_code)]
│ │ │ │ ├─IndexMergeJoin_84 0.09 root inner join, inner:IndexLookUp_82, outer key:zhiyoubao_zhiyoubao.ecc.corp_id, inner key:zhiyoubao2_mq_dw.gy.id
│ │ │ │ │ ├─HashRightJoin_99 0.08 root inner join, inner:IndexLookUp_108, equal:[eq(zhiyoubao_zhiyoubao.ec.id, zhiyoubao_zhiyoubao.ecc.ecological_company_id)]
│ │ │ │ │ │ ├─IndexLookUp_108 0.07 root
│ │ │ │ │ │ │ ├─IndexScan_106 0.07 cop table:ec, index:CODE, range:[“guizhgszhlyyxgs”,“guizhgszhlyyxgs”], keep order:false, stats:pseudo
│ │ │ │ │ │ │ └─TableScan_107 0.07 cop table:ec, keep order:false, stats:pseudo
│ │ │ │ │ │ └─TableReader_102 70.86 root data:Selection_101
│ │ │ │ │ │ └─Selection_101 70.86 cop not(isnull(zhiyoubao_zhiyoubao.ecc.corp_id)), not(isnull(zhiyoubao_zhiyoubao.ecc.ecological_company_id))
│ │ │ │ │ │ └─TableScan_100 71.00 cop table:ecc, range:[-inf,+inf], keep order:false, stats:pseudo
│ │ │ │ │ └─IndexLookUp_82 1.00 root
│ │ │ │ │ ├─IndexScan_79 1.00 cop table:gy, index:ID, range: decided by [eq(zhiyoubao2_mq_dw.gy.id, zhiyoubao_zhiyoubao.ecc.corp_id)], keep order:true
│ │ │ │ │ └─Selection_81 1.00 cop not(isnull(zhiyoubao2_mq_dw.gy.code))
│ │ │ │ │ └─TableScan_80 1.00 cop table:gy, keep order:false
│ │ │ │ └─IndexMergeJoin_124 2707.04 root left outer join, inner:IndexLookUp_122, outer key:zhiyoubao2_mq_dw.od.order_info_id, inner key:zhiyoubao2_mq_dw.oi.id
│ │ │ │ ├─IndexLookUp_141 2707.04 root
│ │ │ │ │ ├─IndexScan_138 2707.04 cop table:od, index:CREATE_TIME, range:[2019-10-31 15:50:33,2019-10-31 16:05:00], keep order:false
│ │ │ │ │ └─Selection_140 2707.04 cop not(isnull(zhiyoubao2_mq_dw.od.corp_code)), not(isnull(zhiyoubao2_mq_dw.od.sale_corp_code))
│ │ │ │ │ └─TableScan_139 2707.04 cop table:od, keep order:false
│ │ │ │ └─IndexLookUp_122 0.96 root
│ │ │ │ ├─IndexScan_120 0.96 cop table:oi, index:ID, range: decided by [eq(zhiyoubao2_mq_dw.oi.id, zhiyoubao2_mq_dw.od.order_info_id)], keep order:true
│ │ │ │ └─TableScan_121 0.96 cop table:oi, keep order:false
│ │ │ └─IndexLookUp_57 1.00 root
│ │ │ ├─Selection_56 1.00 cop not(isnull(zhiyoubao2_mq_dw.fx.code))
│ │ │ │ └─IndexScan_54 1.00 cop table:fx, index:CODE, range: decided by [eq(zhiyoubao2_mq_dw.fx.code, zhiyoubao2_mq_dw.od.corp_code)], keep order:true
│ │ │ └─TableScan_55 1.00 cop table:fx, keep order:false
│ │ └─TableReader_165 188782.00 root data:Selection_164
│ │ └─Selection_164 188782.00 cop not(isnull(zybpd_zyb_product.sttm.code))
│ │ └─TableScan_163 188782.00 cop table:sttm, range:[-inf,+inf], keep order:false
│ └─IndexLookUp_43 1.00 root
│ ├─IndexScan_41 1.00 cop table:sc, index:ID, range: decided by [eq(zhiyoubao2_mq_dw.sc.id, zybpd_zyb_product.sttm.scenic_id)], keep order:true
│ └─TableScan_42 1.00 cop table:sc, keep order:false
└─IndexLookUp_32 1.00 root 
├─IndexScan_30                 |1.00     |cop |table:p, index:CODE, range: decided by [eq(zhiyoubao_zhiyoubao.p.code, substr(zhiyoubao2_mq_dw.oi.tel, 1, 7))], keep order:true                                                                                                                                |
└─TableScan_31                 |1.00     |cop |table:p, keep order:false                                                                                                                                                                                                                                      |

这样是可以出结果的，然而把时间范围的开始时间-1秒，就没有数据了，减1秒后的执行计划：

id count task operator info
Projection_25 246.93 root zhiyoubao_zhiyoubao.ec.code, zhiyoubao_zhiyoubao.ec.name, zhiyoubao2_mq_dw.sc.code, zhiyoubao2_mq_dw.sc.name, zhiyoubao2_mq_dw.od.pay_method, zhiyoubao2_mq_dw.od.quantity, zhiyoubao2_mq_dw.od.already_check_num, zhiyoubao2_mq_dw.od.return_num, zhiyoubao2_m
└─IndexMergeJoin_34 246.93 root left outer join, inner:IndexLookUp_32, outer key:substr(zhiyoubao2_mq_dw.oi.tel, 1, 7), inner key:zhiyoubao_zhiyoubao.p.code
├─Projection_36 241.62 root zhiyoubao2_mq_dw.od.order_info_id, zhiyoubao2_mq_dw.od.order_type, zhiyoubao2_mq_dw.od.sale_corp_code, zhiyoubao2_mq_dw.od.already_check_num, zhiyoubao2_mq_dw.od.check_time, zhiyoubao2_mq_dw.od.goods_name, zhiyoubao2_mq_dw.od.total_price, zhiyoubao2_mq_dw
│ └─IndexMergeJoin_45 241.62 root left outer join, inner:IndexLookUp_43, outer key:zybpd_zyb_product.sttm.scenic_id, inner key:zhiyoubao2_mq_dw.sc.id
│ ├─HashLeftJoin_47 238.95 root left outer join, inner:TableReader_165, equal:[eq(zhiyoubao2_mq_dw.od.goods_code, zybpd_zyb_product.sttm.code)]
│ │ ├─IndexMergeJoin_59 237.22 root inner join, inner:IndexLookUp_57, outer key:zhiyoubao2_mq_dw.od.corp_code, inner key:zhiyoubao2_mq_dw.fx.code
│ │ │ ├─HashRightJoin_65 234.41 root inner join, inner:IndexMergeJoin_84, equal:[eq(zhiyoubao2_mq_dw.gy.code, zhiyoubao2_mq_dw.od.sale_corp_code)]
│ │ │ │ ├─IndexMergeJoin_84 0.09 root inner join, inner:IndexLookUp_82, outer key:zhiyoubao_zhiyoubao.ecc.corp_id, inner key:zhiyoubao2_mq_dw.gy.id
│ │ │ │ │ ├─HashRightJoin_99 0.08 root inner join, inner:IndexLookUp_108, equal:[eq(zhiyoubao_zhiyoubao.ec.id, zhiyoubao_zhiyoubao.ecc.ecological_company_id)]
│ │ │ │ │ │ ├─IndexLookUp_108 0.07 root
│ │ │ │ │ │ │ ├─IndexScan_106 0.07 cop table:ec, index:CODE, range:[“guizhgszhlyyxgs”,“guizhgszhlyyxgs”], keep order:false, stats:pseudo
│ │ │ │ │ │ │ └─TableScan_107 0.07 cop table:ec, keep order:false, stats:pseudo
│ │ │ │ │ │ └─TableReader_102 70.86 root data:Selection_101
│ │ │ │ │ │ └─Selection_101 70.86 cop not(isnull(zhiyoubao_zhiyoubao.ecc.corp_id)), not(isnull(zhiyoubao_zhiyoubao.ecc.ecological_company_id))
│ │ │ │ │ │ └─TableScan_100 71.00 cop table:ecc, range:[-inf,+inf], keep order:false, stats:pseudo
│ │ │ │ │ └─IndexLookUp_82 1.00 root
│ │ │ │ │ ├─IndexScan_79 1.00 cop table:gy, index:ID, range: decided by [eq(zhiyoubao2_mq_dw.gy.id, zhiyoubao_zhiyoubao.ecc.corp_id)], keep order:true
│ │ │ │ │ └─Selection_81 1.00 cop not(isnull(zhiyoubao2_mq_dw.gy.code))
│ │ │ │ │ └─TableScan_80 1.00 cop table:gy, keep order:false
│ │ │ │ └─IndexMergeJoin_124 2709.45 root left outer join, inner:IndexLookUp_122, outer key:zhiyoubao2_mq_dw.od.order_info_id, inner key:zhiyoubao2_mq_dw.oi.id
│ │ │ │ ├─IndexLookUp_141 2709.45 root
│ │ │ │ │ ├─IndexScan_138 3386.82 cop table:od, index:CREATE_TIME, range:[2019-10-31 15:50:32,2019-10-31 16:05:00], keep order:false
│ │ │ │ │ └─Selection_140 2709.45 cop not(isnull(zhiyoubao2_mq_dw.od.corp_code)), not(isnull(zhiyoubao2_mq_dw.od.sale_corp_code))
│ │ │ │ │ └─TableScan_139 3386.82 cop table:od, keep order:false
│ │ │ │ └─IndexLookUp_122 0.96 root
│ │ │ │ ├─IndexScan_120 0.96 cop table:oi, index:ID, range: decided by [eq(zhiyoubao2_mq_dw.oi.id, zhiyoubao2_mq_dw.od.order_info_id)], keep order:true
│ │ │ │ └─TableScan_121 0.96 cop table:oi, keep order:false
│ │ │ └─IndexLookUp_57 1.00 root
│ │ │ ├─Selection_56 1.00 cop not(isnull(zhiyoubao2_mq_dw.fx.code))
│ │ │ │ └─IndexScan_54 1.00 cop table:fx, index:CODE, range: decided by [eq(zhiyoubao2_mq_dw.fx.code, zhiyoubao2_mq_dw.od.corp_code)], keep order:true
│ │ │ └─TableScan_55 1.00 cop table:fx, keep order:false
│ │ └─TableReader_165 188782.00 root data:Selection_164
│ │ └─Selection_164 188782.00 cop not(isnull(zybpd_zyb_product.sttm.code))
│ │ └─TableScan_163 188782.00 cop table:sttm, range:[-inf,+inf], keep order:false
│ └─IndexLookUp_43 1.00 root
│ ├─IndexScan_41 1.00 cop table:sc, index:ID, range: decided by [eq(zhiyoubao2_mq_dw.sc.id, zybpd_zyb_product.sttm.scenic_id)], keep order:true
│ └─TableScan_42 1.00 cop table:sc, keep order:false
└─IndexLookUp_32 1.00 root 
├─IndexScan_30                 |1.00     |cop |table:p, index:CODE, range: decided by [eq(zhiyoubao_zhiyoubao.p.code, substr(zhiyoubao2_mq_dw.oi.tel, 1, 7))], keep order:true                                                                                                                                |
└─TableScan_31                 |1.00     |cop |table:p, keep order:false                                                                                                                                                                                                                                      |

两个执行计划除了count以外其他的是一模一样的

*查询出的结果用count(1)能看出是11条和12条，但是12条那个只要是查询具体字段，就没有任何值，好像是和join的方式有关，加上/*+ TIDB_HJ(oi,p) /使用hashjoin就都能出结果，但是sql会变得慢很多，有什么办法解决么0.0

2

麻烦通过附件的方式上传下 explain analyze 的两个结果 ，保持原来返回的格式。另外麻烦给下 查询的 SQL 相关的表的结构。我们这边再详细分析下。

4

添加hashjoin后的执行计划.txt (15.8 KB)

_explain_select_p_provice_as_province_p_city2_as_city_from_zhiyo_201911041535.txt (16.3 KB)

_explain_select_p_provice_as_province_p_city2_as_city_from_zhiyo_201911041523.txt (16.3 KB)
这是三个执行计划，第二三个修改时间范围1秒，使用count(1)可以看到数据量从11变成12，但是11条数据的时候可以显示，12条数据的时候无法显示。

排查发现主要问题出在zhiyoubao_zhiyoubao.phone_area_new表这里，然后在select后面加上/*+ TIDB_HJ( p) */，使用hashjoin的方式就解决这个问题了，但是问题解决了，具体的原因还是不知道是什么

第一个sql，没有结果

image
image1099×609 19.3 KB

第二个sql，修改了时间范围，有结果
image
image1081×609 22.9 KB

第三个sql，添加了/+ TIDB_HJ( p) /，时间范围无论怎么选都有结果
image
image1100×633 24.2 KB

5

方便提供下着几个表的结构以及索引信息吗？

6 
会影响最终结果的就3个表，sql可以被简化成如下，不加select后面的参数就没有执行结果：

select
-- /*+ TIDB_HJ( p)
	p.provice              as province,          
	p.city2                 as city
from zhiyoubao2_mq_dw.order_detail od
		 left join zhiyoubao2_mq_dw.order_info oi on od.order_info_id = oi.id
		 left join zhiyoubao_zhiyoubao.phone_area_new p on substr(oi.tel, 1, 7) = p.code
WHERE
 od.create_time >= '2019-10-31 15:50:32'
 and 
 od.create_time <= '2019-10-31 16:05:00';

不加参数执行计划：

Projection_9 2749.06 root zhiyoubao_zhiyoubao.p.provice, zhiyoubao_zhiyoubao.p.city2
└─IndexMergeJoin_18 2749.06 root left outer join, inner:IndexLookUp_16, outer key:substr(zhiyoubao2_mq_dw.oi.tel, 1, 7), inner key:zhiyoubao_zhiyoubao.p.code
├─Projection_20 2689.91 root zhiyoubao2_mq_dw.od.order_info_id, zhiyoubao2_mq_dw.od.create_time, zhiyoubao2_mq_dw.oi.id, zhiyoubao2_mq_dw.oi.tel, substr(zhiyoubao2_mq_dw.oi.tel, 1, 7)
│ └─IndexMergeJoin_30 2689.91 root left outer join, inner:IndexLookUp_28, outer key:zhiyoubao2_mq_dw.od.order_info_id, inner key:zhiyoubao2_mq_dw.oi.id
│ ├─IndexLookUp_46 2689.91 root
│ │ ├─IndexScan_44 2689.91 cop table:od, index:CREATE_TIME, range:[2019-10-31 15:50:32,2019-10-31 16:05:00], keep order:false
│ │ └─TableScan_45 2689.91 cop table:od, keep order:false
│ └─IndexLookUp_28 0.26 root
│ ├─IndexScan_26 0.26 cop table:oi, index:ID, range: decided by [eq(zhiyoubao2_mq_dw.oi.id, zhiyoubao2_mq_dw.od.order_info_id)], keep order:true
│ └─TableScan_27 0.26 cop table:oi, keep order:false
└─IndexLookUp_16 1.00 root
├─IndexScan_14 1.00 cop table:p, index:CODE, range: decided by [eq(zhiyoubao_zhiyoubao.p.code, substr(zhiyoubao2_mq_dw.oi.tel, 1, 7))], keep order:true
└─TableScan_15 1.00 cop table:p, keep order:false

加参数执行计划：

Projection_9 2749.06 root zhiyoubao_zhiyoubao.p.provice, zhiyoubao_zhiyoubao.p.city2
└─HashLeftJoin_19 2749.06 root left outer join, inner:TableReader_50, equal:[eq(substr(zhiyoubao2_mq_dw.oi.tel, 1, 7), zhiyoubao_zhiyoubao.p.code)]
├─Projection_20 2689.91 root zhiyoubao2_mq_dw.od.order_info_id, zhiyoubao2_mq_dw.od.create_time, zhiyoubao2_mq_dw.oi.id, zhiyoubao2_mq_dw.oi.tel, substr(zhiyoubao2_mq_dw.oi.tel, 1, 7)
│ └─IndexMergeJoin_30 2689.91 root left outer join, inner:IndexLookUp_28, outer key:zhiyoubao2_mq_dw.od.order_info_id, inner key:zhiyoubao2_mq_dw.oi.id
│ ├─IndexLookUp_46 2689.91 root
│ │ ├─IndexScan_44 2689.91 cop table:od, index:CREATE_TIME, range:[2019-10-31 15:50:32,2019-10-31 16:05:00], keep order:false
│ │ └─TableScan_45 2689.91 cop table:od, keep order:false
│ └─IndexLookUp_28 0.26 root
│ ├─IndexScan_26 0.26 cop table:oi, index:ID, range: decided by [eq(zhiyoubao2_mq_dw.oi.id, zhiyoubao2_mq_dw.od.order_info_id)], keep order:true
│ └─TableScan_27 0.26 cop table:oi, keep order:false
└─TableReader_50 262938.00 root data:TableScan_49
└─TableScan_49 262938.00 cop table:p, range:[-inf,+inf], keep order:false 
order_detail表：CREATE TABLE `order_detail` (
  `ID` decimal(19,0) NOT NULL,
  `ORDER_INFO_ID` decimal(19,0) DEFAULT NULL,
  `ORDER_TYPE` varchar(32) DEFAULT NULL,
  `SUB_ORDER_INFO_ID` decimal(19,0) DEFAULT NULL,
  `PARENT_ID` decimal(19,0) DEFAULT NULL,
  `SALE_CORP_CODE` varchar(32) DEFAULT NULL,
  `FEE` decimal(10,2) DEFAULT NULL,
  `ALREADY_CHECK_NUM` decimal(22,0) DEFAULT NULL,
  `NEED_CHECK_NUM` decimal(22,0) DEFAULT NULL,
  `START_DATE` datetime DEFAULT NULL,
  `END_DATE` datetime DEFAULT NULL,
  `CHECK_TIME` datetime DEFAULT NULL,
  `GOODS_NAME` varchar(128) DEFAULT NULL,
  `BUILD_NAME` varchar(32) DEFAULT NULL,
  `ADULT` decimal(22,0) DEFAULT NULL,
  `CHILD` decimal(22,0) DEFAULT NULL,
  `TOTAL_PRICE` decimal(10,2) DEFAULT NULL,
  `QUANTITY` decimal(22,0) DEFAULT NULL,
  `PRICE` decimal(10,2) DEFAULT NULL,
  `GOODS_CODE` varchar(32) DEFAULT NULL,
  `IS_VM` varchar(32) DEFAULT NULL,
  `CORP_GROUP_CODE` varchar(32) DEFAULT NULL,
  `CORP_CODE` varchar(32) DEFAULT NULL,
  `THIRD_CODE` varchar(32) DEFAULT NULL,
  `PAY_STATUS` varchar(32) DEFAULT NULL,
  `CHECK_STATUS` varchar(32) DEFAULT NULL,
  `PAY_METHOD` varchar(32) DEFAULT NULL,
  `STATUS` varchar(32) DEFAULT NULL,
  `CREATE_BY` varchar(32) DEFAULT NULL,
  `CREATE_TIME` datetime DEFAULT NULL,
  `MODIFY_BY` varchar(32) DEFAULT NULL,
  `MODIFY_TIME` datetime DEFAULT NULL,
  `CHECK_NUM` decimal(11,0) DEFAULT NULL,
  `ONLINE_CHECK_NUM` decimal(11,0) DEFAULT NULL,
  `VERSION` decimal(16,0) DEFAULT NULL,
  `OCC_DATE` datetime DEFAULT NULL,
  `RETURN_NUM` decimal(11,0) DEFAULT NULL,
  `RETURN_FEE` decimal(10,2) DEFAULT NULL,
  `RETURN_FEE_CATEGORY` varchar(32) DEFAULT NULL,
  `RETURN_FEE_RATE` decimal(8,2) DEFAULT NULL,
  `IS_RETREAT` varchar(1) DEFAULT NULL,
  `IS_EXPIRED_RETREAT` varchar(1) DEFAULT NULL,
  `CLOSE_PRICE` decimal(10,2) DEFAULT NULL,
  `CLOSE_TOTAL_PRICE` decimal(10,2) DEFAULT NULL,
  `IS_RETREAT_AUDIT` varchar(1) DEFAULT NULL,
  `WEEK_DAY` varchar(32) DEFAULT NULL,
  `DELETED` varchar(1) DEFAULT NULL,
  `SALE_CODE` varchar(32) DEFAULT NULL,
  `SYNC_STATUS` varchar(32) DEFAULT NULL,
  `LATER_OCC_DATE` decimal(11,0) DEFAULT NULL,
  `BEFORE_OCC_DATE` decimal(11,0) DEFAULT NULL,
  `REMARK` varchar(1024) DEFAULT NULL,
  `CLOSE_STATUS` varchar(32) DEFAULT NULL,
  `CLOSE_DAYS` decimal(11,0) DEFAULT NULL,
  `SYNC_CLOUD_STATUS` varchar(32) DEFAULT NULL,
  `AUDIT_FLAG` varchar(1) DEFAULT NULL,
  `REMARK_DIST` varchar(2000) DEFAULT NULL,
  `TRADE_NO` varchar(32) DEFAULT NULL,
  `IS_CERT_AUTH` varchar(32) DEFAULT NULL,
  `CHECK_TIME_PEROID` varchar(128) DEFAULT NULL,
  `CHECK_TIME_FORBID` varchar(256) DEFAULT NULL,
  `MULT_PROXY_LEVEL` decimal(11,0) DEFAULT NULL,
  `PROXY_PARENT_ID` decimal(19,0) DEFAULT NULL,
  `PROXY_TOP_FLAG` varchar(1) DEFAULT NULL,
  `SHIP_PLAY_TIME` varchar(10) DEFAULT NULL,
  `GIFT_START_NUM` decimal(22,0) DEFAULT NULL,
  `GIFT_TICKET_NUM` decimal(22,0) DEFAULT NULL,
  `IS_GIFT_TICKET` varchar(1) DEFAULT NULL,
  `FROM_GOODS_CODE` varchar(32) DEFAULT NULL,
  `GROUP_START_NUM` decimal(22,0) DEFAULT NULL,
  `RETREAT_TIME_PEROID` varchar(256) DEFAULT NULL,
  `IF_FORTHWITH` varchar(1) DEFAULT NULL,
  `OFFLINE_THIRD_CODE` varchar(64) DEFAULT NULL,
  `REPEAT_ACCESS_TIME` decimal(22,0) DEFAULT NULL,
  `BEFORE_TIME` varchar(10) DEFAULT NULL,
  `INCLUDE_OCC_DATE` varchar(10) DEFAULT NULL,
  `LATER_TIME` varchar(10) DEFAULT NULL,
  `OCC_DATE_TIME` varchar(10) DEFAULT NULL,
  `BEFORE_INCLUDE` varchar(1) DEFAULT NULL,
  `LATER_INCLUDE` varchar(1) DEFAULT NULL,
  `IS_BUY_AUDIT` varchar(1) DEFAULT NULL,
  `SEAT_INFO` varchar(256) DEFAULT NULL,
  `AUDIT_NUMBER` decimal(22,0) DEFAULT NULL,
  `confluent__last_updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`ID`),
  KEY `idx_create_time` (`CREATE_TIME`),
  KEY `idx_modify_time` (`MODIFY_TIME`),
  KEY `idx_confluent__last_updated` (`confluent__last_updated`),
  KEY `idx_goods_code` (`GOODS_CODE`),
  KEY `idx_corp_code` (`CORP_CODE`),
  KEY `idx_sale_corp_code` (`SALE_CORP_CODE`),
  KEY `idx_c_last_updated` (`confluent__last_updated`),
  KEY `order_info_id` (`ORDER_INFO_ID`),
  KEY `idx_sale_corp_code_create_time` (`SALE_CORP_CODE`,`CREATE_TIME`),
  KEY `idx_occ_date` (`OCC_DATE`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin

order_info表：CREATE TABLE `order_info` (
  `ID` decimal(30,0) NOT NULL,
  `LINK_NAME` varchar(64) DEFAULT NULL,
  `TEL` varchar(32) DEFAULT NULL,
  `ORDER_CODE` varchar(32) DEFAULT NULL,
  `CHECK_NO` varchar(64) DEFAULT NULL,
  `ASSIST_CHECK_NO` varchar(32) DEFAULT NULL,
  `CERTIFICATE_TYPE` varchar(32) DEFAULT NULL,
  `CERTIFICATE_NO` varchar(32) DEFAULT NULL,
  `CORP_GROUP_CODE` varchar(32) DEFAULT NULL,
  `CORP_CODE` varchar(32) DEFAULT NULL,
  `SYS_AREA_CODE` varchar(32) DEFAULT NULL,
  `SRC` varchar(32) DEFAULT NULL,
  `FEE` decimal(10,2) DEFAULT NULL,
  `MODEL` varchar(32) DEFAULT NULL,
  `PAY_METHOD` varchar(32) DEFAULT NULL,
  `PAY_STATUS` varchar(32) DEFAULT NULL,
  `CHECK_STATUS` varchar(32) DEFAULT NULL,
  `STATUS` varchar(32) DEFAULT NULL,
  `NEED_CHECK_NUM` decimal(22,0) DEFAULT NULL,
  `ALREADY_CHECK_NUM` decimal(22,0) DEFAULT NULL,
  `THIRD_CODE` varchar(64) DEFAULT NULL,
  `GROUP_NO` varchar(64) DEFAULT NULL,
  `CREATE_BY` varchar(32) DEFAULT NULL,
  `CREATE_TIME` datetime DEFAULT NULL,
  `MODIFY_BY` varchar(32) DEFAULT NULL,
  `MODIFY_TIME` datetime DEFAULT NULL,
  `RETURN_FEE` decimal(10,2) DEFAULT NULL,
  `RETURN_NUM` decimal(5,0) DEFAULT NULL,
  `CLOSE_FEE` decimal(10,2) DEFAULT NULL,
  `DELETED` varchar(1) DEFAULT NULL,
  `IS_REBATE` varchar(1) DEFAULT NULL,
  `INVALID_NUM` decimal(22,0) DEFAULT NULL,
  `AUDIT_FLAG` varchar(1) DEFAULT NULL,
  `SMSTEMPLATE_ID` varchar(32) DEFAULT NULL,
  `MMSTEMPLATE_ID` varchar(32) DEFAULT NULL,
  `PLATEFORM_CODE` varchar(32) DEFAULT NULL,
  `GROUP_FLAG` varchar(1) DEFAULT NULL,
  `PAY_TIME` datetime DEFAULT NULL,
  `TRAVEL_AGENCY_ID` decimal(16,0) DEFAULT NULL,
  `VERSION` decimal(16,0) DEFAULT NULL,
  `CLOSE_TIME` datetime DEFAULT NULL,
  `USER_AUDIT` varchar(32) DEFAULT NULL,
  `OUT_CODE` varchar(64) DEFAULT NULL,
  `MULT_PROXY_FLAG` varchar(1) DEFAULT NULL,
  `OFFLINE_THIRD_CODE_STATUS` varchar(32) DEFAULT NULL,
  `THIRD_PAY_METHOD` varchar(32) DEFAULT NULL,
  `UNION_CARD_NO` varchar(64) DEFAULT NULL,
  `SUB_SRC` varchar(32) DEFAULT NULL,
  `PHOTO_URL` varchar(200) DEFAULT NULL,
  `FACE_SRC` varchar(256) DEFAULT NULL,
  `HARDWARE_DEVICE_CODE` varchar(32) DEFAULT NULL,
  `TEL_MD5` varchar(48) DEFAULT NULL,
  `TEL_DESENSITIZATION` varchar(48) DEFAULT NULL,
  `CERT_MD5` varchar(48) DEFAULT NULL,
  `CERT_DESENSITIZATION` varchar(48) DEFAULT NULL,
  `confluent__last_updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`ID`),
  KEY `idx_create_time` (`CREATE_TIME`),
  KEY `idx_modify_time` (`MODIFY_TIME`),
  KEY `idx_confluent__last_updated` (`confluent__last_updated`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin

phone_area_new表：CREATE TABLE `phone_area_new` (
  `CODE` varchar(20) NOT NULL,
  `PROVICE` varchar(24) DEFAULT NULL,
  `ADDRESS` varchar(100) DEFAULT NULL,
  `CITY2` varchar(100) DEFAULT NULL,
  `confluent__last_updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`CODE`),
  KEY `idx_confluent__last_updated` (`confluent__last_updated`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
7

麻烦提供下版本号，方便进一步进行测试。select tidb_version();

8

Release Version: v4.0.0-alpha-206-gc1d553653

Git Commit Hash: c1d5536530c02d49943d98594fed048b8960c49b

Git Branch: master

UTC Build Time: 2019-09-09 05:14:45

GoVersion: go version go1.12 linux/amd64

Race Enabled: false

TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306

Check Table Before Drop: false`

9

感谢报错，已经复现，正在调查。 可以关注 github issue：https://github.com/pingcap/tidb/issues/13177

10

请问这个issue在哪个版本修复

11

v4.0.0-beta 以及之后的版本。

12

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