Noted on ADS 14-beta-23.

Following aggregation related queries(mongoSQL) in my test case used to work fine against attached zips.json dataset in earlier version of ADS but they no longer work correctly(gives resultset having null columns) in current ADS.

However their pure mongo version still works fine when run on the mongoshell. 

Could you please investigate it? Thanks

1)

--find average population in each state
select T.state, avg(T.citypop) from (
select state, city, sum(pop) as citypop
from zips
group by state, city
) as T
group by state
order by state
GO


/* corresp mongo query
db.zips.aggregate( { $group : { _id : { state : "$state", city : "$city" }, pop : { $sum : "$pop"} }} , { $group : { _id : "$_id.state", avgcitypop : { $avg :"$pop"}}} , {$sort : {_id : 1}})
*/
 

2)

/*
Find the city in each state with the largest number of zip codes.
Then print those cities along with their states using the city population for ordering
(the cities should be ordered by their population.
*/

select T.state, LAST(T.city), LAST(T.pop) as popu, LAST(zipcount) from (
select state, city, count(_id) as zipcount, sum(pop) as pop from zips
group by state, city
order by zipcount
) as T
group by state
order by popu DESC
go


/* corresp mongo query
db.zips.aggregate( { $group:
{ _id: { state: "$state", city: "$city" },
zipcount : {$sum : 1},
pop: { $sum: "$pop" } } },
{ $sort: { zipcount: 1 } },
{ $group:
{ _id : "$_id.state",
biggestCity: { $last: "$_id.city" },
population: { $last: "$pop" },
zipcount : { $last : "$zipcount"}
} } ,{ $sort : {population : -1}})
*/