UNIQUE HASH prefix on utf8mb4 TEXT allows rows with equal indexed prefixes

A UNIQUE HASH index on a one-character prefix of an utf8mb4 TEXT column allows both 'aa' and 'ab' to remain after INSERT IGNORE, although their indexed prefixes are equal. Equivalent latin1 HASH and utf8mb4 BTREE indexes retain only one row.

This occurs when INSERT IGNORE inserts strings with equal one-character prefixes into a UNIQUE HASH index on utf8mb4 TEXT.

Steps to reproduce

1. Save the following as case.sql.
2. Run mariadb < case.sql.

-- Oracle: every row-count result is 1; equal one-character prefixes conflict.

CREATE DATABASE prefix_test;

USE prefix_test;

CREATE TABLE latin_hash (a TEXT CHARACTER SET latin1 COLLATE latin1_bin, UNIQUE KEY ux_a (a(1)) USING HASH) ENGINE=InnoDB;

CREATE TABLE utf8_hash (a TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin, UNIQUE KEY ux_a (a(1)) USING HASH) ENGINE=InnoDB;

CREATE TABLE utf8_btree (a TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin, UNIQUE KEY ux_a (a(1)) USING BTREE) ENGINE=InnoDB;

INSERT IGNORE INTO latin_hash VALUES ('aa'), ('ab');

SELECT 'latin_hash', COUNT(*) FROM latin_hash;

INSERT IGNORE INTO utf8_hash VALUES ('aa'), ('ab');

SELECT 'utf8_hash', COUNT(*) FROM utf8_hash;

INSERT IGNORE INTO utf8_btree VALUES ('aa'), ('ab');

SELECT 'utf8_btree', COUNT(*) FROM utf8_btree;

SHOW CREATE TABLE utf8_hash;

SELECT 'equal_prefix', LEFT('aa',1) = LEFT('ab',1);

DROP DATABASE prefix_test;

SELECT 'cleanup', COUNT(*) FROM information_schema.schemata WHERE schema_name='prefix_test';

Actual result

The utf8mb4 HASH table contains two rows. The controls contain one row, and the direct prefix comparison returns 1. SHOW CREATE TABLE confirms that the accepted HASH key has prefix length 1.

latin_hash  1

utf8_hash   2

utf8_btree  1

utf8_hash   CREATE TABLE `utf8_hash` (

  `a` text CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,

  UNIQUE KEY `ux_a` (`a`(1)) USING HASH

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci

equal_prefix  1

cleanup       0

The statements complete without an SQL error or server interruption.

Expected result

All three row counts should be 1. Because LEFT('aa',1) = LEFT('ab',1) is true, 'aa' and 'ab' have the same indexed one-character prefix and must conflict under each UNIQUE index. INSERT IGNORE should ignore the second conflicting row rather than retain it.

Analysis

The available source analysis indicates a length-unit mismatch in the HASH uniqueness comparison for multibyte text: the TEXT prefix length is converted to bytes in key metadata, while the comparison routine consumes its length argument as a character count. Hash construction converts the prefix back to characters, but duplicate comparison receives the unconverted byte length and can therefore consider characters beyond the declared prefix.