[CosmosDB] Query fails when setting partition_key

[chrisyeh96]

• Package Name: azure-cosmos
• Package Version: 4.1.0
• Operating System: Ubuntu 18.04 LTS
• Python Version: 3.8

Describe the bug
I created a test CosmosDB database using the Azure web portal with the examples from the CosmosDB Getting Started documentation.

{
  "id": "AndersenFamily",
  "children": ["..."],
  "..."
},
{
  "id": "WakefieldFamily",
  "children": ["..."],
  "..."
},

I set the partition_key on the container to be the "id" field. I can run the following queries (from the SQL Query Object Array page) via the "Data Explorer" on the Azure web portal just fine, but the Python SDK has issues when I set partition_key on the query.

The following two queries work fine in Data Explorer on the Azure web portal:

SELECT * FROM Families.children
SELECT * FROM c IN Families.children

Both queries throw errors in the Python SDK when I pass a partition_key parameter to query_items():

import os
from azure.cosmos.cosmos_client import CosmosClient

client = CosmosClient(os.environ['COSMOS_ENDPOINT'], credential=os.environ['COSMOS_KEY'])
container = client.get_database_client('testdb').get_container_client('testcontainer')
query1 = 'SELECT * FROM Families.children'
query2 = 'SELECT * FROM c IN Families.children'

# these work just fine
list(container.query_items(query=query1, enable_cross_partition_query=True))
list(container.query_items(query=query2, enable_cross_partition_query=True))

# both of these throw the same error
list(container.query_items(query=query1, partition_key='AndersenFamily'))
list(container.query_items(query=query2, partition_key='AndersenFamily'))

CosmosHttpResponseError: (BadRequest) Message: {"errors":[
  {"severity":"Error","location":{"start":4294967295,"end":4294967295},"code":"SC2001","message":"Identifier 'Families' could not be resolved."}]}
ActivityId: 5e53c5f6-43bc-43ee-90a7-4e39d48aa271, [...]

Expected behavior
Setting partition_key on query_items() should not cause an error.

Additional context
I suspect that this may be related to the bug reported in #11721. However, my example here is much simpler and identifies the issue with passing a partition_key argument to query_items().

[ghost]

Thanks for the feedback! We are routing this to the appropriate team for follow-up. cc @wmengmsft, @MehaKaushik, @shurd, @anfeldma-ms

[southpolesteve]

@Rodrigossz Can you look into this? May be an issue with the samples for how to do query

[ghost]

Thanks for the feedback! We are routing this to the appropriate team for follow-up. cc @zfoster, @southpolesteve.

[Rodrigossz]

Hello @chrisyeh96

Tks for the feedback. We will check it out. As a workaround you can use 'select children from Families'. BTW, you sample code has 'testcontainer' instead of Families, but I think that it's a copy/paste error. Tks for the contribution!

[Rodrigossz]

Hello @chrisyeh96

I'm closing this issue because it's a documentation problem, what I will start to address today.

Basically what is happening is that your are narrowing down the scope with the FROM clause: instead of Families, and all of it's properties, your "dataset" only has the children property. And the partition key isn't there.

Tks for the help to improve the product.

[chrisyeh96]

@Rodrigossz Thanks for looking into this issue. However, I'm not convinced that narrowing down the scope in the FROM clause is the problem. As explained in the official CosmosDB SQL syntax for the FROM clause, FROM Families.children is certainly valid syntax. It is a <container_expression> '.' property_name.

Plus, the queries work just fine in Data Explorer on the Azure web portal. This is clearly an issue with the Python SDK, not with my query.

[Rodrigossz]

@chrisyeh96

Data Explorer uses another SDK behind the interface and has different environment settings. As an example, it runs all queries as cross partition enabled. So you can expect absolutely different behavior between any SDK and Data Explorer. When you use partition_key='WakefieldFamily' with the SDK's query_items method, the SDK will add and id = 'WakefieldFamily' to your query string.

If you try the same in the Data Explorer you will get:

• SELECT * FROM c IN Families.children where Familes.id = 'WakefieldFamily' => Will return an error
• SELECT * FROM c IN Families.children where id = 'WakefieldFamily' => Will return an error
• SELECT * FROM c IN Families.children where c.id = 'WakefieldFamily' => Will return no data
• SELECT * FROM c IN Families.children where 'id' = 'WakefieldFamily' => Will return no data

Please note that for

query1 = 'SELECT * FROM Families.children'
list(container.query_items(query=query1, partition_key='AndersenFamily'))

The SDK will transform into SELECT * FROM Families.children where Families.children.id = 'AndersenFamily' , what doesn't work.

I am adding clarifications on that to the Python SDK README file.