Code

Opened 2 years ago

Closed 22 months ago

Last modified 11 months ago

#17974 closed Bug (invalid)

.distint('FIELDNAME') FAILS for MySQL

Reported by: mschettler@… Owned by: nobody
Component: Database layer (models, ORM) Version: 1.4
Severity: Normal Keywords: distinct on
Cc: Triage Stage: Unreviewed
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

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)

Attachments (2)

shell.txt (2.8 KB) - added by mschettler@… 2 years ago.
my shell session
distinct_patch.txt (545 bytes) - added by mschettler@… 2 years ago.
a solution that fixed my problem

Download all attachments as: .zip

Change History (11)

Changed 2 years ago by mschettler@…

my shell session

Changed 2 years ago by mschettler@…

a solution that fixed my problem

comment:1 Changed 2 years ago by kmtracey

  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset

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.)

comment:2 in reply to: ↑ description ; follow-up: Changed 2 years ago by ramiro

  • Resolution set to needsinfo
  • Status changed from new to closed

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?

Last edited 2 years ago by ramiro (previous) (diff)

comment:3 in reply to: ↑ 2 Changed 2 years ago by 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

comment:4 Changed 2 years ago by kmtracey

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.

comment:5 Changed 22 months ago by anonymous

  • Resolution needsinfo deleted
  • Status changed from closed to reopened

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?

comment:6 Changed 22 months ago by anonymous

  • Resolution set to invalid
  • Status changed from reopened to closed

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.

comment:7 Changed 11 months ago by 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

comment:8 Changed 11 months ago by 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

comment:9 Changed 11 months ago by akaariai

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.

Add Comment

Modify Ticket

Change Properties
<Author field>
Action
as closed
as The resolution will be set. Next status will be 'closed'
The resolution will be deleted. Next status will be 'new'
Author


E-mail address and user name can be saved in the Preferences.

 
Note: See TracTickets for help on using tickets.