Issues on Index Selection using IN clause

Given this index definition:

CREATE INDEX indexIn on bucket(field1) WHERE field1 IN ["A","B","C"];

The following queries should be using this index and worked properly:

SELECT * from bucket where field1 IN ["A"]

SELECT * from bucket where field1 = "A"

SELECT * from bucket where field1 IN ["B"] 

SELECT * from bucket where field1 = "B"

SELECT * from bucket where field1 IN ["C"]

SELECT * from bucket where field1 = "C"

SELECT * from bucket where field1 IN ["A","B"]

-- All tuples combination

SELECT * from bucket WHERE field1 IN ["A","B","C"];

SELECT * from bucket WHERE field1 IN ["A","C","B"];

-- All triples combination

After several combined tests the only combination that worked was the exact IN clause and exact value:

SELECT * from bucket WHERE field1 IN ["A","B","C"];

SELECT * from bucket WHERE field = "A"; -- OR B OR C

For the rest of the combinations the following error was raised:

[

  {

    "code": 4000,

    "msg": "No index available on keyspace oysho_stg_copy that matches your query. Use CREATE INDEX or CREATE PRIMARY INDEX to create an index, or check that your expected index is online.",

    "query_from_user": "SELECT * from oysho_stg_copy WHERE field1 IN [\"B\",\"C\",\"A\"];"

  }

]

The following issue was discussed with the N1QL team and the answer was to avoid using complex terms (IN, OR, etc.)on indexes to avoid indexing mismatch. In case of using, Query's IN clausule has to be exact to the index's one.

However, IN clause is allowed on CREATE INDEX sintax and doens't works as expected (manage a range of values).

On the other hand, equal operator works on single equal values, so query terms doesn't have to be exact to the Index's IN clause.

None of this limitations are documented on doc site and N1QL books.

After discussing this issue with the N1QL and the tiger team the following actions were detected:

1. File this MB to fix the IN clause behaviour to expected
2. Link to current customer CBSE
3. File a DOC Issue to