This query without a WHERE filter works.

select upper+first from test

But if there is a WHERE filter, it will return an error message.

select upper+first from test where upper+first > 5

Exception occurred during aggregation: exception: $where is not allowed inside of a $match aggregation expression

Verified in 14.0.0-beta-33. Slava, can you test and close all cases ?

exception:$where is not allowed inside of $match

The arithmetic expression involves Aggregation Framework. Therefore, this kind of query can be supported only if the fields present under the WHERE filters also appear as a WHAT field.

e.g.

SELECT valuation+1, city FROM baseball WHERE valuation+1>11 AND city='Chicago'

which would be translated by the JDBC driver to its MongoShell equiv:

db.baseball.aggregate({$project: { "valuation+1" : {$add: ["$valuation", 1]}, city: "$city"}}, {$match: {"valuation+1": {$gt:11}, "city":"Chicago"}})

I'll fix the JDBC driver to support such scenarios.

Tested in Aqua Data Studio Aqua Data Studio 14.0.0-beta-59 Build #: 33157

Adding GROUP BY causes same error:

SELECT max(valuation+1), city FROM baseball WHERE valuation+1>11 AND city='Chicago' GROUP BY city

--Exception occurred during aggregation: exception: $where is not allowed inside of a $match aggregation expression 

Also if "city" absent in the WHAT select return empty result set while returns rows with city in the WHAT:

 SELECT valuation+1 FROM baseball WHERE valuation+1>11 AND city='Chicago' 

-- empty result set 

Also if "city" absent in the WHAT select return empty result set while returns rows with city in the WHAT:

 SELECT valuation+1 FROM baseball WHERE valuation+1>11 AND city='Chicago'

Yes, this won't work in MongoSQL because it would require two $match pipeline operators (one before the projection and another one after it):

db.baseball.aggregate({$match: {city:"Chicago"}}, {$project: { "valuation+1" : {$add: ["$valuation", 1]}}}, {$match: {"valuation+1": {$gt:11}}})

But this conversion is tricky to implement as it is hard to determine the $match object on which each WHERE filters goes. Therefore, this scenario won't be supported and will return empty result.

Adding GROUP BY causes same error:

SELECT max(valuation+1), city FROM baseball WHERE valuation+1>11 AND city='Chicago' GROUP BY city

--Exception occurred during aggregation: exception: $where is not allowed inside of a $match aggregation expression

This scenario has been fixed.

Emil, why do not make simple interpretation of WHERE  clause in way that it will match to Mongo find() requirements. So if you have comparisson statement  - move all numbers to th eright side and fields to the left side. If you will have more than 1 variable  - than you need to use $where and if also statement requires aggregate then provide error message. If you have only 1 field then you can either use regular find() or aggregate with 1 match. In upper example just move +1 to the left and get valuation>11-1:

db.baseball.aggregate({$match: {city:"Chicago,valuation:{$gt:11-1}"}}}, {$project: { "valuation+1" : {$add: ["$valuation", 1]}}})

 I do not know but if 11-1 calculated by Mongo shell then why do not calculate this in the program too or provide  bind variable and assign 11-1 to it. This will simplify and standardise managing of comparisson expressions and  help resolve many restrictions we have now.  

Tested on Aqua Data Studio Aqua Data Studio 14.0.0-beta-64 Build #: 33188 on Ubuntu 12.04( Mongo DB 2.4.3)  and Windows XP ( Mongo DB 2.0.9).

If  valuation+1 changed  on valuation+2 or even just on valuation then  this statement returns empty result set

SELECT max(valuation), city FROM baseball WHERE valuation+1>=11 AND city='Chicago' GROUP BY city

but with valuation +1 it returns row. 

SELECT max(valuation+1), city FROM baseball WHERE valuation+1>=11 AND city='Chicago' GROUP BY city

max(valuation+1)       city

------------------------        -----

                     11       Chicago

Also upper  I have already described situation with empty result set if city is not present in WHAT clause. It still does not work:

 SELECT valuation+1 FROM baseball WHERE valuation+1>=11 AND city='Chicago' 

-- empty result set   

 SELECT valuation+1,city FROM baseball WHERE valuation+1>=11 AND city='Chicago'

 valuation+1               city

------------------------        -----

                     11       Chicago

If  valuation+1 changed  on valuation+2 or even just on valuation then  this statement returns empty result set

SELECT max(valuation), city FROM baseball WHERE valuation+1>=11 AND city='Chicago' GROUP BY city

This scenario fails because the "valuation + 1" arithmetic expression is not found under the WHAT area, thus a corresponding $project pipeline (that would perform this arithmetic operation) is not built.

 SELECT valuation+1 FROM baseball WHERE valuation+1>=11 AND city='Chicago'

Yes, this won't work in MongoSQL because it would require two $match pipeline operators (one before the projection and another one after it):

db.baseball.aggregate({$match: {city:"Chicago"}}, {$project: { "valuation+1" : {$add: ["$valuation", 1]}}}, {$match: {"valuation+1": {$gt:11}}})

But this conversion is tricky to implement as it is hard to determine the $match object on which each WHERE filters goes. Therefore, this scenario won't be supported and will return empty result.

Emil, why do not make simple interpretation of WHERE  clause in way that it will match to Mongo find() requirements. So if you have comparisson statement  - move all numbers to the right side and fields to the left side.

Indeed, this is a good proposal that will solve the two failing scenarios above. However, while working for certain scenarios where only "+"  and/or  "-" operators are present, this solution (move the fields on the LHS and the numbers on the RHS) would become trickier to implement when other arithmetic or binary operators are present (probably only a javascript equiv using $where would work in this case).

We could implement this approach in a future version.