Code

Opened 6 years ago

Closed 6 years ago

Last modified 3 years ago

#7570 closed (wontfix)

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

Reported by: Karen Tracey <kmtracey@…> Owned by: nobody
Component: Database layer (models, ORM) Version: master
Severity: Keywords:
Cc: richard.davies@… Triage Stage: Accepted
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: UI/UX:

Description

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.

Attachments (1)

t7570-r8930.diff (2.1 KB) - added by ramiro 6 years ago.
First try at a documentation addition for this issue

Download all attachments as: .zip

Change History (15)

comment:1 follow-up: Changed 6 years ago by ramiro

  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset

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.

comment:2 Changed 6 years ago by mtredinnick

  • Triage Stage changed from Unreviewed to Accepted

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.

comment:3 Changed 6 years ago by 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.

comment:4 in reply to: ↑ 1 Changed 6 years ago by 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.

comment:5 Changed 6 years ago by mtredinnick

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.

comment:6 Changed 6 years ago by mtredinnick

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

comment:7 Changed 6 years ago by Piotr Lewandowski <django@…>

  • Component changed from Uncategorized to Database wrapper

comment:8 Changed 6 years ago by Richard Davies <richard.davies@…>

  • Cc richard.davies@… added
  • Summary changed from test failure with Windows and sqlite3: ORDER BY terms must not be non-integer constants to test failure with sqlite3 version <=3.3.4 (3.3.5?), typically on Windows: ORDER BY terms must not be non-integer constants

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.

comment:9 Changed 6 years ago by ramiro

  • Summary changed from test failure with sqlite3 version <=3.3.4 (3.3.5?), typically on Windows: ORDER BY terms must not be non-integer constants to test failure with sqlite3 version < 3.3.6, typically on Windows: ORDER BY terms must not be non-integer constants

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.

comment:10 Changed 6 years ago by 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.

comment:11 Changed 6 years ago by mtredinnick

  • milestone set to 1.0

Changed 6 years ago by ramiro

First try at a documentation addition for this issue

comment:12 Changed 6 years ago by jacob

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

comment:13 Changed 6 years ago by jacob

  • Resolution set to wontfix
  • Status changed from new to closed

OK, that's done, so wontfix.

comment:14 Changed 3 years ago by jacob

  • milestone 1.0 deleted

Milestone 1.0 deleted

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.