Description
DROP INDEX default.ix11; |
DROP INDEX default.ix12; |
CREATE INDEX ix11 ON `default` ( ALL ARRAY item.n FOR item IN items WHEN item.type = "al" END) WHERE type = "ll"; |
CREATE INDEX ix12 ON `default` ( l , n, v) WHERE type = "rr"; |
EXPLAIN SELECT f.*
|
FROM ( SELECT item.n, "" AS v |
FROM default AS o |
UNNEST o.items AS item
|
WHERE item.n = "" AND o.type = "ll" AND item.type = "al" ) AS f |
JOIN default AS c |
ON f.n = c.n AND f.v = c.v AND c.type = "rr" AND c.l = "l"; |
EXPLAIN SELECT f.* FROM ( SELECT item.n, "" AS v FROM default AS o UNNEST o.items AS item WHERE item.n = "" AND o.type = "ll" AND item.type = "al" ) AS f JOIN default AS c ON f.n = c.n AND f.v = c.v AND c.type = "rr" AND c.l = "l"; |
{
|
"requestID": "f8abaafd-6134-4ee0-9b5a-bffbb3f8c6b6", |
"signature": "json", |
"results": [ |
{
|
"plan": { |
"#operator": "Sequence", |
"~children": [ |
{
|
"#operator": "Sequence", |
"~children": [ |
{
|
"#operator": "IndexScan3", |
"as": "o", |
"covers": [ |
"cover ((`item`.`n`))", |
"cover ((meta(`o`).`id`))" |
],
|
"filter": "((cover ((`o`.`type`)) = \"ll\") and cover (is_array((`o`.`items`))))", |
"filter_covers": { |
"cover (((`o`.`items`) \u003c {}))": true, |
"cover (([] \u003c= (`o`.`items`)))": true, |
"cover ((`item`.`type`))": "al", |
"cover ((`o`.`type`))": "ll", |
"cover (is_array((`o`.`items`)))": true |
},
|
"index": "ix11", |
"index_id": "c823ac0cd3e2e627", |
"keyspace": "default", |
"namespace": "default", |
"spans": [ |
{
|
"exact": true, |
"range": [ |
{
|
"high": "\"\"", |
"inclusion": 3, |
"index_key": "(all (array (`item`.`n`) for `item` in `items` when ((`item`.`type`) = \"al\") end))", |
"low": "\"\"" |
}
|
]
|
}
|
],
|
"using": "gsi" |
},
|
{
|
"#operator": "Parallel", |
"~child": { |
"#operator": "Sequence", |
"~children": [ |
{
|
"#operator": "Filter", |
"condition": "((cover ((`item`.`n`)) = \"\") and (cover ((`item`.`type`)) = \"al\"))" |
},
|
{
|
"#operator": "InitialProject", |
"result_terms": [ |
{
|
"expr": "cover ((`item`.`n`))" |
},
|
{
|
"as": "v", |
"expr": "\"\"" |
}
|
]
|
}
|
]
|
}
|
}
|
]
|
},
|
{
|
"#operator": "Alias", |
"as": "f" |
},
|
{
|
"#operator": "Parallel", |
"~child": { |
"#operator": "Sequence", |
"~children": [ |
{
|
"#operator": "NestedLoopJoin", |
"alias": "c", |
"on_clause": "(((((`f`.`n`) = (`c`.`n`)) and ((`f`.`v`) = (`c`.`v`))) and ((`c`.`type`) = \"rr\")) and ((`c`.`l`) = \"l\"))", |
"~child": { |
"#operator": "Sequence", |
"~children": [ |
{
|
"#operator": "IndexScan3", |
"as": "c", |
"index": "ix12", |
"index_id": "543c3714c85d3138", |
"index_projection": { |
"primary_key": true |
},
|
"keyspace": "default", |
"namespace": "default", |
"nested_loop": true, |
"spans": [ |
{
|
"exact": true, |
"range": [ |
{
|
"high": "\"l\"", |
"inclusion": 3, |
"index_key": "`l`", |
"low": "\"l\"" |
},
|
{
|
"high": "(`f`.`n`)", |
"inclusion": 3, |
"index_key": "`n`", |
"low": "(`f`.`n`)" |
},
|
{
|
"high": "(`f`.`v`)", |
"inclusion": 3, |
"index_key": "`v`", |
"low": "(`f`.`v`)" |
}
|
]
|
}
|
],
|
"using": "gsi" |
},
|
{
|
"#operator": "Fetch", |
"as": "c", |
"keyspace": "default", |
"namespace": "default", |
"nested_loop": true |
},
|
{
|
"#operator": "Parallel", |
"~child": { |
"#operator": "Sequence", |
"~children": [ |
{
|
"#operator": "Filter", |
"condition": "(((`c`.`type`) = \"rr\") and ((`c`.`l`) = \"l\"))" |
}
|
]
|
}
|
}
|
]
|
}
|
},
|
{
|
"#operator": "InitialProject", |
"result_terms": [ |
{
|
"expr": "`f`", |
"star": true |
}
|
]
|
}
|
]
|
}
|
}
|
]
|
},
|
"text": "SELECT f.* FROM ( SELECT item.n, \"\" AS v FROM default AS o UNNEST o.items AS item WHERE item.n = \"\" AND o.type = \"ll\" AND item.type = \"al\" ) AS f JOIN default AS c ON f.n = c.n AND f.v = c.v AND c.type = \"rr\" AND c.l = \"l\";" |
}
|
],
|
"status": "success", |
"metrics": { |
"elapsedTime": "9.399934ms", |
"executionTime": "9.300051ms", |
"resultCount": 1, |
"resultSize": 8447, |
"serviceLoad": 2 |
}
|
}
|
Why right side of JOIN (c) is not covered?
If we remove item.type = "al" from index and query removed it covers. Interesting thing is FROM subquery where index is used is covered not sure why that impacting outside query.
Attachments
Issue Links
- backports to
-
MB-51581 [BP to 7.0.4] - JOIN query not covered
-
- Closed
-