Why does an error occur when inserting data even though a default partition was created when creating the table partition?

Translated
1

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: 创建表分区的时候创建了默认分区，为什么插入数据时还会报错

| username: TiDBer_7Q5CQdQd

[TiDB Usage Environment] Production Environment
[TiDB Version] 7.5

CREATE TABLE `core`.`company_f3` (
	`id` VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT 'Company ID',
	`name` VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT 'Company Name',
	`establish_date` DATE NULL DEFAULT NULL COMMENT 'Registration Date',
	`reg_capital_std` DOUBLE NULL DEFAULT NULL COMMENT 'Registered Capital, standardized unit. Unit: ten thousand yuan',
	`english_name` VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT 'English Name',
	`registration_code` VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT 'Business Registration Number',
	`organization_code` VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT 'Organization Code',
	`credit_code` VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT 'Unified Social Credit Code',
	`first_type` VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT 'Business Entity',
	`second_type` VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT 'Standardized Company Type',
	`detail_type` VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT 'Company Type',
	`industry` VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT 'National Economic Industry Code',
	`registered_capital` VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT 'Registered Capital',
	`registered_add` VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT 'Registered Address',
	`scope` TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL COMMENT 'Business Scope',
	`business_start_date` DATE NULL DEFAULT NULL COMMENT 'Business Start Date',
	`business_end_date` DATE NULL DEFAULT NULL COMMENT 'Business End Date',
	`reg_organization` VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT 'Registration Authority',
	`legal_representative` VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT 'Legal Representative',
	`legal_rep_type` INT(11) NULL DEFAULT NULL COMMENT 'Legal Representative Type',
	`approval_date` DATE NULL DEFAULT NULL COMMENT 'Approval Date',
	`cancel_date` DATE NULL DEFAULT NULL COMMENT 'Cancellation Date',
	`status_std` VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT 'Company Status',
	`registered_capital_unit` VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT 'Registered Capital Unit',
	`currency` VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT 'Registered Capital Currency',
	`tel` VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT 'Company Contact Number',
	`email` TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL COMMENT 'Email Address',
	`cancel_reason` TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL COMMENT 'Cancellation Reason',
	`ssfCount` VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT 'Number of Employees Insured',
	`wechat` LONGTEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL COMMENT 'WeChat Public Account',
	`logo` VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT 'Company Logo',
	`alias` VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT 'Company Alias',
	`create_time` DATETIME NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Creation Time',
	`update_time` DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT 'Update Time',
	`source_id` VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT 'Source ID',
	`reg_province` VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT 'Province',
	`reg_city` VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT 'City',
	`reg_district` VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT 'District',
	`reg_geo_wgs84` JSON NULL COMMENT 'Geographical Coordinates of Registered Address',
	`display` INT(255) NOT NULL COMMENT 'Company Status\n1: Active Business Entity\n2: Cancelled Company\n3: Non-Business Entity (Associations, Unions, Committees, Government Agencies, Universities, etc. registered with the Ministry of Civil Affairs or Ministry of Education)\n4: Hong Kong Company\n5: Taiwan Company\n0: Duplicate Company\n6: Individual Business\n7: Relocated Company\n8: Suspended Business\n9: Liquidation Company\n10: Other Status',
	`off_address` VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT 'Office Address',
	`off_province` VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT 'Office Province',
	`off_city` VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT 'Office City',
	`off_district` VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT 'Office District',
	`off_geo_wgs84` JSON NULL COMMENT 'Office Geographical Coordinates',
	`region_code` VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT 'Region Code',
	`capital_type` VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT 'Capital Type Label',
	`business_type` VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT 'Business Type',
	`company_scale` VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT 'Company Scale',
	`score_financing_ability` FLOAT NULL DEFAULT NULL COMMENT 'Investment Value/Financing Ability',
	`score_innovative_ability` FLOAT NULL DEFAULT NULL COMMENT 'Innovation Value/Innovation Ability',
	`score_operating_ability` FLOAT NULL DEFAULT NULL COMMENT 'Growth Potential/Operating Ability',
	`score_credit_risk` FLOAT NULL DEFAULT NULL COMMENT 'Credit Risk',
	`score_growth_potential` FLOAT NULL DEFAULT NULL COMMENT 'Growth Ability',
	`score_social_contribution` FLOAT NULL DEFAULT NULL COMMENT 'Social Contribution',
	`score_total_point` FLOAT NULL DEFAULT NULL COMMENT 'Final Score',
	`scale_tag` VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
	`score_competitive_strength` FLOAT NULL DEFAULT NULL COMMENT 'Competitive Strength Index',
	PRIMARY KEY (`id`, `reg_province`, `display`) USING BTREE,
	INDEX `establish_date` (`establish_date`) USING BTREE,
	INDEX `reg_city` (`reg_city`) USING BTREE,
	INDEX `establish_date_2` (`establish_date`, `reg_city`) USING BTREE,
	INDEX `score_total_point` (`score_total_point`) USING BTREE,
	INDEX `establish_date_3` (`establish_date`, `reg_province`, `reg_city`, `score_total_point`) USING BTREE,
	INDEX `reg_province` (`reg_province`) USING BTREE,
	INDEX `reg_district` (`reg_district`) USING BTREE,
	INDEX `name` (`name`) USING BTREE,
	INDEX `reg_capital_std_2` (`reg_capital_std`) USING BTREE,
	INDEX `status_std` (`status_std`) USING BTREE,
	INDEX `company_scale` (`company_scale`) USING BTREE,
	INDEX `mul` (`establish_date`, `reg_capital_std`, `status_std`, `company_scale`, `score_total_point`) USING BTREE,
	INDEX `establish_date_4` (`establish_date`, `reg_province`, `reg_city`, `reg_district`, `score_total_point`) USING BTREE
) ENGINE=INNODB CHARACTER SET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=Compact PARTITION BY LIST COLUMNS (`reg_province`) (
	PARTITION `bjs` VALUES IN ("北京市"),
	PARTITION `other` DEFAULT
);
2
| username: TiDBer_7Q5CQdQd | Original post link

An error occurs when inserting reg_province=“北京市1” after creating a list columns partition for the reg_province field.

3
| username: Kongdom | Original post link

:thinking: This error should be expected, right? It should be partition first, then insert data.

4
| username: TiDBer_7Q5CQdQd | Original post link

I first created the partitions, two of them: one is bjs with values in (“Beijing”), and the other is other as default. But when I insert “Beijing1”, it gives an error.

5
| username: wangccsy | Original post link

Is it possible that Beijing 1 was mistaken for Beijing? Try inserting Tianjin and see if it goes wrong.

6
| username: Kongdom | Original post link

According to the documentation, it should be supported, but I don’t have an environment with version 7.3 or above to verify.

7
| username: TiDBer_7Q5CQdQd | Original post link

The same error.

8
| username: TiDBer_7Q5CQdQd | Original post link

I used the show create table command and got the following result:

CREATE TABLE `company_f3` (
  `id` varchar(255) NOT NULL COMMENT 'Enterprise ID',
  `name` varchar(255) DEFAULT NULL COMMENT 'Enterprise Name',
  `establish_date` date DEFAULT NULL COMMENT 'Registration Date',
  `reg_capital_std` double DEFAULT NULL COMMENT 'Registered Capital, processed in a unified unit. Unit: ten thousand yuan',
  `english_name` varchar(255) DEFAULT NULL COMMENT 'English Name',
  `registration_code` varchar(255) DEFAULT NULL COMMENT 'Business Information - Registration Number',
  `organization_code` varchar(255) DEFAULT NULL COMMENT 'Business Information - Organization Code',
  `credit_code` varchar(255) DEFAULT NULL COMMENT 'Business Information - Unified Social Credit Code',
  `first_type` varchar(255) DEFAULT NULL COMMENT 'Business Entity',
  `second_type` varchar(255) DEFAULT NULL COMMENT 'Standardized Enterprise Type',
  `detail_type` varchar(255) DEFAULT NULL COMMENT 'Enterprise Type',
  `industry` varchar(255) DEFAULT NULL COMMENT 'National Economic Industry Code\n',
  `registered_capital` varchar(255) DEFAULT NULL COMMENT 'Business Information - Registered Capital',
  `registered_add` varchar(255) DEFAULT NULL COMMENT 'Business Information - Registered Address',
  `scope` text DEFAULT NULL COMMENT 'Business Scope',
  `business_start_date` date DEFAULT NULL COMMENT 'Business Start Date',
  `business_end_date` date DEFAULT NULL COMMENT 'Business End Date',
  `reg_organization` varchar(255) DEFAULT NULL COMMENT 'Business Information - Registration Authority',
  `legal_representative` varchar(255) DEFAULT NULL COMMENT 'Legal Representative Information',
  `legal_rep_type` int(11) DEFAULT NULL COMMENT 'Legal Representative Type',
  `approval_date` date DEFAULT NULL COMMENT 'Approval Date',
  `cancel_date` date DEFAULT NULL COMMENT 'Cancellation Date',
  `status_std` varchar(255) DEFAULT NULL COMMENT 'Enterprise Status',
  `registered_capital_unit` varchar(255) DEFAULT NULL COMMENT 'Registered Capital Unit',
  `currency` varchar(255) DEFAULT NULL COMMENT 'Registered Capital Currency',
  `tel` varchar(255) DEFAULT NULL COMMENT 'Business Information - Enterprise Contact Number',
  `email` text DEFAULT NULL COMMENT 'Business Information - Email Address',
  `cancel_reason` text DEFAULT NULL COMMENT 'Business Information - Cancellation Reason',
  `ssfCount` varchar(255) DEFAULT NULL COMMENT 'Business Information - Number of Employees Insured',
  `wechat` longtext DEFAULT NULL COMMENT 'WeChat Public Account',
  `logo` varchar(255) DEFAULT NULL COMMENT 'Company Logo',
  `alias` varchar(255) DEFAULT NULL COMMENT 'Enterprise Alias (Generated)',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT 'Creation Time',
  `update_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT 'Modification Time',
  `source_id` varchar(255) DEFAULT NULL COMMENT 'Source ID',
  `reg_province` varchar(255) NOT NULL COMMENT 'Province',
  `reg_city` varchar(255) DEFAULT NULL COMMENT 'City',
  `reg_district` varchar(255) DEFAULT NULL COMMENT 'District',
  `reg_geo_wgs84` json DEFAULT NULL COMMENT 'Registered Address Geographic Coordinates',
  `display` int(255) NOT NULL COMMENT 'Determines whether the enterprise is a valid enterprise\n1: Normally operating business entity\n2: Cancelled enterprise\n3: Non-business entity (associations, unions, committees, state agencies, state institutions, universities, etc. registered with the Ministry of Civil Affairs or Ministry of Education)\n4: Hong Kong company\n5: Taiwan enterprise\n0: Duplicate enterprise\n6: Individual industrial and commercial households\n7: Relocated enterprise\n8: Suspended enterprise\n9: Liquidation enterprise\n10: Other status enterprises\n',
  `off_address` varchar(255) DEFAULT NULL COMMENT 'Office Address',
  `off_province` varchar(255) DEFAULT NULL COMMENT 'Office Province',
  `off_city` varchar(255) DEFAULT NULL COMMENT 'Office City',
  `off_district` varchar(255) DEFAULT NULL COMMENT 'Office District',
  `off_geo_wgs84` json DEFAULT NULL COMMENT 'Office Geographic Coordinates',
  `region_code` varchar(255) DEFAULT NULL COMMENT 'Region Code',
  `capital_type` varchar(255) DEFAULT NULL COMMENT 'Capital Type Label',
  `business_type` varchar(255) DEFAULT NULL COMMENT 'Production and Operation Type',
  `company_scale` varchar(255) DEFAULT NULL COMMENT 'Enterprise Scale',
  `score_financing_ability` float DEFAULT NULL COMMENT 'Investment Value/Financing Ability',
  `score_innovative_ability` float DEFAULT NULL COMMENT 'Innovation Value/Innovation Ability',
  `score_operating_ability` float DEFAULT NULL COMMENT 'Growth Potential/Operating Ability',
  `score_credit_risk` float DEFAULT NULL COMMENT 'Credit Risk',
  `score_growth_potential` float DEFAULT NULL COMMENT 'Growth Ability',
  `score_social_contribution` float DEFAULT NULL COMMENT 'Social Contribution',
  `score_total_point` float DEFAULT NULL COMMENT 'Final Score',
  `scale_tag` varchar(255) DEFAULT NULL,
  `score_competitive_strength` float DEFAULT NULL COMMENT 'Competitiveness Index',
  PRIMARY KEY (`id`,`reg_province`,`display`) /*T![clustered_index] CLUSTERED */,
  KEY `establish_date` (`establish_date`),
  KEY `reg_city` (`reg_city`),
  KEY `establish_date_2` (`establish_date`,`reg_city`),
  KEY `score_total_point` (`score_total_point`),
  KEY `establish_date_3` (`establish_date`,`reg_province`,`reg_city`,`score_total_point`),
  KEY `reg_province` (`reg_province`),
  KEY `reg_district` (`reg_district`),
  KEY `name` (`name`),
  KEY `reg_capital_std_2` (`reg_capital_std`),
  KEY `status_std` (`status_std`),
  KEY `company_scale` (`company_scale`),
  KEY `mul` (`establish_date`,`reg_capital_std`,`status_std`,`company_scale`,`score_total_point`),
  KEY `establish_date_4` (`establish_date`,`reg_province`,`reg_city`,`reg_district`,`score_total_point`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
PARTITION BY LIST COLUMNS(`reg_province`)
(PARTITION `bjs` VALUES IN ('Beijing'),
 PARTITION `other` VALUES IN ())

I suspect there might be a bug.

9
| username: TiDBer_7Q5CQdQd | Original post link

The case is solved. I am using version 7.1, and this feature was added in version 7.3.

10
| username: tidb菜鸟一只 | Original post link

It works for me in version 7.3. You must not be using version 7.5, right?

11
| username: Kongdom | Original post link

When posting, you need to indicate the version clearly. It was because I saw your indication that I specifically wrote version 7.3.

12
| username: wangccsy | Original post link

The latest version is 7.5. This is the version I used for my experiment.

13
| username: dba远航 | Original post link

It’s good that you found the reason.

14
| username: ShawnYan | Original post link

Next time, you can add select tidb_version() in the post.

15
| username: wangccsy | Original post link

My experimental version.

16
| username: system | Original post link

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.