Azure / Azure/azure-sdk-for-python
Bad Request Error on cross-partition query with an ORDER BY clause
- Dominant language
- Python
- Stars
- 5.6k
- Forks
- 3.4k
- Avg merge
- 1d 21h
- Merged PRs (30d)
- 193
Description
- **Package Name**: `azure-cosmos`
- **Package Version**: `4.9.0`
- **Operating System**: Windows 10
- **Python Version**: `3.11.3`
**Describe the bug**
When executing a cross-partition query with an `ORDER BY` clause, the initial request for the first page of results succeeds and returns a continuation token. However, when a subsequent request is made with the exact same query and the provided token to fetch the next page, the Cosmos DB service returns an HTTP `400 Bad Request` with an "Invalid Continuation Token" error. This makes paginated, sorted queries across multiple partitions unusable.
**To Reproduce**
Steps to reproduce the behavior:
1. Create a Cosmos DB container with a partition key (e.g., `/user_id`).
2. Using the Python SDK, execute a cross-partition query that includes an `ORDER BY` clause but does not filter on the partition key.
* **Example Query:** `SELECT * FROM c WHERE c.conversationStartTime >= @start AND c.conversationStartTime <= @end ORDER BY c.conversationStartTime DESC`
3. The first request (with `continuation_token=None`) succeeds and returns a page of items and an opaque, non-JSON continuation token string.
4. Execute the exact same query again for the second page, providing the continuation token from the previous step.
**Expected behavior**
The second request should succeed and return the next page of sorted results, along with a new continuation token (or `None` if it's the last page).
**Screenshots**
If applicable, add screenshots to help explain your problem.
[error_order_by_logs.txt](https://github.com/user-attachments/files/20609093/error_order_by_logs.txt)
**Additional context**
This issue has been thoroughly debugged, and the following has been confirmed:
1. **Environment Stack:**
* Azure Functions Core Tools: `4.0.6280`
* Azure Functions Runtime: `4.834.3.22875`
2. **Token Integrity:** Through extensive logging down to the raw HTTP request, we have confirmed that the full, unmodified continuation token string is being correctly placed in the `x-ms-continuation` header. The token is not being truncated or corrupted by the client application, the SDK, or any networking components.
3. **Token Format Analysis:** We have verified that a query *without* the `ORDER BY` clause works perfectly with pagination. This working query uses a **structured JSON continuation token**. The failing `ORDER BY` query uses a different, **opaque string token format**. Our application handles both formats correctly by Base64-encoding them, proving the client-side logic is sound.
4. **Critical Workaround Discovery:** A successful workaround was found. If the query is modified to be a **single-partition query** by adding a `WHERE` clause that filters on the partition key (e.g., `... AND c.user_id = @user_id`), the pagination with `ORDER BY` works perfectly.
This analysis isolates the bug specifically to the server-side processing of **continuation tokens for cross-partition `ORDER BY` queries**.
Contributor guide
Assessment
This issue has not been assessed yet.