Opened 14 months ago

Closed 14 months ago

Last modified 14 months ago

#34861 closed Bug (fixed)

KeyTextTransform incompatible with GeneratedField

Reported by: Paolo Melchiorre Owned by: Paolo Melchiorre
Component: Database layer (models, ORM) Version: 5.0
Severity: Release blocker Keywords: field, database, generated, output_field
Cc: 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

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

Change History (8)

comment:1 by Simon Charette, 14 months ago

Triage Stage: UnreviewedAccepted

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

    diff --git a/django/db/models/fields/generated.py b/django/db/models/fields/generated.py
    index deb5875638..225d3e9d12 100644
    a b def contribute_to_class(self, *args, **kwargs):  
    6161            self.register_lookup(lookup, lookup_name=lookup_name)
    6262
    6363    def generated_sql(self, connection):
    64         return self._resolved_expression.as_sql(
    65             compiler=connection.ops.compiler("SQLCompiler")(
    66                 self._query, connection=connection, using=None
    67             ),
    68             connection=connection,
     64        compiler = connection.ops.compiler("SQLCompiler")(
     65            self._query, connection=connection, using=None
    6966        )
     67        return compiler.compile(self._resolved_expression)
    7068
    7169    def check(self, **kwargs):
    7270        databases = kwargs.get("databases") or [
Last edited 14 months ago by Simon Charette (previous) (diff)

comment:2 by Paolo Melchiorre, 14 months ago

Has patch: set
Owner: changed from nobody to Paolo Melchiorre
Status: newassigned

in reply to:  1 comment:3 by Paolo Melchiorre, 14 months ago

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

comment:4 by Simon Charette, 14 months ago

Thanks for the patch and the tests Paolo!

in reply to:  4 comment:5 by Paolo Melchiorre, 14 months ago

Replying to Simon Charette:

Thanks for the patch and the tests Paolo!

Thank you for your invaluable suggestions.

comment:6 by Mariusz Felisiak, 14 months ago

Triage Stage: AcceptedReady for checkin

comment:7 by Mariusz Felisiak <felisiak.mariusz@…>, 14 months ago

Resolution: fixed
Status: assignedclosed

In 574ee402:

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

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

comment:8 by Mariusz Felisiak <felisiak.mariusz@…>, 14 months ago

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

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