Bug in Django 5.2 when joining the same table twice and using order_by and "distinct on"

Using Django Using the ORM
1

This is somewhat of a wild ride and I have not yet reached the end, but I’m posting now in case you guys know of an existing issue related to this and can shortcut me to an answer. I discovered this yesterday and worked late (until about 8:30pm) debugging with ChatGPT’s help. I believe I may have encountered an edge case bug in Django 5.2’s handling of order_by/distinct when the same table is joined twice on 2 different paths, which google’s AI seems to corroborate.

It’s a long story, but I’m hoping this high level description might trigger some recognition by you guys that might allow you to shortcut me to an answer before I waste more time debugging and generating a toy example and bug report.

We upgraded to 5.2 a couple weeks ago, initially with only 1 minor tweak involving a new behavior. However, yesterday, I discovered some regression tests that weren’t running due to a missing init file. When I added the init file and the tests ran, I started seeing errors from Postgres about order by and distinct fields being different (but they weren’t and Django wasn’t adding any automatically from custom orderings). The queryset.query looked good. I couldn’t find any problem. I downgraded temporarily to 4.2, and the tests all pass! I switched back to 5.2 and through debugging, I eventually learned that if I commented out the custom Meta.orderings of 2 (linked) tables that are joined twice on 2 different paths, the errors go away and the tests pass.

Both ChatGPT and Google’s AI say it’s a bug in the ORM when handling this specific edge case, saying that the ORM is mishandling the aliases in this specific scenario. I have not yet searched to see if there is an existing bug report on this or if it is potentially fixed in a later Django version.

I’m happy to eventually try and take the time to confirm my preliminary results, generate a toy, and create a bug report, but RN I’m on a really tight schedule. Google’s AI linked in my first paragraph has a couple suggested work-arounds, but the code I’m dealing with is generic, so it’s not a straightforward thing to do. The most promising suggestion seems to be to create aliases manually.

I’m hoping that my high level description rings some bells with you guys that this is a known and solved issue and that there may exist a bug fix in a later Django version that would make solving this issue a simple thing?

2

Hello @hepcat72,

I tried to infer a set of models that would reproduce the problem you described from your provided link but couldn’t.

Can you provide an exact set of models and associated queries that reproduce the regression (pass against Django 4.2 and fail on 5.2)?

There have been quite a few changes to how implicit ordering is handled between the two versions so it’s almost impossible to pinpoint the cause of your problem without a way to systematically reproduce the problem. In other words, your high level description is not enough for us to help you.

My immediate reaction went you mentioned select, order_by, and distinct was to blame SQLCompiler.get_extra_select but it hasn’t changed since 3.1.

3

Thanks for the quick response. It would take me quite some time to boil down my very complex query to something digestible. It’s time I don’t have ATM, unfortunately. Maybe it would help if I shared my ChatGPT thread? Let me see if I can find something in the chat prompt history so you don’t have to go through all of it…

4

This is the ORM code that causes the Postgres error:

            resultsqs = (
                res.order_by(*all_distinct_fields)
                .distinct(*all_distinct_fields)
                .values_list(*all_fields)
            )

This is all_dicstinct_fields:

This is what's in all_distinct_fields: `['name', 'pk', 'labels__peak_group__name', 'labels__element', 'labels__pk', 'msrun_sample__sample__animal__infusate__name', 'msrun_sample__sample__animal__infusate__tracer_links__tracer__name', 'msrun_sample__sample__animal__infusate__tracer_links__concentration', 'msrun_sample__sample__animal__infusate__tracer_links__pk', 'msrun_sample__sample__animal__infusate__tracers__name', 'msrun_sample__sample__animal__infusate__tracers__pk', 'msrun_sample__sample__animal__infusate__tracers__compound__name', 'msrun_sample__sample__animal__infusate__tracers__compound__pk', 'compounds__name', 'compounds__pk', 'compounds__synonyms__compound__name', 'compounds__synonyms__name', 'compounds__synonyms__pk', 'msrun_sample__sample__animal__studies__name', 'msrun_sample__sample__animal__studies__pk', 'msrun_sample__sample__msrun_samples__ms_data_file__filename', 'msrun_sample__sample__msrun_samples__ms_data_file__data_type__name', 'msrun_sample__sample__msrun_samples__ms_data_file__data_format__name', 'msrun_sample__sample__msrun_samples__ms_data_file__imported_timestamp', 'msrun_sample__sample__msrun_samples__ms_data_file__pk', 'msrun_sample__sample__msrun_samples__ms_raw_file__filename', 'msrun_sample__sample__msrun_samples__ms_raw_file__data_type__name', 'msrun_sample__sample__msrun_samples__ms_raw_file__data_format__name', 'msrun_sample__sample__msrun_samples__ms_raw_file__imported_timestamp', 'msrun_sample__sample__msrun_samples__ms_raw_file__pk']`

This is all_fields:

all_fields: ['name', 'pk', 'labels__peak_group__name', 'labels__element', 'labels__pk', 'msrun_sample__sample__animal__infusate__name', 'msrun_sample__sample__animal__infusate__tracer_links__tracer__name', 'msrun_sample__sample__animal__infusate__tracer_links__concentration', 'msrun_sample__sample__animal__infusate__tracer_links__pk', 'msrun_sample__sample__animal__infusate__tracers__name', 'msrun_sample__sample__animal__infusate__tracers__pk', 'msrun_sample__sample__animal__infusate__tracers__compound__name', 'msrun_sample__sample__animal__infusate__tracers__compound__pk', 'compounds__name', 'compounds__pk', 'compounds__synonyms__compound__name', 'compounds__synonyms__name', 'compounds__synonyms__pk', 'msrun_sample__sample__animal__studies__name', 'msrun_sample__sample__animal__studies__pk', 'msrun_sample__sample__msrun_samples__ms_data_file__filename', 'msrun_sample__sample__msrun_samples__ms_data_file__data_type__name', 'msrun_sample__sample__msrun_samples__ms_data_file__data_format__name', 'msrun_sample__sample__msrun_samples__ms_data_file__imported_timestamp', 'msrun_sample__sample__msrun_samples__ms_data_file__pk', 'msrun_sample__sample__msrun_samples__ms_raw_file__filename', 'msrun_sample__sample__msrun_samples__ms_raw_file__data_type__name', 'msrun_sample__sample__msrun_samples__ms_raw_file__data_format__name', 'msrun_sample__sample__msrun_samples__ms_raw_file__imported_timestamp', 'msrun_sample__sample__msrun_samples__ms_raw_file__pk', 'msrun_sample__sample__animal__name', 'peak_data__labels__element', 'compounds__name', 'msrun_sample__sample__name', 'msrun_sample__sample__tissue__name', 'msrun_sample__sample__animal__infusate__tracers__compound__name', 'msrun_sample__sample__animal__studies__name', 'msrun_sample__sample__animal__feeding_status', 'msrun_sample__sample__animal__infusion_rate', 'msrun_sample__sample__animal__infusate__tracers__compound__name', 'msrun_sample__sample__animal__infusate__tracer_links__concentration']

This might be the most illustrative prompt, after having added some debug prints that ChatGPT suggested:

I commented out the ordering in ArchiveFile and still get an error:

```
SQL: SELECT DISTINCT ON ("DataRepo_peakgroup"."name", "DataRepo_peakgroup"."id", T15."name", "DataRepo_peakgrouplabel"."element", "DataRepo_peakgrouplabel"."id", "DataRepo_infusate"."name", "DataRepo_tracer"."name", "DataRepo_infusatetracer"."concentration", "DataRepo_infusatetracer"."id", "DataRepo_tracer"."name", "DataRepo_infusatetracer"."tracer_id", T11."name", "DataRepo_tracer"."compound_id", "DataRepo_compound"."name", "DataRepo_peakgroup_compounds"."compound_id", T17."name", "DataRepo_compoundsynonym"."name", "DataRepo_compoundsynonym"."name", "DataRepo_study"."name", "DataRepo_animal_studies"."study_id", T18."ms_data_file_id", T18."ms_raw_file_id") "DataRepo_peakgroup"."name" AS "name", "DataRepo_peakgroup"."id" AS "pk", T15."name" AS "labels__peak_group__name", "DataRepo_peakgrouplabel"."element" AS "labels__element", "DataRepo_peakgrouplabel"."id" AS "labels__pk", "DataRepo_infusate"."name" AS "msrun_sample__sample__animal__infusate__name", "DataRepo_tracer"."name" AS "msrun_sample__sample__animal__infusate__tracer_links__tracer__name", "DataRepo_infusatetracer"."concentration" AS "msrun_sample__sample__animal__infusate__tracer_links__concentration", "DataRepo_infusatetracer"."id" AS "msrun_sample__sample__animal__infusate__tracer_links__pk", "DataRepo_tracer"."name" AS "msrun_sample__sample__animal__infusate__tracers__name", "DataRepo_infusatetracer"."tracer_id" AS "msrun_sample__sample__animal__infusate__tracers__pk", T11."name" AS "msrun_sample__sample__animal__infusate__tracers__compound__name", "DataRepo_tracer"."compound_id" AS "msrun_sample__sample__animal__infusate__tracers__compound__pk", "DataRepo_compound"."name" AS "compounds__name", "DataRepo_peakgroup_compounds"."compound_id" AS "compounds__pk", T17."name" AS "compounds__synonyms__compound__name", "DataRepo_compoundsynonym"."name" AS "compounds__synonyms__name", "DataRepo_compoundsynonym"."name" AS "compounds__synonyms__pk", "DataRepo_study"."name" AS "msrun_sample__sample__animal__studies__name", "DataRepo_animal_studies"."study_id" AS "msrun_sample__sample__animal__studies__pk", T18."ms_data_file_id" AS "msrun_sample__sample__msrun_samples__ms_data_file__pk", T18."ms_raw_file_id" AS "msrun_sample__sample__msrun_samples__ms_raw_file__pk", "DataRepo_animal"."name" AS "msrun_sample__sample__animal__name", "DataRepo_peakdatalabel"."element" AS "peak_data__labels__element", "DataRepo_compound"."name" AS "compounds__name1", "DataRepo_sample"."name" AS "msrun_sample__sample__name", "DataRepo_tissue"."name" AS "msrun_sample__sample__tissue__name", T11."name" AS "msrun_sample__sample__animal__infusate__tracers__compound__name1", "DataRepo_study"."name" AS "msrun_sample__sample__animal__studies__name1", "DataRepo_animal"."feeding_status" AS "msrun_sample__sample__animal__feeding_status", "DataRepo_animal"."infusion_rate" AS "msrun_sample__sample__animal__infusion_rate", T11."name" AS "msrun_sample__sample__animal__infusate__tracers__compound__name2", "DataRepo_infusatetracer"."concentration" AS "msrun_sample__sample__animal__infusate__tracer_links__concentration2" FROM "DataRepo_peakgroup" INNER JOIN "DataRepo_msrunsample" ON ("DataRepo_peakgroup"."msrun_sample_id" = "DataRepo_msrunsample"."id") INNER JOIN "DataRepo_sample" ON ("DataRepo_msrunsample"."sample_id" = "DataRepo_sample"."id") INNER JOIN "DataRepo_tissue" ON ("DataRepo_sample"."tissue_id" = "DataRepo_tissue"."id") LEFT OUTER JOIN "DataRepo_peakgroup_compounds" ON ("DataRepo_peakgroup"."id" = "DataRepo_peakgroup_compounds"."peakgroup_id") LEFT OUTER JOIN "DataRepo_compound" ON ("DataRepo_peakgroup_compounds"."compound_id" = "DataRepo_compound"."id") INNER JOIN "DataRepo_animal" ON ("DataRepo_sample"."animal_id" = "DataRepo_animal"."id") LEFT OUTER JOIN "DataRepo_infusate" ON ("DataRepo_animal"."infusate_id" = "DataRepo_infusate"."id") LEFT OUTER JOIN "DataRepo_infusatetracer" ON ("DataRepo_infusate"."id" = "DataRepo_infusatetracer"."infusate_id") LEFT OUTER JOIN "DataRepo_tracer" ON ("DataRepo_infusatetracer"."tracer_id" = "DataRepo_tracer"."id") LEFT OUTER JOIN "DataRepo_compound" T11 ON ("DataRepo_tracer"."compound_id" = T11."id") LEFT OUTER JOIN "DataRepo_animal_studies" ON ("DataRepo_animal"."id" = "DataRepo_animal_studies"."animal_id") LEFT OUTER JOIN "DataRepo_study" ON ("DataRepo_animal_studies"."study_id" = "DataRepo_study"."id") LEFT OUTER JOIN "DataRepo_peakgrouplabel" ON ("DataRepo_peakgroup"."id" = "DataRepo_peakgrouplabel"."peak_group_id") LEFT OUTER JOIN "DataRepo_peakgroup" T15 ON ("DataRepo_peakgrouplabel"."peak_group_id" = T15."id") LEFT OUTER JOIN "DataRepo_compoundsynonym" ON ("DataRepo_compound"."id" = "DataRepo_compoundsynonym"."compound_id") LEFT OUTER JOIN "DataRepo_compound" T17 ON ("DataRepo_compoundsynonym"."compound_id" = T17."id") LEFT OUTER JOIN "DataRepo_msrunsample" T18 ON ("DataRepo_sample"."id" = T18."sample_id") LEFT OUTER JOIN "DataRepo_peakdata" ON ("DataRepo_peakgroup"."id" = "DataRepo_peakdata"."peak_group_id") LEFT OUTER JOIN "DataRepo_peakdatalabel" ON ("DataRepo_peakdata"."id" = "DataRepo_peakdatalabel"."peak_data_id") WHERE UPPER("DataRepo_tissue"."name"::text) = UPPER(Brain) ORDER BY 1 ASC, 2 ASC, 3 ASC, 4 ASC, 5 ASC, 6 ASC, 7 ASC, 8 ASC, 9 ASC, 10 ASC, 11 ASC, 12 ASC, 13 ASC, 14 ASC, 15 ASC, 16 ASC, 17 ASC, 18 ASC, 19 ASC, 20 ASC, 21 ASC, 22 ASC
all_fields: ['name', 'pk', 'labels__peak_group__name', 'labels__element', 'labels__pk', 'msrun_sample__sample__animal__infusate__name', 'msrun_sample__sample__animal__infusate__tracer_links__tracer__name', 'msrun_sample__sample__animal__infusate__tracer_links__concentration', 'msrun_sample__sample__animal__infusate__tracer_links__pk', 'msrun_sample__sample__animal__infusate__tracers__name', 'msrun_sample__sample__animal__infusate__tracers__pk', 'msrun_sample__sample__animal__infusate__tracers__compound__name', 'msrun_sample__sample__animal__infusate__tracers__compound__pk', 'compounds__name', 'compounds__pk', 'compounds__synonyms__compound__name', 'compounds__synonyms__name', 'compounds__synonyms__pk', 'msrun_sample__sample__animal__studies__name', 'msrun_sample__sample__animal__studies__pk', 'msrun_sample__sample__msrun_samples__ms_data_file__pk', 'msrun_sample__sample__msrun_samples__ms_raw_file__pk', 'msrun_sample__sample__animal__name', 'peak_data__labels__element', 'compounds__name', 'msrun_sample__sample__name', 'msrun_sample__sample__tissue__name', 'msrun_sample__sample__animal__infusate__tracers__compound__name', 'msrun_sample__sample__animal__studies__name', 'msrun_sample__sample__animal__feeding_status', 'msrun_sample__sample__animal__infusion_rate', 'msrun_sample__sample__animal__infusate__tracers__compound__name', 'msrun_sample__sample__animal__infusate__tracer_links__concentration']
DISTINCT FIELDS:
['"DataRepo_peakgroup"."name"', '"DataRepo_peakgroup"."id"', 'T15."name"', '"DataRepo_peakgrouplabel"."element"', '"DataRepo_peakgrouplabel"."id"', '"DataRepo_infusate"."name"', '"DataRepo_tracer"."name"', '"DataRepo_infusatetracer"."concentration"', '"DataRepo_infusatetracer"."id"', '"DataRepo_tracer"."name"', '"DataRepo_infusatetracer"."tracer_id"', 'T11."name"', '"DataRepo_tracer"."compound_id"', '"DataRepo_compound"."name"', '"DataRepo_peakgroup_compounds"."compound_id"', 'T17."name"', '"DataRepo_compoundsynonym"."name"', '"DataRepo_compoundsynonym"."name"', '"DataRepo_study"."name"', '"DataRepo_animal_studies"."study_id"', 'T18."ms_data_file_id"', 'T18."ms_raw_file_id"']
[[], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], []]
order_by:
('name',
 'pk',
 'labels__peak_group__name',
 'labels__element',
 'labels__pk',
 'msrun_sample__sample__animal__infusate__name',
 'msrun_sample__sample__animal__infusate__tracer_links__tracer__name',
 'msrun_sample__sample__animal__infusate__tracer_links__concentration',
 'msrun_sample__sample__animal__infusate__tracer_links__pk',
 'msrun_sample__sample__animal__infusate__tracers__name',
 'msrun_sample__sample__animal__infusate__tracers__pk',
 'msrun_sample__sample__animal__infusate__tracers__compound__name',
 'msrun_sample__sample__animal__infusate__tracers__compound__pk',
 'compounds__name',
 'compounds__pk',
 'compounds__synonyms__compound__name',
 'compounds__synonyms__name',
 'compounds__synonyms__pk',
 'msrun_sample__sample__animal__studies__name',
 'msrun_sample__sample__animal__studies__pk',
 'msrun_sample__sample__msrun_samples__ms_data_file__pk',
 'msrun_sample__sample__msrun_samples__ms_raw_file__pk')

distinct_fields:
('name',
 'pk',
 'labels__peak_group__name',
 'labels__element',
 'labels__pk',
 'msrun_sample__sample__animal__infusate__name',
 'msrun_sample__sample__animal__infusate__tracer_links__tracer__name',
 'msrun_sample__sample__animal__infusate__tracer_links__concentration',
 'msrun_sample__sample__animal__infusate__tracer_links__pk',
 'msrun_sample__sample__animal__infusate__tracers__name',
 'msrun_sample__sample__animal__infusate__tracers__pk',
 'msrun_sample__sample__animal__infusate__tracers__compound__name',
 'msrun_sample__sample__animal__infusate__tracers__compound__pk',
 'compounds__name',
 'compounds__pk',
 'compounds__synonyms__compound__name',
 'compounds__synonyms__name',
 'compounds__synonyms__pk',
 'msrun_sample__sample__animal__studies__name',
 'msrun_sample__sample__animal__studies__pk',
 'msrun_sample__sample__msrun_samples__ms_data_file__pk',
 'msrun_sample__sample__msrun_samples__ms_raw_file__pk')
ETEST TIME: DataRepo.tests.views.search.formats.test_dataformat_group.FormatGroupTests.test_get_query_stats_full: 0.012

======================================================================
ERROR: test_get_query_stats_full (DataRepo.tests.views.search.formats.test_dataformat_group.FormatGroupTests.test_get_query_stats_full)
Test that get_query_stats returns a correct stats structure
----------------------------------------------------------------------
Traceback (most recent call last):
  File "/Users/rleach/PROJECT-LOCAL/TRACEBASE/tracebase3/.venv/lib/python3.11/site-packages/django/db/backends/utils.py", line 105, in _execute
    return self.cursor.execute(sql, params)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
psycopg2.errors.InvalidColumnReference: SELECT DISTINCT ON expressions must match initial ORDER BY expressions
LINE 1: ...o_infusatetracer"."id", "DataRepo_tracer"."name", "DataRepo_...
                                                             ^

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/Users/rleach/PROJECT-LOCAL/TRACEBASE/tracebase3/DataRepo/views/search/formats/dataformat_group.py", line 725, in get_query_stats
    for rec in resultsqs.all():
  File "/Users/rleach/PROJECT-LOCAL/TRACEBASE/tracebase3/.venv/lib/python3.11/site-packages/django/db/models/query.py", line 386, in __iter__
    self._fetch_all()
  File "/Users/rleach/PROJECT-LOCAL/TRACEBASE/tracebase3/.venv/lib/python3.11/site-packages/django/db/models/query.py", line 1954, in _fetch_all
    self._result_cache = list(self._iterable_class(self))
                         ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/rleach/PROJECT-LOCAL/TRACEBASE/tracebase3/.venv/lib/python3.11/site-packages/django/db/models/query.py", line 234, in __iter__
    return compiler.results_iter(
           ^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/rleach/PROJECT-LOCAL/TRACEBASE/tracebase3/.venv/lib/python3.11/site-packages/django/db/models/sql/compiler.py", line 1572, in results_iter
    results = self.execute_sql(
              ^^^^^^^^^^^^^^^^^
  File "/Users/rleach/PROJECT-LOCAL/TRACEBASE/tracebase3/.venv/lib/python3.11/site-packages/django/db/models/sql/compiler.py", line 1623, in execute_sql
    cursor.execute(sql, params)
  File "/Users/rleach/PROJECT-LOCAL/TRACEBASE/tracebase3/.venv/lib/python3.11/site-packages/django/db/backends/utils.py", line 79, in execute
    return self._execute_with_wrappers(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/rleach/PROJECT-LOCAL/TRACEBASE/tracebase3/.venv/lib/python3.11/site-packages/django/db/backends/utils.py", line 92, in _execute_with_wrappers
    return executor(sql, params, many, context)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/rleach/PROJECT-LOCAL/TRACEBASE/tracebase3/.venv/lib/python3.11/site-packages/django/db/backends/utils.py", line 100, in _execute
    with self.db.wrap_database_errors:
  File "/Users/rleach/PROJECT-LOCAL/TRACEBASE/tracebase3/.venv/lib/python3.11/site-packages/django/db/utils.py", line 91, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/Users/rleach/PROJECT-LOCAL/TRACEBASE/tracebase3/.venv/lib/python3.11/site-packages/django/db/backends/utils.py", line 105, in _execute
    return self.cursor.execute(sql, params)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
django.db.utils.ProgrammingError: SELECT DISTINCT ON expressions must match initial ORDER BY expressions
LINE 1: ...o_infusatetracer"."id", "DataRepo_tracer"."name", "DataRepo_...
                                                             ^

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/Users/rleach/PROJECT-LOCAL/TRACEBASE/tracebase3/DataRepo/tests/views/search/formats/test_dataformat_group.py", line 612, in test_get_query_stats_full
    res, _, _ = basv.perform_query(qry, "pgtemplate", generate_stats=True)
                ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/rleach/PROJECT-LOCAL/TRACEBASE/tracebase3/DataRepo/views/search/formats/dataformat_group.py", line 587, in perform_query
    data, based_on = self.get_query_stats(
                     ^^^^^^^^^^^^^^^^^^^^^
  File "/Users/rleach/PROJECT-LOCAL/TRACEBASE/tracebase3/DataRepo/views/search/formats/dataformat_group.py", line 805, in get_query_stats
    raise UnsupportedDistinctCombo(all_distinct_fields) from pe
DataRepo.views.search.formats.dataformat_group.UnsupportedDistinctCombo: Unsupported combination of distinct fields: ['name', 'pk', 'labels__peak_group__name', 'labels__element', 'labels__pk', 'msrun_sample__sample__animal__infusate__name', 'msrun_sample__sample__animal__infusate__tracer_links__tracer__name', 'msrun_sample__sample__animal__infusate__tracer_links__concentration', 'msrun_sample__sample__animal__infusate__tracer_links__pk', 'msrun_sample__sample__animal__infusate__tracers__name', 'msrun_sample__sample__animal__infusate__tracers__pk', 'msrun_sample__sample__animal__infusate__tracers__compound__name', 'msrun_sample__sample__animal__infusate__tracers__compound__pk', 'compounds__name', 'compounds__pk', 'compounds__synonyms__compound__name', 'compounds__synonyms__name', 'compounds__synonyms__pk', 'msrun_sample__sample__animal__studies__name', 'msrun_sample__sample__animal__studies__pk', 'msrun_sample__sample__msrun_samples__ms_data_file__pk', 'msrun_sample__sample__msrun_samples__ms_raw_file__pk'].  The problem likely stems from the usage of field references that are not real database fields, used in both .distinct() and .order_by().  Those methods resolve foreign keys (to database fields) in models differently.  Be sure to supply actual database fields and not foreign key object references.
```

Let me see if there’s more that’s useful…

5

This prompt shows the orderings I must comment out to not hit the error (in 5.2). (Note, I edited the prompt, because it originally said Compound, but was actually the CompoundSynonym table.)

OK.  If I comment out the orderings in:

```
CompoundSynonym:
        # ordering = ["compound", "name"]
Tracer:
        # ordering = [Lower("name")]
```

The query works.  If I comment out only 1 of them, it fails.

I believe the source of the 2 joins of the same table are via PeakGroup.compound (which is a “measured compound”) and Tracer.compound (which is a “radio labeled compound”). And CompoundSynonym links to Compound.

6

I think I have managed to implement google’s suggested work-around using aliases. Note, the regression comment was authored by ChatGPT, and I consider it ultimately questionable until I have fully confirmed what’s going on. I could be totally wrong, but the fact this works and yields no postgres ProgrammingError about order_by/distinct(on) issues seems to tentatively confirm the AI diagnosis.

Note, the original code that causes the error is commented out…

            # DEBUG v
            # Django 5.2 regression workaround:
            #
            # Ordering directly by relationship paths that are also used in distinct(*fields) can resolve differently
            # when traversed models define Meta.ordering, causing PostgreSQL:
            #
            #     SELECT DISTINCT ON expressions must match initial ORDER BY expressions
            #
            # Ordering by aliases (F() expressions) avoids the problematic resolution path while preserving the correct
            # DISTINCT ON fields.
            #
            # Verified:
            #   Django 4.2: original code works
            #   Django 5.2.15: requires alias workaround
            all_distinct_field_from_aliases = []
            all_distinct_field_aliases = {}
            fld: str
            for fld in all_distinct_fields:
                if "__" in fld:
                    alias = fld.replace("__", "_") + "_alias"
                    all_distinct_field_from_aliases.append(alias)
                    all_distinct_field_aliases[alias] = F(fld)
                else:
                    all_distinct_field_from_aliases.append(fld)
            resultsqs = (
                res.alias(**all_distinct_field_aliases)
                .order_by(*all_distinct_field_from_aliases)
                .distinct(*all_distinct_fields)
                .values_list(*all_fields)
            )
            # resultsqs = (
            #     res.order_by(*all_distinct_fields)
            #     .distinct(*all_distinct_fields)
            #     .values_list(*all_fields)
            # )
            # DEBUG ^
7

Have you tried to change the order of your operations from:

res.order_by(...).distinct(...).values_list(...)

to

res.values_list(...).order_by(...).distinct(...)

How this affects your tests?

8

Hi @sevdog,

I tried switching it around, like you suggest. Same error.

Rob

9

Looks like ChatGPT identified another clue while I’ve been wrapping up unrelated failing tests. There are 2 instances of ...__compound__name (i.e. duplicates in the distinct fields) in my data structure that defines my stats query distinct fields - both use the same path. They traverse a ManyToManyField relation using the shortcut defined in the model (tracers) and there’s a different field (concentration) to get a value in the “linking” table that uses the through model (tracer_links). That could be the piece I was missing in my preliminary analysis.

{
    "displayname": "Tracer Concentrations",
    "distincts": [
        "msrun_sample__sample__animal__infusate__tracers__compound__name",
        "msrun_sample__sample__animal__infusate__tracer_links__concentration",
    ],
}

So not only is the ORM trying to keep track of the duplicate fields separately, it is also dealing with the shortcut combined with the through model when trying to make things distinct.

10

Can you share a minimal structure of your models and the failing test to better analyze it? Or, at least, a reproducible simple case which shows the issue?

Currently we know the final part of your code, where you put toghether .order_by, .distinct and .values_list, yet we have no clue nor about the DB schema nor abour any previous manipulation of the queryset nor about your expected result. It is very hard to find the root cause without those info.

11

Yes. I know. I’m sorry. I do intend to make a toy example that reproduces it. I just don’t have the time. I’m on a deadline and I leave on vacation tomorrow. I’ll be gone for a little over a week.

12

I played around with this a bit if anyone wants to take a look. Curious if it seems like it’s on the right track: Fixed QuerySet.distinct() crash on duplicated selections by davegaeddert · Pull Request #3 · davegaeddert/django · GitHub

13

My brief vacation skim of the description looks promising to me. I suspect you’re onto it.

14

This looks like like a solid analysis of the problem and solution @davegaeddert.

I’d suggest filing a regression ticket about it as the problem wasn’t reported previously.

15