[Django] #33450: Integer PK wrongly casted as UUID when using with a GenericForeignKey model with a UUID field

[Django]

#33450: Integer PK wrongly casted as UUID when using with a GenericForeignKey model
with a UUID field
-------------------------------------+-------------------------------------
Reporter: Santos | Owner: nobody
Gallegos |
Type: Bug | Status: new
Component: Database | Version: 4.0
layer (models, ORM) |
Severity: Normal | Keywords:
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
Hi, at Read the Docs we have migrated from Django 2.2 to 3.2, and we
encountered the following problem. In a model that makes use of a
GenericForeignKey field, and it has a UUID field as its pk will result in
Django casting the ID of the related model as UUID in some queries (the
related model makes use of the default BigAutoField). I was able to
reproduce this with the following models and using postgres as the DB
backend.

{{{#!python
from django.db import models
from django.contrib.contenttypes.models import ContentType
from django.contrib.contenttypes.fields import (
GenericForeignKey,
GenericRelation,
)
import uuid

class TTag(models.Model):
id = models.UUIDField(primary_key=True, default=uuid.uuid4,
editable=False)

content_type = models.ForeignKey(ContentType,
on_delete=models.CASCADE)
object_id = models.PositiveIntegerField()
content = GenericForeignKey()

class TIntegration(models.Model):
tags = GenericRelation(TTag, related_query_name='integration')
}}}

The problematic queries:

{{{#!python
integration = TIntegration.objects.create()
tag = integration.tags.create()
r = TTag.objects.filter(integration=integration)
print(r.query)

# The next error is raised if the query is evaluated
# psycopg2.errors.UndefinedFunction: operator does not exist: bigint =
uuid
}}}

The query will result in the following SQL

{{{#!sql
SELECT "core_ttag"."id", "core_ttag"."content_type_id",
"core_ttag"."object_id" FROM "core_ttag" INNER JOIN "core_tintegration" ON
("core_ttag"."object_id" = "core_tintegration"."id" AND
("core_ttag"."content_type_id" = 7)) WHERE "core_tintegration"."id" =
00000000-0000-0000-0000-000000000001
}}}

Note as `core_tintegration"."id"` is being casted as an UUID when it
should be an integer (1). I was able to bisect the error to this commit
https://github.com/django/django/commit/1afbc96a75bd1765a56054f57ea2d4b238af3f4d.
Before that commit, the generated SQL is as follows:

{{{#!sql
SELECT "core_ttag"."id", "core_ttag"."content_type_id",
"core_ttag"."object_id" FROM "core_ttag" INNER JOIN "core_tintegration" ON
("core_ttag"."object_id" = "core_tintegration"."id" AND
("core_ttag"."content_type_id" = 7)) WHERE "core_tintegration"."id" = 1
}}}

We were able to work around this issue by changing the queries to
explicitly use the ID.

{{{#!python
TTag.objects.filter(integration__id=integration.id)
}}}

--
Ticket URL: <https://code.djangoproject.com/ticket/33450>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

[Django]

#33450: Integer primary key is wrongly casted to UUID when filtering
GenericRelation on model with UUID primary key.
-------------------------------------+-------------------------------------
Reporter: Santos Gallegos | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 4.0
(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 Mariusz Felisiak):

* cc: Timur (added)
* stage: Unreviewed => Accepted

Comment:

Thanks for the report! Regression in
1afbc96a75bd1765a56054f57ea2d4b238af3f4d.

While checking this report I found another issue that's related but it's
not a regression:

{{{#!python
>>> integration = TIntegration.objects.create()
>>> tag = integration.tags.create()

>>> tag.integration.first()
...
File "/django/django/db/backends/utils.py", line 84, in _execute
return self.cursor.execute(sql, params)
django.db.utils.ProgrammingError: operator does not exist: uuid = numeric
LINE 1: ...ntent_type_id" = 7)) WHERE "test_33450_ttag"."id" = 10341161...
}}}
so cast to `uuid` is missing here 🤔.

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

[Django]

#33450: Integer primary key is wrongly casted to UUID when filtering
GenericRelation on model with UUID primary key.
-------------------------------------+-------------------------------------
Reporter: Santos Gallegos | Owner:

| jdboisvert
Type: Bug | Status: assigned

Component: Database layer | Version: 4.0
(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 jdboisvert):

* owner: nobody => jdboisvert
* status: new => assigned

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

[Django]

#33450: Integer primary key is wrongly casted to UUID when filtering
GenericRelation on model with UUID primary key.
-------------------------------------+-------------------------------------

Reporter: Santos Gallegos | Owner: Jeffrey

Type: Bug | Status: assigned
Component: Database layer | Version: 4.0
(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 Chinmoy):

* cc: Chinmoy (added)

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

[Django]

#33450: Integer primary key is wrongly casted to UUID when filtering
GenericRelation on model with UUID primary key.
-------------------------------------+-------------------------------------
Reporter: Santos Gallegos | Owner: Jeffrey
Type: Bug | Status: assigned
Component: Database layer | Version: 4.0
(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
-------------------------------------+-------------------------------------

Comment (by Jeffrey):

Upon further investigation in Santos Gallegos original post, I also found
that in RelatedLookupMixin.get_prep_lookup it also part of the issue since
"target_field = self.lhs.output_field.path_infos[-1].fields[-1]" points at
TTag's primary key which is a UUID instead of TIntegration's ID. Was able
to resolve the issue by using reverse_path_infos but unsure if this is
correct. So the issue is not so much related to
https://github.com/django/django/commit/1afbc96a75bd1765a56054f57ea2d4b238af3f4d
which does the casting but that at this point of the code it thinks it
should get the ID value for TTag instead of TIntegration.

I will try to see if I can resolve the issue but wanted to share what I
found so far. Let me know what you guys think.

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

[Django]

#33450: Integer primary key is wrongly casted to UUID when filtering
GenericRelation on model with UUID primary key.
-------------------------------------+-------------------------------------
Reporter: Santos Gallegos | Owner: Jeffrey
Type: Bug | Status: assigned
Component: Database layer | Version: 4.0
(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 Jeffrey):

* has_patch: 0 => 1
* stage: Accepted => Ready for checkin

Comment:

Here is the pull request [https://github.com/django/django/pull/15397]

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

[Django]

#33450: Integer primary key is wrongly casted to UUID when filtering
GenericRelation on model with UUID primary key.
-------------------------------------+-------------------------------------
Reporter: Santos Gallegos | Owner: Jeffrey
Type: Bug | Status: assigned
Component: Database layer | Version: 4.0
(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 Jacob Walls):

* stage: Ready for checkin => Accepted

Comment:

Thanks for submitting the PR. RFC is a status set by a reviewer.

--
Ticket URL: <https://code.djangoproject.com/ticket/33450#comment:6>

[Django]

#33450: Integer primary key is wrongly casted to UUID when filtering
GenericRelation on model with UUID primary key.
-------------------------------------+-------------------------------------
Reporter: Santos Gallegos | Owner: Jeffrey
Type: Bug | Status: assigned
Component: Database layer | Version: 4.0
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted

Has patch: 1 | Needs documentation: 0
Needs tests: 1 | Patch needs improvement: 1

Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Mariusz Felisiak):

* needs_better_patch: 0 => 1
* needs_tests: 0 => 1

--
Ticket URL: <https://code.djangoproject.com/ticket/33450#comment:7>

[Django]

#33450: Integer primary key is wrongly casted to UUID when filtering
GenericRelation on model with UUID primary key.
-------------------------------------+-------------------------------------

Reporter: Santos Gallegos | Owner: Abhinav
| Yadav

Type: Bug | Status: assigned
Component: Database layer | Version: 4.0
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Needs tests: 1 | Patch needs improvement: 1

Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Abhinav Yadav):

* owner: Jeffrey => Abhinav Yadav

--
Ticket URL: <https://code.djangoproject.com/ticket/33450#comment:8>

[Django]

#33450: Integer primary key is wrongly casted to UUID when filtering
GenericRelation on model with UUID primary key.
-------------------------------------+-------------------------------------
Reporter: Santos Gallegos | Owner: Abhinav
| Yadav
Type: Bug | Status: assigned
Component: Database layer | Version: 4.0
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Needs tests: 1 | Patch needs improvement: 1

Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Abhinav Yadav):

I have been working on this issue for a while, trying to understand it,
but I'm unable to figure out how django is working internally to answer a
question raised by Mariusz Felisiak in
[https://github.com/django/django/pull/15397/files#r804396962/ this
comment] asking why `self.lhs.output_field` points
`generic_relations.TIntegration.tags` and not to
`generic_relations.TIntegration`. I tried debugging and checking the
variables in related_lookups.py but to no avail.

Any suggestions on where to proceed so as to fix this bug? The patch
provided for this issue seems to work by reversing the fields but I
suppose it's not the expected behaviour to begin with?

--
Ticket URL: <https://code.djangoproject.com/ticket/33450#comment:9>

[Django]

#33450: Integer primary key is wrongly casted to UUID when filtering
GenericRelation on model with UUID primary key.
-------------------------------------+-------------------------------------

Reporter: Santos Gallegos | Owner: Clifford
| Gama

Type: Bug | Status: assigned
Component: Database layer | Version: 4.0
(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 Clifford Gama):

* has_patch: 1 => 0
* needs_better_patch: 1 => 0
* needs_tests: 1 => 0
* owner: Abhinav Yadav => Clifford Gama

--
Ticket URL: <https://code.djangoproject.com/ticket/33450#comment:10>

[Django]

#33450: Integer primary key is wrongly casted to UUID when filtering
GenericRelation on model with UUID primary key.
-------------------------------------+-------------------------------------
Reporter: Santos Gallegos | Owner: Clifford
| Gama
Type: Bug | Status: assigned
Component: Database layer | Version: 4.0
(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 Clifford Gama):

* has_patch: 0 => 1

Comment:

[https://github.com/django/django/pull/19290 PR]
--
Ticket URL: <https://code.djangoproject.com/ticket/33450#comment:11>

[Django]

#33450: Integer primary key is wrongly casted to UUID when filtering
GenericRelation on model with UUID primary key.
-------------------------------------+-------------------------------------
Reporter: Santos Gallegos | Owner: Clifford
| Gama
Type: Bug | Status: assigned
Component: Database layer | Version: 4.0
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted

Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 1

Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Clifford Gama):

* needs_better_patch: 0 => 1

--
Ticket URL: <https://code.djangoproject.com/ticket/33450#comment:12>

[Django]

#33450: Integer primary key is wrongly casted to UUID when filtering
GenericRelation on model with UUID primary key.
-------------------------------------+-------------------------------------
Reporter: Santos Gallegos | Owner: Clifford
| Gama
Type: Bug | Status: assigned
Component: Database layer | Version: 4.0
(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 Clifford Gama):

* needs_better_patch: 1 => 0

--
Ticket URL: <https://code.djangoproject.com/ticket/33450#comment:13>

[Django]

#33450: Integer primary key is wrongly casted to UUID when filtering
GenericRelation on model with UUID primary key.
-------------------------------------+-------------------------------------
Reporter: Santos Gallegos | Owner: Clifford
| Gama
Type: Bug | Status: assigned

Component: | Version: 4.0
contrib.contenttypes |

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 Clifford Gama):

* component: Database layer (models, ORM) => contrib.contenttypes

--
Ticket URL: <https://code.djangoproject.com/ticket/33450#comment:14>

[Django]

#33450: Integer primary key is wrongly casted to UUID when filtering
GenericRelation on model with UUID primary key.
-------------------------------------+-------------------------------------
Reporter: Santos Gallegos | Owner: Clifford
| Gama
Type: Bug | Status: assigned
Component: | Version: 4.0
contrib.contenttypes |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted

Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 1

Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Clifford Gama):

* needs_better_patch: 0 => 1

--
Ticket URL: <https://code.djangoproject.com/ticket/33450#comment:15>