Uploaded image for project: 'Couchbase Lite'
  1. Couchbase Lite
  2. CBL-4639

Use FTS match() in the WHERE clause of LEFT OUTER JOINS Not Returning Correct Result

    XMLWordPrintable

Details

    • Bug
    • Resolution: Fixed
    • Critical
    • Beryllium
    • 3.1.0
    • LiteCore
    • Security Level: Public
    • None
    • LiteCore 111
    • 4

    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.

      Attachments

        Issue Links

          backports to

          Bug - A problem which impairs or prevents the functions of the product. CBL-4623 Use FTS match() in the WHERE clause of LEFT OUTER JOINS Not Returning Correct Result

          • Blocker - Blocks development and/or testing work, production could not run.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. CBL-4640 Use FTS match() in the WHERE clause of LEFT OUTER JOINS Not Returning Correct Result

          • Major - Major loss of function.
          • Closed
          Clones

          Bug - A problem which impairs or prevents the functions of the product. CBL-4623 Use FTS match() in the WHERE clause of LEFT OUTER JOINS Not Returning Correct Result

          • Blocker - Blocks development and/or testing work, production could not run.
          • Closed
          is cloned by

          Bug - A problem which impairs or prevents the functions of the product. CBL-4640 Use FTS match() in the WHERE clause of LEFT OUTER JOINS Not Returning Correct Result

          • Major - Major loss of function.
          • Closed

          Activity

            People

              jianmin.zhao Jianmin Zhao
              jianmin.zhao Jianmin Zhao
              Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                PagerDuty