"select count(1) from docs c" does not work

[sdg002]

Issue

What works?

SELECT c.id FROM docs c

What did not work?

SELECT count(1) FROM docs c

Powershell code

$queryForCount="SELECT  count(1) FROM  docs c"
$docsCount=Get-CosmosDbDocument -context $cosmosDbContext -CollectionId $collnSleep -Query $queryForCount -QueryEnableCrossPartition $true  -Verbose
$docsCount

Error

VERBOSE: Context does not contain any resource tokens.
VERBOSE: Creating authorization token: Method = 'Post', ResourceType = 'docs', ResourceId = 'dbs/participant/colls/v2participantprofiles', Date = '21/06/2020 23:16:31'.
VERBOSE: POST https://********db.documents.azure.com/dbs/participant/colls/v2participantprofiles/docs with -1-byte payload
VERBOSE: {"code":"BadRequest","message":"Message: {\"Errors\":[\"Cross partition query only supports 'VALUE <AggreateFunc>' for aggregates.\"]}\r\nActivityId: c13ed39c-15aa-4cd8-97f8-e90928200135, Microsoft.Azure.Documents.Common/2.11.0"}
Invoke-WebRequest : The remote server returned an error: (400) Bad Request.
At C:\Users\corresilience\Documents\WindowsPowerShell\Modules\CosmosDB\4.2.1\CosmosDB.psm1:1229 char:30
+ ...        $requestResult = Invoke-WebRequest @invokeWebRequestParameters
+                             ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (System.Net.HttpWebRequest:HttpWebRequest) [Invoke-WebRequest], WebException
    + FullyQualifiedErrorId : WebCmdletWebResponseException,Microsoft.PowerShell.Commands.InvokeWebRequestCommand

Before submitting your issue for the CosmosDB project, please take a moment to provide the following details:

• [ 5.1.18362.752 ] Version of PowerShell you're using
• [ISE ] PowerShell host you're using (eg. Console Host, ISE, Visual Studio)
• [ Windows 10] Operating system you're running
• [ 4.2.1] Version of CosmosDB PowerShell Module you're using (use Get-Module -Name CosmosDB)

Thanks for contributing your feedback and support! You can optionally submit a Pull Request against this project, if you have a fix you'd like to share.

Any suggestions? Getting the count would be really useful.

Thank you.

[PlagueHO]

Hi @sdg002,

Can you try the following query:

$queryForCount="SELECT VALUE COUNT(1) FROM  docs c"

and see if that works?

[sdg002]

Thanks for the quick response.

Similar behaviour:

VERBOSE: Context does not contain any resource tokens.
VERBOSE: Creating authorization token: Method = 'Post', ResourceType = 'docs', ResourceId = 'dbs/participant/colls/v2participantprofiles', Date = '22/06/2020 09:17:31'.
VERBOSE: POST https://********db.documents.azure.com/dbs/participant/colls/v2participantprofiles/docs with -1-byte payload
VERBOSE: {"code":"BadRequest","message":"The provided cross partition query can not be directly served by the gateway. This is a first chance (internal) exception that all newer clients will know how to ha
ndle gracefully. This exception is traced, but unless you see it bubble up as an exception (which only happens on older SDK clients), then you can safely ignore this message.\r\nActivityId: c685a914-3b17-4
d33-8112-a0d7350004dd, Microsoft.Azure.Documents.Common/2.11.0","additionalErrorInfo":"{\"partitionedQueryExecutionInfoVersion\":2,\"queryInfo\":{\"distinctType\":\"None\",\"top\":null,\"offset\":null,\"li
mit\":null,\"orderBy\":[],\"orderByExpressions\":[],\"groupByExpressions\":[],\"groupByAliases\":[],\"aggregates\":[\"Count\"],\"groupByAliasToAggregateType\":{},\"rewrittenQuery\":\"SELECT VALUE [{\\\"ite
m\\\": COUNT(1)}]\\nFROM docs AS c\",\"hasSelectValue\":true},\"queryRanges\":[{\"min\":\"\",\"max\":\"FF\",\"isMinInclusive\":true,\"isMaxInclusive\":false}]}"}
Invoke-WebRequest : The remote server returned an error: (400) Bad Request.
At C:\Users\corresilience\Documents\WindowsPowerShell\Modules\CosmosDB\4.2.1\CosmosDB.psm1:1229 char:30
+ ...        $requestResult = Invoke-WebRequest @invokeWebRequestParameters
+                             ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (System.Net.HttpWebRequest:HttpWebRequest) [Invoke-WebRequest], WebException
    + FullyQualifiedErrorId : WebCmdletWebResponseException,Microsoft.PowerShell.Commands.InvokeWebRequestCommand

[PlagueHO]

Hi @sdg002 - Thank you for checking that for me! That is an interesting message and seems to indicate some condition that is handled by the SDK. So I'll need to track down what that is. Leave it with me till the weekend when I'll have some time to dig into it.

[PlagueHO]

Hi @sdg002 - can you confirm you have partitioned your collection and what your partition key is?

[PlagueHO]

I've managed to track down the issue, but don't yet have a solution. See this document: https://docs.microsoft.com/en-us/rest/api/cosmos-db/querying-cosmosdb-resources-using-the-rest-api#queries-that-cannot-be-served-by-gateway

It appears that the CosmosDB SDK client knows how to deal with this problem, but it is not documented as far as I can tell. I suspect the CosmosDB SDK bypasses the gateway when a non-supported aggregation. The best way of dealing with this is to probably re-create this entire module wrapping the .NET Core Cosmos DB SDK - which is something I've been considering for some time, but haven't got the time to do it.

[itpropro]

Unfortunately, it is still not possible to do a COUNT(1) query, did you came further with your fix @PlagueHO ?