Easily find issues by searching: #<Issue ID>
Example: #1832
Easily find members by searching in: <username>, <first name> and <last name>.
Example: Search smith, will return results smith and adamsmith
Tested Aqua Data Studio Aqua Data Studio 14.0.0-beta-68 Build #: 33238 on Ubuntu 12.04( Mongo DB 2.4.3) and Windows XP ( Mongo DB 2.0.9).
When statement has complex WHAT and WHERE cluses program takes full WHERE statement in one name and puts it into the $match clause.Thus such statements always works in wrong way by returning empty result set. At list program should return error message, e.g. "Complex expressions not supported" instead showing wrong result in silance.For example,
--Because program builds absolutely wrong statement by naming full WHERE clause as one name "valuation + 2 - (valuation + 1) + (valuation + 21)"
--[ { "$project" : { "_id" : 0, "(valuation + 3) * (valuation - 10)" : { "$multiply" : [ { "$add" : [ "$valuation", 3 ] }, { "$subtract" : [ "$valuation", 10 ] } ] }, "valuation + 21" : { "$add" : [ "$valuation", 21 ] }, "city" : "$city" } }, { "$match" : { "valuation + 2 - (valuation + 1) + (valuation + 21)" : 32 } } ]
In current version I propose to show error message instead such wrong beheviour. In next version such statement can be realised with help of using 2 or more $project statements when all binary memebers from WHAT and WHERE clauses automaticaly calculated in the first $project with giving them names( it is nt a problem if same memebers will be in the WHERE and WHAT because in $project duplication of same names is possible). In next $project in pipeline with these names possible to make new operations with new naming and finaly all comparisons can be done in the $match.
For example, currently wrong working statement:
Emil, would it be an easy fix to return an error message to let the users know that the WHERE expression should be included in the SELECT field list? Returning an empty result set is misleading.
An error message is now displayed if the arithmetic expression inside the WHERE clause does not appear among the WHAT fields.
An error message is now displayed if the arithmetic expression inside the WHERE clause does not appear among the WHAT fields.
Tested Aqua Data Studio Aqua Data Studio 14.0.0-beta-82 Build #: 33525 on Ubuntu 12.04( Mongo DB 2.4.3)
Correction not work if more than one expressions are in the WHERE:
select max(valuation),city from baseball where city='Chicago' and valuation+1=11
go
--empty result set
select max(valuation+1),city from baseball where city='Chicago' and valuation+1=11
go
--returns row
Tested Aqua Data Studio Aqua Data Studio 14.0.0-beta-82 Build #: 33525 on Ubuntu 12.04( Mongo DB 2.4.3)
Correction not work if more than one expressions are in the WHERE:
select max(valuation),city from baseball where city='Chicago' and valuation+1=11
go
--empty result set
select max(valuation+1),city from baseball where city='Chicago' and valuation+1=11
go
--returns row
With the ADS-14.0.0-beta-82 build, I get the following error message for both queries:
select max(valuation),city from baseball where city='Chicago' and valuation+1=11
-- Cannot display the 'city' field when aggregate functions are present.
select max(valuation),city from baseball where city='Chicago' and valuation+1=11
-- Cannot display the 'city' field when aggregate functions are present.
Slava, please provide the proper queries that you have run.
With the ADS-14.0.0-beta-82 build, I get the following error message for both queries:
select max(valuation),city from baseball where city='Chicago' and valuation+1=11
-- Cannot display the 'city' field when aggregate functions are present.
select max(valuation),city from baseball where city='Chicago' and valuation+1=11
-- Cannot display the 'city' field when aggregate functions are present.
Slava, please provide the proper queries that you have run.
Also empty result set and no message when GROUP BY is present:
SELECT valuation+3 FROM baseball WHERE valaution+1=11 GROUP BY valuation
go
-- empty result set
SELECT valuation+3 FROM baseball WHERE valaution+1=11
go
-- The 'valuation+1' expression should appear among the WHAT fields
Also empty result set and no message when GROUP BY is present:
SELECT valuation+3 FROM baseball WHERE valaution+1=11 GROUP BY valuation
go
-- empty result set
SELECT valuation+3 FROM baseball WHERE valaution+1=11
go
-- The 'valuation+1' expression should appear among the WHAT fields
Using aliases causes program show this message unappropriately:
SELECT (valuation + 1) as vl1 FROM baseball WHERE WHERE valuation+1=11
go
-- The 'valuation+1' expression should appear among the WHAT fields
Using aliases causes program show this message unappropriately:
SELECT (valuation + 1) as vl1 FROM baseball WHERE WHERE valuation+1=11
go
-- The 'valuation+1' expression should appear among the WHAT fields
Sorry. In that queries I missed GROUP BY:
select max(valuation),city from baseball where city='Chicago' and valuation+1=11 group by city
go
--empty result set
select max(valuation+1),city from baseball where city='Chicago' and valuation+1=11 group by city
go
--returns row
Sorry. In that queries I missed GROUP BY:
select max(valuation),city from baseball where city='Chicago' and valuation+1=11 group by city
go
--empty result set
select max(valuation+1),city from baseball where city='Chicago' and valuation+1=11 group by city
go
--returns row
Adequate error message is now displayed for these scenarios.
Using aliases causes program show this message unappropriately:
SELECT (valuation + 1) as vl1 FROM baseball WHERE valuation+1=11
-- The 'valuation+1' expression should appear among the WHAT fields
This query now returns the expected resultset.
Adequate error message is now displayed for these scenarios.
Using aliases causes program show this message unappropriately:
SELECT (valuation + 1) as vl1 FROM baseball WHERE valuation+1=11
-- The 'valuation+1' expression should appear among the WHAT fields
This query now returns the expected resultset.
Using aliases causes program show this message unappropriately:
SELECT (valuation + 1) as vl1 FROM baseball WHERE valuation+1=11
-- The 'valuation+1' expression should appear among the WHAT fields
This query now returns the expected resultset.
Still
SELECT (valuation + 1) as vl1 FROM baseball WHERE valuation+1=11
-- The 'valuation+1' expression should appear among the WHAT fields
go
Using aliases causes program show this message unappropriately:
SELECT (valuation + 1) as vl1 FROM baseball WHERE valuation+1=11
-- The 'valuation+1' expression should appear among the WHAT fields
This query now returns the expected resultset.
Still
SELECT (valuation + 1) as vl1 FROM baseball WHERE valuation+1=11
-- The 'valuation+1' expression should appear among the WHAT fields
go
Indeed, I was testing that it works when the GROUP BY clause was present.
Now the
SELECT (valuation+1) as vl1 FROM baseball WHERE valuation+1=11
scenario works as expected and the exception is no more thrown.
Indeed, I was testing that it works when the GROUP BY clause was present.
Now the
SELECT (valuation+1) as vl1 FROM baseball WHERE valuation+1=11
scenario works as expected and the exception is no more thrown.
Tested in Aqua Data Studio Aqua Data Studio 14.0.0-rc-13 Build #: 33892 on Ubuntu 12.04( Mongo DB 2.4.3)
Tested in Aqua Data Studio Aqua Data Studio 14.0.0-rc-13 Build #: 33892 on Ubuntu 12.04( Mongo DB 2.4.3)
Adequate pipeline operators are now built when date conversion functions are present inside the WHERE/HAVING clauses.
Adequate pipeline operators are now built when date conversion functions are present inside the WHERE/HAVING clauses.
Tested in Aqua Data Studio Aqua Data Studio 14.0.0-rc-20 Build #: 33993 on Ubuntu 12.04( Mongo DB 2.4.3)
SELECT valuation+1 as vl1 FROM baseball WHERE valuation+1=11 GROUP BY valuation+1
--The 'valuation' identifier is not available on this aggregate query
go
SELECT valuation+1 FROM baseball WHERE valuation+1=11 GROUP BY valuation+1
--empty result set
go
SELECT valuation+1 as vl1 FROM baseball WHERE valuation+1=11 GROUP BY vl1
--works correct
go
Tested in Aqua Data Studio Aqua Data Studio 14.0.0-rc-20 Build #: 33993 on Ubuntu 12.04( Mongo DB 2.4.3)
SELECT valuation+1 as vl1 FROM baseball WHERE valuation+1=11 GROUP BY valuation+1
--The 'valuation' identifier is not available on this aggregate query
go
SELECT valuation+1 FROM baseball WHERE valuation+1=11 GROUP BY valuation+1
--empty result set
go
SELECT valuation+1 as vl1 FROM baseball WHERE valuation+1=11 GROUP BY vl1
--works correct
go
Verified in Aqua Data Studio 14.0.0-rc-27 Build #: 34104 on Ubuntu 12.04( Mongo DB 2.4.3)
Verified in Aqua Data Studio 14.0.0-rc-27 Build #: 34104 on Ubuntu 12.04( Mongo DB 2.4.3)
Issue #9629 |
| Closed |
| Fixed |
| Resolved |
Completion |
| No due date |
| Fixed Build ADS 14.0.0-rc-25 (mongo-jdbc 1.3.5) |
| No time estimate |
2 issue links |
breaks #13625
Issue #13625Bug in Flattening behavior when dividing column by 1000 |
relates to #9218
Issue #9218Exception "$where is not allowed inside of $match" |
Emil, would it be an easy fix to return an error message to let the users know that the WHERE expression should be included in the SELECT field list? Returning an empty result set is misleading.