Major Description
create index ix10 on default (a,b,c,d); |
create index ix11 on default (x,y,z); |
select a,b,c from default where a = 5 UNION ALL select x,y,z from default WHERE x > 5 ORDER BY a; |
-- If we remove ORDER BY a, both queries are covered indipendently
|
-- With ORDER BY both are not covered
|
|
|
|
Work around
|
|
|
(select a,b,c from default where a = 5) UNION ALL (select x,y,z from default WHERE x > 5) ORDER BY a; |
The following index and query also generates wrong plan and result in wrong results. If use () it generates right plan.
USING UNIQUE ALIAS in whole query generates right plan
If you look second arm of UNION does Fetch but filter has covers which result in whole second arm results are thrown away.
|
|
CREATE INDEX ix21 ON default (cliCode, DISTINCT ARRAY v FOR v IN supIds END, stDate); |
|
|
EXPLAIN SELECT cl.stDate FROM `default` AS cl WHERE cl.cliCode = 2033 AND ANY v IN cl.supIds SATISFIES v = 1037172 END UNION ALL SELECT cl.stDate FROM `default` AS cl WHERE cl.cliCode = 2033 AND ANY v IN cl.supIds SATISFIES v = 1037172 END ORDER by stDate; |
{
|
"requestID": "b514caa0-91bf-4b10-9544-5a14c41f115a", |
"signature": "json", |
"results": [ |
{
|
"plan": { |
"#operator": "Sequence", |
"~children": [ |
{
|
"#operator": "UnionAll", |
"~children": [ |
{
|
"#operator": "Sequence", |
"~children": [ |
{
|
"#operator": "DistinctScan", |
"scan": { |
"#operator": "IndexScan3", |
"as": "cl", |
"covers": [ |
"cover ((`cl`.`cliCode`))", |
"cover ((distinct (array `v` for `v` in (`cl`.`supIds`) end)))", |
"cover ((`cl`.`stDate`))", |
"cover ((meta(`cl`).`id`))" |
],
|
"filter_covers": { |
"cover (any `v` in (`cl`.`supIds`) satisfies (`v` = 1037172) end)": true |
},
|
"index": "ix21", |
"index_id": "3850094af6d78fa0", |
"index_projection": { |
"entry_keys": [ |
0, |
2 |
],
|
"primary_key": true |
},
|
"keyspace": "default", |
"namespace": "default", |
"spans": [ |
{
|
"exact": true, |
"range": [ |
{
|
"high": "2033", |
"inclusion": 3, |
"low": "2033" |
},
|
{
|
"high": "1037172", |
"inclusion": 3, |
"low": "1037172" |
}
|
]
|
}
|
],
|
"using": "gsi" |
}
|
},
|
{
|
"#operator": "Parallel", |
"~child": { |
"#operator": "Sequence", |
"~children": [ |
{
|
"#operator": "Filter", |
"condition": "((cover ((`cl`.`cliCode`)) = 2033) and cover (any `v` in (`cl`.`supIds`) satisfies (`v` = 1037172) end))" |
},
|
{
|
"#operator": "InitialProject", |
"result_terms": [ |
{
|
"expr": "cover ((`cl`.`stDate`))" |
}
|
]
|
},
|
{
|
"#operator": "FinalProject" |
}
|
]
|
}
|
}
|
]
|
},
|
{
|
"#operator": "Sequence", |
"~children": [ |
{
|
"#operator": "DistinctScan", |
"scan": { |
"#operator": "IndexScan3", |
"as": "cl", |
"index": "ix21", |
"index_id": "3850094af6d78fa0", |
"index_projection": { |
"primary_key": true |
},
|
"keyspace": "default", |
"namespace": "default", |
"spans": [ |
{
|
"exact": true, |
"range": [ |
{
|
"high": "2033", |
"inclusion": 3, |
"low": "2033" |
},
|
{
|
"high": "1037172", |
"inclusion": 3, |
"low": "1037172" |
}
|
]
|
}
|
],
|
"using": "gsi" |
}
|
},
|
{
|
"#operator": "Fetch", |
"as": "cl", |
"keyspace": "default", |
"namespace": "default" |
},
|
{
|
"#operator": "Parallel", |
"~child": { |
"#operator": "Sequence", |
"~children": [ |
{
|
"#operator": "Filter", |
"condition": "((cover ((`cl`.`cliCode`)) = 2033) and cover (any `v` in (`cl`.`supIds`) satisfies (`v` = 1037172) end))" |
},
|
{
|
"#operator": "InitialProject", |
"result_terms": [ |
{
|
"expr": "cover ((`cl`.`stDate`))" |
}
|
]
|
},
|
{
|
"#operator": "FinalProject" |
}
|
]
|
}
|
}
|
]
|
}
|
]
|
},
|
{
|
"#operator": "Order", |
"sort_terms": [ |
{
|
"expr": "`stDate`" |
}
|
]
|
}
|
]
|
},
|
"text": "SELECT cl.stDate FROM `default` AS cl WHERE cl.cliCode = 2033 AND ANY v IN cl.supIds SATISFIES v = 1037172 END UNION ALL SELECT cl.stDate FROM `default` AS cl WHERE cl.cliCode = 2033 AND ANY v IN cl.supIds SATISFIES v = 1037172 END ORDER by stDate;" |
}
|
],
|
"status": "success", |
"metrics": { |
"elapsedTime": "2.679617ms", |
"executionTime": "2.657931ms", |
"resultCount": 1, |
"resultSize": 9267 |
}
|
}
|