Flexible Sync - Rules and Queryable Fields

Hi there,

I recently switched from Partition Based to Flexible Sync in order to use Realm with my new Flutter app.

I have a collection Trades with the following schema and the following rules:

{
  "title": "Trades",
  "bsonType": "object",
  "required": [
    "_id",
    "_partition"
  ],
  "properties": {
    "_id": {
      "bsonType": "objectId"
    },
    "_partition": {
      "bsonType": "string"
    },
    "name": {
      "bsonType": "string"
    },
    "imageBlobURL": {
      "bsonType": "string"
    },
    "isCompanyTrade": {
      "bsonType": "bool"
    }
  }
}
{
  "roles": [
    {
      "name": "default",
      "apply_when": {},
      "document_filters": {
        "write": {},
        "read": {}
      },
      "read": true,
      "write": true,
      "insert": true,
      "delete": true,
      "search": true
    }
  ]
}

The _partition queryable field is made like this:

_partition: "companyId=<ID_OF_THE_COMPANY>,parentId=trades"

where <ID_OF_THE_COMPANY> is either the id of the company of the user or shared if the Trades object is shared by all the companies.

When I was using Partition Based Sync, I was able to query the Trades objects with both <ID_OF_THE_COMPANY> = a “real” company id and <ID_OF_THE_COMPANY> = shared.
However, with Flexyble Sync, I’m only able to query Trades objects with a real company id.

Here is the Flutter code I use:

final config = Configuration.flexibleSync(REALM_USER!, [Trades.schema]);
final realm = Realm(config);

final trades = realm.all<Trades>().sortedBy((element) => element.name ?? "");

This give my only the Trades objects with a realm company id and not the ones which are shared.

final config = Configuration.flexibleSync(REALM_USER!, [Trades.schema]);
final realm = Realm(config);

realm.subscriptions.update((mutableSubscriptions) {
      mutableSubscriptions.add(realm.query<Trades>(
          r'_partition == $0', ["companyId=shared,parentId=trades"]));
    });

This give me the following error:

[INFO] Realm: Connection[1]: Session[1]: Received QUERY_ERROR “Client provided query with bad syntax: the indexed queryable field must be ANDed with the remainder of the query” (error_code=300, query_version=253)

Thanks for your help!

Hey Julien, can you set the log level to trace and run the client side app and attach the log? I’d be curious to see what is the actual query that the client sends to the server - it doesn’t appear like there’s anything wrong with your code, but it’s possible you’re hitting some corner case in the query parser we didn’t account for.

Hi @nirinchev,

Thanks for your help.

I’ve just noticed that when I delete the app and install it again I’m able to get the shared Trades or the company’s Trades (depending on which one I put on the realm.subscriptions.update()).

However, if I install the app with realm.subscriptions.update() containing either shared or company id, I can’t switch even if I log the user out or re-run the app (only if I delete and reinstall the app).

Here are the logs:

flutter: 2024-01-17T11:58:24.257719: [DETAIL] Realm: DB: 3041 Thread 0x16fd33000: Open file: /Users/xxx/Library/Developer/CoreSimulator/Devices/xxx/data/Containers/Data/Application/xxx/Documents/mongodb-realm/appquote-vyxhe/server-utility/metadata/sync_metadata.realm


flutter: 2024-01-17T11:58:24.258249: [TRACE] Realm: DB: 3041 Thread 0x16fd33000: Start read 22175: 14 ref 7008


flutter: 2024-01-17T11:58:24.258503: [TRACE] Realm: DB: 3041 Thread 0x16fd33000: End transaction 22175


flutter: 2024-01-17T11:58:24.258728: [TRACE] Realm: DB: 3041 Thread 0x16fd33000: Start read 22175: 14 ref 7008


flutter: 2024-01-17T11:58:24.258987: [TRACE] Realm: DB: 3041 Thread 0x16fd33000: End transaction 22175


flutter: 2024-01-17T11:58:24.259206: [TRACE] Realm: DB: 3041 Thread 0x16fd33000: Start read 22175: 14 ref 7008


flutter: 2024-01-17T11:58:24.259402: [DEBUG] Realm: DB: 3041 Thread 0x16fd33000: Query find all: 'identity == "623115f0b06"', limit = -1


flutter: 2024-01-17T11:58:24.259574: [DEBUG] Realm: DB: 3041 Thread 0x16fd33000: Query found: 1, Duration: 3 us


flutter: 2024-01-17T11:58:24.259768: [TRACE] Realm: DB: 3041 Thread 0x16fd33000: acquire writemutex


flutter: 2024-01-17T11:58:24.259942: [TRACE] Realm: DB: 3041 Thread 0x16fd33000: writemutex acquired


flutter: 2024-01-17T11:58:24.260147: [TRACE] Realm: DB: 3041 Thread 0x16fd33000: Tr 22175: Acquired write lock in 7 us


flutter: 2024-01-17T11:58:24.260366: [TRACE] Realm: DB: 3041 Thread 0x16fd33000: Tr 22175: Already on version: 14


flutter: 2024-01-17T11:58:24.260568: [TRACE] Realm: DB: 3041 Thread 0x16fd33000: Tr 22175: Promote to write: 14 -> 14


flutter: 2024-01-17T11:58:24.260739: [DEBUG] Realm: DB: 3041 Thread 0x16fd33000: Initiate commit version: 15


flutter: 2024-01-17T11:58:24.260952: [DEBUG] Realm: DB: 3041 Thread 0x16fd33000: Commit of size 328 done in 3217 us ref 7144


flutter: 2024-01-17T11:58:24.261151: [TRACE] Realm: DB: 3041 Thread 0x16fd33000: writemutex released


flutter: 2024-01-17T11:58:24.261323: [TRACE] Realm: DB: 3041 Thread 0x16fd33000: End transaction 22175


flutter: 2024-01-17T11:58:24.261499: [DETAIL] Realm: DB: 3041 Thread 0x16fd33000: DB closed


flutter: 2024-01-17T11:58:24.261694: [TRACE] Realm: DB: 13569 Thread 0x16fd33000: Start read 57443: 18 ref 7304


flutter: 2024-01-17T11:58:24.261875: [TRACE] Realm: DB: 13569 Thread 0x16fd33000: End transaction 57443


flutter: 2024-01-17T11:58:24.262042: [TRACE] Realm: DB: 13569 Thread 0x16fd33000: Start read 22175: 18 ref 7304


flutter: 2024-01-17T11:58:24.262223: [TRACE] Realm: DB: 13569 Thread 0x16fd33000: Start frozen 59827: 18 ref 7304


flutter: 2024-01-17T11:58:24.262403: [DEBUG] Realm: DB: 13569 Thread 0x16fd33000: Query find all: '(state == 3 or state == 6)', limit = -1


flutter: 2024-01-17T11:58:24.262597: [DEBUG] Realm: DB: 13569 Thread 0x16fd33000: Query found: 1, Duration: 2 us


flutter: 2024-01-17T11:58:24.262789: [TRACE] Realm: DB: 13569 Thread 0x16fd33000: End transaction 59827


flutter: 2024-01-17T11:58:24.263003: [TRACE] Realm: DB: 13569 Thread 0x16fd33000: acquire writemutex


flutter: 2024-01-17T11:58:24.263172: [TRACE] Realm: DB: 13569 Thread 0x16fd33000: writemutex acquired


flutter: 2024-01-17T11:58:24.263338: [TRACE] Realm: DB: 13569 Thread 0x16fd33000: Start write 59827: 18 ref 7304


flutter: 2024-01-17T11:58:24.263493: [DEBUG] Realm: DB: 13569 Thread 0x16fd33000: Query count: '_partition == "companyId=shared,parentId=trades"', limit = -1


flutter: 2024-01-17T11:58:24.263648: [DEBUG] Realm: DB: 13569 Thread 0x16fd33000: Query matches: 0, Duration: 1 us


flutter: 2024-01-17T11:58:24.263851: [DEBUG] Realm: DB: 13569 Thread 0x16fd33000: Initiate commit version: 19


flutter: 2024-01-17T11:58:24.264040: [DEBUG] Realm: DB: 13569 Thread 0x16fd33000: Commit of size 4160 done in 2466 us ref 5512


[ERROR:flutter/runtime/dart_vm_initializer.cc(41)] Unhandled Exception: RealmException: Client provided query with bad syntax: the indexed queryable field must be ANDed with the remainder of the query


#0      SubscriptionSet.waitForSynchronization (package:realm/src/subscription.dart:166:7)


<asynchronous suspension>


#1      TradesUtil.initRealm (package:edeal/Realm/Models/Trades/TradesUtil.dart:19:5)


<asynchronous suspension>


#2      initRealms (package:edeal/Realm/Realm.dart:59:27)


<asynchronous suspension>


flutter: 2024-01-17T11:58:24.264961: [TRACE] Realm: DB: 13569 Thread 0x16fd33000: writemutex released


flutter: 2024-01-17T11:58:24.265111: [TRACE] Realm: DB: 13569 Thread 0x16fd33000: Start frozen 59599: 19 ref 5512


flutter: 2024-01-17T11:58:24.265321: [TRACE] Realm: DB: 13569 Thread 0x16fb8f000: Start read 52635: 19 ref 5512


flutter: 2024-01-17T11:58:24.265472: [TRACE] Realm: DB: 13569 Thread 0x16fd33000: End transaction 59599


flutter: 2024-01-17T11:58:24.265689: [DEBUG] Realm: DB: 13569 Thread 0x16fb8f000: Query find all: 'version > 2 and (state == 1 or state == 2)', limit = -1


flutter: 2024-01-17T11:58:24.265886: [DEBUG] Realm: DB: 13569 Thread 0x16fb8f000: Query found: 1, Duration: 3 us


flutter: 2024-01-17T11:58:24.266067: [TRACE] Realm: DB: 13569 Thread 0x16fb8f000: End transaction 52635


flutter: 2024-01-17T11:58:24.266254: [TRACE] Realm: DB: 13569 Thread 0x16fb8f000: Start frozen 52635: 19 ref 5512


flutter: 2024-01-17T11:58:24.266433: [TRACE] Realm: DB: 13569 Thread 0x16fb8f000: End transaction 52635


flutter: 2024-01-17T11:58:24.266641: [TRACE] Realm: DB: 13569 Thread 0x16fd33000: End transaction 59827


flutter: 2024-01-17T11:58:24.266812: [DEBUG] Realm: Connection[1]: Session[1]: Sending: QUERY(query_version=3, query_size=141, query="{"Trades":"(_partition == \"companyId=62311465111d72bd027e5616,parentId=trades\" ) OR (_partition == \"companyId=shared,parentId=trades\" )"}", snapshot_version=18)


flutter: 2024-01-17T11:58:24.266991: [TRACE] Realm: DB: 13569 Thread 0x16fb8f000: Start read 52635: 19 ref 5512


flutter: 2024-01-17T11:58:24.267253: [TRACE] Realm: DB: 13569 Thread 0x16fb8f000: End transaction 52635


flutter: 2024-01-17T11:58:24.267430: [TRACE] Realm: DB: 13569 Thread 0x16fb8f000: Start read 52635: 19 ref 5512


flutter: 2024-01-17T11:58:24.267591: [TRACE] Realm: DB: 13569 Thread 0x16fb8f000: End transaction 52635


flutter: 2024-01-17T11:58:24.267752: [DEBUG] Realm: Connection[1]: Session[1]: Sending: MARK(request_ident=4)


flutter: 2024-01-17T11:58:24.267923: [TRACE] Realm: DB: 13569 Thread 0x16fb8f000: Start read 52635: 19 ref 5512


flutter: 2024-01-17T11:58:24.268081: [DEBUG] Realm: DB: 13569 Thread 0x16fb8f000: Query find all: 'version > 3 and (state == 1 or state == 2)', limit = -1


flutter: 2024-01-17T11:58:24.268268: [DEBUG] Realm: DB: 13569 Thread 0x16fb8f000: Query found: 0, Duration: 1 us


flutter: 2024-01-17T11:58:24.268441: [TRACE] Realm: DB: 13569 Thread 0x16fb8f000: End transaction 52635


flutter: 2024-01-17T11:58:24.268607: [TRACE] Realm: DB: 13569 Thread 0x16fb8f000: Start read 52635: 19 ref 5512


flutter: 2024-01-17T11:58:24.268799: [TRACE] Realm: DB: 13569 Thread 0x16fb8f000: End transaction 52635


flutter: 2024-01-17T11:58:24.268959: [DEBUG] Realm: Connection[1]: Session[1]: Sending: UPLOAD(progress_client_version=19, progress_server_version=23554, locked_server_version=23554, num_changesets=0)


flutter: 2024-01-17T11:58:24.269129: [TRACE] Realm: DB: 13569 Thread 0x16fb8f000: Start read 52635: 19 ref 5512


flutter: 2024-01-17T11:58:24.269313: [DEBUG] Realm: DB: 13569 Thread 0x16fb8f000: Query find all: 'version > 3 and (state == 1 or state == 2)', limit = -1


flutter: 2024-01-17T11:58:24.269506: [DEBUG] Realm: DB: 13569 Thread 0x16fb8f000: Query found: 0, Duration: 0 us


flutter: 2024-01-17T11:58:24.269659: [TRACE] Realm: DB: 13569 Thread 0x16fb8f000: End transaction 52635


flutter: 2024-01-17T11:58:24.269844: [INFO] Realm: Connection[1]: Session[1]: Received QUERY_ERROR "Client provided query with bad syntax: the indexed queryable field must be ANDed with the remainder of the query" (error_code=300, query_version=3)


flutter: 2024-01-17T11:58:24.270007: [TRACE] Realm: DB: 13569 Thread 0x16fb8f000: acquire writemutex


flutter: 2024-01-17T11:58:24.270183: [TRACE] Realm: DB: 13569 Thread 0x16fb8f000: writemutex acquired


flutter: 2024-01-17T11:58:24.270335: [TRACE] Realm: DB: 13569 Thread 0x16fb8f000: Start write 59827: 19 ref 5512


flutter: 2024-01-17T11:58:24.270483: [DEBUG] Realm: DB: 13569 Thread 0x16fb8f000: Initiate commit version: 20


flutter: 2024-01-17T11:58:24.270667: [DEBUG] Realm: DB: 13569 Thread 0x16fb8f000: Commit of size 2088 done in 2405 us ref 7664


flutter: 2024-01-17T11:58:24.271354: [TRACE] Realm: DB: 13569 Thread 0x16fb8f000: writemutex released


flutter: 2024-01-17T11:58:24.271559: [TRACE] Realm: DB: 13569 Thread 0x16fb8f000: Start frozen 14110: 20 ref 7664


flutter: 2024-01-17T11:58:24.271741: [TRACE] Realm: DB: 13569 Thread 0x16fb8f000: End transaction 14110


flutter: 2024-01-17T11:58:24.271893: [TRACE] Realm: DB: 13569 Thread 0x16fb8f000: End transaction 59827


flutter: 2024-01-17T11:58:24.272040: [TRACE] Realm: DB: 13569 Thread 0x16fb8f000: Start read 14110: 20 ref 7664


flutter: 2024-01-17T11:58:24.272193: [DEBUG] Realm: DB: 13569 Thread 0x16fb8f000: Query find all: 'version > 3 and (state == 1 or state == 2)', limit = -1


flutter: 2024-01-17T11:58:24.272339: [DEBUG] Realm: DB: 13569 Thread 0x16fb8f000: Query found: 0, Duration: 3 us


flutter: 2024-01-17T11:58:24.272512: [TRACE] Realm: DB: 13569 Thread 0x16fb8f000: End transaction 14110


flutter: 2024-01-17T11:58:24.272662: [TRACE] Realm: DB: 13569 Thread 0x16fb8f000: Start read 14110: 20 ref 7664


flutter: 2024-01-17T11:58:24.272812: [TRACE] Realm: DB: 13569 Thread 0x16fb8f000: End transaction 14110


flutter: 2024-01-17T11:58:24.272981: [DEBUG] Realm: Connection[1]: Session[1]: Sending: UPLOAD(progress_client_version=20, progress_server_version=23554, locked_server_version=23554, num_changesets=0)


flutter: 2024-01-17T11:58:24.273123: [TRACE] Realm: DB: 13569 Thread 0x16fb8f000: Start read 14110: 20 ref 7664


flutter: 2024-01-17T11:58:24.273259: [TRACE] Realm: DB: 13569 Thread 0x16fb8f000: End transaction 14110


flutter: 2024-01-17T11:58:24.273392: [TRACE] Realm: DB: 13569 Thread 0x16fb8f000: Start read 14110: 20 ref 7664


flutter: 2024-01-17T11:58:24.273542: [TRACE] Realm: DB: 13569 Thread 0x16fb8f000: End transaction 14110


flutter: 2024-01-17T11:58:24.273704: [TRACE] Realm: DB: 13569 Thread 0x16fb8f000: Start read 14110: 20 ref 7664


flutter: 2024-01-17T11:58:24.273867: [DEBUG] Realm: DB: 13569 Thread 0x16fb8f000: Query find all: 'version > 3 and (state == 1 or state == 2)', limit = -1


flutter: 2024-01-17T11:58:24.274045: [DEBUG] Realm: DB: 13569 Thread 0x16fb8f000: Query found: 0, Duration: 1 us


flutter: 2024-01-17T11:58:24.274188: [TRACE] Realm: DB: 13569 Thread 0x16fb8f000: End transaction 14110

Hi @Julien_Chouvet,

The original error indicates that your app is configured with an indexed queryable field which adds some additional requirements that your subscriptions must adhere to (see the Client-Side Queries on Indexed Queryable Fields section of the link above).

If you don’t need the performance benefits of an indexed queryable field then you can remove it from your backend sync config and continue with adhoc subscriptions.

Hi @Kiro_Morkos,

Thanks for your answer.

The Indexed Query Field is _partition.

What are the “additional requirements” you are talking about?
How to query the Trades collection if I want to keep _partition as an Indexed Query Field?

Thanks.

Sorry I didn’t realize your query was included in the logs you shared

(_partition == \"companyId=62311465111d72bd027e5616,parentId=trades\" ) OR (_partition == \"companyId=shared,parentId=trades\" )

This should be supported, I believe we may have a bug on our part that is not checking the restrictions correctly. In the meantime you can work around this by representing the query like this instead

_partition IN {\"companyId=62311465111d72bd027e5616,parentId=trades\", \"companyId=shared,parentId=trades\"}

I assume the OR in your query is a result of two different calls to mutableSubscriptions.add(...), so you’ll need to combine them into one call using the IN operator. We will report back when the bug is fixed and you can switch back to your current code.

1 Like

Thanks @Kiro_Morkos, it’s working with the IN operator!

1 Like