Running last() on a related object with a F() ordering mutates model ordering.

[Mikail]

Hi,

I have ran into a weird issue when working with a O2M relation, where one would use the meta field ordering with F(...).

We can take this following code to illustrate as an example:

from django.db import models
from django.db.models import F


class Item(models.Model):
    pass


class ItemValue(models.Model):
    sort_order = models.IntegerField(unique=True, null=True)
    item = models.ForeignKey(Item, on_delete=models.CASCADE, related_name="items")

    class Meta:
        ordering = (F("sort_order").asc(nulls_last=True), "id")

In Item we have a relation to the items. The most important part here, is the ordering with a F(...).

Now, if another part of the code (a view, a test, ...) runs last() on the items, it will trigger some kind of switch in the ORM, which will make it always run F(...) in DESC.

Here is an example:

item = Item.objects.create()
ItemValue.objects.bulk_create([
    ItemValue(item=self.item, sort_order=0),
    ItemValue(item=self.item),
    ItemValue(item=self.item),
])

sort_order = item.items.all()[0].sort_order  # is 0

item.items.last()

sort_order = item.items.all()[0].sort_order # is None

item.items.last()

sort_order = item.items.all()[0].sort_order # is 0

The biggest issue with this, is if this is ran into a view or a test, the ordering will always be reversed until we restart the django worker, or if we run item.items.last() again to flip the switch.

Here is a full example:

# models
from django.db import models
from django.db.models import F


class Item(models.Model):
    pass


class ItemValue(models.Model):
    sort_order = models.IntegerField(unique=True, null=True)
    item = models.ForeignKey(Item, on_delete=models.CASCADE, related_name="items")

    class Meta:
        ordering = (F("sort_order").asc(nulls_last=True), "id")


# test
class TestItemValue(TestCase):
    def setUp(self) -> None:
        self.item = Item.objects.create()
        ItemValue.objects.bulk_create([
            ItemValue(item=self.item, sort_order=0),
            ItemValue(item=self.item),
            ItemValue(item=self.item),
        ])

    def test_valid(self):
        self.assertEqual(self.item.items.all()[0].sort_order, 0)

    def test_switch(self):
        self.item.items.last()

        with CaptureQueriesContext(connection) as ctx:
            sort_order = self.item.items.all()[0].sort_order
            sql = ctx[0]['sql']
            order_by = sql[sql.find("ORDER BY"):]
            self.assertEqual(sort_order, 0, order_by)

This is an example of the switch in action during a .all():

[Mariusz Felisiak]

Thanks for this report, it is a duplicate of #30501 because last() uses QuerySet.reverse(). Patch will appear in Django 3.0 (see f8b8b00f0197e52f7a0986fae51462be174dbaea).