KeyTextTransform incompatible with GeneratedField

[Paolo Melchiorre]

Broken SQL code generated when KeyTextTransform is used in GeneratedField expression

Steps

Steps to reproduce the error.

Model

from django.db import models
from django.db.models.fields.json import KT

class Response(models.Model):
    data = models.JSONField(default=dict)
    status = models.GeneratedField(
        db_persist=True,
        expression=KT("data__status"),
        output_field=models.PositiveSmallIntegerField(),
    )

Migration

$ python3 -m manage makemigrations
Migrations for 'https':
  https/migrations/0001_initial.py
    - Create model Response
$ python -m manage sqlmigrate https 0001

BEGIN;
--
-- Create model Response
--
CREATE TABLE "https_response" (
    "id" bigint NOT NULL PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
    "data" jsonb NOT NULL,
    "status" smallint GENERATED ALWAYS AS (None("data")) STORED
);
COMMIT;

Traceback

$ python -m manage migrate https 0001

Operations to perform:
  Target specific migration: 0001_initial, from https
Running migrations:
  Applying https.0001_initial...Traceback (most recent call last):
  File "/home/paulox/Projects/generatedfield/.venv/lib/python3.11/site-packages/django/db/backends/utils.py", line 99, in _execute
    return self.cursor.execute(sql)
           ^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/paulox/Projects/generatedfield/.venv/lib/python3.11/site-packages/psycopg/cursor.py", line 737, in execute
    raise ex.with_traceback(None)
psycopg.errors.SyntaxError: syntax error at or near "("
LINE 1: ... NULL, "status" smallint GENERATED ALWAYS AS (None("data")) ...
                                                             ^

Queryset

Using KeyTextTransform in a query instead generates a correct SQL code.

>>> from django.db.models.fields.json import KT
>>> from https.models import Response
>>> str(Response.objects.values_list(KT("data__status")).query)
'SELECT ("https_response"."data" ->> status) AS "keytexttransform1" FROM "geometricfigures_response"'

[Simon Charette]

The issue lies in GeneratedField.generated_sql, it should not call as_sql directly as it doesn't account for {vendor}_sql overrides.

It should ​let the compiler compile the expression instead, something like

django/db/models/fields/generated.py

@@ -61 +61 @@
             self.register_lookup(lookup, lookup_name=lookup_name)
 
     def generated_sql(self, connection):
-        return self._resolved_expression.as_sql(
-            compiler=connection.ops.compiler("SQLCompiler")(
-                self._query, connection=connection, using=None
-            ),
-            connection=connection,
+        compiler = connection.ops.compiler("SQLCompiler")(
+            self._query, connection=connection, using=None
         )
+        return compiler.compile(self._resolved_expression)
 
     def check(self, **kwargs):
         databases = kwargs.get("databases") or [

[Paolo Melchiorre]

Replying to Simon Charette:

The issue lies in GeneratedField.generated_sql, it should not call as_sql directly as it doesn't account for {vendor}_sql overrides.

It should ​let the compiler compile the expression instead, something like

Thanks, Simon, I used the code you suggested and confirm that it solves the problem.

I tested the patch locally on my PC with both SQLite and PostgreSQL.

I submitted a commit with the fix where I added you as a co-author and tried to add an explicit test on the generated SQL code.

Here you can find my PR ​https://github.com/django/django/pull/17301

[Simon Charette]

Thanks for the patch and the tests Paolo!

[Paolo Melchiorre]

Replying to Simon Charette:

Thanks for the patch and the tests Paolo!

Thank you for your invaluable suggestions.

[Mariusz Felisiak <felisiak.mariusz@…>]

In 574ee402:

Fixed #34861 -- Fixed crash when adding GeneratedField with some expressions.

Co-authored-by: Simon Charette <charette.s@…>

[Mariusz Felisiak <felisiak.mariusz@…>]

In 81663cc4:

[5.0.x] Fixed #34861 -- Fixed crash when adding GeneratedField with some expressions.

Co-authored-by: Simon Charette <charette.s@…>

Backport of 574ee4023e15cfb195833edfbaed353f8021c62f from main