DISTINCT querysets with RANDOM ordering crash with DatabaseError in Postgres

[Paul Bailey]

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

[Anssi Kääriäinen]

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.

[anonymous]

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

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

[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')

[Étienne Beaulé]

Fixed in 0c7633178fa9410f102e4708cef979b873bccb76

[Mariusz Felisiak]

If it's already fixed we should at least add a regression test.

[Mariusz Felisiak]

Regression test.

[Mariusz Felisiak]

It doesn't crash but it's not fixed, see Anssi's comment. I attached a regression test.

[Ryan Cheley]

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