[MDEV-20111] (CREATE|ALTER) SEQUENCE / SETVAL: only integer literal accepted as parameter Created: 2019-07-21  Updated: 2019-07-24  Resolved: 2019-07-22

Status: Closed
Project: MariaDB Server
Component/s: Sequences
Affects Version/s: 10.3.16
Fix Version/s: 10.3.16

Type: Bug Priority: Major
Reporter: Pablo Recalde Assignee: Anel Husakovic
Resolution: Not a Bug Votes: 0
Labels: contribution, need_feedback
Environment:

docker-linux

Issue Links:
Relates
relates to MDEV-10139 Support for SEQUENCE objects Closed

 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

 Comments   
Comment by Anel Husakovic [ 2019-07-22 ]

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.

Comment by Pablo Recalde [ 2019-07-22 ]

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

Comment by Anel Husakovic [ 2019-07-22 ]

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.

Comment by Ian Gilfillan [ 2019-07-22 ]

I've documented the limitation.

Comment by Pablo Recalde [ 2019-07-24 ]

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

Comment by Pablo Recalde [ 2019-07-24 ]

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

Generated at Thu Feb 08 08:56:54 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.