Order by sometimes not work in my query

Question

I defined two classes:

class OrderEntryVacancyRenew(OrderEntry):
    ...
    vacancy_id = db.Column(db.Integer, db.ForeignKey('vacancy.id'), nullable=False)
    vacancy = db.relationship('Vacancy')
    remaining = db.Column(db.SmallInteger)

class Vacancy(db.Model):
    id = db.Column(db.Integer, autoincrement=True, primary_key=True)
    renew_at = db.Column(TZDateTime, index=True)

Then I defined the method to refresh OrderEntryVacancyRenew.remaining and Vacancy.renew_at.

def renew_vacancy():
    filters = [
        OrderEntryVacancyRenew.remaining,
        Vacancy.status == 0,
        or_(
            Vacancy.renew_at <= (utcnow() - INTERVAL),
            Vacancy.renew_at.is_(None))
    ]

    renew_vacancies = OrderEntryVacancyRenew.query.options(
        load_only('remaining', 'vacancy_id')
    ).order_by(
        OrderEntryVacancyRenew.id
    ).from_self().group_by(
        OrderEntryVacancyRenew.vacancy_id
    ).join(
        OrderEntryVacancyRenew.vacancy
    ).options(
        contains_eager(OrderEntryVacancyRenew.vacancy).load_only('renew_at')
    ).filter(*filters)

    for entry in renew_vacancies:
        entry.vacancy.renew_at = utcnow()
        entry.remaining -= 1

    db.session.commit()

I wrote the unit test to check renew_vacancy

vacancy1 = Vacancy(id=10000)
vacancy2 = Vacancy(id=10001)
db.session.add_all([vacancy1, vacancy2])
vacancy_renew1 = OrderEntryVacancyRenew(
    vacancy_id=vacancy1.id,
    remaining=24)
# make sure vacancy_renew1.id < vacancy_renew2.id
db.session.add(vacancy_renew1)
db.session.commit()
vacancy_renew2 = OrderEntryVacancyRenew(
    vacancy_id=vacancy1.id,
    remaining=8)
vacancy_renew3 = OrderEntryVacancyRenew(
    vacancy_id=vacancy2.id,
    remaining=42)
db.session.add_all((vacancy_renew2, vacancy_renew3))
db.session.commit()

renew_vacancy()
self.assertEqual(
    (vacancy_renew1.remaining, vacancy_renew2.remaining), (23, 8))

renew_vacancies is order by OrderEntryVacancyRenew id and group by Vacancy id, so I expect it will filter vacancy_renew1 and vacancy_renew3.

I used the following command to run the unit test 100 times:

for i in `seq 1 100`; do python test.py; done

In some rare situations, it filters vacancy_renew2 instead of vacancy_renew1.

Why does it happen that sometimes order by does not work as expected?

I try to print vacancy_renew1.id and vacancy_renew2.id after renew_vacancy.

...
db.session.commit()
renew_vacancy()
print vacancy_renew1.id
print vacancy_renew2.id
self.assertEqual(
    (vacancy_renew1.remaining, vacancy_renew2.remaining), (23, 8))
...

Answer 1

Why does it happen that sometimes ORDER BY does not work as expected?

Given standard SQL, the results of your query are indeterminate, so it is not very valuable to know why it works most of the time and fails rarely. There are two things that make the results vary:

1. Generally you should not rely on the order of rows of a subquery in enclosing queries, even if you apply an ordering. Some database implementations may have additional guarantees, but on others for example optimizers may deem the ORDER BY unnecessary – which MySQL 5.7 and up does, it removes the subquery entirely.

2. Usually databases, such as SQLite and MySQL¹, that allow selecting non-aggregate items that are not functionally dependent on, or are not named in the GROUP BY clause, leave it unspecified from which row in the group the values are taken:

   • SQLite:

     ... Otherwise, it is evaluated against a single arbitrarily chosen row from within the group. If there is more than one non-aggregate expression in the result-set, then all such expressions are evaluated for the same row.

   • MySQL:

     ... In this case, the server is free to choose any value from each group, so unless they are the same, the values chosen are nondeterministic, which is probably not what you want. Furthermore, the selection of values from each group cannot be influenced by adding an ORDER BY clause.

Trying out the query on SQLite failed the assertion on this machine, while on MySQL it passed. This is probably due to implementation of selecting the row from within the group, but you should not rely on such details.

What you seem to be after is a greatest-n-per-group query, or top-1. Not knowing which database you are using here's a somewhat generic way to do just that using an EXISTS subquery expression:

renew_alias = db.aliased(OrderEntryVacancyRenew)

renew_vacancies = db.session.query(OrderEntryVacancyRenew).\
    join(OrderEntryVacancyRenew.vacancy).\
    options(
        load_only('remaining'),
        contains_eager(OrderEntryVacancyRenew.vacancy).load_only('renew_at')).\
    filter(db.not_(db.exists().where(
        db.and_(renew_alias.vacancy_id == OrderEntryVacancyRenew.vacancy_id,
                renew_alias.id < OrderEntryVacancyRenew.id)))).\
    filter(*filters)

This query passes the assertion on both SQLite and MySQL. Alternatively you could replace the EXISTS subquery expression with a LEFT JOIN and IS NULL predicate.

P.s. I suppose you're using some flavour of MySQL and following advice such as this. You should read the commentary on that one as well, since there are many people rightly pointing out the pitfalls. It does not work, at least for MySQL 5.7 and up.

---

¹: Controllable with the ONLY_FULL_GROUP_BY SQL mode setting, enabled by default in MySQL 5.7.5 and up.