Ordering and grouping by select position is off by one past a composite primary key in values()

[Dave Gaeddert]

When a composite primary key is selected with values() / values_list(), ordering by a later selected field sorts by the wrong column:

class Tenant(models.Model):
    pass

class User(models.Model):
    pk = models.CompositePrimaryKey("tenant_id", "id")
    tenant = models.ForeignKey(Tenant, models.CASCADE)
    id = models.SmallIntegerField(unique=True)
    email = models.EmailField(unique=True)

User.objects.values_list("pk", "email").order_by("email")

SELECT "user"."tenant_id", "user"."id", "user"."email" AS "email"
FROM "user" ORDER BY 2 ASC

ORDER BY 2 is the id column; email is at position 3. Rows come back ordered by id, not email, with no exception. GROUP BY has the same drift: values("pk", "email").annotate(count=Count(...)) emits GROUP BY 2 where 3 was meant. PostgreSQL's functional-dependency rule hides that when the composite key is the primary key, but it would produce wrong aggregates for a composite foreign key.

Reproduces on 5.2, 6.0, and main, on PostgreSQL 16 and SQLite.

Cause: _order_by_pairs() and get_group_by() count select positions one per selection, but a CompositePrimaryKey is one selection compiled to N columns. Query._subquery_fields_len already compensates for this width elsewhere.

Ordering isn't listed among the composite-pk limitations in the docs, and tests/composite_pk/test_values.py already covers values_list("pk", "email"), so this is a supported path.

Found while verifying the fix for #37222. Jacob Walls agreed this is a bug on the PR thread: ​https://github.com/django/django/pull/21659#discussion_r3899304732

Fix with regression tests on my fork: ​https://github.com/davegaeddert/django/pull/4 — happy to open it against main if accepted.

(AI assistance: Claude Code was used to find and reproduce this and draft the fix; I verified the reproducer and test results.)