[MDEV-30415] MDEV-30415 PERIOD false positive overlap wtih utf8mb4_unicode_nopad_ci Created: 2023-01-16  Updated: 2023-04-06  Resolved: 2023-04-06

Status: Closed
Project: MariaDB Server
Component/s: Data types
Affects Version/s: 10.10.2, 10.5, 10.6, 10.7, 10.8, 10.9, 10.10
Fix Version/s: 11.1.1, 10.11.3, 11.0.2, 10.5.20, 10.6.13, 10.7.8, 10.8.8, 10.9.6, 10.10.4

Type: Bug Priority: Critical
Reporter: Remy Fox Assignee: Alexander Barkov
Resolution: Fixed Votes: 0
Labels: None


 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')
 
;



 Comments   
Comment by Alice Sherepa [ 2023-01-16 ]

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: 0
 
 
 
MariaDB [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)

Generated at Thu Feb 08 10:16:05 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.