I am running into an issue in couchbase Enterprise Edition 5.5.2 build 3733. It looks like couchbase is incorrectly handling AND TRUE and LIMIT in a query. Using beer-sample as an example:
If I run either of the following queries, I get 5891 results:
/* 1 */ SELECT * FROM `beer-sample` WHERE type='beer' /* 2 */ SELECT * FROM `beer-sample` WHERE TRUE AND type='beer'
If I run this query with a LIMIT 2 clause added, I get 1 result (expecting 2)
/* 3 */ SELECT * FROM `beer-sample` WHERE TRUE AND type='beer' LIMIT 2
Looking at the plan for query 3, I can see that it performs a primary scan with limit 2, a fetch, and then applies the filter afterwards.
I can also run this query and get 2 results as expected:
/* 4 */ SELECT * FROM `beer-sample` WHERE FALSE OR type='beer' LIMIT 2
The plan for query 4 does not apply a limit on the primary scan.
If I run the same exact queries in couchbase Enterprise Edition 5.1.0 build 5552, I get the expected number of results for all queries. The plan for query 3 in 5.10 does not appear to apply a limit in the primary scan.
Is this a known bug in couchbase, or am I missing something?