[Mondrian] Eigenbase perforce change 14675 for review

Julian Hyde jhyde at pentaho.com
Fri Oct 7 12:51:17 EDT 2011


> Luc wrote:

> On MySQL, Mondrian executes this query, which returns 21.
> 
>    select count(*) as `c0` from (select distinct `store`.`store_sqft`
> as `c0` from `foodmart`.`store` as `store`) as `init`
> 
> While on Greenplum, Mondrian executes this, which returns 20.
> 
>    select count(DISTINCT "store"."store_sqft") as "c0" from "store" as "store"

Ha!

Both databases are giving the right response. The queries are not equivalent. I ran

>    select count(`c0`) as `c1` from (select distinct `store`.`store_sqft`
> as `c0` from `foodmart`.`store` as `store`) as `init`

in MySQL and it gives 20 as expected.

So, why is Mondrian generating different queries? The problem seems to be in SqlMemberSource.makeLevelMemberCountSql. In the name of consistency, it should generate 'count(c)' rather than 'count(*)'. Null values will be ignored, but it will perform better and mondrian will be consistent across databases.

That method kind of goes about things backwards. It will generate a 'select count ... from subquery' style query even on databases that support compound distinct-count, like MySQL. It could be rewritten to first try to generate a distinct-count query, failing if the database doesn't support distinct-count or if the query is composite and the database doesn't support composite-distinct-count. Then it could try to use the subquery method. Lastly, it could fail.

How much of this should you do? Depends on how much time you have and how long you think it would take. What you don't implement, please document in a jira case.

Julian



More information about the Mondrian mailing list