Details
-
Bug
-
Resolution: Fixed
-
Critical
-
6.5.0
Description
Build: 6.5.0-3939
I'm trying to run the following query against standard `beer-sample` bucket:
SELECT t1.code,
|
t1.state,
|
t1.city,
|
t2.name
|
FROM `beer-sample` AS t1
|
INNER JOIN `beer-sample` t2 ON t1.code=t2.brewery_id
|
WHERE t1.type='brewery' |
AND t2.type='beer' |
AND SEARCH(`t1`.`state`, 'state:California') |
ORDER BY t1.code,
|
t2.name
|
fts index is:
{
"name": "idx_beer_sample_fts",
"type": "fulltext-index",
"params": {
"doc_config":
,
"mapping": {
"default_analyzer": "standard",
"default_datetime_parser": "dateTimeOptional",
"default_field": "_all",
"default_mapping":
,
"default_type": "_default",
"docvalues_dynamic": true,
"index_dynamic": true,
"store_dynamic": false,
"type_field": "_type"
},
"store": {
"indexType": "scorch",
"kvStoreName": "mossStore",
"mossStoreOptions": {}
}
},
"sourceType": "couchbase",
"sourceName": "beer-sample",
"sourceUUID": "b91fe621a8a0b93e714db0bdb1b1726a",
"sourceParams": null,
"planParams": {
"maxPartitionsPerPIndex": 171,
"numReplicas": 0,
"indexPartitions": 6
},
"uuid": "515dceb756d04fb8"
}
GSI indexes are:
CREATE PRIMARY INDEX `beer_primary` ON `beer-sample` WITH { "defer_build":true } |
CREATE INDEX `beer_sample_brewery_id_idx` ON `beer-sample`((self.`brewery_id`))
|
CREATE INDEX `beer_sample_code_idx` ON `beer-sample`((self.`code`))
|
CREATE INDEX `idx_brewery_id` ON `beer-sample`(`brewery_id`)
|
CREATE INDEX `idx_code` ON `beer-sample`(`code`)
|
CREATE INDEX `idx_type` ON `beer-sample`(`type`)
|
Explain plan is:
{
|
"#operator": "Sequence", |
"~children": [ |
{
|
"#operator": "Sequence", |
"~children": [ |
{
|
"#operator": "IntersectScan", |
"scans": [ |
{
|
"#operator": "IndexScan3", |
"as": "t1", |
"index": "beer_sample_code_idx", |
"index_id": "8d878de8a710611a", |
"index_projection": { |
"primary_key": true |
},
|
"keyspace": "beer-sample", |
"namespace": "default", |
"spans": [ |
{
|
"exact": true, |
"range": [ |
{
|
"inclusion": 0, |
"low": "null" |
}
|
]
|
}
|
],
|
"using": "gsi" |
},
|
{
|
"#operator": "IndexScan3", |
"as": "t1", |
"index": "idx_type", |
"index_id": "368dfb71316953b8", |
"index_projection": { |
"primary_key": true |
},
|
"keyspace": "beer-sample", |
"namespace": "default", |
"spans": [ |
{
|
"exact": true, |
"range": [ |
{
|
"high": "\"brewery\"", |
"inclusion": 3, |
"low": "\"brewery\"" |
}
|
]
|
}
|
],
|
"using": "gsi" |
},
|
{
|
"#operator": "IndexFtsSearch", |
"as": "t1", |
"index": "idx_beer_sample_fts", |
"index_id": "515dceb756d04fb8", |
"keyspace": "beer-sample", |
"namespace": "default", |
"search_info": { |
"field": "\"`state`\"", |
"outname": "out", |
"query": "\"state:California\"" |
},
|
"using": "fts" |
}
|
]
|
},
|
{
|
"#operator": "Fetch", |
"as": "t1", |
"keyspace": "beer-sample", |
"namespace": "default" |
},
|
{
|
"#operator": "Parallel", |
"~child": { |
"#operator": "Sequence", |
"~children": [ |
{
|
"#operator": "NestedLoopJoin", |
"alias": "t2", |
"on_clause": "((`t1`.`code`) = (`t2`.`brewery_id`))", |
"~child": { |
"#operator": "Sequence", |
"~children": [ |
{
|
"#operator": "IntersectScan", |
"scans": [ |
{
|
"#operator": "IndexScan3", |
"as": "t2", |
"index": "idx_brewery_id", |
"index_id": "9bbaf382a84cb584", |
"index_projection": { |
"primary_key": true |
},
|
"keyspace": "beer-sample", |
"namespace": "default", |
"nested_loop": true, |
"spans": [ |
{
|
"exact": true, |
"range": [ |
{
|
"high": "(`t1`.`code`)", |
"inclusion": 3, |
"low": "(`t1`.`code`)" |
}
|
]
|
}
|
],
|
"using": "gsi" |
},
|
{
|
"#operator": "IndexScan3", |
"as": "t2", |
"index": "idx_type", |
"index_id": "368dfb71316953b8", |
"index_projection": { |
"primary_key": true |
},
|
"keyspace": "beer-sample", |
"namespace": "default", |
"nested_loop": true, |
"spans": [ |
{
|
"exact": true, |
"range": [ |
{
|
"high": "\"beer\"", |
"inclusion": 3, |
"low": "\"beer\"" |
}
|
]
|
}
|
],
|
"using": "gsi" |
}
|
]
|
},
|
{
|
"#operator": "Fetch", |
"as": "t2", |
"keyspace": "beer-sample", |
"namespace": "default", |
"nested_loop": true |
}
|
]
|
}
|
},
|
{
|
"#operator": "Filter", |
"condition": "((((`t1`.`type`) = \"brewery\") and ((`t2`.`type`) = \"beer\")) and search((`t1`.`state`), \"state:California\"))" |
},
|
{
|
"#operator": "InitialProject", |
"result_terms": [ |
{
|
"expr": "(`t1`.`code`)" |
},
|
{
|
"expr": "(`t1`.`state`)" |
},
|
{
|
"expr": "(`t1`.`city`)" |
},
|
{
|
"expr": "(`t2`.`name`)" |
}
|
]
|
}
|
]
|
}
|
}
|
]
|
},
|
{
|
"#operator": "Order", |
"sort_terms": [ |
{
|
"expr": "(`t1`.`code`)" |
},
|
{
|
"expr": "(`t2`.`name`)" |
}
|
]
|
},
|
{
|
"#operator": "FinalProject" |
}
|
]
|
}
|
Logs are attached.