index-composite: elevate WHERE+ORDER BY composite index guidance to top-level examples

[jaydestro]

Summary

The index-composite rule's top-level examples show pure ORDER BY queries without WHERE filters. Guidance for combining equality filters with ORDER BY exists in the Multi-Tenant Composite Index Patterns subsection (commit 2272f6e), which states First path can be equality filter and Include /type as the first path. However, agents do not reliably reach this subsection.

SCOPE Java Gaming Leaderboard V-C: 3/7 code-producing profiles (43%) -- including P02 which HAD the Agent Kit -- define composite indexes matching only the ORDER BY clause, omitting the WHERE equality filter field. This causes BadRequestException: The order by query does not have a corresponding composite index on every leaderboard read.

Evidence

Query pattern (all 3 affected profiles):

SELECT * FROM c WHERE c.type = @type ORDER BY c.bestScore DESC, c.playerId ASC

Incorrect composite index (generated by agents):

[{"path": "/bestScore", "order": "descending"}, {"path": "/playerId", "order": "ascending"}]

Correct composite index:

[{"path": "/type", "order": "ascending"}, {"path": "/bestScore", "order": "descending"}, {"path": "/playerId", "order": "ascending"}]

Profile	Agent Kit?	Result
P02R01	Yes	500 on all leaderboard reads
P04R01	No	500 on global, regional, rank
P07R01	No	500 on global, regional

Recommended Fix

1. Elevate the WHERE+ORDER BY pattern from the Multi-Tenant subsection to the first correct/incorrect example pair in the rule (before existing pure ORDER BY examples).

2. Add a warning callout above top-level examples:

   If your query has a WHERE clause, equality filter fields MUST appear as the first elements of the composite index. A composite index matching only ORDER BY fields will be rejected at runtime.

3. Include both approaches per official docs:

   • Option A: Include filter field in composite index (recommended)
   • Option B: Add filter field to ORDER BY clause

References

• Queries with a filter and ORDER BY
• Composite indexes documentation
• SCOPE evaluation: Java Gaming Leaderboard V-C (April 2026)

---

Additional Justification (Production + Emulator Validation)

This is a Cosmos DB service-level rejection, not emulator-specific

The BadRequestException with error "The order by query does not have a corresponding composite index that it can be served from" is returned by the Cosmos DB query engine (status code 400, sub-status 0). This behavior is identical on:

• Azure Cosmos DB production accounts (any region, any SKU)
• Azure Cosmos DB Windows Emulator (local)
• Azure Cosmos DB Linux vNext Emulator (Docker)

The query engine validates composite index availability before executing the query. A missing equality filter field in the composite index causes immediate rejection ΓÇö the query never runs.

What the existing rule currently says (top-level examples)

The index-composite rule's first correct/incorrect example pair shows:

Incorrect (from rule):

var query = @"
    SELECT * FROM c 
    WHERE c.status = 'active' 
    ORDER BY c.createdAt DESC, c.priority ASC";
// Without composite index, this may:
// 1. Fail with: "Order-by item requires a corresponding composite index"

Correct (from rule):

// Must match ORDER BY exactly (properties and sort order)
new CompositePath { Path = "/createdAt", Order = CompositePathSortOrder.Descending },
new CompositePath { Path = "/priority", Order = CompositePathSortOrder.Ascending }

Note: The "correct" example defines a composite index on (/createdAt, /priority) ΓÇö the ORDER BY fields only. It does not include /status (the WHERE equality filter field) in the composite index. This example works only because /status is a WHERE equality filter with /* included paths (default policy includes everything). When agents use exclude-all-first indexing (as recommended by index-exclude-unused), the equality filter field MUST be explicitly included in the composite index.

The Multi-Tenant Composite Index Patterns subsection (added later, commit 2272f6e) correctly shows /type as the first composite path, but this guidance is in a subsection that agents skip for non-multi-tenant scenarios.

What the MS Learn documentation says (quoted)

From Queries with a filter and ORDER BY:

"If the query filters on properties, these properties should be included first in the ORDER BY clause."

And the docs provide this example transformation:

-- Query using range index (works but suboptimal):
SELECT * FROM c WHERE c.name = "John" ORDER BY c.timestamp

-- Query using composite index (recommended):
SELECT * FROM c WHERE c.name = "John" ORDER BY c.name, c.timestamp

The documentation explicitly states that filter properties should be added to the ORDER BY clause, which forces them into the composite index definition. The rule should present both options:

• Option A: Add the filter field to the composite index definition directly (simpler)
• Option B: Add the filter field to the ORDER BY clause per MS docs (self-documenting)

V-C SCOPE Evidence

Profile	Agent Kit?	Query	Composite Index Defined	Missing Field	Runtime Result
P02R01	Yes	WHERE c.type = @type ORDER BY c.bestScore DESC, c.playerId ASC	[/bestScore DESC, /playerId ASC]	/type	BadRequestException 400 on all leaderboard reads
P04R01	No	Same pattern	Same deficiency	/type	BadRequestException 400 on global, regional, rank
P07R01	No	Same pattern	Same deficiency	/type	BadRequestException 400 on global, regional

P02 had the Agent Kit installed, read the index-composite rule, and still produced this bug ΓÇö proving the top-level examples are insufficient.

3/7 code-producing profiles (43%) hit this bug. It is the most impactful recurring V-C runtime failure.

[jaydestro]

Closing per SCOPE triage rules: only 1 AK profile (P02) hit this in V-C, with non-AK profiles P04/P07 also affected. Per updated triage policy, single-AK-profile/single-run failures are anomalies — the pattern must reproduce across multiple AK profiles to warrant a rule enhancement. The non-AK hits (P04, P07) are not actionable since we cannot steer non-AK profiles with rules.