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')
|
;
|
Thank you! I repeated as described on 10.5-10.11.
It is somehow also dependent on which value was inserted first. If that was the one without trailing space - then there is no error:
MariaDB [test]> CREATE TABLE `t1` (-> `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;Query OK, 0 rows affected (0,056 sec)MariaDB [test]> INSERT INTO t1 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');ERROR 1062 (23000): Duplicate entry 'def-2001-01-01 00:00:00.000000-2000-01-01 00:00:00.000000' for key 'index_name'MariaDB [test]> INSERT INTO t1 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 '), ('2000-01-01 00:00:00.000000', '2001-01-01 00:00:00.000000', 'def ');Query OK, 3 rows affected (0,021 sec)Records: 3 Duplicates: 0 Warnings: 0MariaDB [test]> select * from t1;+----------------------------+----------------------------+------------------+| datetime_column_name_1 | datetime_column_name_2 | text_column_name |+----------------------------+----------------------------+------------------+| 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 || 2000-01-01 00:00:00.000000 | 2001-01-01 00:00:00.000000 | def |+----------------------------+----------------------------+------------------+3 rows in set (0,002 sec)