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

NEXTVAL should increment only once for a given row

    XMLWordPrintable

    Details

      Description

      According to discussion in comments in MDEV-10139 "If there are multiple instances of <next value expression>s specifying the same sequence generator within a single SQL-statement, all those instances return the same value for a given row processed by that SQL-statement."

      So SELECT below should show the same number twice, and it doesn't:

      MariaDB [test]> create or replace sequence x;
      Query OK, 0 rows affected (0.18 sec)
       
      MariaDB [test]> select nextval(x), nextval(x);
      +------------+------------+
      | nextval(x) | nextval(x) |
      +------------+------------+
      |          1 |          2 |
      +------------+------------+
      1 row in set (0.04 sec)
      

      Oracle has a lot of limitations of how NEXTVAL() can be used:
      https://docs.oracle.com/cd/A84870_01/doc/server.816/a76989/ch26.htm#4062
      In the case that Oracle doesn't support NEXTVAL(), we don't have return the lastvalue if it's too hard to achieve.
      For example when doing an ORDER BY or GROUP BY with NEXTVAL(), it's not always possible to ensure that all NEXTVAL()
      will return the same value for the same row combination. With current code we have:

       create sequence s1;
       SELECT NEXTVAL(s1),NEXTVAL(s1) as a from seq_1_to_2 order by a;
      +-------------+------+
      | NEXTVAL(s1) | a    |
      +-------------+------+
      |           1 |    2 |
      |           3 |    4 |
      +-------------+------+
      

      Don't think we have to fix the above, but we have to at least document this.

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              robertbindar Robert Bindar
              Reporter:
              anikitin Andrii Nikitin (Inactive)
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

                Dates

                Created:
                Updated: