FlexIndex:Default Index: FlexQuery gives different results than GSi for range query with email field

Build: 7.0.0-2016
Build:7.0.0 build 2016

• Load dataset attached
• Create default fts index with keyword analyzer:

  {

  "type": "fulltext-index",

  "name": "default_index",

  "uuid": "5005f41409c983df",

  "sourceType": "gocbcore",

  "sourceName": "default",

  "sourceUUID": "b08aa384fdcd7adfdfe3569237428631",

  "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": {}

  }

• Create GSI primary index
• For the below Flex queries, fts index is considered and returns right results comparing with GSI

  Number of results from flex query: select meta().id from default use index (USING FTS, USING GSI) where ( email > "cynara" and email <= "luella") ORDER BY meta().id ASC is 219

• for below query Flex returns 500 docs where GSI results 262 docs

  select meta().id from default use index (USING FTS, USING GSI) where ( email > "cynara" and email <= "muella") ORDER BY meta().id ASC. ---> 500 docs

  select meta().id from default where ( email > "cynara" and email <= "muella") ORDER BY meta().id ASC --> 262 docs

  Explain Plan:

  {

  "#operator": "Sequence",

  "~children": [

  {

  "#operator": "Sequence",

  "~children": [

  {

  "#operator": "IndexFtsSearch",

  "covers": [

  "cover (((\"cynara\" < (`default`.`email`)) and ((`default`.`email`) <= \"muella\")))",

  "cover ((meta(`default`).`id`))"

  ],

  "index": "default_index",

  "index_id": "3a0c668028f3f2cf",

  "keyspace": "default",

  "namespace": "default",

  "search_info": {

  "field": "\"\"",

  "options": "{\"index\": \"default_index\"}",

  "outname": "out",

  "query": "{\"query\": {\"field\": \"email\", \"inclusive_max\": true, \"inclusive_min\": false, \"max\": \"muella\", \"min\": \"cynara\"}, \"score\": \"none\"}"

  },

  "using": "fts"

  },

  {

  "#operator": "Parallel",

  "~child": {

  "#operator": "Sequence",

  "~children": [

  {

  "#operator": "Filter",

  "condition": "cover (((\"cynara\" < (`default`.`email`)) and ((`default`.`email`) <= \"muella\")))"

  },

  {

  "#operator": "InitialProject",

  "result_terms": [

  {

  "expr": "cover ((meta(`default`).`id`))"

  }

  ]

  }

  ]

  }

  }

  ]

  },

  {

  "#operator": "Order",

  "sort_terms": [

  {

  "expr": "cover ((meta(`default`).`id`))"

  }

  ]

  }

  ]

  }

• one of the doc returned by FTS but not by GSI:

  {

  "emp_id": "10000003",

  "name": "Tempest Ángel",

  "dept": "Sales",

  "email": "tempest@mcdiabetes.com",

  "salary": 80144.68,

  "join_date": "1987-12-04T17:21:00",

  "languages_known": [

  "Sinhalese",

  "Africans",

  "Romanian"

  ],

  "is_manager": true,

  "mutated": 0,

  "type": "emp",

  "manages": {

  "team_size": 9,

  "reports": [

  "Hedda Evans",

  "Bryn Russell",

  "Kacila Campbell",

  "Jerica Hill",

  "Delores Hill",

  "Amara Stewart",

  "Salina Clark",

  "Bryn Thomas",

  "Cytheria Davis"

  ]

  }

  }