db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Daniel John Debrunner (JIRA)" <j...@apache.org>
Subject [jira] Commented: (DERBY-2887) NULLS FIRST / LAST for ORDER BY
Date Tue, 24 Jul 2007 13:49:31 GMT

    [ https://issues.apache.org/jira/browse/DERBY-2887?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12514968

Daniel John Debrunner commented on DERBY-2887:

Some  thoughts on the patch (v2):

Does the patch work with sort avoidance? If a scan is against an index and there is an ORDER
BY clause that matches the index then we avoid the order by.
  This optimization has to be turned off if NULLS FIRST is set (unless the columns are not-nullable).

  The passing of an extra argument to the compare() method, and for the regular compare now
calling the new compare method will affect performance. I don't know how significant it will
be, but within an index btree lookup compare will be called a lot, and now we are passing
in an argument which will always be false. A possible alternative that would not affect existing
performance, would be to have a single nulls first compare in DataType like (code only, comments
you added should be kept):

public final int compare(DataValueDescriptor other, boolean nullsFirst)  throws StandardException
 		if (this.isNull() || other.isNull())
 			if (!isNull())
				return nullsFirst ? 1 : -1;
 			if (!other.isNull())
			   return nullsFirst ? -1 : 1;
 			return 0;							// both null
                return compare(other);

Moving the single argument compare() method in each data type to the parent class DataType
would avoid repeated identical code in each class. However with the above comment it would
mean this comment doesn't apply, but it's a idea to remember for future changes, if possible
push duplicate code up the hierarchy.

For IndexColumnOrder, one approach would be to have two constructors, one with the old signature
which would default nulls first to false, and then the new one. This would mean that code
that isn't changing doesn't need to be modified by the patch.

> -------------------------------
>                 Key: DERBY-2887
>                 URL: https://issues.apache.org/jira/browse/DERBY-2887
>             Project: Derby
>          Issue Type: Improvement
>          Components: SQL
>            Reporter: Christian d'Heureuse
>            Assignee: Bryan Pendleton
>            Priority: Minor
>         Attachments: prototypeCodeNoTests.diff, prototypeCodeNoTests_v2.diff
> I suggest to implement the "null ordering" option for the "ORDER BY" clause:
> According to the SQL standard, 10.10 <sort specification list>:
>  <null ordering> ::=
>     | NULLS LAST

This message is automatically generated by JIRA.
You can reply to this email to add a comment to the issue online.

View raw message