Details
-
Bug
-
Resolution: Fixed
-
Major
-
7.6.0, 7.1.0, 7.2.0
-
Untriaged
-
0
-
Unknown
Description
Affect versions are : 7.1.x, 7.2.x, 7.6.x until fixed
UPSERT INTO default VALUES("doc2", {"contacts":[{"type":"mobile", "phone":"123-45-6789"}, |
{"type":"email","id":"abc@gmail.com"}]}); |
CREATE INDEX ix1 ON default(DISTINCT ARRAY FLATTEN_KEYS(v1.type,v1.phone) FOR v1 IN contacts END);
|
EXPLAIN SELECT META(d).id
|
FROM default AS d
|
WHERE ANY v1 IN contacts SATISFIES v1.type ="mobile" AND v1.phone = "123-45-6789" END
|
AND ANY v2 IN contacts SATISFIES v2.type ="email" AND v2.id = "abc@gmail.com" END;
|
Above query uses multiple ANY clauses (2) Same index is sargable both of them.
As it is AND and ARRAY index we need to do IntersectScan.
One ANY clause srgable 2 keys, other sargable 1 key
While generate spans we look whole predicate vs individual ANY this causes 1 sargable key inherit span from other which can cause index eliminate false negatives.
In below example second scan (email) should not have phone number.
Depends on which scan succeed (due to early termination) some times query gives results, some times not
EXPLAIN SELECT META(d).id FROM default AS d WHERE ANY v1 IN contacts SATISFIES v1.type ="mobile" AND v1.phone = "123-45-6789" END AND ANY v2 IN contacts SATISFIES v2.type ="email" AND v2.id = "abc@gmail.com" END;
|
{
|
"requestID": "a6013074-bb2b-47b8-96bd-069915619ae3",
|
"signature": "json",
|
"results": [
|
{
|
"plan": {
|
"#operator": "Sequence",
|
"~children": [
|
{
|
"#operator": "IntersectScan",
|
"scans": [
|
{
|
"#operator": "DistinctScan",
|
"scan": {
|
"#operator": "IndexScan3",
|
"as": "d",
|
"index": "ix1",
|
"index_id": "9b9a99e9bd09626c",
|
"index_projection": {
|
"primary_key": true
|
},
|
"keyspace": "default",
|
"namespace": "default",
|
"spans": [
|
{
|
"exact": true,
|
"range": [
|
{
|
"high": "\"mobile\"",
|
"inclusion": 3,
|
"index_key": "(`v1`.`type`)",
|
"low": "\"mobile\""
|
},
|
{
|
"high": "\"123-45-6789\"",
|
"inclusion": 3,
|
"index_key": "(`v1`.`phone`)",
|
"low": "\"123-45-6789\""
|
}
|
]
|
}
|
],
|
"using": "gsi"
|
}
|
},
|
{
|
"#operator": "DistinctScan",
|
"scan": {
|
"#operator": "IndexScan3",
|
"as": "d",
|
"index": "ix1",
|
"index_id": "9b9a99e9bd09626c",
|
"index_projection": {
|
"primary_key": true
|
},
|
"keyspace": "default",
|
"namespace": "default",
|
"spans": [
|
{
|
"exact": true,
|
"range": [
|
{
|
"high": "\"email\"",
|
"inclusion": 3,
|
"index_key": "(`v1`.`type`)",
|
"low": "\"email\""
|
},
|
{
|
"high": "\"123-45-6789\"",
|
"inclusion": 3,
|
"index_key": "(`v1`.`phone`)",
|
"low": "\"123-45-6789\""
|
}
|
]
|
}
|
],
|
"using": "gsi"
|
}
|
}
|
]
|
},
|
{
|
"#operator": "Fetch",
|
"as": "d",
|
"early_projection": [
|
"contacts"
|
],
|
"keyspace": "default",
|
"namespace": "default"
|
},
|
{
|
"#operator": "Parallel",
|
"~child": {
|
"#operator": "Sequence",
|
"~children": [
|
{
|
"#operator": "Filter",
|
"condition": "(any `v1` in (`d`.`contacts`) satisfies (((`v1`.`type`) = \"mobile\") and ((`v1`.`phone`) = \"123-45-6789\")) end and any `v2` in (`d`.`contacts`) satisfies (((`v2`.`type`) = \"email\") and ((`v2`.`id`) = \"abc@gmail.com\")) end)"
|
},
|
{
|
"#operator": "InitialProject",
|
"discard_original": true,
|
"preserve_order": true,
|
"result_terms": [
|
{
|
"expr": "(meta(`d`).`id`)"
|
}
|
]
|
}
|
]
|
}
|
}
|
]
|
},
|
"text": "SELECT META(d).id FROM default AS d WHERE ANY v1 IN contacts SATISFIES v1.type =\"mobile\" AND v1.phone = \"123-45-6789\" END AND ANY v2 IN contacts SATISFIES v2.type =\"email\" AND v2.id = \"abc@gmail.com\" END;"
|
}
|
],
|
"status": "success",
|
"metrics": {
|
"elapsedTime": "3.580625ms",
|
"executionTime": "3.438125ms",
|
"resultCount": 1,
|
"resultSize": 5390,
|
"serviceLoad": 2
|
}
|
}
|
cc Bingjie Miao