Details
-
Bug
-
Resolution: Fixed
-
Critical
-
7.6.2
-
7.6.2-3688
-
Untriaged
-
0
-
Unknown
Description
|
upsert into default values("k0",
|
{"f1":1,"f2":2,"f3":3}
|
),values("k1",
|
{"f1":2,"f2":2,"f3":0}
|
),values("k2",
|
{"f1":1,"f2":2,"f3":3,"string":"abcd"}
|
),values("k3",
|
{"f1":0,"f2":2,"f3":3}
|
),values("k4",
|
{"f1":1,"f2":2,"f3":3,"string":"abcde"}
|
),values("k5",
|
{"f1":1,"f2":2,"f3":3,"string":"cdesf"}
|
),values("k6",
|
{"f1":0,"f2":2,"f3":3,"string":"abcd"}
|
),values("k7",
|
{"f1":1,"f2":2,"f3":3,"string":"defsr"}
|
)
|
create index ixto on default(f1,f2,`string`);
|
create primary index on default;
|
select * from default where f1 < 10 and f2 = 2 and lower(`string`) in ['abcd','abcde','cdesf'] order by f1,meta().id desc limit 2 offset 1; is giving the same results as
|
select * from default where f1 < 10 and f2 = 2 and lower(`string`) in ['abcd','abcde','cdesf'] order by f1,meta().id desc limit 2;
|
which should not be the case as the first query contains an offset clause and the second query does not
both return
[
{
"default":
},
{
"default":
}
]
if we run the same two query with use primary, we see the correct behavior
select * from default use index(`#primary`) where f1 < 10 and f2 = 2 and lower(`string`) in ['abcd','abcde','cdesf'] order by f1,meta().id desc limit 2 <--- no offset
[
{
"default":
},
{
"default":
}
]
select * from default use index(`#primary`) where f1 < 10 and f2 = 2 and lower(`string`) in ['abcd','abcde','cdesf'] order by f1,meta().id desc limit 2 offset 1 <--- contains offset
[
{
"default":
},
{
"default":
}
]
explain query #1 (with offset)
{
|
"plan": {
|
"#operator": "Sequence",
|
"~children": [
|
{
|
"#operator": "Sequence",
|
"~children": [
|
{
|
"#operator": "IndexScan3",
|
"filter": "(lower(_index_key ((`default`.`string`))) in [\"abcd\", \"abcde\", \"cdesf\"])",
|
"index": "ixto",
|
"index_id": "b66736eb13430cdd",
|
"index_keys": [
|
"_index_key ((`default`.`f1`))",
|
"_index_key ((`default`.`string`))",
|
"_index_key ((meta(`default`).`id`))"
|
],
|
"index_order": [
|
{
|
"keypos": 0
|
}
|
],
|
"index_projection": {
|
"entry_keys": [
|
0,
|
2
|
],
|
"primary_key": true
|
},
|
"keyspace": "default",
|
"namespace": "default",
|
"optimizer_estimates": {
|
"cardinality": 4.3308632917652044e-10,
|
"cost": 0.00003174292694902935,
|
"fr_cost": 0.00003174292694902935,
|
"size": 61
|
},
|
"spans": [
|
{
|
"range": [
|
{
|
"high": "10",
|
"inclusion": 0,
|
"index_key": "`f1`",
|
"low": "null"
|
},
|
{
|
"high": "2",
|
"inclusion": 3,
|
"index_key": "`f2`",
|
"low": "2"
|
},
|
{
|
"inclusion": 0,
|
"index_key": "`string`",
|
"low": "null"
|
}
|
]
|
}
|
],
|
"using": "gsi"
|
},
|
{
|
"#operator": "Order",
|
"flags": 7,
|
"optimizer_estimates": {
|
"cardinality": 4.3308632917652044e-10,
|
"cost": 0.00003174292694902935,
|
"fr_cost": 0.00003174292694902935,
|
"size": 61
|
},
|
"partial_sort_term_count": 1,
|
"sort_terms": [
|
{
|
"expr": "_index_key ((`default`.`f1`))"
|
},
|
{
|
"desc": "\"desc\"",
|
"expr": "_index_key ((meta(`default`).`id`))"
|
}
|
]
|
},
|
{
|
"#operator": "Fetch",
|
"keyspace": "default",
|
"namespace": "default",
|
"optimizer_estimates": {
|
"cardinality": 4.3308632917652044e-10,
|
"cost": 0.00003174856882177627,
|
"fr_cost": 0.00003174856882177627,
|
"size": 422
|
}
|
},
|
{
|
"#operator": "Parallel",
|
"maxParallelism": 1,
|
"~child": {
|
"#operator": "Sequence",
|
"~children": [
|
{
|
"#operator": "Filter",
|
"condition": "((((`default`.`f1`) < 10) and ((`default`.`f2`) = 2)) and (lower((`default`.`string`)) in [\"abcd\", \"abcde\", \"cdesf\"]))",
|
"optimizer_estimates": {
|
"cardinality": 4.3308632917652044e-10,
|
"cost": 0.000031748577718512204,
|
"fr_cost": 0.000031748577718512204,
|
"size": 422
|
}
|
},
|
{
|
"#operator": "InitialProject",
|
"optimizer_estimates": {
|
"cardinality": 4.3308632917652044e-10,
|
"cost": 0.00003174858661524814,
|
"fr_cost": 0.00003174858661524814,
|
"size": 422
|
},
|
"preserve_order": true,
|
"result_terms": [
|
{
|
"expr": "self",
|
"star": true
|
}
|
]
|
}
|
]
|
}
|
}
|
]
|
},
|
{
|
"#operator": "Limit",
|
"expr": "2",
|
"optimizer_estimates": {
|
"cardinality": 4.3308632917652044e-10,
|
"cost": 0.020574387170789386,
|
"fr_cost": 0.020574387170789386,
|
"size": 422
|
}
|
}
|
]
|
}
|
}
|
explain query #2(no offset)
{
|
"plan": {
|
"#operator": "Sequence",
|
"~children": [
|
{
|
"#operator": "Sequence",
|
"~children": [
|
{
|
"#operator": "IndexScan3",
|
"filter": "(lower(_index_key ((`default`.`string`))) in [\"abcd\", \"abcde\", \"cdesf\"])",
|
"index": "ixto",
|
"index_id": "b66736eb13430cdd",
|
"index_keys": [
|
"_index_key ((`default`.`f1`))",
|
"_index_key ((`default`.`string`))",
|
"_index_key ((meta(`default`).`id`))"
|
],
|
"index_order": [
|
{
|
"keypos": 0
|
}
|
],
|
"index_projection": {
|
"entry_keys": [
|
0,
|
2
|
],
|
"primary_key": true
|
},
|
"keyspace": "default",
|
"namespace": "default",
|
"optimizer_estimates": {
|
"cardinality": 4.3308632917652044e-10,
|
"cost": 0.00003174292694902935,
|
"fr_cost": 0.00003174292694902935,
|
"size": 61
|
},
|
"spans": [
|
{
|
"range": [
|
{
|
"high": "10",
|
"inclusion": 0,
|
"index_key": "`f1`",
|
"low": "null"
|
},
|
{
|
"high": "2",
|
"inclusion": 3,
|
"index_key": "`f2`",
|
"low": "2"
|
},
|
{
|
"inclusion": 0,
|
"index_key": "`string`",
|
"low": "null"
|
}
|
]
|
}
|
],
|
"using": "gsi"
|
},
|
{
|
"#operator": "Order",
|
"flags": 7,
|
"optimizer_estimates": {
|
"cardinality": 4.3308632917652044e-10,
|
"cost": 0.00003174292694902935,
|
"fr_cost": 0.00003174292694902935,
|
"size": 61
|
},
|
"partial_sort_term_count": 1,
|
"sort_terms": [
|
{
|
"expr": "_index_key ((`default`.`f1`))"
|
},
|
{
|
"desc": "\"desc\"",
|
"expr": "_index_key ((meta(`default`).`id`))"
|
}
|
]
|
},
|
{
|
"#operator": "Fetch",
|
"keyspace": "default",
|
"namespace": "default",
|
"optimizer_estimates": {
|
"cardinality": 4.3308632917652044e-10,
|
"cost": 0.00003174856882177627,
|
"fr_cost": 0.00003174856882177627,
|
"size": 422
|
}
|
},
|
{
|
"#operator": "Parallel",
|
"maxParallelism": 1,
|
"~child": {
|
"#operator": "Sequence",
|
"~children": [
|
{
|
"#operator": "Filter",
|
"condition": "((((`default`.`f1`) < 10) and ((`default`.`f2`) = 2)) and (lower((`default`.`string`)) in [\"abcd\", \"abcde\", \"cdesf\"]))",
|
"optimizer_estimates": {
|
"cardinality": 4.3308632917652044e-10,
|
"cost": 0.000031748577718512204,
|
"fr_cost": 0.000031748577718512204,
|
"size": 422
|
}
|
},
|
{
|
"#operator": "InitialProject",
|
"optimizer_estimates": {
|
"cardinality": 4.3308632917652044e-10,
|
"cost": 0.00003174858661524814,
|
"fr_cost": 0.00003174858661524814,
|
"size": 422
|
},
|
"preserve_order": true,
|
"result_terms": [
|
{
|
"expr": "self",
|
"star": true
|
}
|
]
|
}
|
]
|
}
|
}
|
]
|
},
|
{
|
"#operator": "Limit",
|
"expr": "2",
|
"optimizer_estimates": {
|
"cardinality": 4.3308632917652044e-10,
|
"cost": 0.020574387170789386,
|
"fr_cost": 0.020574387170789386,
|
"size": 422
|
}
|
}
|
]
|
}
|
}
|