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"}

Answer 1

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

Answer 2

this approach worked for me

SELECT VALUE COUNT(1) FROM telemetry T