#28371: Cast generating invalid SQL for SQLite and PostgreSQL
-------------------------------------+-------------------------------------
Reporter: | Owner: nobody
jamesdoherty |
Type: Bug | Status: new
Component: Database | Version: 1.11
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 |
-------------------------------------+-------------------------------------
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)' )
--
Ticket URL: <https://code.djangoproject.com/ticket/28371>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/055.e45176d8a8c0896738b390e32ca3f938%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
