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.

Luc
On Oct 6, 2011 9:12 PM, "Julian Hyde" <julian (AT) hydromatic (DOT) net> wrote:
> It's standard behavior that databases don't count nulls. For example,

given table t with one column c and values null, 1, 2, then 'select count(*)
from t' will return 3 and 'select count(c) from t' will return 2.
>
> Sounds like Greenplum complies with the standard, so you shouldn't need to

do anything special in the dialect. I think there might be some other
problem.
>
> Julian
>
> On Oct 6, 2011, at 5:58 PM, Luc Boudreau wrote:
>
>> http://p4web.eigenbase.org/@md=d&c=6PU@/14675?ac=10
>>
>> Change 14675 by lucboudreau (AT) luc-mondrian-3 (DOT) 2 on 2011/10/06 17:56:36
>>
>> MONDRIAN: Fixes an issue with Greenplum. A null value can be returned by

the driver, thus creating an NPE when comparing values. Also fixes an issue
where COUNT() in Greenplum doesn't count null values.
>>
>> Affected files ...
>>
>> ... //open/mondrian/src/main/mondrian/rolap/SqlMemberSource.java#122 edit
>> ... //open/mondrian/src/main/mondrian/spi/Dialect.java#27 edit
>> ... //open/mondrian/src/main/mondrian/spi/impl/GreenplumDialect.java#5

edit
>> ... //open/mondrian/src/main/mondrian/spi/impl/JdbcDialectImpl.java#34

edit
>>
>> Differences ...
>>
>> ==== //open/mondrian/src/main/mondrian/rolap/SqlMemberSource.java#122

(ktext) ====
>>
>> 2c2
>> < // $Id:

//open/mondrian/src/main/mondrian/rolap/SqlMemberSource.java#121 $
>> ---
>>> // $Id: //open/mondrian/src/main/mondrian/rolap/SqlMemberSource.java#122

$
>> 41c41
>> < * @version $Id:

//open/mondrian/src/main/mondrian/rolap/SqlMemberSource.java#121 $
>> ---
>>> * @version $Id:

//open/mondrian/src/main/mondrian/rolap/SqlMemberSource.java#122 $
>> 136c136
>> < if (!colStr.equals(colStrings[i])) {
>> ---
>>> if (!Util.equals(colStr, colStrings[i])) {

>> 230,231c230,233
>> < sqlQuery.addSelect(colDef, null);
>> < sqlQuery.addOrderBy(colDef, true, false, true);
>> ---
>>> final String exp =
>>> sqlQuery.getDialect().generateCountExpression(colDef);
>>> sqlQuery.addSelect(exp, null);
>>> sqlQuery.addOrderBy(exp, true, false, true);

>> 240c242,244
>> < sb.append(colDef);
>> ---
>>> sb.append(
>>> sqlQuery.getDialect()
>>> .generateCountExpression(colDef));

>>
>> ==== //open/mondrian/src/main/mondrian/spi/Dialect.java#27 (ktext) ====
>>
>> 86c86
>> < * @version $Id: //open/mondrian/src/main/mondrian/spi/Dialect.java#26 $
>> ---
>>> * @version $Id: //open/mondrian/src/main/mondrian/spi/Dialect.java#27 $

>> 718a719,728
>>> * Some databases, like Greenplum, don't include nulls as part
>>> * of the results of a COUNT sql call. This allows dialects
>>> * to wrap the count expression in something before it is used
>>> * in the query.
>>> * @param exp The expression to wrap.
>>> * @return A valid expression to use for a count operation.
>>> */
>>> String generateCountExpression(String exp);
>>>
>>> /**

>>
>> ==== //open/mondrian/src/main/mondrian/spi/impl/GreenplumDialect.java#5

(ktext) ====
>>
>> 20c20
>> < * @version $Id:

//open/mondrian/src/main/mondrian/spi/impl/GreenplumDialect.java#4 $
>> ---
>>> * @version $Id:

//open/mondrian/src/main/mondrian/spi/impl/GreenplumDialect.java#5 $
>> 76a77,80
>>> public String generateCountExpression(String exp) {
>>> return caseWhenElse( exp + " ISNULL", "'0'", "TEXT(" + exp + ")");
>>> }
>>>

>>
>> ==== //open/mondrian/src/main/mondrian/spi/impl/JdbcDialectImpl.java#34

(ktext) ====
>>
>> 32c32
>> < * @version $Id:

//open/mondrian/src/main/mondrian/spi/impl/JdbcDialectImpl.java#33 $
>> ---
>>> * @version $Id:

//open/mondrian/src/main/mondrian/spi/impl/JdbcDialectImpl.java#34 $
>> 871a872,875
>>> public String generateCountExpression(String exp) {
>>> return exp;
>>> }
>>>

>>

>


_______________________________________________
Mondrian mailing list
Mondrian (AT) pentaho (DOT) org
http://lists.pentaho.org/mailman/listinfo/mondrian