#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)
follow-up: 3 comment:1 by , 14 months ago
Triage Stage: | Unreviewed → Accepted |
---|
comment:2 by , 14 months ago
Has patch: | set |
---|---|
Owner: | changed from | to
Status: | new → assigned |
comment:3 by , 14 months ago
Replying to Simon Charette:
The issue lies in
GeneratedField.generated_sql
, it should not callas_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:5 by , 14 months ago
Replying to Simon Charette:
Thanks for the patch and the tests Paolo!
Thank you for your invaluable suggestions.
comment:6 by , 14 months ago
Triage Stage: | Accepted → Ready for checkin |
---|
The issue lies in
GeneratedField.generated_sql
, it should not callas_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