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

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

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

          relates to

          Task - A task that needs to be done. MDEV-10139 Support for SEQUENCE objects

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-33666 MariaDB 11.x - ALTER SEQUENCE RESTART WITH not working?

          • Major - Major loss of function.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            anel Anel Husakovic added a comment -

            Hi,
            it seems like that second parameter of setval() as well as START option in CREATE SEQUENCE has to be number, so it shouldn't support subqueries or parameters but I agree that it should be stated a restriction in documentation page, right greenman ?
            But in general I think this is not a bug and this question maybe should be addressed on KB as a question, although I think I already answered it and I don't know workaround in you case.

            anel Anel Husakovic added a comment - Hi, it seems like that second parameter of setval() as well as START option in CREATE SEQUENCE has to be number, so it shouldn't support subqueries or parameters but I agree that it should be stated a restriction in documentation page , right greenman ? But in general I think this is not a bug and this question maybe should be addressed on KB as a question , although I think I already answered it and I don't know workaround in you case.
            precalde Pablo Recalde added a comment - - edited

            I did workarround this limitation with c# code. But I didn't find a way of doing it using SQL only.

            precalde Pablo Recalde added a comment - - edited I did workarround this limitation with c# code. But I didn't find a way of doing it using SQL only.
            anel Anel Husakovic added a comment -

            Only allowed type is the number, so in sql I think there is no workaround for this.
            Thanks for pointing this, we will update documentation according to your inputs.

            anel Anel Husakovic added a comment - Only allowed type is the number, so in sql I think there is no workaround for this. Thanks for pointing this, we will update documentation according to your inputs.
            greenman Ian Gilfillan added a comment -

            I've documented the limitation.

            greenman Ian Gilfillan added a comment - I've documented the limitation.
            precalde Pablo Recalde added a comment -

            Thank you @Anel Husakovic @Ian Gilfillan I see you've added a statement on SETVAL documentation making this limitation explicit. I would like to suggest you to add the same statement on CREATE and ALTER sequence documentation. Thanks

            precalde Pablo Recalde added a comment - Thank you @Anel Husakovic @Ian Gilfillan I see you've added a statement on SETVAL documentation making this limitation explicit. I would like to suggest you to add the same statement on CREATE and ALTER sequence documentation. Thanks
            precalde Pablo Recalde added a comment -

            Also noted, next time I'll open a KB question. Regards

            precalde Pablo Recalde added a comment - Also noted, next time I'll open a KB question. Regards

            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.