ANY IN with positional parameter

Couchbase Server
1

Hi,

I still don’t understand how positional parameters in ANY IN queries works. I wrote some time ago a ticket because I thougt it was related to the client library. But I can reproduce it in the admin console, so I think I’m doing something wrong :slight_smile:

I created a bucket “test” with 3 documents:

{
“type”: “foo”,
“id”: 1,
“placement”: [1, 2, 3, 4, 5]
}

{
“type”: “foo”,
“id”: 2,
“placement”: [3, 6, 7]
}

{
“type”: “foo”,
“id”: 3,
“placement”: [2, 3]
}

and created a index:
CREATE INDEX test1 ON test(type,(distinct (array v for v in placement end)),id) WHERE (type = “foo”)

With a regular query:
SELECT id FROM test WHERE type=“foo” AND ANY v IN placement SATISFIES v=3 END;
I get the answer index covered.

If I try the same with positional parameters:
SELECT id FROM test WHERE type=“foo” AND ANY v IN placement SATISFIES v=$1 END;
and set “Positional Parameter: $1=3” in settings it fetches all 3 documents to return the result.

Parameter outside the query in works, so
SELECT id FROM test WHERE type=“foo” AND ANY v IN placement SATISFIES v=3 END;
and
SELECT id FROM test WHERE type=$1 AND ANY v IN placement SATISFIES v=3 END;
are both index covered

What I’m doing wrong?

Thanks, Pascal

2

Index test1 is ARRAY Index. There are some restrictions Array Index implicitly covering without whole array. Array Index key depends on named parameter or positional parameter it required Fetch or you can do explicitly covering.

  1. Do explicit covering with
    CREATE INDEX test1 ON test(type,(distinct (array v for v in placement end)), placement, id) WHERE (type = “foo”);
  2. try adhoc=true from SDK
  3. non covered query.
3

Thanks! Works perfectly with your index definition :slight_smile: