Execution of Stored Procedure seems to ignore passed in Partition Key Value

Question

Execution of Stored Procedure seems to ignore passed in Partition Key Value

Mike Lee 81

I've been getting up to speed with Store Procedures with Cosmos DB and I've found that when I execute a sp i wrote the returned docs ignore the partition key value I pass in.

For example, a couple of docs that look something like this (partition key is /city):

{
"projectId": 100,
"projectName": "civil bridge 1",
"city": "chicago"
}

{
"projectId": 101,
"projectName": "civil bridge 2",
"city": "chicago"
}

{
"projectId": 103,
"projectName": "civil bridge 3",
"city": "seattle"
}

The stored procedure is passed in a project name search term as a parameter. So if the term passed in is "bridge" the documents that have the term "bridge" should be returned.

When I execute the stored procedure using "bridge" as the search term and a partition value of "chicago" I would expect to see the first 2 docs returned. However, during my testing I'm seeing all 3 docs returned.

Does anyone know why I'm seeing results from outside the partition I specified when I called the stored procedure?

Thanks,

Mike

Anurag Sharma 17,381 Reputation points

2020-06-16T15:25:33.633+00:00

Really appreciate the details provided with respect to query.
You are correct and the procedure should return only 2 docs as mentioned by you.

As per the functionality of Azure Cosmos DB stored procedure:
"For partitioned containers, when executing a stored procedure, a partition key value must be provided in the request options. Stored procedures are always scoped to a partition key. Items that have a different partition key value will not be visible to the stored procedure. This also applied to triggers as well."

Can you please check the partition key once on the container? Also could you provide the procedure code although I don't think it would matter here.
Mike Lee 81 Reputation points

2020-06-16T15:35:33.277+00:00
Thanks for the reply back @AnuragSharma-0605

I performed some additional troubleshooting and found something interesting. If I use .queryDocuments() or .readDocuments() in my stored procedure the resulting documents I receive in the callback are scoped to the partition key I sent when I executed the stored procedure (which is exactly what I'm expecting).

However, if I use Javascript Language Integrated Query instead the resulting documents ignore the partition key I sent.

For example, if I use the following to get all the documents in the partition key I sent:

__.chain() .filter(function(doc) { return true }) .value()

I end up seeing results from all partition key values and not just the one I sent to the stored procedure.

Shouldn't using chain and filter as I have it above bring back the same results of __.readDocuments()?

Mike
Mark Brown - MSFT 2,751 Reputation points Microsoft Employee

2020-06-17T05:31:09.41+00:00

Impossible to answer this without seeing both the SDK call to the stored proc as well as the stored proc itself.
Mike Lee 81 Reputation points

2020-06-17T13:30:03.147+00:00
Below are the two stored procedures I've been testing with. I've stripped away everything to the bare essentials.

To test I've been executing them directly from Data Explorer. I supply the same partition key value (numerical value of 100 in my case) when I execute both SP's. When I execute SP1 I see the first 100 documents returned have partition key values that have many values other than 100. If I execute SP2 I get 2 documents back which is correct since I only have 2 documents with a partition key value of 100.

SP1

function sample() { __.chain() .filter(function(doc) { return true }) .value((err, documents, responseOptions) => { __.response.setBody(documents); }) }

SP2

function sample2() { const status = __.readDocuments(__.getSelfLink(), (err, documents, responseOptions) => { __.response.setBody(documents); }); }

Anurag Sharma 17,381 Reputation points

2020-06-16T15:25:33.633+00:00

Really appreciate the details provided with respect to query.
You are correct and the procedure should return only 2 docs as mentioned by you.

As per the functionality of Azure Cosmos DB stored procedure:
"For partitioned containers, when executing a stored procedure, a partition key value must be provided in the request options. Stored procedures are always scoped to a partition key. Items that have a different partition key value will not be visible to the stored procedure. This also applied to triggers as well."

Can you please check the partition key once on the container? Also could you provide the procedure code although I don't think it would matter here.
Mike Lee 81 Reputation points

2020-06-16T15:35:33.277+00:00

Thanks for the reply back @AnuragSharma-0605

I performed some additional troubleshooting and found something interesting. If I use .queryDocuments() or .readDocuments() in my stored procedure the resulting documents I receive in the callback are scoped to the partition key I sent when I executed the stored procedure (which is exactly what I'm expecting).

However, if I use Javascript Language Integrated Query instead the resulting documents ignore the partition key I sent.

For example, if I use the following to get all the documents in the partition key I sent:

__.chain() .filter(function(doc) { return true }) .value()

I end up seeing results from all partition key values and not just the one I sent to the stored procedure.

Shouldn't using chain and filter as I have it above bring back the same results of __.readDocuments()?

Mike
Mark Brown - MSFT 2,751 Reputation points Microsoft Employee

2020-06-17T05:31:09.41+00:00

Impossible to answer this without seeing both the SDK call to the stored proc as well as the stored proc itself.
Mike Lee 81 Reputation points

2020-06-17T13:30:03.147+00:00

Below are the two stored procedures I've been testing with. I've stripped away everything to the bare essentials.

To test I've been executing them directly from Data Explorer. I supply the same partition key value (numerical value of 100 in my case) when I execute both SP's. When I execute SP1 I see the first 100 documents returned have partition key values that have many values other than 100. If I execute SP2 I get 2 documents back which is correct since I only have 2 documents with a partition key value of 100.

SP1

function sample() { __.chain() .filter(function(doc) { return true }) .value((err, documents, responseOptions) => { __.response.setBody(documents); }) }

SP2

function sample2() { const status = __.readDocuments(__.getSelfLink(), (err, documents, responseOptions) => { __.response.setBody(documents); }); }

Answer 1

Mark Brown - MSFT 2,751 Microsoft Employee

This is a known issue where JS query api doesn’t treat PK value while queryDocuments implicitly adds to the query.

If you add the PK condition manually to the __.filter it will work.

Thanks.

Mike Lee 81 Reputation points

2020-06-18T13:46:07.637+00:00

Good to know!

Is there a site or blog that covers a list of known issues or things to know about Cosmos? Looking for something that would help new users of Cosmos so they don't spin their wheels wondering why something isn't working as expected.

Thanks
Mark Brown - MSFT 2,751 Reputation points Microsoft Employee

2020-06-18T13:54:58.847+00:00

Here or Stack Overflow are generally the best places. We generally fix bugs that people come across. Just so happened this one got missed.

Thanks.
David Fowler 1 Reputation point

2021-08-27T11:50:47.187+00:00

Hi @Mark Brown - MSFT ,

There is an issue logged in this repo:

https://github.com/Azure/azure-cosmosdb-js-server/issues/28

It was logged on 22 Jun 2018 and there has been no response. There seems to be no recent activity on this project. Is it deprecated?

The filter issue strikes as quite a serious bug - even if it is considered a feature, surely it should be flagged in the documentation.

Execution of Stored Procedure seems to ignore passed in Partition Key Value

0 additional answers

Activity