Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
5.5(EOL), 10.0(EOL), 10.1(EOL), 10.2(EOL), 10.3(EOL), 10.4(EOL), 10.5, 10.6, 10.7(EOL), 10.8(EOL), 10.9(EOL), 10.10(EOL)
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.
Attachments
Issue Links
- causes
-
-
- Closed
-
- relates to
-
MDEV-16708 Unsupported commands for prepared statements
-
- Closed
-
Activity
Hi ralf.gebhardt@mariadb.com, I think a few hours should be enough.
There is a simple workaround:
PREPARE stmt FROM 'SELECT * FROM `tt` WHERE _utf8mb4`test` COLLATE utf8mb4_unicode_ci' LIKE ? |
This bug was fixed starting from 10.11 only, because there was a major refactoring in the code handling the COLLATE clause.
Fixing in an earlier version would need a different patch. It's not feasible to spend much time on a new patch as there is a simple workaround.
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.