Details
-
Bug
-
Resolution: Fixed
-
Major
-
5.0.0
-
5.0.0-2079
-
Triaged
-
Unknown
Description
Query issued with 3 OR clauses uses primary index.
explain SELECT k01 FROM default where k01 > "abc" OR k02 > 123 or k03>10; |
|
Indexes present:
CREATE INDEX `k1` ON `default`(`k01`); |
CREATE INDEX `k2` ON `default`(`k02`); |
CREATE INDEX `k3` ON `default`(`k03`); |
Result of above query:
[
|
{
|
"code": 4000, |
"msg": "No index available on keyspace default that matches your query. Use CREATE INDEX or CREATE PRIMARY INDEX to create an index, or check that your expected index is online.", |
"query_from_user": "explain SELECT k01 FROM default where k01 > \"abc\" OR k02 > 123 or k03>10;" |
}
|
]
|
Query issued with 2 OR clauses uses the right indexes with UnionScan:
explain SELECT k01 FROM default where k01 > "abc" OR k02 > 123;
[
|
{
|
"plan": { |
"#operator": "Sequence", |
"~children": [ |
{
|
"#operator": "UnionScan", |
"scans": [ |
{
|
"#operator": "IndexScan2", |
"index": "k1", |
"index_id": "4cc6eef9a0eea52d", |
"index_projection": { |
"primary_key": true |
},
|
"keyspace": "default", |
"namespace": "default", |
"spans": [ |
{
|
"exact": true, |
"range": [ |
{
|
"inclusion": 0, |
"low": "\"abc\"" |
}
|
]
|
}
|
],
|
"using": "gsi" |
},
|
{
|
"#operator": "IndexScan2", |
"index": "k2", |
"index_id": "c28c0476c56efeb", |
"index_projection": { |
"primary_key": true |
},
|
"keyspace": "default", |
"namespace": "default", |
"spans": [ |
{
|
"exact": true, |
"range": [ |
{
|
"inclusion": 0, |
"low": "123" |
}
|
]
|
}
|
],
|
"using": "gsi" |
}
|
]
|
},
|
{
|
"#operator": "Fetch", |
"keyspace": "default", |
"namespace": "default" |
},
|
{
|
"#operator": "Parallel", |
"~child": { |
"#operator": "Sequence", |
"~children": [ |
{
|
"#operator": "Filter", |
"condition": "((\"abc\" < (`default`.`k01`)) or (123 < (`default`.`k02`)))" |
},
|
{
|
"#operator": "InitialProject", |
"result_terms": [ |
{
|
"expr": "(`default`.`k01`)" |
}
|
]
|
},
|
{
|
"#operator": "FinalProject" |
}
|
]
|
}
|
}
|
]
|
},
|
"text": "SELECT k01 FROM default where k01 > \"abc\" OR k02 > 123;" |
}
|
]
|
Attachments
Issue Links
- blocks
-
MB-22188 4.6.2 Minor Release
-
- Closed
-
| For Gerrit Dashboard: MB-22994 | ||||||
|---|---|---|---|---|---|---|
| # | Subject | Branch | Project | Status | CR | V |
| 74730,2 | MB-22994. flatten OR predicate before doing Orscan | master | query | Status: MERGED | +2 | +1 |
| 75177,2 | MB-22994. flatten OR predicate before doing Orscan | watson | query | Status: MERGED | +2 | +1 |
| 76584,1 | MB-22994: Test for Query with predicates on 3 different fields with OR clause doesnt use UnionScan MB-22143: Test for Index scans for OR predicates on disjoint terms | master | testrunner | Status: ABANDONED | 0 | 0 |
| 76585,2 | MB-22994: Test for Query with predicates on 3 different fields with OR clause doesnt use UnionScan MB-22143: Test for Index scans for OR predicates on disjoint terms | watson | testrunner | Status: MERGED | +2 | +1 |
| 76586,2 | MB-22994: Test for Query with predicates on 3 different fields with OR clause doesnt use UnionScan MB-22143: Test for Index scans for OR predicates on disjoint terms | master | testrunner | Status: MERGED | +2 | +1 |