Exception "Cross partition query with TOP/.../... function is not supported"

[Van Cuong Nguyen]

Hello,

I have an issue when query data from a partitioned collection.

I create a CosmosDB with Mongo API on Azure and use Mongo Driver for .Net version 2.5.1 to query data.

I have a collection "data" that is partitioned with partition key "dataType".

In the .Net project, I create an Data model to present a document in "data" collection as below:

class Data

{

       [BsonElement("DataType")]

       public string DataType {get; set;}

       public long DateTime {get; set; }

}

I queried data form "data" collection with the statement below:

var filter = new BsonDocument("dataType", "Temperature");

SensorDatas.Find(filter).SortByDescending(s => s.DateTime).ToListAsync();

This statement works well when "data" collection is a single partition collection.

But with partitioned collection, I got an exception with message:

"Cross partition query with TOP/ORDER BY or aggregate functions is not supported"

This issue only happens when the number of document in result is bigger batch size (Default batch size = 101).

I changed the query statement to get number of document instead of list document, it works:
SensorDatas.Find(filter).SortByDescending(s => s.DateTime).Count();

So I think, the exception happens when mongo driver downloads list document in CosmosDB by many batches.

I also test with Robo3t with the query below, it worked:
db.getCollection('data').find({dataType: 'Temperature'}).sort({dateTime: -1})

I applied a workaround that setting batch size in FindOptions to a big number (One billion), to be sure all documents a return in a batch.

But I think, it's not a good solution.

Did I do any wrong thing?
Could some one give me some advice to resolve this issue?

Thanks and best regards,
Cuong Nguyen

[Stephen Steneker]

On Saturday, 21 April 2018 02:35:11 UTC+10, Van Cuong Nguyen wrote:

I create a CosmosDB with Mongo API on Azure and use Mongo Driver for .Net version 2.5.1 to query data.

Hi,

CosmosDB with the MongoDB API is an independent server implementation that does not directly align with MongoDB server versions and features. CosmosDB has some different behaviours and results, particularly with their implementation of partitioning as compared to MongoDB's sharding.

Please use Azure/CosmosDB support channels for assistance, or consider using MongoDB Atlas on Azure if you'd like full MongoDB feature support.

Regards,

Stennie

[Van Cuong Nguyen]

Hi   Stennie,

Thanks for your recommendation. It's really helpful for me. I'll study more on MongoDB Atlas. Hope it'll resolve my issue.

Regards,

Van Cuong

--
You received this message because you are subscribed to the Google Groups "mongodb-user"
group.
 
For other MongoDB technical support options, see: https://docs.mongodb.com/manual/support/
---
You received this message because you are subscribed to the Google Groups "mongodb-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mongodb-user+unsubscribe@googlegroups.com.
To post to this group, send email to mongod...@googlegroups.com.
Visit this group at https://groups.google.com/group/mongodb-user.
To view this discussion on the web visit https://groups.google.com/d/msgid/mongodb-user/e9cc44e6-f7ad-47d6-a7e9-ce99ac4674ee%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

--

NGUYEN VAN CUONG

School of Mechanical Engineering  

Hanoi University of Science and Technology

Tel: +84 167 571 6467

Email: vancu...@gmail.com

[Van Cuong Nguyen]

Hi,

I'm still considering if the issue is caused by from CosmosDB server or  by Mongo Driver for .Net version 2.5.1.
Because I can query data on CosmosDB with Robo3t.

It seems the issue comes from  Mongo Driver for .Net version 2.5.1 or I did something wrong. I'm not sure.

Hopefully, someone helps me to point out the cause of this issue.

Regards,

Van Cuong

[Stephen Steneker]

On Thursday, 24 May 2018 01:26:39 UTC+10, Van Cuong Nguyen wrote:

I'm still considering if the issue is caused by from CosmosDB server or  by Mongo Driver for .Net version 2.5.1.
Because I can query data on CosmosDB with Robo3t.

It seems the issue comes from  Mongo Driver for .Net version 2.5.1 or I did something wrong. I'm not sure.

Hi,

The error message you are encountering is from the CosmosDB server:

"Cross partition query with TOP/ORDER BY or aggregate functions is not supported" 

As I noted earlier, CosmosDB is an independent server implementation. CosmosDB supports a subset of the MongoDB API and translates requests into the CosmosDB SQL equivalent. The CosmosDB server behaviour is not identical in many cases, but the onus is on CosmosDB to improve their emulation of MongoDB. MongoDB drivers are not tested with CosmosDB or expected to handle non-standard behaviour.

The sort clause in your example query translates into an ORDER BY in CosmosDB's SQL, which apparently is not supported for queries that span multiple partitions in a collection. You may be able to mitigate this error by limiting the query range to a single partition based on the partition key, but your best option for support is the CosmosDB documentation or support channels.

If a similar query works from Robo3t, I expect there is a difference in the query being sent to the CosmosDB server. For example, by default Robo3t sets a limit of 50 results.

If your goal is to learn MongoDB using a Database-as-a-Service platform, I would recommend using MongoDB Atlas. You can also download the MongoDB Community Server if you would like to set up a deployment in your own server or development environment.

Regards,

Stennie

[Van Cuong Nguyen]

Hi,

Thanks so much for your supporting.

It's a big help.

Regards,

Van Cuong

Vào 04:59, T.5, 24 Th5, 2018 'Stephen Steneker' via mongodb-user <mongod...@googlegroups.com> đã viết:

--

You received this message because you are subscribed to the Google Groups "mongodb-user"
group.
 
For other MongoDB technical support options, see: https://docs.mongodb.com/manual/support/
---
You received this message because you are subscribed to the Google Groups "mongodb-user" group.

To unsubscribe from this group and stop receiving emails from it, send an email to mongodb-user...@googlegroups.com.

To post to this group, send email to mongod...@googlegroups.com.
Visit this group at https://groups.google.com/group/mongodb-user.

To view this discussion on the web visit https://groups.google.com/d/msgid/mongodb-user/e17f76f5-620e-41d4-ae3c-b780cf3e96f3%40googlegroups.com.