[Django] #28371: Cast generating invalid SQL for SQLite and PostgreSQL
Django
-------------------------------------+-------------------------------------
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.
Django
-------------------------------------+-------------------------------------
Type: Bug | Status: new
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* cc: felixxm (added)
* version: 1.11 => master
--
Ticket URL: <https://code.djangoproject.com/ticket/28371#comment:1>
Django
-------------------------------------+-------------------------------------
Type: Bug | Status: new
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage:
| Unreviewed
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by felixxm):
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.
--
Ticket URL: <https://code.djangoproject.com/ticket/28371#comment:2>
Django
provide to CharField
-------------------------------------+-------------------------------------
Reporter: jamesdoherty | Owner: nobody
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* stage: Unreviewed => Accepted
Comment:
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.
--
Ticket URL: <https://code.djangoproject.com/ticket/28371#comment:3>
Django
provide to CharField
-------------------------------------+-------------------------------------
Type: Bug | Status: assigned
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* status: new => assigned
* owner: nobody => felixxm
--
Ticket URL: <https://code.djangoproject.com/ticket/28371#comment:4>
Django
isn't provide to CharField
-------------------------------------+-------------------------------------
Reporter: James Doherty | Owner: felixxm
Type: Bug | Status: assigned
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
--
Ticket URL: <https://code.djangoproject.com/ticket/28371#comment:5>
Django
isn't provide to CharField
-------------------------------------+-------------------------------------
Reporter: James Doherty | Owner: felixxm
Type: Bug | Status: assigned
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* has_patch: 0 => 1
Comment:
[https://github.com/django/django/pull/8758 PR]
--
Ticket URL: <https://code.djangoproject.com/ticket/28371#comment:6>
Django
isn't provide to CharField
-------------------------------------+-------------------------------------
Reporter: James Doherty | Owner: felixxm
Type: Bug | Status: assigned
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Tim Graham <timograham@…>):
In [changeset:"8e41373c81cbd914d84b362ca4c85d3ed4fcff43" 8e41373]:
{{{
#!CommitTicketReference repository=""
revision="8e41373c81cbd914d84b362ca4c85d3ed4fcff43"
Allowed database backends to specify data types for Cast().
A small refactor ahead of refs #28371.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/28371#comment:7>
Django
isn't provide to CharField
-------------------------------------+-------------------------------------
Reporter: James Doherty | Owner: felixxm
Component: Database layer | Version: master
(models, ORM) |
Keywords: | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* status: assigned => closed
* resolution: => fixed
Comment:
In [changeset:"b61d5b1991e2ca2c3450ccc334224f3d51da39dc" b61d5b1]:
{{{
#!CommitTicketReference repository=""
revision="b61d5b1991e2ca2c3450ccc334224f3d51da39dc"
Fixed #28371 -- Fixed Cast() with CharField if the max_length argument
isn't provided.
Thanks Tim Graham for the review.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/28371#comment:8>