#36612: KeyTextTransform uses ->> on CASTed TextField to JSONField in MySQL,
causing a syntax error
-------------------------------------+-------------------------------------
Reporter: Jacob Tavener | Owner: (none)
Type: Bug | Status: new
Component: Database layer | Version: 4.2
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Description changed by Jacob Tavener:
Old description:
> == 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.
New description:
== 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(CAST(json_text as json), '$.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
CAST(json_text as json)->>'$.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#comment:1>
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/0107019955d8376b-15d7d1ab-8303-4fe4-b201-e674c5dc315f-000000%40eu-central-1.amazonses.com.
