N1QL search query result varies depending on selected fields

[JesusTheHun]

I’m testing new 6.5 feature, N1QL SEARCH function but surprisingly query result varies depending on selected fields.

I created a FTS index that respond as expected for my requests.
The FTS type identifier is based on the “is_manageable” field.
The field used as a FTS type is also indexed in the query service.

Query index :

CREATE INDEX `is_manageable` ON `foo`(`is_manageable`)

FTS index :

Summary

{
  "type": "fulltext-index",
  "name": "manageables",
  "uuid": "2d05b774c39f96cb",
  "sourceType": "couchbase",
  "sourceName": "foo",
  "sourceUUID": "0f88fab718b7522c2cca0649af97c26f",
  "planParams": {
    "maxPartitionsPerPIndex": 171,
    "indexPartitions": 6
  },
  "params": {
    "doc_config": {
      "docid_prefix_delim": "",
      "docid_regexp": "",
      "mode": "type_field",
      "type_field": "is_manageable"
    },
    "mapping": {
      "analysis": {
        "analyzers": {
          "k_main_entry_analyser": {
            "char_filters": [
              "asciifolding"
            ],
            "token_filters": [
              "to_lower",
              "unique",
              "k_min_length",
              "k_ngram"
            ],
            "tokenizer": "unicode",
            "type": "custom"
          },
          "k_secondary_entries_analyser": {
            "char_filters": [
              "asciifolding"
            ],
            "token_filters": [
              "k_min_length",
              "to_lower"
            ],
            "tokenizer": "unicode",
            "type": "custom"
          }
        },
        "token_filters": {
          "k_min_length": {
            "max": 255,
            "min": 3,
            "type": "length"
          },
          "k_ngram": {
            "back": "false",
            "max": 20,
            "min": 3,
            "type": "edge_ngram"
          }
        }
      },
      "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": {
        "true": {
          "default_analyzer": "standard",
          "dynamic": false,
          "enabled": true,
          "properties": {
            "description": {
              "dynamic": false,
              "enabled": true,
              "fields": [
                {
                  "analyzer": "k_secondary_entries_analyser",
                  "index": true,
                  "name": "description",
                  "type": "text"
                }
              ]
            },
            "title": {
              "dynamic": false,
              "enabled": true,
              "fields": [
                {
                  "analyzer": "k_main_entry_analyser",
                  "docvalues": true,
                  "include_in_all": true,
                  "include_term_vectors": true,
                  "index": true,
                  "name": "title",
                  "store": true,
                  "type": "text"
                }
              ]
            }
          }
        }
      }
    },
    "store": {
      "indexType": "scorch"
    }
  },
  "sourceParams": null
}

When I execute this query :

select meta(m).id from `foo` as m
where m.is_manageable = "true"
and search(m, "coco");

The result contains one document containing only the id, as expected.

When I execute this query (note the wildcard select):

select * from `foo` as m
where m.is_manageable = "true"
and search(m, "coco");

It returns no document.
The doc at Search Functions | Couchbase Docs says :

If the full text search index being queried has its default mapping disabled and has a custom type mapping defined, the query needs to specify the type explicitly. The above query uses the demonstration index travel-sample-index-hotel-description, which has the custom type mapping “hotel”.
For more information on defining custom type mappings within the full text search index, refer to Specifying Type Mappings. Note that for N1QL queries, only full text search indexes with one type mapping are searchable. Also the supported type identifiers at the moment are “type_field” and “docid_prefix”, “docid_regexp” isn’t supported yet for SEARCH queries via N1QL.

I have only one type mapping, and the field is specified in the query. I am missing something ?

I tried to compare with the sample travel bucket, both query return consistent results.

SELECT *
FROM `travel-sample` AS t1
WHERE t1.type = "hotel" and SEARCH(t1, "bathrobes");

Is it supposed to behave like that ? If so, how can I have the entire document ?

PS :

I tried to use Doc ID up to separator type identifier but this definition it never returns anything in the N1QL search query. That’s probably another topic.

[abhinav]

Thank you for bringing up the issue.

In the event of “SELECT *”, the query involves a document fetch from couchbase and reapplying the search query on it to validate the fetched document. These document fetches do not occur when the “SELECT” only requests meta data.

The issue you’ve discovered here lies within this validation code path - which we will address for the upcoming release: 6.6 and after - it’s tracked with this internal ticket: https://issues.couchbase.com/browse/MB-39592

For the moment, we do have a work around for you - say your FTS index’s name was manageables, you can run your query this way …

select meta(m).id from `foo` as m
where m.is_manageable = "true"
and search(m, "coco", {"index":"manageables"});

select * from `foo` as m
where m.is_manageable = "true"
and search(m, "coco", {"index":"manageables"});

This essentially enforces the “validation” phase to use the same definition as the FTS index used earlier as opposed to inferring one from the query.

If you were to have a custom type mapping, the query would need to carry the condition expression outside the SEARCH(…) function, and your example is on point.

[JesusTheHun]

Thank you for your answer. Your workaround works like a charm.
How can I carry the condition of my custom type with an ID prefix ? I tried the LIKE function and SUBSTR without success.

[abhinav]

To use an ID prefix, you’d need to use ‘docid_prefix_delim’ in your FTS index definition as opposed to ‘type_field’.

Here’s how you’d set up ‘docid_prefix_delim’ for your index : Say you have 3 docs with keys: hotel_001, hotel_002, airport_001, and you want to index all documents with prefix “hotel”, you define, “docid_prefix_delim”: “_” and “mode”: “docid_prefix”; Next you will define a type mapping “hotel” and either index everything within or specific fields.

The condition expression now for this FTS index is “meta().id LIKE ‘hotel_%’”, so here’s a sample query for you assuming you name your FTS index “hotels” over bucket “travel-sample” …

SELECT *
FROM `travel-sample` AS t1
WHERE meta().id LIKE "hotel_%" and SEARCH(t1, "bathrobes", {"index": "hotels"});

I realize the documentation doesn’t carry this, I’ll have an update on it shortly.

[JesusTheHun]

I tried LIKE but only to cover the type, without the delimiter !

LIKE "hotel%" . Wrong.

Thank you very much for your responses.

EDIT : from a performance perspective, I realize LIKE uses index when the wildcard is on the right, but how does it compare with a strict string equality like in my exemple ?
Also type field type cannot be boolean, is there a reason for that or is it just a forgotten case ? if so do you plan to add it in a future release ?

[abhinav]

The condition expressions are to qualify FTS indexes for N1QL queries. There is no performance difference in evaluating those with ‘LIKE’ and those with ‘=’.

The type_field needs to be of “text” type. These are the names used for type mappings within the FTS index.