Details
-
Bug
-
Resolution: Fixed
-
Major
-
7.6.0, 7.6.2, 7.6.1
-
Untriaged
-
0
-
Unknown
Description
Repro Steps:
1.
insert into coll1 ( key k, value v ) select to_string(a) as k, { "id": a, "test":a+100 } as v from array_range(1,4) a; |
2.
create index i_coll1_id on coll1(id);
|
3.
select * from coll1 where id is not missing order by id, name offset 1; |
Results:
These results are wrong - the OFFSET clause has not come into effect.
[
|
{
|
"coll1": {
|
"id": 1,
|
"test": 101
|
}
|
},
|
{
|
"coll1": {
|
"id": 2,
|
"test": 102
|
}
|
},
|
{
|
"coll1": {
|
"id": 3,
|
"test": 103
|
}
|
}
|
]
|
As seen in the query plan below, a Partial Sort Order by operator is created, but the Offset in this case is not handled by it.
This is because since there is no LIMIT clause, the offset is not embedded in the Order by operator:
But an explicit Offset operator is not created either: https://github.com/couchbase/query/blob/70b6b24b400bb065e2a20ab7be47eb7541bdace8/planner/build_select.go#L189
Hence the OFFSET clause is never applied on the ordered results.
For this case should the Offset be handled in the Partial Sort Order operator? Or should an explicit Offset operator be created?
EXPLAIN's query plan:
[
|
{
|
"plan": { |
"#operator": "Sequence", |
"~children": [ |
{
|
"#operator": "Sequence", |
"~children": [ |
{
|
"#operator": "IndexScan3", |
"bucket": "bucket1", |
"index": "i_coll1_id", |
"index_id": "e796e927c5711553", |
"index_order": [ |
{
|
"keypos": 0 |
}
|
],
|
"index_projection": { |
"primary_key": true |
},
|
"keyspace": "coll1", |
"namespace": "default", |
"scope": "scope1", |
"spans": [ |
{
|
"exact": true, |
"range": [ |
{
|
"inclusion": 1, |
"index_key": "`id`", |
"low": "null" |
}
|
]
|
}
|
],
|
"using": "gsi" |
},
|
{
|
"#operator": "Fetch", |
"bucket": "bucket1", |
"keyspace": "coll1", |
"namespace": "default", |
"scope": "scope1" |
},
|
{
|
"#operator": "Parallel", |
"maxParallelism": 1, |
"~child": { |
"#operator": "Sequence", |
"~children": [ |
{
|
"#operator": "Filter", |
"condition": "((`coll1`.`id`) is not missing)" |
},
|
{
|
"#operator": "InitialProject", |
"preserve_order": true, |
"result_terms": [ |
{
|
"expr": "self", |
"star": true |
}
|
]
|
}
|
]
|
}
|
}
|
]
|
},
|
{
|
"#operator": "Order", |
"flags": 6, |
"partial_sort_term_count": 1, |
"sort_terms": [ |
{
|
"expr": "(`coll1`.`id`)" |
},
|
{
|
"expr": "(`coll1`.`name`)" |
}
|
]
|
}
|
]
|
},
|
"text": "select * from coll1 where id is not missing order by id, name offset 1;" |
}
|
]
|
Attachments
Issue Links
- relates to
-
MB-33961 order-by optimization via max-heap popping instead of final sort
-
- Closed
-
| For Gerrit Dashboard: MB-61760 | ||||||
|---|---|---|---|---|---|---|
| # | Subject | Branch | Project | Status | CR | V |
| 209826,2 | MB-61760 Correct generation of offset operator | trinity | query | Status: MERGED | +2 | +1 |
| 210064,2 | MB-61760 [BP 33961] ORDER BY optimisation | trinity | query | Status: MERGED | +2 | +1 |