Uploaded image for project: 'NHibernate [Moved to GitHub]'
  1. NH-2840

Improper SQL is generated for Take (pagination) for dialects which have UseMaxForLimit = true (like Oracle)

    Details

    • Type: Patch
    • Status: Closed
    • Priority: Trivial
    • Resolution: Fixed
    • Affects Version/s: 3.2.0.GA
    • Fix Version/s: 3.3.0.CR1
    • Component/s: Core
    • Sprint:

      Description

      QueryTakeParameterSpecification has a flaw in Bind because it does not take into account the absolute limit for certain dialects which have the flag UseMaxForLimit = true. The generated SQL does not return any results when executed for a query with Take(10).Skip(10) for example.

      select
      c1, c2
      from
      ( select
      row_.*,
      rownum rownum_
      from
      ( SELECT c1,c2
      FROM
      t1
      WHERE
      this_.Order_Date >= to_date('10/08/2011 21:00:00', 'DD/MM/YYYY HH24:MI:SS')
      this_.Order_Date <= to_date('11/08/2011 20:59:59', 'DD/MM/YYYY HH24:MI:SS')
      ORDER BY
      this_.Order_Date desc ) row_
      where
      rownum <=10)
      where
      rownum_ >10;

      The proper SQL must have rownum <= 20 clause instead of rownum <=10. The original QueryTakeParameterSpecification.Bind method always used the MaxRows corresponding to Take() without considering the dialect requirements.

      Attached is the patch which solves the above issue for all dialects which require absolute limit (Oracle included).

        Attachments

          Issue links

            Activity

              People

              • Assignee:
                flukefan Richard Brown
                Reporter:
                robert.mircea Robert Mircea
              • Votes:
                4 Vote for this issue
                Watchers:
                6 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved:

                  Who's Looking?