I am not sure whether the queries mentioned in the issue description were working before.

The second query select month(date()),stats[0].year sts from baseball  group by stats[0].year having stats[0].year=1904 doesn't return any data for me even though the issue description says that it works correctly.

The query select FLATTEN_ARRAY month(date()), stats.year sts from baseball group by stats.year having stats.year=1904 works correctly for me.

Emil, would you please take a look to see if the queries mentioned in the issue description are supported?

My inclination here is to not make any changes.

@emil: After your investigation, pls let us know what issues, if any, you think need to be fixed.

I guess Slava was referring to the following statement

select month(date()),stats.year as sts from baseball group by sts having sts=1904

which was indeed working some builds ago (e.g. ADS-14.0.0-rc8) after which some changes were made in the way how the pipeline operators are prepared for the Aggregation Framework (this was required to fix other issues). The problem resides in the first $project pipeline operator, (the "sts":1 projection) as underlined in red by Slava.

I've fixed the passing-over algorithm (for the group-by keys etc) inside this $project pipeline op and now this scenario is supported.

Tested in Aqua Data Studio 14.0.0-rc-39 Build #: 34238 on Ubuntu 12.04( Mongo DB 2.4.3) 

Also looks like as regression error with aliases:

Emil, please investigate and let us know what the problem is.

You cannot use the dates field (i.e. the original field's name) inside HAVING when aliases are present. The MongoSQL Query Reference states the following:

On an aggregate SELECT query (i.e. when the GROUP BY clause is present), the WHERE clause filters the documents before performing the GROUP BY operation while the HAVING clause filters the result set after the aggregation step.  In the WHERE clause, the collection's field names are used when processing the WHERE filters.  However, the field names specified in the HAVING clause are matched with the alias names specified in the SELECT fields first when processing the HAVING filters.

Therefore, the following query should be used

I see there word FIRST. It means that in SECOND real fields will be used in HAVING.  And this states that if alias name is the same as some other field then this "double" name interpreted as alias in the HAVING FIRST.

The query:  

select dates d from orddate where month(dates) between 1 and 12 group by dates having month(dates) between 1 and 12

absolutely correct from the SQL point of view - user just wants to rename title on output. We have many similar queries which work correct. Solution is to use one internal alias for dates and d  and change month(...) to this alias or project both of them.

Solution is to use one internal alias for dates and d  and change month(...) to this alias or project both of them.

Cannot project both the original name and the alias on the final $project pipeline operator because this change would reopen the issue #9675.

I think the enhancement of using internal aliases cannot be properly handled for the current release of ADS: attention should be paid not to overlap existing fields (when they are used on a WHERE / HAVING filter) etc. Here is your comment from issue #9455:

This can be performed by internal naming of aliases. It can be also usefull in other cases not only this one. So valuation+1 you call saying "WHATvaluation" and in the lastest special $project rename it to the user's alias  - "valuation" and do not touch $match etc. But necessary  to have list of collection field names to see wheather or not alias matches any field name.  It can be time expensive.

emil.goicovici wrote:
However, this query is not currently working because the month(d) from the HAVING clause is also projected on the $project pipeline operator that occurs before the $group one. I have to fix this.

I've fixed this issue.

I think the enhancement of using internal aliases cannot be properly handled for the current release of ADS:

Agreed -- this can be logged as a separate enhancement request and assigned to v16. 

OK. I'm marking this issue as Resolved to indicate that the following query has been fixed:

select dates d from orddate where month(dates) between 1 and 12 group by dates having month(d) between 1 and 12

Verified in ADS 14.0.1

I closed this ticket but open new one in Ver 16.0 for query which still does not work.