[Mondrian] Eigenbase perforce change 14675 for review

Luc Boudreau lucboudreau at gmail.com
Fri Oct 7 08:26:13 EDT 2011


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"

Luc


On Fri, Oct 7, 2011 at 12:23 AM, Julian Hyde <jhyde at pentaho.com> wrote:
> On Oct 6, 2011, at 6:17 PM, Luc Boudreau wrote:
>
> The problem appears when doing a count(distinct x) operation. All platforms
> return the null as a distinct value while Greenplum ignores it. I'll double
> check everything and revert if necessary. Thanks for the warning.
>
> count(distinct x) should eliminate the duplicates but still not count the
> null values. Thus, if the table has values {null, 1, 2, 2, 2} it should
> return 2. If it just has value {null} is should return 0.
> (I've implemented the rewrite rule
>   select count(distinct x) from t
>     --> select count(dx) from (select distinct x as dx from t)
> more than once in my RDBMS-implementing career.)
> I just checked, and MySQL does this. I'm 99.9% certain Oracle does too.
> So... Greenplum is standards-compliant here.
> If there's a specific query where Greenplum is giving a dubious result, send
> me the SQL and I'll check it out.
> Julian
> _______________________________________________
> Mondrian mailing list
> Mondrian at pentaho.org
> http://lists.pentaho.org/mailman/listinfo/mondrian
>
>


More information about the Mondrian mailing list