DocumentDb error "Cross partition query only supports 'VALUE ' 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"}

score 15 · Answer 1 · answered Jun 17 '18 at 04:32

15

this approach worked for me

SELECT VALUE COUNT(1) FROM telemetry T

answered Jun 17 '18 at 04:32

Behnam Abdy

345
5
10

SELECT VALUE MAX(c.externalId) FROM c where c.surveyId =123 – PNC Nov 03 '18 at 00:39

score 13 · Answer 2 · answered Feb 29 '20 at 15:55

13

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

answered Feb 29 '20 at 15:55

Vitaliy Kalinin

1,791
12
20

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 :) – Gerrit-K Aug 02 '21 at 12:44

score 2 · Answer 3 · answered Sep 28 '17 at 19:20

2

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 askcosmosdb@microsoft.com.

answered Sep 28 '17 at 19:20

Aravind Krishna R.

7,885
27
37

4

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 – atomaras Nov 11 '19 at 22:04

score 0 · Answer 4 · answered Mar 03 '23 at 09:41

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

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

4 Answers4

Linked

Related