#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: | Owned by: | nobody | |
---|---|---|---|
Component: | Database layer (models, ORM) | Version: | dev |
Severity: | Keywords: | ||
Cc: | richard.davies@… | Triage Stage: | Accepted |
Has patch: | no | Needs documentation: | no |
Needs tests: | no | Patch needs improvement: | no |
Easy pickings: | no | UI/UX: | no |
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)
Change History (15)
follow-up: 4 comment:1 by , 16 years ago
comment:2 by , 16 years ago
Triage Stage: | Unreviewed → 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 by , 16 years ago
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 by , 16 years ago
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 by , 16 years ago
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 by , 16 years ago
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 by , 16 years ago
Component: | Uncategorized → Database wrapper |
---|
comment:8 by , 16 years ago
Cc: | added |
---|---|
Summary: | test failure with Windows and sqlite3: ORDER BY terms must not be non-integer constants → 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 by , 16 years ago
Summary: | test failure with sqlite3 version <=3.3.4 (3.3.5?), typically on Windows: ORDER BY terms must not be non-integer constants → 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 by , 16 years ago
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 by , 16 years ago
milestone: | → 1.0 |
---|
by , 16 years ago
Attachment: | t7570-r8930.diff added |
---|
First try at a documentation addition for this issue
comment:12 by , 16 years ago
comment:13 by , 16 years ago
Resolution: | → wontfix |
---|---|
Status: | new → closed |
OK, that's done, so wontfix.
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.