#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:14 Adam Johnson]:
> > I made sure to only cast the RHS parameters to keep index performance
high.
>
> Wasn't the conclusion of the previous discussion that we cannot do just
the RHS?
I've verified in a Docker environment (MySQL 9.0 with
`utf8mb4_general_ci`) that casting only the RHS (e.g. `LIKE CAST(%s AS
BINARY)`) correctly forces a case-sensitive comparison. This aligns with
the MySQL documentation https://dev.mysql.com/doc/refman/8.0/en/string-
comparison-functions.html#operator_like, which states: "A comparison
between a character string and a binary string is treated as a comparison
of binary strings."
The primary advantage of this approach is that it preserves index usage by
leaving the LHS column untransformed. I have cross-verified this fix by
running the full `expressions` and `lookup` test suites (337 tests) on
both MySQL 9.0 and MariaDB latest backends, and all tests passed
(including `test_patterns_escape`).
--
Ticket URL: <https://code.djangoproject.com/ticket/36571#comment:15>
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/0107019ccafd20af-a88a05e0-f9c2-4126-8c05-b4d473bbdcf9-000000%40eu-central-1.amazonses.com.
