The hash join changes the query result: 'a' does not match 'a ' on a PAD SPACE _bin collation

Likely cause: commit a095346a9d32ca583d211d07075e805881fdb4e5 (LP bug #670380, 2010), in Field_str::eq_cmp_as_binary(), which kept the memcmp() key compare of the first hash join code for a BINARY_FLAG column. CS 10.11.19 and ES 10.6.28-24 have it too.

CREATE TABLE t1 (id INT, c VARCHAR(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin);

CREATE TABLE t2 (id INT, c VARCHAR(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin);

INSERT INTO t1 VALUES (1,'a'),(2,'b ');

INSERT INTO t2 VALUES (1,'a '),(2,'b'),(3,'c');

SET join_cache_level=4;

--let $c= query_get_value(SELECT COUNT(t2.id) AS c FROM t1 LEFT JOIN t2 ON t2.c = t1.c, c, 1)

if ($c != 2)

{

  --die The hash join does not match 'a' with 'a ' and 'b ' with 'b' on utf8mb4_bin, which ignores trailing spaces

}

SET join_cache_level=DEFAULT;

DROP TABLE t1, t2;

Leads to:

mysqltest: At line 9: The hash join does not match 'a' with 'a ' and 'b ' with 'b' on utf8mb4_bin, which ignores trailing spaces

The hash join changes the query result. With join_cache_level 1 or 2, the join matches both rows, and COUNT(t2.id) is 2. EXPLAIN shows "Using join buffer (flat, BNLH join)" for t2. Each join_cache_level from 3 to 8 gives 0, and latin1_bin gives the same. utf8mb4_nopad_bin, VARBINARY and CHAR give the right result. Optimized builds give the same result.

MySQL is not affected: MySQL 8.0.36 and 9.1.0 use a hash join for the same SELECT, and give 2.

From the code it seems that the Field_str constructor in sql/field.cc sets BINARY_FLAG for every collation with MY_CS_BINSORT, so also for utf8mb4_bin, which is PAD SPACE. Field_str::eq_cmp_as_binary() in sql/field.h returns that flag, and JOIN_CACHE_HASHED::init() in sql/sql_join_cache.cc then hashes and compares the join keys as bytes, with get_hash_idx_simple() and equal_keys_simple(). As bytes, 'a' and 'a ' differ.