[Django] #36612: KeyTextTransform uses ->> on CASTed TextField to JSONField in MySQL, causing a syntax error

[Django]

#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.

[Django]

#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:

New description:

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%'

--
Ticket URL: <https://code.djangoproject.com/ticket/36612#comment:1>

[Django]

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:

{{{#!div style="font-size: 80%"
{{{#!sql

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

Ticket URL: <https://code.djangoproject.com/ticket/36612#comment:2>

[Django]

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

Reporter: Jacob Tavener | Owner: Simon
| Charette
Type: Bug | Status: assigned

Component: Database layer | Version: 4.2
(models, ORM) |
Severity: Normal | Resolution:

Keywords: | Triage Stage: Accepted

Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Simon Charette):

* owner: (none) => Simon Charette
* stage: Unreviewed => Accepted
* status: new => assigned

--
Ticket URL: <https://code.djangoproject.com/ticket/36612#comment:3>

[Django]

#36612: KeyTextTransform uses ->> on CASTed TextField to JSONField in MySQL,
causing a syntax error
-------------------------------------+-------------------------------------
Reporter: Jacob Tavener | Owner: Simon
| Charette
Type: Bug | Status: assigned
Component: Database layer | Version: 4.2
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted

Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by Simon Charette):

* has_patch: 0 => 1

--
Ticket URL: <https://code.djangoproject.com/ticket/36612#comment:4>

[Django]

#36612: KeyTextTransform uses ->> on CASTed TextField to JSONField in MySQL,
causing a syntax error
-------------------------------------+-------------------------------------
Reporter: Jacob Tavener | Owner: Simon
| Charette
Type: Bug | Status: assigned
Component: Database layer | Version: 4.2
(models, ORM) |
Severity: Normal | Resolution:

Keywords: | Triage Stage: Ready for
| checkin

Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Sarah Boyce):

* stage: Accepted => Ready for checkin

--
Ticket URL: <https://code.djangoproject.com/ticket/36612#comment:5>

[Django]

#36612: KeyTextTransform uses ->> on CASTed TextField to JSONField in MySQL,
causing a syntax error
-------------------------------------+-------------------------------------
Reporter: Jacob Tavener | Owner: Simon
| Charette

Type: Bug | Status: closed

Component: Database layer | Version: 4.2
(models, ORM) |

Severity: Normal | Resolution: fixed

Keywords: | Triage Stage: Ready for
| checkin
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Sarah Boyce <42296566+sarahboyce@…>):

* resolution: => fixed
* status: assigned => closed

Comment:

In [changeset:"af84cfba5970fda8306860b650937701c7c03c6f" af84cfba]:
{{{#!CommitTicketReference repository=""
revision="af84cfba5970fda8306860b650937701c7c03c6f"
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.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/36612#comment:6>

[Django]

#36612: KeyTextTransform uses ->> on CASTed TextField to JSONField in MySQL,
causing a syntax error
-------------------------------------+-------------------------------------
Reporter: Jacob Tavener | Owner: Simon
| Charette
Type: Bug | Status: closed
Component: Database layer | Version: 4.2
(models, ORM) |
Severity: Normal | Resolution: fixed
Keywords: | Triage Stage: Ready for
| checkin
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Sarah Boyce <42296566+sarahboyce@…>):

In [changeset:"ffac97bf63ea803a6196d2097b4186e0865d9091" ffac97b]:
{{{#!CommitTicketReference repository=""
revision="ffac97bf63ea803a6196d2097b4186e0865d9091"
[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.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/36612#comment:7>