Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Received-SPF: neutral (athena.apache.org: local policy)
Message-ID: <5138C84B.6010706@sbcglobal.net>
Date: Thu, 07 Mar 2013 09:03:07 -0800
From: Katherine Marsden <kmarsdenderby@sbcglobal.net>
User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64;
 rv:17.0) Gecko/20130215 Thunderbird/17.0.3
MIME-Version: 1.0
To: derby-dev@db.apache.org
Subject: Are these ROW_NUMBER caveats still true?
Content-Type: multipart/alternative;
 boundary="------------060902090109040701020107"

This is a multi-part message in MIME format.
--------------060902090109040701020107
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

http://db.apache.org/derby/docs/10.9/ref/rreffuncrownumber.html

  * Derby does not currently allow the named or unnamed window
    specification to be specified in the OVER() clause, but requires an
    empty parenthesis. This means the function is evaluated over the
    entire result set.
  * The ROW_NUMBER function cannot currently be used in a WHERE clause.
  * Derby does not currently support ORDER BY in subqueries, so there is
    currently no way to guarantee the order of rows in the SELECT
    subquery. An optimizer override can be used to force the optimizer
    to use an index ordered on the desired column(s) if ordering is a
    firm requirement.


I see these possibly relevant fixes:

https://issues.apache.org/jira/browse/DERBY-3634
https://issues.apache.org/jira/browse/DERBY-4397

Also are there still performance considerations with ROW_NUMBER?
I see:
https://issues.apache.org/jira/browse/DERBY-3505

Are there still other concerns with ROW_NUMBER?

Thanks

Kathey

--------------060902090109040701020107
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

<html>
  <head>

    <meta http-equiv="content-type" content="text/html; charset=ISO-8859-1">
  </head>
  <body text="#000000" bgcolor="#FFFFFF">
    <a class="moz-txt-link-freetext" href="http://db.apache.org/derby/docs/10.9/ref/rreffuncrownumber.html">http://db.apache.org/derby/docs/10.9/ref/rreffuncrownumber.html</a><br>
    <br>
    <ul>
      <li>Derby does not currently allow the named or unnamed window
        specification to be specified in the <samp class="codeph">OVER()</samp>
        clause, but requires an empty parenthesis. This means the
        function is evaluated over the entire result set.</li>
      <li>The ROW_NUMBER function cannot currently be used in a WHERE
        clause.</li>
      <li>Derby does not currently support ORDER BY in subqueries, so
        there is currently no way to guarantee the order of rows in the
        SELECT subquery. An optimizer override can be used to force the
        optimizer to use an index ordered on the desired column(s) if
        ordering is a firm requirement.</li>
    </ul>
    <br>
    I see these possibly relevant fixes:<br>
    <br>
    <a class="moz-txt-link-freetext" href="https://issues.apache.org/jira/browse/DERBY-3634">https://issues.apache.org/jira/browse/DERBY-3634</a><br>
    <a class="moz-txt-link-freetext" href="https://issues.apache.org/jira/browse/DERBY-4397">https://issues.apache.org/jira/browse/DERBY-4397</a><br>
    <br>
    Also are there still performance considerations with ROW_NUMBER?<br>
    I see:<br>
    <a class="moz-txt-link-freetext" href="https://issues.apache.org/jira/browse/DERBY-3505">https://issues.apache.org/jira/browse/DERBY-3505</a><br>
    <br>
    Are there still other concerns with ROW_NUMBER?<br>
    <br>
    Thanks<br>
    <br>
    Kathey<br>
  </body>
</html>

--------------060902090109040701020107--