Fixed #37115, Refs #5929 -- Added a CompositeField expression to support multi-column subqueries. by p-r-a-v-i-n · Pull Request #21636 · django/django

p-r-a-v-i-n · 2026-07-17T08:13:15Z

Trac ticket number

Branch description

This PR Introduces a CompositeField to support subqueries which return multi-cols.

Currently Django ORM does not have internal machinery to decide the output_field of subqueries which have multi-columns in their output.

CompositeField will resolve output_field for such subqueries.

Scope of this PR:

Scope of CompositeField for this PR is limited to virtual sources only.
selecting multi-column subquery result as annotation not support.
Correlated(using OuterRef) subquery not supported .

References:

AI Assistance Disclosure (REQUIRED)

No AI tools were used in preparing this PR.
1. I have used Chatgpt 5.5, for creating intial data for testing, and I have used it for researching internal of Orm.
2. I also took help to write documentation part.

Checklist

This PR follows the contribution guidelines.
This PR does not disclose a security vulnerability (see vulnerability reporting).
This PR targets the main branch.
The commit message is written in past tense, mentions the ticket number (if applicable), and ends with a period (see guidelines).
I have not requested, and will not request, an automated AI review for this PR.

I have checked the "Has patch" ticket flag in the Trac system.
I have added or updated relevant tests.
I have added or updated relevant docs, including release notes if applicable.
I have attached screenshots in both light and dark modes for any UI changes.

p-r-a-v-i-n · 2026-07-17T09:22:30Z

+            clause = WhereNode([condition], connector=AND)
+            if (
+                current_negated
+                and condition.lookup_name != "isnull"
+                and condition.rhs is not None
+                and self._expression_contains_nullable_subquery_column(condition.lhs)
+            ):
+                lookup_class = condition.lhs.get_lookup("isnull")
+                clause.add(lookup_class(condition.lhs, False), AND)


This change is for the case when we have something
inner --> which don't have email = 'bob@mail.com'. but in the outer query if we are doing
outer --> .exclude(info__email='bob@mail.com') in this case
we are appending here is_null=False condition in the where node. so we should get explicit true or false. not unknown.

Before:

WHERE NOT ( 'info'.email == 'bob@mail.com'') but info.email is NULL, so it becaming WHERE NOT ( null == 'bob@mail.com' ) --> WHERE NOT UNKNOWN will remove the outer row.

After:

NULL == "'bob@mail.com" --> unknown AND NULL IS NOT NULL --> FALSE UNKOWN FALSE --> FALSE NOT FALSE --> TRUE --> so the outer row is preserved.

I may sound unclear here , please don't hesitate to ask again.

p-r-a-v-i-n · 2026-07-17T09:51:11Z

#21496 (comment) and Jacob's suggestion

As a first step, let's avoid that. Is there a way to add the JOIN "just-in-time" when a filter needs it?

helped me to come up with this idea.

Just high-level overview of the current approach ( may be I should have put this into the PR body ):

We are not changing the existing behavior for single-column subqueries. We trigger this new logic only when these .filter(), .values(), or .order_by(). methods reference the multi-column alias
For example, when we receive:
.filter(info__email="bob@mail.com")
we split the name and check whether info is present in self.annotations. If it is present, we check whether the annotation is a multi-column subquery.

If it is a multi-column subquery, we check whether a SubqueryJoin for this annotation is already present in alias_map. If it is present, we reuse it. Otherwise, we create a new SubqueryJoin and register it in alias_map.

The SubqueryJoin puts the inner query into the outer query’s FROM clause:

LEFT OUTER JOIN (
    SELECT email, name
    FROM ...
) info ON (1 = 1)

After that:
info__email
is resolved into an individual Col pointing to info.email.
If the whole annotation is used, for example in a tuple lookup, we resolve it into an internal Tuple containing all the columns.

this way, the inner multi-column query is added to the FROM clause only when one of its fields, or the complete tuple, is actually needed. The derived table appears only once in the generated SQL, and multiple references reuse the same SubqueryJoin

jacobtylerwalls

This simpler version looks promising!

jacobtylerwalls · 2026-07-20T18:06:02Z

+class CompositeField(Field):
+    is_composite = True
+
+    def __init__(self, **kwargs):


As on the previous PR, I think we should add support for *args without names. I suppose we could do this in another PR that adds test cases for Func() subclasses instead of Subquery, as I imagine that to be the use case for anonymous args.

See #21636 (comment) for more discussion about *args.

jacobtylerwalls · 2026-07-25T15:01:18Z

buildbot, test on oracle.

jacobtylerwalls

Did a manual test against a PR for djangoproject.com, and I have a couple of observations.

jacobtylerwalls · 2026-08-07T16:06:56Z

+
+These subfields can be selected, filtered, annotated, and ordered in the same
+way as model fields. See :ref:`using-multi-column-subqueries` for more details.
+


In a follow-up PR, we could walk through the example from jacobtylerwalls/djangoproject.com@219e530 in the contrib.postgres docs for SearchHeadline, and show an example like this:

Since SearchHeadline can be expensive to calculate, the query below avoids annotating it on all results before the limit is taken, and also avoids re-expressing the rank:: subquery = ( Product.objects .annotate(rank=SearchRank(...)) .values("pk", "rank") .order_by("-rank", "pk") ) query = ( Product.objects .alias(ranked=subquery[:10]) .filter(pk__in=F("ranked__pk") .annotate(headline=SearchHeadline(...)) .order_by("-ranked__rank", "pk") )

or similar.

If we do that, then we could possibly link to that here, idk.

(But we can leave for a follow-up, to give others a chance to weigh in.)

I liked it. That will add more merits (in my POV).

jacobtylerwalls

I really appreciate the incremental commits including test cases with each change, nice work on that!

I did another holistic review, and then my plan is to look a little closer at the join machinery parts to see if we can squeeze out any simplifications.

jacobtylerwalls · 2026-08-12T01:05:36Z

+)
+
+
+class CompositeFieldOutputFieldTests(SimpleTestCase):


You might burst this file into modules. You don't have to use as many as tests/composite_pk did, but it's probably good to have more than just one module.

Maybe we can hold this comment until the end, since I just praised all your atomic commits with tests 🤣

This reverts commit a2efb67.

…in query.

github-actions · 2026-10-15T14:31:25Z

Uncovered lines in changed files

-------------
django/db/models/__init__.py (100%)
django/db/models/expressions.py (100%)
django/db/models/fields/__init__.py (100%)
django/db/models/fields/composite.py (100%)
django/db/models/fields/reverse_related.py (100%)
django/db/models/fields/tuple_lookups.py (100%)
django/db/models/lookups.py (100%)
django/db/models/sql/compiler.py (100%)
django/db/models/sql/datastructures.py (98.0%): Missing lines 253
django/db/models/sql/query.py (98.8%): Missing lines 387,398
-------------
Total:   313 lines
Missing: 3 lines
Coverage: 99%
-------------

Note: Missing lines are warnings only. Some database-specific lines may not be measured. More information

p-r-a-v-i-n marked this pull request as draft July 17, 2026 08:13

p-r-a-v-i-n mentioned this pull request Jul 17, 2026

Refs #5929 -- Added a CompositeField expression to support multi-column subqueries. #21496

Closed

7 of 11 tasks

LilyFirefly self-requested a review July 17, 2026 08:29

p-r-a-v-i-n commented Jul 17, 2026

View reviewed changes

Comment thread django/db/models/sql/compiler.py Outdated

p-r-a-v-i-n commented Jul 17, 2026

View reviewed changes

p-r-a-v-i-n marked this pull request as ready for review July 17, 2026 09:23

charettes self-requested a review July 17, 2026 12:35

jacobtylerwalls added geodjango coverage Generate a comment with a coverage diff labels Jul 19, 2026

This comment has been minimized.

Sign in to view

jacobtylerwalls reviewed Jul 20, 2026

View reviewed changes

p-r-a-v-i-n force-pushed the clean-implementation-of-composite-field branch 2 times, most recently from 19d7423 to 05fa1eb Compare July 21, 2026 15:13

p-r-a-v-i-n mentioned this pull request Jul 31, 2026

Support for set returning functions #21708

Closed

11 tasks

jacobtylerwalls reviewed Aug 3, 2026

View reviewed changes

jacobtylerwalls reviewed Aug 7, 2026

View reviewed changes

p-r-a-v-i-n force-pushed the clean-implementation-of-composite-field branch from 637cbe3 to 2acd3cd Compare August 9, 2026 14:33

p-r-a-v-i-n requested a review from jacobtylerwalls August 9, 2026 14:46

jacobtylerwalls reviewed Aug 12, 2026

View reviewed changes

p-r-a-v-i-n commented Aug 12, 2026

View reviewed changes

Comment thread django/db/models/sql/query.py

jacobtylerwalls reviewed Aug 12, 2026

View reviewed changes

Comment thread django/db/models/sql/query.py Outdated

p-r-a-v-i-n force-pushed the clean-implementation-of-composite-field branch from ed7559a to b7c33e5 Compare August 13, 2026 08:25

p-r-a-v-i-n commented Aug 13, 2026

View reviewed changes

Comment thread tests/composite_field/test_composite_field.py Outdated

jacobtylerwalls mentioned this pull request Aug 13, 2026

Fixed #37274 -- Allowed transforms in order_by() after alias(). #21775

Merged

10 of 11 tasks

jacobtylerwalls reviewed Aug 13, 2026

View reviewed changes

Comment thread django/db/models/sql/query.py Outdated

Comment thread django/db/models/lookups.py Outdated

Comment thread django/db/models/sql/query.py Outdated

jacobtylerwalls reviewed Aug 14, 2026

View reviewed changes

Comment thread django/db/models/sql/query.py Outdated

p-r-a-v-i-n added 25 commits October 14, 2026 21:32

Version added for is_composite in docs.

71e5fa7

Prevented using CompositeField on models.

b5f3c7d

Made CompositePrimaryKey inherit CompositeField.

0323226

Addressed documentation feedback by Lily.

d3c3850

Rejected scalar output fields for multi-column subqueries.

53c15b9

Added a shared base for composite fields.

7cc85a1

Simplified composite ordering compilation.

ee67280

Refactored query output expression handling.

8d069c2

Improved composite field tests.

ce6d431

Flattened composite primary key values in tuple lookup RHS.

5da1022

Revert "Flattened composite primary key values in tuple lookup RHS."

db83de9

This reverts commit a2efb67.

Defined is_composite on Field.

5c2adf5

Small coverage.

b2bd96b

fixed tracking of aliases for repeated table joins.

76a391f

Nesting of composite field is avoided.

3dffa8d

Refactored CompositeField to accept positional fields

9ad112b

Added CompositeFieldExpression and removed expression.is_composite

15e01a9

Derived CompositeField nullability from fields

69850bf

Handled nullable expressions in negated filters

98b9733

Added composite flag to reverse relations

d56a6f1

Adjusted character mismatch for oracle.

89da6d3

Added FilteredRelation table alias regression tests

fc678f3

Refactoed table_alias.

0cf2b4d

Removed ON(1=1) for explicit CROSS JOIN.

2279fd0

Handled missing promotion/demotion of table source when made require …

11869c9

…in query.

p-r-a-v-i-n force-pushed the clean-implementation-of-composite-field branch from 349539b to 11869c9 Compare October 14, 2026 16:05

p-r-a-v-i-n added 2 commits October 14, 2026 21:55

Fixed composite expression detection

6632d37

Required explicit fields for multi-column subquery aliases.

f7a2507

jacobtylerwalls changed the title ~~Refs #5929 -- Added a CompositeField expression to support multi-column subqueries.~~ Fixed #37115, Refs #5929 -- Added a CompositeField expression to support multi-column subqueries. Oct 15, 2026


		These subfields can be selected, filtered, annotated, and ordered in the same
		way as model fields. See :ref:`using-multi-column-subqueries` for more details.

		)


		class CompositeFieldOutputFieldTests(SimpleTestCase):

Conversation

p-r-a-v-i-n commented Jul 17, 2026 • edited by jacobtylerwalls Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Trac ticket number

Branch description

Scope of this PR:

AI Assistance Disclosure (REQUIRED)

Checklist

Uh oh!

Uh oh!

p-r-a-v-i-n Jul 17, 2026

Choose a reason for hiding this comment

Uh oh!

p-r-a-v-i-n commented Jul 17, 2026 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Uh oh!

This comment has been minimized.

This comment has been minimized.

This comment has been minimized.

jacobtylerwalls left a comment

Choose a reason for hiding this comment

Uh oh!

Uh oh!

Uh oh!

Uh oh!

jacobtylerwalls Jul 20, 2026

Choose a reason for hiding this comment

Uh oh!

jacobtylerwalls Sep 25, 2026

Choose a reason for hiding this comment

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

jacobtylerwalls commented Jul 25, 2026

Uh oh!

jacobtylerwalls left a comment

Choose a reason for hiding this comment

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

jacobtylerwalls Aug 7, 2026 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Choose a reason for hiding this comment

Uh oh!

p-r-a-v-i-n Aug 7, 2026 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Choose a reason for hiding this comment

Uh oh!

jacobtylerwalls left a comment

Choose a reason for hiding this comment

Uh oh!

Uh oh!

jacobtylerwalls Aug 12, 2026

Choose a reason for hiding this comment

Uh oh!

jacobtylerwalls Aug 12, 2026

Choose a reason for hiding this comment

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

p-r-a-v-i-n commented Jul 17, 2026 •

edited by jacobtylerwalls

Loading

p-r-a-v-i-n commented Jul 17, 2026 •

edited

Loading

jacobtylerwalls Aug 7, 2026 •

edited

Loading

p-r-a-v-i-n Aug 7, 2026 •

edited

Loading