[bug] Query join between two types of documents (Adaptive index with predicate on document key)

SQL++
1

I have came across this weird behavior (potentially bug?) with the join query below. I was testing these queries in “Query Editor”. The result would often return empty and other times it would return correctly.

Query #1

SELECT * FROM bucket report JOIN bucket recording ON KEYS report.recordingId
WHERE report.type=“report” AND recording.type=“recording” AND meta(report).id=“report_0000”;

However, if i would remove the types in the WHERE clause, it will return the result correctly all the time.

Query #2

SELECT * FROM bucket report JOIN bucket recording ON KEYS report.recordingId WHERE meta(report).id=“report_0000”;

In the above scenario, we have these indexes created for the bucket

1. adaptive index on report
2.adpative index on recording
3.primary index

CREATE INDEX report ON bucket((distinct (pairs(self)))) WHERE (type = “report”)
CREATE INDEX recording ON bucket((distinct (pairs(self)))) WHERE (type = “recording”)
CREATE PRIMARY INDEX #primary ON bucket

When the result return correctly, we can see from the EXPLAIN that uses “#primary” index.

Question: The problem we are having on production is that, we don’t have a primary index created for that bucket. As state from the best practice guide, one should avoid adding primary index in production. Especially, dealing with large bucket size (in millions to billions number of document).

Therefore, while running Query #1, the result is always empty, and Query #2 will through index error due to lack of primary instead.

We would like to know how we can resolve this issue?

Version of couchbase 5.0

1 Like
2

Thanks for reporting it. Opened MB-33553

  1. Use normal GSI index instead of adaptive index.

  2. If you have predicate on META().id use USE KEYS this avoids index scan too.

     SELECT * 
 FROM bucket report  USE KEYS ["report_0000"] 
 JOIN bucket recording ON KEYS report.recordingId
 WHERE report.type="report" AND recording.type="recording";
1 Like
3

Thank you for replying.
Your suggested query resolved the issue we encountered.
:+1::+1::+1:

We will keep an eye out on issue MB-33553