Details
-
Bug
-
Resolution: Unresolved
-
Major
-
7.1.6, 7.2.5, 7.6.2
-
Untriaged
-
0
-
No
Description
CREATE INDEX ix1 ON default(type, META().id);
|
UPSERT INTO default (KEY t.id, VALUE t) SELECT {"type":"doc", "id":"aa_"||TO_STR(d), "a1": ARRAY {"ac0":IMOD(d1,1), "ac1":IMOD(d1,10), "ac2":IMOD(d1,2), "ac3":IMOD(d1,3)} FOR d1 IN ARRAY_RANGE(0,5) END } AS t FROM ARRAY_RANGE(0,5) AS d;
|
SELECT u.ac1, COUNT(1) AS cnt FROM (SELECT a1 FROM default AS t WHERE type = "doc" AND META().id LIKE "aa_%" ) AS d UNNEST d.a1 AS u GROUP BY u.ac1;
|
Above query gives right results
Now make it UINON by adding same query second ARM it should give same results. Instead counts wrong because a1 is same across documents wrongly added distinct on subquery eliminates duplicates
SELECT u.ac1, COUNT(1) AS cnt FROM (SELECT a1 FROM default AS t WHERE type = "doc" AND META().id LIKE "aa_%" ) AS d UNNEST d.a1 AS u GROUP BY u.ac1
|
UNION
|
SELECT u.ac1, COUNT(1) AS cnt FROM (SELECT a1 FROM default AS t WHERE type = "doc" AND META().id LIKE "aa_%" ) AS d UNNEST d.a1 AS u GROUP BY u.ac1;
|
If u look at Plan Distinct added after subquery projection. It should not be there. It should not go over UNION query block.
EXPLAIN SELECT u.ac1, COUNT(1) AS cnt FROM (SELECT a1 FROM default AS t WHERE type = "doc" AND META().id LIKE "aa_%" ) AS d UNNEST d.a1 AS u GROUP BY u.ac1 UNION SELECT u.ac1, COUNT(1) AS cnt FROM (SELECT a1 FROM default AS t WHERE type = "doc" AND META().id LIKE "aa_%" ) AS d UNNEST d.a1 AS u GROUP BY u.ac1;
|
{
|
"requestID": "81e8a3e6-2370-445b-9e43-77f7d6673c7a",
|
"signature": "json",
|
"results": [
|
{
|
"plan": {
|
"#operator": "Sequence",
|
"~children": [
|
{
|
"#operator": "UnionAll",
|
"~children": [
|
{
|
"#operator": "Sequence",
|
"~children": [
|
{
|
"#operator": "Sequence",
|
"~children": [
|
{
|
"#operator": "IndexScan3",
|
"as": "t",
|
"filter": "(_index_key ((meta(`t`).`id`)) like \"aa_%\")",
|
"index": "ix1",
|
"index_id": "36f3ccd730abb4dd",
|
"index_keys": [
|
"_index_key ((meta(`t`).`id`))"
|
],
|
"index_projection": {
|
"primary_key": true
|
},
|
"keyspace": "default",
|
"namespace": "default",
|
"optimizer_estimates": {
|
"cardinality": 250,
|
"cost": 40.54138126514911,
|
"fr_cost": 12.057082762530298,
|
"size": 2
|
},
|
"spans": [
|
{
|
"range": [
|
{
|
"high": "\"doc\"",
|
"inclusion": 3,
|
"index_key": "`type`",
|
"low": "\"doc\""
|
},
|
{
|
"high": "\"ab\"",
|
"inclusion": 1,
|
"index_key": "(meta().`id`)",
|
"low": "\"aa\""
|
}
|
]
|
}
|
],
|
"using": "gsi"
|
},
|
{
|
"#operator": "Fetch",
|
"as": "t",
|
"early_projection": [
|
"a1",
|
"type"
|
],
|
"keyspace": "default",
|
"namespace": "default",
|
"optimizer_estimates": {
|
"cardinality": 250,
|
"cost": 80.04138126514911,
|
"fr_cost": 24.1670827625303,
|
"size": 1
|
}
|
},
|
{
|
"#operator": "Parallel",
|
"~child": {
|
"#operator": "Sequence",
|
"~children": [
|
{
|
"#operator": "Filter",
|
"condition": "(((`t`.`type`) = \"doc\") and ((meta(`t`).`id`) like \"aa_%\"))",
|
"optimizer_estimates": {
|
"cardinality": 250,
|
"cost": 80.29138126514911,
|
"fr_cost": 24.1680827625303,
|
"size": 1
|
}
|
},
|
{
|
"#operator": "InitialProject",
|
"discard_original": true,
|
"optimizer_estimates": {
|
"cardinality": 250,
|
"cost": 80.54138126514911,
|
"fr_cost": 24.1690827625303,
|
"size": 1
|
},
|
"result_terms": [
|
{
|
"expr": "(`t`.`a1`)"
|
}
|
]
|
},
|
{
|
"#operator": "Distinct"
|
}
|
]
|
}
|
},
|
{
|
"#operator": "Distinct"
|
}
|
]
|
},
|
{
|
"#operator": "Alias",
|
"as": "d"
|
},
|
{
|
"#operator": "Parallel",
|
"~child": {
|
"#operator": "Sequence",
|
"~children": [
|
{
|
"#operator": "Unnest",
|
"as": "u",
|
"expr": "(`d`.`a1`)"
|
}
|
]
|
}
|
},
|
{
|
"#operator": "Parallel",
|
"~child": {
|
"#operator": "Sequence",
|
"~children": [
|
{
|
"#operator": "InitialGroup",
|
"aggregates": [
|
"count(1)"
|
],
|
"flags": 4,
|
"group_keys": [
|
"(`u`.`ac1`)"
|
]
|
}
|
]
|
}
|
},
|
{
|
"#operator": "IntermediateGroup",
|
"aggregates": [
|
"count(1)"
|
],
|
"flags": 4,
|
"group_keys": [
|
"(`u`.`ac1`)"
|
]
|
},
|
{
|
"#operator": "FinalGroup",
|
"aggregates": [
|
"count(1)"
|
],
|
"flags": 4,
|
"group_keys": [
|
"(`u`.`ac1`)"
|
]
|
},
|
{
|
"#operator": "Parallel",
|
"~child": {
|
"#operator": "Sequence",
|
"~children": [
|
{
|
"#operator": "InitialProject",
|
"discard_original": true,
|
"preserve_order": true,
|
"result_terms": [
|
{
|
"expr": "(`u`.`ac1`)"
|
},
|
{
|
"as": "cnt",
|
"expr": "count(1)"
|
}
|
]
|
},
|
{
|
"#operator": "Distinct"
|
}
|
]
|
}
|
},
|
{
|
"#operator": "Distinct"
|
}
|
]
|
},
|
{
|
"#operator": "Sequence",
|
"~children": [
|
{
|
"#operator": "Sequence",
|
"~children": [
|
{
|
"#operator": "IndexScan3",
|
"as": "t",
|
"filter": "(_index_key ((meta(`t`).`id`)) like \"aa_%\")",
|
"index": "ix1",
|
"index_id": "36f3ccd730abb4dd",
|
"index_keys": [
|
"_index_key ((meta(`t`).`id`))"
|
],
|
"index_projection": {
|
"primary_key": true
|
},
|
"keyspace": "default",
|
"namespace": "default",
|
"optimizer_estimates": {
|
"cardinality": 250,
|
"cost": 40.54138126514911,
|
"fr_cost": 12.057082762530298,
|
"size": 2
|
},
|
"spans": [
|
{
|
"range": [
|
{
|
"high": "\"doc\"",
|
"inclusion": 3,
|
"index_key": "`type`",
|
"low": "\"doc\""
|
},
|
{
|
"high": "\"ab\"",
|
"inclusion": 1,
|
"index_key": "(meta().`id`)",
|
"low": "\"aa\""
|
}
|
]
|
}
|
],
|
"using": "gsi"
|
},
|
{
|
"#operator": "Fetch",
|
"as": "t",
|
"early_projection": [
|
"a1",
|
"type"
|
],
|
"keyspace": "default",
|
"namespace": "default",
|
"optimizer_estimates": {
|
"cardinality": 250,
|
"cost": 80.04138126514911,
|
"fr_cost": 24.1670827625303,
|
"size": 1
|
}
|
},
|
{
|
"#operator": "Parallel",
|
"~child": {
|
"#operator": "Sequence",
|
"~children": [
|
{
|
"#operator": "Filter",
|
"condition": "(((`t`.`type`) = \"doc\") and ((meta(`t`).`id`) like \"aa_%\"))",
|
"optimizer_estimates": {
|
"cardinality": 250,
|
"cost": 80.29138126514911,
|
"fr_cost": 24.1680827625303,
|
"size": 1
|
}
|
},
|
{
|
"#operator": "InitialProject",
|
"discard_original": true,
|
"optimizer_estimates": {
|
"cardinality": 250,
|
"cost": 80.54138126514911,
|
"fr_cost": 24.1690827625303,
|
"size": 1
|
},
|
"result_terms": [
|
{
|
"expr": "(`t`.`a1`)"
|
}
|
]
|
},
|
{
|
"#operator": "Distinct"
|
}
|
]
|
}
|
},
|
{
|
"#operator": "Distinct"
|
}
|
]
|
},
|
{
|
"#operator": "Alias",
|
"as": "d"
|
},
|
{
|
"#operator": "Parallel",
|
"~child": {
|
"#operator": "Sequence",
|
"~children": [
|
{
|
"#operator": "Unnest",
|
"as": "u",
|
"expr": "(`d`.`a1`)"
|
}
|
]
|
}
|
},
|
{
|
"#operator": "Parallel",
|
"~child": {
|
"#operator": "Sequence",
|
"~children": [
|
{
|
"#operator": "InitialGroup",
|
"aggregates": [
|
"count(1)"
|
],
|
"flags": 4,
|
"group_keys": [
|
"(`u`.`ac1`)"
|
]
|
}
|
]
|
}
|
},
|
{
|
"#operator": "IntermediateGroup",
|
"aggregates": [
|
"count(1)"
|
],
|
"flags": 4,
|
"group_keys": [
|
"(`u`.`ac1`)"
|
]
|
},
|
{
|
"#operator": "FinalGroup",
|
"aggregates": [
|
"count(1)"
|
],
|
"flags": 4,
|
"group_keys": [
|
"(`u`.`ac1`)"
|
]
|
},
|
{
|
"#operator": "Parallel",
|
"~child": {
|
"#operator": "Sequence",
|
"~children": [
|
{
|
"#operator": "InitialProject",
|
"discard_original": true,
|
"preserve_order": true,
|
"result_terms": [
|
{
|
"expr": "(`u`.`ac1`)"
|
},
|
{
|
"as": "cnt",
|
"expr": "count(1)"
|
}
|
]
|
},
|
{
|
"#operator": "Distinct"
|
}
|
]
|
}
|
},
|
{
|
"#operator": "Distinct"
|
}
|
]
|
}
|
]
|
},
|
{
|
"#operator": "Distinct"
|
}
|
]
|
},
|
"text": "SELECT u.ac1, COUNT(1) AS cnt FROM (SELECT a1 FROM default AS t WHERE type = \"doc\" AND META().id LIKE \"aa_%\" ) AS d UNNEST d.a1 AS u GROUP BY u.ac1 UNION SELECT u.ac1, COUNT(1) AS cnt FROM (SELECT a1 FROM default AS t WHERE type = \"doc\" AND META().id LIKE \"aa_%\" ) AS d UNNEST d.a1 AS u GROUP BY u.ac1;"
|
}
|
],
|
"status": "success",
|
"metrics": {
|
"elapsedTime": "12.79707ms",
|
"executionTime": "12.571914ms",
|
"resultCount": 1,
|
"resultSize": 22449,
|
"serviceLoad": 3
|
}
|
}
|