Collections and counts

When I added those documents you gave and run the query you gave above I got 3 entries in 1 result using .NET:

Index 0: The first document in your list
Index 1: 1
Index 2: 1

This seems to be correct? What are you getting?

@borrrden just increase the number of entries of Collection B and Collection C to 5 and above it will return the wrong results in Couchbase lite for android

I’m not that big of an expert in the way that JOIN works but what is happening is that it’s creating a sort of cross product for every permutation of the JOIN. So say you have values of 3 and 5, first you end up with 3 rows for the initial join and then each of those rows get joined with the 5 from the second for a total of 15 entries. @jens Is this how JOIN is supposed to work? Part of me thinks it should be different but I can’t quite put my finger on what the behavior should be and the other part thinks that this is correct.

As a workaround, obviously, you can stop chaining JOIN in the short term and run the two independently.

@borden shall i report as a bug in github?

It took me a while to correctly write the N1QL equivalent but the same behavior applies to server which makes me think that this result is correct.

I’ve confirmed with the N1QL team that the results are correct. Unfortunately given the current mobile query constraints there is not a way to get all the results you want in one single query. It would be reliant on an aggregate distinct operator which has yet to be implemented (i.e. Function.count(SelectResult.expression(Meta.id).from("collectionBDS")).distinct()). The reasoning behind the way it becomes 6 is because each join permutation needs to be accounted for. So in the case you have 2 matches on B and 3 matches on C your rows would be like this:

  1. Result with B1 and C1
  2. Result with B1 and C2
  3. Result with B1 and C3
  4. Result with B2 and C1
  5. Result with B2 and C2
  6. Result with B2 and C3

COUNT is a post fetch operator so it will just return the number of rows emitted in the query. You will have to split your query into two each with one join to get the results you want. If you are worried about multithreading issues you can run the entire two step query inside of a call to inBatch

Thanks @borrrden :grinning: