[MDEV-10865] COLLATE keyword doesn't work in PREPARE query Created: 2016-09-22  Updated: 2023-11-28

Status: Confirmed
Project: MariaDB Server
Component/s: Character Sets
Affects Version/s: 5.5, 10.0, 10.1, 10.2, 10.3, 10.4, 10.5, 10.6, 10.7, 10.8, 10.9, 10.10
Fix Version/s: 10.4, 10.5, 10.6

Type: Bug Priority: Major
Reporter: Diego Dupin Assignee: Alexander Barkov
Resolution: Unresolved Votes: 0
Labels: django, upstream

Issue Links:
Problem/Incident
causes CONJ-345 Regression with unexpected collation ... Closed
Relates
relates to MDEV-16708 Unsupported commands for prepared sta... Closed

 Description   

COLLATE keyword doesn't work in PREPARE query.
Example : 

CREATE TABLE IF NOT EXISTS `tt` (
 
    `test` varchar(500) COLLATE utf8mb4_unicode_ci NOT NULL
 
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
 
 
 
SELECT * FROM `tt` WHERE `test` LIKE  'jj' COLLATE utf8mb4_unicode_ci

Text work well

	PREPARE stmt FROM 'SELECT * FROM `tt` WHERE `test` LIKE  ? COLLATE utf8mb4_unicode_ci'

will throw an error 

Erreur SQL (1253) : COLLATION 'utf8mb4_unicode_ci' is not valid for CHARACTER SET 'binary'"

In PREPARE, collate is always considering character set as binary.

 Comments   
Comment by Elena Stepanova [ 2016-09-26 ]

Same on MySQL 5.7.

More precisely, it's not just "in prepare", it's "in prepare for placeholders".
I fully expect it to turn out to be intentional or at least inevitable, but I will leave it to bar to decide and comment on it.

Comment by Alexander Barkov [ 2016-09-27 ]

Looks like a bug.

Comment by Alexander Barkov [ 2020-07-27 ]

Hi ralf.gebhardt@mariadb.com, I think a few hours should be enough.

Comment by Alexander Barkov [ 2020-09-20 ]

There is a simple workaround:

PREPARE stmt FROM 'SELECT * FROM `tt` WHERE _utf8mb4`test` COLLATE utf8mb4_unicode_ci' LIKE  ?

Generated at Thu Feb 08 07:45:31 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.