API tests for sqlite3 broken, count(distinct(...)) does not work on sqlite3

[mir@…]

sqlite3 appears not to understand select count(distinct(...)). Runtests shows:

Running tests with database 'sqlite3'

'many_to_many' module: API test raised an exception
===================================================
Code: 'Article.objects.filter(publications__title__startswith="Science").distinct().count()'
Line: 58
Exception:   File "/home/mir/src/django/svn-trunk/tests/doctest.py", line 1243, in __run
    compileflags, 1) in test.globs
  File "<doctest many_to_many[25]>", line 1, in ?
    Article.objects.filter(publications__title__startswith="Science").distinct().count()
  File "/home/mir/src/django/active/django/db/models/query.py", line 188, in count
    cursor.execute("SELECT COUNT(DISTINCT(%s))" % id_col + sql, params)
  File "/home/mir/src/django/active/django/db/backends/sqlite3/base.py", line 73, in execute
    return Database.Cursor.execute(self, query, params)
OperationalError: near "DISTINCT": syntax error


'many_to_one' module: API test raised an exception
==================================================
Code: "Reporter.objects.filter(article__headline__startswith='This').distinct().count()"
Line: 183
Exception:   File "/home/mir/src/django/svn-trunk/tests/doctest.py", line 1243, in __run
    compileflags, 1) in test.globs
  File "<doctest many_to_one[69]>", line 1, in ?
    Reporter.objects.filter(article__headline__startswith='This').distinct().count()
  File "/home/mir/src/django/active/django/db/models/query.py", line 188, in count
    cursor.execute("SELECT COUNT(DISTINCT(%s))" % id_col + sql, params)
  File "/home/mir/src/django/active/django/db/backends/sqlite3/base.py", line 73, in execute
    return Database.Cursor.execute(self, query, params)
OperationalError: near "DISTINCT": syntax error

This probably got introduced by changeset 2902 / ticket #1530

I got into the habit to check tests after each merge with my patches ...

[Malcolm Tredinnick <malcolm@…>]

Except that I did check the tests. I use sqlite3 normally and it's my default for testing. I also checked with PostgreSQL and MySQL.

.-(malcolm@counterweight 20:08:33) ~/BleedingEdge/django_src/tests
`--> ./runtests.py --settings=settings
Running tests with database 'sqlite3'

'markup' module: Error while importing
======================================
  File "./runtests.py", line 196, in run_tests
    mod = __import__("othertests." + module, '', '', [''])
  File "/home/malcolm/BleedingEdge/django_src_current/tests/othertests/markup.py", line 50, in ?
    assert rendered == """<p>Paragraph 1</p><h2>An h2</h2>"""
AssertionError

1 error:

(and that one error is unrelated and unavoidable, since markdown's output format has changed between versions and it's not easily possible to detect the module version.)

So the question now becomes, which version of SQLite, pysqlite2 and what OS are you testing on. We've seen version differences with SQLite previously, so we should try and track this down. From the SQLite changelog, it looks like COUNT(DISTINCT(...)) support was added in September 2005 (I has not realised it was that recently... my impression was it had been there for about a year).

I'll have a think about what a good solution here is. Returning the wrong answer from QuerySet.count() is bad, but returning different answers in different installations is not ideal, either.

[brice.carpentier@…]

I noticed this bug under the following conditions :

• Ubuntu Breezy (5.10) x86 edition
• sqlite3 v3.2.1-1

[Malcolm Tredinnick]

Shortly after this bug was filed, we established that an SQLite version of at least 3.2.6 was required here. There is a discussion in ​this thread on django-developers about possible work arounds for people with less function sqlite versions. No clear resolution yet, but I should kickstart this again.

[Chris Beaven]

Sounds like a bug to me. Exact resolution has not been defined, but I'll still accept.

I'm for the suggestion on the developers thread:

We don't support QuerySet.distinct().count() on older versions of
SQLite. We can make the tests conditional, so that if you get the
"unsupported" error, it just informs you (raises an exception?). We
might even be able to make the tests always pass.

Obviously needs tests :) Probably documentation too?

[Malcolm Tredinnick]

Support for count(distinct(...)) was added to SQLite over two years ago now. I think we can ask that people wanting to use this feature just use a sufficiently recent release.