db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Satheesh Bandaram <sathe...@Sourcery.Org>
Subject Re: About improvement of DERBY-134
Date Tue, 15 Mar 2005 15:28:53 GMT
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <meta content="text/html;charset=ISO-2022-JP"
 http-equiv="Content-Type">
  <title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
Since you are working on an enhancement to Derby, you need to sign a
CLA with Apache.<br>
<br>
Jean, is this the right CLA?
<a class="moz-txt-link-freetext" href="http://www.apache.org/~tetsuya/db/agreement.html">http://www.apache.org/~tetsuya/db/agreement.html</a><br>
<br>
Satheesh<br>
<br>
TomohitoNakayama wrote:
<blockquote cite="mid000c01c528ac$9505abf0$2000a8c0@Arkat" type="cite">Hello.
  <br>
  <br>
I have finished coding and testing in orderby.sql.
  <br>
I'm not sure test is enough.
  <br>
  <br>
Would you please review it ?
  <br>
  <br>
Best regards.
  <br>
  <br>
/*
  <br>
  <br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Tomohito Nakayama
  <br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <a class="moz-txt-link-abbreviated"
href="mailto:tomoihto@rose.zero.ad.jp">tomoihto@rose.zero.ad.jp</a>
  <br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <a class="moz-txt-link-abbreviated"
href="mailto:tomonaka@basil.ocn.ne.jp">tomonaka@basil.ocn.ne.jp</a>
  <br>
  <br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Naka
  <br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <a class="moz-txt-link-freetext"
href="http://www5.ocn.ne.jp/~tomohito/TopPage.html">http://www5.ocn.ne.jp/~tomohito/TopPage.html</a>
  <br>
  <br>
*/
  <br>
----- Original Message ----- From: "Satheesh Bandaram"
<a class="moz-txt-link-rfc2396E" href="mailto:satheesh@sourcery.org">&lt;satheesh@sourcery.org&gt;</a>
  <br>
To: "Derby Development" <a class="moz-txt-link-rfc2396E" href="mailto:derby-dev@db.apache.org">&lt;derby-dev@db.apache.org&gt;</a>
  <br>
Sent: Saturday, March 12, 2005 6:59 AM
  <br>
Subject: Re: About improvement of DERBY-134
  <br>
  <br>
  <br>
  <blockquote type="cite">Hi Tomohito Nakayama,
    <br>
    <br>
Just wanted to check how you are progressing on the patch update,
    <br>
following comments by myself and Jack. I do think you are working on an
    <br>
important enhancement that not only yourself but other developpers have
    <br>
expressed interest in. I strongly encourage you to continue working on
    <br>
this and post any questions or comments you might have. You are pretty
    <br>
close to addressing all issues.
    <br>
    <br>
I am willing to help, if you need any, to continue taking this further.
    <br>
    <br>
Satheesh
    <br>
    <br>
TomohitoNakayama wrote:
    <br>
    <br>
    <blockquote type="cite">Hello.
      <br>
Thanks for your reviewing.
      <br>
      <br>
About 1:
      <br>
Handling any sortKey as expression is better structure.
      <br>
A little challenging but worth for it.
      <br>
I will try.
      <br>
      <br>
About 2:
      <br>
Uh oh.
      <br>
Bug about starting value of element indexing in ResultColumnList ....
      <br>
Test of comma separated lists of ORDER BY expressions in orderby.sql
      <br>
was needed.....
      <br>
      <br>
About 3:
      <br>
I see.
      <br>
It seems that it is certainly needed to add test case .
      <br>
      <br>
I will continue this issue.
      <br>
Best regards.
      <br>
      <br>
/*
      <br>
      <br>
Tomohito Nakayama
      <br>
<a class="moz-txt-link-abbreviated" href="mailto:tomoihto@rose.zero.ad.jp">tomoihto@rose.zero.ad.jp</a>
      <br>
<a class="moz-txt-link-abbreviated" href="mailto:tomonaka@basil.ocn.ne.jp">tomonaka@basil.ocn.ne.jp</a>
      <br>
      <br>
Naka
      <br>
<a class="moz-txt-link-freetext" href="http://www5.ocn.ne.jp/~tomohito/TopPage.html">http://www5.ocn.ne.jp/~tomohito/TopPage.html</a>
      <br>
      <br>
*/
      <br>
----- Original Message ----- From: "Jack Klebanoff"
      <br>
<a class="moz-txt-link-rfc2396E" href="mailto:klebanoff-derby@sbcglobal.net">&lt;klebanoff-derby@sbcglobal.net&gt;</a>
      <br>
To: "Derby Development" <a class="moz-txt-link-rfc2396E" href="mailto:derby-dev@db.apache.org">&lt;derby-dev@db.apache.org&gt;</a>
      <br>
Sent: Sunday, February 20, 2005 8:37 AM
      <br>
Subject: Re: About improvement of DERBY-134
      <br>
      <br>
      <br>
      <blockquote type="cite">TomohitoNakayama wrote:
        <br>
        <br>
        <blockquote type="cite">Hello.
          <br>
          <br>
I have put some LOOKAHEAD to sqlgrammer.jj and
          <br>
add some test pattern to orderby.sql.
          <br>
          <br>
Would someone review patch please ?
          <br>
          <br>
Best regards.
          <br>
          <br>
/*
          <br>
          <br>
Tomohito Nakayama
          <br>
<a class="moz-txt-link-abbreviated" href="mailto:tomoihto@rose.zero.ad.jp">tomoihto@rose.zero.ad.jp</a>
          <br>
<a class="moz-txt-link-abbreviated" href="mailto:tomonaka@basil.ocn.ne.jp">tomonaka@basil.ocn.ne.jp</a>
          <br>
          <br>
Naka
          <br>
<a class="moz-txt-link-freetext" href="http://www5.ocn.ne.jp/~tomohito/TopPage.html">http://www5.ocn.ne.jp/~tomohito/TopPage.html</a>
          <br>
          <br>
*/
          <br>
----- Original Message ----- From: "TomohitoNakayama"
          <br>
<a class="moz-txt-link-rfc2396E" href="mailto:tomonaka@basil.ocn.ne.jp">&lt;tomonaka@basil.ocn.ne.jp&gt;</a>
          <br>
To: "Derby Development" <a class="moz-txt-link-rfc2396E" href="mailto:derby-dev@db.apache.org">&lt;derby-dev@db.apache.org&gt;</a>
          <br>
Sent: Sunday, February 13, 2005 4:09 PM
          <br>
Subject: Re: About improvement of DERBY-134
          <br>
          <br>
          <br>
          <blockquote type="cite">Sorry.
            <br>
Mistaken.
            <br>
            <br>
LOOKAHEAD()....
            <br>
            <br>
/*
            <br>
            <br>
Tomohito Nakayama
            <br>
<a class="moz-txt-link-abbreviated" href="mailto:tomoihto@rose.zero.ad.jp">tomoihto@rose.zero.ad.jp</a>
            <br>
<a class="moz-txt-link-abbreviated" href="mailto:tomonaka@basil.ocn.ne.jp">tomonaka@basil.ocn.ne.jp</a>
            <br>
            <br>
Naka
            <br>
<a class="moz-txt-link-freetext" href="http://www5.ocn.ne.jp/~tomohito/TopPage.html">http://www5.ocn.ne.jp/~tomohito/TopPage.html</a>
            <br>
            <br>
*/
            <br>
----- Original Message ----- From: "TomohitoNakayama"
            <br>
<a class="moz-txt-link-rfc2396E" href="mailto:tomonaka@basil.ocn.ne.jp">&lt;tomonaka@basil.ocn.ne.jp&gt;</a>
            <br>
To: "Derby Development" <a class="moz-txt-link-rfc2396E" href="mailto:derby-dev@db.apache.org">&lt;derby-dev@db.apache.org&gt;</a>
            <br>
Sent: Sunday, February 13, 2005 3:42 PM
            <br>
Subject: Re: About improvement of DERBY-134
            <br>
            <br>
            <br>
            <blockquote type="cite">Hello.
              <br>
              <br>
Thank's for your reviewing.
              <br>
Grammer ambiguity is very critical problem ....
              <br>
              <br>
I will try to put LOOKUP() and consider about testing..
              <br>
              <br>
#World is not simple as I wish to be.....
              <br>
              <br>
Best regards.
              <br>
              <br>
/*
              <br>
              <br>
Tomohito Nakayama
              <br>
<a class="moz-txt-link-abbreviated" href="mailto:tomoihto@rose.zero.ad.jp">tomoihto@rose.zero.ad.jp</a>
              <br>
<a class="moz-txt-link-abbreviated" href="mailto:tomonaka@basil.ocn.ne.jp">tomonaka@basil.ocn.ne.jp</a>
              <br>
              <br>
Naka
              <br>
<a class="moz-txt-link-freetext" href="http://www5.ocn.ne.jp/~tomohito/TopPage.html">http://www5.ocn.ne.jp/~tomohito/TopPage.html</a>
              <br>
              <br>
*/
              <br>
----- Original Message ----- From: Satheesh Bandaram
              <br>
To: Derby Development
              <br>
Sent: Saturday, February 12, 2005 4:10 AM
              <br>
Subject: Re: About improvement of DERBY-134
              <br>
              <br>
              <br>
I think the patch is a good start. But more work needs to be done.
              <br>
Based on a quick review, some of the items to be completed are:
              <br>
(there may be more)
              <br>
              <br>
Grammar ambiguity. SortKey() has grammar ambiguity the way the patch
              <br>
is written. Since orderby expression and orderby column can both
              <br>
start with an identifier, this causes ambiguity. Need to rewrite or
              <br>
add lookup to avoid this.
              <br>
Current patch doesn't seem to support all expressions, Ex: select i
              <br>
from t1 order by i/2. So, needs more work.
              <br>
Add more test cases and test outputs to show changed behavior. You
              <br>
could add test cases to orderby.sql test that is already part of
              <br>
functionTests/tests/lang.
              <br>
I do encourage you to continue work on this ...
              <br>
              <br>
Satheesh
              <br>
              <br>
TomohitoNakayama wrote:
              <br>
              <br>
I tried to solve DERBY-134.
              <br>
Patch is attached to this mail.
              <br>
              <br>
              <br>
/*
              <br>
              <br>
Tomohito Nakayama
              <br>
<a class="moz-txt-link-abbreviated" href="mailto:tomoihto@rose.zero.ad.jp">tomoihto@rose.zero.ad.jp</a>
              <br>
<a class="moz-txt-link-abbreviated" href="mailto:tomonaka@basil.ocn.ne.jp">tomonaka@basil.ocn.ne.jp</a>
              <br>
              <br>
Naka
              <br>
<a class="moz-txt-link-freetext" href="http://www5.ocn.ne.jp/~tomohito/TopPage.html">http://www5.ocn.ne.jp/~tomohito/TopPage.html</a>
              <br>
              <br>
*/
              <br>
----- Original Message ----- From: "TomohitoNakayama"
              <br>
<a class="moz-txt-link-rfc2396E" href="mailto:tomonaka@basil.ocn.ne.jp">&lt;tomonaka@basil.ocn.ne.jp&gt;</a>
              <br>
To: "Derby Development" <a class="moz-txt-link-rfc2396E" href="mailto:derby-dev@db.apache.org">&lt;derby-dev@db.apache.org&gt;</a>
              <br>
Sent: Wednesday, February 09, 2005 5:33 PM
              <br>
Subject: Re: About improvement of DERBY-134
              <br>
              <br>
              <br>
              <br>
Woops.
              <br>
Mistaken.
              <br>
              <br>
              <br>
That's "DERBY-124 Sorted string columns are sorted in a case
              <br>
sensitive way"
              <br>
              <br>
              <br>
              <br>
That's "DERBY-134 Sorted string columns are sorted in a case
              <br>
sensitive way"
              <br>
              <br>
/*
              <br>
              <br>
Tomohito Nakayama
              <br>
<a class="moz-txt-link-abbreviated" href="mailto:tomoihto@rose.zero.ad.jp">tomoihto@rose.zero.ad.jp</a>
              <br>
<a class="moz-txt-link-abbreviated" href="mailto:tomonaka@basil.ocn.ne.jp">tomonaka@basil.ocn.ne.jp</a>
              <br>
              <br>
Naka
              <br>
<a class="moz-txt-link-freetext" href="http://www5.ocn.ne.jp/~tomohito/TopPage.html">http://www5.ocn.ne.jp/~tomohito/TopPage.html</a>
              <br>
              <br>
*/
              <br>
----- Original Message ----- From: "TomohitoNakayama"
              <br>
<a class="moz-txt-link-rfc2396E" href="mailto:tomonaka@basil.ocn.ne.jp">&lt;tomonaka@basil.ocn.ne.jp&gt;</a>
              <br>
To: <a class="moz-txt-link-rfc2396E" href="mailto:derby-dev@db.apache.org">&lt;derby-dev@db.apache.org&gt;</a>
              <br>
Sent: Wednesday, February 09, 2005 4:35 PM
              <br>
Subject: About improvement of DERBY-134
              <br>
              <br>
              <br>
              <br>
Hello.
              <br>
My name is Naka.
              <br>
I'm very newbie in derby community.
              <br>
              <br>
I'm now seeing report for derby in ASF Jira.
              <br>
And found a interesting issue.
              <br>
              <br>
That's "DERBY-124 Sorted string columns are sorted in a case
              <br>
sensitive way"
              <br>
              <br>
This issue seems to mean that we can't use complex item in order
              <br>
clause.
              <br>
#That title was difficult to understand a bit ....
              <br>
              <br>
Solving this isn't useful?
              <br>
Especially when we manipulate DBMS by hand.
              <br>
              <br>
What I think we need to do is as next:
              <br>
              <br>
1) Allow additiveExpression() in sortKey() in "sqlgrammer.jj". 2)
              <br>
Make OrderByColumn class to support additiveExpression.
              <br>
              <br>
Best regards.
              <br>
              <br>
/*
              <br>
              <br>
Tomohito Nakayama
              <br>
<a class="moz-txt-link-abbreviated" href="mailto:tomoihto@rose.zero.ad.jp">tomoihto@rose.zero.ad.jp</a>
              <br>
<a class="moz-txt-link-abbreviated" href="mailto:tomonaka@basil.ocn.ne.jp">tomonaka@basil.ocn.ne.jp</a>
              <br>
              <br>
Naka
              <br>
<a class="moz-txt-link-freetext" href="http://www5.ocn.ne.jp/~tomohito/TopPage.html">http://www5.ocn.ne.jp/~tomohito/TopPage.html</a>
              <br>
              <br>
*/
              <br>
              <br>
            </blockquote>
          </blockquote>
        </blockquote>
I have worked on Derby/Cloudscape for a few years and have even fixed
        <br>
one or two ORDER BY bugs in the past. I have reviewed your patch. It is
        <br>
close, but I have some problems with it.
        <br>
        <br>
1. sqlgrammar.jj. I think that creating a new method,
        <br>
isNonReservedKeyword() to determine whether a token is a non-reserved
        <br>
keyword or not, is a maintenance problem. Whenever we add a new
        <br>
non-reserved keyword we must add it to the list of tokens, to
        <br>
nonReservedKeyword(), and now to isNonReservedKeyword(). Having to add
        <br>
it in two places is difficult enough to discover or remember. If we
need
        <br>
isNonReservedKeyword then we should find a way of combining
        <br>
nonReservedKeyword and isNonReservedKeyword so that only one of them
        <br>
keeps the list of non-reserved key words.
        <br>
        <br>
It is not necessary for the parser to recognize 3 cases of ORDER BY
sort
        <br>
key type. A column name is just one kind of &lt;expression&gt;. If the
parser
        <br>
treats it as an expression we should still get the right ordering. I
        <br>
think that it would better if the parser did so. The OrderByColumn
class
        <br>
can special case a simple column reference expression, as an
        <br>
optimization. This considerably simplifies parsing sort keys.
        <br>
        <br>
The only sort key type that has to be handled specially is that of an
        <br>
integer constant. That specifies one of the select list columns as the
        <br>
sort key. This case can be recognized in the parser, as is done in the
        <br>
patch, or it can be recognized in OrderByColumn. In this alternative
the
        <br>
parser always creates OrderByColumn nodes with the sort key given by an
        <br>
expression (a ValueNode). At bind time OrderByColumn can determine
        <br>
whether the sort key expression is an integer constant, and if so treat
        <br>
it as a column position.
        <br>
        <br>
The two alternatives differ in the way that they treat constant integer
        <br>
expressions like "ORDER BY 2-1". The patch orders the rows by the
        <br>
constant 1, which is not usefull. With the patch "ORDER BY 2-1 ASC" and
        <br>
"ORDER BY 2-1 DESC" produce the same ordering. If OrderByColumn treated
        <br>
an integer constant sort key expression as a result column position
then
        <br>
"ORDER BY 2-1" would cause the rows to be ordered on the first result
        <br>
column, which I think is more usefull.
        <br>
        <br>
2. OrderByColumn. I think that there is a mistake in the patch to the
        <br>
bindOrderByColumn method of class OrderByColumn.
        <br>
        <br>
The new code is
        <br>
}else if(expression != null){
        <br>
        <br>
ResultColumn col = null;
        <br>
int i = 0;
        <br>
        <br>
for(i = 0;
        <br>
i &lt; targetCols.size();
        <br>
i ++){
        <br>
col = targetCols.getOrderByColumn(i);
        <br>
if(col != null &amp;&amp;
        <br>
col.getExpression() == expression){
        <br>
break;
        <br>
}
        <br>
}
        <br>
        <br>
Method ResultColumnList.getOrderByColumn( int) uses 1 indexing. The
        <br>
patch assumes 0 indexing. So the loop really should be "for( i = 1; i
&lt;=
        <br>
targetCols.size(); i++)".
        <br>
        <br>
(Java likes 0 indexing while SQL likes 1 indexing. So some parts of the
        <br>
Derby code use 0 indexing while others use 1 indexing. The resulting
        <br>
confusion has caught most of us at one time or another).
        <br>
        <br>
The result is that when the sort key is an expression
        <br>
OrderByColumn.pullUpOrderByColumn adds it to the end of the target
list,
        <br>
but OrderByColumn.bindOrderByColumn doesn't find it.
        <br>
OrderByColumn.bindOrderByColumn tests whether the second last column in
        <br>
the target list is orderable. This is usually not right. Consider the
        <br>
following SQL:
        <br>
        <br>
create table tblob( id int, b blob(1000));
        <br>
select id,b from tblob order by abs(id);
        <br>
select b,id from tblob order by abs(id);
        <br>
        <br>
The first SELECT raises the error "ERROR X0X67: Columns of type 'BLOB'
        <br>
may not be used in CREATE INDEX, ORDER BY, GROUP BY, UNION, INTERSECT,
        <br>
EXCEPT or DISTINCT, because comparisons are not supported for that
        <br>
type". The second SELECT executes properly.
        <br>
        <br>
3. Testing. I would like to see some additional tests: the failing case
        <br>
above; ORDER BY expressions combined with ASC and DESC, to ensure that
        <br>
the compiler handles ASC and DESC after a sort key, and comma separated
        <br>
lists of ORDER BY expressions.
        <br>
        <br>
Jack
        <br>
        <br>
        <br>
        <br>
        <br>
        <br>
--&nbsp;<br>
No virus found in this incoming message.
        <br>
Checked by AVG Anti-Virus.
        <br>
Version: 7.0.300 / Virus Database: 266.1.0 - Release Date: 2005/02/18
        <br>
        <br>
        <br>
      </blockquote>
      <br>
      <br>
      <br>
    </blockquote>
    <br>
    <br>
    <br>
    <br>
    <br>
--&nbsp;<br>
No virus found in this incoming message.
    <br>
Checked by AVG Anti-Virus.
    <br>
Version: 7.0.308 / Virus Database: 266.7.2 - Release Date: 2005/03/11
    <br>
    <br>
    <br>
  </blockquote>
  <pre wrap="">
<hr size="4" width="90%">
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.7.2 - Release Date: 2005/03/11
  </pre>
</blockquote>
</body>
</html>


Mime
View raw message