Nested queries support: Field for ValuesListQuerySet (flat=True) gets lost in query construction

[Matthias Kestenholz]

I have a model called Favorite which has a generic foreign key. I want to get all bookmarked jobs for a user efficiently, therefore I query the database for all object_ids:

ids = Favorite.objects.get_favorites_by_model(Job).filter(user=1).values_list('object_id', flat=True)

In [28]: ids
Out[28]: [338L, 308L, 212L, 345L, 307L, 264L, 322L, 354L, 326L, 328L, 335L, 334L, 332L, 353L, 352L, 351L, 350L, 349L, 316L]

In [33]: type(ids)
Out[33]: <class 'django.db.models.query.ValuesListQuerySet'>

Now, using this information I want to get a list of all jobs. However, the following two statements yield completely different results. Only the former is correct:

In [22]: Job.objects.filter(id__in=tuple(ids))
Out[22]: [<Job....

In [20]: Job.objects.filter(id__in=ids)
Out[20]: [<Job....

The two corresponding SQL statements are:

{'sql': u'SELECT jobs_job.id, jobs_job.created, jobs_job.modified, jobs_job.created_by_id, jobs_job.name, jobs_job.notes, jobs_job.job_id, jobs_job.customer_id, jobs_job.contact_id, jobs_job.project_id, jobs_job.state, jobs_job.manager_id, jobs_job.no_profit, jobs_job.prepayment, jobs_job.archived_on FROM jobs_job WHERE jobs_job.id IN (338, 308, 212, 345, 307, 264, 322, 354, 326, 328, 335, 334, 332, 353, 352, 351, 350, 349, 316) ORDER BY jobs_job.job_id DESC LIMIT 21',

'time': '0.001'}

{'sql': u'SELECT jobs_job.id, jobs_job.created, jobs_job.modified, jobs_job.created_by_id, jobs_job.name, jobs_job.notes, jobs_job.job_id, jobs_job.customer_id, jobs_job.contact_id, jobs_job.project_id, jobs_job.state, jobs_job.manager_id, jobs_job.no_profit, jobs_job.prepayment, jobs_job.archived_on FROM jobs_job WHERE jobs_job.id IN (SELECT U0.id FROM favorites_favorite U0 WHERE (U0.content_type_id = 20 AND U0.user_id = 1 )) ORDER BY jobs_job.job_id DESC LIMIT 21',

'time': '0.001'}

Note how the subquery selects the primary key instead of the object_id.

[Matthias Kestenholz]

(sorry for the spam, will try to correctly set the ticket properties from the start next time)

[Malcolm Tredinnick]

Hmm. Good point. This opens a whole other bag of worms for how people are going to use and abuse nested queries, too. Time to insert a bunch of error messages along with some fixes.

[Malcolm Tredinnick]

(In [9759]) Fixed #9997 -- Fixed use of ValuesQuerySets as rvalues in filters.

Previous behaviour was pretty stupid. Let's never speak of it again. New
behaviour both works and is documented.