#36571: Deprecated usage of BINARY expr in MySQL lookups
-------------------------------------+-------------------------------------
Reporter: Simon Charette | Owner: Youssef
Type: | Tarek Ali
Cleanup/optimization | Status: assigned
Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution:
Keywords: mysql binary like | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 1
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Youssef Tarek Ali):
Replying to [comment:16 Simon Charette]:
> I have to note that while I agree (in comment:2) with comment:1 in
theory I never cross verified it myself so it's entirely possible that
this simple approach just work.
>
> All I can say is that we've been using an approach similar to the `CAST`
approach at work and it has been working fine for us, we don't use exotic
collations though (we stick to `utf8_general_ci`).
>
> Something is definitely weird with this deprecation as MySQL's `LIKE`
documentation [https://dev.mysql.com/doc/refman/9.6/en/string-comparison-
functions.html#operator_like still mentions] the usage of `BINARY` to
force case-sensitive comparison. From my understanding we have gone the
extra length of doing so on MySQL because the historical default
collations are all case-insensitive (e.g. `utf8_general_ci`) and breaking
with this legacy by forcing users to move a case-sensitive collation on a
new version of Django would be quite disruptive even with the relatively
recent introduction of `Field(db_collation)`.
I verified this with a MySQL 9.0 Docker setup on `utf8mb4_general_ci`. The
RHS-only `CAST` is case-sensitive because MySQL treats comparisons with a
binary string as binary (per [https://dev.mysql.com/doc/refman/8.0/en
/string-comparison-functions.html#operator_like the docs]). This seems to
handle the deprecation nicely without losing index performance on the LHS.
--
Ticket URL: <https://code.djangoproject.com/ticket/36571#comment:17>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion visit
https://groups.google.com/d/msgid/django-updates/0107019cd4d27b4b-e0ad782c-2dd0-4419-aee2-72b0b28dd3d2-000000%40eu-central-1.amazonses.com.
