Lookups using m2m relationships return incorrect results with Sqlite on Windows

[Manoj Govindan <egmanoj@…>]

Consider the following model

from django.db import models
from django.contrib.auth.models import User

class Book(models.Model):
    name = models.CharField(max_length = 255, unique = True)
    users = models.ManyToManyField(User)

m2m lookups using username attribute of User are returning incorrect results with Sqlite only on Windows. It seems that the filtering condition is being ignored and all books are being returned for any given username.

Book.objects.filter(users__username = <some username> )

as well as

Book.objects.filter(users__username_exact = <some username> )

are not working properly.

I have added tests for the same.

[Manoj Govindan <egmanoj@…>]

Tests for the defect.

[Malcolm Tredinnick]

For those of us not running Windows, what results *do* you see when you run this test? Which test fails?

Also, which version of SQLite are you using to test this?

[Manoj Govindan <egmanoj@…>]

Replying to mtredinnick:

For those of us not running Windows, what results *do* you see when you run this test? Which test fails?

Four out of the five tests I have added are failing. The first one is a look up of all books. That works as expected. Subsequent tests are look ups of books based on user names, both with and without the __exact check.

Results can be seen below.

======================================================================
FAIL: Doctest: modeltests.m2m_sqlite_windows.models.__test__.API_TESTS
----------------------------------------------------------------------
Traceback (most recent call last):
  File "C:\Python25\lib\site-packages\django\test\_doctest.py", line 2180, in ru
nTest
    raise self.failureException(self.format_failure(new.getvalue()))
AssertionError: Failed doctest test for modeltests.m2m_sqlite_windows.models.__t
est__.API_TESTS
  File "D:\manoj\projects\django_tests\modeltests\m2m_sqlite_windows\models.py",
 line unknown line number, in API_TESTS

----------------------------------------------------------------------
File "D:\manoj\projects\django_tests\modeltests\m2m_sqlite_windows\models.py", l
ine ?, in modeltests.m2m_sqlite_windows.models.__test__.API_TESTS
Failed example:
    Book.objects.filter(users__username = 'twoface')
Expected:
    [<Book: My Lives: Two Face>, <Book: Why we don't need Batman>]
Got:
    [<Book: My Lives: Two Face>, <Book: Why we don't need Batman>, <Book: Global
 Warming: The Looming Danger>]
----------------------------------------------------------------------
File "D:\manoj\projects\django_tests\modeltests\m2m_sqlite_windows\models.py", l
ine ?, in modeltests.m2m_sqlite_windows.models.__test__.API_TESTS
Failed example:
    Book.objects.filter(users__username__exact = 'twoface')
Expected:
    [<Book: My Lives: Two Face>, <Book: Why we don't need Batman>]
Got:
    [<Book: My Lives: Two Face>, <Book: Why we don't need Batman>, <Book: Global
 Warming: The Looming Danger>]
----------------------------------------------------------------------
File "D:\manoj\projects\django_tests\modeltests\m2m_sqlite_windows\models.py", l
ine ?, in modeltests.m2m_sqlite_windows.models.__test__.API_TESTS
Failed example:
    Book.objects.filter(users__username = 'penguin')
Expected:
    [<Book: Global Warming: The Looming Danger>]
Got:
    [<Book: My Lives: Two Face>, <Book: Why we don't need Batman>, <Book: Global
 Warming: The Looming Danger>]
----------------------------------------------------------------------
File "D:\manoj\projects\django_tests\modeltests\m2m_sqlite_windows\models.py", l
ine ?, in modeltests.m2m_sqlite_windows.models.__test__.API_TESTS
Failed example:
    Book.objects.filter(users__username__exact = 'penguin')
Expected:
    [<Book: Global Warming: The Looming Danger>]
Got:
    [<Book: My Lives: Two Face>, <Book: Why we don't need Batman>, <Book: Global
 Warming: The Looming Danger>]


----------------------------------------------------------------------
Ran 1 test in 0.437s

FAILED (failures=1)

Also, which version of SQLite are you using to test this?

I am using v3.5.4

Interestingly enough the generated queries for the lookups worked fine when I executed them using the sqlite client.

[Manoj Govindan <egmanoj@…>]

This bug seems to have been fixed in revision 7540. At least I could not reproduce it and the tests are passing. Can someone verify and close the ticket please?

[Karen Tracey <kmtracey@…>]

Confirmed this has been fixed. I was able to recreate the error on r7092 (which was probably approximately the 'current' revision when this ticket was opened) and my Windows XP box. The same tests run without errors using today's trunk (r7787).