-1

I'm in the process of upgrading from Django 1.8.19 to 1.11.15 and I've found a piece of code which is breaking.

In particular this query is doing something different to what it did before.

project_groups = brand.project_groups.prefetch_related(
   'project', 'project__score', 'project__themes').filter(
   project=projects
).distinct()

Previously (in Django 1.8), according to the output of "project_groups.query" it produced SQL including:

... projectgroup.project_id IN [projects query]

Now it produces SQL reading:

... projectgroup.project_id = [projects query]

This breaks as the [projects query] returns more than one row. So I get a:

ProgrammingError: more than one row returned by a subquery used as an expression

The only changes I've made to the code for this upgrade are to models and migrations to use ArrayField and HStoreField from django.contrib.postgres.fields instead of the django_hstore equivalents.

My guess is that the original code was wrong, but worked due to a bug in Django (filter/prefetch_related) which has now been fixed. Is that likely to be correct? If this is in fact a new bug in Django I don't want to write code which relies on it!

James Bradbury
  • 1,708
  • 1
  • 19
  • 31

1 Answers1

1

There was a change to field lookups behaviour of Django between 1.8 and 1.9 that explains this - you can see the details at Django ticket #25284.

In Django 1.8 a query such as Model.objects.filter(related_id = RelatedModel.objects.all()) used to result in an implicit __in lookup so the SQL query contained related_id IN (SELECT id FROM ...). But in Django 1.9 the "IN" is changed to an "=", which causes the query to break in MySql and Postgres. The change was classed a bug fix as the implicit "IN" behaviour was undocumented and probably accidental.

You should be able to fix the query fairly easily by adding an explicit lookup type to the field lookup such as .filter(project__in=projects) - see Django Documentation: Field Lookups

Lady F
  • 36
  • 2