id summary reporter owner description type status component version severity resolution keywords cc stage has_patch needs_docs needs_tests needs_better_patch easy ui_ux 33682 Clarify using distinct() with related fields that have Meta.ordering defined. Robert Leach Abhimanyu Singh Negi "I have a rather complex database and an advanced search interface that creates complex queries. It’s been working great for over a year now. I recently added a feature to count distinct related table records in the joined results. When I added fields to these many-related tables to `.distinct()` (and to `.order_by()`), I couldn’t get the test to execute without hitting an `InvalidColumnReference` error. And though I’m supplying the same expanded fields list to `.order_by()` that I am to `.distinct()`, the error claims that `SELECT DISTINCT ON expressions must match initial ORDER BY expressions`… When I print the SQL, the place where it notes a difference has a weird `T8` reference where the model name should be in an `order by` clause. The corresponding `distinct` clause has the full table name instead of the reference, which is what I suspect is triggering the exception. I was able to create a set of toy models and a test that minimally reproduces the exception... toymodels.py: {{{ from django.db.models import Model, CharField, AutoField, ForeignKey, ManyToManyField, CASCADE class TestPeak(Model): id = AutoField(primary_key=True) name = CharField(max_length=10) compounds = ManyToManyField( to=""TestCompound"", related_name=""testpeaks"", ) class Meta: verbose_name = ""testpeak"" verbose_name_plural = ""testpeaks"" ordering = [""name""] class TestCompound(Model): id = AutoField(primary_key=True) name = CharField(max_length=10) class Meta: verbose_name = ""testcompound"" verbose_name_plural = ""testcompounds"" ordering = [""name""] class TestSynonym(Model): name = CharField(max_length=10, primary_key=True) compound = ForeignKey( TestCompound, related_name=""testsynonyms"", on_delete=CASCADE ) class Meta: verbose_name = ""testsynonym"" verbose_name_plural = ""testsynonyms"" ordering = [""compound"", ""name""] }}} test_bug.py: {{{ from DataRepo.tests.tracebase_test_case import TracebaseTestCase from DataRepo.models.toymodels import TestPeak, TestCompound, TestSynonym from django.db.models import Q class DjangoSQLBug(TracebaseTestCase): maxDiff = None @classmethod def setUpTestData(cls): TestCompound.objects.create(name=""testcpd"") cpd = TestCompound.objects.get(id__exact=1) TestSynonym.objects.create(name=""testsyn"",compound=cpd) TestPeak.objects.create(name=""testpk"") pk = TestPeak.objects.get(id__exact=1) pk.compounds.add(cpd) def test_mm_om_query(self): q_exp = Q(name__iexact=""testpk"") distinct_fields = ['name', 'pk', 'compounds__testsynonyms__compound', 'compounds__testsynonyms__name', 'compounds__testsynonyms__pk', 'compounds__name', 'compounds__pk'] qs = TestPeak.objects.filter(q_exp).order_by(*distinct_fields).distinct(*distinct_fields) self.assertEqual(qs.count(), 1) }}} `python manage.py test` output: {{{ Creating test database for alias 'default'... Creating test database for alias 'validation'... System check identified no issues (0 silenced). E ====================================================================== ERROR: test_mm_om_query (DataRepo.tests.sqlbugtest.test_bug.DjangoSQLBug) ---------------------------------------------------------------------- Traceback (most recent call last): File ""/Users/rleach/PROJECT-local/TRACEBASE/tracebase/.venv/lib/python3.9/site-packages/django/db/backends/utils.py"", line 84, in _execute return self.cursor.execute(sql, params) psycopg2.errors.InvalidColumnReference: SELECT DISTINCT ON expressions must match initial ORDER BY expressions LINE 1: ...peak"".""id"", ""DataRepo_testsynonym"".""compound_id"", ""DataRepo_... ^ The above exception was the direct cause of the following exception: Traceback (most recent call last): File ""/Users/rleach/PROJECT-local/TRACEBASE/tracebase/DataRepo/tests/sqlbugtest/test_bug.py"", line 21, in test_mm_om_query self.assertEqual(qs.count(), 1) File ""/Users/rleach/PROJECT-local/TRACEBASE/tracebase/.venv/lib/python3.9/site-packages/django/db/models/query.py"", line 412, in count return self.query.get_count(using=self.db) File ""/Users/rleach/PROJECT-local/TRACEBASE/tracebase/.venv/lib/python3.9/site-packages/django/db/models/sql/query.py"", line 519, in get_count number = obj.get_aggregation(using, ['__count'])['__count'] File ""/Users/rleach/PROJECT-local/TRACEBASE/tracebase/.venv/lib/python3.9/site-packages/django/db/models/sql/query.py"", line 504, in get_aggregation result = compiler.execute_sql(SINGLE) File ""/Users/rleach/PROJECT-local/TRACEBASE/tracebase/.venv/lib/python3.9/site-packages/django/db/models/sql/compiler.py"", line 1175, in execute_sql cursor.execute(sql, params) File ""/Users/rleach/PROJECT-local/TRACEBASE/tracebase/.venv/lib/python3.9/site-packages/django/db/backends/utils.py"", line 66, in execute return self._execute_with_wrappers(sql, params, many=False, executor=self._execute) File ""/Users/rleach/PROJECT-local/TRACEBASE/tracebase/.venv/lib/python3.9/site-packages/django/db/backends/utils.py"", line 75, in _execute_with_wrappers return executor(sql, params, many, context) File ""/Users/rleach/PROJECT-local/TRACEBASE/tracebase/.venv/lib/python3.9/site-packages/django/db/backends/utils.py"", line 84, in _execute return self.cursor.execute(sql, params) File ""/Users/rleach/PROJECT-local/TRACEBASE/tracebase/.venv/lib/python3.9/site-packages/django/db/utils.py"", line 90, in __exit__ raise dj_exc_value.with_traceback(traceback) from exc_value File ""/Users/rleach/PROJECT-local/TRACEBASE/tracebase/.venv/lib/python3.9/site-packages/django/db/backends/utils.py"", line 84, in _execute return self.cursor.execute(sql, params) django.db.utils.ProgrammingError: SELECT DISTINCT ON expressions must match initial ORDER BY expressions LINE 1: ...peak"".""id"", ""DataRepo_testsynonym"".""compound_id"", ""DataRepo_... ^ ---------------------------------------------------------------------- Ran 1 test in 0.018s FAILED (errors=1) Destroying test database for alias 'default'... Destroying test database for alias 'validation'... gen-rl-macbookair[2022-05-05 12:34:44]:~/PROJECT-local/TRACEBASE/tracebase$ }}} I posted on Django Users about this, if you would like more information: https://forum.djangoproject.com/t/is-this-a-bug-in-djangos-sql-creation-through-multiple-many-to-many-tables/13508/9 We’re on Django 3.2 using Postgres." Cleanup/optimization assigned Documentation 3.2 Normal sql, distinct Carlton Gibson Accepted 1 0 0 0 0 0