Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: None
    • Labels:

      Description

      SQL:2008 introduced OFFSET and FETCH; LIMIT is a similar clause to do the same thing.

      Postgres supports both; MySQL just LIMIT; SQL Server supports OFFSET/FETCH.

      The rest of this description is from PostgreSQL's site http://www.postgresql.org/docs/8.4/static/sql-select.html#SQL-LIMIT

      SELECT ...
      [ ORDER BY expression ]
      [ LIMIT

      { count | ALL } ]
      [ OFFSET start [ ROW | ROWS ] ]
      [ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY ]

      The LIMIT clause consists of two independent sub-clauses:

      LIMIT { count | ALL }

      OFFSET start
      count specifies the maximum number of rows to return, while start specifies the number of rows to skip before starting to return rows. When both are specified, start rows are skipped before starting to count the count rows to be returned.

      If the count expression evaluates to NULL, it is treated as LIMIT ALL, i.e., no limit. If start evaluates to NULL, it is treated the same as OFFSET 0.

      SQL:2008 introduced a different syntax to achieve the same thing, which PostgreSQL also supports. It is:

      OFFSET start

      { ROW | ROWS }
      FETCH { FIRST | NEXT } [ count ] { ROW | ROWS }

      ONLY

      Both clauses are optional, but if present the OFFSET clause must come before the FETCH clause. ROW and ROWS as well as FIRST and NEXT are noise words that don't influence the effects of these clauses. In this syntax, when using expressions other than simple constants for start or count, parentheses will be necessary in most cases. If count is omitted in FETCH, it defaults to 1.

      ---------------- Imported from GitHub ----------------
      Url: https://github.com/julianhyde/optiq/issues/43
      Created by: julianhyde
      Labels:
      Created at: Fri Jun 07 23:43:59 CEST 2013
      State: closed

        Issue Links

          Activity

          Hide
          github-import GitHub Import added a comment -

          [Date: Fri Jun 07 23:51:59 CEST 2013, Author: julianhyde]

          Note that in SQL:2011 these features are called <result offset clause> and <fetch first clause>.

          Show
          github-import GitHub Import added a comment - [Date: Fri Jun 07 23:51:59 CEST 2013, Author: julianhyde ] Note that in SQL:2011 these features are called <result offset clause> and <fetch first clause>.
          Hide
          github-import GitHub Import added a comment -

          [Date: Sun Aug 18 22:45:44 CEST 2013, Author: tnachen]

          Hey Julian, is this implemented yet in optiq? I was trying to get limit working in Drill but realize I don't see a LimitRel implemented in Optiq in the version we're referencing.

          Show
          github-import GitHub Import added a comment - [Date: Sun Aug 18 22:45:44 CEST 2013, Author: tnachen ] Hey Julian, is this implemented yet in optiq? I was trying to get limit working in Drill but realize I don't see a LimitRel implemented in Optiq in the version we're referencing.
          Hide
          github-import GitHub Import added a comment -

          [Date: Mon Aug 19 19:29:01 CEST 2013, Author: julianhyde]

          It's still on the backlog. It's not hard, so I'll try to get to it in the next week.

          Note that the JDBC driver already implements java.sql.Statement.setMaxRows(), but does it inefficiently on the client side. I would improve that implementation once this feature is done.

          Limit and Sort often go together. Rather than adding a new relational operator LimitRel, I am considering adding "start" and "count" parameters to SortRel. In the unlikely even that you want a limit without a sort, you can sort on 0 columns.

          Of course an implementation such as Drill would be free to implement a SortRel with limit as two separate physical operators.

          Show
          github-import GitHub Import added a comment - [Date: Mon Aug 19 19:29:01 CEST 2013, Author: julianhyde ] It's still on the backlog. It's not hard, so I'll try to get to it in the next week. Note that the JDBC driver already implements java.sql.Statement.setMaxRows(), but does it inefficiently on the client side. I would improve that implementation once this feature is done. Limit and Sort often go together. Rather than adding a new relational operator LimitRel, I am considering adding "start" and "count" parameters to SortRel. In the unlikely even that you want a limit without a sort, you can sort on 0 columns. Of course an implementation such as Drill would be free to implement a SortRel with limit as two separate physical operators.
          Hide
          github-import GitHub Import added a comment -

          [Date: Mon Aug 19 20:59:08 CEST 2013, Author: tnachen]

          Sounds good, are you going to work on this soon? If not I can try to add this in Optiq since I was wanting to learn more about it since we've started using it in Drill. Let me know!

          Show
          github-import GitHub Import added a comment - [Date: Mon Aug 19 20:59:08 CEST 2013, Author: tnachen ] Sounds good, are you going to work on this soon? If not I can try to add this in Optiq since I was wanting to learn more about it since we've started using it in Drill. Let me know!
          Hide
          github-import GitHub Import added a comment -

          [Date: Mon Aug 19 21:56:53 CEST 2013, Author: julianhyde]

          It would be easier if I added support in Optiq (including support for parsing SQL, validation, and a default implementation). But I won't get to this for at least a week.

          But, it would be great if you could implement this in Drill. In your own fork of Optiq, you could add fields to SortRel:

          public RexLiteral rowOffset;
          public RexLiteral rowCount;

          and then add code in DrillSortRule.java to create a DrillLimitRel if these fields are not null.

          Also handle the case where SortRel.fieldExps and SortRel.collation are empty. That will occur when people use a LIMIT without an ORDER BY. You probably want to create a DrillLimitRel without an underlying DrillSortRel.

          After I've implemented the feature in Optiq, your code should work almost unchanged.

          Show
          github-import GitHub Import added a comment - [Date: Mon Aug 19 21:56:53 CEST 2013, Author: julianhyde ] It would be easier if I added support in Optiq (including support for parsing SQL, validation, and a default implementation). But I won't get to this for at least a week. But, it would be great if you could implement this in Drill. In your own fork of Optiq, you could add fields to SortRel: public RexLiteral rowOffset; public RexLiteral rowCount; and then add code in DrillSortRule.java to create a DrillLimitRel if these fields are not null. Also handle the case where SortRel.fieldExps and SortRel.collation are empty. That will occur when people use a LIMIT without an ORDER BY. You probably want to create a DrillLimitRel without an underlying DrillSortRel. After I've implemented the feature in Optiq, your code should work almost unchanged.
          Hide
          github-import GitHub Import added a comment -
          Show
          github-import GitHub Import added a comment - [Date: Thu Aug 22 00:46:25 CEST 2013, Author: julianhyde ] Fixed in https://github.com/julianhyde/optiq/commit/c12768bbc1566a56cf52bad927b07e1799e3d917 .

            People

            • Assignee:
              Unassigned
              Reporter:
              github-import GitHub Import
            • Votes:
              0 Vote for this issue
              Watchers:
              0 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development