Details
-
Bug
-
Resolution: Fixed
-
Major
-
7.1.0
-
7.1.0-1858
-
Untriaged
-
1
-
Yes
Description
similar to MB-48790
CREATE INDEX idxVM2 ON default( aLL ARRAY x.RAM FOR x within tokens(VMs) END,VMs) USING GSI
explain SELECT x from default emp1 USE INDEX(idxVM2) UNNEST tokens(emp1.VMs) as x JOIN default task ON KEYS meta(`emp1`).id where x.RAM > 1 and x.RAM < 5; |
|
[
|
{
|
"optimizer_hints": { |
"hints_followed": [ |
"INDEX(emp1 idxVM2)" |
]
|
},
|
"plan": { |
"#operator": "Sequence", |
"~children": [ |
{
|
"#operator": "DistinctScan", |
"scan": { |
"#operator": "IndexScan3", |
"as": "emp1", |
"index": "idxVM2", |
"index_id": "7c9b4711ae1758d1", |
"index_projection": { |
"primary_key": true |
},
|
"keyspace": "default", |
"namespace": "default", |
"spans": [ |
{
|
"range": [ |
{
|
"high": "5", |
"inclusion": 0, |
"index_key": "(all (array (`x`.`RAM`) for `x` within tokens(`VMs`) end))", |
"low": "1" |
},
|
{
|
"inclusion": 0, |
"index_key": "`VMs`", |
"low": "null" |
}
|
]
|
}
|
],
|
"using": "gsi" |
}
|
},
|
{
|
"#operator": "Fetch", |
"as": "emp1", |
"keyspace": "default", |
"namespace": "default" |
},
|
{
|
"#operator": "Parallel", |
"~child": { |
"#operator": "Sequence", |
"~children": [ |
{
|
"#operator": "Filter", |
"condition": "is_array(tokens((`emp1`.`VMs`)))" |
},
|
{
|
"#operator": "Unnest", |
"as": "x", |
"expr": "tokens((`emp1`.`VMs`))", |
"filter": "((1 < (`x`.`RAM`)) and ((`x`.`RAM`) < 5) and (`x` is not missing))" |
}
|
]
|
}
|
},
|
{
|
"#operator": "Join", |
"as": "task", |
"keyspace": "default", |
"namespace": "default", |
"on_keys": "(meta(`emp1`).`id`)" |
},
|
{
|
"#operator": "Parallel", |
"~child": { |
"#operator": "Sequence", |
"~children": [ |
{
|
"#operator": "InitialProject", |
"result_terms": [ |
{
|
"expr": "`x`" |
}
|
]
|
}
|
]
|
}
|
}
|
]
|
},
|
"text": "SELECT x\nFROM default emp1 USE INDEX(idxVM2)\nUNNEST TOKENS(emp1.VMs) AS x\n JOIN default task ON KEYS META(`emp1`).id\nWHERE x.RAM > 1\n AND x.RAM < 5;" |
}
|
In 1325 (I know it was a long time ago MB-48790 was masking this)
EXPLAIN SELECT x
|
FROM default emp1 USE INDEX(idxVM2) |
UNNEST TOKENS(emp1.VMs) AS x
|
JOIN default task ON KEYS META(`emp1`).id |
WHERE x.RAM > 1 |
AND x.RAM < 5 |
[
|
{
|
"plan": { |
"#operator": "Sequence", |
"~children": [ |
{
|
"#operator": "DistinctScan", |
"scan": { |
"#operator": "IndexScan3", |
"as": "emp1", |
"covers": [ |
"cover ((all (array (`x`.`RAM`) for `x` within tokens((`emp1`.`VMs`)) end)))", |
"cover ((`emp1`.`VMs`))", |
"cover ((meta(`emp1`).`id`))" |
],
|
"filter": "is_array(tokens(cover ((`emp1`.`VMs`))))", |
"index": "idxVM2", |
"index_id": "dc5723f7fc2acda4", |
"index_projection": { |
"entry_keys": [ |
1 |
],
|
"primary_key": true |
},
|
"keyspace": "default", |
"namespace": "default", |
"spans": [ |
{
|
"range": [ |
{
|
"high": "5", |
"inclusion": 0, |
"index_key": "(all (array (`x`.`RAM`) for `x` within tokens(`VMs`) end))", |
"low": "1" |
},
|
{
|
"inclusion": 0, |
"index_key": "`VMs`", |
"low": "null" |
}
|
]
|
}
|
],
|
"using": "gsi" |
}
|
},
|
{
|
"#operator": "Parallel", |
"~child": { |
"#operator": "Sequence", |
"~children": [ |
{
|
"#operator": "Unnest", |
"as": "x", |
"expr": "tokens(cover ((`emp1`.`VMs`)))", |
"filter": "((1 < (`x`.`RAM`)) and ((`x`.`RAM`) < 5) and (`x` is not missing))" |
}
|
]
|
}
|
},
|
{
|
"#operator": "Join", |
"as": "task", |
"keyspace": "default", |
"namespace": "default", |
"on_keys": "cover ((meta(`emp1`).`id`))" |
},
|
{
|
"#operator": "Parallel", |
"~child": { |
"#operator": "Sequence", |
"~children": [ |
{
|
"#operator": "InitialProject", |
"result_terms": [ |
{
|
"expr": "`x`" |
}
|
]
|
}
|
]
|
}
|
}
|
]
|
},
|
"text": "SELECT x\nFROM default emp1 USE INDEX(idxVM2)\nUNNEST TOKENS(emp1.VMs) AS x\n JOIN default task ON KEYS META(`emp1`).id\nWHERE x.RAM > 1\n AND x.RAM < 5" |
}
|
]
|