Paginator UnorderedObjectListWarning on union(all=True) of two sorted queries

[Rich Rauenzahn]

I wonder if this is a case you want to catch and *not* warn about.

In my case, I'm doing this:

haves = MyModel.objects.filter(foreign_relationship=4).order_by('foreign_relationship__value', 'common_key')
havenots = MyModel.objects.exclude(id__in=haves).order_by('common_key')
query = haves.union(havenots, all=True)

I'm using this with a Paginator. The Paginator complains the queries are not ordered, but they actually are (right?) due to the all=True in the union.

Is this a case the warning ought to handle and ignore?

The Django 1.11 source is:

    def _check_object_list_is_ordered(self):
        """
        Warn if self.object_list is unordered (typically a QuerySet).
        """
        ordered = getattr(self.object_list, 'ordered', None)
        if ordered is not None and not ordered:
            obj_list_repr = (
                '{} {}'.format(self.object_list.model, self.object_list.__class__.__name__)
                if hasattr(self.object_list, 'model')
                else '{!r}'.format(self.object_list)
            )
            warnings.warn(
                'Pagination may yield inconsistent results with an unordered '
                'object_list: {}.'.format(obj_list_repr),
                UnorderedObjectListWarning,
                stacklevel=3
            )

Should union(..., all=True) set ordered in the QuerySet? Or maybe should it look and see if all the source queries are ordered and propagate that to the QuerySet union returns?

[Mariusz Felisiak]

I agree that in your case all composed queries are ordered but the final query is not. To avoid this warning you can add order_by() to the final queryset, e.g.

haves = MyModel.objects.filter(foreign_relationship=4).order_by('foreign_relationship__value', 'common_key')
havenots = MyModel.objects.exclude(id__in=haves).order_by('common_key')
query = haves.union(havenots, all=True).order_by('common_key')

[Rich Rauenzahn]

I *can't* add an order_by() because the key I need to sort by isn't in columns union'ed -- and sorting the union by common_key would *unorder* the sort by foreign_relationship__value

[Rich Rauenzahn]

...Actually. I could have sworn I read somewhere that union(all=True) preserves order because it doesn't remove duplicates, but I can't find it again.

Postgres says:

UNION effectively appends the result of query2 to the result of query1 (although there is no guarantee that this is the order in which the rows are actually returned).
Furthermore, it eliminates duplicate rows from its result, in the same way as DISTINCT, unless UNION ALL is used.

In practice it appears to be true, but I don't think it's strictly defined in the spec .. this SO post says it is guaranteed, but presents no citation:

​https://stackoverflow.com/questions/31975969/is-order-preserved-after-union-in-postgresql