#24148 (test_combined_expression test failure on Windows/Python 2) – Django (original) (raw)

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:

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.