id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
28371	Cast generates invalid SQL for SQLite, PostgreSQL, and Oracle if max_length isn't provide to CharField	James Doherty	Mariusz Felisiak	"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)' )"	Bug	closed	Database layer (models, ORM)	dev	Normal	fixed		Mariusz Felisiak	Accepted	1	0	0	0	0	0