Details
Critical Description
A UNIQUE index that includes a PERIOD in its definition may falsely report a uniqueness violation. Here is an example. Two of the three tables work correctly, but the one with the PERIOD in the UNIQUE index does not. If I insert a value with a trailing space, there should not be an error with collation 'utf8mb4_unicode_nopad_ci'. There is an error when the PERIOD is part of the unique index and the value with the trailing space gets inserted first.
# A table without datetime columns
|
CREATE TABLE `table` (
|
`text_column_name` TEXT COLLATE utf8mb4_unicode_nopad_ci NOT NULL,
|
UNIQUE KEY `index_name` (`text_column_name`(191))
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_nopad_ci;
|
|
|
# A table with two datetime columns
|
CREATE TABLE `table_with_two_datetime_columns` (
|
`datetime_column_name_1` DATETIME(6) NOT NULL,
|
`datetime_column_name_2` DATETIME(6) NOT NULL,
|
`text_column_name` TEXT COLLATE utf8mb4_unicode_nopad_ci NOT NULL,
|
UNIQUE KEY `index_name` (`text_column_name`(191),`datetime_column_name_1`,`datetime_column_name_2`)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_nopad_ci;
|
|
|
#A table with two datetime columns that are part of a PERIOD
|
CREATE TABLE `table_with_two_range_datetime_columns` (
|
`datetime_column_name_1` DATETIME(6) NOT NULL,
|
`datetime_column_name_2` DATETIME(6) NOT NULL,
|
`text_column_name` TEXT COLLATE utf8mb4_unicode_nopad_ci NOT NULL,
|
PERIOD FOR `period_name` (`datetime_column_name_1`, `datetime_column_name_2`),
|
UNIQUE KEY `index_name` (`text_column_name`(191),`period_name` WITHOUT OVERLAPS)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_nopad_ci;
|
|
|
# Insert values in the tables. First is the value without the extra space.
|
INSERT INTO `table`(`text_column_name`)
|
VALUES
|
('abc'),
|
('abc ')
|
;
|
|
|
INSERT INTO `table_with_two_datetime_columns`(`datetime_column_name_1`, `datetime_column_name_2`, `text_column_name`)
|
VALUES
|
('2000-01-01 00:00:00.000000', '2001-01-01 00:00:00.000000', 'abc'),
|
('2000-01-01 00:00:00.000000', '2001-01-01 00:00:00.000000', 'abc ')
|
;
|
|
|
INSERT INTO `table_with_two_range_datetime_columns`(`datetime_column_name_1`, `datetime_column_name_2`, `text_column_name`)
|
VALUES
|
('2000-01-01 00:00:00.000000', '2001-01-01 00:00:00.000000', 'abc'),
|
('2000-01-01 00:00:00.000000', '2001-01-01 00:00:00.000000', 'abc ')
|
;
|
|
|
# Now try again but this time we revert the order of insertions. First is the string with the extra space.
|
INSERT INTO `table`(`text_column_name`)
|
VALUES
|
('def '),
|
('def')
|
;
|
|
|
INSERT INTO `table_with_two_datetime_columns`(`datetime_column_name_1`, `datetime_column_name_2`, `text_column_name`)
|
VALUES
|
('2000-01-01 00:00:00.000000', '2001-01-01 00:00:00.000000', 'def '),
|
('2000-01-01 00:00:00.000000', '2001-01-01 00:00:00.000000', 'def')
|
;
|
|
|
# There will be an error for inserting into this table.
|
INSERT INTO `table_with_two_range_datetime_columns`(`datetime_column_name_1`, `datetime_column_name_2`, `text_column_name`)
|
VALUES
|
('2000-01-01 00:00:00.000000', '2001-01-01 00:00:00.000000', 'def '),
|
('2000-01-01 00:00:00.000000', '2001-01-01 00:00:00.000000', 'def')
|
;
|