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 Thu, 26 Jul 2007 17:20:16 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:

The comment on the change is:
Removed ref to unrelated DERBY-2916

  6 rows selected
+ == Null Ordering versus Ordered Nulls ==
+ '''Null Ordering''' and '''Ordered Nulls''' are similar, but not identical, concepts. '''Ordered
Nulls''' terminology is used when we are discussing whether or not we want the Derby engine
to use '''three-valued logic''' while performing comparison operations. When using three-valued
logic for comparison operations, comparison operators always return the special value '''unknown'''
if either of the two operands is NULL. Thus the Derby comparison operations can either operate
in '''Ordered Nulls''' mode, or in '''three-valued logic''' mode. '''Null Ordering''' is only
relevant in '''Ordered Nulls''' mode, and it further refines Ordered Nulls mode to describe
whether the NULL values should be compared lower than non-NULL values or higher than non-NULL
  == Current Implementation ==
  Currently, Derby always sorts NULL values as greater than non-NULL values.
@@ -172, +176 @@

   * The DataValueDescriptor.compare(DataValueDescriptor other) method must be changed to
allow the null ordering choice to be passed in. We want to be careful here, because the passing
of an extra argument to the compare() method will affect performance. For example, within
an index btree lookup the compare method will be called a lot, and we don't want to be needlessly
passing in an argument which will always be false.
   * Also, wherever possible, we want to consolidate common data type code into the common
superclass, rather than repeating redundant code throughout the subclasses. One way or another,
all of the types which implement the compare method must be changed to use the specified null
ordering (possibly by calling a new common method which implements the null ordering decision,
then delegates through to the data-type-specific subclass for comparison of non-null values).
   * The code which decides whether a query can use an index scan instead of a sort needs
to consider whether the null ordering allows that. This means that in some cases, depending
on how the user has specified the null ordering, we cannot use an index scan and must use
a sort instead.
-  * There seems to be some interaction between all the above code and the implicit "IS NULL"
predicate that is part of an OUTER join, which is causing me to get different "ordered null
semantics" on some of the OUTER JOIN queries in the Wisconsin regression suite. This behavior
needs to be investigated and resolved, since I don't think the null ordering changes should
affect the ordered null semantics of an OUTER JOIN.
   * A variety of new test cases must be added to the test suites, including:
     * There should be tests of ORDER BY clauses with NULLS FIRST specified, insuring that
null values indeed come first.
     * There should be tests of ORDER BY clauses with NULLS LAST specified, insuring that
null values indeed come last.
@@ -186, +189 @@

     * There should be tests that verify that sort avoidance occurs properly:
       * if an ORDER BY clause can be validly satisfied by an index, it should continue to
do so
       * if an ORDER BY clause can '''not''' be satisfied by an index, due to the user's <null
ordering> specification, the optimizer should force a true sort to occur.
-    * There should be tests that verify that the OUTER JOIN "ordered null semantics" are
preserved (FIXME: need to understand this better).
   * The existing test cases should all be run to verify that they still pass.

View raw message