Opened 7 years ago

Last modified 12 months ago

#21604 new New feature

Embed raw queries as subqueries when used with an __in filter

Reported by: alex@… Owned by: nobody
Component: Database layer (models, ORM) Version: master
Severity: Normal Keywords:
Cc: Triage Stage: Accepted
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: yes
Easy pickings: no UI/UX: no


As per this discussion on django-developers:

In brief: At the moment, when passing a RawQuerySet to an __in filter, the raw query is evaluated and the result passed as a parameter to the filter's SQL. It would be nice to have the option of using the raw SQL in a subquery instead, for performance reasons.

I've had an initial shot at implementing this feature and would love any feedback:

I've taken the approach of wrapping the raw query in another SELECT when it's used as a subquery, selecting the required columns (either the primary key or others as specified in only() - see below) and adding an IS NOT NULL clause.

Responding to Anssi's feedback on django-developers about this approach:

  1. It should not be assumed that the primary key is the field needed in the inner query. We do have foreign keys to other fields, and one can use this approach without foreign keys at all.

We discussed making a ValuesRawQuerySet that could be used like this: Book.objects.filter(subject_code__in=subject_raw_query.values('code'))

But that feels a bit too convoluted - as Anssi pointed out, it's a verbose way of writing raw SQL and getting plain data back, which Django already has.

What I think makes sense, and what I've implemented, is using only() for this purpose. QuerySet.only() and QuerySet.defer() appear to silently ignore requests to defer the PK: if you do Book.objects.only('title'), both title and the primary key are fetched. What I've implement will follow that convention when the RawQuery is executed directly, but leave the PK out in subqueries when applicable, so that things like Author.objects.get(pk__in=raw_book_query.only('author_id')) work as expected.

Supporting only() requires a pretty big change to RawQuery, and I feel like it might be all a bit too tricky - it's all contained in the most recent commit so can be easily rolled back if necessary. Keen to know what others think.

Using only() also doesn't work in SQLite currently, due to #21603.

  1. If the query is of the form NOT IN, then we need to also filter out null values, otherwise no results are returned if the raw query contains a single null (NOT IN is weird...)

This is taken into account in the patch - subqueries are always filtered for nulls. This will be a database no-op in the common case of selecting a not-null primary key.

  1. Does the wrapping of the subquery into (select field from (raw_query) where field is not null) cause performance problems? It could be possible that this prevents the DB's optimizer from working correctly.

Postgres does the right thing and as far as I can read SQLite's query planner, that does too. Have not tried any other backends yet.

  1. If the query doesn't select the required column (that is, it is deferred), we don't have any way to know that. This seems like a documentation issue, but worth mentioning.

The patch in its current state sidesteps this to some degree, in that including the primary key is already a stated requirement of the queries passed to RawQuerySet. However, at the moment you just get an error back from the database backend, instead of the InvalidQuery Django raises when evaluating a pk-less raw query. I think this is acceptable if documented.

Change History (3)

comment:1 Changed 7 years ago by Marc Tamlyn

Triage Stage: UnreviewedAccepted

comment:2 Changed 6 years ago by Anssi Kääriäinen

Patch needs improvement: set

Sorry for not responding to this ticket sooner.

I think the patch is a bit too invasive for this ticket. We only need support for somefield__in=rawqs. It would be hopefully possible to just alter the way how the SQL is generated when used in an in lookup. In particular there shouldn't be any need

I wonder if it would be better to just add support for qs.filter(somefield__in=RawQuery(sql_str, params)) (and also do the same for other lookup types if at all possible). This would be a lot easier to support (actually, this will be likely extremely easy to do after #14030). Of course, this doesn't allow one to use an existing raw query.

If this could be implemented with just wrapping the query in subquery when used from __in lookup, then I think supporting somefield__in=rawqs directly is OK. If not, then lets focus on adding support for somefield__in=RawQuery(sql_str, params). In other words, the current patch seems too complicated for the added feature.

comment:3 Changed 12 months ago by Simon Charette

This should be as easy as implementing RawQuery.as_sql to return (self.sql, self.params) and setting has_select_fields=True as class attribute.

Note: See TracTickets for help on using tickets.
Back to Top