Different results when using flex indexes

Hi,

I am trying to run a query using flex indexes and a wildcard query. The issue that I am having is that I am getting different results based on the fields I want to return from the document.

I have the following document in a bucket:

{
      "_class": "com.company.model.Identity",
      "displayName": "Mauro Monti",
      "emailAddress": "mauro.monti@company.com",
      "username": "mmonti",
      "firstName": "Mauro",
      "lastName": "Monti"
    }

I have an fts index created named test:

{
  "type": "fulltext-index",
  "name": "test",
  "uuid": "53f06b0e22e20c87",
  "sourceType": "couchbase",
  "sourceName": "identity",
  "sourceUUID": "94addaa1cea17029f205db3ce9f2164b",
  "planParams": {
    "maxPartitionsPerPIndex": 171,
    "indexPartitions": 6
  },
  "params": {
    "doc_config": {
      "docid_prefix_delim": "",
      "docid_regexp": "",
      "mode": "type_field",
      "type_field": "_class"
    },
    "mapping": {
      "analysis": {
        "analyzers": {
          "lower_analyzer": {
            "token_filters": [
              "to_lower"
            ],
            "tokenizer": "unicode",
            "type": "custom"
          }
        }
      },
      "default_analyzer": "standard",
      "default_datetime_parser": "dateTimeOptional",
      "default_field": "_all",
      "default_mapping": {
        "dynamic": true,
        "enabled": false
      },
      "default_type": "_default",
      "docvalues_dynamic": true,
      "index_dynamic": true,
      "store_dynamic": false,
      "type_field": "_type",
      "types": {
        "com.company.model.Identity": {
          "default_analyzer": "lower_analyzer",
          "dynamic": false,
          "enabled": true,
          "properties": {
            "displayName": {
              "dynamic": false,
              "enabled": true,
              "fields": [
                {
                  "docvalues": true,
                  "include_in_all": true,
                  "include_term_vectors": true,
                  "index": true,
                  "name": "displayName",
                  "store": true,
                  "type": "text"
                }
              ]
            },
            "emailAddress": {
              "dynamic": false,
              "enabled": true,
              "fields": [
                {
                  "analyzer": "web",
                  "docvalues": true,
                  "include_in_all": true,
                  "include_term_vectors": true,
                  "index": true,
                  "name": "emailAddress",
                  "store": true,
                  "type": "text"
                }
              ]
            },
            "firstName": {
              "dynamic": false,
              "enabled": true,
              "fields": [
                {
                  "docvalues": true,
                  "include_in_all": true,
                  "include_term_vectors": true,
                  "index": true,
                  "name": "firstName",
                  "store": true,
                  "type": "text"
                }
              ]
            },
            "lastName": {
              "dynamic": false,
              "enabled": true,
              "fields": [
                {
                  "docvalues": true,
                  "include_in_all": true,
                  "include_term_vectors": true,
                  "index": true,
                  "name": "lastName",
                  "store": true,
                  "type": "text"
                }
              ]
            },
            "username": {
              "dynamic": false,
              "enabled": true,
              "fields": [
                {
                  "docvalues": true,
                  "include_in_all": true,
                  "include_term_vectors": true,
                  "index": true,
                  "name": "username",
                  "store": true,
                  "type": "text"
                }
              ]
            }
          }
        }
      }
    },
    "store": {
      "indexType": "scorch"
    }
  },
  "sourceParams": {}
}

I am getting different results when I execute the same query with different select statements. As an example:

Selecting only the ID:

SELECT meta().id
FROM `identity` USE INDEX(test USING FTS) 
WHERE SEARCH(`identity`, { "query": { "wildcard": "*mauro.monti@company.com*" }})
AND  `_class` = "com.company.model.Identity"

Result:

[
  {
    "id": "b7e7594f-034d-460e-a949-943d6e2f7c31@identity"
  }
]

Selecting all the fields:

SELECT *
FROM `identity` USE INDEX(test USING FTS) 
WHERE SEARCH(`identity`, { "query": { "wildcard": "*mauro.monti@company.com*" }})
AND  `_class` = "com.company.model.Identity"

Result:

{
  "results": []
}

Selecting all the fields with a different wildcard query:

SELECT *
FROM `identity` USE INDEX(test USING FTS) 
WHERE SEARCH(`identity`, { "query": { "wildcard": "*mauro*" }})
AND  `_class` = "com.company.model.Identity"

Result:

[
  {
    "identity": {
      "_class": "com.company.model.Identity",
      "displayName": "Mauro Monti",
      "emailAddress": "mauro.monti@company.com",
      "username": "mmonti",
      "firstName": "Mauro",
      "lastName": "Monti"
    }
  }
]

Is this behavior due to the way I have my index defined?

Hi @monti.mauro, I’d firstly like to correct the nomenclature here - your queries are using the search functions capability of N1QL queries and not the flex index query capability.

As for the issue you’re seeing, I’m going to guess you’re on couchbase server version <= 6.6.0.

This issue where you do not see expected results in case of “non-covered” query when the “field” isn’t specified is a bug we’ve addressed in 6.6.1 - here’s the ticket for it - https://issues.couchbase.com/browse/MB-41536

Also, while using non-analytic queries such as these - Non-Analytic Queries | Couchbase Docs , we recommend using the “keyword” analyzer while running your FTS queries from N1QL.

2 Likes

Hi @abhinav thanks for the clarification. I indeed was using version 6.6.0. After testing in a v7, the queries are working as expected.