Opened 5 years ago

Closed 5 years ago

#24148 closed Bug (fixed)

test_combined_expression test failure on Windows/Python 2

Reported by: Tim Graham Owned by: Michał Modzelewski
Component: Database layer (models, ORM) Version: master
Severity: Release blocker Keywords:
Cc: Michał Modzelewski Triage Stage: Accepted
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

=====================================================================
FAIL: test_combined_expression (expressions_case.tests.CaseExpressionTests)
----------------------------------------------------------------------
Traceback (most recent call last):
  File "c:\Users\Tim\code\django\tests\expressions_case\tests.py", line 257, in
test_combined_expression
    transform=attrgetter('integer', 'test')
  File "c:\users\tim\code\django\django\test\testcases.py", line 883, in assertQ
uerysetEqual
    return self.assertEqual(list(items), values, msg=msg)
AssertionError: Lists differ: [(1, 3), (2, 3), (3, 4), (2, 3... != [(1, 3), (2,
2), (3, 4), (2, 2...

First differing element 1:
(2, 3)
(2, 2)

- [(1, 3), (2, 3), (3, 4), (2, 3), (3, 4), (3, 4), (4, 4)]
?              ^               ^

+ [(1, 3), (2, 2), (3, 4), (2, 2), (3, 4), (3, 4), (4, 4)]
?              ^               ^

No problem on Python 3.4.

Change History (6)

comment:1 Changed 5 years ago by Tim Graham

Cc: Michał Modzelewski added

Michał, any chance you have access to Windows and can reproduce this? If not, I can give you access to a system if you're willing to help debug this.

comment:2 Changed 5 years ago by Michał Modzelewski

Owner: changed from nobody to Michał Modzelewski
Status: newassigned

Reproduced. I'll look into it.

comment:3 Changed 5 years ago by Michał Modzelewski

I've had a look and it's not pretty. The SQL generated in pythons 2.7, 3.2, 3.3 and 3.4 on windows is exactly the same.

sqlite3.version is 2.6.0 on all of them.
What differs is sqlite3.sqlite_version:

  • py2.7: 3.6.21
  • py3.2: 3.7.4
  • py3.3: 3.7.12
  • py3.4: 3.8.3.1

I installed pysqlite from http://www.lfd.uci.edu/~gohlke/pythonlibs/ (2.6.3 / 3.8.5) and the test passes.

What is happening? SQLite is adding the value in the lookup to the value in the then.
You can see this better with a different example (with the same test data as the testcase):

>>> qs = CaseTestModel.objects.annotate(
...     test=Case(
...         When(integer=1, then=Value(1)),
...         When(integer=2, then=Value(1)),
...         When(integer=3, then=Value(1)),
...         When(integer=4, then=Value(10)),
...         default=Value(1),
...         output_field=models.IntegerField(),
...     ) + 100,
... ).order_by('pk')
>>> # expected: [(1, 101), (2, 101), (3, 101), (2, 101), (3, 101), (3, 101), (4, 110)]
>>> [(o.integer, o.test) for o in qs]
[(1, 2), (2, 3), (3, 4), (2, 3), (3, 4), (3, 4), (4, 14)]

Removing the default fixes it (since all the cases are taken care of)

>>> qs = CaseTestModel.objects.annotate(
...     test=Case(
...         When(integer=1, then=Value(1)),
...         When(integer=2, then=Value(1)),
...         When(integer=3, then=Value(1)),
...         When(integer=4, then=Value(10)),
...         # default=Value(1),
...         output_field=models.IntegerField(),
...     ) + 100,
... ).order_by('pk')
>>> # expected: [(1, 101), (2, 101), (3, 101), (2, 101), (3, 101), (3, 101), (4, 110)]
>>> [(o.integer, o.test) for o in qs]
[(1, 101), (2, 101), (3, 101), (2, 101), (3, 101), (3, 101), (4, 110)]

Also, if I send the query as a string without the placeholders it works. So I assume that SQLite 3.6.21 is mixing up the query parameters. Not sure if the error is specific to this version, but it seems there was a query related bug fix in 3.6.22 (http://www.sqlite.org/changes.html#version_3_6_22 ).

Unfortunately, I checked all the windows python versions from 2.7a1 to 2.7.9 and they all have the same sqlite3.sqlite_version, so there's nothing we can do here other than mark the test as an expected failure and recommend pysqlite on windows.

Last edited 5 years ago by Michał Modzelewski (previous) (diff)

comment:4 Changed 5 years ago by Tim Graham

That resolution is fine with me.

comment:5 Changed 5 years ago by Michał Modzelewski

Has patch: set

I've done some testing, and SQLite 3.7.0 is the first version that passes this test case.
PR is ready.

comment:6 Changed 5 years ago by Tim Graham <timograham@…>

Resolution: fixed
Status: assignedclosed

In 39b58ad95ade8109de0f0ae3930e333b7f689c0f:

Fixed #24148 -- Documented a bug with case expressions in SQLite < 3.7.0

Note: See TracTickets for help on using tickets.
Back to Top