Query with select_related and defer on MySQL causes id field to be returned as bool

[Aleksey Kladov]

I selected an object obj by a query with select_related and defer, to find out that obj.user.id is True instead of 1. Technically, True == 1, but it doesn't work with, for example, url reversing. I managed to reproduce this behaviour on MySQL, python2 and python3. With SQLite I get 1 as expected. Here is a test case that show this surprising behaviour:

models.py

from django.conf import settings
from django.db import models

class A(models.Model):
    dspam = models.TextField()
    eggs = models.BooleanField(default=False)

class B(models.Model):
    a = models.ForeignKey(A)

class C(models.Model):
    b = models.ForeignKey(B)

class D(models.Model):
    c = models.ForeignKey(C)
    dfoo = models.TextField()
    dbar = models.TextField()

    user = models.ForeignKey(settings.AUTH_USER_MODEL, null=True)
    time = models.DateTimeField(null=True)

class E(models.Model):
    d = models.ForeignKey(D)
    dbaz = models.TextField()

test.py

from django.contrib.auth.models import User
from django.test import TestCase
from .models import *

class Test(TestCase):
    def setUp(self):
        self.a = A.objects.create()
        self.b = B.objects.create(a=self.a)
        self.c = C.objects.create(b=self.b)
        self.user = User.objects.create_user("foo", "foo.bar@example.com", password="424242", id=1)
        self.d = D.objects.create(c=self.c, user=self.user)
        self.e = E.objects.create(d=self.d)

    def test(self):
        qs = (E.objects
              .select_related('d__user', 'd__c__b__a')
              .filter(d__c__b=self.b, d__user_id=1)
              .defer('dbaz', 'd__dfoo', 'd__dbar',
                     'd__c__b__a__dspam'))
        user = qs[0].d.user
        print(user.id)  # prints True. Technically it is an Int, but bools don't work with, for example, url reversing

pip freeze

Django==1.6.1
MySQL-python==1.2.5
wsgiref==0.1.2

[Russell Keith-Magee]

For anyone digging into this - it's possible this is a bug with select_related, but keep in mind that it's not out of the question that it might also be a… ahem… feature… of MySQL.

See #16809 for an example of the sort of thing MySQL will do if you give it enough rope. Check to see if this is a problem on other databases.

[Michael Manfre]

Django has had some issues with field alignment when using select_related and defer. See #21203. The fix was back patched to 1.6.x, but perhaps there has been a subsequent regression.

[Anssi Kääriäinen]

Seems like patch 9918c11114ac3ec9622631558ef26ebf3919cb69 (#21413) should be backpatched from master. I don't recall why I didn't do that originally, maybe I just forgot to do that.

See ​https://github.com/akaariai/django/compare/django:stable%2F1.6.x...ticket_21903_16 for a branch having tests for this ticket + backpatch of #21413. I think that there isn't point in adding this ticket's tests. The backpatch has a much simpler test case. I'll mark this RFC, but I'll wait a little bit for possible reviewers.

As for why these bugs happen - the compiler.resolve_columns() is trying to mimic what SELECT clause generation is doing. And, unfortunately the SELECT clause generation is complex (caused by defer, select_related, annotations and extra). In the long term we should probably move to something where select setup generates a list of "col_sql, alias, field", and then resolve_columns could just use that list.

[anonymous]

+1 for getting this ticket into a 1.6 point release soon. We just ran into this problem in production. We applied the above-mentioned patch to the 1.6.1 sources and have deployed this patched version. We are no longer running into the mismatched result row/field tuples.

[eswenson]

Didn't mean to be anonymous.

[Anssi Kääriäinen]

I committed the above mentioned patch to 1.6.x, so this should get fixed in 1.6.2.