Code

Opened 8 years ago

Closed 7 years ago

#1878 closed defect (wontfix)

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

Reported by: mir@… Owned by: nobody
Component: Database layer (models, ORM) Version: master
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: UI/UX:

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

Attachments (0)

Change History (5)

comment:1 Changed 8 years ago by 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.

comment:2 Changed 8 years ago by brice.carpentier@…

  • Cc brice.carpentier@… added

I noticed this bug under the following conditions :

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

comment:3 Changed 8 years ago by mtredinnick

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 Changed 7 years ago by SmileyChris

  • Needs documentation set
  • Needs tests set
  • Triage Stage changed from Unreviewed to 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 Changed 7 years ago by mtredinnick

  • Resolution set to wontfix
  • Status changed from new to 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.

Add Comment

Modify Ticket

Change Properties
<Author field>
Action
as closed
as The resolution will be set. Next status will be 'closed'
The resolution will be deleted. Next status will be 'new'
Author


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

 
Note: See TracTickets for help on using tickets.