[Django] #28371: Cast generating invalid SQL for SQLite and PostgreSQL

[Django]

#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 django-updates+unsubscr...@googlegroups.com.
To post to this group, send email to django-updates@googlegroups.com.
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.