exclude with OneToOneField produces incorrect results on sqlite

[Raphael Kimmig]

When excluding instances where the related model has a certain field value
instances where no related model exists are excluded as well. This happens
with sqlite 3.16.2 on mac os.

Using postgresql with psycopg2 I was unable to reproduce the issue.

from django.db import models


class A(models.Model):
    pass


class B(models.Model):
    a = models.OneToOneField(A)
    value = models.IntegerField()


def test():
    A.objects.all().delete()
    B.objects.all().delete()

    A.objects.create()
    assert A.objects.exclude(b__value=12345).count() == 1, "We expect to get one A but got zero."

[Tim Graham]

I can't reproduce the issue give what you provided. Can you do any further investigation such as checking the query that Django generates? Not sure if it could be a bug in your particular version of SQLite.

[Raphael Kimmig]

I just tried reproducing the bug on Debian 9 and I can't.
I'm running mac os 10.12.6 on the affected machine.

The query produced looks fine:

 SELECT "bar_a"."id" FROM "bar_a" LEFT OUTER JOIN "bar_b" ON ("bar_a"."id" = "bar_b"."a_id") WHERE NOT ("bar_b"."value" = 12345 AND "bar_b"."value" IS NOT NULL) LIMIT 21

sqlite version on the affected mac:

sqlite3.version
>>> '2.6.0'
sqlite3.sqlite_version
>>> '3.23.0'

Is there anything else that might be helpful?

[Tim Graham]

I noticed "Fix two problems in the new LEFT JOIN strength reduction optimization." in the ​SQLite 3.23.1 release notes which looks like it could be the cause.