Uploaded image for project: 'Couchbase Server'
  1. Couchbase Server
  2. MB-39633

FlexIndex:n1fty: not sargable with default index

    XMLWordPrintable

    Details

    • Triage:
      Untriaged
    • Story Points:
      1
    • Is this a Regression?:
      Yes

      Description

      Build : 6.6.0-7713
      Build Sanity which is failied: http://server.jenkins.couchbase.com/job/build_sanity_matrix/DISTRO=centos7,TYPE=4node/7550/consoleFull

      • Load attached napa dataset: export_napa.json
      • Create default index like below:

        {
          "type": "fulltext-index",
          "name": "default_index",
          "uuid": "4d7e227251d72327",
          "sourceType": "couchbase",
          "sourceName": "default",
          "planParams": {
            "maxPartitionsPerPIndex": 171
          },
          "params": {
            "doc_config": {
              "docid_prefix_delim": "",
              "docid_regexp": "",
              "mode": "type_field",
              "type_field": "type"
            },
            "mapping": {
              "analysis": {},
              "default_analyzer": "standard",
              "default_datetime_parser": "dateTimeOptional",
              "default_field": "_all",
              "default_mapping": {
                "default_analyzer": "keyword",
                "dynamic": true,
                "enabled": true
              },
              "default_type": "_default",
              "docvalues_dynamic": true,
              "index_dynamic": true,
              "store_dynamic": false,
              "type_field": "type"
            },
            "store": {
              "indexType": "scorch",
              "mossStoreOptions": {}
            }
          },
          "sourceParams": {}
        }
        

      • Below queries throws error like ""msg": "n1fty: not sargable",":

        SELECT META().id FROM `default` USE INDEX (USING FTS, USING GSI) WHERE address.ecpdId="1" ORDER BY META().id LIMIT 100
         
        SELECT META().id, email FROM `default` USE INDEX (USING FTS, USING GSI) WHERE address.ecpdId="3" OR address.applicationId=300 ORDER BY email,META().id LIMIT 10
         
        SELECT META().id, address.applicationId FROM `default` USE INDEX (USING FTS, USING GSI) WHERE address.ecpdId="3" AND address.deviceTypeId=9 ORDER BY address.applicationId,META().id LIMIT 100
         
        SELECT META().id FROM `default` USE INDEX (USING FTS, USING GSI) WHERE (address.ecpdId="3" OR address.applicationId=300) AND (address.deviceTypeId=9 OR address.deviceStatus=0) ORDER BY META().id LIMIT 100
         
        SELECT META().id, address.activationDate FROM `default` USE INDEX (USING FTS, USING GSI) WHERE address.ecpdId="1" ORDER BY address.activationDate,META().id LIMIT 100
        SELECT META().id FROM default USE INDEX (USING FTS, USING GSI) WHERE ( ( ( ( ( email LIKE "A%") OR ( ANY v IN devices SATISFIES v LIKE "2%" END)) OR ( first_name > "Karianne" AND first_name <= "Qarianne")) OR ( routing_number = 12160)) OR ( address.activationDate BETWEEN "1995-10-10T21:22:00" AND "2020-05-09T20:08:02.462692")) ORDER BY address.city,META().id LIMIT 100
         
        SELECT META().id, company_name FROM default USE INDEX (USING FTS, USING GSI) WHERE (( email = "Aaron.Jaskolski18@yahoo.com") AND ( ANY v IN children SATISFIES v.first_name = "Raven" END)) OR (( company_code > "IMWW" AND company_code <= "D3IHO") AND ( routing_number = 67473) OR ( address.activationDate BETWEEN "2019-10-10T21:22:00" AND "2020-05-09T20:08:02.462692")) ORDER BY address.activationDate,META().id OFFSET 500 LIMIT 100
         
         
        SELECT first_name, last_name, email FROM default USE INDEX (USING FTS, USING GSI) WHERE ( ( SOME v IN children SATISFIES v.first_name LIKE "R%" END) AND ( age > 20 AND age < 40)) OR (( dob BETWEEN "1994-12-08T01:19:00" AND "2020-05-09T20:08:02.469127") AND isActive = FALSE) OR (address.deviceTypeId > 3 AND ISNUMBER(address.deviceTypeId)) ORDER BY address.activationDate,META().id OFFSET 500 LIMIT 100
         
        SELECT first_name, last_name, email, address.country FROM default USE INDEX (USING FTS, USING GSI) WHERE ANY c IN children SATISFIES c.gender = "F" AND (c.age > 5 AND c.age <15) OR c.first_name LIKE "a%" END ORDER BY address.country,META().id OFFSET 500 LIMIT 100
         
         
        [
          {
            "code": 5000,
            "msg": "n1fty: not sargable",
            "query": "SELECT META().id FROM `default` USE INDEX (USING FTS, USING GSI) WHERE address.ecpdId=\"adda\" ORDER BY META().id LIMIT 100"
          }
        ]
        

        Attachments

          Issue Links

          No reviews matched the request. Check your Options in the drop-down menu of this sections header.

            Activity

            Hide
            arunkumar Arunkumar Senthilnathan added a comment -

            Last good build: 6.6.0-7708

            Show
            arunkumar Arunkumar Senthilnathan added a comment - Last good build: 6.6.0-7708
            Hide
            abhinav Abhinav Dangeti added a comment -

            Looks like this regression was caused by MB-39592. Let me see if I can address this easily - if not I'll back out that change.

            Show
            abhinav Abhinav Dangeti added a comment - Looks like this regression was caused by  MB-39592 . Let me see if I can address this easily - if not I'll back out that change.
            Show
            abhinav Abhinav Dangeti added a comment - http://review.couchbase.org/c/n1fty/+/129549
            Hide
            abhinav Abhinav Dangeti added a comment -

            Fix available in builds 6.6.0-7733 onwards.

            Show
            abhinav Abhinav Dangeti added a comment - Fix available in builds 6.6.0-7733 onwards.
            Hide
            girish.benakappa Girish Benakappa added a comment -

            Build Sanity with 6.6.0-7748 looks good.

            Abhinav Dangeti This fix yet to be merged to CC ?

            Show
            girish.benakappa Girish Benakappa added a comment - Build Sanity with 6.6.0-7748 looks good. Abhinav Dangeti This fix yet to be merged to CC ?
            Hide
            abhinav Abhinav Dangeti added a comment -

            The issue also affects CC builds, and the fix has been forward merged.

            However since the fix from MB-38957 isn't available in CC builds, you'll hit that issue before you see this

            Show
            abhinav Abhinav Dangeti added a comment - The issue also affects CC builds, and the fix has been forward merged. However since the fix from  MB-38957 isn't available in CC builds, you'll hit that issue before you see this
            Hide
            girish.benakappa Girish Benakappa added a comment -

            Closing as build sanity looks good with 7.0.0-2572 and 6.6.0-7748

            Show
            girish.benakappa Girish Benakappa added a comment - Closing as build sanity looks good with 7.0.0-2572 and 6.6.0-7748

              People

              Assignee:
              abhinav Abhinav Dangeti
              Reporter:
              girish.benakappa Girish Benakappa
              Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved:

                  Gerrit Reviews

                  There are no open Gerrit changes

                    PagerDuty