Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Message-ID: <1713396853.1255060171453.JavaMail.jira@brutus>
Date: Thu, 8 Oct 2009 20:49:31 -0700 (PDT)
From: "Dag H. Wanvik (JIRA)" <jira@apache.org>
To: derby-dev@db.apache.org
Subject: [jira] Updated: (DERBY-3634) Cannot use row_number() in ORDER BY
 clause
In-Reply-To: <1747973222.1208544261642.JavaMail.jira@brutus>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit


     [ https://issues.apache.org/jira/browse/DERBY-3634?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Dag H. Wanvik updated DERBY-3634:
---------------------------------

    Attachment: derby-3634-newimpl-3.stat
                derby-3634-newimpl-3.diff

Uploading derby-3634-newimpl-3. Re-running regressions now.

- Inserted code to avoid pushdown of predicate if there is an ORDER BY
  and a window function because that would make ROW_NUMBER give wrong
  result, e.g.

    SELECT * from (SELECT ROW_NUMBER() OVER (), j FROM T
                   ORDER BY j) WHERE j=5

  Note that ORDER BY in a subselect is not supported until DERBY-4397
  is completed, so this is just to future-proof the code.  We could
  elect to always avoid push-down here, since the results may be
  surprising to some users (ROW_NUMBER() always being 1 for first
  matching row above), but relying on the order without an ORDER BY is
  inherently not protable and brittle, so I chose still push down the
  restrict in such cases, since it's a valid SQL result, but I am
  willing to reconsider this decision.

- Knut's bug: Remove an ASSERT in WindowFunctionNode#getGeneratedRef,
  since there is a valid use case in which the reference is null when
  retrieved, cf.

  SELECT * FROM t WHERE EXISTS (SELECT ROW_NUMBER() OVER () FROM t)

  in which case the top PRN of the subquery sitting over a
  WindowResultSetNode just contains a RC which is boolean constant
  true.  This means that the
  WindowFunctionNode#replaceCallsWithColumnReferences will not have
  been called for a window function call (one of
  SelectNode.windowFuncCalls), so the returned {@code generatedRef} is
  null. Another approach would have been to remove the window function
  call from SelectNode.windowFuncCalls, but that seems more complex.

  Since the generated ref may now be null, a test is added for this
  case in WindowResultSetNode#addNewColumns.

  Added a new test case to OLAPTest to check that this query works.

- Added check that a window function is not used in a WHERE clause
  (except when nested inside a subquery). Added test cases for this to
  OLAPTest.

- Added check that a window function is not used in a GROUP BY column
  expression (except when nested inside a subquery, but see
  DERBY-4403). Added test cases for this to OLAPTest.

- Added check that a window function is not used in a HAVING BY column
  expression (except when nested inside a subquery). Added test cases
  for this to OLAPTest. SQL 2003, section 7.10 SR 4.

- Fixed GroupByNode#addNewColumnsForAggregation to allow window
  functions inside a subquery in a HAVING clause, e.g.

  select sum(a) from t5 group by b
               having b = (select row_number() over () + 3
                               from t5 where a=1)

- Added check that a window function is not used in a VALUES row
  constructor (except when nested inside a subquery).  Added test
  cases for this to OLAPTest.

- Added check that a window function is not used inside an aggregate
  function.  Added test cases for this to OLAPTest. SQL 2003, section
  10.9 SR 7.

- Added check that a window function is not used inside a JOIN ON
  clause, (except when nested inside a subquery).  Added test cases
  for this to OLAPTest. SQL 2003, section 7.7 SR 5.


> Cannot use row_number() in ORDER BY clause
> ------------------------------------------
>
>                 Key: DERBY-3634
>                 URL: https://issues.apache.org/jira/browse/DERBY-3634
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 10.4.1.3
>            Reporter: Rick Hillegas
>            Assignee: Dag H. Wanvik
>         Attachments: derby-3634-a.diff, derby-3634-a.stat, derby-3634-a.txt, derby-3634-b.diff, derby-3634-b.stat, derby-3634-c.diff, derby-3634-c.stat, derby-3634-newimpl-1.diff, derby-3634-newimpl-1.stat, derby-3634-newimpl-1.txt, derby-3634-newimpl-2.diff, derby-3634-newimpl-2.stat, derby-3634-newimpl-3.diff, derby-3634-newimpl-3.stat, derby-3634-remove-2.diff, derby-3634-remove-2.stat, derby-3634-remove.diff, derby-3634-remove.stat
>
>
> The following query works correctly:
> select abs(a), row_number() over ()
> from t
> where a > 100 and a < 111
> order by abs(a)
> I expected the following query to also work, but it raised an exception:
> select abs(a), row_number() over ()
> from t
> where a > 100 and a < 111
> order by row_number() over ()
> This is the error I saw: "ERROR 42X01: Syntax error: Encountered "over" at line 5, column 23".
> Here are the reasons why I think that this syntax is supposed to be supported:
> According to my reading of the 2003 SQL spec, the ORDER BY clause should be able to sort on any expression in the SELECT list. That includes OLAP expressions. I believe this is so because, according to part 2, section 10.10 (<sort specification>), a <sort key> can be any <value expression> and if you follow the grammar for <value expression>, it can resolve to be a <value expression primary> (see section 6.3), which can in turn resolve to be a <window function>. This reasoning is supported by tracing the hotlinks on the following page which lays out the SQL 2003 BNF: http://savage.net.au/SQL/sql-2003-2.bnf.html This interpretation is further supported by the example of an ORDER BY clause referencing an OLAP expression which is provided on page 23 of the introduction to OLAP written by Fred Zemke, Krishna Kulkarni, Andy Witkowski, and Bob Lyle: www.cse.iitb.ac.in/dbms/Data/Papers-Other/SQL1999/OLAP-99-154r2.pdf

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