Pagination breaks in ListView after migrating from Django 1.11 to 2.2 with SQLite

[hodossy....@gmail.com]

Hello,

I am facing the following issue after upgrading to 2.2 from 1.11. Here is the stack trace:

Traceback (most recent call last):

  File "(...)\.env\lib\site-packages\django\db\backends\utils.py", line 84, in _execute

    return self.cursor.execute(sql, params)

  File "(...)\.env\lib\site-packages\django\db\backends\sqlite3\base.py", line 383, in execute

    return Database.Cursor.execute(self, query, params)

sqlite3.OperationalError: only a single result allowed for a SELECT that is part of an expression

The above exception was the direct cause of the following exception:

Traceback (most recent call last):

  File "(...)\dashboard\deployment\tests\test_view.py", line 53, in test_display_list_view

    response = self.client.get(self.url_my_list)

  File "(...)\.env\lib\site-packages\django\test\client.py", line 535, in get

    response = super().get(path, data=data, secure=secure, **extra)

  File "(...)\.env\lib\site-packages\django\test\client.py", line 347, in get

    **extra,

  File "(...)\.env\lib\site-packages\django\test\client.py", line 422, in generic

    return self.request(**r)

  File "(...)\.env\lib\site-packages\django\test\client.py", line 503, in request

    raise exc_value

  File "(...)\.env\lib\site-packages\django\core\handlers\exception.py", line 34, in inner

    response = get_response(request)

  File "(...)\.env\lib\site-packages\django\core\handlers\base.py", line 115, in _get_response

    response = self.process_exception_by_middleware(e, request)

  File "(...)\.env\lib\site-packages\django\core\handlers\base.py", line 113, in _get_response

    response = wrapped_callback(request, *callback_args, **callback_kwargs)

  File "(...)\.env\lib\site-packages\django\views\generic\base.py", line 71, in view

    return self.dispatch(request, *args, **kwargs)

  File "(...)\.env\lib\site-packages\django\contrib\auth\mixins.py", line 52, in dispatch

    return super().dispatch(request, *args, **kwargs)

  File "(...)\.env\lib\site-packages\django\views\generic\base.py", line 97, in dispatch

    return handler(request, *args, **kwargs)

  File "(...)\.env\lib\site-packages\django\views\generic\list.py", line 157, in get

    context = self.get_context_data()

  File "(...)\dashboard\deployment\views.py", line 53, in get_context_data

    context = super().get_context_data(**kwargs)

  File "(...)\.env\lib\site-packages\django\views\generic\list.py", line 119, in get_context_data

    paginator, page, queryset, is_paginated = self.paginate_queryset(queryset, page_size)

  File "(...)\.env\lib\site-packages\django\views\generic\list.py", line 69, in paginate_queryset

    page = paginator.page(page_number)

  File "(...)\.env\lib\site-packages\django\core\paginator.py", line 70, in page

    number = self.validate_number(number)

  File "(...)\.env\lib\site-packages\django\core\paginator.py", line 48, in validate_number

    if number > self.num_pages:

  File "(...)\.env\lib\site-packages\django\utils\functional.py", line 80, in __get__

    res = instance.__dict__[self.name] = self.func(instance)

  File "(...)\.env\lib\site-packages\django\core\paginator.py", line 97, in num_pages

    if self.count == 0 and not self.allow_empty_first_page:

  File "(...)\.env\lib\site-packages\django\utils\functional.py", line 80, in __get__

    res = instance.__dict__[self.name] = self.func(instance)

  File "(...)\.env\lib\site-packages\django\core\paginator.py", line 91, in count

    return c()

  File "(...)\.env\lib\site-packages\django\db\models\query.py", line 392, in count

    return self.query.get_count(using=self.db)

  File "(...)\.env\lib\site-packages\django\db\models\sql\query.py", line 504, in get_count

    number = obj.get_aggregation(using, ['__count'])['__count']

  File "(...)\.env\lib\site-packages\django\db\models\sql\query.py", line 489, in get_aggregation

    result = compiler.execute_sql(SINGLE)

  File "(...)\.env\lib\site-packages\django\db\models\sql\compiler.py", line 1097, in execute_sql

    cursor.execute(sql, params)

  File "(...)\.env\lib\site-packages\django\db\backends\utils.py", line 67, in execute

    return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)

  File "(...)\.env\lib\site-packages\django\db\backends\utils.py", line 76, in _execute_with_wrappers

    return executor(sql, params, many, context)

  File "(...)\.env\lib\site-packages\django\db\backends\utils.py", line 84, in _execute

    return self.cursor.execute(sql, params)

  File "(...)\.env\lib\site-packages\django\db\utils.py", line 89, in __exit__

    raise dj_exc_value.with_traceback(traceback) from exc_value

  File "(...)\.env\lib\site-packages\django\db\backends\utils.py", line 84, in _execute

    return self.cursor.execute(sql, params)

  File "(...)\.env\lib\site-packages\django\db\backends\sqlite3\base.py", line 383, in execute

    return Database.Cursor.execute(self, query, params)

django.db.utils.OperationalError: only a single result allowed for a SELECT that is part of an expression

It seems that during pagination some query is made that is not supported by SQLite. Did anybody face the same issue? I am using Python 3.6 with SQLite 3.14.

Thanks in advance,

Szabolcs

[Simon Charette]

I assume you are either using a Subquery annotation that returns

multiple results or an __in lookup against a query with multiple columns.

I vaguely remember something changed in this area but it was documented

in one of the 2.0, 2.1 or 2.2 release notes.

Best,

Simon

[Szabolcs Hodossy]

Thanks for the help, I have checked those release notes, but I was not able to identify the related change (neither subquery nor __in lookup). I wanted to give you a deeper look into the code, but during that I managed to isolate the issue. It seems that the '__in' lookups previously accepted objects and automatically extracted the referenced columns in case of Foreign Key fields, but that is not valid now. I added `values('id')` everywhere, and it solved the problem. I am not sure how to feel about this change, as 'id' is automatically created.

Br,

Szabolcs

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to django-users...@googlegroups.com.
To post to this group, send email to django...@googlegroups.com.
Visit this group at https://groups.google.com/group/django-users.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/8b7a762f-4aab-4a18-b160-ee53995e0ef1%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.