#36612: KeyTextTransform uses ->> on CASTed TextField to JSONField in MySQL,
causing a syntax error
-------------------------------------+-------------------------------------
Reporter: jacobtavenerhulla | Type: Bug
Status: new | Component: Database
| layer (models, ORM)
Version: 4.2 | Severity: Normal
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
== 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:
{{{#!div style="font-size: 80%"
{{{#!python
from django.db import models
class ExampleModel(models.Model):
json_text = models.TextField()
}}}
}}}
Store a sample JSON string in the TextField:
{{{#!div style="font-size: 80%"
{{{#!python
ExampleModel.objects.create(json_text='{"key": "value"}')
}}}
}}}
Perform a JSON key lookup using a cast and string lookup:
{{{#!div style="font-size: 80%"
{{{#!python
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:
{{{#!div style="font-size: 80%"
{{{#!sql
JSON_UNQUOTE(JSON_EXTRACT(json_text, '$.key')) LIKE '%val%'
}}}
}}}
This SQL should execute without errors.
== Actual Behavior ==
Django generates SQL using the ->> operator on the CASTed expression:
{{{#!div style="font-size: 80%"
{{{#!sql
json_text->>'$.key' LIKE '%val%'
}}}
}}}
This is invalid in MySQL when applied to a CASTed expression.
Results in a syntax error at runtime:
{{{#!div style="font-size: 80%"
{{{#!text
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:
{{{#!div style="font-size: 80%"
{{{#!python
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.
--
Ticket URL: <https://code.djangoproject.com/ticket/36612>
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 django-updates+unsubscr...@googlegroups.com.
To view this discussion visit
https://groups.google.com/d/msgid/django-updates/0107019955b91ed1-0b452063-f254-4005-85af-772fd46686e5-000000%40eu-central-1.amazonses.com.
