test failure with sqlite3 version < 3.3.6, typically on Windows: ORDER BY terms must not be non-integer constants

[Karen Tracey <kmtracey@…>]

Running trunk r7787 on WinXP, Python2.5.2, the queries regression tests fail using sqlite3:

C:\u\kmt\django\trunk\tests>runtests.py --settings=testsettings queries
======================================================================
FAIL: Doctest: regressiontests.queries.models.__test__.API_TESTS
----------------------------------------------------------------------
Traceback (most recent call last):
  File "c:\u\kmt\django\trunk\django\test\_doctest.py", line 2180, in runTest
    raise self.failureException(self.format_failure(new.getvalue()))
AssertionError: Failed doctest test for regressiontests.queries.models.__test__.API_TESTS
  File "C:\u\kmt\django\trunk\tests\regressiontests\queries\models.py", line unknown line number, in API_TESTS

----------------------------------------------------------------------
File "C:\u\kmt\django\trunk\tests\regressiontests\queries\models.py", line ?, in regressiontests.queries.models.__test__.API_TESTS
Failed example:
    Item.objects.dates('created', 'day').extra(select={'a': 1})
Exception raised:
    Traceback (most recent call last):
      File "c:\u\kmt\django\trunk\django\test\_doctest.py", line 1267, in __run
        compileflags, 1) in test.globs
      File "<doctest regressiontests.queries.models.__test__.API_TESTS[169]>", line 1, in <module>
        Item.objects.dates('created', 'day').extra(select={'a': 1})
      File "c:\u\kmt\django\trunk\django\db\models\query.py", line 129, in __repr__
        return repr(list(self))
      File "c:\u\kmt\django\trunk\django\db\models\query.py", line 141, in __len__
        self._result_cache.extend(list(self._iter))
      File "c:/u/kmt/django/trunk\django\db\models\sql\subqueries.py", line 351, in results_iter
        for rows in self.execute_sql(MULTI):
      File "c:\u\kmt\django\trunk\django\db\models\sql\query.py", line 1607, in execute_sql
        cursor.execute(sql, params)
      File "c:\u\kmt\django\trunk\django\db\backends\sqlite3\base.py", line 136, in execute
        return Database.Cursor.execute(self, query, params)
    OperationalError: ORDER BY terms must not be non-integer constants


----------------------------------------------------------------------
Ran 1 test in 0.859s

FAILED (failures=1)

C:\u\kmt\django\trunk\tests>

I do not get this failure using sqlite3 on Linux for the tests, nor using MySQL on Windows. So it's something specific to sqlite3 on Windows. Recreating in the shell and showing the query that generates the error:

>>> Item.objects.dates('created', 'day').extra(select={'a': 1})
Traceback (most recent call last):
  File "<console>", line 1, in <module>
  File "c:\u\kmt\django\trunk\django\db\models\query.py", line 129, in __repr__
    return repr(list(self))
  File "c:\u\kmt\django\trunk\django\db\models\query.py", line 141, in __len__
    self._result_cache.extend(list(self._iter))
  File "c:/u/kmt/django/trunk\django\db\models\sql\subqueries.py", line 351, in results_iter
    for rows in self.execute_sql(MULTI):
  File "c:\u\kmt\django\trunk\django\db\models\sql\query.py", line 1607, in execute_sql
    cursor.execute(sql, params)
  File "c:\u\kmt\django\trunk\django\db\backends\util.py", line 18, in execute
    return self.cursor.execute(sql, params)
  File "c:\u\kmt\django\trunk\django\db\backends\sqlite3\base.py", line 136, in execute
    return Database.Cursor.execute(self, query, params)
OperationalError: ORDER BY terms must not be non-integer constants
>>> connection.queries[-1]
{'time': '0.000', 'sql': u'SELECT DISTINCT (1) AS "a", django_date_trunc("day", "testfail_item"."created") FROM "testfail_item" ORDER BY 1 ASC'}
>>>

doesn't clarify anything for me. I suspect this is a bug in the sqlite3/Windows combo rather than a bug in Django and I'm not even sure it's worth tracking down. However it would be nice if the test suite could run without error for this combo, so if there was a way to rewrite this test to still test whatever it's supposed to be testing but not trigger this failure on Windows that might be good? Only I don't quite understand what this test is trying to test, exactly, so I'm not sure how to even begin tweaking it to still do what it's intended to do without tripping over this sqlite3/Windows failure.

[Ramiro Morales]

Karen, take a look at the DjangoOnWindows wikipage I've just created. Didn't dare to open a ticket because I wasn't sure win32 is really a supported platform.

[Malcolm Tredinnick]

It seems to be saying that you can't use ordinal values (the integer) in order-by clauses, which will be a bit of a problem (we use them in a couple of places in the query construction code). If that is indeed the case, then it's not just a failing test, it's indicative of a problem in the query construction code (well, it's an SQLite limitation, but one that Django trips over).

Worth at least thinking about if we can work around it, although, from memory, the places where we use ordinal values are made quite a bit easier by that fact.

[Karen Tracey <kmtracey@…>]

I have a hard time understanding what it's trying to say.

"ORDER BY terms must not be non-integer constants"

removing the double negative:

ORDER BY terms must be integer constants

is an equivalent statement, right?

but the query says "ORDER BY 1". Isn't 1 an integer constant? I'm long overdue for some food so it's quite possible my mind is not working right.

Note it's only failing on Windows, the same test on Linux/sqlite3 works.

[Karen Tracey <kmtracey@…>]

Replying to ramiro:

Karen, take a look at the DjangoOnWindows wikipage I've just created. Didn't dare to open a ticket because I wasn't sure win32 is really a supported platform.

Oh, you've already figured it all out, down to concluding it's been fixed in a more recent version of sqlite than is bundled with Python on Windows. So maybe your suggestion for changing how Django imports the support is the right way to solve this. Then it's up to the users to upgrade their sqlite if necessary.

Don't fear opening tickets for stuff like this, win32 isn't the most popular platform but it is supported. The core devs may be limited in terms of being able to produce/test fixes for the platform since I don't think any of them use Windows, but they certainly accept fixes from the community when they are provided.

[Malcolm Tredinnick]

Karen, you're right. I misread all the negations. So I don't understand the problem, either. I feel comfortable with the import order fix on the DjangoOnWindows page, since it's asking for trouble with outdated. In theory, the python2.5 installation is going to be updated regularly and people won't worry about the pysqlite2 stuff, since "that's included with Python". Trading one bag of worms for another one of a different colour doesn't feel like a permanent solution.

Understanding what the error message means will be helpful here. Worst case, we have to blacklist a version or two of sqlite based on the OS, but I'd prefer to work around it more permanently, if we can. It's not like we aren't leaping through hoops for other backends at times; SQLite is exceedingly well-behaved, in general.

[Malcolm Tredinnick]

doh! Hit "submit" instead of "preview". I do not feel comfortable with the proposed fix on the DjangoOnWindows page, that should have said.

[Richard Davies <richard.davies@…>]

I have encountered the same test failure on (home-rolled distribution) Linux with sqlite3 version 3.2.7 (python 2.5.2).

I don't get the failure on a Ubuntu Linux box with sqlite3 version 3.4.2 (python 2.5.2).

The DjangoOnWindows page has a few Linux tests, but none on Linux with early sqlite versions.

So, I am renaming this ticket, since the problem appears to be specific to early sqlite, not to Windows.

[Ramiro Morales]

Ok, so we know it works with SQLite 3.3.6 and fails with 3.3.4.

I think it's a SQLite bug reported in this ​ticket and fixed in this ​commit six days after 3.3.5 was tagged so it would be safe to assume it is also affected.

This is the complete changelog between 3.3.4 and 3.3.6: ​http://www.sqlite.org/cvstrac/timeline?d=115&e=2006-Jun-06&c=2&px=&s=9&dm=1&x=1

Conclusion?: Should we document that SQLite 3.3.6 or newer is required?.

Just to put things on perspective: As of r8747 the only tests failing under Windows (XP 32 bits) + official Python 2.5.2 (includes SQLite 3.3.4) are this one and the one reported in #8196.

[Richard Davies <richard.davies@…>]

With 1.0rc1 there are also two more similar failures in regressiontests/extra_regress:

======================================================================
FAIL: Doctest: regressiontests.extra_regress.models.__test__.API_TESTS
----------------------------------------------------------------------
Traceback (most recent call last):
  File "/home/elastic/django-dev/trunk/django/test/_doctest.py", line 2180, in runTest
    raise self.failureException(self.format_failure(new.getvalue()))
AssertionError: Failed doctest test for regressiontests.extra_regress.models.__test__.API_TESTS
  File "/home/elastic/django-dev/trunk/tests/regressiontests/extra_regress/models.py", line unknown line number, in API_TESTS

----------------------------------------------------------------------
File "/home/elastic/django-dev/trunk/tests/regressiontests/extra_regress/models.py", line ?, in regressiontests.extra_regress.models.__test__.API_TESTS
Failed example:
    User.objects.extra(select={'extra_field': 1}, order_by=['extra_field'])
Exception raised:
    Traceback (most recent call last):
      File "/home/elastic/django-dev/trunk/django/test/_doctest.py", line 1267, in __run
        compileflags, 1) in test.globs
      File "<doctest regressiontests.extra_regress.models.__test__.API_TESTS[26]>", line 1, in <module>
        User.objects.extra(select={'extra_field': 1}, order_by=['extra_field'])
      File "/home/elastic/django-dev/trunk/django/db/models/query.py", line 144, in __repr__
        return repr(list(self))
      File "/home/elastic/django-dev/trunk/django/db/models/query.py", line 156, in __len__
        self._result_cache.extend(list(self._iter))
      File "/home/elastic/django-dev/trunk/django/db/models/query.py", line 269, in iterator
        for row in self.query.results_iter():
      File "/home/elastic/django-dev/trunk/django/db/models/sql/query.py", line 206, in results_iter
        for rows in self.execute_sql(MULTI):
      File "/home/elastic/django-dev/trunk/django/db/models/sql/query.py", line 1700, in execute_sql
        cursor.execute(sql, params)
      File "/home/elastic/django-dev/trunk/django/db/backends/sqlite3/base.py", line 167, in execute
        return Database.Cursor.execute(self, query, params)
    OperationalError: ORDER BY terms must not be non-integer constants
----------------------------------------------------------------------
File "/home/elastic/django-dev/trunk/tests/regressiontests/extra_regress/models.py", line ?, in regressiontests.extra_regress.models.__test__.API_TESTS
Failed example:
    User.objects.extra(select={'extra_field': 1}, order_by=['extra_field']).distinct()
Exception raised:
    Traceback (most recent call last):
      File "/home/elastic/django-dev/trunk/django/test/_doctest.py", line 1267, in __run
        compileflags, 1) in test.globs
      File "<doctest regressiontests.extra_regress.models.__test__.API_TESTS[27]>", line 1, in <module>
        User.objects.extra(select={'extra_field': 1}, order_by=['extra_field']).distinct()
      File "/home/elastic/django-dev/trunk/django/db/models/query.py", line 144, in __repr__
        return repr(list(self))
      File "/home/elastic/django-dev/trunk/django/db/models/query.py", line 156, in __len__
        self._result_cache.extend(list(self._iter))
      File "/home/elastic/django-dev/trunk/django/db/models/query.py", line 269, in iterator
        for row in self.query.results_iter():
      File "/home/elastic/django-dev/trunk/django/db/models/sql/query.py", line 206, in results_iter
        for rows in self.execute_sql(MULTI):
      File "/home/elastic/django-dev/trunk/django/db/models/sql/query.py", line 1700, in execute_sql
        cursor.execute(sql, params)
      File "/home/elastic/django-dev/trunk/django/db/backends/sqlite3/base.py", line 167, in execute
        return Database.Cursor.execute(self, query, params)
    OperationalError: ORDER BY terms must not be non-integer constants

My conclusion would also be that we should just close this ticket as 'wontfix' and document that SQLite 3.3.6 or newer is required.

[Ramiro Morales]

First try at a documentation addition for this issue

[Jacob]

(In [8950]) Added a note about SQLite versions before 3.3.6. Refs #7570. Thanks, ramiro.

[Jacob]

OK, that's done, so wontfix.

[Jacob]

Milestone 1.0 deleted