Partitioning changes the query result after an ALTER TABLE to another collation of a partitioning column

--source include/have_partition.inc

--source include/have_innodb.inc

CREATE TABLE t1 (a VARCHAR(10) COLLATE utf8mb4_general_ci) ENGINE=InnoDB PARTITION BY LIST COLUMNS (a) (PARTITION p0 VALUES IN ('a'), PARTITION p1 DEFAULT);

INSERT INTO t1 VALUES ('A');

# With utf8mb4_bin, the row 'A' belongs in p1

ALTER TABLE t1 MODIFY a VARCHAR(10) COLLATE utf8mb4_bin;

--let $c= query_get_value(SELECT COUNT(*) AS c FROM t1 WHERE a='A', c, 1)

if ($c != 1)

{

  --die The row 'A' is not found by a='A' after the collation of the LIST COLUMNS column was changed

}

CREATE TABLE t2 (a VARCHAR(10) COLLATE utf8mb4_general_ci) ENGINE=InnoDB PARTITION BY KEY (a) PARTITIONS 5;

INSERT INTO t2 VALUES ('a'),('b'),('c'),('d'),('e'),('f'),('g'),('h');

ALTER TABLE t2 MODIFY a VARCHAR(10) COLLATE utf8mb4_bin;

--let $c= query_get_value(SELECT COUNT(*) AS c FROM t2 WHERE a='a' OR a='b' OR a='c' OR a='d' OR a='e' OR a='f' OR a='g' OR a='h', c, 1)

if ($c != 8)

{

  --die The condition a='a' OR ... OR a='h' finds $c of the 8 rows after the collation of the KEY partitioning column was changed

}

DROP TABLE t1, t2;

mysqltest: At line 11: The row 'A' is not found by a='A' after the collation of the LIST COLUMNS column was changed

+--------+----------------+

| MySQL  | t2, rows found |

+--------+----------------+

| 5.5.62 | 8 of 8         |

| 5.6.51 | 8 of 8         |

| 5.7.44 | 8 of 8         |

| 8.0.36 | 8 of 8         |

| 9.1.0  | 8 of 8         |

+--------+----------------+