Strange behavior of query: different results per execution of same query without change of source data

SQL++
1

I was testing query in Query tab of workbench and noticed I get different results on each execution while the documents have not been added/updated/deleted. On each “Execute” button click, I would get either correct result or partial result.
Screen cast:

https://drive.google.com/file/d/1KWT8DyDkBIR3dp7M2EI-_L4Os5Tr6kJ4/view?usp=sharing

Query:

SELECT RAW  d.ball.position
FROM
	(
       SELECT 
	       (ARRAY {s.playerId, s.isResultIn, s.type, p.pointData.serveClass, p.pointData.scorer.playerId,
                'detail': (ARRAY detail
                            FOR detail IN s.detail
                            WHEN detail.keyLocation = 'BOUNCE' 
                            AND ABS(detail.ball.position.y) > ((4.1148 / 3) * 2) AND ABS(detail.ball.position.y) <= 99999999
                            AND ABS(detail.ball.position.y) <= 4.1148 
                            AND ABS(detail.ball.position.x) <= 6.4 END)
                }
        FOR s IN p.pointData.shot END) AS shot
	FROM CourtTracking p
	WHERE p.type = 'point'
	AND p.isValid = true
	AND p.pointData.tournament.year = 9999
	AND p.pointData.tournament.`number` = 999
	AND p.pointData.match.matchId = '999'
	AND p.`set` != 0
	AND p.isValid = true 
	AND p.type = 'point'
	AND p.pointData.shot IS NOT MISSING
	AND p.serve = 1
	AND array_contains(['RETURNED', 'NOT RETURNED'], p.pointData.serveClass)
	AND p.pointData.server.playerId = '999'
	AND p.pointData.scorer.playerId != '999'
    AND p.point % 2  = 1  
     ) AS t UNNEST t.shot[0:1] AS s
    UNNEST s.detail as d
    WHERE s.playerId = '999'
    AND s.isResultIn = true
    AND s.type='SERVE'
    AND ARRAY_LENGTH(s.detail)>0
2

HI @evgeniya.bell, this one has all the characteristics of a bug, unfortunately.
Each time, we’re probably choosing a different index and a plan.
Few things for you to debug.

  1. See which index and plan gives you the right results and use the index in the query with USE INDEX directive.
    2.For us to debug this, we’d need data and list of all the indexes you have and your couchbase version. Please share the data if you can. Or you can use some open data to repro and point to it.
    Also, if you’re an enterprise user, you can open a support ticket.
3

The other approach is simply do the USE INDEX (#primary) on the CourtTracking. The results you get on this should be the CORRECT results. Even with this, verify this returns consistent results.