#1265 WHERE condition on ROW_NUMBER does not work

current-release
closed-fixed
1
2012-11-01
2012-10-30
rsteppac
No

Where conditions on the generated row number generated by ROW_NUMBER() OVER() does not work for lower bounds > 0.

Assuming 5 records matching the SELECT criteria...

SELECT * FROM
(
SELECT ROW_NUMBER() OVER() as row_num, inner.* FROM
(
SELECT ...
) AS inner
) AS foo
WHERE row_num > 0 and row_num <= 5;

returns 5 records. 5 records were expected.

Changing the WHERE condition to

WHERE row_num > 2 and row_num <= 4

returns 0 records. 2 records (row_number 3 and 4) were expected.

Discussion

  • rsteppac

    rsteppac - 2012-10-30

    In the attached example, change the where condition to reproduce the problem

     
  • rsteppac

    rsteppac - 2012-10-30

    OS is Ubuntu linux 11.04
    JDK is Oracle jdk1.6.0_31

     
  • Fred Toussi

    Fred Toussi - 2012-10-30

    Thanks for reporting.
    in this SELECT, the optimiser removes the main SELECTand pushes the condition into the first inner select. This will not work for ROW_NUMBER() > X.
    Will fix in the next snapshot.

     
  • Fred Toussi

    Fred Toussi - 2012-10-30
    • assigned_to: nobody --> fredt
    • status: open --> open-accepted
     
  • Fred Toussi

    Fred Toussi - 2012-11-01
    • priority: 5 --> 1
    • status: open-accepted --> closed-fixed
     

Log in to post a comment.

Get latest updates about Open Source Projects, Conferences and News.

Sign up for the SourceForge newsletter:

JavaScript is required for this form.





No, thanks