Cosmos: bad SQL generated when projecting out "Value"

[maumar]

query:

ss.Set<Customer>().Select(c => new { Id = c.CustomerID, Value = c.CompanyName.Contains(c.ContactName) })

Sql we generate:

SELECT c["CustomerID"] AS Id, CONTAINS(c["CompanyName"], c["ContactName"]) AS Value
FROM root c
WHERE (c["Discriminator"] = "Customer")

result:

Message: 
Microsoft.Azure.Cosmos.CosmosException : Response status code does not indicate success: BadRequest (400); Substatus: 0; ActivityId: ; Reason: (Response status code does not indicate success: BadRequest (400); Substatus: 0; ActivityId: ; Reason: ({"errors":[{"severity":"Error","location":{"start":78,"end":83},"code":"SC1001","message":"Syntax error, incorrect syntax near 'Value'."}]}););
---- Microsoft.Azure.Cosmos.Query.Core.Exceptions.ExpectedQueryPartitionProviderException : {"errors":[{"severity":"Error","location":{"start":78,"end":83},"code":"SC1001","message":"Syntax error, incorrect syntax near 'Value'."}]}
-------- System.Runtime.InteropServices.COMException : 0x800A0B00

  Stack Trace: 
QueryPartitionProvider.TryGetPartitionedQueryExecutionInfo(String querySpecJsonString, PartitionKeyDefinition partitionKeyDefinition, Boolean requireFormattableOrderByQuery, Boolean isContinuationExpected, Boolean allowNonValueAggregateQuery, Boolean hasLogicalPartitionKey, Boolean allowDCount, Boolean useSystemPrefix, GeospatialType geospatialType)
CosmosQueryClientCore.TryGetPartitionedQueryExecutionInfoAsync(SqlQuerySpec sqlQuerySpec, ResourceType resourceType, PartitionKeyDefinition partitionKeyDefinition, Boolean requireFormattableOrderByQuery, Boolean isContinuationExpected, Boolean allowNonValueAggregateQuery, Boolean hasLogicalPartitionKey, Boolean allowDCount, Boolean useSystemPrefix, GeospatialType geospatialType, CancellationToken cancellationToken)
AsyncMethodBuilderCore.Start[TStateMachine](TStateMachine& stateMachine)
CosmosQueryClientCore.TryGetPartitionedQueryExecutionInfoAsync(SqlQuerySpec sqlQuerySpec, ResourceType resourceType, PartitionKeyDefinition partitionKeyDefinition, Boolean requireFormattableOrderByQuery, Boolean isContinuationExpected, Boolean allowNonValueAggregateQuery, Boolean hasLogicalPartitionKey, Boolean allowDCount, Boolean useSystemPrefix, GeospatialType geospatialType, CancellationToken cancellationToken)
QueryPlanHandler.TryGetQueryInfoAsync(SqlQuerySpec sqlQuerySpec, ResourceType resourceType, PartitionKeyDefinition partitionKeyDefinition, Boolean hasLogicalPartitionKey, Boolean useSystemPrefix, GeospatialType geospatialType, CancellationToken cancellationToken)
QueryPlanHandler.TryGetQueryPlanAsync(SqlQuerySpec sqlQuerySpec, ResourceType resourceType, PartitionKeyDefinition partitionKeyDefinition, QueryFeatures supportedQueryFeatures, Boolean hasLogicalPartitionKey, Boolean useSystemPrefix, GeospatialType geospatialType, CancellationToken cancellationToken)
AsyncMethodBuilderCore.Start[TStateMachine](TStateMachine& stateMachine)
QueryPlanHandler.TryGetQueryPlanAsync(SqlQuerySpec sqlQuerySpec, ResourceType resourceType, PartitionKeyDefinition partitionKeyDefinition, QueryFeatures supportedQueryFeatures, Boolean hasLogicalPartitionKey, Boolean useSystemPrefix, GeospatialType geospatialType, CancellationToken cancellationToken)
QueryPlanRetriever.GetQueryPlanWithServiceInteropAsync(CosmosQueryClient queryClient, SqlQuerySpec sqlQuerySpec, ResourceType resourceType, PartitionKeyDefinition partitionKeyDefinition, Boolean hasLogicalPartitionKey, GeospatialType geospatialType, Boolean useSystemPrefix, ITrace trace, CancellationToken cancellationToken)
AsyncMethodBuilderCore.Start[TStateMachine](TStateMachine& stateMachine)
<32 more frames...>
Task`1.TrySetResult(TResult result)
UnwrapPromise`1.TrySetFromTask(Task task, Boolean lookForOce)
UnwrapPromise`1.ProcessInnerTask(Task task)
Task.RunContinuations(Object continuationObject)
Task.FinishSlow(Boolean userDelegateExecute)
Task.ExecuteWithThreadLocal(Task& currentTaskSlot, Thread threadPoolThread)
ThreadPoolWorkQueue.Dispatch()
WorkerThread.WorkerThreadStart()
----- Inner Stack Trace -----
----- Inner Stack Trace -----

  Standard Output: 
Compiling query expression: 
'DbSet<Customer>()
    .Select(c => new { 
        Id = c.CustomerID, 
        Value = c.CompanyName.Contains(c.ContactName)
     })'

Generated query execution expression: 
'queryContext => new QueryingEnumerable<<>f__AnonymousType71<string, bool>>(
    (CosmosQueryContext)queryContext, 
    SqlExpressionFactory, 
    QuerySqlGeneratorFactory, 
    [Cosmos.Query.Internal.SelectExpression], 
    Func<QueryContext, JObject, <>f__AnonymousType71<string, bool>>, 
    TestModels.Northwind.NorthwindContext, 
    None, 
    False, 
    True
)'

Executing SQL query for container 'NorthwindContext' in partition 'None' [Parameters=[]]
SELECT c["CustomerID"] AS Id, CONTAINS(c["CompanyName"], c["ContactName"]) AS Value
FROM root c
WHERE (c["Discriminator"] = "Customer")

Executed ReadNext (86.5268 ms, 0 RU) ActivityId='(null)', Container='NorthwindContext', Partition='None', Parameters=[]
SELECT c["CustomerID"] AS Id, CONTAINS(c["CompanyName"], c["ContactName"]) AS Value
FROM root c
WHERE (c["Discriminator"] = "Customer")

An exception occurred while iterating over the results of a query for context type 'Microsoft.EntityFrameworkCore.TestModels.Northwind.NorthwindContext'.
Microsoft.Azure.Cosmos.CosmosException : Response status code does not indicate success: BadRequest (400); Substatus: 0; ActivityId: ; Reason: (Response status code does not indicate success: BadRequest (400); Substatus: 0; ActivityId: ; Reason: ({"errors":[{"severity":"Error","location":{"start":78,"end":83},"code":"SC1001","message":"Syntax error, incorrect syntax near 'Value'."}]}););
 ---> Microsoft.Azure.Cosmos.Query.Core.Exceptions.ExpectedQueryPartitionProviderException: {"errors":[{"severity":"Error","location":{"start":78,"end":83},"code":"SC1001","message":"Syntax error, incorrect syntax near 'Value'."}]}
 ---> System.Runtime.InteropServices.COMException (0x800A0B00): 0x800A0B00
   --- End of inner exception stack trace ---

[roji]

Yeah, I just encountered this while working on the Cosmos query pipeline - Cosmos disallows projecting something with the alias Value using AS (case-insensitive). However, it's possible to use "JSON object projection style" instead:

SELECT VALUE {
    "Id": c["CustomerID"],
    "Value": CONTAINS(c["CompanyName"], c["ContactName"])
}

This is already fixed as part of my upcoming PR on primitive collections.

[maumar]

@roji make sure to enable the test that I skipped with this issue number