DocumentDb error "Cross partition query only supports 'VALUE <AggreateFunc>' for aggregates"

Question

I'm stumped.

I send the following SQL query to my documentDb instance.

{"query":"SELECT SUM(c.clicks), SUM(c.opens), MAX(c._ts), COUNT(c) FROM c WHERE c.clicks > 0"}

I recieve the following error:

<Message>An error has occurred.</Message>
<ExceptionMessage>
Message: {"Errors":["Cross partition query only supports 'VALUE <AggreateFunc>' for aggregates."]} ActivityId: 1e5a7d1e-b3a2-4700-9239-a777a682d868
</ExceptionMessage>
<ExceptionType>Microsoft.Azure.Documents.DocumentClientException</ExceptionType>
<StackTrace>
at Microsoft.Azure.Documents.Client.ClientExtensions.<ParseResponseAsync>d__4.MoveNext() --- End of stack trace from previous location where exception was thrown --- at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at System.Runtime.CompilerServices.TaskAwaiter.ValidateEnd(Task task) at Microsoft.Azure.Documents.GatewayStoreModel.<>c__DisplayClass10.<<InvokeAsync>b__f>d__12.MoveNext() --- End of stack trace from previous location where exception was thrown --- at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at Microsoft.Azure.Documents.BackoffRetryUtility`1.<>c__DisplayClass2.<<ExecuteAsync>b__0>d__4.MoveNext() --- End of stack trace from previous location where exception was thrown --- at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at Microsoft.Azure.Documents.BackoffRetryUtility`1.<ExecuteRetry>d__1b.MoveNext() --- End of stack trace from previous location where exception was thrown --- at Microsoft.Azure.Documents.BackoffRetryUtility`1.<ExecuteRetry>d__1b.MoveNext() --- End of stack trace from previous location where exception was thrown --- at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at Microsoft.Azure.Documents.BackoffRetryUtility`1.<ExecuteAsync>d__a.MoveNext() --- End of stack trace from previous location where exception was thrown --- at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at Microsoft.Azure.Documents.GatewayStoreModel.<InvokeAsync>d__1f.MoveNext() --- End of stack trace from previous location where exception was thrown --- at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at Microsoft.Azure.Documents.GatewayStoreModel.<ProcessMessageAsync>d__2.MoveNext() --- End of stack trace from previous location where exception was thrown --- at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at Microsoft.Azure.Documents.Client.DocumentClient.<ExecuteQueryAsync>d__328.MoveNext() --- End of stack trace from previous location where exception was thrown --- at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at Microsoft.Azure.Documents.Query.DocumentQueryClient.<ExecuteQueryAsync>d__a.MoveNext() --- End of stack trace from previous location where exception was thrown --- at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at Microsoft.Azure.Documents.Query.DocumentQueryExecutionContextBase.<ExecuteQueryRequestInternalAsync>d__38.MoveNext() --- End of stack trace from previous location where exception was thrown --- at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at Microsoft.Azure.Documents.Query.DocumentQueryExecutionContextBase.<ExecuteQueryRequestAsync>d__14.MoveNext() --- End of stack trace from previous location where exception was thrown --- at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at Microsoft.Azure.Documents.Query.DocumentQueryExecutionContextBase.<ExecuteRequestAsync>d__e.MoveNext() --- End of stack trace from previous location where exception was thrown --- at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at System.Runtime.CompilerServices.TaskAwaiter.ValidateEnd(Task task) at Microsoft.Azure.Documents.Query.DefaultDocumentQueryExecutionContext.<ExecuteOnceAsync>d__f.MoveNext() --- End of stack trace from previous location where exception was thrown --- at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at Microsoft.Azure.Documents.Query.DefaultDocumentQueryExecutionContext.<>c__DisplayClass2.<<ExecuteInternalAsync>b__1>d__4.MoveNext() --- End of stack trace from previous location where exception was thrown --- at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at Microsoft.Azure.Documents.BackoffRetryUtility`1.<>c__DisplayClass2.<<ExecuteAsync>b__0>d__4.MoveNext() --- End of stack trace from previous location where exception was thrown --- at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at Microsoft.Azure.Documents.BackoffRetryUtility`1.<ExecuteRetry>d__1b.MoveNext() --- End of stack trace from previous location where exception was thrown --- at Microsoft.Azure.Documents.BackoffRetryUtility`1.<ExecuteRetry>d__1b.MoveNext() --- End of stack trace from previous location where exception was thrown --- at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at Microsoft.Azure.Documents.BackoffRetryUtility`1.<ExecuteAsync>d__a.MoveNext() --- End of stack trace from previous location where exception was thrown --- at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at Microsoft.Azure.Documents.Query.DefaultDocumentQueryExecutionContext.<ExecuteInternalAsync>d__8.MoveNext() --- End of stack trace from previous location where exception was thrown --- at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at Microsoft.Azure.Documents.Query.DocumentQueryExecutionContextBase.<ExecuteNextAsync>d__4.MoveNext() --- End of stack trace from previous location where exception was thrown --- at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at Microsoft.Azure.Documents.Query.ProxyDocumentQueryExecutionContext.<ExecuteNextAsync>d__0.MoveNext()
</StackTrace>

Which is strange becuase I can run the SAME EXACT query via the portal and have it return the data I'm looking for.

Example:

What's going on?

UPDATE

• I am using version 1.17.0 of the Microsoft.Azure.DocumentDB client libraries
• I am using .Net Framework 4.6

Here's a snapshot of what the client produces via HTTP:

POST /dbs/<ignored>==/colls/<ignored>=/docs HTTP/1.1
x-ms-continuation: 
x-ms-documentdb-isquery: True
x-ms-documentdb-query-enablecrosspartition: True
x-ms-documentdb-query-iscontinuationexpected: False
x-ms-documentdb-populatequerymetrics: False
x-ms-date: Wed, 30 Aug 2017 16:26:35 GMT
authorization: <ignored>
Cache-Control: no-cache
x-ms-consistency-level: Eventual
User-Agent: documentdb-dotnet-sdk/1.17.0 Host/32-bit MicrosoftWindowsNT/10.0.15063.0
x-ms-version: 2017-02-22
Accept: application/json
Content-Type: application/query+json
Host: <ignored>-<ignored>.documents.azure.com
Content-Length: 94
Expect: 100-continue

{"query":"SELECT SUM(c.clicks), SUM(c.opens), MAX(c._ts), COUNT(c) FROM c WHERE c.clicks > 0"}

Distrust

Answer 1

this approach worked for me

SELECT VALUE COUNT(1) FROM telemetry T

Bivalve

SELECT VALUE MAX(c.externalId) FROM c where c.surveyId =123 – Annora

Answer 2

I trapped around the same problem. In my case wrapping of actual query into subquery solved the problem:

SELECT VALUE root
FROM 
(SELECT SUM(c.clicks) as sumclicks, SUM(c.opens) as sumopens, MAX(c._ts) as tsmax, COUNT(c) as count FROM c WHERE c.clicks > 0) as root

Hotchpotch

I feel bad for doing this, but it actually works ... (in my case it was the azure-cosmos python sdk and interestingly the value of enable_cross_partition_query didn't have any impact). I'd be interested in the details of why this works, if anyone can provide them :) – Clairvoyance

It works, and thanks for sharing, but wow, that's nasty 🤢 – Valorievalorization

It is mine speculation only but from error one may assume that it is being generated on client side by SDK during preprocessing of the query. Making query more complex probably forces SDK to give up and not interrupt in process with second guesses and just send query to CosmosDB as is – Hotchpotch

Answer 3

For queries scoped to a single partition key (or when the collection has no partition key definition), Cosmos DB supports multiple aggregations. Otherwise, you need to have a single VALUE and an aggregate.

As far as the difference between portal and SDK for the same collection, that doesn't seem correct. You should have the team investigate via a ticket/emailing [email protected].

Griqua

I am seeing the same issue. v2.0 of the SDK throws the above error and yet the portal is successful in running the exact same SQL – Cristobal

Answer 4

You can wrap your query into:

SELECT VALUE {
sumclicks: a.sumclicks,
sumopens: a.sumopens,
tsmax : a.tsmax,
count : a.count 
FROM 
(SELECT SUM(c.clicks) as sumclicks, SUM(c.opens) as sumopens, MAX(c._ts) as tsmax, COUNT(c) as count FROM c WHERE c.clicks > 0)
as a

Stereoisomerism