Affects Version/s: 3.2.1 GA (3.7.0 GA Suite Release)
Fix Version/s: Not Planned
Contrary to InfobrightDialect, recent versions of Infobright do support compound distinct-count.
Email from Tom Barber:
> I'm working with Pedro on a project involving
> Infobright(3.4.2). Pedro noticed that Mondrian is executing
> the following:
> select count as `c0` from (select distinct
> `dim_marketing_sources`.`sublevel2` as `c0`,
> `dim_marketing_sources`.`sublevel1` as `c1`,
> `dim_marketing_sources`.`mkt_channel` as `c2` from
> `dim_marketing_sources` as `dim_marketing_sources`) as `init`;
> which takes 18 seconds to execute where as,
> select count(distinct `dim_marketing_sources`.`sublevel2` ,
> `dim_marketing_sources`.`mkt_channel`) as `c` from
> takes less than 1.
> Is there any technical reason why the top count is preferred
> and if not, what needs to occur for the dialect to change?
The method that controls this is Dialect.allowsCompoundCountDistinct().
This method returns false by default, MySqlDialect overrides to return true, and InfobrightDialect overrides that to return false.
I added InfobrightDialect in change 12402, fixing bug http://jira.pentaho.com/browse/MONDRIAN-512. I must have made it return false for a good reason. Looking at DialectTest.testAllowsCompoundCountDistinct, it appears that older versions of Infobright returned the following error:
"The query includes syntax that is not supported by the Infobright Optimizer. Either restructure the query with supported syntax, or enable the MySQL Query Path in the brighthouse.ini file to execute the query with reduced performance."
My guess is that you are running a later version of Infobright. What version are you running?
If so, the fix would be for the method to return a different value depending on the version of infobright. Run the full suite through infobright, and let me know I will accept a patch to this method, but as usual the contributor must run the full mondrian suite. Let me know if any other DialectTest methods fail... Each of those failures may indicate a feature of infobright that we can take advantage of.