Chapter 10 · From QuerySet to SQL
10.8 Lookups on relations and composite keys: RelatedIn and the tuple lookups
ForeignObject registers lookup classes of its own, RelatedExact, RelatedIn, RelatedIsNull and four comparisons, which take an instance, a key or a queryset on the right of a relation’s name and compare the column that holds the key. CompositePrimaryKey registers TupleExact, TupleIn and their relatives, which compare several columns at once as a row value, or as a chain of one-column comparisons where the backend has no row values.
A comparison on a relation’s name, filter(ship=petrel), has to take whatever a program would naturally give for the ship: the instance, its key, the key as the string a URL carried, a queryset of ships, or a list that mixes these. What reaches the database is one column, "ship_id", compared with one value, a list of values or one subquery. So the lookups registered on a relation field do two things that the comparisons of an ordinary field do not: they turn an instance into its key, and they prepare the value through the field the key belongs to on the far side, not through the relation field itself. A comment gives the reason for the second: a foreign key to an integer field, given "abc", has no validation of its own for what is not an integer, so the validation has to be the target field’s.
A key may be several columns. A model with a CompositePrimaryKey has one, and a relation that reaches such a model compares against one. The comparison is then between tuples, and SQL has a form for it, the row value (a, b) = (x, y), which not every backend accepts in every position. The tuple lookups therefore write the row value where the backend’s features allow it and, for a direct value, a chain of one-column comparisons where they do not; TupleIsNull writes the chain whatever the features say.
Which field a relation’s name asks
A lookup class is found by the left side of the comparison, a column whose output field is the field at the end of the path the name walked (The lookup registry: RegisterLookupMixin). For a name that ends at a relation, that field is the join_field of the path’s last step: the foreign key itself for a forward name such as "ship", the rel for a reverse name such as "crew", and for a many-to-many name the through model’s foreign key to the far model, "port" of Licence for "pilots_into". A rel passes get_lookup to its field, so each of these ends at a ForeignObject, the class ForeignKey and OneToOneField are built on. The module registers seven classes on it after the class is defined, and ForeignObject cuts the merge of the method resolution order off at ForeignObject, so that a relation answers to these seven and to none of the pattern or range comparisons a plain field has.
What follows was recorded from a running Django, on SQLite, over the shipping line of From QuerySet to SQL: each line is a question and its answer after ->, and a line that begins SQL is a statement as Django handed it to its cursor.
{name: cls.__name__ for name, cls in sorted(vars(ForeignObject)['class_lookups'].items())} -> {'exact': 'RelatedExact', 'gt': 'RelatedGreaterThan', 'gte': 'RelatedGreaterThanOrEqual', 'in': 'RelatedIn', 'isnull': 'RelatedIsNull', 'lt': 'RelatedLessThan', 'lte': 'RelatedLessThanOrEqual'}
A line that begins with entry answers with the first child of the queryset’s where tree, a lookup; one that begins with tail answers with the statement from its "FROM" on, without the select list’s parameters; and one that begins with sql_of answers with the statement and its parameters. The two lookups on "pilots_into" and on "calls" end at those through-model keys, the second through the table Django made for a relation declared without a through model:
entry(Sailor.objects.filter(pilots_into=bergen)), entry(Voyage.objects.filter(calls=bergen)) -> (RelatedExact(Col(office_licence, office.Licence.port), 1), RelatedExact(Col(fleet_voyage_calls, fleet.Voyage_calls.port), 1))
Six of the seven have one shape: the comparison class of django/ with RelatedLookupMixin in front of it, RelatedExact on Exact, RelatedIsNull on IsNull, and RelatedLessThan, RelatedGreaterThan, RelatedGreaterThanOrEqual and RelatedLessThanOrEqual on their four. The mixin changes how the right side is prepared and what happens when the left side is several columns, and leaves the SQL to the parent (Lookups: Lookup, BuiltinLookup and the standard comparisons). RelatedIn is built on In directly and has the same two changes written for a list of values and for a queryset.
get_normalized_value: an instance becomes its key
get_normalized_value is the function both the mixin and RelatedIn call on a value, and it returns a tuple. A value that is not a model instance goes through untouched: a tuple as it is, anything else wrapped as a tuple of one. An instance is refused with ValueError unless its primary key is set, which Model._is_pk_set decides: a key of None, or one still holding a DatabaseDefault, or a composite key any part of which is either, is not set. The test is on the key and not on the row, so an instance made with pk=1 and never saved passes it.
For an instance that passes, the function takes the target fields of the relation’s last path step, with a composite key opened into its fields by unnest, and reads from the instance the attname of each. Where the instance is not of the target field’s model and the target field is itself a relation, it follows that relation to the field it points at, and reads that one’s attname instead: a parent’s instance, given where the target is a child’s parent link, is read as the parent’s own key. Where the instance has no such attribute at all, the function gives up on the walk and returns the instance’s pk as a tuple; the comment names the case, a one-to-one field that is its model’s primary key, given an instance of that model.
entry(Sailor.objects.filter(ship=petrel)), entry(Sailor.objects.filter(ship=1)), entry(Sailor.objects.filter(ship='1')) -> (RelatedExact(Col(crew_sailor, crew.Sailor.ship), 1), RelatedExact(Col(crew_sailor, crew.Sailor.ship), 1), RelatedExact(Col(crew_sailor, crew.Sailor.ship), 1))
entry(Sailor.objects.filter(ship=petrel)).rhs, entry(Sailor.objects.filter(ship='1')).rhs, type(entry(Sailor.objects.filter(ship='1')).rhs).__name__ -> (1, 1, 'int')
Sailor.objects.filter(ship='x') -> raises ValueError: Field 'id' expected a number but got 'x'.
Sailor.objects.filter(ship=Ship(name='Skua')) -> raises ValueError: Model instances passed to related filters must be saved.
The string became an integer through the target field’s get_prep_value, which is where "x" was refused in the words of an integer field. The unsaved Ship was refused by get_normalized_value, on _is_pk_set’s word.
tail(Ship.objects.filter(captain=cora)), tail(Ship.objects.filter(captain=ada.pk)) -> (('FROM "fleet_ship" WHERE "fleet_ship"."captain_id" = %s ORDER BY "fleet_ship"."name" ASC', (3,)), ('FROM "fleet_ship" WHERE "fleet_ship"."captain_id" = %s ORDER BY "fleet_ship"."name" ASC', (1,)))
entry(Sailor.objects.filter(officer=cora)), tail(Sailor.objects.filter(officer=cora)) -> (RelatedExact(Col(crew_officer, crew.Officer.sailor_ptr), 3), ('FROM "crew_sailor" INNER JOIN "crew_officer" ON ("crew_sailor"."id" = "crew_officer"."sailor_ptr_id") WHERE "crew_officer"."sailor_ptr_id" = %s', (3,)))
entry(Sailor.objects.filter(ship__home=bergen)), entry(Ship.objects.filter(crew=ada)) -> (RelatedExact(Col(fleet_ship, fleet.Ship.home), 1), RelatedExact(Col(crew_sailor, crew.Sailor.id), 1))
An Officer is a Sailor, so Cora given for the captain reads as Cora’s "id", and given for the reverse one-to-one name "officer" reads as the "sailor_ptr_id" the child’s table holds, the target of that reverse path being the child’s primary key. The line on "ship__home" and "crew" has a foreign key reached through another and a reverse foreign key: in both the lookup is the same RelatedExact, and for "crew" the column is the far table’s "id", since a reverse path’s target is the far model’s primary key. A proxy of the right model passes the type check and _is_pk_set alike, an unsaved one included:
tail(Ship.objects.filter(captain=Veteran(pk=1))) -> ('FROM "fleet_ship" WHERE "fleet_ship"."captain_id" = %s ORDER BY "fleet_ship"."name" ASC', (1,))
Whether the value is of a model the relation can take at all is decided before the lookup is built, by Query, which refuses a Port given for a ship (The where clause: build_filter, add_q and WhereNode).
A key of two columns: Berth
The office knows a berth by its port and its number there, and has no key of its own:
# harbour/office/models.py
class Berth(models.Model):
pk = models.CompositePrimaryKey("port_id", "number")
port = models.ForeignKey("fleet.Port", on_delete=models.CASCADE)
number = models.PositiveSmallIntegerField()
metres = models.PositiveSmallIntegerField()
CompositePrimaryKey is a field with no column of its own: it names the fields that make the key, becomes the primary key of the model’s Model._meta as it joins the class, and puts a descriptor on the class that reads and writes the named fields as one tuple (The field classes). What matters to a query is CompositePrimaryKey, which returns a ColPairs over the key’s fields where another field returns a Col, so that "pk" in a filter on Berth is the pair of columns ("port_id", "number"), and Options, which opens the key into those fields for whoever has to count a model’s key columns. The module registers the seven tuple lookups on the class after it, under the same seven words ForeignObject uses.
entry(Berth.objects.filter(pk=(1, 2))), tail(Berth.objects.filter(pk=(1, 2))) -> (TupleExact(ColPairs('office_berth', (<django.db.models.fields.related.ForeignKey: port>, <django.db.models.fields.PositiveSmallIntegerField: number>), (<django.db.models.fields.related.ForeignKey: port>, <django.db.models.fields.PositiveSmallIntegerField: number>), <django.db.models.fields.composite.CompositePrimaryKey: pk>), (1, 2)), ('FROM "office_berth" WHERE ("office_berth"."port_id", "office_berth"."number") = (%s, %s)', (1, 2)))
The lookup holds the ColPairs on its left, with its alias, its two target fields, the same two as sources, and the composite key as its output field, and the tuple (1, 2) on its right; the statement compares the two columns with two placeholders as one row value.
RelatedLookupMixin: one column, or a pair of columns
RelatedLookupMixin does its work for a left side that is one column and a right side that is not an expression: it normalizes the value and takes the first element of the tuple, then, unless the lookup has switched preparation off, runs the target field’s get_prep_value on it. The target field is the last of the target fields of the relation’s last path step, which the comment says can safely be assumed to be one, since several would have made the left side a pair of columns and taken the other branch. Then the parent’s preparation runs, ending in Lookup, which prepares a direct value through the left side’s output field where that has a get_prep_value: a foreign key’s hands on to its target field, and a rel has none, so a value on the right of a reverse name is prepared once. RelatedIsNull is the one of the six whose parent has prepare_rhs off, so the True or False it is given is kept as it is, and it is what build_lookup makes of captain=None (From a name to a column: names_to_path, setup_joins and build_lookup).
tail(Sailor.objects.filter(ship__gt=1)), tail(Sailor.objects.filter(ship__isnull=False)) -> (('FROM "crew_sailor" WHERE "crew_sailor"."ship_id" > %s', (1,)), ('FROM "crew_sailor" WHERE "crew_sailor"."ship_id" IS NOT NULL', ()))
The SQL is the parent’s: "ship_id" > %s from GreaterThan, "ship_id" IS NOT NULL from IsNull.
The left side is a ColPairs when the key compared is several columns, and RelatedLookupMixin is where that case is handled. Query makes a ColPairs for a relation whose trimmed targets are several, and a target that is a CompositePrimaryKey makes one of its own accord, since its get_col returns a ColPairs and not a Col: a reverse name that reaches a model with a composite key, "berth" on Port, compares against the pair ("port_id", "number"). For such a left side as_sql refuses a right side that is an expression, with the message that the lookup does not support multi-column subqueries, normalizes the value into its tuple, builds the tuple lookup of the same name from the table tuple_lookups, and compiles that instead. ColPairs itself, an expression that stands for several columns of one alias and writes them joined by commas, is Expressions: resolve_expression and as_sql’s.
entry(Port.objects.filter(berth=(1, 2))) -> RelatedExact(ColPairs('office_berth', (<django.db.models.fields.related.ForeignKey: port>, <django.db.models.fields.PositiveSmallIntegerField: number>), (<django.db.models.fields.related.ForeignKey: port>, <django.db.models.fields.PositiveSmallIntegerField: number>), <ManyToOneRel: office.berth>), (1, 2))
tail(Port.objects.filter(berth=(1, 2))) -> ('FROM "fleet_port" INNER JOIN "office_berth" ON ("fleet_port"."id" = "office_berth"."port_id") WHERE ("office_berth"."port_id", "office_berth"."number") = (%s, %s) ORDER BY "fleet_port"."name" ASC', (1, 2))
tail(Port.objects.filter(berth=Berth.objects.get(number=2))) -> ('FROM "fleet_port" INNER JOIN "office_berth" ON ("fleet_port"."id" = "office_berth"."port_id") WHERE ("office_berth"."port_id", "office_berth"."number") = (%s, %s) ORDER BY "fleet_port"."name" ASC', (1, 2))
SQL SELECT "office_berth"."port_id", "office_berth"."number", "office_berth"."metres" FROM "office_berth" WHERE "office_berth"."number" = %s LIMIT 21 with params (2,)
...
tail(Port.objects.filter(berth__isnull=True)) -> ('FROM "fleet_port" LEFT OUTER JOIN "office_berth" ON ("fleet_port"."id" = "office_berth"."port_id") WHERE ("office_berth"."port_id" IS NULL OR "office_berth"."number" IS NULL) ORDER BY "fleet_port"."name" ASC', ())
tail(Port.objects.filter(berth=Berth.objects.values('pk')[:1])) -> raises ValueError: 'exact' doesn't support multi-column subqueries.
The lookup is a RelatedExact whose left side is the ColPairs of the berths’ two key columns, with the rel as its output field, and the statement compares the pair with the tuple as one row value; an instance given instead is read into the same tuple, after the statement that fetched it. The "isnull" reached TupleIsNull as a tuple of one boolean and was written as an "OR" over the two columns, and the queryset was refused.
RelatedIn
A subquery under "in" has to yield the column the relation compares against: the far model’s key, or, where the relation points at another column, that column, and for a key of several columns one column for each. In sees to the common case for every field, adding "pk" to a subquery that has chosen no columns, and RelatedIn steps in before it where the relation’s own target columns are wanted instead: a relation on several columns, and one whose target is not the far model’s primary key. With a ColPairs on the left and a queryset of the related model that has chosen nothing, it has the queryset select the key’s source fields by name, with Query.set_values, so that the subquery yields tuples of the key’s width. With one column and such a queryset, where the relation’s target field is not a primary key, it narrows the queryset to that target column. Where, besides, the field is itself its model’s primary key and the queryset is of that model, the case the comment describes for a one-to-one field, it narrows the queryset to the field’s own name instead. A list of direct values it normalizes and prepares through the target field, one by one, and sets prepare_rhs false on the instance, so that In’s own preparation, which would prepare each value again through the left side’s field, leaves them alone. Then In runs: it checks that the subquery yields as many columns as the left side has, clears its ordering, and adds the primary key where nothing is selected (Subqueries: Subquery, Exists, OuterRef and a query inside another).
entry(Sailor.objects.filter(ship__in=[petrel, 2, '2'])).rhs -> [1, 2, 2]
entry(Sailor.objects.filter(ship__in=Ship.objects.filter(tonnage__gt=1000))).rhs.values_select, entry(Sailor.objects.filter(ship__in=Ship.objects.filter(tonnage__gt=1000))).rhs.has_select_fields -> ((), False)
tail(Sailor.objects.filter(ship__in=Ship.objects.filter(tonnage__gt=1000))) -> ('FROM "crew_sailor" WHERE "crew_sailor"."ship_id" IN (SELECT "U0"."id" FROM "fleet_ship" "U0" WHERE "U0"."tonnage" > %s)', (1000,))
tail(Sailor.objects.filter(ship__in=Ship.objects.values('home'))) -> ('FROM "crew_sailor" WHERE "crew_sailor"."ship_id" IN (SELECT "U0"."home_id" AS "home" FROM "fleet_ship" "U0")', ())
The list came out as three integers. The queryset of ships was given no columns, so after preparation its values_select is still empty and has_select_fields false, and the key was added to its select list without an alias: the subquery reads "U0"."id". The line with values('home') is a queryset that had chosen its column, which is left as it is, and the sailors’ "ship_id" is compared with ports’ keys: the lookup checks the width of the subquery and not what it selects.
RelatedIn.as_sql is the counterpart of the mixin’s as_sql: a ColPairs on the left is handed to TupleIn, with each direct value normalized first and a queryset passed as it is; one column goes to In.as_sql. On the reverse name to the berths, a list of pairs went to TupleIn as it was, and a queryset of berths that had chosen nothing was made to select its two source fields under their names:
tail(Port.objects.filter(berth__in=[(1, 1), (1, 2)])) -> ('FROM "fleet_port" INNER JOIN "office_berth" ON ("fleet_port"."id" = "office_berth"."port_id") WHERE ("office_berth"."port_id", "office_berth"."number") IN ((%s, %s), (%s, %s)) ORDER BY "fleet_port"."name" ASC', (1, 1, 1, 2))
tail(Port.objects.filter(berth__in=Berth.objects.filter(metres__gt=100))) -> ('FROM "fleet_port" INNER JOIN "office_berth" ON ("fleet_port"."id" = "office_berth"."port_id") WHERE ("office_berth"."port_id", "office_berth"."number") IN (SELECT "U0"."port_id" AS "port", "U0"."number" AS "number" FROM "office_berth" "U0" WHERE "U0"."metres" > %s) ORDER BY "fleet_port"."name" ASC', (100,))
Tuple and TupleLookupMixin
Tuple is a Func whose function name is the empty string, so that the template of Func writes its arguments in parentheses with nothing in front: (%s, %s) for two values, and ((%s, %s), (%s, %s)) for a Tuple of two tuples. It has a length and can be iterated, like a ColPairs, and it allows composite expressions among its sources, which BaseExpression refuses for a class that has not said so (Transforms and database functions: Transform and Func).
TupleLookupMixin is what the seven classes have in common, in front of the ordinary comparison class each is built on, and its work is to make a row value out of parts that are each one column: a left side that is a ColPairs or a Tuple of columns, a right side that is a tuple of values, a ColPairs, a ResolvedOuterRef or a Query, and parentheses round each half. get_prep_lookup checks a direct value for being a tuple or a list of the left side’s width, and refuses otherwise with ValueError naming the lookup and the field; an expression has to be one of those three classes, and then the parent’s preparation runs, which for TupleExact is Exact’s checks on a subquery. get_prep_lhs turns a left side given as a tuple or list of columns into a Tuple, and process_lhs compiles the left side and adds the parentheses unless it is a Tuple, which carries its own. process_rhs turns a direct value into a Tuple, each element that is not already an expression wrapped as a Value typed by the column it stands against, and compiles that; a ColPairs is compiled and put in parentheses; a Query is compiled as Lookup compiles any expression; anything else is refused, since composite field lookups work with composite expressions alone.
TupleLookupMixin asks the backend’s features twice. Where supports_tuple_comparison_against_subquery is off and the lookup is one of the four ordered comparisons with a subquery on the right, it raises NotSupportedError. Where supports_tuple_lookups is off, it returns get_fallback_sql, which six of the seven define for themselves (TupleIsNull overrides as_sql and never reaches it) and the mixin’s own refuses with NotImplementedError. Otherwise the parent’s as_sql writes the row value: BuiltinLookup puts the two compiled halves either side of the operator get_rhs_op gives, the backend’s table entry for the comparisons and "IN" from In.get_rhs_op for TupleIn. Both features are true in the base features class, and Oracle is the backend that answers otherwise: supports_tuple_comparison_against_subquery is false there, and supports_tuple_lookups is decided by its version (Oracle).
Berth.objects.filter(pk=F('pk')) -> <QuerySet [<Berth: Berth object ((1, 1))>, <Berth: Berth object ((1, 2))>]>
SQL SELECT "office_berth"."port_id", "office_berth"."number", "office_berth"."metres" FROM "office_berth" WHERE ("office_berth"."port_id", "office_berth"."number") = ("office_berth"."port_id", "office_berth"."number") LIMIT 21
tail(Berth.objects.filter(pk=Berth.objects.values('pk')[:1])) -> ('FROM "office_berth" WHERE ("office_berth"."port_id", "office_berth"."number") = (SELECT "U0"."port_id", "U0"."number" FROM "office_berth" "U0" LIMIT 1)', ())
tail(Berth.objects.filter(pk__gt=Berth.objects.values('pk')[:1])) -> ('FROM "office_berth" WHERE ("office_berth"."port_id", "office_berth"."number") > (SELECT "U0"."port_id", "U0"."number" FROM "office_berth" "U0" LIMIT 1)', ())
connection.features.supports_tuple_lookups, connection.features.supports_tuple_comparison_against_subquery -> (True, True)
The F resolved to the key’s own ColPairs, which stands on the right in parentheses. A queryset on the right of "exact" is checked by Exact, which counts the subquery’s columns with a composite key opened out, two for values('pk') as for a queryset that selected nothing, and compares the count with the pair’s width (Subqueries: Subquery, Exists, OuterRef and a query inside another).
The seven tuple lookups
CompositePrimaryKey registers, for a key of two columns a and b against the values x and y: what each writes where the backend has supports_tuple_lookups, and what get_fallback_sql writes where it has not. The forms are the ones the classes’ own comments give, with the three-column case cut to two.| word | class | the row value | the fallback |
|---|---|---|---|
"exact" |
class TupleExact |
the row value "(a, b) = (x, y)" |
the fallback "a = x AND b = y" |
"gt" |
class TupleGreaterThan |
the row value "(a, b) > (x, y)" |
the fallback "a > x OR (a = x AND b > y)" |
"gte" |
class Tuple |
the row value "(a, b) >= (x, y)" |
the fallback "a > x OR (a = x AND (b > y OR b = y))" |
"lt" |
class TupleLessThan |
the row value "(a, b) < (x, y)" |
the fallback "a < x OR (a = x AND b < y)" |
"lte" |
class Tuple |
the row value "(a, b) <= (x, y)" |
the fallback "a < x OR (a = x AND (b < y OR b = y))" |
"in" |
class TupleIn |
the row value "(a, b) IN ((x1, y1), (x2, y2))" |
the fallback "(a = x1 AND b = y1) OR (a = x2 AND b = y2)", or "EXISTS(...)" for a subquery |
"isnull" |
class TupleIsNull |
the row value "a IS NULL OR b IS NULL" for True, "a IS NOT NULL AND b IS NOT NULL" for False |
the fallback the same |
TupleExact writes one Exact for each column and joins them under "AND" in a WhereNode of its own; with a Query on the right it writes the row value all the same, through Exact.as_sql. The four ordered comparisons build the chain the table shows as nested WhereNode objects, alternating the comparison with Exact and "OR" with "AND" column by column, the inclusive two ending with an "OR" on equality of the last column.
TupleIsNull is the one class that does not write a row value at all: its as_sql writes one IsNull for each column and joins them with "OR" for True and "AND" for False, whatever the backend’s features say. Its get_prep_lookup accepts a boolean, or a tuple or list of one boolean, which is the form RelatedLookupMixin hands on after normalizing the value, and refuses anything else.
tail(Berth.objects.filter(pk__in=[(1, 1), (1, 2)])) -> ('FROM "office_berth" WHERE ("office_berth"."port_id", "office_berth"."number") IN ((%s, %s), (%s, %s))', (1, 1, 1, 2))
tail(Berth.objects.filter(pk__gt=(1, 1))), tail(Berth.objects.filter(pk__lte=(1, 1))) -> (('FROM "office_berth" WHERE ("office_berth"."port_id", "office_berth"."number") > (%s, %s)', (1, 1)), ('FROM "office_berth" WHERE ("office_berth"."port_id", "office_berth"."number") <= (%s, %s)', (1, 1)))
tail(Berth.objects.filter(pk__isnull=False)), tail(Berth.objects.filter(pk__isnull=True)) -> (('FROM "office_berth" WHERE ("office_berth"."port_id" IS NOT NULL AND "office_berth"."number" IS NOT NULL)', ()), ('FROM "office_berth" WHERE ("office_berth"."port_id" IS NULL OR "office_berth"."number" IS NULL)', ()))
TupleIn has checks of its own for a direct value: that it is a tuple or a list, that each of its elements is one, and that each has the width of the left side; an expression has to be a Query or a Subquery, and then In runs. TupleIn writes a direct value as a Tuple of tuples, each element that is not already an expression wrapped as a Value typed by its column, leaving out any row that holds a None, since a comment notes that "NULL" is never equal to anything; an empty list, or a list that is empty once those rows are gone, raises EmptyResultSet, and the statement is never sent. TupleIn writes a direct value as an "OR" of one "AND" of equalities for each row, with the same rows left out and the same EmptyResultSet; a Query on the right it turns into an Exists: a clone of the subquery with one Exact added to its where tree for each column, comparing the outer column with the inner one, the inner one taken from the subquery’s own select list through a compiler of its own.
tail(Berth.objects.filter(pk__in=Berth.objects.filter(metres__gt=100))) -> ('FROM "office_berth" WHERE ("office_berth"."port_id", "office_berth"."number") IN (SELECT "U0"."port_id", "U0"."number" FROM "office_berth" "U0" WHERE "U0"."metres" > %s)', (100,))
sql_of(Berth.objects.filter(pk__in=[])) -> raises EmptyResultSet:
sql_of(Berth.objects.filter(pk__in=[(1, None)])) -> raises EmptyResultSet:
Berth.objects.filter(pk=1) -> raises ValueError: 'exact' lookup of 'pk' must be a tuple or a list
Berth.objects.filter(pk=(1,)) -> raises ValueError: 'exact' lookup of 'pk' must have 2 elements
Berth.objects.filter(pk__in=[(1, 2, 3)]) -> raises ValueError: 'in' lookup of 'pk' must have 2 elements each
Berth.objects.filter(pk__in=[1, 2]) -> raises ValueError: 'in' lookup of 'pk' must be a collection of tuples or lists
The subquery selects the two key columns without aliases, as In added them. The two EmptyResultSet lines are an empty list and a list whose one row held a None. The four refusals are the checks of TupleLookupMixin and TupleIn, each with the lookup’s word and the field’s name in its message.
With the feature switched off for one block of the recording, the same filters write the fallbacks:
With supports_tuple_lookups switched off for this block: the fallback SQL of the tuple lookups, written as ANDs and ORs of one-column comparisons
connection.features.supports_tuple_lookups -> False
tail(Berth.objects.filter(pk=(1, 2))) -> ('FROM "office_berth" WHERE ("office_berth"."port_id" = %s AND "office_berth"."number" = %s)', (1, 2))
tail(Berth.objects.filter(pk__in=[(1, 1), (1, 2)])) -> ('FROM "office_berth" WHERE (("office_berth"."port_id" = %s AND "office_berth"."number" = %s) OR ("office_berth"."port_id" = %s AND "office_berth"."number" = %s))', (1, 1, 1, 2))
tail(Berth.objects.filter(pk__gt=(1, 1))) -> ('FROM "office_berth" WHERE ("office_berth"."port_id" > %s OR ("office_berth"."port_id" = %s AND "office_berth"."number" > %s))', (1, 1, 1))
tail(Berth.objects.filter(pk__gte=(1, 1))) -> ('FROM "office_berth" WHERE ("office_berth"."port_id" > %s OR ("office_berth"."port_id" = %s AND ("office_berth"."number" > %s OR "office_berth"."number" = %s)))', (1, 1, 1, 1))
tail(Berth.objects.filter(pk__in=Berth.objects.filter(metres__gt=100))) -> ('FROM "office_berth" WHERE EXISTS(SELECT %s AS "a" FROM "office_berth" "U0" WHERE ("U0"."metres" > %s AND "office_berth"."port_id" = ("U0"."port_id") AND "office_berth"."number" = ("U0"."number")) LIMIT 1)', (1, 100))
The "in" with a subquery became an "EXISTS", whose inner statement compares "U0"."port_id" and "U0"."number" with the outer berth’s columns: the subquery had already been resolved against the outer query, with its aliases renamed, when the filter was built (Subqueries: Subquery, Exists, OuterRef and a query inside another).