Details
-
Bug
-
Status: Closed (View Workflow)
-
Blocker
-
Resolution: Fixed
-
10.5, 10.6, 10.11, 11.1(EOL), 11.2, 11.0(EOL)
-
None
Description
REPLACE with unique key works in some cases, not in other cases:
CREATE OR REPLACE TABLE t1 (
|
`id` bigint(20) NOT NULL AUTO_INCREMENT,
|
`M_Number` varchar(30) DEFAULT NULL,
|
`A_Number` varchar(30) DEFAULT NULL,
|
`Type` varchar(255) DEFAULT NULL,
|
`S_Id` varchar(30) DEFAULT NULL,
|
`U_Id` varchar(255) DEFAULT NULL,
|
`IND` varchar(255) DEFAULT NULL,
|
PRIMARY KEY (`id`),
|
UNIQUE `problem_key` (`A_Number`,`S_Id`,`U_Id`,`M_Number`,`Type`,`IND`) USING HASH
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci;
|
INSERT INTO t1 (`M_Number`, `A_Number`, `Type`, `S_Id`, `U_Id`, `IND`) VALUES ('00004', '0001009089999', '', 'NETSTES', 'PSIT', 'D');
|
REPLACE INTO t1 (`M_Number`, `A_Number`, `Type`, `S_Id`, `U_Id`, `IND`) VALUES ('00004', '0001009089999', '', 'NETSTES', 'PSIT', 'D');
|
INSERT INTO t1 (`M_Number`, `A_Number`, `Type`, `S_Id`, `U_Id`, `IND`) VALUES ('00004', '0001009089999', '', 'NETSTES', 'PSIT', 'E');
|
# This should not generate a duplicate key error
|
REPLACE INTO t1 (`M_Number`, `A_Number`, `Type`, `S_Id`, `U_Id`, `IND`) VALUES ('00004', '0001009089999', '', 'NETSTES', 'PSIT', 'E');
|
ERROR: Duplicate entry '0001009089999-NETSTES-PSIT-00004--E' for key 'problem_key'
|
|
select * from t1;
|
|
+----+----------+---------------+------+---------+------+------+
|
| id | M_Number | A_Number | Type | S_Id | U_Id | IND |
|
+----+----------+---------------+------+---------+------+------+
|
| 0 | 00004 | 0001009089999 | | NETSTES | PSIT | D |
|
| 2 | 00004 | 0001009089999 | | NETSTES | PSIT | E |
|
+----+----------+---------------+------+---------+------+------+
|
There are two bugs here:
The last REPLACE should replace the third inserted row and not give an error
The first replace should not change the ID to 0
One can get the correct result by removing 'USING HASH' above:
+----+----------+---------------+------+---------+------+------+
|
| id | M_Number | A_Number | Type | S_Id | U_Id | IND |
|
+----+----------+---------------+------+---------+------+------+
|
| 2 | 00004 | 0001009089999 | | NETSTES | PSIT | D |
|
| 4 | 00004 | 0001009089999 | | NETSTES | PSIT | E |
|
+----+----------+---------------+------+---------+------+------+
|
Attachments
Issue Links
- relates to
-
MDEV-33003 long uniques don't work with spider
- Open
-
MDEV-33731 Server crashes when deleting partitions from a table with spider engine
- Closed
-
MDEV-32837 long unique does not work like unique key when using replace
- Closed