Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-25430

ROW | ROWS should be a required keyword after OFFSET start

    XMLWordPrintable

Details

    Description

      Both the standard and MDEV-23908 description specify the syntax as

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

      It doesn't work in MariaDB, ROW|ROWS after OFFSET start is rejected:

      bb-10.6-refactor-limit-review 4bd13ff8d

      MariaDB [test]> select seq from seq_1_to_10 offset 5 rows fetch next 1 rows only;
      ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'rows fetch next 1 rows only' at line 1
      

      And it works without ROW[S]:

      MariaDB [test]> select seq from seq_1_to_10 offset 5 fetch next 1 rows only;
      +-----+
      | seq |
      +-----+
      |   6 |
      +-----+
      1 row in set (1.250 sec)
      

      In PostreSQL 13 it works both with and without ROWS:

      create table t1 (pk int primary key);
      insert into t1 values (1),(2),(3),(4);
      select pk from t1 offset 2 rows fetch next 1 rows only;
       
      4 rows affected
      pk
      3
      

      select pk from t1 offset 2 fetch next 1 rows only;
       
      pk
      3
      

      Maybe it's intentional, but then it needs to be documented as a deviation from the standard (a limitation, not an extension).

      Attachments

        Issue Links

          Activity

            People

              cvicentiu Vicențiu Ciorbaru
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.