Description
This query:
SELECT job_title, array_concat(array_agg(name), array_agg(email), array_agg(join_day)) as names FROM default GROUP BY job_title limit 10
Limits the array_agg fields before aggregation instead of limiting the group by fields after array aggregation.
Plan:
{
"plan":{
"#operator":"Sequence",
"~children":[
{
"#operator":"Sequence",
"~children":[
{
"#operator":"PrimaryScan3",
"index":"#primary",
"index_projection":{
"primary_key":true
},
"keyspace":"default",
"limit":"10",
"namespace":"default",
"using":"gsi"
},
{
"#operator":"Fetch",
"keyspace":"default",
"namespace":"default"
},
{
"#operator":"Parallel",
"~child":{
"#operator":"Sequence",
"~children":[
{
"#operator":"InitialGroup",
"aggregates":[
"array_agg((`default`.`email`))",
"array_agg((`default`.`join_day`))",
"array_agg((`default`.`name`))"
],
"group_keys":[
"(`default`.`job_title`)"
]
}
]
}
},
{
"#operator":"IntermediateGroup",
"aggregates":[
"array_agg((`default`.`email`))",
"array_agg((`default`.`join_day`))",
"array_agg((`default`.`name`))"
],
"group_keys":[
"(`default`.`job_title`)"
]
},
{
"#operator":"FinalGroup",
"aggregates":[
"array_agg((`default`.`email`))",
"array_agg((`default`.`join_day`))",
"array_agg((`default`.`name`))"
],
"group_keys":[
"(`default`.`job_title`)"
]
},
{
"#operator":"Parallel",
"~child":{
"#operator":"Sequence",
"~children":[
{
"#operator":"InitialProject",
"result_terms":[
{
"expr":"(`default`.`job_title`)"
},
{
"as":"names",
"expr":"array_concat(array_agg((`default`.`name`)), array_agg((`default`.`email`)), array_agg((`default`.`join_day`)))"
}
]
},
{
"#operator":"FinalProject"
}
]
}
}
]
},
{
"#operator":"Limit",
"expr":"10"
}
]
},
"text":"SELECT job_title, array_concat(array_agg(name), array_agg(email), array_agg(join_day)) as names FROM default GROUP BY job_title limit 10"
}
Results:
[
{
"job_title": "Engineer",
"names": [
"employee-9",
"employee-9",
"employee-9",
"employee-9",
"employee-9",
"employee-9",
"employee-9",
"employee-9",
"employee-9",
"employee-9",
"9-mail@couchbase.com",
"9-mail@couchbase.com",
"9-mail@couchbase.com",
"9-mail@couchbase.com",
"9-mail@couchbase.com",
"9-mail@couchbase.com",
"9-mail@couchbase.com",
"9-mail@couchbase.com",
"9-mail@couchbase.com",
"9-mail@couchbase.com",
9,
9,
9,
9,
9,
9,
9,
9,
9,
9
]
}
]
Expected Results:
three rows representing the job_title group by with names field being an array of hundreds if not thousands of values
./testrunner -i cb.ini doc-per-day=6,standard_buckets=1,primary_indx_type=GSI,skip_cleanup=False,nodes_init=1 -t tuqquery.tuq_sanity.QuerySanityTests.test_array_concat