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

              cvicentiu Vicențiu Ciorbaru
              anikitin Andrii Nikitin (Inactive)
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

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