Details
-
Bug
-
Status: Closed (View Workflow)
-
Minor
-
Resolution: Fixed
-
10.3(EOL), 10.4(EOL), 10.5
-
None
Description
Let's try to simulate behavior
MariaDB [d]> CREATE SEQUENCE a1 START WITH 5000 INCREMENT BY 1 MINVALUE=5000 MAXVALUE=5003; |
Query OK, 0 rows affected (0.026 sec) |
|
|
MariaDB [d]> SELECT LASTVAL(a1), NEXTVAL(a1); |
+-------------+-------------+ |
| LASTVAL(a1) | NEXTVAL(a1) |
|
+-------------+-------------+ |
| NULL | 5000 | |
+-------------+-------------+ |
1 row in set (0.001 sec) |
|
|
MariaDB [d]> SELECT LASTVAL(a1), NEXTVAL(a1); |
+-------------+-------------+ |
| LASTVAL(a1) | NEXTVAL(a1) |
|
+-------------+-------------+ |
| 5000 | 5001 |
|
+-------------+-------------+ |
1 row in set (0.000 sec) |
|
|
MariaDB [d]> FLUSH TABLES;
|
Query OK, 0 rows affected (0.000 sec) |
|
|
MariaDB [d]> SELECT LASTVAL(a1); # FLUSH TABLES shouldn't affect LASTVAL() - OK |
+-------------+
|
| LASTVAL(a1) |
|
+-------------+
|
| 5001 |
|
+-------------+
|
1 row in set (0.001 sec)
|
|
|
MariaDB [d]> SELECT NEXTVAL(a1); # Problematic behavior
|
ERROR 4084 (HY000): Sequence 'd.a1' has run out |
MariaDB [d]> SELECT LASTVAL(a1); # NEXTVAL() affects LASTVAL(), should it? |
+-------------+ |
| LASTVAL(a1) |
|
+-------------+ |
| NULL | |
+-------------+ |
1 row in set (0.000 sec) |
|
The same as above happens if we start the new session or restart the server.
So here 2 questions should be addressed:
1. How to open/count table/value of closed sequence, after FLUSH TABLES is invoked? Documentation nextval sequence should be updated.
Also, the error message ERROR 4084 (HY000): Sequence 'd.a1' has run out is the same for "empty" sequence (no cached value) as showed above as well as for NON-CYCLE sequence crossing MAXVALUE. Distinction between those should be done.
2. LASTVAL in the current situation (crossing MAXVALUE) is correct? If yes it should be documented saying that crossing the value for NEXTVAL is setting the function to NULL. If not, let's change it.