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

(CREATE|ALTER) SEQUENCE / SETVAL: only integer literal accepted as parameter

    XMLWordPrintable

Details

    Description

      MariaDB's SETVAL does not accept subqueries or variables as arguments.

      SELECT @max_value := MAX(`Id`) FROM `test_table`;
      SELECT setval(`test_sequence`, @max_value, true);
      

      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 '@max_value, true)' at line 1
      

      https://dbfiddle.uk/?rdbms=mariadb_10.3&fiddle=7268e0c79e88fcf13ed77fc050c8116a

      I also tried with:

      SELECT setval(`test_sequence`, (SELECT MAX(`Id`) FROM `test_table`), true);
      

      and

      SELECT setval(`test_sequence`, MAX(`Id`), true) FROM `test_table`;
      

      also

      SELECT setval(`test_sequence`, max_table.Maximum, true) FROM (SELECT MAX(Id) as Maximum FROM `test_table`) as max_table;
      

      `ALTER SEQUENCE` and `CREATE SEQUENCE` give the same error when using queries or variables as `START` parameter.

      Is there any way to specify an start value for sequence that is the output of another query?

      I'm doing a migration code where we are going to introduce a couple of sequences and after a massive update I would like to `SELECT` the value where the sequence has to start. This will happen just ONCE.

      Is this behaviour by design? If so, I think documentation on those 3 statements has to be explicit about this.

      Regards

      Attachments

        Issue Links

          Activity

            People

              anel Anel Husakovic
              precalde Pablo Recalde
              Votes:
              0 Vote for this issue
              Watchers:
              4 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.