Flex Index: Default Index: queries returns 0 results for text comparison in predicate

Build: 7.0.0-1939

• 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 but returns 0 results but GSI do returns some results

  Number of results from flex query: select meta().id from default USE INDEX ( USING FTS) where type = "emp" and ANY v IN languages_known SATISFIES v = "Urdu" END is 0

  Number of results from gsi query: select meta().id from default where type = "emp" and ANY v IN languages_known SATISFIES v = "Urdu" END is 119

  Explain plan:

  {

  "#operator": "Sequence",

  "~children": [

  {

  "#operator": "IndexFtsSearch",

  "covers": [

  "cover ((((`default`.`type`) = \"emp\") and any `v` in (`default`.`languages_known`) satisfies (`v` = \"Urdu\") end))",

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

  ],

  "index": "default_index",

  "index_id": "5005f41409c983df",

  "keyspace": "default",

  "namespace": "default",

  "search_info": {

  "field": "\"\"",

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

  "outname": "out",

  "query": "{\"query\": {\"conjuncts\": [{\"field\": \"type\", \"term\": \"emp\"}, {\"field\": \"languages_known\", \"term\": \"Urdu\"}]}, \"score\": \"none\"}"

  },

  "using": "fts"

  },

  {

  "#operator": "Parallel",

  "~child": {

  "#operator": "Sequence",

  "~children": [

  {

  "#operator": "Filter",

  "condition": "cover ((((`default`.`type`) = \"emp\") and any `v` in (`default`.`languages_known`) satisfies (`v` = \"Urdu\") end))"

  },

  {

  "#operator": "InitialProject",

  "result_terms": [

  {

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

  }

  ]

  }

  ]

  }

  }

  ]

  }

Number of results from flex query: select meta().id from default USE INDEX ( USING FTS) where type = "emp" and (((name > "Vala" and name <= "Ebony" AND (salary > 109000.0 and salary < 116292.0)) AND salary > 70000.0 and salary < 79884.0 AND email between "duvessa@mcdiabetes.com" and "devi@mcdiabetes.com") AND email between "thea@mcdiabetes.com" and "gallia@mcdiabetes.com" OR dept between "Dev-ops" and "Sales") OR dept between "Pre-sales" and "Pre-sales" is 0

Number of results from gsi query: select meta().id from default where type = "emp" and (((name > "Vala" and name <= "Ebony" AND (salary > 109000.0 and salary < 116292.0)) AND salary > 70000.0 and salary < 79884.0 AND email between "duvessa@mcdiabetes.com" and "devi@mcdiabetes.com") AND email between "thea@mcdiabetes.com" and "gallia@mcdiabetes.com" OR dept between "Dev-ops" and "Sales") OR dept between "Pre-sales" and "Pre-sales" is 746

explain plan:

{

    "#operator": "Sequence",

    "~children": [

        {

            "#operator": "IndexFtsSearch",

            "index": "default_index",

            "index_id": "5005f41409c983df",

            "keyspace": "default",

            "namespace": "default",

            "search_info": {

                "field": "\"\"",

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

                "outname": "out",

                "query": "{\"query\": {\"disjuncts\": [{\"conjuncts\": [{\"field\": \"type\", \"term\": \"emp\"}, {\"field\": \"salary\", \"inclusive_max\": false, \"inclusive_min\": false, \"max\": 116292, \"min\": 109000}, {\"field\": \"salary\", \"inclusive_max\": false, \"inclusive_min\": false, \"max\": 79884, \"min\": 70000}]}, {\"conjuncts\": [{\"field\": \"type\", \"term\": \"emp\"}, {\"field\": \"dept\", \"inclusive_max\": true, \"inclusive_min\": true, \"max\": \"Sales\", \"min\": \"Dev-ops\"}]}, {\"field\": \"dept\", \"inclusive_max\": true, \"inclusive_min\": true, \"max\": \"Pre-sales\", \"min\": \"Pre-sales\"}]}, \"score\": \"none\"}"

            },

            "using": "fts"

        },

        {

            "#operator": "Fetch",

            "keyspace": "default",

            "namespace": "default"

        },

        {

            "#operator": "Parallel",

            "~child": {

                "#operator": "Sequence",

                "~children": [

                    {

                        "#operator": "Filter",

                        "condition": "((((`default`.`type`) = \"emp\") and ((((((((\"Vala\" < (`default`.`name`)) and ((`default`.`name`) <= \"Ebony\")) and ((109000 < (`default`.`salary`)) and ((`default`.`salary`) < 116292))) and (70000 < (`default`.`salary`))) and ((`default`.`salary`) < 79884)) and ((`default`.`email`) between \"duvessa@mcdiabetes.com\" and \"devi@mcdiabetes.com\")) and ((`default`.`email`) between \"thea@mcdiabetes.com\" and \"gallia@mcdiabetes.com\")) or ((`default`.`dept`) between \"Dev-ops\" and \"Sales\"))) or ((`default`.`dept`) between \"Pre-sales\" and \"Pre-sales\"))"

                    },

                    {

                        "#operator": "InitialProject",

                        "result_terms": [

                            {

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

                            }

                        ]

                    }

                ]

            }

        }

    ]

}

Number of results from flex query: select meta().id from default USE INDEX ( USING FTS) where type = "emp" and (name > "Kerry-Ann" and name < "Kimberly") is 0

Number of results from gsi query: select meta().id from default where type = "emp" and (name > "Kerry-Ann" and name < "Kimberly") is 22

Explain plan:

{

    "#operator": "Sequence",

    "~children": [

        {

            "#operator": "IndexFtsSearch",

            "index": "default_index",

            "index_id": "5005f41409c983df",

            "keyspace": "default",

            "namespace": "default",

            "search_info": {

                "field": "\"\"",

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

                "outname": "out",

                "query": "{\"query\": {\"conjuncts\": [{\"field\": \"type\", \"term\": \"emp\"}, {\"field\": \"name\", \"inclusive_max\": false, \"inclusive_min\": false, \"max\": \"Kimberly\", \"min\": \"Kerry-Ann\"}]}, \"score\": \"none\"}"

            },

            "using": "fts"

        },

        {

            "#operator": "Fetch",

            "keyspace": "default",

            "namespace": "default"

        },

        {

            "#operator": "Parallel",

            "~child": {

                "#operator": "Sequence",

                "~children": [

                    {

                        "#operator": "Filter",

                        "condition": "(((`default`.`type`) = \"emp\") and ((\"Kerry-Ann\" < (`default`.`name`)) and ((`default`.`name`) < \"Kimberly\")))"

                    },

                    {

                        "#operator": "InitialProject",

                        "result_terms": [

                            {

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

                            }

                        ]

                    }

                ]

            }

        }

    ]

}