Details
-
Bug
-
Resolution: Fixed
-
Major
-
7.6.2
-
Untriaged
-
0
-
Unknown
Description
INSERT INTO default VALUES("doc1", {"c1": 10, "a1": [ "500", "656", "700" ]}),
|
VALUES("doc2", {"c1": 25, "a1": [ "50", "60", "75" ]}),
|
VALUES("doc3", {"c1": 5, "a1": [ "11740", "12540", "15638" ]}),
|
VALUES("doc4", {"c1": 12, "a1": [ "692", "503", "180" ]}),
|
VALUES("doc5", {"a1": [ "55", "30", "66" ]});
|
|
|
DROP index default.ix50;
|
create index ix50 on default (DISTINCT ARRAY k FOR k IN a1 END, c1 DESC);
|
|
|
EXPLAIN SELECT t.id
|
FROM [ "656", "55","11740"] AS d
|
JOIN (SELECT meta().id, o.c1
|
FROM default AS o
|
WHERE ANY k IN o.a1 SATISFIES k = d END
|
ORDER BY o.c1 DESC
|
LIMIT 50) AS t ON 1 = 1
|
ORDER BY t.c1 DESC
|
LIMIT 50;
|
cbq> EXPLAIN SELECT t.id FROM [ "656", "55","11740"] AS d JOIN (SELECT meta().id, o.c1 FROM default AS o WHERE ANY k IN o.a1 SATISFIES k = d END ORDER BY o.c1 DESC LIMIT 50) AS t ON 1 = 1 ORDER BY t.c1 DESC LIMIT 50;
|
{
|
"requestID": "5010b463-2f66-43cd-ad43-a308b83936bd",
|
"signature": "json",
|
"results": [
|
{
|
"plan": {
|
"#operator": "Sequence",
|
"~children": [
|
{
|
"#operator": "Sequence",
|
"~children": [
|
{
|
"#operator": "ExpressionScan",
|
"alias": "d",
|
"expr": "[\"656\", \"55\", \"11740\"]",
|
"optimizer_estimates": {
|
"cardinality": 3,
|
"cost": 0.024,
|
"fr_cost": 0.008,
|
"size": 64
|
},
|
"uncorrelated": true
|
},
|
{
|
"#operator": "Parallel",
|
"~child": {
|
"#operator": "Sequence",
|
"~children": [
|
{
|
"#operator": "NestedLoopJoin",
|
"alias": "t",
|
"on_clause": "(1 = 1)",
|
"~child": {
|
"#operator": "Sequence",
|
"~children": [
|
{
|
"#operator": "ExpressionScan",
|
"alias": "t",
|
"expr": "correlated (select cover ((meta(`o`).`id`)), cover ((`o`.`c1`)) from `default`:`default` as `o` where cover (any `k` in (`o`.`a1`) satisfies (`k` = `d`) end) ORDER BY cover ((`o`.`c1`)) DESC limit 50)",
|
"nested_loop": true
|
}
|
]
|
}
|
},
|
{
|
"#operator": "InitialProject",
|
"preserve_order": true,
|
"result_terms": [
|
{
|
"expr": "(`t`.`id`)"
|
}
|
]
|
}
|
]
|
}
|
}
|
]
|
},
|
{
|
"#operator": "Order",
|
"flags": 6,
|
"limit": "50",
|
"sort_terms": [
|
{
|
"desc": "\"desc\"",
|
"expr": "(`t`.`c1`)"
|
}
|
]
|
},
|
{
|
"#operator": "Limit",
|
"expr": "50"
|
}
|
]
|
},
|
"text": "SELECT t.id FROM [ \"656\", \"55\",\"11740\"] AS d JOIN (SELECT meta().id, o.c1 FROM default AS o WHERE ANY k IN o.a1 SATISFIES k = d END ORDER BY o.c1 DESC LIMIT 50) AS t ON 1 = 1 ORDER BY t.c1 DESC LIMIT 50;",
|
"~subqueries": [
|
{
|
"correlated": true,
|
"plan": {
|
"#operator": "Sequence",
|
"~children": [
|
{
|
"#operator": "Sequence",
|
"~children": [
|
{
|
"#operator": "DistinctScan",
|
"scan": {
|
"#operator": "IndexScan3",
|
"as": "o",
|
"index": "ix50",
|
"index_id": "7b88f880219a7640",
|
"index_order": [
|
{
|
"desc": true,
|
"keypos": 1
|
}
|
],
|
"index_projection": {
|
"primary_key": true
|
},
|
"keyspace": "default",
|
"namespace": "default",
|
"spans": [
|
{
|
"exact": true,
|
"range": [
|
{
|
"high": "`d`",
|
"inclusion": 3,
|
"index_key": "(distinct (array `k` for `k` in `a1` end))",
|
"low": "`d`"
|
}
|
]
|
}
|
],
|
"using": "gsi"
|
}
|
},
|
{
|
"#operator": "Fetch",
|
"as": "o",
|
"early_projection": [
|
"a1",
|
"c1"
|
],
|
"keyspace": "default",
|
"namespace": "default"
|
},
|
{
|
"#operator": "Parallel",
|
"maxParallelism": 1,
|
"~child": {
|
"#operator": "Sequence",
|
"~children": [
|
{
|
"#operator": "Filter",
|
"condition": "cover (any `k` in (`o`.`a1`) satisfies (`k` = `d`) end)"
|
},
|
{
|
"#operator": "InitialProject",
|
"result_terms": [
|
{
|
"expr": "cover ((meta(`o`).`id`))"
|
},
|
{
|
"expr": "cover ((`o`.`c1`))"
|
}
|
]
|
}
|
]
|
}
|
}
|
]
|
},
|
{
|
"#operator": "Limit",
|
"expr": "50"
|
}
|
]
|
},
|
"subquery": "select cover ((meta(`o`).`id`)), cover ((`o`.`c1`)) from `default`:`default` as `o` where cover (any `k` in (`o`.`a1`) satisfies (`k` = `d`) end) ORDER BY cover ((`o`.`c1`)) DESC limit 50"
|
}
|
]
|
}
|
],
|
"status": "success",
|
"metrics": {
|
"elapsedTime": "3.000291ms",
|
"executionTime": "2.858208ms",
|
"resultCount": 1,
|
"resultSize": 7984,
|
"serviceLoad": 2
|
}
|
}
|
Subquery has Fetch also cover around expression this can result in wrong results.
FYI: If use FLATTEN_KEYS() seems to correct
Execute the query should return:
"results": [
|
{
|
"id": "doc1"
|
},
|
{
|
"id": "doc3"
|
},
|
{
|
"id": "doc5"
|
}
|
],
|
|