db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Apache Wiki <wikidi...@apache.org>
Subject [Db-derby Wiki] Update of "OLAPNullOrdering" by BryanPendleton
Date Sat, 21 Jul 2007 17:08:37 GMT
Dear Wiki user,

You have subscribed to a wiki page or wiki category on "Db-derby Wiki" for change notification.

The following page has been changed by BryanPendleton:
http://wiki.apache.org/db-derby/OLAPNullOrdering

------------------------------------------------------------------------------
  
  (As an aside, I wonder if there once was a time when NULL values sorted low in Derby, rather
than sorting HIGH. The reason I think this might have been true is that the JavaDoc for {{{DataValueDescriptor.compare()}}}
is incorrect, and says that null will be treated as '''less''' than all other values, and
also the code comment in NumberDataType.java is backward. Thirdly, the code in SQLBoolean.java
and in XML.java seems backward, which might be an un-noticed bug because the boolean data
type is not surfaced to users in current Derby, and the XML type is now and not much used
yet?)
  
+ == Interaction of NULLS FIRST/LAST and ASC/DESC ==
+ 
+ Are the results below correct? In particular, how does NULLS FIRST interact with DESC? Is
it correct that if you say "DESC NULLS FIRST", then the NULL values actually appear last (they're
sorted FIRST, but the results are DESCending)?
+ 
+ {{{
+ ij> select * from t1;
+ C1         |C2
+ -----------------------
+ NULL       |NULL
+ 1          |1
+ NULL       |NULL
+ 2          |1
+ 3          |1
+ 10         |10
+ 
+ 6 rows selected
+ ij> select * from t1 order by c1;
+ C1         |C2
+ -----------------------
+ 1          |1
+ 2          |1
+ 3          |1
+ 10         |10
+ NULL       |NULL
+ NULL       |NULL
+ 
+ 6 rows selected
+ ij> select * from t1 order by c1 nulls first;
+ C1         |C2
+ -----------------------
+ NULL       |NULL
+ NULL       |NULL
+ 1          |1
+ 2          |1
+ 3          |1
+ 10         |10
+ 
+ 6 rows selected
+ ij> select * from t1 order by c1 desc;
+ C1         |C2
+ -----------------------
+ NULL       |NULL
+ NULL       |NULL
+ 10         |10
+ 3          |1
+ 2          |1
+ 1          |1
+ 
+ 6 rows selected
+ ij> select * from t1 order by c1 desc nulls first;
+ C1         |C2
+ -----------------------
+ 10         |10
+ 3          |1
+ 2          |1
+ 1          |1
+ NULL       |NULL
+ NULL       |NULL
+ 
+ 6 rows selected
+ ij> select * from t1 order by c1 desc nulls last;
+ C1         |C2
+ -----------------------
+ NULL       |NULL
+ NULL       |NULL
+ 10         |10
+ 3          |1
+ 2          |1
+ 1          |1
+ 
+ 6 rows selected
+ }}}
+ 
  == Proposed Changes ==
  
  There are several parts to implementing the <null ordering> feature for Derby:

Mime
View raw message