Exception occurs performing aggregate queries on x64 platform

Question

When running a query using aggregates I receive the following exception:

Microsoft.Azure.Documents.BadRequestException: 
{"Errors":["Cross partition query only supports 'VALUE <AggreateFunc>' for aggregates."]} 
---> System.Runtime.InteropServices.COMException: Exception from HRESULT: 0x800A0B00

My query code is:

var store = "1234";
var sumResults = client.CreateDocumentQuery(
UriFactory.CreateDocumentCollectionUri("my-data-db", "my-collection"),
$"SELECT SUM(t.totalAmount) FROM t where t.siteData.siteID = '{store}'");

It took me a while to figure out this works fine when the build platform is set to x86 but fails when set to x64.

I'm using the latest version of the package 1.12.2

Any ideas on how to make the x64 version work?

Answer 1

According to your description, I used Microsoft.Azure.DocumentDB 1.12.2 to test the similar query both on DocomentDB Emulator and Azure DocumentDB. After some trials, I found this issue only occurs when you are using the SQL aggregate functions (e.g. COUNT, MIN, MAX, SUM, and AVG) against DocomentDB Emulator (my current version is 1.11.136.2) and your project is built on x64.

As this official document mentioned about aggregates with Azure DocumentDB:

Aggregate support has been rolled out to all DocumentDB production datacenters. You can start running aggregate queries against your existing DocumentDB accounts or provision new DocumentDB accounts via the SDKs, REST API, or the Azure Portal. You must however download the latest version of the SDKs in order to perform cross-partition aggregate queries or use LINQ aggregate operators in .NET.

I assumed that you could leverage aggregates with LINQ as follows:

var amount=client.CreateDocumentQuery<DocEntity>("/dbs/{your-db-id}/colls/{your-collection-id}",
     new FeedOptions { MaxDegreeOfParallelism = -1 })
      .Where(r => r.siteID == "1234")
      .Sum(r=>r.totalAmount);

We could find the above query would generate the following query:

{"query":"SELECT VALUE Sum(root[\"totalAmount\"]) FROM root WHERE (root[\"siteID\"] =\"1234\") "}

For more details, you could refer to this tutorial.

Answer 2

You need to put Value in your query, like this

SELECT VALUE COUNT(1) FROM C