[
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.
