Blocker Description
From CBSE-14445, use FTS match() in the WHERE clause of OUTER LEFT JOINS Not Returning Correct Result.
How to Reproduce :
1. Use database from falconEventDB-S606-aee69b0c-00b7-46c3-8e6f-c0755005266b.cblite2(withchanges).zip attached in the CBSE-14445 ticket.
2. Use any CBL 3.1.0 or cblite tool built with LiteCore 3.1.0 and execute the following query:
SELECT meta(Exhibitor).id, Exhibitor.* as content FROM _default AS Exhibitor LEFT OUTER JOIN _default AS SponsorshipLevel ON SponsorshipLevel.id = Exhibitor.sponsorshipLevelId WHERE Exhibitor.type = "Exhibitor" AND MATCH(Exhibitor.EXHIBITOR_LIST_ATTR_FTS_IDX, "test*") |
3. The result returned by CBL 3.1.0 is empty. The expected result is as follows:
[{"id": "S606::falcon-data-service::Exhibitor::af8bb0b7-d5e5-412b-8fc4-a82be632b09f", "content": {"address2":"","attachmentCount":0,"channels":[{"channel":"s606::event::aee69b0c-00b7-46c3-8e6f-c0755005266b"}],"city":"","deleted":"false","description":"","email":"","eventId":"aee69b0c-00b7-46c3-8e6f-c0755005266b","eventSponsor":true,"facebookUrl":"","featured":false,"hidden":false,"id":"af8bb0b7-d5e5-412b-8fc4-a82be632b09f","inboundLeadsEnabled":false,"instagramUrl":"","linkCount":0,"linkedInUrl":"","location":"","mobilePhone":"","name":"Test Exhibitor","otherPhone":"","profileId":"27e35868-fa80-4d06-b3f6-8a5e111b8f92","sponsorOnlyExhibitor":false,"sponsorshipLevelId":"a53a12f7-6d86-46c0-8ed2-257c1edd1c1f","state":"","streetAddress":"","twitterUrl":"","type":"Exhibitor","updatedAt":"2023-05-26T17:37:57.860Z","version":0,"virtualBoothUrl":"","websiteUrl":"","workPhone":"","zip":""}}] |
Some other info
1. The customer is using CBL Android 3.0.5.
2. The FTS index (EXHIBITOR_LIST_ATTR_FTS_IDX) was created from 2 fields : name and location.
3. The customer said that the query works in 3.0.5 but I couldn't run the same query with 3.0.x because in 3.0.x we have an issue that MATCH() function doesn't support data-source alias name. We fixed the issue in 3.1.0 (CBL-3994). So I couldn't confirm that this is a regression or not.
