#10303: Same aggreagate command works differ with and without GROUP BY in SQL

slavakiev reported 2013-10-03T13:04:09Z · last modified 2015-07-07T20:08:06Z

Same aggreagate command works differ with and without GROUP BY in SQL

Dev	emil.goicovici
QA	anilkalshetti

Priority	Low
Complexity	Unknown

Component	MongoSQL
Version	17.0

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

Same aggregate commands ( just _id:0 and _id:1 in the $group) used for some SQL statements deespite on using GROUP BY in SQL. But results differ:

--Without GROUP BY

select month(date()) as mnn,AVG(valuation) as a,min(city) as c from baseball

where mnn=9 and city in ['Chicago','NewYork'] and stats[0].year in [1904] and teamName='Cubs-2' and ((managerName.first='John') or (colors[0]='blue'))

--Invalid query: cannot have both aggregate and conversion functions.

--With GROUP BY

select month(date()) as mnn,AVG(valuation) as a,min(city) as c from baseball where mnn=9 and city in ['Chicago','NewYork'] and stats[0].year in [1904] and teamName='Cubs-2' and ((managerName.first='John') or (colors[0]='blue')) group by valuation

--works correct

--Without GROUP BY

~~select month(dates) from orddate where month(dates) <=12 and funds>1~~

select dates+1 from orddate where month(dates)=12 and year(dates)=2012

_{--The expression 'month(dates)' should appear among the SELECT fields}

--With GROUP BY

~~select month(dates) from orddate where month(dates) <=12 and funds>1 group by dates~~

select dates+1 from orddate where month(dates)=12 and year(dates)=2012 group by dates

--works correct

Necessary to use same program code for such case.

1 attachment

screenshot1.png

2015-06-29T08:21:56Z

120 KB

All Comments (8) Change History

JennyNishimura 2013-10-03T15:47:39Z

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

emil.goicovici 2013-10-04T11:10:42Z · (edited)

2.

--Without GROUP BY

select month(dates) from orddate where month(dates) <=12 and funds>1

_{--The expression 'funds' should appear among the SELECT fields}

I don't think you've checked this with the rc-39 build, as stated on the description. This error doesn't occur for the given scenario with the latest builds (14.0.0-rc-39 , 14.0.0-rc-44). It does reproduce on older builds.

slavakiev 2013-10-04T17:43:54Z

Sorry, I tested on older VMWare virtual machine. I found other SQL with same error and edit ticket.

emil.goicovici 2013-10-04T19:49:56Z · (edited)

Necessary to use same program code for such case.

It's not quite possible in this case to use the same approach because when the GROUP BY clause is not present, the $group pipeline operator becomes optional inside the implementation (i.e. it is used only if there are some aggregate functions like min(), max(), sum(), addToSet() etc). That's why the implementation is different and we get different results, but I agree that for scenarios like these we should get the same resultsets.

--Without GROUP BY

select month(date()) as mnn,AVG(valuation) as a,min(city) as c from baseball

where mnn=9 and city in ['Chicago','NewYork'] and stats[0].year in [1904] and teamName='Cubs-2' and ((managerName.first='John') or (colors[0]='blue'))

--Invalid query: cannot have both aggregate and conversion functions.

This exception was thrown because when the support for date conversion functions were introduced, I noticed that I have to suppress the $group pipeline operator (that it is used for other scenarios that requires Aggregation Framework) because otherwise this pipeline operator will hide the fields that are nor aggregated nor used as a grouo-by key.

Not using the $group pipeline operator allowed the advantage of using Aggregation Framework only to extract the month / day / year value without actually aggregating anything and thus being able to use any field of the given collection as a projection / where filter.

e.g. select month(dates) , funds from orddate

Both the 'funds' and 'dates' fields become unavailable if the $group pipeline op is present. This pipeline operator is required even if the GROUP BY clause is not explicitly specified:

select max(valuation) from baseball

A $group pipeline op is required for the latter query.

Given these scenarios that fail, I have to find a solution to support them, but still throw an error if the given combination (of projections) is not supported. For example, the following query can't be supported inside MongoSQL due to the Aggregation Framework particularities (the 'dates' field is not available after aggregation):

select mont(dates), avg(valuation) from baseball group by valuation

Of course, workarounds are possible such as using month(dates) as an artificial group-by key, but this will make translations from MongoSQL to MongoShell equivs more complex. I have to check if such workarounds are valid and can be generalized.

Necessary to use same program code for such case.

--Without GROUP BY

select month(date()) as mnn,AVG(valuation) as a,min(city) as c from baseball

where mnn=9 and city in ['Chicago','NewYork'] and stats[0].year in [1904] and teamName='Cubs-2' and ((managerName.first='John') or (colors[0]='blue'))

--Invalid query: cannot have both aggregate and conversion functions.

e.g. select month(dates) , funds from orddate

Both the 'funds' and 'dates' fields become unavailable if the $group pipeline op is present. This pipeline operator is required even if the GROUP BY clause is not explicitly specified:

select max(valuation) from baseball

A $group pipeline op is required for the latter query.

select mont(dates), avg(valuation) from baseball group by valuation

emil.goicovici 2013-10-04T20:10:40Z

Of course, workarounds are possible such as using month(dates) as an artificial group-by key,

Actually this is not a valid workaround because

select month(dates), funds from orddate group by funds

is not equivalent to

select month(dates), funds from orddate group by month(dates), funds

as the aggregation processes are different.

slavakiev 2013-10-05T10:19:17Z

I have no access to the source codes and can be wrong but seems to me that workflow is not depend on MongoDB aggregate statements. If you have methods something like "Mastering_$group", "Mastering_$match" etc. which make all necessary checks and error mesaging then workflow for all these statements go through these methods and such differences will be absent.

About scenario (1). Program should recognize that month(date( )) just a constant but not function on the field and does not show error in any cases.

In case when date conversion function has field as an argument necessary to treat them logicaly just as that field. So if $group is present and argument-field absent among groupped fields error should be same like in the next statement( just remove extra words "Can't display"):

SELECT dates,funds FROM orddate GROUP BY funds

-- Field "dates" is not present under the GROUP BY clause

But now instead of this error - exception occures:

SELECT month(dates),funds FROM orddate GROUP BY funds

-- Exception:Can't convert from BSON type E00 to date

SachinPrakash 2015-04-24T19:07:15Z · (edited)

Given the introduction of MongoJS, we won't be resolving this issue unless we get a customer request to do so.

@qa: pls ensure that this use case is supported in MongoJS

anilkalshetti 2015-06-29T08:22:51Z

Verified in ADSv17.0.0-dev-65

Please refer screenshot1.png

Search Tips

Aqua Data Studio / nhilam

Same aggreagate command works differ with and without GROUP BY in SQL

1 attachment

Issue #10303

Completion

Search Tips

Aqua Data Studio / nhilam

Title

Same aggreagate command works differ with and without GROUP BY in SQL

1 attachment

Issue #10303

Completion