[Mondrian] RE: Change 12424

Julian Hyde jhyde at pentaho.com
Wed Mar 18 22:05:07 EDT 2009


I did some checking and found that it's not as simple as 'nulls first' or
'nulls last'.
 
Oracle collates NULLS as if they are positive infinity (i.e. first for ASC,
last for DESC) and MySQL as if they are negaitve infinity. All other
databases seem to be compatible with Oracle.
 
Therefore I am going to replace Dialect.isNullsCollateLast with
Dialect.getNullCollation, where enum NullCollation { POSINF, NEGINF }
captures the behavior.
 
I also added a test to DialectTest to validate these assumptions. Then we'll
fail fast if a database collates nulls in a way we're not expecting.
 
I'll check in these changes tonight. Pull them tomorrow and see if they
solve your problem.
 
Julian


  _____  

From: Matt Campbell [mailto:mkambol at gmail.com] 
Sent: Wednesday, March 18, 2009 12:46 PM
To: jhyde at pentaho.com
Subject: Re: [Mondrian] RE: Change 12424


At least with the version of Oracle we are running (10.2.0.3.0) NULL FIRST
is the default.  The actual query executed (when native topcount is used) is
the one pasted below.  If NULLS LAST is omitted then the first 5 records
returned have null cell data. 


SELECT
"store"."store_name" AS "c0",
CASE "store_name" 
WHEN 'HQ' 
THEN NULL 
ELSE "store_name" 
END                      AS "c1",
"store"."store_sqft" AS "c2" 
FROM
"store" "store" 
GROUP BY
"store"."store_name",
CASE "store_name" 
WHEN 'HQ' 
THEN NULL 
ELSE "store_name" 
END,
"store"."store_sqft" 
ORDER BY
SUM("store"."store_sqft") DESC,
CASE "store_name" 
WHEN 'HQ' 
THEN NULL 
ELSE "store_name" 
END ASC


Regarding testNullCollation:  if the purpose is to test ordering of null
cells then I'm not sure why the [HQ] member of [Store Name] is being set to
have a NULL ordinal column, since the ordering in the test query will
ultimately be based on the results of the measure, not the OrdinalExpression
of [Store Name].


On Wed, Mar 18, 2009 at 3:15 PM, Julian Hyde <jhyde at pentaho.com> wrote:


Option #1 is the only feasible one, because in MySQL we need to wrap the
expression:
 
e DESC --> ISNULL(e), e DESC
 
But I'm pretty sure that Oracle puts nulls last by default. So, the query
should be producing the correct result as it stands.



On another note, I'm not sure that testNullCollation was ever testing what
it was intended to.  It appears to be testing the the ordering of members
with NULL ordinals correctly puts them at the end.  The [HQ] member in the
test is set to have a null ordinal.  The query used in the test, however,
uses a topcount function, and HQ does not fall into the topcount set.  So
this would never actually validate that HQ was placed at the end. 

I may be wrong, but my impression is that testNullCollation is testing the
ordering of null cells, not null members. The topCount generates select ...
group by sum(...) order by sum(...) to retrieve the cells in sorted order,
and we want cells whose values are nulls to appear last.
 
Julian
 


-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.pentaho.org/pipermail/mondrian/attachments/20090318/f1c528ec/attachment.html 


More information about the Mondrian mailing list