Opened 15 months ago

Closed 15 months ago

Last modified 15 months ago

#34858 closed Bug (fixed)

Output field for combined PositiveIntegerField is not properly resolved.

Reported by: Toan Vuong Owned by: Toan Vuong
Component: Database layer (models, ORM) Version: 4.2
Severity: Normal Keywords:
Cc: Simon Charette, Luke Plant Triage Stage: Ready for checkin
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description (last modified by Toan Vuong)

Tested on Django 4.2.4.

My model is defined like this:

class Choice(models.Model):
    choice_text = models.CharField(max_length=200)
    votes = models.IntegerField(default=0)
    arbitrary_num = models.PositiveIntegerField(default=1)
    arbitrary_num2 = models.PositiveIntegerField(default=2)

And this query is failing with Oracle (Tested on 19.3), but not Postgres:

s = Coalesce(F('arbitrary_num') + F('arbitrary_num2'),
                 Value(0, PositiveIntegerField()))
case = Case(
        When(arbitrary_num=1, then=Value(1, PositiveIntegerField())),
        default=s,
        output_field=PositiveIntegerField()
)

qs = Choice.objects.annotate(ss=case)
list(qs)

#qs = Choice.objects.annotate(s=s)
#list(qs)

The error (It's talking about the Coalesce function not having an output_field):

django.core.exceptions.FieldError: Expression contains mixed types: IntegerField, PositiveIntegerField. You must set output_field.

I believe the error is correct, because I think adding two PositiveIntegerField() seem to result in an IntegerField, so we *need* to specify an output_field on Coalesce to tell Django the proper output type to use. My question is that this seem to only throw an error in Oracle, and *not* Postgres. So it seems like the behavior on Postgres is unexpected? Somehow, the Case statement swallows the exception in Postgres and generates correct result. This can be demonstrated by the queryset where the Coalesce function is annotated directly without a case statement -- in this case, both Postgres and Oracle fails with the above error.

Note that this used to work prior to 4.2 (we upgraded from 3.x, where this used to work), although again I believe it was incorrect behavior and an output_field *should* be specified.

On the other hand, it's a little strange that adding two PositiveIntegerField() results in an output type of IntegerField(), so perhaps that's a bug as well...

Change History (11)

comment:1 by Toan Vuong, 15 months ago

Description: modified (diff)

comment:2 by Toan Vuong, 15 months ago

Description: modified (diff)

comment:3 by Mariusz Felisiak, 15 months ago

Cc: Simon Charette Luke Plant added
Component: UncategorizedDatabase layer (models, ORM)
Summary: Bug in output field handlingOutput field for combined PositiveIntegerField is not properly resolved.
Triage Stage: UnreviewedAccepted
Type: UncategorizedBug

Thanks for the ticket. This is not strictly related with Oracle, the main issue is that output_field is not properly resolved for a combination of PositiveIntegerField. Coalesce() on Oracle checks the output_field of arguments and crashes. We should be able to fix this but prioritize PositiveIntegerField, e.g.

  • django/db/models/expressions.py

    diff --git a/django/db/models/expressions.py b/django/db/models/expressions.py
    index 4ea179ecde..54edaa611c 100644
    a b class Expression(BaseExpression, Combinable):  
    512512
    513513_connector_combinations = [
    514514    # Numeric operations - operands of same type.
     515    {
     516        # PositiveIntegerField should take precedence over IntegerField.
     517        connector: [
     518            (
     519                fields.PositiveIntegerField,
     520                fields.PositiveIntegerField,
     521                fields.PositiveIntegerField,
     522            ),
     523        ]
     524        for connector in (
     525            Combinable.ADD,
     526            Combinable.MUL,
     527            Combinable.DIV,
     528            Combinable.MOD,
     529            Combinable.POW,
     530        )
     531    },
    515532    {
    516533        connector: [
    517534            (fields.IntegerField, fields.IntegerField, fields.IntegerField),

Would you like to prepare a patch (via GitHub PR)? a regression test is required.

Regression in 40b8a6174f001a310aa33f7880db0efeeb04d4c4 (#33397).

comment:4 by Toan Vuong, 15 months ago

Thanks for the reply! I can work on a PR for the PositiveIntegerField change following the patch you wrote.

However, regarding your statement:

Coalesce() on Oracle checks the output_field of arguments and crashes

This seems incorrect, because using Coalesce alone would crash on both Postgres and Oracle. The weird thing is if I have a Case on top of the "bad" Coalesce, then only Oracle crashes. The Postgres queryset executes successfully. So it seems like Case behaves differently between the two, and probably incorrectly swallows the error thrown by Coalesce in the Postgres scenario which also seems like a bug?

comment:5 by Toan Vuong, 15 months ago

Owner: changed from nobody to Toan Vuong
Status: newassigned

comment:6 by Toan Vuong, 15 months ago

Has patch: set

Opened a PR:
Edit: https://github.com/django/django/pull/17299 (Merging to main)

Still wondering about whether we need to do something about Case between Postgres vs. Oracle, because the behavior still seems inconsistent there.

Edit: Also not sure what the process is to open PRs for other branches(?). Seems like I should also merge this to main and stable/5.0.x?
Edit 2: Per the first PR's comment, I recreated a PR for main only.

Last edited 15 months ago by Toan Vuong (previous) (diff)

in reply to:  6 comment:7 by Mariusz Felisiak, 15 months ago

Replying to Toan Vuong:

Still wondering about whether we need to do something about Case between Postgres vs. Oracle, because the behavior still seems inconsistent there.

Coalesce() on Oracle explicitly checks the output_field when it's compiled (for Cast.default), on other database the output_field is not used when Coalesce() is compiled so it doesn't crash. The crash on Oracle is a side-effect of the current implementation, IMO, there is no need to change it.

Edit: Also not sure what the process is to open PRs for other branches(?). Seems like I should also merge this to main and stable/5.0.x?

Mergers will backport if needed.

comment:8 by Mariusz Felisiak, 15 months ago

Triage Stage: AcceptedReady for checkin

comment:9 by Mariusz Felisiak <felisiak.mariusz@…>, 15 months ago

Resolution: fixed
Status: assignedclosed

In 4de31ec:

Fixed #34858 -- Corrected resolving output_field for PositiveIntegerField.

Regression in 40b8a6174f001a310aa33f7880db0efeeb04d4c4.

comment:10 by Mariusz Felisiak <felisiak.mariusz@…>, 15 months ago

In dcd3a03:

[5.0.x] Fixed #34858 -- Corrected resolving output_field for PositiveIntegerField.

Regression in 40b8a6174f001a310aa33f7880db0efeeb04d4c4.

Backport of 4de31ec680df062e5964b630f1b881ead5004e15 from main

comment:11 by Toan Vuong, 15 months ago

Coalesce() on Oracle ​explicitly checks the output_field when it's compiled (for Cast.default), on other database the output_field is not used when Coalesce() is compiled so it doesn't crash. The crash on Oracle is a side-effect of the current implementation, IMO, there is no need to change it.

Thanks, this is what I was missing. In our local copy of Django I essentially added a try/catch on that as_oracle function which worked, but I didn't realize that was only called in some cases but not others.

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