TiDB 4.0 查询单个列异常 Data is corrupted, missing data for NOT NULL column

mao_siyu · 2020 年4 月 15 日 07:49

版本

tidb 4.0 -rc

问题描述

-- 查询单个列 normal_quantity
mysql > SELECT
       DcfdSale.normal_quantity
       FROM
         dc_f_d_sale DcfdSale
       LEFT JOIN dc_o_master DcoMaster ON DcfdSale.distributor_code = DcoMaster.organ_code
       AND DcoMaster.paas_is_del = '0'

       WHERE
        DcfdSale.seller_date BETWEEN '2020-02-29 00:00:00'
       AND '2020-03-01 23:59:59'
(1105, u'[components/tidb_query/src/batch/executors/table_scan_executor.rs:333]: Data is corrupted, missing data for NOT NULL column (offset = 1)')



-- 查询所有
mysql > SELECT
       DcfdSale.*
       FROM
         dc_f_d_sale DcfdSale
       LEFT JOIN dc_o_master DcoMaster ON DcfdSale.distributor_code = DcoMaster.organ_code
       AND DcoMaster.paas_is_del = '0'

       WHERE
        DcfdSale.seller_date BETWEEN '2020-02-29 00:00:00'
       AND '2020-03-01 23:59:59'

yilong · 2020 年4 月 15 日 08:24

您好：

 1. 是只查单列报错，查所有列会不会报错？
 2. 请先拿一下表结构和 explain 吧
 3. 以及试一下 select DcfdSale.normal_quantity from dc_f_d_sale DcfdSale where DcfdSale.seller_date BETWEEN '2020-02-29 00:00:00' AND '2020-03-01 23:59:59' 看看会不会报错

mao_siyu · 2020 年4 月 15 日 08:57

你好：

1. 是只查单列报错，查所有列会不会报错？

答：只有这一个列报错

2. 表结构和 explain

CREATE TABLE `dc_f_d_sale` (
  `distributor_code` varchar(200) COLLATE utf8_bin DEFAULT NULL ,
  `distributor_name` varchar(200) COLLATE utf8_bin DEFAULT NULL ,
  `seller_name` varchar(200) COLLATE utf8_bin DEFAULT NULL ,
  `normal_seller_code` varchar(32) COLLATE utf8_general_ci NOT NULL DEFAULT '',
  `organ_name` varchar(200) COLLATE utf8_bin DEFAULT NULL ,
  `normal_organ_code` varchar(32) COLLATE utf8_general_ci DEFAULT '',
  `relation_organ_code` varchar(200) COLLATE utf8_bin DEFAULT NULL ,
  `relation_organ_name` varchar(200) COLLATE utf8_bin DEFAULT NULL ,
  `product_code` varchar(200) COLLATE utf8_bin DEFAULT NULL ,
  `product_name` varchar(200) COLLATE utf8_bin DEFAULT NULL ,
  `normal_product_code` varchar(32) COLLATE utf8_bin NOT NULL ,
  `normal_quantity` decimal(20,6) DEFAULT NULL ,
  `quantity` decimal(20,6) DEFAULT NULL ,
  `seller_mapping_id` varchar(32) COLLATE utf8_bin DEFAULT NULL ,
  `organ_mapping_id` varchar(32) COLLATE utf8_bin DEFAULT NULL ,
  `product_mapping_id` varchar(32) COLLATE utf8_bin DEFAULT NULL ,
  `br_status` varchar(32) COLLATE utf8_bin DEFAULT NULL ,
  `organ_enable` varchar(32) COLLATE utf8_bin DEFAULT NULL ,
  `product_enable` varchar(32) COLLATE utf8_bin DEFAULT NULL ,
  `data_flag` varchar(32) COLLATE utf8_bin DEFAULT NULL ,
  `source_file_id` varchar(500) COLLATE utf8_bin DEFAULT NULL ,
  `seller_date` date NOT NULL ,
  `source` varchar(32) COLLATE utf8_bin DEFAULT NULL ,
  `amount` decimal(64,6) DEFAULT NULL ,
  `is_disable` varchar(32) COLLATE utf8_bin DEFAULT '0' ,
  `create_user` varchar(32) COLLATE utf8_bin DEFAULT NULL ,
  `create_time` datetime DEFAULT NULL ,
  `update_user` varchar(32) COLLATE utf8_bin DEFAULT NULL ,
  `update_time` datetime DEFAULT NULL ,
  `version_no` varchar(32) COLLATE utf8_bin DEFAULT NULL ,
  `is_del` varchar(32) COLLATE utf8_bin DEFAULT '0' ,
  `id` varchar(32) COLLATE utf8_bin NOT NULL,
  `price` decimal(64,6) DEFAULT NULL ,
  `br_check_description` varchar(1000) COLLATE utf8_bin DEFAULT NULL ,
  `normal_price` decimal(20,6) DEFAULT NULL ,
  `normal_amount` decimal(20,6) DEFAULT NULL ,
  `agent_status` varchar(10) COLLATE utf8_bin DEFAULT NULL ,
  `belong_month` date DEFAULT NULL ,
  `organ_address` varchar(500) COLLATE utf8_bin DEFAULT NULL ,
  `specification` varchar(200) COLLATE utf8_bin DEFAULT NULL ,
  `vender_info` varchar(200) COLLATE utf8_bin DEFAULT NULL ,
  `odd_num` varchar(200) COLLATE utf8_bin DEFAULT NULL ,
  `lot_no` varchar(200) COLLATE utf8_bin DEFAULT NULL ,
  `validity_date` date DEFAULT NULL ,
  `create_ntid` varchar(32) COLLATE utf8_general_ci DEFAULT '' ,
  `update_ntid` varchar(32) COLLATE utf8_general_ci DEFAULT '' ,
  `seller_year_month` varchar(20) COLLATE utf8_general_ci DEFAULT '' ,
  PRIMARY KEY (`seller_date`,`id`),
  KEY `index1` (`seller_date`,`is_del`,`normal_product_code`,`normal_seller_code`,`normal_organ_code`,`br_status`,`distributor_code`),
  KEY `index2` (`seller_date`,`is_del`,`normal_product_code`,`normal_seller_code`,`normal_organ_code`,`br_status`,`distributor_code`,`source_file_id`),
  KEY `index_source_file_id` (`seller_date`,`source_file_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci COMMENT='数据'
PARTITION BY RANGE ( TO_DAYS(`seller_date`) ) (
  PARTITION `p200001` VALUES LESS THAN (730485),
  PARTITION `p201701` VALUES LESS THAN (736726),
  PARTITION `p201702` VALUES LESS THAN (736754),
  PARTITION `p201703` VALUES LESS THAN (736785),
  PARTITION `p201704` VALUES LESS THAN (736815),
  PARTITION `p201705` VALUES LESS THAN (736846),
  PARTITION `p201706` VALUES LESS THAN (736876),
  PARTITION `p201707` VALUES LESS THAN (736907),
  PARTITION `p201708` VALUES LESS THAN (736938),
  PARTITION `p201709` VALUES LESS THAN (736968),
  PARTITION `p201710` VALUES LESS THAN (736999),
  PARTITION `p201711` VALUES LESS THAN (737029),
  PARTITION `p201712` VALUES LESS THAN (737060),
  PARTITION `p201801` VALUES LESS THAN (737091),
  PARTITION `p201802` VALUES LESS THAN (737119),
  PARTITION `p201803` VALUES LESS THAN (737150),
  PARTITION `p201804` VALUES LESS THAN (737180),
  PARTITION `p201805` VALUES LESS THAN (737211),
  PARTITION `p201806` VALUES LESS THAN (737241),
  PARTITION `p201807` VALUES LESS THAN (737272),
  PARTITION `p201808` VALUES LESS THAN (737303),
  PARTITION `p201809` VALUES LESS THAN (737333),
  PARTITION `p201810` VALUES LESS THAN (737364),
  PARTITION `p201811` VALUES LESS THAN (737394),
  PARTITION `p201812` VALUES LESS THAN (737425),
  PARTITION `p201901` VALUES LESS THAN (737456),
  PARTITION `p201902` VALUES LESS THAN (737484),
  PARTITION `p201903` VALUES LESS THAN (737515),
  PARTITION `p201904` VALUES LESS THAN (737545),
  PARTITION `p201905` VALUES LESS THAN (737576),
  PARTITION `p201906` VALUES LESS THAN (737606),
  PARTITION `p201907` VALUES LESS THAN (737637),
  PARTITION `p201908` VALUES LESS THAN (737668),
  PARTITION `p201909` VALUES LESS THAN (737698),
  PARTITION `p201910` VALUES LESS THAN (737729),
  PARTITION `p201911` VALUES LESS THAN (737759),
  PARTITION `p201912` VALUES LESS THAN (737790),
  PARTITION `p202001` VALUES LESS THAN (737821),
  PARTITION `p202002` VALUES LESS THAN (737850),
  PARTITION `p202003` VALUES LESS THAN (737881),
  PARTITION `p202004` VALUES LESS THAN (737911),
  PARTITION `p202005` VALUES LESS THAN (737942),
  PARTITION `p202006` VALUES LESS THAN (737972),
  PARTITION `p202007` VALUES LESS THAN (738003),
  PARTITION `p202008` VALUES LESS THAN (738034),
  PARTITION `p202009` VALUES LESS THAN (738064),
  PARTITION `p202010` VALUES LESS THAN (738095),
  PARTITION `p202011` VALUES LESS THAN (738125),
  PARTITION `p202012` VALUES LESS THAN (738156),
  PARTITION `p202101` VALUES LESS THAN (738187),
  PARTITION `p202102` VALUES LESS THAN (738215),
  PARTITION `p202103` VALUES LESS THAN (738246),
  PARTITION `p202104` VALUES LESS THAN (738276),
  PARTITION `p202105` VALUES LESS THAN (738307),
  PARTITION `p202106` VALUES LESS THAN (738337),
  PARTITION `p202107` VALUES LESS THAN (738368),
  PARTITION `p202108` VALUES LESS THAN (738399),
  PARTITION `p202109` VALUES LESS THAN (738429),
  PARTITION `p202110` VALUES LESS THAN (738460),
  PARTITION `p202111` VALUES LESS THAN (738490),
  PARTITION `p202112` VALUES LESS THAN (738521),
  PARTITION `p202201` VALUES LESS THAN (738552),
  PARTITION `p202202` VALUES LESS THAN (738580),
  PARTITION `p202203` VALUES LESS THAN (738611),
  PARTITION `p202204` VALUES LESS THAN (738641),
  PARTITION `p202205` VALUES LESS THAN (738672),
  PARTITION `p202206` VALUES LESS THAN (738702),
  PARTITION `p202207` VALUES LESS THAN (738733),
  PARTITION `p202208` VALUES LESS THAN (738764),
  PARTITION `p202209` VALUES LESS THAN (738794),
  PARTITION `p202210` VALUES LESS THAN (738825),
  PARTITION `p202211` VALUES LESS THAN (738855),
  PARTITION `p202212` VALUES LESS THAN (738886),
  PARTITION `p202301` VALUES LESS THAN (738917),
  PARTITION `p202302` VALUES LESS THAN (738945),
  PARTITION `p202303` VALUES LESS THAN (738976),
  PARTITION `p202304` VALUES LESS THAN (739006),
  PARTITION `p202305` VALUES LESS THAN (739037),
  PARTITION `p202306` VALUES LESS THAN (739067),
  PARTITION `p202307` VALUES LESS THAN (739098),
  PARTITION `p202308` VALUES LESS THAN (739129),
  PARTITION `p202309` VALUES LESS THAN (739159),
  PARTITION `p202310` VALUES LESS THAN (739190),
  PARTITION `p202311` VALUES LESS THAN (739220),
  PARTITION `p202312` VALUES LESS THAN (739251),
  PARTITION `p202401` VALUES LESS THAN (739282),
  PARTITION `p202402` VALUES LESS THAN (739311),
  PARTITION `p202403` VALUES LESS THAN (739342),
  PARTITION `p202404` VALUES LESS THAN (739372),
  PARTITION `p202405` VALUES LESS THAN (739403),
  PARTITION `p202406` VALUES LESS THAN (739433),
  PARTITION `p202407` VALUES LESS THAN (739464),
  PARTITION `p202408` VALUES LESS THAN (739495),
  PARTITION `p202409` VALUES LESS THAN (739525),
  PARTITION `p202410` VALUES LESS THAN (739556),
  PARTITION `p202411` VALUES LESS THAN (739586),
  PARTITION `p202412` VALUES LESS THAN (739617),
  PARTITION `p202501` VALUES LESS THAN (739648),
  PARTITION `p202502` VALUES LESS THAN (739676),
  PARTITION `p202503` VALUES LESS THAN (739707),
  PARTITION `p202504` VALUES LESS THAN (739737),
  PARTITION `p202505` VALUES LESS THAN (739768),
  PARTITION `p202506` VALUES LESS THAN (739798),
  PARTITION `p202507` VALUES LESS THAN (739829),
  PARTITION `p202508` VALUES LESS THAN (739860),
  PARTITION `p202509` VALUES LESS THAN (739890),
  PARTITION `p202510` VALUES LESS THAN (739921),
  PARTITION `p202511` VALUES LESS THAN (739951),
  PARTITION `p202512` VALUES LESS THAN (739982),
  PARTITION `p202601` VALUES LESS THAN (740013),
  PARTITION `p202602` VALUES LESS THAN (740041),
  PARTITION `p202603` VALUES LESS THAN (740072),
  PARTITION `p202604` VALUES LESS THAN (740102),
  PARTITION `p202605` VALUES LESS THAN (740133),
  PARTITION `p202606` VALUES LESS THAN (740163),
  PARTITION `p202607` VALUES LESS THAN (740194),
  PARTITION `p202608` VALUES LESS THAN (740225),
  PARTITION `p202609` VALUES LESS THAN (740255),
  PARTITION `p202610` VALUES LESS THAN (740286),
  PARTITION `p202611` VALUES LESS THAN (740316),
  PARTITION `p202612` VALUES LESS THAN (740347),
  PARTITION `p202701` VALUES LESS THAN (740378),
  PARTITION `p202702` VALUES LESS THAN (740406),
  PARTITION `p202703` VALUES LESS THAN (740437),
  PARTITION `p202704` VALUES LESS THAN (740467),
  PARTITION `p202705` VALUES LESS THAN (740498),
  PARTITION `p202706` VALUES LESS THAN (740528),
  PARTITION `p202707` VALUES LESS THAN (740559),
  PARTITION `p202708` VALUES LESS THAN (740590),
  PARTITION `p202709` VALUES LESS THAN (740620),
  PARTITION `p202710` VALUES LESS THAN (740651),
  PARTITION `p202711` VALUES LESS THAN (740681)
);

mysql     > EXPLAIN SELECT
               DcfdSale.normal_quantity
               FROM
                 dc_f_d_sale DcfdSale
               LEFT JOIN dc_o_master DcoMaster ON DcfdSale.distributor_code = DcoMaster.organ_code
               AND DcoMaster.paas_is_del = '0'

               WHERE
                DcfdSale.seller_date BETWEEN '2020-02-29 00:00:00'
               AND '2020-03-01 23:59:59'
+--------------------------------+---------+-----------+-------------------------------------------------------------------------------------+---------------------------------------------------------------+
| id                             | estRows | task      | access object                                                                       | operator info                                                 |
+--------------------------------+---------+-----------+-------------------------------------------------------------------------------------+---------------------------------------------------------------+
| Projection_9                   | 500.00  | root      |                                                                                     | dev.dc_f_d_sale.normal_quantity          |
| └─Union_10                     | 500.00  | root      |                                                                                     |                                                               |
|   ├─IndexLookUp_19             | 250.00  | root      |                                                                                     |                                                               |
|   │ ├─IndexRangeScan_17(Build) | 250.00  | cop[tikv] | table:DcfdSale, partition:p202002, index:PRIMARY(seller_date, paas_id) | range:[2020-02-29,2020-03-01], keep order:false, stats:pseudo |
|   │ └─TableRowIDScan_18(Probe) | 250.00  | cop[tikv] | table:DcfdSale, partition:p202002                                      | keep order:false, stats:pseudo                                |
|   └─IndexLookUp_37             | 250.00  | root      |                                                                                     |                                                               |
|     ├─IndexRangeScan_35(Build) | 250.00  | cop[tikv] | table:DcfdSale, partition:p202003, index:PRIMARY(seller_date, paas_id) | range:[2020-02-29,2020-03-01], keep order:false, stats:pseudo |
|     └─TableRowIDScan_36(Probe) | 250.00  | cop[tikv] | table:DcfdSale, partition:p202003                                      | keep order:false, stats:pseudo                                |
+--------------------------------+---------+-----------+-------------------------------------------------------------------------------------+---------------------------------------------------------------+

8 rows in set
Time: 0.016s
mysql     >

3. dc_f_sale 表

mysql  > SELECT
        normal_quantity
       FROM
         dc_f_d_sale
       WHERE
        seller_date BETWEEN '2020-02-29 00:00:00' AND '2020-03-01 23:59:59'

       LIMIT 1
+-----------------+
| normal_quantity |
+-----------------+
| 10.000000       |
+-----------------+
1 row in set
Time: 0.017s
mysql  >

4. 我又尝试了 INNER JION 结果是可以的，目前看来只有 LEFT JION 有这个问题

mysql > SELECT
		   DcfdSale.normal_quantity
		   FROM
			   dc_f_d_sale DcfdSale
		   INNER JOIN dc_o_master DcoMaster ON DcfdSale.distributor_code = DcoMaster.organ_code
		   AND DcoMaster.paas_is_del = '0'

		   WHERE
			DcfdSale.seller_date BETWEEN '2020-02-29 00:00:00' AND '2020-03-01 23:59:59'

		   LIMIT 1
+-----------------+
| normal_quantity |
+-----------------+
| 111.980000      |
+-----------------+
1 row in set
Time: 0.055s
mysql >

5. dc_o_master

CREATE TABLE `dc_o_master` (
  `organ_code` varchar(100) DEFAULT NULL,
  `organ_name` varchar(200) DEFAULT NULL,
  `province` varchar(100) DEFAULT NULL,
  `city` varchar(200) DEFAULT NULL,
  `district` varchar(500) DEFAULT NULL,
  `address` varchar(500) DEFAULT NULL,
  `property_one` varchar(32) DEFAULT NULL,
  `merge_to` varchar(200) DEFAULT NULL,
  `parent_id` varchar(100) DEFAULT NULL,
  `organ_type` varchar(32) DEFAULT NULL,
  `level` varchar(32) DEFAULT NULL,
  `nature` varchar(32) DEFAULT NULL,
  `organ_used_names` varchar(2000) DEFAULT NULL,
  `is_territory` varchar(32) DEFAULT NULL,
  `su` varchar(500) DEFAULT NULL,
  `bai` varchar(500) DEFAULT NULL,
  `erp_code_one` varchar(32) DEFAULT NULL,
  `geography_id` varchar(100) DEFAULT NULL ,
  `org_note` varchar(500) DEFAULT NULL ,
  `grade` varchar(32) DEFAULT NULL ,
  `drugstore_type` varchar(32) DEFAULT NULL ,
  `alias` varchar(500) DEFAULT NULL ,
  `first_in_terr_date` varchar(32) DEFAULT NULL ,
  `erp_code` varchar(32) DEFAULT NULL ,
  `is_disable` varchar(32) DEFAULT '0' ,
  `create_user` varchar(32) DEFAULT NULL ,
  `create_time` datetime DEFAULT NULL ,
  `update_user` varchar(32) DEFAULT NULL ,
  `update_time` datetime DEFAULT NULL ,
  `version_no` varchar(32) DEFAULT NULL ,
  `is_del` varchar(32) DEFAULT '0' ,
  `id` varchar(100) NOT NULL,
  `sync_time` datetime DEFAULT NULL ,
  `head_office_code` varchar(100) DEFAULT NULL ,
  `head_office_name` varchar(200) DEFAULT NULL ,
  `head_office_used_names` varchar(2000) DEFAULT NULL ,
  `qualified_hospital` varchar(200) DEFAULT NULL ,
  `tripartite_certification` varchar(100) DEFAULT NULL ,
  `sap_code` varchar(100) DEFAULT NULL ,
  `legal_entity` varchar(100) DEFAULT NULL ,
  `status` varchar(32) DEFAULT NULL ,
  `operat_type` varchar(32) DEFAULT NULL ,
  `mdm_id` varchar(200) DEFAULT NULL ,
  `error_memo` varchar(2000) DEFAULT NULL ,
  `mapping_id` varchar(100) DEFAULT '' ,
  PRIMARY KEY (`id`),
  KEY `geography_id_fk_index` (`geography_id`),
  KEY `codeName_index` (`organ_code`,`organ_name`,`is_del`),
  KEY `organ_name` (`organ_name`),
  KEY `index_mdm_id` (`mdm_id`),
  KEY `index_1` (`is_del`,`organ_code`,`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='主数据';

spc_monkey · 2020 年4 月 15 日 09:16

1、有时间可以在原先语句上，加上一个条件：DcfdSale.distributor_code is not null ,来测试是否是 null 导致关联出错。即执行一下下面 SQL 看看是否报错：

SELECT
       DcfdSale.normal_quantity
       FROM
         dc_f_d_sale DcfdSale
       LEFT JOIN dc_o_master DcoMaster ON DcfdSale.distributor_code = DcoMaster.organ_code
       AND DcoMaster.paas_is_del = '0' AND DcfdSale.distributor_code is not null
       WHERE
        DcfdSale.seller_date BETWEEN '2020-02-29 00:00:00'
       AND '2020-03-01 23:59:59'

mao_siyu · 2020 年4 月 15 日 09:33

还是同样的错误

mysql  > SELECT
		   DcfdSale.normal_quantity
		   FROM
			 dc_f_d_sale DcfdSale
		   LEFT JOIN dc_o_master DcoMaster
		   ON DcfdSale.distributor_code = DcoMaster.organ_code
		   AND DcoMaster.paas_is_del = '0'
		   AND DcoMaster.organ_code IS NOT NULL
		   AND DcfdSale.distributor_code IS NOT NULL

		   WHERE
			DcfdSale.seller_date BETWEEN '2020-02-29 00:00:00' AND '2020-03-01 23:59:59'

(1105, u'[components/tidb_query/src/batch/executors/table_scan_executor.rs:333]: Data is corrupted, missing data for NOT NULL column (offset = 1)')
mysql  >

yilong · 2020 年4 月 15 日 11:01

这一列paas_is_del ，怎么在DDL里没有呢？

mao_siyu · 2020 年4 月 15 日 11:16

我把SQL 替换了，公司有信息安全要求，所有带 paas_都删除了

yilong · 2020 年4 月 15 日 11:18

好的，我们在分析下，多谢

mao_siyu · 2020 年4 月 16 日 01:14

表结构与统计信息.zip (7.8 KB)

密码微信发给你

yilong · 2020 年4 月 16 日 02:02

您好：
这个问题已经有PR会在后面的版本修复，多谢 https://github.com/pingcap/tidb/pull/16006

system · 2022 年10 月 31 日 19:06

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