Database identifiers are not properly escaped in some queries

[Nikolay Kurevin]

Happens on 1.7.2-1.7.7, does not happens on 1.7.1

Rough setup:

class Bmodel(models.Model):
    is_active = models.BooleanField()

class Amodel(models.Model):
    active = models.BooleanField()
    bmodel = models.ForeignKey(Bmodel, related_name='Amodel_bmodel')

class Cmodel(models.Model):
    amodel = models.ForeignKey(Amodel)

Depending on order of Q filters there are 2 outcomes:

>>> models.Amodel.objects.exclude(Q(active=False, bmodel__is_active=False) & Q(cmodel__isnull=True)).query.__str__()

u'SELECT (...cut...) FROM "coreApi_amodel" INNER JOIN "coreApi_bmodel" ON ( "coreApi_amodel"."bmodel_id" = "coreApi_bmodel"."id" ) WHERE NOT ("coreApi_amodel"."active" = False AND "coreApi_bmodel"."is_active" = False AND "coreApi_amodel"."id" IN (SELECT U0."id" AS "id" FROM coreApi_amodel U0 LEFT OUTER JOIN "coreApi_cmodel" U1 ON ( U0."id" = U1."amodel_id" ) WHERE (U1."id" IS NULL AND U0."id" = (coreApi_amodel."id"))))'

>>> models.Amodel.objects.exclude(Q(active=False, bmodel__is_active=False) & Q(cmodel__isnull=True))
Traceback (most recent call last):
  File "<input>", line 1, in <module>
  File ".../.venv/src/django/django/db/models/query.py", line 116, in __repr__
    data = list(self[:REPR_OUTPUT_SIZE + 1])
  File ".../.venv/src/django/django/db/models/query.py", line 141, in __iter__
    self._fetch_all()
  File ".../.venv/src/django/django/db/models/query.py", line 966, in _fetch_all
    self._result_cache = list(self.iterator())
  File ".../.venv/src/django/django/db/models/query.py", line 265, in iterator
    for row in compiler.results_iter():
  File ".../.venv/src/django/django/db/models/sql/compiler.py", line 700, in results_iter
    for rows in self.execute_sql(MULTI):
  File ".../.venv/src/django/django/db/models/sql/compiler.py", line 786, in execute_sql
    cursor.execute(sql, params)
  File ".../.venv/src/django/django/db/backends/utils.py", line 81, in execute
    return super(CursorDebugWrapper, self).execute(sql, params)
  File ".../.venv/src/django/django/db/backends/utils.py", line 65, in execute
    return self.cursor.execute(sql, params)
  File ".../.venv/src/django/django/db/utils.py", line 94, in __exit__
    six.reraise(dj_exc_type, dj_exc_value, traceback)
  File ".../.venv/src/django/django/db/backends/utils.py", line 65, in execute
    return self.cursor.execute(sql, params)
ProgrammingError: relation "coreapi_amodel" does not exist
LINE 1: ...pi_amodel"."id" IN (SELECT U0."id" AS "id" FROM coreApi_am...
                                                             ^

Change Q order:

>>> models.Amodels.objects.exclude(Q(cmodel__isnull=True) & Q(active=False, bmodel__is_active=False)).query.__str__()

u'SELECT (...cut...) FROM "coreApi_amodel" INNER JOIN "coreApi_bmodel" ON ( "coreApi_amodel"."bmodel_id" = "coreApi_bmodel"."id" ) WHERE NOT ("coreApi_amodel"."id" IN (SELECT U0."id" AS "id" FROM "coreApi_amodel" U0 LEFT OUTER JOIN "coreApi_cmodel" U1 ON ( U0."id" = U1."amodel_id" ) WHERE U1."id" IS NULL) AND "coreApi_amodel"."active" = False AND "coreApi_bmodel"."is_active" = False)'

>>> models.Amodels.objects.exclude(Q(cmodel__isnull=True) & Q(active=False, bmodel__is_active=False))
[...results...]

[Tim Graham]

Could you please edit the ticket to include all the models and fields that your query references?

[Nikolay Kurevin]

Done.
Basic query to reproduce:

>>> models.Amodel.objects.exclude(Q(bmodel__id=False) & Q(cmodel__isnull=True)).query.__str__()

u'SELECT "TestTest_amodel"."id", "TestTest_amodel"."bmodel_id" FROM "TestTest_amodel" WHERE NOT ("TestTest_amodel"."bmodel_id" = 0 AND "TestTest_amodel"."id" IN (SELECT U0."id" AS "id" FROM TestTest_amodel U0 LEFT OUTER JOIN "TestTest_cmodel" U1 ON ( U0."id" = U1."amodel_id" ) WHERE (U1."id" IS NULL AND U0."id" = (TestTest_amodel."id"))))'

Bug is DB backend agnostic.

[Tim Graham]

Bisected to 01f2cf2aecc932d43b20b55fc19a8fa440457b5f. 1.7.x is now in security-fix only mode, but we can fix in 1.8.x.