[#MDEV-31149] Periodic 'Illegal mix of collations' for same query

The query below periodically fails with "Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8mb4_general_ci,COERCIBLE) for operation 'like'".

The error is not strange per se, but the fact that it only fails periodically boggles my mind.

Could it be query plan dependent?

SELECT *

FROM customers c

WHERE (

    name LIKE '%foo bøj%' OR

    att LIKE '%foo bøj%' OR

    billingName LIKE '%foo bøj%' OR

    billingAtt LIKE '%foo bøj%' OR

    profileName LIKE '%foo bøj%' OR

    profileAtt LIKE '%foo bøj%'

  AND c.email NOT LIKE 'anonymous+%@domain.com'

ORDER BY date DESC

LIMIT 100

Table:

CREATE TABLE `customers` (

  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,

  `date` int(10) unsigned NOT NULL,

  `name` varchar(100) NOT NULL DEFAULT '',

  `att` varchar(100) DEFAULT NULL,

  `billingName` varchar(100) DEFAULT NULL,

  `billingAtt` varchar(100) DEFAULT NULL,

  `profileName` varchar(100) NOT NULL DEFAULT '',

  `profileAtt` varchar(100) DEFAULT NULL,

  PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci

Environment:

character_set_client: latin1

character_set_connection: latin1

character_set_database: latin1

character_set_filesystem: binary

character_set_results: latin1

character_set_server: utf8mb4

character_set_system: utf8mb3

collation_connection: latin1_swedish_ci

collation_database: latin1_swedish_ci

collation_server: utf8mb4_general_ci

[MDEV-31149] Periodic 'Illegal mix of collations' for same query Created: 2023-04-28 Updated: 2024-01-03
Status:	Open
Project:	MariaDB Server
Component/s:	None
Affects Version/s:	10.11.2
Fix Version/s:	None

[MDEV-31149] Periodic 'Illegal mix of collations' for same query Created: 2023-04-28 Updated: 2024-01-03