Opened 4 weeks ago

Closed 3 weeks ago

Last modified 3 weeks ago

#36612 closed Bug (fixed)

KeyTextTransform uses ->> on CASTed TextField to JSONField in MySQL, causing a syntax error

Reported by: Jacob Tavener Owned by: Simon Charette
Component: Database layer (models, ORM) Version: 4.2
Severity: Normal Keywords:
Cc: Jacob Tavener Triage Stage: Ready for checkin
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description (last modified by Jacob Tavener)

Background / Context

In Django 4.2, KeyTextTransform is used for JSON key lookups that return text, such as when performing icontains on a JSON key.

On MySQL, Django generates SQL using the ->> operator. While this works for native JSONField columns, it fails when the JSON expression comes from a CAST of a TextField to a JSONField, because MySQL does not allow ->> on expressions like CAST(text_column AS JSON). The ->> operator is only valid on actual JSON columns. Attempting it on a CASTed expression produces a syntax error.

Steps to Reproduce

Create a model with a TextField storing JSON:

from django.db import models

class ExampleModel(models.Model):
json_text = models.TextField()

Store a sample JSON string in the TextField:

ExampleModel.objects.create(json_text='{"key": "value"}')

Perform a JSON key lookup using a cast and string lookup:

from django.db.models import F, JSONField
from django.db.models.functions import Cast

ExampleModel.objects.annotate(
json_data=Cast('json_text', JSONField())
).filter(json_data__key__icontains='val')

Expected Behavior

Django should generate valid MySQL SQL that extracts the JSON key as text, compatible with CASTed TextField columns. Example:

LOWER(JSON_UNQUOTE(JSON_EXTRACT(CAST(json_text as json), '$.key'))) LIKE LOWER('%val%')

This SQL should execute without errors.

Actual Behavior

Django generates SQL using the ->> operator on the CASTed expression:

LOWER(CAST(json_text as json)) ->> '$.key' LIKE LOWER('%val%')

This is invalid in MySQL when applied to a CASTed expression.

Results in a syntax error at runtime:

ProgrammingError: (1064, 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '->> '$.\"key\"')) LIKE LOWER('%val%') LIMIT 21' at line 1')

Current Workaround

A temporary workaround in projects using MySQL is to monkey patch KeyTextTransform.as_mysql to use JSON_UNQUOTE(JSON_EXTRACT(...)) instead of the ->> operator. This allows JSON key lookups on CASTed TextField → JSONField expressions to work correctly.

Example monkey patch:

from django.db.models.fields.json import KeyTextTransform, compile_json_path
from django.db.models import JSONField
from django.db.models.functions import Cast

original_as_mysql = KeyTextTransform.as_mysql

def safe_as_mysql(self, compiler, connection):
lhs, params, key_transforms = self.preprocess_lhs(compiler, connection)
json_path = compile_json_path(key_transforms)

sql, params = "JSON_EXTRACT(%s, %%s)" % lhs, tuple(params) + (json_path,)
return "JSON_UNQUOTE(%s)" % sql, params


KeyTextTransform.as_mysql = safe_as_mysql

Notes:

This patch only affects MySQL connections.

It applies globally and must be applied once at project startup (e.g., in an AppConfig.ready() method).

This is a temporary workaround; a proper fix in Django core is preferred.

Notes / Additional Context

This issue occurs only on MySQL.

Native JSONField columns are not affected; ->> works fine on them.

The problem arises specifically when a TextField containing JSON is cast to JSONField for ORM JSON lookups.

Change History (7)

comment:1 by Jacob Tavener, 4 weeks ago

Description: modified (diff)

comment:2 by Jacob Tavener, 4 weeks ago

Description: modified (diff)

comment:3 by Simon Charette, 4 weeks ago

Owner: set to Simon Charette
Status: new → assigned
Triage Stage: Unreviewed → Accepted

comment:4 by Simon Charette, 4 weeks ago

Has patch: set

comment:5 by Sarah Boyce, 4 weeks ago

Triage Stage: Accepted → Ready for checkin

comment:6 by Sarah Boyce <42296566+sarahboyce@…>, 3 weeks ago

Resolution: → fixed
Status: assigned → closed

In af84cfba:

Fixed #36612 -- Fixed a KeyTextTransform crash on MySQL against annotations.

MySQL only supports the ->> when used directly against columns, this can be
inferred by the presence of lhs.output_field.model as model bounds fields are
directly tied to columns.

Purposely don't systematically switch to using JSON_QUOTE(JSON_EXTRACT(...))
as there might be functional indices out there that rely on the SQL remaining
stable between versions.

Thanks Jacob Tavener for the report.

comment:7 by Sarah Boyce <42296566+sarahboyce@…>, 3 weeks ago

In ffac97b:

[6.0.x] Fixed #36612 -- Fixed a KeyTextTransform crash on MySQL against annotations.

MySQL only supports the ->> when used directly against columns, this can be
inferred by the presence of lhs.output_field.model as model bounds fields are
directly tied to columns.

Purposely don't systematically switch to using JSON_QUOTE(JSON_EXTRACT(...))
as there might be functional indices out there that rely on the SQL remaining
stable between versions.

Thanks Jacob Tavener for the report.

Backport of af84cfba5970fda8306860b650937701c7c03c6f from main.

Note: See TracTickets for help on using tickets.
Back to Top