Opened 5 years ago

Closed 5 years ago

#28371 closed Bug (fixed)

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

Reported by: James Doherty Owned by: Mariusz Felisiak
Component: Database layer (models, ORM) Version: dev
Severity: Normal Keywords:
Cc: Mariusz Felisiak Triage Stage: Accepted
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

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)' )

Change History (8)

comment:1 Changed 5 years ago by Mariusz Felisiak

Cc: Mariusz Felisiak added
Version: 1.11master

comment:2 Changed 5 years ago by 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.

comment:3 Changed 5 years ago by Tim Graham

Summary: Cast generating invalid SQL for SQLite and PostgreSQLCast generates invalid SQL for SQLite and PostgreSQL if max_length isn't provide to CharField
Triage Stage: UnreviewedAccepted

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.

comment:4 Changed 5 years ago by Mariusz Felisiak

Owner: changed from nobody to Mariusz Felisiak
Status: newassigned

comment:5 Changed 5 years ago by Mariusz Felisiak

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

comment:6 Changed 5 years ago by Mariusz Felisiak

Has patch: set

comment:7 Changed 5 years ago by Tim Graham <timograham@…>

In 8e41373:

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

A small refactor ahead of refs #28371.

comment:8 Changed 5 years ago by GitHub <noreply@…>

Resolution: fixed
Status: assignedclosed

In b61d5b1:

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

Thanks Tim Graham for the review.

Note: See TracTickets for help on using tickets.
Back to Top