Code

Opened 2 years ago

Last modified 4 months ago

#17990 new Bug

Distinct + Random + Postgres = Bug

Reported by: pizzapanther Owned by: nobody
Component: Database layer (models, ORM) Version: 1.4
Severity: Normal Keywords:
Cc: Triage Stage: Accepted
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

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

Attachments (0)

Change History (3)

comment:1 Changed 2 years ago by akaariai

  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset
  • Triage Stage changed from Unreviewed to Accepted

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.

comment:2 Changed 22 months ago by anonymous

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

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

comment:3 Changed 4 months ago 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')

Add Comment

Modify Ticket

Change Properties
<Author field>
Action
as new
The owner will be changed from nobody to anonymous. Next status will be 'assigned'
as The resolution will be set. Next status will be 'closed'
Author


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

 
Note: See TracTickets for help on using tickets.