Derby
  1. Derby
  2. DERBY-4192

OFFSET and FETCH FIRST documentation improvement

    Details

    • Type: Improvement Improvement
    • Status: Closed
    • Priority: Minor Minor
    • Resolution: Fixed
    • Affects Version/s: 10.5.1.1
    • Fix Version/s: 10.6.1.0
    • Component/s: None
    • Labels:
      None
    • Urgency:
      Low
    • Issue & fix info:
      Newcomer

      Description

      I have a suggestion regarding the documentation for the OFFSET and FETCH FIRST documentation. On these documents, we have three SQL examples on the usage of these clauses. I suggest that we add a brief description of what each clause does.

      This might help the users to have a better understanding at first of how the said clauses work.

      Here are the examples with a possible description:

      – This query fetches the first row from T
      SELECT * FROM T FETCH FIRST ROW ONLY;

      – This query skips the first 10 rows and fetches the following 10.
      – It will fetch rows 11 through 20 (inclusive) from the table T, sorted by I
      SELECT * FROM T ORDER BY I OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;

      – This query skips the first 100 rows from T.
      – If the table has less than 101 records, then an empty result set is returned.
      SELECT * FROM T OFFSET 100 ROWS;

      1. derby-4192-2.patch
        1.0 kB
        Kim Haase
      2. rrefsqljoffsetfetch.html
        5 kB
        Kim Haase
      3. refderby.pdf
        1.52 MB
        Tiago R. Espinha
      4. derby-4192.patch
        1 kB
        Tiago R. Espinha
      5. derby-4192.patch
        1 kB
        Tiago R. Espinha

        Activity

        Hide
        Dag H. Wanvik added a comment -

        Thanks for this improvement, Tiago. +1

        This sentence could be misconstrued though;

        – It will fetch rows 11 through 20 (inclusive) from the table T, sorted by I

        The sorting happen before the row count filtering, so I think it would be better to say:

        – The statement will order T by I, and then fetch rows 11 through 20 (inclusive) of the sorted rows.

        In general, just returning a subset of the rows without ordering is not portable, unless the app
        does not care which rows go into the subset.

        Show
        Dag H. Wanvik added a comment - Thanks for this improvement, Tiago. +1 This sentence could be misconstrued though; – It will fetch rows 11 through 20 (inclusive) from the table T, sorted by I The sorting happen before the row count filtering, so I think it would be better to say: – The statement will order T by I, and then fetch rows 11 through 20 (inclusive) of the sorted rows. In general, just returning a subset of the rows without ordering is not portable, unless the app does not care which rows go into the subset.
        Hide
        Tiago R. Espinha added a comment -

        Great, Dag.

        I shall make it into a patch then, with the change that you suggested. That does make it clearer that the row filtering happens after the sorting.

        There is just one thing that I didn't follow. You said that returning a subset of the rows without ordering is not portable. What do you mean by this?

        I reckon that the row filtering ability is only really useful when there is sorting in place, but it works regardlessly of that, right?

        Show
        Tiago R. Espinha added a comment - Great, Dag. I shall make it into a patch then, with the change that you suggested. That does make it clearer that the row filtering happens after the sorting. There is just one thing that I didn't follow. You said that returning a subset of the rows without ordering is not portable. What do you mean by this? I reckon that the row filtering ability is only really useful when there is sorting in place, but it works regardlessly of that, right?
        Hide
        Dag H. Wanvik added a comment -

        A database may return the same rows in the same order every time, without any ordering indicated in the query. This could lead
        the application developer to rely on a certain subset of the rows (for example by 20 first by insertion order).
        On another database, a different set of rows might be returned
        due to another (equally valid) execution plan, making the application fail (if it relies on the first subset).

        Show
        Dag H. Wanvik added a comment - A database may return the same rows in the same order every time, without any ordering indicated in the query. This could lead the application developer to rely on a certain subset of the rows (for example by 20 first by insertion order). On another database, a different set of rows might be returned due to another (equally valid) execution plan, making the application fail (if it relies on the first subset).
        Hide
        Tiago R. Espinha added a comment -

        I get what you mean now. Do you think this worth mentioning on the OFFSET documentation as well?

        Show
        Tiago R. Espinha added a comment - I get what you mean now. Do you think this worth mentioning on the OFFSET documentation as well?
        Hide
        Tiago R. Espinha added a comment -

        I'm attaching a fix for this issue. I've added a small note below the examples, but it is my first time with this type of documentation, so please check if everything is well structured.

        Show
        Tiago R. Espinha added a comment - I'm attaching a fix for this issue. I've added a small note below the examples, but it is my first time with this type of documentation, so please check if everything is well structured.
        Hide
        Tiago R. Espinha added a comment -

        The other patch caused an error on compile. This one compiles cleanly.

        I'm also attaching the final PDF just for reference. (Refer to page 79)

        Show
        Tiago R. Espinha added a comment - The other patch caused an error on compile. This one compiles cleanly. I'm also attaching the final PDF just for reference. (Refer to page 79)
        Hide
        Kim Haase added a comment -

        Good work, Tiago! Thank you for taking this on. I'm attaching a suggested revised patch, derby-4192-2.patch, with some formatting and wording changes to conform to more usual technical writing usage. If they change the meaning, though, please fix them back again!

        The output HTML is in rrefsqljoffsetfetch.html.

        Show
        Kim Haase added a comment - Good work, Tiago! Thank you for taking this on. I'm attaching a suggested revised patch, derby-4192-2.patch, with some formatting and wording changes to conform to more usual technical writing usage. If they change the meaning, though, please fix them back again! The output HTML is in rrefsqljoffsetfetch.html.
        Hide
        Tiago R. Espinha added a comment -

        Thanks Kim, for the revisions. They look good to me so unless someone else objects to it, you can go ahead and commit the patch.

        Show
        Tiago R. Espinha added a comment - Thanks Kim, for the revisions. They look good to me so unless someone else objects to it, you can go ahead and commit the patch.
        Hide
        Kim Haase added a comment -

        Committed patch derby-4192-2.patch to documentation trunk at revision 770442. Thanks very much to Tiago Espinha for this contribution.

        Show
        Kim Haase added a comment - Committed patch derby-4192-2.patch to documentation trunk at revision 770442. Thanks very much to Tiago Espinha for this contribution.
        Hide
        Tiago R. Espinha added a comment -

        Thanks Kim, for revising and for committing. Closing the issue.

        Show
        Tiago R. Espinha added a comment - Thanks Kim, for revising and for committing. Closing the issue.

          People

          • Assignee:
            Tiago R. Espinha
            Reporter:
            Tiago R. Espinha
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Development