#9644: Wrong realization of COUNT() in case when aggregation framework involved when GROUP BY is absent

slavakiev reported 2013-07-23T20:27:04Z · last modified 2013-08-02T19:42:24Z

Wrong realization of COUNT() in case when aggregation framework involved when GROUP BY is absent

Dev	emil.goicovici
QA	tariqrahiman

Priority	Major
Complexity	Unknown

Component	MongoSQL
Version	14.0

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).

In case when SQL statement has no GROUP BY but elements of WHAT clause causes to use aggregate framework realization of COUNT( ) done in the program by using $cond statement in $group and this returns 0. See result of next statement:

select count(valuation),sum(valuation/valuation) from baseball

--count(valuation) sum(valuation/valuation)

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

--0 1

Correct result can be obtained If $cond statement use in the project instead $group:

db.baseball.aggregate( { "$project" :{ "valuation / valuation" : { "$divide" : [ "$valuation", "$valuation" ]} , "valcond":{"$cond" : [ { "$or" : [ "$valuation", { "$eq" : [ "$valuation", null ] } ] }, 1, 0 ] } }},{ "$group" : { "_id" : null , "count(valuation)" : { "$sum" : "$valcond" }, "sum(valuation / valuation)" : { "$sum" : "$valuation / valuation" } } } )

All Comments (3) Change History

JennyNishimura 2013-07-24T22:53:24Z

The following queries return the correct result sets.

select count(valuation) from baseball
go
select sum(valuation) from baseball
go
select sum(valuation/valuation) from baseball
go
select count(valuation), sum(valuation) from baseball
go
select valuation, count(valuation), sum(valuation/valuation) from baseball group by valuation
go

But the following query returns 0 for count(valuation):

select count(valuation), sum(valuation/valuation) from baseball
go

The following queries return the correct result sets.

select count(valuation) from baseball
go
select sum(valuation) from baseball
go
select sum(valuation/valuation) from baseball
go
select count(valuation), sum(valuation) from baseball
go
select valuation, count(valuation), sum(valuation/valuation) from baseball group by valuation
go

But the following query returns 0 for count(valuation):

select count(valuation), sum(valuation/valuation) from baseball
go

emil.goicovici 2013-07-31T21:08:39Z

These scenarios work now as expected with the fix made for #9644. Thank you, Slava.

slavakiev 2013-08-02T19:42:24Z

Verified in Aqua Data Studio Aqua Data Studio 14.0.0-beta-79 Build #: 33402 on Ubuntu 12.04( Mongo DB 2.4.3)

and Windows XP ( Mongo DB 2.0.9).

Search Tips

Aqua Data Studio / nhilam

Wrong realization of COUNT() in case when aggregation framework involved when GROUP BY is absent

Issue #9644

Completion

3 issue links

Issue #9690

Issue #9654

Issue #9690

Search Tips

Aqua Data Studio / nhilam

Title

Wrong realization of COUNT() in case when aggregation framework involved when GROUP BY is absent

Issue #9644

Completion

3 issue links

Issue #9690

Issue #9654

Issue #9690