Cast generates invalid SQL for SQLite, PostgreSQL, and Oracle if max_length isn't provide to CharField

[James Doherty]

I have a nullable IntegerField. When trying to cast it to a char, SQLite and PostgreSQL error due to invalid SQL:

class Numbers(models.Model):
    number = models.IntegerField(null=True)

The following code causes the error.

Numbers.objects.annotate(as_string=Cast('number', CharField()))

PostgreSQL

ProgrammingError: syntax error at or near "None"
LINE 1: ...mbers"."number", "demo_numbers"."number"::varchar(None) AS "...

SELECT "demo_numbers"."id", "demo_numbers"."number", "demo_numbers"."number"::varchar(None) AS "as_string" FROM "demo_numbers"

Removing the '(None)' from the SQL makes this work.

SQLite

OperationalError: near "None": syntax error

SELECT "demo_numbers"."id", "demo_numbers"."number", CAST("demo_numbers"."number" AS varchar(None)) AS "as_string" FROM "demo_numbers"

According to the SQLite documentation, varchar is not a valid type for SQLite: ​http://www.sqlite.org/lang_expr.html#castexpr

Changing the SQL to 'CAST("demo_numbers"."number" AS TEXT)' succeeds. It's worth noting that it is possible to give SQLite an invalid cast type that doesn't cause an error (eg, try 'CAST("demo_numbers"."number" AS BOGUS)' )

[Mariusz Felisiak]

I confirm that if you use Cast with CharField() without max_length argument then SQL is invalid. On the other hand I'm not convinced that it's a bug because max_length is required for CharField's.

[Tim Graham]

For Cast, I'm not sure if max_length matters. If not too complicated, I think it wouldn't be unreasonable to allow this to work -- or at least a more helpful error message should be provided.

[Mariusz Felisiak]

​PR

[Tim Graham <timograham@…>]

In 8e41373:

Allowed database backends to specify data types for Cast().

A small refactor ahead of refs #28371.

[GitHub <noreply@…>]

In b61d5b1:

Fixed #28371 -- Fixed Cast() with CharField if the max_length argument isn't provided.

Thanks Tim Graham for the review.