Hello,
I recently came across a bug in 4.5.1 CE. I’m writing it here to help everyone else spending a lot of time on finding the bug.
So if you have an ARRAY index in any version prior to 4.6.2 the variable names had to be exactly the same as in the index definition, in order for the index to be used (see https://issues.couchbase.com/browse/MB-22646)
So far everything is fine, as long as you are aware of that, but if you are doing something like the following your results will vary randomly:
select
meta(`holder`).`id`
from `mybucket` as `holder`
where ClassName = 'ObjectName' AND
(ANY `refKey` IN `holder`.`refKeys` SATISFIES [`refKey`.`key`, `refKey`.`value`] = ['KEY1', 'VALUE1'] END) AND
(ANY `refKey` IN `holder`.`refKeys` SATISFIES [`refKey`.`key`, `refKey`.`value`] = ['KEY2', 'VALUE2'] END)
That’s because N1QL somehow optimizes the two ANY IN clauses to run in parallel and as they are using the same variable names (even though they are in a completely different context and parenthesis) they somehow get mixed up, which does result in different results every time you do execute this query. If you do a little adjustment and change the variable name in the second Claus from refKey to let’s say refKey1 than everything is fine again.
select
meta(`holder`).`id`
from `mybucket` as `holder`
where ClassName = 'ObjectName' AND
(ANY `refKey` IN `holder`.`refKeys` SATISFIES [`refKey`.`key`, `refKey`.`value`] = ['KEY1', 'VALUE1'] END) AND
(ANY `refKey1` IN `holder`.`refKeys` SATISFIES [`refKey1`.`key`, `refKey1`.`value`] = ['KEY2', 'VALUE2'] END)
So if you come across the same issue make sure to use different variable names to avoid these issues … if you are using a Couchbase version prior to 4.6.2 then please make sure that the first clause (the one with the same naming as the index) narrows down your result set as much as possible, because all later clauses will be scans on your results ignoring the index.