N1ql -> IN operator does not work with other conditions

Question

The following query works just fine when only IN operator is used
SELECT META().id FROM bucket_name WHERE description IN ['Item1','Item2']

But when I fire this query it gives me a blank result
SELECT META().id FROM bucket_name WHERE id = 123 AND description IN ['Item1','Item2']

Am I doing something wrong or somebody else has faced the same problem?

Answer 1

I think you have to take your "IN" condition into parenthesis to make it work:

SELECT META().id FROM bucket_name WHERE id = 123 AND (description IN ['Item1','Item2'])

It has to do with the precedence level of the operators evaluation by N1QL processor

If you run it with EXPLAIN keyword it will show how it links conditions against each other.

e.g.

explain SELECT META().id FROM bucket_name WHERE id = 123 AND (description IN ['Item1','Item2'])

vs

explain SELECT META().id FROM bucket_name WHERE id = 123 AND description IN ['Item1','Item2']

Answer 2

With the latest N1QL developer preview (http://docs.couchbase.com/developer/n1ql-dp3/n1ql-intro.html) the IN clause does not need to be parenthesized, so this should work:

SELECT META(b).id FROM bucket_name b WHERE id = 123 AND description IN ['Item1','Item2']

You need to pass the bucket name (or alias) to META() I think because N1QL now supports queries on multiple buckets.