Derby
  1. Derby
  2. DERBY-3634

Cannot use row_number() in ORDER BY clause

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 10.4.1.3
    • Fix Version/s: 10.6.1.0
    • Component/s: SQL
    • Labels:
      None
    • Urgency:
      Normal
    • Issue & fix info:
      High Value Fix

      Description

      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

      1. derby-3634-a.diff
        100 kB
        Dag H. Wanvik
      2. derby-3634-a.stat
        3 kB
        Dag H. Wanvik
      3. derby-3634-a.txt
        37 kB
        Dag H. Wanvik
      4. derby-3634-b.diff
        113 kB
        Dag H. Wanvik
      5. derby-3634-b.stat
        3 kB
        Dag H. Wanvik
      6. derby-3634-c.diff
        102 kB
        Dag H. Wanvik
      7. derby-3634-c.stat
        2 kB
        Dag H. Wanvik
      8. derby-3634-newimpl-1.diff
        110 kB
        Dag H. Wanvik
      9. derby-3634-newimpl-1.stat
        2 kB
        Dag H. Wanvik
      10. derby-3634-newimpl-1.txt
        38 kB
        Dag H. Wanvik
      11. derby-3634-newimpl-2.diff
        113 kB
        Dag H. Wanvik
      12. derby-3634-newimpl-2.stat
        2 kB
        Dag H. Wanvik
      13. derby-3634-newimpl-3.diff
        124 kB
        Dag H. Wanvik
      14. derby-3634-newimpl-3.stat
        3 kB
        Dag H. Wanvik
      15. derby-3634-newimpl-4.diff
        123 kB
        Dag H. Wanvik
      16. derby-3634-newimpl-4.stat
        3 kB
        Dag H. Wanvik
      17. derby-3634-remove.diff
        74 kB
        Dag H. Wanvik
      18. derby-3634-remove.stat
        2 kB
        Dag H. Wanvik
      19. derby-3634-remove-2.diff
        59 kB
        Dag H. Wanvik
      20. derby-3634-remove-2.stat
        2 kB
        Dag H. Wanvik

        Issue Links

          Activity

          Hide
          Knut Anders Hatlen added a comment -

          Fixed typo in summary.

          Show
          Knut Anders Hatlen added a comment - Fixed typo in summary.
          Hide
          Knut Anders Hatlen added a comment -

          There's a similar problem when you use an alias in the ORDER BY clause:

          This query works:

          select abs(a) c, row_number() over ()
          from t
          where a > 100 and a < 111
          order by c

          Whereas this query fails (ERROR 42X78: Column 'C' is not in the result of the query expression.):

          select abs(a), row_number() over () c
          from t
          where a > 100 and a < 111
          order by c

          Show
          Knut Anders Hatlen added a comment - There's a similar problem when you use an alias in the ORDER BY clause: This query works: select abs(a) c, row_number() over () from t where a > 100 and a < 111 order by c Whereas this query fails (ERROR 42X78: Column 'C' is not in the result of the query expression.): select abs(a), row_number() over () c from t where a > 100 and a < 111 order by c
          Hide
          Knut Anders Hatlen added a comment -

          Triaged for 10.5.2. Made it Unassigned because there has been no activity.

          Show
          Knut Anders Hatlen added a comment - Triaged for 10.5.2. Made it Unassigned because there has been no activity.
          Hide
          Dag H. Wanvik added a comment -

          While looking into the issues logged against the present
          ROW_NUMBER/WINDOW functionality (DERBY-3634(1), DERBY-3635(2),
          DERBY-4069(3), I was hard pressed to find a way to fix these without
          doing a major rewrite.

          1) DERBY-3634 Cannot use row_number() in ORDER BY clause
          2) DERBY-3635 Cannot build SELECT LIST expressions involving row_number()
          3) DERBY-4069 Wrong behavior when ROW_NUMBER is combined with ORDER BY

          This patch is a proof-of-concept (not for commit!) for restructuring
          the row_number/windowing code, which solves the three issues above and
          adds some generality as well.

          It does not yet address DERBY-3505 "Current implementation of
          ROW_NUMBER() window function does not stop execution once criteria is
          met".

          For details, see the write-up in derby-3634-a.txt.

          The patch adds new tests for the new functionality. Regressions ran cleanly.

          Show
          Dag H. Wanvik added a comment - While looking into the issues logged against the present ROW_NUMBER/WINDOW functionality ( DERBY-3634 (1), DERBY-3635 (2), DERBY-4069 (3), I was hard pressed to find a way to fix these without doing a major rewrite. 1) DERBY-3634 Cannot use row_number() in ORDER BY clause 2) DERBY-3635 Cannot build SELECT LIST expressions involving row_number() 3) DERBY-4069 Wrong behavior when ROW_NUMBER is combined with ORDER BY This patch is a proof-of-concept (not for commit!) for restructuring the row_number/windowing code, which solves the three issues above and adds some generality as well. It does not yet address DERBY-3505 "Current implementation of ROW_NUMBER() window function does not stop execution once criteria is met". For details, see the write-up in derby-3634-a.txt. The patch adds new tests for the new functionality. Regressions ran cleanly.
          Hide
          Knut Anders Hatlen added a comment -

          I noticed another row_number() problem that I don't think is mentioned in any of the bug reports. You cannot use row_number() in EXCEPT queries. (It works in UNION queries, though.)

          ij> select x, row_number() over() from t except select x, row_number() over () from t;
          ERROR 42X77: Column position '2' is out of range for the query expression.

          With your patch, this query starts working too, so it looks like it must be doing something right.

          Show
          Knut Anders Hatlen added a comment - I noticed another row_number() problem that I don't think is mentioned in any of the bug reports. You cannot use row_number() in EXCEPT queries. (It works in UNION queries, though.) ij> select x, row_number() over() from t except select x, row_number() over () from t; ERROR 42X77: Column position '2' is out of range for the query expression. With your patch, this query starts working too, so it looks like it must be doing something right.
          Hide
          Dag H. Wanvik added a comment - - edited

          Attaching version "b" of this patch. Relative to "a", it adds:

          • formatting cleanup, whitespace, long lines etc.
          • Makes ROW_NUMBER work inside an ORDER BY clause in the presence of
            a WHERE clause. It was optimized away because it was erroneously
            found to be constant relative to the restriction, see SelectNode's
            call to orderByList.removeConstantColumns(wherePredicates).

          Letting WdwFunctionNode overload methods isConstantExpression and
          constantExpression to return "false" solved the problem. A new test
          case has been added to OLAPTest to verify this use case.

          The patch it not production-ready, it lacks some Javadocs still, but
          otherwise, I think it is ready for a review.

          Show
          Dag H. Wanvik added a comment - - edited Attaching version "b" of this patch. Relative to "a", it adds: formatting cleanup, whitespace, long lines etc. Makes ROW_NUMBER work inside an ORDER BY clause in the presence of a WHERE clause. It was optimized away because it was erroneously found to be constant relative to the restriction, see SelectNode's call to orderByList.removeConstantColumns(wherePredicates). Letting WdwFunctionNode overload methods isConstantExpression and constantExpression to return "false" solved the problem. A new test case has been added to OLAPTest to verify this use case. The patch it not production-ready, it lacks some Javadocs still, but otherwise, I think it is ready for a review.
          Hide
          Bryan Pendleton added a comment -

          Hi Dag, thanks for working on this. I've just started reading your detailed writeups; they are very helpful – thanks!

          I think it would be nice to spell out the word "Window" in the code, so we'd have
          WindowFunctionNode, not WdwFunctionNode, etc.

          Show
          Bryan Pendleton added a comment - Hi Dag, thanks for working on this. I've just started reading your detailed writeups; they are very helpful – thanks! I think it would be nice to spell out the word "Window" in the code, so we'd have WindowFunctionNode, not WdwFunctionNode, etc.
          Hide
          Dag H. Wanvik added a comment -

          Thanks for looking at this, Bryan!
          I agree, I intend to change the names to "window" all over;
          I only shortened it because the old windowing code is still in the code base (the present patch doesn't remove it except for in the grammar), intention being that it would be easy to see what's old and what's new.

          Show
          Dag H. Wanvik added a comment - Thanks for looking at this, Bryan! I agree, I intend to change the names to "window" all over; I only shortened it because the old windowing code is still in the code base (the present patch doesn't remove it except for in the grammar), intention being that it would be easy to see what's old and what's new.
          Hide
          Bryan Pendleton added a comment -

          I read through your writeup in more detail – it looks great!

          Am I correct in understanding that the "window function node" classes would be the place
          where we could add additional window functions in the future, such as RANK, NTILE, or
          maybe moving averages and things like that?

          Regarding leaving the complete syntax present in the parser, but with parts commented out,
          what is the tradeoff between doing that, versus having the unsupported syntax variants be
          present and recognized by the parser, but throw some sort of "sorry, not yet implemented" exception?

          Regarding keeping the patch simpler for review, perhaps you could post two patches:
          1) the first patch would delete the old code that we aren't going to keep around any more
          2) the second patch would be your proposed new code, with all the function names, etc.
          as you actually intend them to be.

          That way, patch #2 would still be a manageable size (wouldn't include large diffs for removal
          of old code) but it would also be possible to apply both patches in sequence and see the
          proposed codeline as you intend it to really look ("window" rather than 'wdw", etc.)

          Anyway, now that I've read through your design notes, I'll try to find some time to read
          through the actual code soon.

          Show
          Bryan Pendleton added a comment - I read through your writeup in more detail – it looks great! Am I correct in understanding that the "window function node" classes would be the place where we could add additional window functions in the future, such as RANK, NTILE, or maybe moving averages and things like that? Regarding leaving the complete syntax present in the parser, but with parts commented out, what is the tradeoff between doing that, versus having the unsupported syntax variants be present and recognized by the parser, but throw some sort of "sorry, not yet implemented" exception? Regarding keeping the patch simpler for review, perhaps you could post two patches: 1) the first patch would delete the old code that we aren't going to keep around any more 2) the second patch would be your proposed new code, with all the function names, etc. as you actually intend them to be. That way, patch #2 would still be a manageable size (wouldn't include large diffs for removal of old code) but it would also be possible to apply both patches in sequence and see the proposed codeline as you intend it to really look ("window" rather than 'wdw", etc.) Anyway, now that I've read through your design notes, I'll try to find some time to read through the actual code soon.
          Hide
          Dag H. Wanvik added a comment -

          Thanks for looking at this, Bryan!

          Yes, that was indeed my intention for the "window function node" classes.

          Regarding the ordering of patches, what you propose makes sense, I
          guess I hesitated to do that at first since I wasn't sure how long it
          would take to replace the old code, and it's tedious to maintain
          sequences of dependent patches. But the removal patch would be quite stable,
          so I'll get on to that.

          Show
          Dag H. Wanvik added a comment - Thanks for looking at this, Bryan! Yes, that was indeed my intention for the "window function node" classes. Regarding the ordering of patches, what you propose makes sense, I guess I hesitated to do that at first since I wasn't sure how long it would take to replace the old code, and it's tedious to maintain sequences of dependent patches. But the removal patch would be quite stable, so I'll get on to that.
          Hide
          Dag H. Wanvik added a comment -

          Uploading patch derby-3634-remove, which removes the old windowing
          code so as to make the new patch able to use legible "window"
          identifiers wihtout risk of crashing with old code as well as make
          that patch smaller, as suggested by Bryan.

          This patch is essentially a "backwards merge" of DERBY-2998 mods, cf.
          http://svnbook.red-bean.com/en/1.5/svn-book.html#svn.branchmerge.basicmerging.undo

          svn merge -c -634057 https://svn.eu.apache.org/repos/asf/db/derby/code/trunk
          svn merge -c -633251 https://svn.eu.apache.org/repos/asf/db/derby/code/trunk
          svn merge -c -633067 https://svn.eu.apache.org/repos/asf/db/derby/code/trunk
          svn merge -c -633058 https://svn.eu.apache.org/repos/asf/db/derby/code/trunk
          svn merge -c -632992 https://svn.eu.apache.org/repos/asf/db/derby/code/trunk
          svn merge -c -632988 https://svn.eu.apache.org/repos/asf/db/derby/code/trunk
          svn merge -c -632494 https://svn.eu.apache.org/repos/asf/db/derby/code/trunk
          svn merge -c -601311 https://svn.eu.apache.org/repos/asf/db/derby/code/trunk
          svn merge -c -601309 https://svn.eu.apache.org/repos/asf/db/derby/code/trunk
          svn merge -c -601021 https://svn.eu.apache.org/repos/asf/db/derby/code/trunk

          plus some conflict resolutions and final svn deletes. Running
          regressions.

          Btw, if the new patch doesn't make the cut, it should be easy to "undo"
          this one again, reviving the old functionality

          Show
          Dag H. Wanvik added a comment - Uploading patch derby-3634-remove, which removes the old windowing code so as to make the new patch able to use legible "window" identifiers wihtout risk of crashing with old code as well as make that patch smaller, as suggested by Bryan. This patch is essentially a "backwards merge" of DERBY-2998 mods, cf. http://svnbook.red-bean.com/en/1.5/svn-book.html#svn.branchmerge.basicmerging.undo svn merge -c -634057 https://svn.eu.apache.org/repos/asf/db/derby/code/trunk svn merge -c -633251 https://svn.eu.apache.org/repos/asf/db/derby/code/trunk svn merge -c -633067 https://svn.eu.apache.org/repos/asf/db/derby/code/trunk svn merge -c -633058 https://svn.eu.apache.org/repos/asf/db/derby/code/trunk svn merge -c -632992 https://svn.eu.apache.org/repos/asf/db/derby/code/trunk svn merge -c -632988 https://svn.eu.apache.org/repos/asf/db/derby/code/trunk svn merge -c -632494 https://svn.eu.apache.org/repos/asf/db/derby/code/trunk svn merge -c -601311 https://svn.eu.apache.org/repos/asf/db/derby/code/trunk svn merge -c -601309 https://svn.eu.apache.org/repos/asf/db/derby/code/trunk svn merge -c -601021 https://svn.eu.apache.org/repos/asf/db/derby/code/trunk plus some conflict resolutions and final svn deletes. Running regressions. Btw, if the new patch doesn't make the cut, it should be easy to "undo" this one again, reviving the old functionality
          Hide
          Dag H. Wanvik added a comment -

          Regressions passed with the patch derby-3634-remove applied. I intend to commit this patch
          if there are no objections to my approach here.

          Show
          Dag H. Wanvik added a comment - Regressions passed with the patch derby-3634-remove applied. I intend to commit this patch if there are no objections to my approach here.
          Hide
          Rick Hillegas added a comment -

          +1 to your approach, Dag. I think it will make it easier to follow your new approach to modelling the window structures.

          Show
          Rick Hillegas added a comment - +1 to your approach, Dag. I think it will make it easier to follow your new approach to modelling the window structures.
          Hide
          Micky Li added a comment - - edited

          I am a user for derby.

          I don't know how to apply this patch. It seems that I need to checkout all sources codes and apply this patch then recompile all and jar them to derby.jar. But I don't think it's a good way to do for me.

          Will the new release of derby apply the patch and when will be released?

          Show
          Micky Li added a comment - - edited I am a user for derby. I don't know how to apply this patch. It seems that I need to checkout all sources codes and apply this patch then recompile all and jar them to derby.jar. But I don't think it's a good way to do for me. Will the new release of derby apply the patch and when will be released?
          Hide
          Bryan Pendleton added a comment -

          Hi Micky. Your general understanding of how to use the patch is correct. It's not hard, but you have
          to be comfortable with the overall concepts of working with Java source code. You can find more
          information about working directly with the Derby source code here:
          http://db.apache.org/derby/dev/derby_source.html
          http://wiki.apache.org/db-derby/ForNewDevelopers

          Successful usage of a patch in a variety of environments increases the likelihood that
          it will be committed to Derby and incorporated in future releases.

          Show
          Bryan Pendleton added a comment - Hi Micky. Your general understanding of how to use the patch is correct. It's not hard, but you have to be comfortable with the overall concepts of working with Java source code. You can find more information about working directly with the Derby source code here: http://db.apache.org/derby/dev/derby_source.html http://wiki.apache.org/db-derby/ForNewDevelopers Successful usage of a patch in a variety of environments increases the likelihood that it will be committed to Derby and incorporated in future releases.
          Hide
          Micky Li added a comment -

          Hi, does this patch only be applied to trunk at svn?

          I checkout two source codes, one is from trunk, the other is from branch 10.5.
          Only the codes from trunk will be compiled after applying the patch. And this trunk is 10.6 beta which is not suitable for my derby database (10.5)

          When I apply the patch to branch 10.5 and run Ant to compile it, there are some errors I don't know how to solve as following

          ====

          compile:
          [javac] Compiling 417 source files to C:\eclipse-galileo\workspace\derby-10.5\classes
          [javac] C:\eclipse-galileo\workspace\derby-10.5\java\engine\org\apache\derby\impl\sql\compile\ResultColumnList.java:4223: cannot find symbol
          [javac] symbol : method isWindowFunction()
          [javac] location: class org.apache.derby.impl.sql.compile.ResultColumn
          [javac] if (rc.isWindowFunction()){
          [javac] ^
          [javac] C:\eclipse-galileo\workspace\derby-10.5\java\engine\org\apache\derby\impl\sql\compile\ResultColumnList.java:4238: cannot find symbol
          [javac] symbol : method isWindowFunction()
          [javac] location: class org.apache.derby.impl.sql.compile.ResultColumn
          [javac] if (rc.isWindowFunction()){
          [javac] ^
          [javac] 2 errors

          BUILD FAILED
          ====

          Is any suggesstion?

          Show
          Micky Li added a comment - Hi, does this patch only be applied to trunk at svn? I checkout two source codes, one is from trunk, the other is from branch 10.5. Only the codes from trunk will be compiled after applying the patch. And this trunk is 10.6 beta which is not suitable for my derby database (10.5) When I apply the patch to branch 10.5 and run Ant to compile it, there are some errors I don't know how to solve as following ==== compile: [javac] Compiling 417 source files to C:\eclipse-galileo\workspace\derby-10.5\classes [javac] C:\eclipse-galileo\workspace\derby-10.5\java\engine\org\apache\derby\impl\sql\compile\ResultColumnList.java:4223: cannot find symbol [javac] symbol : method isWindowFunction() [javac] location: class org.apache.derby.impl.sql.compile.ResultColumn [javac] if (rc.isWindowFunction()){ [javac] ^ [javac] C:\eclipse-galileo\workspace\derby-10.5\java\engine\org\apache\derby\impl\sql\compile\ResultColumnList.java:4238: cannot find symbol [javac] symbol : method isWindowFunction() [javac] location: class org.apache.derby.impl.sql.compile.ResultColumn [javac] if (rc.isWindowFunction()){ [javac] ^ [javac] 2 errors BUILD FAILED ==== Is any suggesstion?
          Hide
          Bryan Pendleton added a comment -

          Hi Micky,

          Currently, the patch only applies to the trunk.

          You can use the trunk code with your current 10.5 database as follows:
          1) Make a backup of your 10.5 database and restore it to a testing location (IMPORTANT!)
          2) When you run the modified, patched, trunk software to access your test copy
          of your database, pass -Dderby.database.allowPreReleaseUpgrade=true

          This special flag tells the Derby software that it's OK to open your database using
          the unreleased trunk code, and it will automatically be upgraded to 10.6 trunk format.
          This automatic upgrade, which cannot be undone, is why it is CRITICAL that you
          do this with a testing copy of your database, NOT with your production data.

          If these instructions are confusing, please let us know and I can try to explain some more.

          Show
          Bryan Pendleton added a comment - Hi Micky, Currently, the patch only applies to the trunk. You can use the trunk code with your current 10.5 database as follows: 1) Make a backup of your 10.5 database and restore it to a testing location (IMPORTANT!) 2) When you run the modified, patched, trunk software to access your test copy of your database, pass -Dderby.database.allowPreReleaseUpgrade=true This special flag tells the Derby software that it's OK to open your database using the unreleased trunk code, and it will automatically be upgraded to 10.6 trunk format. This automatic upgrade, which cannot be undone, is why it is CRITICAL that you do this with a testing copy of your database, NOT with your production data. If these instructions are confusing, please let us know and I can try to explain some more.
          Hide
          Micky Li added a comment -

          Dear Bryan,

          After apply the patch and use it as you said "pass -Dderby.database.allowPreReleaseUpgrade=true" to run,
          there is syntax error as following, "語法錯誤:Encountered "(" at line 1, column 41。" (in chinese)
          the error may be translated to "Syntax error: Encountered "(" at line 1, column 41." in english.

          the SQL query string is

          SELECT * FROM ( SELECT ROW_NUMBER() OVER() AS rownum, event_log.* FROM event_log WHERE event_log.id<=2147483647) AS TR WHERE rownum >= 2189 AND rownum <= 2288

          Notice that the SQL query string just doesn't include the "ORDER BY" clause and cause error.
          Before apply the patch, the SQL query string works well.

          The trunk revision I checkout is 819006.

          How to solve this?

          Thanks your help in advance.

          Show
          Micky Li added a comment - Dear Bryan, After apply the patch and use it as you said "pass -Dderby.database.allowPreReleaseUpgrade=true" to run, there is syntax error as following, "語法錯誤:Encountered "(" at line 1, column 41。" (in chinese) the error may be translated to "Syntax error: Encountered "(" at line 1, column 41." in english. the SQL query string is — SELECT * FROM ( SELECT ROW_NUMBER() OVER() AS rownum, event_log.* FROM event_log WHERE event_log.id<=2147483647) AS TR WHERE rownum >= 2189 AND rownum <= 2288 — Notice that the SQL query string just doesn't include the "ORDER BY" clause and cause error. Before apply the patch, the SQL query string works well. The trunk revision I checkout is 819006. How to solve this? Thanks your help in advance.
          Hide
          Knut Anders Hatlen added a comment -

          Which patch did you apply? derby-3634-remove.diff removes the support for ROW_NUMBER from trunk in preparation for the fix, so that it will be easier to understand the final patch that will add a completely new implementation of ROW_NUMBER later. If this was the patch you tried, you'll probably have more luck with derby-3634-b.diff, which is a proof of concept for the planned fix.

          Show
          Knut Anders Hatlen added a comment - Which patch did you apply? derby-3634-remove.diff removes the support for ROW_NUMBER from trunk in preparation for the fix, so that it will be easier to understand the final patch that will add a completely new implementation of ROW_NUMBER later. If this was the patch you tried, you'll probably have more luck with derby-3634-b.diff, which is a proof of concept for the planned fix.
          Hide
          Dag H. Wanvik added a comment -

          Hi Micky, I upload derby-3634-c, a slight revision of the proof-of-concept patch (not for production!) which applies
          cleanly against the current trunk at svn (@819907) , so that you may try it out.
          I am currently running full regression tests on it.

          Show
          Dag H. Wanvik added a comment - Hi Micky, I upload derby-3634-c, a slight revision of the proof-of-concept patch (not for production!) which applies cleanly against the current trunk at svn (@819907) , so that you may try it out. I am currently running full regression tests on it.
          Hide
          Micky Li added a comment -

          @Knut
          Thank for your help.
          I just used the wrong patch derby-3634-remove.diff as you said. After I apply derby-3634-b.diff, SQL query works well but can't be used for "ORDER BY" query. It show error as later description.

          @Dag
          Thank for your help.
          When I use browser to open http://svn.apache.org/repos/asf/db/derby/code/trunk/, it shows 819978 revision. But I use the same link in Eclipse SVN Repository, the trunk revision always shows 819006 from yesterday even I refresh it. I don't know why ..., and I also use TortoiseSVN (Windows version) to explore that link, it still shows 819006 at trunk and 819547 at branch in Repository Browser of TortoiseSVN.

          After apply the derby-3634-c.diff patch, it compiles and jar OK. The SQL query string as I mentioned at 28/Sep/09 09:02 PM works well.
          My target is to use ROW_NUMBER() with ORDER BY in one SQL query, but there shows a error as following:
          'Syntax error: Encountered "ORDER" at line 1, column 113'

          the SQL query string is

          SELECT * FROM ( SELECT ROW_NUMBER() OVER() AS rownum, event_log.* FROM event_log WHERE event_log.id<=2147483647 ORDER BY id DESC) AS TR WHERE rownum >= 2189 AND rownum <= 2288

          It clearly that the syntax error is caused by the ORDER which is added. But I think the derby-3634-c.diff patch is to solve this problem, am I right?

          Is there something I missed?

          Show
          Micky Li added a comment - @Knut Thank for your help. I just used the wrong patch derby-3634-remove.diff as you said. After I apply derby-3634-b.diff, SQL query works well but can't be used for "ORDER BY" query. It show error as later description. @Dag Thank for your help. When I use browser to open http://svn.apache.org/repos/asf/db/derby/code/trunk/ , it shows 819978 revision. But I use the same link in Eclipse SVN Repository, the trunk revision always shows 819006 from yesterday even I refresh it. I don't know why ..., and I also use TortoiseSVN (Windows version) to explore that link, it still shows 819006 at trunk and 819547 at branch in Repository Browser of TortoiseSVN. After apply the derby-3634-c.diff patch, it compiles and jar OK. The SQL query string as I mentioned at 28/Sep/09 09:02 PM works well. My target is to use ROW_NUMBER() with ORDER BY in one SQL query, but there shows a error as following: 'Syntax error: Encountered "ORDER" at line 1, column 113' the SQL query string is — SELECT * FROM ( SELECT ROW_NUMBER() OVER() AS rownum, event_log.* FROM event_log WHERE event_log.id<=2147483647 ORDER BY id DESC) AS TR WHERE rownum >= 2189 AND rownum <= 2288 — It clearly that the syntax error is caused by the ORDER which is added. But I think the derby-3634-c.diff patch is to solve this problem, am I right? Is there something I missed?
          Hide
          Dag H. Wanvik added a comment -

          The problem here is that you have an ORDER BY in a sub-select, which
          is currently not allowed by Derby. ORDER BY is only allowed on the
          outer SELECT. This patch does allow you to use ROW_NUMBER() in the
          order by clause, but this is not what you want, it seems.

          E.g. SELECT ... ORDER BY ROW_NUMBER() OVER ()
          is allowed by the patch

          I plan to work on relaxing this restriction as part of the 10.6 work.

          You could probably achieve what you want by using the new OFFSET/FETCH
          FIRST syntax introduced in 10.5, cf.

          http://db.apache.org/derby/docs/10.5/ref/rrefsqljoffsetfetch.html

          Show
          Dag H. Wanvik added a comment - The problem here is that you have an ORDER BY in a sub-select, which is currently not allowed by Derby. ORDER BY is only allowed on the outer SELECT. This patch does allow you to use ROW_NUMBER() in the order by clause, but this is not what you want, it seems. E.g. SELECT ... ORDER BY ROW_NUMBER() OVER () is allowed by the patch I plan to work on relaxing this restriction as part of the 10.6 work. You could probably achieve what you want by using the new OFFSET/FETCH FIRST syntax introduced in 10.5, cf. http://db.apache.org/derby/docs/10.5/ref/rrefsqljoffsetfetch.html
          Hide
          Dag H. Wanvik added a comment - - edited

          Committed derby-3634-remove-2 as svn 820483 and 820494.

          Show
          Dag H. Wanvik added a comment - - edited Committed derby-3634-remove-2 as svn 820483 and 820494.
          Hide
          Dag H. Wanvik added a comment -

          Note that the proof-of-concept patch is no longer applicable on trunk after derby-3634-remove-2 was committed. Update to svn 820482 if you need it. A new version of derby-3634-c with the proper window naming will appear soon, meanwhile ROW_NUMBER() is unavailable on trunk.

          Show
          Dag H. Wanvik added a comment - Note that the proof-of-concept patch is no longer applicable on trunk after derby-3634-remove-2 was committed. Update to svn 820482 if you need it. A new version of derby-3634-c with the proper window naming will appear soon, meanwhile ROW_NUMBER() is unavailable on trunk.
          Hide
          Micky Li added a comment -

          Thank you, Dag.

          The OFFSET/FETCH function is suitable for my application. It works well. I just wanted to use LIMIT and searched results in ROW_NUMBER of derby before.

          Now I wonder what's the different between them? And which one has better efficiency in core codes?

          Show
          Micky Li added a comment - Thank you, Dag. The OFFSET/FETCH function is suitable for my application. It works well. I just wanted to use LIMIT and searched results in ROW_NUMBER of derby before. Now I wonder what's the different between them? And which one has better efficiency in core codes?
          Hide
          Dag H. Wanvik added a comment -

          The row_number() is part of SQL's OLAP machinery and has been in the standard since 1999, I think.
          Until SQL 008 added the new OFFSET/FETCH FIRST syntax, there was no way to achieve "LIMIT" functionality via standard SQL without using the rather more indirect ROW_NUMBER window function.
          If the underlying result set is not materialized (depends on the actual query), the OFFSET/FETCH FIRST is better, because it will stop reading rows from the table once the the # of fetch rows have been seen. For the time being, a WHERE predicate on ROW_NUMBER() is not optimized, so as long as you keep draining the result set, all rows from the underlying (sub)query will be read. There is currently no optimization for skipping (OFFSET); all rows are read internally but discarded until we hit the offset.

          Show
          Dag H. Wanvik added a comment - The row_number() is part of SQL's OLAP machinery and has been in the standard since 1999, I think. Until SQL 008 added the new OFFSET/FETCH FIRST syntax, there was no way to achieve "LIMIT" functionality via standard SQL without using the rather more indirect ROW_NUMBER window function. If the underlying result set is not materialized (depends on the actual query), the OFFSET/FETCH FIRST is better, because it will stop reading rows from the table once the the # of fetch rows have been seen. For the time being, a WHERE predicate on ROW_NUMBER() is not optimized, so as long as you keep draining the result set, all rows from the underlying (sub)query will be read. There is currently no optimization for skipping (OFFSET); all rows are read internally but discarded until we hit the offset.
          Hide
          Dag H. Wanvik added a comment -

          Uploading a cleaned up version of the last prototype patch. Please see notes in
          derby-3634-newimpl-1.txt attached for details.

          This version is fairly close to a committable state, I hope.
          Comments are much appreciated!

          Show
          Dag H. Wanvik added a comment - Uploading a cleaned up version of the last prototype patch. Please see notes in derby-3634-newimpl-1.txt attached for details. This version is fairly close to a committable state, I hope. Comments are much appreciated!
          Hide
          Dag H. Wanvik added a comment -

          Uploading derby-3634-newimpl-2, which just adds a metadata test fixture for ROW_NUMBER() to OLAPTest, and fixes some end-of-line blanks/too long lines.

          Show
          Dag H. Wanvik added a comment - Uploading derby-3634-newimpl-2, which just adds a metadata test fixture for ROW_NUMBER() to OLAPTest, and fixes some end-of-line blanks/too long lines.
          Hide
          Knut Anders Hatlen added a comment -

          Hi Dag,
          I applied the patch and tried some random queries. Most of them worked as expected, but some of them caused assert failures:

          ij> values row_number() over ();
          ERROR XJ001: Java exception: 'ASSERT FAILED cannot get interface without operand: org.apache.derby.shared.common.sanity.AssertFailure'.

          ij> select * from t where exists (select row_number() over () from t);
          ERROR XJ001: Java exception: 'ASSERT FAILED generatedRef is null. replaceCallsWithColumnReferences() has not been called on this WindowFunctionNode. Make surethe node is under a ResultColumn as expected.: org.apache.derby.shared.common.sanity.AssertFailure'.

          ij> select * from t where row_number() over () > 3;
          ERROR XJ001: Java exception: 'ASSERT FAILED cannot get interface without operand: org.apache.derby.shared.common.sanity.AssertFailure'.

          ij> select row_number() over (), count from t group by row_number() over ();
          ERROR XJ001: Java exception: 'ASSERT FAILED generatedRef is null. replaceCallsWithColumnReferences() has not been called on this WindowFunctionNode. Make surethe node is under a ResultColumn as expected.: org.apache.derby.shared.common.sanity.AssertFailure'.

          ij> select count from t group by row_number() over ();
          ERROR XJ001: Java exception: 'ASSERT FAILED cannot get interface without operand: org.apache.derby.shared.common.sanity.AssertFailure'.

          Show
          Knut Anders Hatlen added a comment - Hi Dag, I applied the patch and tried some random queries. Most of them worked as expected, but some of them caused assert failures: ij> values row_number() over (); ERROR XJ001: Java exception: 'ASSERT FAILED cannot get interface without operand: org.apache.derby.shared.common.sanity.AssertFailure'. ij> select * from t where exists (select row_number() over () from t); ERROR XJ001: Java exception: 'ASSERT FAILED generatedRef is null. replaceCallsWithColumnReferences() has not been called on this WindowFunctionNode. Make surethe node is under a ResultColumn as expected.: org.apache.derby.shared.common.sanity.AssertFailure'. ij> select * from t where row_number() over () > 3; ERROR XJ001: Java exception: 'ASSERT FAILED cannot get interface without operand: org.apache.derby.shared.common.sanity.AssertFailure'. ij> select row_number() over (), count from t group by row_number() over (); ERROR XJ001: Java exception: 'ASSERT FAILED generatedRef is null. replaceCallsWithColumnReferences() has not been called on this WindowFunctionNode. Make surethe node is under a ResultColumn as expected.: org.apache.derby.shared.common.sanity.AssertFailure'. ij> select count from t group by row_number() over (); ERROR XJ001: Java exception: 'ASSERT FAILED cannot get interface without operand: org.apache.derby.shared.common.sanity.AssertFailure'.
          Hide
          Dag H. Wanvik added a comment -

          Thanks for test driving this, Knut! More cases to handle, it seems! I'm not sure all of these are even allowed, will consult spec.

          Show
          Dag H. Wanvik added a comment - Thanks for test driving this, Knut! More cases to handle, it seems! I'm not sure all of these are even allowed, will consult spec.
          Hide
          Dag H. Wanvik added a comment -

          Some comments on your cases, Knut.

          ij> values row_number() over ();

          This should be flagged. SQL 2003, section 4.14.9 Windowed tables says:
          "A window is a transient data structure associated with a <table
          expression>." A window function like ROW_NUMBER is always defined
          relative a window, and there is no <table expression> here and hence
          no window either.

          Explicitly, in 4.15.3: Window functions may only appear in the <select
          list> of a <query specification> or <select statement: single row>, or
          the <order by clause> of a simple table query.

          ij> select * from t where exists (select row_number() over () from t);

          Bug.

          ij> select * from t where row_number() over () > 3;

          Should be flagged, cf. 4.15.3 quoted above.

          ij> select row_number() over (), count from t group by row_number() over ();
          ij> select count from t group by row_number() over ();

          Both should be flagged, GROUP BY allows <ordinary grouping set> ->
          <grouping column reference>. Cf. 4.15.3 quoted above as well.

          Show
          Dag H. Wanvik added a comment - Some comments on your cases, Knut. ij> values row_number() over (); This should be flagged. SQL 2003, section 4.14.9 Windowed tables says: "A window is a transient data structure associated with a <table expression>." A window function like ROW_NUMBER is always defined relative a window, and there is no <table expression> here and hence no window either. Explicitly, in 4.15.3: Window functions may only appear in the <select list> of a <query specification> or <select statement: single row>, or the <order by clause> of a simple table query. ij> select * from t where exists (select row_number() over () from t); Bug. ij> select * from t where row_number() over () > 3; Should be flagged, cf. 4.15.3 quoted above. ij> select row_number() over (), count from t group by row_number() over (); ij> select count from t group by row_number() over (); Both should be flagged, GROUP BY allows <ordinary grouping set> -> <grouping column reference>. Cf. 4.15.3 quoted above as well.
          Hide
          Knut Anders Hatlen added a comment -

          Another thing I noticed, was a change in behaviour. Given this sequence of statements

          create table t (x int);
          insert into t values 1,2,3;
          select * from (select row_number() over (), x from t) s(r,x) where x > 2;

          the old implementation would return (3,3) whereas the new implementation returns (1,3), probably because the restriction (x>2) is now pushed down. This is not a bug, as the order of the rows in the inner select is undefined, but it might have some consequences that need to be handled when ORDER BY in sub-selects is implemented (DERBY-4397).

          Similarly, some may perhaps find it unintuitive that this query now returns 1 for all parameter values in (1,2,3), whereas it previously returned different values for different parameter values:

          select r from (select row_number() over (), x from t) s(r,x) where x = ?

          But again, it's not a bug.

          Show
          Knut Anders Hatlen added a comment - Another thing I noticed, was a change in behaviour. Given this sequence of statements create table t (x int); insert into t values 1,2,3; select * from (select row_number() over (), x from t) s(r,x) where x > 2; the old implementation would return (3,3) whereas the new implementation returns (1,3), probably because the restriction (x>2) is now pushed down. This is not a bug, as the order of the rows in the inner select is undefined, but it might have some consequences that need to be handled when ORDER BY in sub-selects is implemented ( DERBY-4397 ). Similarly, some may perhaps find it unintuitive that this query now returns 1 for all parameter values in (1,2,3), whereas it previously returned different values for different parameter values: select r from (select row_number() over (), x from t) s(r,x) where x = ? But again, it's not a bug.
          Hide
          Dag H. Wanvik added a comment -

          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.
          Show
          Dag H. Wanvik added a comment - 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.
          Hide
          Knut Anders Hatlen added a comment -

          Patch #3 appears to be missing some files, so I'm not able to compile with it.

          Show
          Knut Anders Hatlen added a comment - Patch #3 appears to be missing some files, so I'm not able to compile with it.
          Hide
          Dag H. Wanvik added a comment -

          Sorry, here is the full patch. Regressions ran ok.

          Show
          Dag H. Wanvik added a comment - Sorry, here is the full patch. Regressions ran ok.
          Hide
          Knut Anders Hatlen added a comment -

          After DERBY-3002, the patch needs to be refreshed because of a conflict in OLAPTest.

          Show
          Knut Anders Hatlen added a comment - After DERBY-3002 , the patch needs to be refreshed because of a conflict in OLAPTest.
          Hide
          Knut Anders Hatlen added a comment -

          Thanks Dag, it looks like the problems I found in the previous patch have been fixed. Noticed another problem with this query (inspired by DERBY-4391):

          ij> create table t(x int unique, y int);
          0 rows inserted/updated/deleted
          ij> insert into t values (1,2);
          1 row inserted/updated/deleted
          ij> select * from t where x > (select row_number() over () from sysibm.sysdummy1 union select 1-row_number() over () from sysibm.sysdummy1 where 1=0);
          ERROR 21000: Scalar subquery is only allowed to return a single row.

          But the scalar subquery does return a single row:

          ij> select row_number() over () from sysibm.sysdummy1 union select 1-row_number() over () from sysibm.sysdummy1 where 1=0;
          1
          --------------------
          1

          1 row selected

          Show
          Knut Anders Hatlen added a comment - Thanks Dag, it looks like the problems I found in the previous patch have been fixed. Noticed another problem with this query (inspired by DERBY-4391 ): ij> create table t(x int unique, y int); 0 rows inserted/updated/deleted ij> insert into t values (1,2); 1 row inserted/updated/deleted ij> select * from t where x > (select row_number() over () from sysibm.sysdummy1 union select 1-row_number() over () from sysibm.sysdummy1 where 1=0); ERROR 21000: Scalar subquery is only allowed to return a single row. But the scalar subquery does return a single row: ij> select row_number() over () from sysibm.sysdummy1 union select 1-row_number() over () from sysibm.sysdummy1 where 1=0; 1 -------------------- 1 1 row selected
          Hide
          Dag H. Wanvik added a comment -

          Thanks for looking again, Knut. The last error you see seems not introduced by this patch, you can see it in this query as well (without the patch):

          select * from t where i > (values 1 union select j from t where 1=0)

          It only happens if the outer selected column is unique; if I change the query to

          select * from t where j > (values 1 union select j from t where 1=0)

          it works as expected.

          Show
          Dag H. Wanvik added a comment - Thanks for looking again, Knut. The last error you see seems not introduced by this patch, you can see it in this query as well (without the patch): select * from t where i > (values 1 union select j from t where 1=0) It only happens if the outer selected column is unique; if I change the query to select * from t where j > (values 1 union select j from t where 1=0) it works as expected.
          Hide
          Dag H. Wanvik added a comment -

          Re-spinning this patch, resolving conflict in OLAPTest plus fixed an omission in the new GroupByColumn accept method (to heed any skipChildren).

          Show
          Dag H. Wanvik added a comment - Re-spinning this patch, resolving conflict in OLAPTest plus fixed an omission in the new GroupByColumn accept method (to heed any skipChildren).
          Hide
          Knut Anders Hatlen added a comment -

          OK, thanks, I'll file a separate bug then.

          Show
          Knut Anders Hatlen added a comment - OK, thanks, I'll file a separate bug then.
          Hide
          Knut Anders Hatlen added a comment -

          Logged as DERBY-4411.

          By the way, the syntax rules in SQL:2003, part 2, 14.1 <declare cursor> say

          18) If an <order by clause> is specified, then:
          a) Let OBC be the <order by clause>. Let NSK be the number of <sort specification>s in OBC. For each
          i between 1 (one) and NSK, let Ki be the <sort key> contained in the i-th <sort specification> in OBC.
          b) Each Ki shall contain a <column reference> and shall not contain a <subquery> or a <set function
          specification>.

          What exactly does this mean? ROW_NUMBER() OVER () doesn't contain a <column reference>, so should ORDER BY ROW_NUMBER() OVER () be rejected because of this? On the other hand, (ROW_NUMBER() OVER () + COL1) does contain a <column reference>, so ORDER BY (ROW_NUMBER() OVER () + COL1) would be fine? I'm sure I'm missing something, but what?

          Show
          Knut Anders Hatlen added a comment - Logged as DERBY-4411 . By the way, the syntax rules in SQL:2003, part 2, 14.1 <declare cursor> say 18) If an <order by clause> is specified, then: a) Let OBC be the <order by clause>. Let NSK be the number of <sort specification>s in OBC. For each i between 1 (one) and NSK, let Ki be the <sort key> contained in the i-th <sort specification> in OBC. b) Each Ki shall contain a <column reference> and shall not contain a <subquery> or a <set function specification>. What exactly does this mean? ROW_NUMBER() OVER () doesn't contain a <column reference>, so should ORDER BY ROW_NUMBER() OVER () be rejected because of this? On the other hand, (ROW_NUMBER() OVER () + COL1) does contain a <column reference>, so ORDER BY (ROW_NUMBER() OVER () + COL1) would be fine? I'm sure I'm missing something, but what?
          Hide
          Dag H. Wanvik added a comment -

          Yes, that's how I read it, too (no col.ref -> rejection). A window function is explicitly allowed inside an ORDER BY clause, though, cf. SQL 2003:2, section 4.15.3 "Window functions":

          "Window functions may only appear in the <select list> of a <query specification> or <select statement: single row>, or the <order by clause> of a simple table query."

          But Derby is more lenient, allowing <sort key>s without column references, so I think ROW_NUMBER doesn't introduce anything new in this respect, as you found in DERBY-4406 ("ORDER BY RANDOM()")

          Show
          Dag H. Wanvik added a comment - Yes, that's how I read it, too (no col.ref -> rejection). A window function is explicitly allowed inside an ORDER BY clause, though, cf. SQL 2003:2, section 4.15.3 "Window functions": "Window functions may only appear in the <select list> of a <query specification> or <select statement: single row>, or the <order by clause> of a simple table query." But Derby is more lenient, allowing <sort key>s without column references, so I think ROW_NUMBER doesn't introduce anything new in this respect, as you found in DERBY-4406 ("ORDER BY RANDOM()")
          Hide
          Knut Anders Hatlen added a comment -

          I agree. Derby already accepts sort keys without column references, so there's no reason why we should treat ROW_NUMBER differently from any other no-arg function in an ORDER BY clause.

          Show
          Knut Anders Hatlen added a comment - I agree. Derby already accepts sort keys without column references, so there's no reason why we should treat ROW_NUMBER differently from any other no-arg function in an ORDER BY clause.
          Hide
          Dag H. Wanvik added a comment -

          Committed derby-3634-newimpl-4 as svn 829155, resolving.

          Show
          Dag H. Wanvik added a comment - Committed derby-3634-newimpl-4 as svn 829155, resolving.

            People

            • Assignee:
              Dag H. Wanvik
              Reporter:
              Rick Hillegas
            • Votes:
              1 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development