Opened 18 years ago
Closed 17 years ago
#1878 closed defect (wontfix)
API tests for sqlite3 broken, count(distinct(...)) does not work on sqlite3
Reported by: | Owned by: | nobody | |
---|---|---|---|
Component: | Database layer (models, ORM) | Version: | dev |
Severity: | major | Keywords: | |
Cc: | malcolm@…, leftwing17@…, brice.carpentier@… | Triage Stage: | Accepted |
Has patch: | no | Needs documentation: | yes |
Needs tests: | yes | Patch needs improvement: | no |
Easy pickings: | no | UI/UX: | no |
Description
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 ...
Change History (5)
comment:1 by , 18 years ago
comment:2 by , 18 years ago
Cc: | added |
---|
I noticed this bug under the following conditions :
- Ubuntu Breezy (5.10) x86 edition
- sqlite3 v3.2.1-1
comment:3 by , 18 years ago
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.
comment:4 by , 18 years ago
Needs documentation: | set |
---|---|
Needs tests: | set |
Triage Stage: | Unreviewed → Accepted |
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?
comment:5 by , 17 years ago
Resolution: | → wontfix |
---|---|
Status: | new → closed |
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.
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.
(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.