[Django] #17990: Distinct + Random + Postgres = Bug

[Django]

#17990: Distinct + Random + Postgres = Bug
----------------------------------------------+--------------------
Reporter: pizzapanther | Owner: nobody
Type: Bug | Status: new
Component: Database layer (models, ORM) | Version: 1.4
Severity: Normal | Keywords:
Triage Stage: Unreviewed | Has patch: 0
Easy pickings: 0 | UI/UX: 0
----------------------------------------------+--------------------
When using distinct().order_by('?') with Postgres you get a query error.

My models:
{{{
class Category (models.Model):
title = models.CharField(max_length=100)
slug = models.SlugField(unique=True, max_length=200)

class Post (models.Model):
title = models.CharField(max_length=255)
slug = models.SlugField(unique=True, max_length=200)
publish = models.DateTimeField()
categories = models.ManyToManyField(Category)
body = models.TextField()

}}}

Causes Bug in Postgres but not SQLite:
{{{
return
Post.objects.filter(categories__in=cats).exclude(id=self.id).order_by('?').distinct()[:5]
}}}

No Error:
{{{
return
Post.objects.filter(categories__in=cats).exclude(id=self.id).distinct()[:5]
}}}

Traceback:
{{{
ERROR:django.request:Internal Server Error: /blog/online-photo-editors/
Traceback (most recent call last):
File "/home/webapp/pyenv/cr/local/lib/python2.7/site-
packages/django/core/handlers/base.py", line 136, in get_response
response = response.render()
File "/home/webapp/pyenv/cr/local/lib/python2.7/site-
packages/django/template/response.py", line 104, in render
self._set_content(self.rendered_content)
File "/home/webapp/pyenv/cr/local/lib/python2.7/site-
packages/django/template/response.py", line 81, in rendered_content
content = template.render(context)
File "/home/webapp/pyenv/cr/local/lib/python2.7/site-
packages/django/template/base.py", line 140, in render
return self._render(context)
File "/home/webapp/pyenv/cr/local/lib/python2.7/site-
packages/django/template/base.py", line 134, in _render
return self.nodelist.render(context)
File "/home/webapp/pyenv/cr/local/lib/python2.7/site-
packages/django/template/base.py", line 823, in render
bit = self.render_node(node, context)
File "/home/webapp/pyenv/cr/local/lib/python2.7/site-
packages/django/template/base.py", line 837, in render_node
return node.render(context)
File "/home/webapp/pyenv/cr/local/lib/python2.7/site-
packages/django/template/loader_tags.py", line 123, in render
return compiled_parent._render(context)
File "/home/webapp/pyenv/cr/local/lib/python2.7/site-
packages/django/template/base.py", line 134, in _render
return self.nodelist.render(context)
File "/home/webapp/pyenv/cr/local/lib/python2.7/site-
packages/django/template/base.py", line 823, in render
bit = self.render_node(node, context)
File "/home/webapp/pyenv/cr/local/lib/python2.7/site-
packages/django/template/base.py", line 837, in render_node
return node.render(context)
File "/home/webapp/pyenv/cr/local/lib/python2.7/site-
packages/django/template/loader_tags.py", line 62, in render
result = block.nodelist.render(context)
File "/home/webapp/pyenv/cr/local/lib/python2.7/site-
packages/django/template/base.py", line 823, in render
bit = self.render_node(node, context)
File "/home/webapp/pyenv/cr/local/lib/python2.7/site-
packages/django/template/base.py", line 837, in render_node
return node.render(context)
File "/home/webapp/pyenv/cr/local/lib/python2.7/site-
packages/django/template/defaulttags.py", line 145, in render
len_values = len(values)
File "/home/webapp/pyenv/cr/local/lib/python2.7/site-
packages/django/db/models/query.py", line 85, in __len__
self._result_cache = list(self.iterator())
File "/home/webapp/pyenv/cr/local/lib/python2.7/site-
packages/django/db/models/query.py", line 291, in iterator
for row in compiler.results_iter():
File "/home/webapp/pyenv/cr/local/lib/python2.7/site-
packages/django/db/models/sql/compiler.py", line 763, in results_iter
for rows in self.execute_sql(MULTI):
File "/home/webapp/pyenv/cr/local/lib/python2.7/site-
packages/django/db/models/sql/compiler.py", line 818, in execute_sql
cursor.execute(sql, params)
File "/home/webapp/pyenv/cr/local/lib/python2.7/site-
packages/django/db/backends/postgresql_psycopg2/base.py", line 52, in
execute
return self.cursor.execute(query, args)
DatabaseError: for SELECT DISTINCT, ORDER BY expressions must appear in
select list
LINE 1: ..." = 4 ) AND NOT ("blog_post"."id" = 4 )) ORDER BY RANDOM() L...
}}}

--
Ticket URL: <https://code.djangoproject.com/ticket/17990>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

[Django]

#17990: Distinct + Random + Postgres = Bug

-------------------------------------+-------------------------------------

Reporter: pizzapanther | Owner: nobody
Type: Bug | Status: new

Component: Database layer | Version: 1.4
(models, ORM) | Resolution:
Severity: Normal | Triage Stage: Accepted
Keywords: | Needs documentation: 0
Has patch: 0 | Patch needs improvement: 0
Needs tests: 0 | UI/UX: 0
Easy pickings: 0 |
-------------------------------------+-------------------------------------
Changes (by akaariai):

* needs_docs: => 0
* needs_better_patch: => 0
* needs_tests: => 0
* stage: Unreviewed => Accepted

Comment:

I don't think this will be easy to fix. The obvious fix is to add the
random() to the select distinct list, but that of course breaks the
distinct (you are doing distinct on random(), which isn't wanted). The
only solution I can see is using an inner query with the distinct, then
ordering by random() in the outer query. It should be possible to do. The
usecase isn't common and the patch will likely be nontrivial.

--
Ticket URL: <https://code.djangoproject.com/ticket/17990#comment:1>

[Django]

#17990: Distinct + Random + Postgres = Bug

-------------------------------------+-------------------------------------

Reporter: pizzapanther | Owner: nobody
Type: Bug | Status: new

Component: Database layer | Version: 1.4
(models, ORM) | Resolution:
Severity: Normal | Triage Stage: Accepted
Keywords: | Needs documentation: 0
Has patch: 0 | Patch needs improvement: 0
Needs tests: 0 | UI/UX: 0
Easy pickings: 0 |
-------------------------------------+-------------------------------------

Comment (by anonymous):

I solved this problem using GROUP BY instead of DISTINCT. I found this
solution here:

http://stackoverflow.com/questions/9538578/

--
Ticket URL: <https://code.djangoproject.com/ticket/17990#comment:2>

[Django]

#17990: Distinct + Random + Postgres = Bug

-------------------------------------+-------------------------------------

Reporter: pizzapanther | Owner: nobody
Type: Bug | Status: new

Component: Database layer | Version: 1.4
(models, ORM) | Resolution:
Severity: Normal | Triage Stage: Accepted
Keywords: | Needs documentation: 0
Has patch: 0 | Patch needs improvement: 0
Needs tests: 0 | UI/UX: 0
Easy pickings: 0 |
-------------------------------------+-------------------------------------

Comment (by gcbirzan):

Actually, an even easier fix would be to exclude the RANDOM from the
distinct. That's in a patch. In a trivial case, that can be worked around
like this:

Model.objects.extra(select={'rand':
'RANDOM()'}).distinct('pk').order_by('rand')

--
Ticket URL: <https://code.djangoproject.com/ticket/17990#comment:3>

[Django]

#17990: DISTINCT querysets with RANDOM ordering crash with DatabaseError in
Postgres
-------------------------------------+-------------------------------------
Reporter: pizzapanther | Owner: nobody
Type: Bug | Status: closed

Component: Database layer | Version: 1.4
(models, ORM) |

Severity: Normal | Resolution: fixed
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0

Easy pickings: 0 | UI/UX: 0

-------------------------------------+-------------------------------------
Changes (by Étienne Beaulé):

* cc: Étienne Beaulé (added)
* status: new => closed
* resolution: => fixed

Comment:

Fixed in 0c7633178fa9410f102e4708cef979b873bccb76

--
Ticket URL: <https://code.djangoproject.com/ticket/17990#comment:4>

[Django]

#17990: DISTINCT querysets with RANDOM ordering crash with DatabaseError in
Postgres
-------------------------------------+-------------------------------------

Reporter: pizzapanther | Owner: nobody

Type: Bug | Status: closed
Component: Database layer | Version: 1.4
(models, ORM) |
Severity: Normal | Resolution: fixed
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0

Easy pickings: 0 | UI/UX: 0

-------------------------------------+-------------------------------------
Changes (by Mariusz Felisiak):

* Attachment "test-17990.diff" added.

Regression test.

[Django]

#17990: DISTINCT querysets with RANDOM ordering crash with DatabaseError in
Postgres
-------------------------------------+-------------------------------------

Reporter: pizzapanther | Owner: nobody
Type: Bug | Status: new

Component: Database layer | Version: 1.4
(models, ORM) |
Severity: Normal | Resolution:

Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0

Easy pickings: 0 | UI/UX: 0

-------------------------------------+-------------------------------------
Changes (by Mariusz Felisiak):

* status: closed => new
* resolution: fixed =>

Comment:

It doesn't crash but it's not fixed, see
[https://code.djangoproject.com/ticket/17990#comment:1 Anssi's comment]. I
attached a regression test.

--
Ticket URL: <https://code.djangoproject.com/ticket/17990#comment:6>

[Django]

#17990: DISTINCT querysets with RANDOM ordering crash with DatabaseError in
Postgres
-------------------------------------+-------------------------------------

Reporter: pizzapanther | Owner: Ryan
| Cheley
Type: Bug | Status: assigned

Component: Database layer | Version: 1.4
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0

Easy pickings: 0 | UI/UX: 0

-------------------------------------+-------------------------------------
Changes (by Ryan Cheley):

* owner: nobody => Ryan Cheley
* status: new => assigned

--
Ticket URL: <https://code.djangoproject.com/ticket/17990#comment:7>

[Django]

#17990: DISTINCT querysets with RANDOM ordering crash with DatabaseError in
Postgres
-------------------------------------+-------------------------------------
Reporter: pizzapanther | Owner: Ryan
| Cheley

Type: Bug | Status: closed

Component: Database layer | Version: 1.4
(models, ORM) |

Severity: Normal | Resolution: duplicate

Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0

Easy pickings: 0 | UI/UX: 0

-------------------------------------+-------------------------------------
Changes (by Ryan Cheley):

* status: assigned => closed
* resolution: => duplicate

Comment:

The solution offered by Anssi Kääriäinen and supported by the
stackoverflow answer in ticket:17990#comment:2 point to this being a
subset of the solution being worked on in 24462. Therefore it is being
marked as resolved - duplicate

--
Ticket URL: <https://code.djangoproject.com/ticket/17990#comment:8>