Cast() to AutoField generates invalid SQL

[Andrew Standley]

Using PostgreSQL 10.1 I encountered a problem trying to use Cast with an AutoField. Trying to perform a manual join on generic foreign keys:
Item.objects.filter(pk__in=Activity.objects.filter(content_type=item_ct).annotate(casted_pk=Cast('object_pk', AutoField())).values('casted_pk'))
The db connection responds with a "psycopg2.ProgrammingError: type "serial" does not exist" error.

Upon investigation, it seems that AutoField.cast_db_type uses the inherited definition from Field and returns AutoField.db_type. However AutoField.db_type returns 'serial', which is a "syntactical sugar" (not a true type) in Postgresql and only valid for creation.
The issue arises when AutoField.cast_db_type also returns 'serial', which is invalid.

More confusingly AutoField.rel_db_type has already been overridden and correctly returns 'integer'.

I believe fixing this is as easy as copying the override of AutoField.rel_db_type to AutoField.cast_db_type.
I'll try to at least get this implemented as a test on my fork, so that it is easy to confirm, but in the mean time the models to reproduce are:

class Item(models.Model):
    label = models.CharField(max_length=100)
    cost = models.IntegerField()

class Activity(models.Model):
    content_type = models.ForeignKey(ContentType, on_delete=models.CASCADE)
    object_pk = models.CharField(max_length=100)
    generic_object = GenericForeignKey(fk_field='object_pk') 

Reference:
PostgreSQL docs on 'serial' type (​https://www.postgresql.org/docs/10/static/datatype-numeric.html#DATATYPE-SERIAL)

[Mariusz Felisiak]

Thanks for the report. It doesn't sound natural to me to cast to AutoField, because if you want to get an integer / bigint, why not use it directly? Nevertheless you can use DatabaseOperations.cast_data_types to fix this easily. This issue probably appears on other back-ends.

[Andrew Standley]

I'm using Cast on generic foreign keys to handle the type coercion in the database, so I'm not coding around a 'known' field-type. I would indeed just use an IntegerField, 'integer' type directly otherwise, and as a temporary solution I'm doing just that for the edge case of AutoField.

Can you elaborate on how I would use DatabaseOperations.cast_data_types to fix this? I'm afraid I'm not familiar with that whole side of things. It (perhaps naively) seems to me that DatabaseOperations is going to have no effect on the Cast operation compilation as Cast selects the db_type through a different route than the one that DatabaseOperations.cast_data_type is used in.
Replying to felixxm:

Thanks for the report. It doesn't sound natural to me to cast to AutoField, because if you want to get an integer / bigint, why not use it directly? Nevertheless you can use DatabaseOperations.cast_data_types to fix this easily. This issue probably appears on other back-ends.

[Mariusz Felisiak]

In Django we defined DatabaseOperations for each back-end. cast_db_type() uses back-end specific dictionary connection.ops.cast_data_types to find data type to use in the Cast() (see ​django/db/models/fields/__init__.py).

[Mariusz Felisiak]

​PR

[GitHub <noreply@…>]

In ac25dd1f:

Fixed #29569 -- Fixed Cast() with AutoField and BigAutoField.