django.db.utils.DataError: integer out of range on annotated field

Using Django Using the ORM
1 
class Order(models.Model):
    deal_quantity = models.IntegerField()
    
    product = models.ForeignKey(
        Product,
        on_delete=models.PROTECT,
    )

class Product(models.Model):
    product_quantity = models.IntegerField()

low_orders = Order.objects.annotate(
    quantity=ExpressionWrapper(
        F("deal_quantity") * F("product__product_quantity"), 
        output_field=BigIntegerField()
    )
).filter(
    quantity__lte=35983851
)

I am getting a django.db.utils.DataError: integer out of range error
On the above, deal_quantity is between 1-20000000 and product_quantity is between 1-20000.
If I change those two fields to BigIntegerField then the query runs succesfully.

2

I’m not sure I understand the purpose of this post. Is there a question or issue that you are seeking assistance with?

From what I’m reading here, it appears you’ve identified an issue you had been having, and have found the solution for it.

If this is a case where the solution that you have found is not appropriate or useful in your case, we’ll need a little more information, starting with knowing what database you’re using here.

This expression is being calculated in the database - so the interpretation of these values adhere to how the database would handle the data types. You may need to Cast the individual values to what the database considers to be a “Big Integer” as part of the expression before performing the multiplication - but it does depend on the database.

3

Sorry, I should have been more explicit with the question.

I was under the impression that this should be doing the casting:

ExpressionWrapper(
        F("deal_quantity") * F("product__product_quantity"), 
        output_field=BigIntegerField()
    )

Do I need to explicitly cast as well? I would like to keep the initial fields as IntegerFields if possible. I am using postgres.

4

Replacing this section with:

Cast(
        F("deal_quantity") * F("product__product_quantity"), 
        output_field=BigIntegerField()
    )

Doesnt appear to help.

Is the only way to use BigIntegerFields initially?

5

The output_field is going to cast the result of the multiplication expression. This implies that the multiplication is going to occur before the Cast can happen.

You would need to cast the two values before doing the multiplication, so that the database performs the operation on two bigint fields.

You’re looking for something like:

quantity = ExpressionWrapper(
    Cast('deal_quantity', output_field=BigIntegerField()) *
    Cast('product__product_quantity', output_field=BigIntegerField())
)

(I think I’ve got the parens all matched up here.)

Since the result of a multiplication between two bigints is a bigint, I don’t think you need to cast the result of that expression.

6

Specifying output_field doesn’t do an implicit cast.

What it does is tell the ORM what lookups and transforms can be used against further references to the annotation (e.g. through filter(quantity__lte)) and how the data returned from the database should be turned into a Python objects through Field.from_db. It’s also necessary when performing arithmetic against mixed data types but that’s something you likely already figured out if you used ExpressionWrapper.

If you get a DateError that’s your database telling you that you need explicit casts and the way to do that is by using Cast.

TL;DR ExpressionWrapper doesn’t perform implicit casts.

7

Ah, yes, casting the original fields works perfectly. Thanks very much!