[MDEV-30415] MDEV-30415 PERIOD false positive overlap wtih utf8mb4_unicode_nopad_ci - Jira

Remy Fox created issue - 2023-01-16 13:40

Remy Fox made changes - 2023-01-16 13:41

Field	Original Value	New Value
Description	A UNIQUE index that includes a PERIOD in its definition may report a uniqueness violation, when it is not justified. 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 no error, unless 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') ; 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') ;}}	A UNIQUE index that includes a PERIOD in its definition may report a uniqueness violation, when it is not justified. 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 no error, unless the PERIOD is part of the unique index and the value with the trailing space gets inserted first. {quote}# 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') ; 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') ;{quote}

Remy Fox made changes - 2023-01-16 13:41

Description

A UNIQUE index that includes a PERIOD in its definition may report a uniqueness violation, when it is not justified. 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 no error, unless the PERIOD is part of the unique index and the value with the trailing space gets inserted first.

{quote}# 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')
;

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')
;{quote}

A UNIQUE index that includes a PERIOD in its definition may report a uniqueness violation, when it is not justified. 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 no error, unless 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')
;

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

Remy Fox made changes - 2023-01-16 13:42

Description

A UNIQUE index that includes a PERIOD in its definition may report a uniqueness violation, when it is not justified. 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 no error, unless 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')
;

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

A UNIQUE index that includes a PERIOD in its definition may report a uniqueness violation, when it is not justified. 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 no error, unless 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')
;

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

Remy Fox made changes - 2023-01-16 13:43

Description

A UNIQUE index that includes a PERIOD in its definition may report a uniqueness violation, when it is not justified. 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 no error, unless 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')
;

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

A UNIQUE index that includes a PERIOD in its definition may report a uniqueness violation, when it is not justified. 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 no error, unless the PERIOD is part of the unique index and the value with the trailing space gets inserted first.

{code}
# 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')
;

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')
;
{code}

Remy Fox made changes - 2023-01-16 13:44

Description

A UNIQUE index that includes a PERIOD in its definition may report a uniqueness violation, when it is not justified. 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 no error, unless the PERIOD is part of the unique index and the value with the trailing space gets inserted first.

{code}
# 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')
;

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')
;
{code}

A UNIQUE index that includes a PERIOD in its definition may report a uniqueness violation, when it is not justified. 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 no error, unless the PERIOD is part of the unique index and the value with the trailing space gets inserted first.

{code}
# 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')
;

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')
;
{code}

Remy Fox made changes - 2023-01-16 13:44

Description

A UNIQUE index that includes a PERIOD in its definition may report a uniqueness violation, when it is not justified. 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 no error, unless the PERIOD is part of the unique index and the value with the trailing space gets inserted first.

{code}
# 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')
;

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')
;
{code}

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.

{code}
# 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')
;

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')
;
{code}

Remy Fox made changes - 2023-01-16 13:45

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.

{code}
# 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')
;

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')
;
{code}

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.

{code}
# 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')
;
{code}

Remy Fox made changes - 2023-01-16 13:45

Affects Version/s

10.10.2 [ 28410 ]

Alice Sherepa made changes - 2023-01-16 14:59

Affects Version/s		10.5 [ 23123 ]
Affects Version/s		10.6 [ 24028 ]
Affects Version/s		10.7 [ 24805 ]
Affects Version/s		10.8 [ 26121 ]
Affects Version/s		10.9 [ 26905 ]
Affects Version/s		10.10 [ 27530 ]

Alice Sherepa added a comment - 2023-01-16 15:04

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)

Alice Sherepa added a comment - 2023-01-16 15:04 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)

Alice Sherepa made changes - 2023-01-16 15:05

Fix Version/s		10.5 [ 23123 ]
Fix Version/s		10.6 [ 24028 ]
Fix Version/s		10.7 [ 24805 ]
Fix Version/s		10.8 [ 26121 ]
Fix Version/s		10.9 [ 26905 ]
Fix Version/s		10.10 [ 27530 ]

Alice Sherepa made changes - 2023-01-16 15:06

Assignee

Nikita Malyavin [ nikitamalyavin ]

Alice Sherepa made changes - 2023-01-16 15:06

Status

Open [ 1 ]

Confirmed [ 10101 ]

Nikita Malyavin made changes - 2023-01-24 07:34

Summary

Unique index with a PERIOD false positive

~~MDEV-30415~~ PERIOD false positive overlap wtih utf8mb4_unicode_nopad_ci

Nikita Malyavin made changes - 2023-01-24 09:43

Assignee	Nikita Malyavin [ nikitamalyavin ]	Alexander Barkov [ bar ]
Status	Confirmed [ 10101 ]	In Review [ 10002 ]

Julien Fritsch made changes - 2023-03-03 17:35

Fix Version/s

10.7 [ 24805 ]

Alexander Barkov made changes - 2023-03-24 06:28

Status

In Review [ 10002 ]

Stalled [ 10000 ]

Alexander Barkov made changes - 2023-04-06 05:47

Status

Stalled [ 10000 ]

In Progress [ 3 ]

Alexander Barkov made changes - 2023-04-06 06:06

Component/s		Data types [ 13906 ]
Fix Version/s		10.5.20 [ 28512 ]
Fix Version/s		10.6.13 [ 28514 ]
Fix Version/s		10.8.8 [ 28518 ]
Fix Version/s		10.9.6 [ 28520 ]
Fix Version/s		10.10.4 [ 28522 ]
Fix Version/s		10.11.3 [ 28524 ]
Fix Version/s		11.1.1 [ 28704 ]
Fix Version/s		11.0.2 [ 28706 ]
Fix Version/s		10.7.8 [ 28515 ]
Fix Version/s	10.5 [ 23123 ]
Fix Version/s	10.6 [ 24028 ]
Fix Version/s	10.8 [ 26121 ]
Fix Version/s	10.9 [ 26905 ]
Fix Version/s	10.10 [ 27530 ]
Resolution		Fixed [ 1 ]
Status	In Progress [ 3 ]	Closed [ 6 ]

MariaDB Server

MDEV-30415 PERIOD false positive overlap wtih utf8mb4_unicode_nopad_ci

Details

Description

Attachments

Activity

People

Dates

Git Integration