Case statement giving incorrect result

SQL++
,
1

Hi

I have a query which returns ‘OVERDUE’ as status of the document when it meets a particular criteria inside Case statement. The query I am using is giving different result for same predicate when I add another field inside select (keeping the rest query exactly the same).

When I select following fields,

Select case When r.verificationStatus = 'PENDING' and (date_diff_str(r.embarkDate, '2018-03-06', 'day') <= 5 OR date_diff_str('2018-03-06' , r.firstPendingDocDate, 'day') >= 10 )
    Then 'OVERDUE' ELSE r.verificationStatus end as status, 
    r.shipCode, r.embarkDate,r.isInternational,r.isOcrDirty,r.debarkDate,r.firstName, r.middleName, r.lastName, r.reservationGuestId, r.securityPhotoMediaItemId, 
    r.profilePhotoMediaItemId, r.reservationNumber,r.isReviewLater, r.verifiedBy, r.shipName, r.genderCode  
    From reporting r where r.type = 'ReservationGuestModeration' 
    AND meta(r).id NOT LIKE "_sync%"  
    AND r.shipCode =  'BLISS' 
    AND r.embarkDate >= '2018-03-06' 
    AND r.verificationStatus IN ["PENDING"]

This query gives total 42 documents and all with ‘PENDING’ status.

When I append date_diff_str(‘2018-03-06’ , r.firstPendingDocDate, ‘day’)
as another select field in the above query, the result is : 33 documents as ‘OVERDUE’ and 9 documents as ‘PENDING’ (Which is the expected result).

Index Definition:

CREATE INDEX `IX_reporting_GuestListing_ReservationGuestModeration` ON `reporting`(`shipCode`,`embarkDate`,`verificationStatus`,`firstPendingDocDate`,`isInternational`,`isOcrDirty`,`debarkDate`,`firstName`,`middleName`,`lastName`,`reservationGuestId`,`securityPhotoMediaItemId`,`profilePhotoMediaItemId`,`reservationNumber`,`isReviewLater`,`verifiedBy`,`shipName`,`genderCode`) WHERE ((`type` = "ReservationGuestModeration") and (not ((meta().`id`) like "_sync%")))

Please note that this query is giving incorrect result when executed on Couchbase 5.0.1-5003-enterprise. When executed on version 4.6.3-4136-enterprise, it is giving proper result.

2

Looks like you might be hitting MB-27647

This has been fixed in 5.1.0 (5.1.0-5552) release

For covered queries. If you look EXPLAIN index_projection might be missing some fields referenced in the CASE. Work around is define LET xxx = [r.firstPendingDocDate] between from and where.