.distint('FIELDNAME') FAILS for MySQL

[mschettler@…]

Hello -- I've long had a django 1.3 application using .distinct() successfully with my mysql backend.

Ive noticed a rather big (app breaking) issue in 1.4 release. Note, nothing else has changed except me updating to 1.4 today.

When I updated to 1.4 (via rev:17806 in django-trunk), I've been getting a

  File "/Applications/django-1.4/python/lib/python2.6/site-packages/django/db/backends/__init__.py", line 572, in distinct_sql
    raise NotImplementedError('DISTINCT ON fields is not supported by this database backend')
NotImplementedError: DISTINCT ON fields is not supported by this database backend

This seems to have happened as a result of this changeset:
​http://www.mail-archive.com/django-updates@googlegroups.com/msg81871.html

I think the issue is this: (from above changeset)

+        if fields:
+            raise NotImplementedError('DISTINCT ON fields is not supported by 
this database backend')
+        else:
+            return 'DISTINCT'

You'll notice from my attached shell session that when i call .distinct() with no fields, it works properly. As soon as I try .distinct('fieldname'), I get the above error.

I then made this change:

Index: django/db/backends/__init__.py
===================================================================
--- django/db/backends/__init__.py	(revision 17806)
+++ django/db/backends/__init__.py	(working copy)
@@ -568,7 +568,7 @@
         result set. If any fields are given, only the given fields are being
         checked for duplicates.
         """
-        if fields:
+        if not fields:
             raise NotImplementedError('DISTINCT ON fields is not supported by this database backend')
         else:
             return 'DISTINCT'

With this change in place, my code executes perfectly.

I have seen this bug in the 1.4 release, and as of writing current django-trunk. I've attached both my shell session and above patch to this ticket

Is this a bug? Or is something else going on at play locally with my setup? I've witnessed this both on my dev machine (OSX 10.7) and my deployment machine (UBUNTU 10.04)

[mschettler@…]

my shell session

[mschettler@…]

a solution that fixed my problem

[Karen Tracey]

QuerySet distinct didn't accept arguments in 1.3: ​https://docs.djangoproject.com/en/1.3/ref/models/querysets/#django.db.models.query.QuerySet.distinct

If you were passing in a field name and expecting that to have some effect, it was not. Removing the fieldname you were passing into distinct would restore your application to behaving as it was in 1.3. (Which may not be how you thought it was working, if you thought that fieldname passed in was doing something.)

[Ramiro Morales]

Replying to mschettler@…:

Hello -- I've long had a django 1.3 application using .distinct() successfully with my mysql backend.

Yes, we need you to give us that critical piece of information: Were you were using distinct() (supported, ​documented) or distinct('field_name') (unsupported) with Django 1.3?

[mschettler@…]

Replying to ramiro:

Replying to mschettler@…:

Hello -- I've long had a django 1.3 application using .distinct() successfully with my mysql backend.

Yes, we need you to give us that critical piece of information: Were you were using dictinct() (supported, ​documented) or dictinct('field_name') (unsupported) with Django 1.3?

I was using distinct('fieldname') and from what i could tell, it was working.

How was it not working?

-Matt

[Karen Tracey]

The field name you were passing was not being used the any way you were expecting. QuerySet distinct in 1.3 had an undocumented true_or_false argument, which defaulted to True. Passing in a non-empty field name string (distinct('field')) was equivalent to passing in nothing (distinct()). No code within the distinct implementation was using the field name value to restrict the distinct clause to that field. (See https://code.djangoproject.com/changeset/17244#file4 for the changes made here when the list of field names support was added.)

Since true_or_false was undocumented it was decided when #6422 was fixed that this backwards-incompatibility of removing the true_or_false argument was acceptable. I'm not sure if anyone considered that possibly people were incorrectly passing in field names thinking that was having an effect. Essentially what this change has done is expose a bug in your application that previously was hidden due to the undocumented true_or_false argument hiding the error when an argument was passed to distinct. Given the docs for 1.3 really all along you should have been getting a TypeError on trying to pass an argument into distinct.

[anonymous]

Im getting this error on 1.4?
​https://docs.djangoproject.com/en/1.4/ref/models/querysets/#django.db.models.query.QuerySet.distinct
It says its supported if you use Order_by?

[anonymous]

If you are getting exactly the error noted in the original description, then the problem is you are trying to use a feature supported by only PostgreSQL on some other database.

[Paul von Hoesslin]

ok but then what is the point of an ORM? The purpose of having an ORM is to create database independence (and don't kid yourself, I use distinct in mysql all the time). Please cater for this functionality or don't bother giving us the choice. This is as useless as Model.objects.all().delete()... Why not just truncate the table, or give some sort of functionality to do so, such as Model.objects.truncate(). Django's awesome but without these "little" functionalities you really shoot yourself in the foot.https://code.djangoproject.com/ticket/17974#no3

[Paul von Hoesslin]

"If you are getting exactly the error noted in the original description, then the problem is you are trying to use a feature supported by only PostgreSQL on some other database. "

Mysql supports distinct. I practically use it daily. So your statement is invalid. This error != true

[Anssi Kääriäinen]

If you can make .distinct('somefield') work on MySQL it would be very welcome addition. I believe it is somewhat hard to write that patch correctly (particularly, how to make .order_by() + .distinct() work correctly). MySQL doesn't have DISTINCT ON (fields), so you need something like this: ​http://stackoverflow.com/questions/5804529/distinct-on-multiple-columns.

Of course, if you want just distinct somefield values, instead of the full DISTINCT ON semantics, then qs.values_list('somefield').distinct() should work.

Why is there DISTINCT ON implementation that works only on PostgreSQL? The choice was to not support this at all, or support this only on PostgreSQL. The distinct(*fields) is very useful in certain cases, so I think having it on PostgreSQL only is better than not having it at all.