Description
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:
- File this MB to fix the IN clause behaviour to expected
- Link to current customer CBSE
- File a DOC Issue to
Attachments
Issue Links
- relates to
-
DOC-4542 Issues on limitations when using IN clause on index creation
- Closed