LIMIT and OFFSET Clauses are not working properly with joins

.NET SDK
1

Hello Guys,

I have 100000 documents in my bucket which is having different types of documents.

Now my problem is that when I am using joins with Limit and Offset then I think it is considering only leading document type for Limit.

eg. LIMIT 10 OFFSET 0 then it is giving only 8 records.
LIMIT 10 OFFSET 10 then it is giving next 7 records.

Is this the behavior or bug ?

My requirement is that it should apply the OFFSET and LIMIT clauses after joining.And should return 10 docs for
LIMIT 10 OFFSET 0

Can any one please help me on this.

Regards
Mukesh Raghuwanhsi

2

@mukeshraghu1c

My understanding is that LIMIT and OFFSET should be applied after the joins and filters are complete. Can you provide the full text of the query you are using?

Thanks,
Brant

3

Thanks @btburnett3 for the reply.

Please find the query below -

SELECT * FROM N1QL_PROD AS INVOICE_0 
UNNEST INVOICE_0.INVOICE_LINE INVOICE_LINE_0_0
JOIN N1QL_PROD AS SALES_ORDER_0 ON KEYS ("SALES_ORDER:" || TOSTRING(INVOICE_LINE_0_0.SALES_ORDER_ID) || ":" || TOSTRING(INVOICE_LINE_0_0.VERSION))
WHERE INVOICE_0.DOCUMENT_TYPE = "INVOICE"
  AND SALES_ORDER_0.DOCUMENT_TYPE = "SALES_ORDER"
  AND INVOICE_0.CONNECTIONID = SALES_ORDER_0.CONNECTIONID
  AND IFNULL(INVOICE_0.NAME,"") LIKE "%John Bowler%"
  AND IFNULL(SALES_ORDER_0.SO_PID,"") LIKE "%DV1-422::%"
  AND INVOICE_0.VERSION BETWEEN 1 AND 1
LIMIT 10 OFFSET 0;

This query is giving me 8 records out of 100000 documents.And in next chunk (i.e. OFFSET 10) it is giving next 7 records.

Regards
Mukesh Raghuwanshi

4

@mukeshraghu1c

Your query is definitely advanced, but it appears like it should work to me. Could add EXPLAIN to the beginning of the statement and give us the output. There might be something in the query plan that will clue us in. If that doesn’t work, we might need to get this question over to some of the experts from the query team.

Thanks,
Brant

5

Thanks for the reply.

Sorry I was mistaken here.

I found that the limit and offset clause is working absolutely fine with joins also.

Only the prob is that it is giving me duplicate records and I have applied distinct after getting the records that is why it was giving me only 8 records.