Add optional error-on-integer-overflow setting for QuerySet filters

[bubusuke]

(English is not my native language.)

Code of Conduct

• I agree to follow Django's Code of Conduct

Feature Description

Adds an opt-in Django setting that raises a new EmptyResultSetDueToIntegerOverflow exception whenever arithmetic in a QuerySet filter would overflow an integer column, instead of silently returning an empty result.
This surfaces hidden overflows and simplifies debugging and migrations from Django 4.x to 5.x.

Problem

• Problem

  • In Django 5.0 the behaviour for arithmetic that overflows an integer column changed: a filter that would overflow now always returns an empty QuerySet instead of being transparently cast to a wider integer type (the historical behaviour on most back-ends).
  • It makes migrations from Django 4.x harder to verify—tests may “pass” yet silently return fewer (or zero) rows. It is also difficult for developers on 5.x to notice when an overflow is happening because the condition is swallowed inside the ORM.

• Benefits

  • Safe migrations – Projects upgrading from 4.x can enable the flag in CI to surface every place where arithmetic now truncates results.

  • Debuggability – Developers on 5.x who run into an unexpected empty queryset get an immediate stack-trace pointing at the offending expression.

  • Backwards-compatible – Opt-in toggle preserves the 5.0 behaviour by default; no existing code breaks unless the project explicitly asks for stricter checking.

Request or proposal

proposal

Additional Details

Add a new setting ERROR_IF_INTEGER_OVERFLOW_IN_FILTER_QUERYSET to switch behavior to raise EmptyResultSet or raising EmptyResultSetDueToIntegerOverflow exception if the QuerySet filter's operation is about to overflow an integer column. (See detailed flow in the implementation suggestions).

Implementation Suggestions

• Add new parameter.

# settings.py
ERROR_IF_INTEGER_OVERFLOW_IN_FILTER_QUERYSET = True  # default: False

• Add new exception

class EmptyResultSetDueToIntegerOverflow(Exception):
    """Raised when an integer overflow is detected while building a WHERE
    clause.  Subclasses plain Exception so it is *not* caught by existing
    EmptyResultSet handlers."""

• Raise it at the source

In django.db.models.lookups.IntegerFieldOverflow (or equivalent part of the lookup machinery), raise EmptyResultSetDueToIntegerOverflow instead of EmptyResultSet.

• Handle it centrally

In django.db.models.sql.where.WhereNode.as_sql() wrap the call to compiler.compile(child)

try:
    ...
except EmptyResultSetDueToIntegerOverflow:
    if settings.ERROR_IF_INTEGER_OVERFLOW_IN_FILTER_QUERYSET:
        raise
    empty_needed -= 1  # current silent-empty logic

[github-actions]

Thank you bubusuke for sharing your idea! We have a lot of them so please be patient. You can see the current queue here.

Community instructions

For commenters, please use the emoji reactions on the issue to express support, and/or concern easily. Please use the comments to ask questions or contribute knowledge about the idea. It is unhelpful to post comments of "I'd love this" or "What's the state of this?"

Reaction Guide

• 👍 This is something I would use
• 👎 This is something that would cause problems for me or Django
• 😕 I’m indifferent to this
• 🎉 This is an easy win

[LilyAcorn]

Is the behaviour change between Django 4.2 and 5.0 a deliberate change or an overlooked regression? Can you link to any relevant tickets from Trac?

[bubusuke]

This change in behavior appears to be intentional. It appears in the “backwards-incompatible changes” section of the Django 5.0 release notes and was introduced through work tracked in Trac ticket #27397 or #35147 and merged via GitHub PR #16599.

But the following queries began to fail. I think this was unintended.
This code works, at least in Django 4.X.

def test_integer_overflow():
    num = 10000000000 # Value that overflows if integer
    qs = ContentType.objects.alias(  # For ease of confirmation, ContentType.objects.alias is used here. Please assume that you are originally using the business model.
            after=Value(100000000000, output_field=BigIntegerField()),  # Think of it as a biginteger value held in the model.
            before=Value(9000, output_field=BigIntegerField()),  # Think of it as a biginteger value held in the model.
            diff=F("after") - F("before"),
        ).filter(diff__gt=num)
    assert qs.count() > 0  # Failed because count() returns 0 value due to EmptyResultSet exception

Success if the following. Such changes should be made to account for overflow. We consider this debug difficult.

def test_not_integer_overflow():
    num = 10000000000 # Value that overflows if integer
    qs = ContentType.objects.alias(  # For ease of confirmation, ContentType.objects.alias is used here. Please assume that you are originally using the business model.
            after=Value(100000000000, output_field=BigIntegerField()),  # Think of it as a biginteger value held in the model.
            before=Value(9000, output_field=BigIntegerField()),  # Think of it as a biginteger value held in the model.
            diff=ExpressionWrapper(F("after") - F("before"), output_field=BigIntegerField()),  # <- CHANGE HERE!!
        ).filter(diff__gt=num)
    assert qs.count() > 0 # Success