Opened 4 years ago

Closed 4 years ago

Last modified 4 years ago

#32525 closed New feature (wontfix)

Class Cast throws DataError when column contains numeric string or alphabet string in postgres

Reported by: kygoh Owned by: nobody
Component: Database layer (models, ORM) Version: 3.0
Severity: Normal Keywords: QuerySet.extra
Cc: Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

Using the following queryset

InspectionDetails.objects.annotate(
  itemval = Cast('item_value', output_field=FloatField(default=0.0))
).filter(
  Q(itemval__lt=216) | Q(itemval__gt=253)
)

will generate the following SQL statement for postgres:

SELECT
 "inspectv1_inspectiondetails"."id", "inspectv1_inspectiondetails"."master_id_id", "inspectv1_inspectiondetails"."category_id_id",
 "inspectv1_inspectiondetails"."item_id_id", "inspectv1_inspectiondetails"."item_value", "inspectv1_inspectiondetails"."item_image",
 ("inspectv1_inspectiondetails"."item_value")::double precision AS "itemval"
FROM
 "inspectv1_inspectiondetails"
WHERE
 (("inspectv1_inspectiondetails"."item_value")::double precision < 216.0 OR ("inspectv1_inspectiondetails"."item_value")::double precision > 253.0)

However, item_value may store numeric string as well as 'true' which will cause:

django.db.utils.DataError: invalid input syntax for type double precision: "true"

To overcome the problem, extra() QuerySet modifier was used as follows:

q = InspectionDetails.objects.extra(where=['cast_to_numeric(item_value) < 216 or cast_to_numeric(item_value) > 253'])

where cast_to_numeric is the following postgres function (source: https://stackoverflow.com/a/10307443):

create or replace function cast_to_numeric(text) returns numeric as $$
begin
    -- Note the double casting to avoid infinite recursion.
    return cast($1::varchar as numeric);
exception
    when invalid_text_representation then
        return 0;
end;
$$ language plpgsql immutable;

I hope this edge case can be considered in the QuerySet API enhancement to allow removing extra().

Change History (6)

comment:1 by Mariusz Felisiak, 4 years ago

Resolution: wontfix
Status: newclosed

Thanks for the ticket, however it's an issue in your data not in Django. Moreover, it's unexpected to prevent/ignore data errors.

comment:2 by Mariusz Felisiak, 4 years ago

Component: UncategorizedDatabase layer (models, ORM)

in reply to:  1 comment:3 by kygoh, 4 years ago

Resolution: wontfix
Status: closednew

Replying to Mariusz Felisiak:

Thanks for the ticket, however it's an issue in your data not in Django. Moreover, it's unexpected to prevent/ignore data errors.

Unfortunately the item_value column by design allows numeric as well as non-numeric string data. My request is not to prevent/ignore data errors but to not deprecate extra() unless there's an alternative to handle such cases since Django documentation states:

This is an old API that we aim to deprecate at some point in the future

comment:4 by Mariusz Felisiak, 4 years ago

Resolution: wontfix
Status: newclosed

Please don't reopen closed tickets. QuerySet.extra() will be deprecated in the future, however we don't have plans to do this in Django 4.0.

comment:5 by Mariusz Felisiak, 4 years ago

Also, you don't need to use extra() you can use a custom function (if it already exists in your db):

class CastToNumeric(Func):
    function = 'cast_to_numeric'
    output_field = FloatField()

in reply to:  5 comment:6 by kygoh, 4 years ago

Replying to Mariusz Felisiak:

Also, you don't need to use extra() you can use a custom function (if it already exists in your db):

class CastToNumeric(Func):
    function = 'cast_to_numeric'
    output_field = FloatField()

Pardon my ignorance for reopening closed tickets. This suggestion is indeed of great help. Thank you.

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