Details
-
Bug
-
Resolution: Fixed
-
Major
-
Cheshire-Cat
-
Untriaged
-
1
-
Unknown
Description
Build: 7.0.0-2005
- Load Travel-Sample
- Create a GSI with type: CREATE INDEX `def_type` ON `travel-sample`(`type`)
- Create FTS index with below:
{
"type": "fulltext-index",
"name": "t_smapl",
"uuid": "3157423be1f0eae5",
"sourceType": "gocbcore",
"sourceName": "travel-sample",
"sourceUUID": "7e9fad049aa02b440b6d98cd93dbeeaa",
"planParams": {
"maxPartitionsPerPIndex": 171,
"indexPartitions": 6
},
"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": {
"dynamic": true,
"enabled": false
},
"default_type": "_default",
"docvalues_dynamic": true,
"index_dynamic": true,
"store_dynamic": false,
"type_field": "_type",
"types": {
"airline": {
"dynamic": false,
"enabled": true,
"properties": {
"country": {
"dynamic": false,
"enabled": true,
"fields": [
{
"analyzer": "keyword",
"docvalues": true,
"include_in_all": true,
"include_term_vectors": true,
"index": true,
"name": "country",
"type": "text"
}
]
}
}
},
"airport": {
"dynamic": false,
"enabled": true,
"properties": {
"country": {
"dynamic": false,
"enabled": true,
"fields": [
{
"analyzer": "keyword",
"docvalues": true,
"include_in_all": true,
"include_term_vectors": true,
"index": true,
"name": "country",
"type": "text"
}
]
}
}
}
}
},
"store": {
"indexType": "scorch"
}
},
"sourceParams": {}
}
- Below Flex Query reports 242 results
SELECT meta().id
FROM `travel-sample` as t USE INDEX (USING FTS, USING GSI)
WHERE type = "airline" or type = "airport" and country = "France"
Explain plan:
{
"#operator": "Sequence",
"~children": [
{
"#operator": "IntersectScan",
"scans": [
{
"#operator": "IndexScan3",
"as": "t",
"index": "def_type",
"index_id": "86a9d5335c9ca7de",
"index_projection": {
"primary_key": true
},
"keyspace": "travel-sample",
"namespace": "default",
"spans": [
{
"exact": true,
"range": [
{
"high": "\"airline\"",
"inclusion": 3,
"low": "\"airline\""
}
]
},
{
"exact": true,
"range": [
{
"high": "\"airport\"",
"inclusion": 3,
"low": "\"airport\""
}
]
}
],
"using": "gsi"
},
{
"#operator": "IndexFtsSearch",
"as": "t",
"index": "t_smapl",
"index_id": "3157423be1f0eae5",
"keyspace": "travel-sample",
"namespace": "default",
"search_info": {
"field": "\"\"",
"options": "{\"index\": \"t_smapl\"}",
"outname": "out",
"query": "{\"query\": {\"field\": \"country\", \"term\": \"France\"}, \"score\": \"none\"}"
},
"using": "fts"
}
]
},
{
"#operator": "Fetch",
"as": "t",
"keyspace": "travel-sample",
"namespace": "default"
},
{
"#operator": "Parallel",
"~child": {
"#operator": "Sequence",
"~children": [
{
"#operator": "Filter",
"condition": "(((`t`.`type`) = \"airline\") or (((`t`.`type`) = \"airport\") and ((`t`.`country`) = \"France\")))"
},
{
"#operator": "InitialProject",
"result_terms": [
{
"expr": "(meta(`t`).`id`)"
}
]
}
]
}
}
]
}
- Below corresponding GSI query gives 408 results
SELECT meta().id
FROM `travel-sample` as t
WHERE type = "airline" or type = "airport" and country = "France"
Explain Plan:
{
"#operator": "Sequence",
"~children": [
{
"#operator": "IndexScan3",
"as": "t",
"index": "def_type",
"index_id": "86a9d5335c9ca7de",
"index_projection": {
"primary_key": true
},
"keyspace": "travel-sample",
"namespace": "default",
"spans": [
{
"exact": true,
"range": [
{
"high": "\"airline\"",
"inclusion": 3,
"low": "\"airline\""
}
]
},
{
"exact": true,
"range": [
{
"high": "\"airport\"",
"inclusion": 3,
"low": "\"airport\""
}
]
}
],
"using": "gsi"
},
{
"#operator": "Fetch",
"as": "t",
"keyspace": "travel-sample",
"namespace": "default"
},
{
"#operator": "Parallel",
"~child": {
"#operator": "Sequence",
"~children": [
{
"#operator": "Filter",
"condition": "(((`t`.`type`) = \"airline\") or (((`t`.`type`) = \"airport\") and ((`t`.`country`) = \"France\")))"
},
{
"#operator": "InitialProject",
"result_terms": [
{
"expr": "(meta(`t`).`id`)"
}
]
}
]
}
}
]
}
Attachments
Issue Links
- is duplicated by
-
MB-39253 FlexIndex: multitypemapping: FTS index not sargable if predicate field is not indexed in all typemapping in index
-
- Closed
-