Couchbase::Error::AmbiguousTimeout (unable to query: ambiguous_timeout)

vishnuchiyyarath · February 8, 2024, 7:43pm

Hello CB Team,

I have a fully covered N1QL index. Recently we did couple of things:

We upgraded our CB Ruby SDK (on Rails) from version gem 'couchbase', '1.3.14' to gem 'couchbase', '3.4.5'.
Parameterized the query.

NOTE: We have a default query timeout of 75 seconds.

After the upgrade, we are experiencing a significant increase in the occurrence of Couchbase::Error::AmbiguousTimeout (unable to query: ambiguous_timeout) errors for queries that take more than 75 seconds.

I recently posted another issue regarding parameterization, which turned out to be a bug in the Couchbase query planner causing large spans. You can find more information at Query performance issue while parameterizing.

CREATE INDEX `users_idx_v2`
    ON `sync_gateway_sw1` (
         ALL ARRAY [`l`.`name`, `l`.`val`.`role`, `l`.`val`.`association`.`state`, `l`.`val`.`association`.`invitationCode`] FOR `l` IN OBJECT_PAIRS(`locations`) END,
         `organizationId`,
         `name`,
         `displayName`,
         `firstName`,
         `lastName`,
         `fullName`,
         `title`,
         `phoneNumber`,
         TOBOOLEAN(IFMISSINGORNULL(`phoneConfirmedWrong`, FALSE)),
         TOBOOLEAN(IFMISSINGORNULL(`temporaryUserName`, FALSE)),
         TOBOOLEAN(IFMISSINGORNULL(`unselectable`, FALSE)),
         TOBOOLEAN(IFMISSINGORNULL(`unfilterable`, FALSE)),
         TOBOOLEAN(IFMISSINGORNULL(`unmanagedUser`, FALSE)),
         `attachmentsMeta`[0].`attachmentId`,
         `rev`
       )
 WHERE (NOT IFMISSINGORNULL(`_deleted`, FALSE))
   AND (NOT IFMISSINGORNULL(`softDelete`, FALSE))
   AND (META().`id` NOT LIKE "_sync:%")
   AND (`type` = "user")
   AND (TOBOOLEAN(IFMISSINGORNULL(`resident`, FALSE)) = FALSE)

SELECT META(t).`id`,
       ARRAY_AGG(nv) AS `locations`,
       t.`organizationId`,
       t.`name`,
       t.`displayName`,
       t.`firstName`,
       t.`lastName`,
       t.`fullName`,
       t.`title`,
       t.`phoneNumber`,
       TOBOOLEAN(IFMISSINGORNULL(t.`phoneConfirmedWrong`, FALSE)) AS `phoneConfirmedWrong`,
       TOBOOLEAN(IFMISSINGORNULL(t.`temporaryUserName`, FALSE)) AS `temporaryUserName`,
       TOBOOLEAN(IFMISSINGORNULL(t.`unselectable`, FALSE)) AS `unselectable`,
       TOBOOLEAN(IFMISSINGORNULL(t.`unfilterable`, FALSE)) AS `unfilterable`,
       TOBOOLEAN(IFMISSINGORNULL(t.`unmanagedUser`, FALSE)) AS `unmanagedUser`,
       t.`attachmentsMeta`[0].`attachmentId`,
       t.`rev`
FROM sync_gateway_sw1 t
UNNEST OBJECT_PAIRS(t.`locations`) AS l
LET nv = [`l`.`name`, `l`.`val`.`role`, `l`.`val`.`association`.`state`, `l`.`val`.`association`.`invitationCode`]
WHERE (NOT IFMISSINGORNULL(t.`_deleted`, FALSE))
    AND (NOT IFMISSINGORNULL(t.`softDelete`, FALSE))
    AND (META(t).id NOT LIKE "_sync:%")
    AND t.`type` = "user"
    AND (((nv >= [$param1])
            AND (nv < [SUCCESSOR($param1)]))
        OR ((nv >= [$param5])
            AND (nv < [SUCCESSOR($param5)])))
    AND (TOBOOLEAN(IFMISSINGORNULL(t.`unselectable`, FALSE)) = $param2)
    AND (TOBOOLEAN(IFMISSINGORNULL(t.`unfilterable`, FALSE)) = $param3)
    AND (TOBOOLEAN(IFMISSINGORNULL(t.`resident`, FALSE)) = FALSE)
    AND nv[2] NOT IN $param4
GROUP BY META(t).`id`,
         t.`organizationId`,
         t.`name`,
         t.`displayName`,
         t.`firstName`,
         t.`lastName`,
         t.`fullName`,
         t.`title`,
         t.`phoneNumber`,
         TOBOOLEAN(IFMISSINGORNULL(t.`phoneConfirmedWrong`, FALSE)),
         TOBOOLEAN(IFMISSINGORNULL(t.`temporaryUserName`, FALSE)),
         TOBOOLEAN(IFMISSINGORNULL(t.`unselectable`, FALSE)),
         TOBOOLEAN(IFMISSINGORNULL(t.`unfilterable`, FALSE)),
         TOBOOLEAN(IFMISSINGORNULL(t.`unmanagedUser`, FALSE)),
         t.`attachmentsMeta`[0].`attachmentId`,
         t.`rev`
ORDER BY LOWER(t.`displayName`) ASC
LIMIT 100
OFFSET 0

EXPLAIN:

{
  "#operator": "Sequence",
  "#stats": {
    "#phaseSwitches": 1,
    "execTime": "5.057µs"
  },
  "~children": [
    {
      "#operator": "Authorize",
      "#stats": {
        "#phaseSwitches": 3,
        "execTime": "12.54µs",
        "servTime": "16.122391ms"
      },
      "privileges": {
        "List": [
          {
            "Target": "default:sync_gateway_sw1",
            "Priv": 7
          }
        ]
      },
      "~child": {
        "#operator": "Sequence",
        "#stats": {
          "#phaseSwitches": 1,
          "execTime": "6.216µs"
        },
        "~children": [
          {
            "#operator": "Sequence",
            "#stats": {
              "#phaseSwitches": 1,
              "execTime": "6.27µs"
            },
            "~children": [
              {
                "#operator": "IndexScan3",
                "#stats": {
                  "#itemsOut": 1,
                  "#phaseSwitches": 7,
                  "execTime": "80.547µs",
                  "kernTime": "2.62µs",
                  "servTime": "1.51798ms"
                },
                "as": "t",
                "covers": [
                  "cover ([(`l`.`name`), ((`l`.`val`).`role`), (((`l`.`val`).`association`).`state`), (((`l`.`val`).`association`).`invitationCode`)])",
                  "cover ((`t`.`organizationId`))",
                  "cover ((`t`.`name`))",
                  "cover ((`t`.`displayName`))",
                  "cover ((`t`.`firstName`))",
                  "cover ((`t`.`lastName`))",
                  "cover ((`t`.`fullName`))",
                  "cover ((`t`.`title`))",
                  "cover ((`t`.`phoneNumber`))",
                  "cover (to_boolean(ifmissingornull((`t`.`phoneConfirmedWrong`), false)))",
                  "cover (to_boolean(ifmissingornull((`t`.`temporaryUserName`), false)))",
                  "cover (to_boolean(ifmissingornull((`t`.`unselectable`), false)))",
                  "cover (to_boolean(ifmissingornull((`t`.`unfilterable`), false)))",
                  "cover (to_boolean(ifmissingornull((`t`.`unmanagedUser`), false)))",
                  "cover ((((`t`.`attachmentsMeta`)[0]).`attachmentId`))",
                  "cover ((`t`.`rev`))",
                  "cover ((meta(`t`).`id`))"
                ],
                "filter_covers": {
                  "cover (([] <= object_pairs((`t`.`locations`))))": true,
                  "cover ((`t`.`type`))": "user",
                  "cover ((not ((meta(`t`).`id`) like \"_sync:%\")))": true,
                  "cover ((not ifmissingornull((`t`.`_deleted`), false)))": true,
                  "cover ((not ifmissingornull((`t`.`softDelete`), false)))": true,
                  "cover ((object_pairs((`t`.`locations`)) < {}))": true,
                  "cover (is_array(object_pairs((`t`.`locations`))))": true,
                  "cover (to_boolean(ifmissingornull((`t`.`resident`), false)))": false
                },
                "index": "users_idx0_v2",
                "index_id": "41fa8726e03aa3cc",
                "keyspace": "sync_gateway_sw1",
                "namespace": "default",
                "spans": [
                  {
                    "range": [
                      {
                        "high": "[successor($param1)]",
                        "inclusion": 1,
                        "low": "[$param1]"
                      },
                      {
                        "inclusion": 0
                      },
                      {
                        "inclusion": 0
                      },
                      {
                        "inclusion": 0
                      },
                      {
                        "inclusion": 0
                      },
                      {
                        "inclusion": 0
                      },
                      {
                        "inclusion": 0
                      },
                      {
                        "inclusion": 0
                      },
                      {
                        "inclusion": 0
                      },
                      {
                        "inclusion": 0
                      },
                      {
                        "inclusion": 0
                      },
                      {
                        "high": "$param2",
                        "inclusion": 3,
                        "low": "$param2"
                      },
                      {
                        "high": "$param3",
                        "inclusion": 3,
                        "low": "$param3"
                      }
                    ]
                  },
                  {
                    "range": [
                      {
                        "high": "[successor($param5)]",
                        "inclusion": 1,
                        "low": "[$param5]"
                      },
                      {
                        "inclusion": 0
                      },
                      {
                        "inclusion": 0
                      },
                      {
                        "inclusion": 0
                      },
                      {
                        "inclusion": 0
                      },
                      {
                        "inclusion": 0
                      },
                      {
                        "inclusion": 0
                      },
                      {
                        "inclusion": 0
                      },
                      {
                        "inclusion": 0
                      },
                      {
                        "inclusion": 0
                      },
                      {
                        "inclusion": 0
                      },
                      {
                        "high": "$param2",
                        "inclusion": 3,
                        "low": "$param2"
                      },
                      {
                        "high": "$param3",
                        "inclusion": 3,
                        "low": "$param3"
                      }
                    ]
                  }
                ],
                "using": "gsi",
                "#time_normal": "00:00.001",
                "#time_absolute": 0.0015985270000000002
              },
              {
                "#operator": "Sequence",
                "#stats": {
                  "#phaseSwitches": 1,
                  "execTime": "3.977µs"
                },
                "~children": [
                  {
                    "#operator": "Let",
                    "#stats": {
                      "#itemsIn": 1,
                      "#itemsOut": 1,
                      "#phaseSwitches": 7,
                      "execTime": "38.764µs",
                      "kernTime": "1.610067ms"
                    },
                    "bindings": [
                      {
                        "expr": "cover ([(`l`.`name`), ((`l`.`val`).`role`), (((`l`.`val`).`association`).`state`), (((`l`.`val`).`association`).`invitationCode`)])",
                        "var": "nv"
                      }
                    ],
                    "#time_normal": "00:00.000",
                    "#time_absolute": 0.000038764000000000004
                  },
                  {
                    "#operator": "Filter",
                    "#stats": {
                      "#itemsIn": 1,
                      "#itemsOut": 1,
                      "#phaseSwitches": 7,
                      "execTime": "160.408µs",
                      "kernTime": "1.655941ms"
                    },
                    "condition": "((((((((cover ((not ifmissingornull((`t`.`_deleted`), false))) and cover ((not ifmissingornull((`t`.`softDelete`), false)))) and cover ((not ((meta(`t`).`id`) like \"_sync:%\")))) and (cover ((`t`.`type`)) = \"user\")) and ((([$param1] <= `nv`) and (`nv` < [successor($param1)])) or (([$param5] <= `nv`) and (`nv` < [successor($param5)])))) and (cover (to_boolean(ifmissingornull((`t`.`unselectable`), false))) = $param2)) and (cover (to_boolean(ifmissingornull((`t`.`unfilterable`), false))) = $param3)) and (cover (to_boolean(ifmissingornull((`t`.`resident`), false))) = false)) and (not ((`nv`[2]) in $param4)))",
                    "#time_normal": "00:00.000",
                    "#time_absolute": 0.00016040799999999999
                  },
                  {
                    "#operator": "InitialGroup",
                    "#stats": {
                      "#itemsIn": 1,
                      "#itemsOut": 1,
                      "#phaseSwitches": 7,
                      "execTime": "117.808µs",
                      "kernTime": "1.823392ms"
                    },
                    "aggregates": [
                      "array_agg(`nv`)"
                    ],
                    "group_keys": [
                      "cover ((meta(`t`).`id`))",
                      "cover ((`t`.`organizationId`))",
                      "cover ((`t`.`name`))",
                      "cover ((`t`.`displayName`))",
                      "cover ((`t`.`firstName`))",
                      "cover ((`t`.`lastName`))",
                      "cover ((`t`.`fullName`))",
                      "cover ((`t`.`title`))",
                      "cover ((`t`.`phoneNumber`))",
                      "cover (to_boolean(ifmissingornull((`t`.`phoneConfirmedWrong`), false)))",
                      "cover (to_boolean(ifmissingornull((`t`.`temporaryUserName`), false)))",
                      "cover (to_boolean(ifmissingornull((`t`.`unselectable`), false)))",
                      "cover (to_boolean(ifmissingornull((`t`.`unfilterable`), false)))",
                      "cover (to_boolean(ifmissingornull((`t`.`unmanagedUser`), false)))",
                      "cover ((((`t`.`attachmentsMeta`)[0]).`attachmentId`))",
                      "cover ((`t`.`rev`))"
                    ],
                    "#time_normal": "00:00.000",
                    "#time_absolute": 0.00011780800000000001
                  }
                ],
                "#time_normal": "00:00.000",
                "#time_absolute": 0.000003977
              },
              {
                "#operator": "IntermediateGroup",
                "#stats": {
                  "#itemsIn": 1,
                  "#itemsOut": 1,
                  "#phaseSwitches": 7,
                  "execTime": "89.322µs",
                  "kernTime": "1.953414ms"
                },
                "aggregates": [
                  "array_agg(`nv`)"
                ],
                "group_keys": [
                  "cover ((meta(`t`).`id`))",
                  "cover ((`t`.`organizationId`))",
                  "cover ((`t`.`name`))",
                  "cover ((`t`.`displayName`))",
                  "cover ((`t`.`firstName`))",
                  "cover ((`t`.`lastName`))",
                  "cover ((`t`.`fullName`))",
                  "cover ((`t`.`title`))",
                  "cover ((`t`.`phoneNumber`))",
                  "cover (to_boolean(ifmissingornull((`t`.`phoneConfirmedWrong`), false)))",
                  "cover (to_boolean(ifmissingornull((`t`.`temporaryUserName`), false)))",
                  "cover (to_boolean(ifmissingornull((`t`.`unselectable`), false)))",
                  "cover (to_boolean(ifmissingornull((`t`.`unfilterable`), false)))",
                  "cover (to_boolean(ifmissingornull((`t`.`unmanagedUser`), false)))",
                  "cover ((((`t`.`attachmentsMeta`)[0]).`attachmentId`))",
                  "cover ((`t`.`rev`))"
                ],
                "#time_normal": "00:00.000",
                "#time_absolute": 0.000089322
              },
              {
                "#operator": "FinalGroup",
                "#stats": {
                  "#itemsIn": 1,
                  "#itemsOut": 1,
                  "#phaseSwitches": 7,
                  "execTime": "75.309µs",
                  "kernTime": "2.048641ms"
                },
                "aggregates": [
                  "array_agg(`nv`)"
                ],
                "group_keys": [
                  "cover ((meta(`t`).`id`))",
                  "cover ((`t`.`organizationId`))",
                  "cover ((`t`.`name`))",
                  "cover ((`t`.`displayName`))",
                  "cover ((`t`.`firstName`))",
                  "cover ((`t`.`lastName`))",
                  "cover ((`t`.`fullName`))",
                  "cover ((`t`.`title`))",
                  "cover ((`t`.`phoneNumber`))",
                  "cover (to_boolean(ifmissingornull((`t`.`phoneConfirmedWrong`), false)))",
                  "cover (to_boolean(ifmissingornull((`t`.`temporaryUserName`), false)))",
                  "cover (to_boolean(ifmissingornull((`t`.`unselectable`), false)))",
                  "cover (to_boolean(ifmissingornull((`t`.`unfilterable`), false)))",
                  "cover (to_boolean(ifmissingornull((`t`.`unmanagedUser`), false)))",
                  "cover ((((`t`.`attachmentsMeta`)[0]).`attachmentId`))",
                  "cover ((`t`.`rev`))"
                ],
                "#time_normal": "00:00.000",
                "#time_absolute": 0.000075309
              },
              {
                "#operator": "InitialProject",
                "#stats": {
                  "#itemsIn": 1,
                  "#itemsOut": 1,
                  "#phaseSwitches": 7,
                  "execTime": "39.867µs",
                  "kernTime": "2.129477ms"
                },
                "result_terms": [
                  {
                    "expr": "cover ((meta(`t`).`id`))"
                  },
                  {
                    "as": "locations",
                    "expr": "array_agg(`nv`)"
                  },
                  {
                    "expr": "cover ((`t`.`organizationId`))"
                  },
                  {
                    "expr": "cover ((`t`.`name`))"
                  },
                  {
                    "expr": "cover ((`t`.`displayName`))"
                  },
                  {
                    "expr": "cover ((`t`.`firstName`))"
                  },
                  {
                    "expr": "cover ((`t`.`lastName`))"
                  },
                  {
                    "expr": "cover ((`t`.`fullName`))"
                  },
                  {
                    "expr": "cover ((`t`.`title`))"
                  },
                  {
                    "expr": "cover ((`t`.`phoneNumber`))"
                  },
                  {
                    "as": "phoneConfirmedWrong",
                    "expr": "cover (to_boolean(ifmissingornull((`t`.`phoneConfirmedWrong`), false)))"
                  },
                  {
                    "as": "temporaryUserName",
                    "expr": "cover (to_boolean(ifmissingornull((`t`.`temporaryUserName`), false)))"
                  },
                  {
                    "as": "unselectable",
                    "expr": "cover (to_boolean(ifmissingornull((`t`.`unselectable`), false)))"
                  },
                  {
                    "as": "unfilterable",
                    "expr": "cover (to_boolean(ifmissingornull((`t`.`unfilterable`), false)))"
                  },
                  {
                    "as": "unmanagedUser",
                    "expr": "cover (to_boolean(ifmissingornull((`t`.`unmanagedUser`), false)))"
                  },
                  {
                    "expr": "cover ((((`t`.`attachmentsMeta`)[0]).`attachmentId`))"
                  },
                  {
                    "expr": "cover ((`t`.`rev`))"
                  }
                ],
                "#time_normal": "00:00.000",
                "#time_absolute": 0.000039866999999999996
              }
            ],
            "#time_normal": "00:00.000",
            "#time_absolute": 0.000006269999999999999
          },
          {
            "#operator": "Order",
            "#stats": {
              "#itemsIn": 1,
              "#itemsOut": 1,
              "#phaseSwitches": 8,
              "execTime": "58.303µs",
              "kernTime": "2.184665ms"
            },
            "limit": "100",
            "sort_terms": [
              {
                "expr": "lower(cover ((`t`.`displayName`)))"
              }
            ],
            "#time_normal": "00:00.000",
            "#time_absolute": 0.000058303
          },
          {
            "#operator": "Limit",
            "#stats": {
              "#itemsIn": 1,
              "#itemsOut": 1,
              "#phaseSwitches": 3,
              "execTime": "4.321µs"
            },
            "expr": "100",
            "#time_normal": "00:00.000",
            "#time_absolute": 0.000004321
          },
          {
            "#operator": "FinalProject",
            "#stats": {
              "#itemsIn": 1,
              "#itemsOut": 1,
              "#phaseSwitches": 4,
              "execTime": "5.001µs",
              "kernTime": "1.776µs"
            },
            "#time_normal": "00:00.000",
            "#time_absolute": 0.000005001
          }
        ],
        "#time_normal": "00:00.000",
        "#time_absolute": 0.000006216
      },
      "#time_normal": "00:00.016",
      "#time_absolute": 0.016134930999999998
    },
    {
      "#operator": "Stream",
      "#stats": {
        "#itemsIn": 1,
        "#itemsOut": 1,
        "#phaseSwitches": 5,
        "execTime": "94.05µs",
        "kernTime": "18.430886ms"
      },
      "#time_normal": "00:00.000",
      "#time_absolute": 0.00009405
    }
  ],
  "~versions": [
    "6.6.3-N1QL",
    "6.6.3-9808-enterprise"
  ],
  "#time_normal": "00:00.000",
  "#time_absolute": 0.000005057
}

QUESTIONS:

What is the reason for Couchbase::Error::AmbiguousTimeout?
Do you see anything abnormal in the EXPLAIN?
Can we optimize the index/query to reduce the query latency and this timeout?

Thanks,
Vishnu

mreiche · February 9, 2024, 4:02pm

If you perform your changes one-at-time, (sdk version or parameterize query) you can isolate what change made the difference. I assume it was the parameterization.

Given that the trade-off for not parameterizing the query is a fraction of a second to compile the query in exchange for many seconds of execution time - this is a good candidate to keep unparameterized.

You should probably wait for @vsr1 to weigh in on the optimization, but here are some things that I wonder about.

Consider the predicate below. Can it not be rewritten as (nv == [$param1] OR nv == [$param5])? The equalities could (possibly) be used as lookups instead of ranges. Also - is the intention that $param1 match only on nv containing a single element, and that single element match $param1? And that single element can be any of name, role, state, invitationCode… ?

          (((nv >= [$param1])
            AND (nv < [SUCCESSOR($param1)]))
        OR ((nv >= [$param5])
            AND (nv < [SUCCESSOR($param5)])))

Also - I wonder about the effectiveness of putting all the fields in a single index - Does that rule out an index-lookup and requires an index-scan instead?

And supposing their is an indexScan - I would expect some benefit from the index beginning with an element that is being scanned for (ie. nv[2] NOT IN $param4) - so would starting the index with l.val.role be helpful(?). Also - an inequality is going to result in a rangescan.

vsr1 · February 9, 2024, 8:41pm

The one provided Profile Timings shows it completed in ms. Not sure why it took 75s?
system:completed_requests and see where it spent time.

The query seems right (may be small improvements can be made but unless we know what causing avoid those)

If param1, param2 value has many many entries, based on this everything change. Findout which value it causing and generate profile and investigate.

IndexScan might taking time and you have last keys 11th, 12th are equality before keys makes this range scan almost whole index. Like dictionary look up
each document (group by has document key) might have to do GROUP/Aggregation those might be high
Sort might taking time

vsr1 · February 9, 2024, 9:39pm

I am not sure this query is more optimal, it is written to do covering. It may be more expensive because you are doing GROUP by document key . Best is do Fetch.

CREATE INDEX ix1 ON sync_gateway_sw1 ( TOBOOLEAN(IFMISSINGORNULL(`unselectable`, FALSE)),
                                       TOBOOLEAN(IFMISSINGORNULL(`unfilterable`, FALSE)),
                                       ALL l.name FOR l IN OBJECT_PAIRS(locations) END),
                                       LOWER(displayName)
                                WHERE type = "user"
                                      AND IFMISSINGORNULL(`_deleted`, FALSE) = FALSE
                                      AND IFMISSINGORNULL(`softDelete`, FALSE) = FALSE
                                      AND TOBOOLEAN(IFMISSINGORNULL(`resident`, FALSE)) = FALSE
                                      AND (META().id NOT LIKE "_sync:%");

SELECT META(t).id, t.`organizationId`, t.`name`, t.`displayName`, t.`firstName`, t.`lastName`, t.`fullName`,
       t.`title`, t.`phoneNumber`, TOBOOLEAN(IFMISSINGORNULL(t.`phoneConfirmedWrong`, FALSE)) AS `phoneConfirmedWrong`,
       TOBOOLEAN(IFMISSINGORNULL(t.`temporaryUserName`, FALSE)) AS `temporaryUserName`,
       TOBOOLEAN(IFMISSINGORNULL(t.`unselectable`, FALSE)) AS `unselectable`,
       TOBOOLEAN(IFMISSINGORNULL(t.`unfilterable`, FALSE)) AS `unfilterable`,
       TOBOOLEAN(IFMISSINGORNULL(t.`unmanagedUser`, FALSE)) AS `unmanagedUser`,
       t.`attachmentsMeta`[0].`attachmentId`, t.`rev`
       ARRAY {"name":n, v.`role`, v.association.state, v.association.invitationCode }
             FOR n:v IN t.locations WHEN n IN $param1  AND v.association.state NOT IN $param4
             END AS locations
FROM sync_gateway_sw1 AS t
WHERE t.`type` = "user"
      AND IFMISSINGORNULL(t.`_deleted`, FALSE) = FALSE
      AND IFMISSINGORNULL(t.`softDelete`, FALSE) = FALSE
      AND TOBOOLEAN(IFMISSINGORNULL(t.`resident`, FALSE)) = FALSE
      AND (META(t).id NOT LIKE "_sync:%")
      AND TOBOOLEAN(IFMISSINGORNULL(t.`unselectable`, FALSE)) = $param2
      AND TOBOOLEAN(IFMISSINGORNULL(t.`unfilterable`, FALSE)) = $param3
      AND ANY l IN OBJECT_PAIRS(t.locations) SATISFIES l.name IN $param1 AND l.val.association.state NOT IN $param4 END
ORDER BY LOWER(t.`displayName`) ASC
LIMIT 100
OFFSET 0;

Note: param1 pass ARRAY/list of all values you need