Cast with AutoField results in a 'type does not exist' error in PostgreSQL 10.1

[Andrew Standley]

Hey all,
 I've run across an issue and I'm not sure if what I have is a bug, or
if I've just done something horribly wrong.
I'm hoping one of you can help me work out what it is.

The issue is that `AutoField.db_type` returns 'serial', which is a
"syntactical sugar" (not a true type) in Postgresql and only valid for
creation. This is fine, but `AutoField.cast_db_type` also returns
'serial', which is invalid. More confusingly `AutoField.rel_db_type`
correctly returns 'integer'.

This means that when using `Cast` with AutoField as part of a join a
"psycopg2.ProgrammingError: type "serial" does not exist" error is
thrown by the connection.
Manually 'correcting' for this and using an IntegerField in the query
produces the correct results.

This seems like a bug to me, but I just want to be sure that I've not
missed something, like considerations for other databases, or other uses
of `cast_db_type` that mean it can't return 'integer'.

Full details of the route to this problem are below.

Cheers,
    Andrew

----

I ran into it using django-guardian's object permission implementation,
but have reproduced with the following test case.

Using PostgreSQL 10.1 with the following models:

```
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')
```

I needed to get all Items which have an Activity. I can't use
GenericRelation because I have to be able to get records with Activities
for ANY arbitrary model, and I don't have control over some of the
models. So naively I tried
```
item_ct = ContentType.objects.get_for_model(Item)
Item.objects.filter(pk__in=Activity.objects.filter(content_type=item_ct).values('object_pk'))
```
which, of course, resulted in a type error "psycopg2.ProgrammingError:
operator does not exist: integer = character varying"

So after some research I managed to get the ORM to produce the statement
I wanted using `annotate` and `Cast`
```
item_ct = ContentType.objects.get_for_model(Item)
Item.objects.filter(pk__in=Activity.objects.filter(content_type=item_ct).annotate(casted_pk=Cast('object_pk',
IntegerField())).values('casted_pk'))
```

However when I went to extend this to the general case I ran into the
'type' issue. (model = Item)
```
model_ct = ContentType.objects.get_for_model(model)
pk_field_class = model._meta.pk.__class__
model.objects.filter(pk__in=Activity.objects.filter(content_type=model_ct).annotate(casted_pk=Cast('object_pk',pk_field_class
())).values('casted_pk'))
```
I received the "psycopg2.ProgrammingError: type "serial" does not exist"
error.

On investigation 'SERIAL' in just syntactic sugar over the `INT` type,
which seems to work correctly for the database definition creation.
Item does indeed have the 'id' `AutoField` as an 'integer' type that has
the correct 'autoincrement' default.
```
                                                             Table
"public.test_item"
     Column      |           Type           | Collation | Nullable
|                Default                 | Storage  | Stats target |
Description
-----------------+--------------------------+-----------+----------+----------------------------------------+----------+--------------+-------------
 id              | integer                  |           | not null |
nextval('test_item_id_seq'::regclass) | plain    |              |
 label            | character varying(100)    |           | not null
|                                        | extended |              |
 cost             | integer                  |           | not null
|                                        | plain
```

DefaultConnectionProxy does map AutoField to 'serial', which seems
reasonable given it can only hold one 'type'.
```
>>> connection.data_types
{'BigAutoField': 'bigserial', 'IntegerField': 'integer',
'DurationField': 'interval', 'GenericIPAddressField': 'inet',
'FilePathField': 'varchar(%(max_length)s)', 'TimeField': 'time'mallint',
'OneToOneField': 'integer', 'CharField': 'varchar(%(max_length)s)',
'NullBooleanField': 'boolean', 'TextField': 'text', 'DateTimeField':
'timestamp with time zone', 'FileFith)s)', 'IPAddressField': 'inet',
'AutoField': 'serial', 'PositiveIntegerField': 'integer',
'DecimalField': 'numeric(%(max_digits)s, %(decimal_places)s)',
'UUIDField': 'uuid', 'DateFmallIntegerField': 'smallint', 'BinaryField':
'bytea', 'BooleanField': 'boolean', 'SlugField':
'varchar(%(max_length)s)', 'FloatField': 'double precision',
'BigIntegerField': 'bigint
```

However, it seems pretty clear that when trying to CAST to an AutoField
column you need to CAST to an 'integer' type.

Looking at the implementation of AutoField `rel_db_type` has been
overloaded with:
```
def rel_db_type(self, connection):
        return IntegerField().db_type(connection=connection)
```
but `cast_db_type` has not.

So I'm left wondering why `AutoField.cast_db_type` does not return
'integer' as `AutoField.rel_db_type` does?
Is there some consideration I'm not taking into account, or is this just
a bug? (In which case at least the 'fix' seems straightforward enough)


--
This message has been scanned by E.F.A. Project and is believed to be clean.

[Carlton Gibson]

Hi Andrew, 

Reading the description, it seems like you may have hit a bug. 

Could you possibly put this into an actual test case and open a PR

(plus Trac ticket) with that  assuming the test fails? 

With code in hand it's much easier to assess (and fix). 

Thanks. 

Kind Regards,

Carlton