Limit the number of terms in an admin search

[VINAY KUMAR SHARMA]

Found this bug when searching with long string:

OperationalError at /posts/posts

(1116, 'Too many tables; MySQL can only use 61 tables in a join')

Request URL: http://cms.example.com/posts/posts/?q=Deliverable++TRC+Promo+%282+Options%29+Visual+Snapshot++%28Montage%29+How+do+you+know+your+reward+Strategy+is+working%3F+Masterclass%3A+Consumerization+of+Total+Rewards%3A+Superhero%27s+Toolkit+Alignment%2C+Management+and+Governance+of+%E2%80%98Executive+Pay%E2%80%99+and+Long+Term+Incentives+Award+Ceremony%3A+League+Awards+-Total+Rewards+2017+%22Masterclass%3AMotivating+your+Internal+Customers++Part+II%3A+A+Communication+Toolkit+by+Preeti+Gupta%2C+Aditya+Birla+Group

I've created a CMS system using Django Admin, where models Posts is in app Posts.

Model Posts has an M2M field authors, and this field I've included in search for admin.ModelAdmin:

e.g here:

# models.py
@python_2_unicode_compatible
class Posts(models.Model):
    id = models.AutoField(primary_key=True, db_column='article_id')
    #
    # ... other fields ...
    #
    # Related M2M/Foreign Key fields
    authors = models.ManyToManyField(Author, through='PostAuthors',
                                     through_fields=('post', 'author'))
    subcategories = models.ManyToManyField(SubCategory, through='PostSubcategory',
                                           through_fields=('post', 'subcategory'))

# admin.py
@admin.register(Posts)
class PostsAdmin(admin.ModelAdmin):
    actions = [delete_selected, republish_selected, add_to_top_stories]
    list_per_page = ADMIN_LIST_PER_PAGE
    list_filter = [PostTypeListFilter, 'datetime', 'is_active', 'is_deleted']
    search_fields = ['authors__name', 'title']

[Jani Tiainen]

Could you create minimal testcase that causes the error?

[Tim Graham]

As Jani said, there isn't really enough information here to determine if it's a bug in Django. Ideally the steps to reproduce shouldn't use any third-party apps.

[Simon Charette]

Tim, I'm confident this is related to this ​PR.

The thing here is that since search terms are filtered independently each space delimited term results in a JOIN, the problem gets even worse when there's multiple search_fields.

I think limiting the number of search terms wouldn't hurt here.

[VINAY KUMAR SHARMA]

Replying to Jani Tiainen:

Could you create minimal testcase that causes the error?

Test :(

It's really hard for me! I don't write tests. Testing in Django is very difficult part. As this requires very high mem/cpu and configuration. I had tried but it sucks the system with large database. I also tried with fixtures, this also failed!

:( so sorry

[Tim Graham]

Limiting the number of search terms in the admin seems okay to me, although that's more complexity and another customization point. Some consideration in choosing a sensible default limit will be needed.

[John Wright]

Here is a test for admin_changelist/tests.py

    def test_long_search_var(self):                                                                                     
        """                                                                                                             
        """                                                                                                             
        parent = Parent.objects.create(name='Mary')                                                                     
        Child.objects.create(parent=parent, name='Danielle')                                                            
        Child.objects.create(parent=parent, name='Daniel')                                                              
                                                                                                                        
        m = ParentAdmin(Parent, custom_site)                                                                            
        request = self.factory.get('/parent/', data={SEARCH_VAR: ' '.join(['daniel']*100)})                             
        request.user = self.superuser                                                                                   
                                                                                                                        
        cl = m.get_changelist_instance(request)                                                                         
        # Make sure distinct() was called                                                                               
        self.assertEqual(cl.queryset.count(), 1)                                                                        

======================================================================
ERROR: test_long_search_var (admin_changelist.tests.ChangeListTests)
----------------------------------------------------------------------
Traceback (most recent call last):
  File "/usr/lib/python3.7/unittest/case.py", line 59, in testPartExecutor
    yield
  File "/usr/lib/python3.7/unittest/case.py", line 615, in run
    testMethod()
  File "/source/django/tests/admin_changelist/tests.py", line 420, in test_long_search_var
    cl = m.get_changelist_instance(request)
  File "/source/django/django/contrib/admin/options.py", line 742, in get_changelist_instance
    sortable_by,
  File "/source/django/django/contrib/admin/views/main.py", line 82, in __init__
    self.get_results(request)
  File "/source/django/django/contrib/admin/views/main.py", line 210, in get_results
    result_count = paginator.count
  File "/source/django/django/utils/functional.py", line 57, in __get__
    res = instance.__dict__[self.name] = self.func(instance)
  File "/source/django/django/core/paginator.py", line 91, in count
    return c()
  File "/source/django/django/db/models/query.py", line 391, in count
    return self.query.get_count(using=self.db)
  File "/source/django/django/db/models/sql/query.py", line 504, in get_count
    number = obj.get_aggregation(using, ['__count'])['__count']
  File "/source/django/django/db/models/sql/query.py", line 489, in get_aggregation
    result = compiler.execute_sql(SINGLE)
  File "/source/django/django/db/models/sql/compiler.py", line 1080, in execute_sql
    cursor.execute(sql, params)
  File "/source/django/django/db/backends/utils.py", line 67, in execute
    return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
  File "/source/django/django/db/backends/utils.py", line 76, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File "/source/django/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
  File "/source/django/django/db/utils.py", line 89, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/source/django/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
  File "/source/django/django/db/backends/sqlite3/base.py", line 383, in execute
    return Database.Cursor.execute(self, query, params)
django.db.utils.OperationalError: at most 64 tables in a join

----------------------------------------------------------------------
Ran 56 tests in 0.627s

FAILED (errors=1, skipped=1)

print in django/db/backends/sqlite3/base.py shows this SQL and params:

SELECT COUNT(*) FROM (SELECT DISTINCT "admin_changelist_parent"."id" AS Col1, "admin_changelist_parent"."name" AS Col2 FROM "admin_changelist_parent" INNER JOIN "admin_changelist_child" ON ("admin_changelist_parent"."id" = "admin_changelist_child"."parent_id") INNER JOIN "admin_changelist_child" T3 ON ("admin_changelist_parent"."id" = T3."parent_id") INNER JOIN "admin_changelist_child" T4 ON ("admin_changelist_parent"."id" = T4."parent_id") INNER JOIN "admin_changelist_child" T5 ON ("admin_changelist_parent"."id" = T5."parent_id") INNER JOIN "admin_changelist_child" T6 ON ("admin_changelist_parent"."id" = T6."parent_id") INNER JOIN "admin_changelist_child" T7 ON ("admin_changelist_parent"."id" = T7."parent_id") INNER JOIN "admin_changelist_child" T8 ON ("admin_changelist_parent"."id" = T8."parent_id") INNER JOIN "admin_changelist_child" T9 ON ("admin_changelist_parent"."id" = T9."parent_id") INNER JOIN "admin_changelist_child" T10 ON ("admin_changelist_parent"."id" = T10."parent_id") INNER JOIN "admin_changelist_child" T11 ON ("admin_changelist_parent"."id" = T11."parent_id") INNER JOIN "admin_changelist_child" T12 ON ("admin_changelist_parent"."id" = T12."parent_id") INNER JOIN "admin_changelist_child" T13 ON ("admin_changelist_parent"."id" = T13."parent_id") INNER JOIN "admin_changelist_child" T14 ON ("admin_changelist_parent"."id" = T14."parent_id") INNER JOIN "admin_changelist_child" T15 ON ("admin_changelist_parent"."id" = T15."parent_id") INNER JOIN "admin_changelist_child" T16 ON ("admin_changelist_parent"."id" = T16."parent_id") INNER JOIN "admin_changelist_child" T17 ON ("admin_changelist_parent"."id" = T17."parent_id") INNER JOIN "admin_changelist_child" T18 ON ("admin_changelist_parent"."id" = T18."parent_id") INNER JOIN "admin_changelist_child" T19 ON ("admin_changelist_parent"."id" = T19."parent_id") INNER JOIN "admin_changelist_child" T20 ON ("admin_changelist_parent"."id" = T20."parent_id") INNER JOIN "admin_changelist_child" T21 ON ("admin_changelist_parent"."id" = T21."parent_id") INNER JOIN "admin_changelist_child" T22 ON ("admin_changelist_parent"."id" = T22."parent_id") INNER JOIN "admin_changelist_child" T23 ON ("admin_changelist_parent"."id" = T23."parent_id") INNER JOIN "admin_changelist_child" T24 ON ("admin_changelist_parent"."id" = T24."parent_id") INNER JOIN "admin_changelist_child" T25 ON ("admin_changelist_parent"."id" = T25."parent_id") INNER JOIN "admin_changelist_child" T26 ON ("admin_changelist_parent"."id" = T26."parent_id") INNER JOIN "admin_changelist_child" T27 ON ("admin_changelist_parent"."id" = T27."parent_id") INNER JOIN "admin_changelist_child" T28 ON ("admin_changelist_parent"."id" = T28."parent_id") INNER JOIN "admin_changelist_child" T29 ON ("admin_changelist_parent"."id" = T29."parent_id") INNER JOIN "admin_changelist_child" T30 ON ("admin_changelist_parent"."id" = T30."parent_id") INNER JOIN "admin_changelist_child" T31 ON ("admin_changelist_parent"."id" = T31."parent_id") INNER JOIN "admin_changelist_child" T32 ON ("admin_changelist_parent"."id" = T32."parent_id") INNER JOIN "admin_changelist_child" T33 ON ("admin_changelist_parent"."id" = T33."parent_id") INNER JOIN "admin_changelist_child" T34 ON ("admin_changelist_parent"."id" = T34."parent_id") INNER JOIN "admin_changelist_child" T35 ON ("admin_changelist_parent"."id" = T35."parent_id") INNER JOIN "admin_changelist_child" T36 ON ("admin_changelist_parent"."id" = T36."parent_id") INNER JOIN "admin_changelist_child" T37 ON ("admin_changelist_parent"."id" = T37."parent_id") INNER JOIN "admin_changelist_child" T38 ON ("admin_changelist_parent"."id" = T38."parent_id") INNER JOIN "admin_changelist_child" T39 ON ("admin_changelist_parent"."id" = T39."parent_id") INNER JOIN "admin_changelist_child" T40 ON ("admin_changelist_parent"."id" = T40."parent_id") INNER JOIN "admin_changelist_child" T41 ON ("admin_changelist_parent"."id" = T41."parent_id") INNER JOIN "admin_changelist_child" T42 ON ("admin_changelist_parent"."id" = T42."parent_id") INNER JOIN "admin_changelist_child" T43 ON ("admin_changelist_parent"."id" = T43."parent_id") INNER JOIN "admin_changelist_child" T44 ON ("admin_changelist_parent"."id" = T44."parent_id") INNER JOIN "admin_changelist_child" T45 ON ("admin_changelist_parent"."id" = T45."parent_id") INNER JOIN "admin_changelist_child" T46 ON ("admin_changelist_parent"."id" = T46."parent_id") INNER JOIN "admin_changelist_child" T47 ON ("admin_changelist_parent"."id" = T47."parent_id") INNER JOIN "admin_changelist_child" T48 ON ("admin_changelist_parent"."id" = T48."parent_id") INNER JOIN "admin_changelist_child" T49 ON ("admin_changelist_parent"."id" = T49."parent_id") INNER JOIN "admin_changelist_child" T50 ON ("admin_changelist_parent"."id" = T50."parent_id") INNER JOIN "admin_changelist_child" T51 ON ("admin_changelist_parent"."id" = T51."parent_id") INNER JOIN "admin_changelist_child" T52 ON ("admin_changelist_parent"."id" = T52."parent_id") INNER JOIN "admin_changelist_child" T53 ON ("admin_changelist_parent"."id" = T53."parent_id") INNER JOIN "admin_changelist_child" T54 ON ("admin_changelist_parent"."id" = T54."parent_id") INNER JOIN "admin_changelist_child" T55 ON ("admin_changelist_parent"."id" = T55."parent_id") INNER JOIN "admin_changelist_child" T56 ON ("admin_changelist_parent"."id" = T56."parent_id") INNER JOIN "admin_changelist_child" T57 ON ("admin_changelist_parent"."id" = T57."parent_id") INNER JOIN "admin_changelist_child" T58 ON ("admin_changelist_parent"."id" = T58."parent_id") INNER JOIN "admin_changelist_child" T59 ON ("admin_changelist_parent"."id" = T59."parent_id") INNER JOIN "admin_changelist_child" T60 ON ("admin_changelist_parent"."id" = T60."parent_id") INNER JOIN "admin_changelist_child" T61 ON ("admin_changelist_parent"."id" = T61."parent_id") INNER JOIN "admin_changelist_child" T62 ON ("admin_changelist_parent"."id" = T62."parent_id") INNER JOIN "admin_changelist_child" T63 ON ("admin_changelist_parent"."id" = T63."parent_id") INNER JOIN "admin_changelist_child" T64 ON ("admin_changelist_parent"."id" = T64."parent_id") INNER JOIN "admin_changelist_child" T65 ON ("admin_changelist_parent"."id" = T65."parent_id") INNER JOIN "admin_changelist_child" T66 ON ("admin_changelist_parent"."id" = T66."parent_id") INNER JOIN "admin_changelist_child" T67 ON ("admin_changelist_parent"."id" = T67."parent_id") INNER JOIN "admin_changelist_child" T68 ON ("admin_changelist_parent"."id" = T68."parent_id") INNER JOIN "admin_changelist_child" T69 ON ("admin_changelist_parent"."id" = T69."parent_id") INNER JOIN "admin_changelist_child" T70 ON ("admin_changelist_parent"."id" = T70."parent_id") INNER JOIN "admin_changelist_child" T71 ON ("admin_changelist_parent"."id" = T71."parent_id") INNER JOIN "admin_changelist_child" T72 ON ("admin_changelist_parent"."id" = T72."parent_id") INNER JOIN "admin_changelist_child" T73 ON ("admin_changelist_parent"."id" = T73."parent_id") INNER JOIN "admin_changelist_child" T74 ON ("admin_changelist_parent"."id" = T74."parent_id") INNER JOIN "admin_changelist_child" T75 ON ("admin_changelist_parent"."id" = T75."parent_id") INNER JOIN "admin_changelist_child" T76 ON ("admin_changelist_parent"."id" = T76."parent_id") INNER JOIN "admin_changelist_child" T77 ON ("admin_changelist_parent"."id" = T77."parent_id") INNER JOIN "admin_changelist_child" T78 ON ("admin_changelist_parent"."id" = T78."parent_id") INNER JOIN "admin_changelist_child" T79 ON ("admin_changelist_parent"."id" = T79."parent_id") INNER JOIN "admin_changelist_child" T80 ON ("admin_changelist_parent"."id" = T80."parent_id") INNER JOIN "admin_changelist_child" T81 ON ("admin_changelist_parent"."id" = T81."parent_id") INNER JOIN "admin_changelist_child" T82 ON ("admin_changelist_parent"."id" = T82."parent_id") INNER JOIN "admin_changelist_child" T83 ON ("admin_changelist_parent"."id" = T83."parent_id") INNER JOIN "admin_changelist_child" T84 ON ("admin_changelist_parent"."id" = T84."parent_id") INNER JOIN "admin_changelist_child" T85 ON ("admin_changelist_parent"."id" = T85."parent_id") INNER JOIN "admin_changelist_child" T86 ON ("admin_changelist_parent"."id" = T86."parent_id") INNER JOIN "admin_changelist_child" T87 ON ("admin_changelist_parent"."id" = T87."parent_id") INNER JOIN "admin_changelist_child" T88 ON ("admin_changelist_parent"."id" = T88."parent_id") INNER JOIN "admin_changelist_child" T89 ON ("admin_changelist_parent"."id" = T89."parent_id") INNER JOIN "admin_changelist_child" T90 ON ("admin_changelist_parent"."id" = T90."parent_id") INNER JOIN "admin_changelist_child" T91 ON ("admin_changelist_parent"."id" = T91."parent_id") INNER JOIN "admin_changelist_child" T92 ON ("admin_changelist_parent"."id" = T92."parent_id") INNER JOIN "admin_changelist_child" T93 ON ("admin_changelist_parent"."id" = T93."parent_id") INNER JOIN "admin_changelist_child" T94 ON ("admin_changelist_parent"."id" = T94."parent_id") INNER JOIN "admin_changelist_child" T95 ON ("admin_changelist_parent"."id" = T95."parent_id") INNER JOIN "admin_changelist_child" T96 ON ("admin_changelist_parent"."id" = T96."parent_id") INNER JOIN "admin_changelist_child" T97 ON ("admin_changelist_parent"."id" = T97."parent_id") INNER JOIN "admin_changelist_child" T98 ON ("admin_changelist_parent"."id" = T98."parent_id") INNER JOIN "admin_changelist_child" T99 ON ("admin_changelist_parent"."id" = T99."parent_id") INNER JOIN "admin_changelist_child" T100 ON ("admin_changelist_parent"."id" = T100."parent_id") INNER JOIN "admin_changelist_child" T101 ON ("admin_changelist_parent"."id" = T101."parent_id") WHERE ("admin_changelist_child"."name" LIKE ? ESCAPE '\' AND T3."name" LIKE ? ESCAPE '\' AND T4."name" LIKE ? ESCAPE '\' AND T5."name" LIKE ? ESCAPE '\' AND T6."name" LIKE ? ESCAPE '\' AND T7."name" LIKE ? ESCAPE '\' AND T8."name" LIKE ? ESCAPE '\' AND T9."name" LIKE ? ESCAPE '\' AND T10."name" LIKE ? ESCAPE '\' AND T11."name" LIKE ? ESCAPE '\' AND T12."name" LIKE ? ESCAPE '\' AND T13."name" LIKE ? ESCAPE '\' AND T14."name" LIKE ? ESCAPE '\' AND T15."name" LIKE ? ESCAPE '\' AND T16."name" LIKE ? ESCAPE '\' AND T17."name" LIKE ? ESCAPE '\' AND T18."name" LIKE ? ESCAPE '\' AND T19."name" LIKE ? ESCAPE '\' AND T20."name" LIKE ? ESCAPE '\' AND T21."name" LIKE ? ESCAPE '\' AND T22."name" LIKE ? ESCAPE '\' AND T23."name" LIKE ? ESCAPE '\' AND T24."name" LIKE ? ESCAPE '\' AND T25."name" LIKE ? ESCAPE '\' AND T26."name" LIKE ? ESCAPE '\' AND T27."name" LIKE ? ESCAPE '\' AND T28."name" LIKE ? ESCAPE '\' AND T29."name" LIKE ? ESCAPE '\' AND T30."name" LIKE ? ESCAPE '\' AND T31."name" LIKE ? ESCAPE '\' AND T32."name" LIKE ? ESCAPE '\' AND T33."name" LIKE ? ESCAPE '\' AND T34."name" LIKE ? ESCAPE '\' AND T35."name" LIKE ? ESCAPE '\' AND T36."name" LIKE ? ESCAPE '\' AND T37."name" LIKE ? ESCAPE '\' AND T38."name" LIKE ? ESCAPE '\' AND T39."name" LIKE ? ESCAPE '\' AND T40."name" LIKE ? ESCAPE '\' AND T41."name" LIKE ? ESCAPE '\' AND T42."name" LIKE ? ESCAPE '\' AND T43."name" LIKE ? ESCAPE '\' AND T44."name" LIKE ? ESCAPE '\' AND T45."name" LIKE ? ESCAPE '\' AND T46."name" LIKE ? ESCAPE '\' AND T47."name" LIKE ? ESCAPE '\' AND T48."name" LIKE ? ESCAPE '\' AND T49."name" LIKE ? ESCAPE '\' AND T50."name" LIKE ? ESCAPE '\' AND T51."name" LIKE ? ESCAPE '\' AND T52."name" LIKE ? ESCAPE '\' AND T53."name" LIKE ? ESCAPE '\' AND T54."name" LIKE ? ESCAPE '\' AND T55."name" LIKE ? ESCAPE '\' AND T56."name" LIKE ? ESCAPE '\' AND T57."name" LIKE ? ESCAPE '\' AND T58."name" LIKE ? ESCAPE '\' AND T59."name" LIKE ? ESCAPE '\' AND T60."name" LIKE ? ESCAPE '\' AND T61."name" LIKE ? ESCAPE '\' AND T62."name" LIKE ? ESCAPE '\' AND T63."name" LIKE ? ESCAPE '\' AND T64."name" LIKE ? ESCAPE '\' AND T65."name" LIKE ? ESCAPE '\' AND T66."name" LIKE ? ESCAPE '\' AND T67."name" LIKE ? ESCAPE '\' AND T68."name" LIKE ? ESCAPE '\' AND T69."name" LIKE ? ESCAPE '\' AND T70."name" LIKE ? ESCAPE '\' AND T71."name" LIKE ? ESCAPE '\' AND T72."name" LIKE ? ESCAPE '\' AND T73."name" LIKE ? ESCAPE '\' AND T74."name" LIKE ? ESCAPE '\' AND T75."name" LIKE ? ESCAPE '\' AND T76."name" LIKE ? ESCAPE '\' AND T77."name" LIKE ? ESCAPE '\' AND T78."name" LIKE ? ESCAPE '\' AND T79."name" LIKE ? ESCAPE '\' AND T80."name" LIKE ? ESCAPE '\' AND T81."name" LIKE ? ESCAPE '\' AND T82."name" LIKE ? ESCAPE '\' AND T83."name" LIKE ? ESCAPE '\' AND T84."name" LIKE ? ESCAPE '\' AND T85."name" LIKE ? ESCAPE '\' AND T86."name" LIKE ? ESCAPE '\' AND T87."name" LIKE ? ESCAPE '\' AND T88."name" LIKE ? ESCAPE '\' AND T89."name" LIKE ? ESCAPE '\' AND T90."name" LIKE ? ESCAPE '\' AND T91."name" LIKE ? ESCAPE '\' AND T92."name" LIKE ? ESCAPE '\' AND T93."name" LIKE ? ESCAPE '\' AND T94."name" LIKE ? ESCAPE '\' AND T95."name" LIKE ? ESCAPE '\' AND T96."name" LIKE ? ESCAPE '\' AND T97."name" LIKE ? ESCAPE '\' AND T98."name" LIKE ? ESCAPE '\' AND T99."name" LIKE ? ESCAPE '\' AND T100."name" LIKE ? ESCAPE '\' AND T101."name" LIKE ? ESCAPE '\')) subquery ('%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%')

Obviously, a bit more hilarious when it is the same word over and over again.

[Mariusz Felisiak]

As far as I'm concerned no limit is needed anymore after 76ccce64cc3d66cfec075651c3d2239fda747dc2. To reach MySQL's limit on the number of JOIN-ed table you will need to have over 60 relations and use all of them in search fields. In that case, the OperationalError is rather desirable.