Details
-
Bug
-
Status: Closed (View Workflow)
-
Minor
-
Resolution: Won't Fix
-
10.3.0
Description
Oracle example hr ddl script has following command:
CREATE SEQUENCE EMPLOYEES_SEQ |
INCREMENT BY 1 |
MAXVALUE 9999999999999999999999999999
|
MINVALUE 1
|
NOCACHE
|
;
|
Which fails in 10.3.0 with sql_mode='oracle'::
MariaDB [test]> CREATE SEQUENCE EMPLOYEES_SEQ
|
-> INCREMENT BY 1
|
-> MAXVALUE 9999999999999999999999999999
|
-> MINVALUE 1
|
-> NOCACHE
|
-> ;
|
ERROR 1064 (42000): 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 '9999999999999999999999999999
|
MINVALUE 1
|
NOCACHE' at line 3
|
The command succeeds when MAXVALUE is reduced down to 18x9s, i.e.:
MariaDB [test]> create sequence d18 maxvalue 999999999999999999;
|
Query OK, 0 rows affected (0.03 sec)
|
|
MariaDB [test]> create sequence d19 maxvalue 9999999999999999999;
|
ERROR 1064 (42000): 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 '9999999999999999999' at line 1
|
I am not sure that we should implement sequence with such high range, but we definitely should make sure that such commands succeed (maybe with a warning that maxvalue was adjusted). That should simplify typical migration and eventual testing with existing scripts.
Attachments
Issue Links
- relates to
-
MDEV-10139 Support for SEQUENCE objects
-
- Closed
-
-
MDEV-12533 sql_mode=ORACLE: Add support for database qualified sequence names in NEXTVAL and CURRVAL
-
- Closed
-
https://mariadb.com/kb/en/library/create-sequence/ gives the minimum/minimum value for each of the create sequence parameters:
MAXVALUE >= start
MAXVALUE > MINVALUE
START >= MINVALUE
MAXVALUE <= 9223372036854775806 (LONGLONG_MAX-1)
MINVALUE >= -9223372036854775807 (LONGLONG_MIN+1)
The reason for the error message is that MAXVALUE expects an integer as argument and the number 9999999999999999999999999999 is not an integer.
You get the same error for the given number if it's used as an argument for anything that expects an integer. I don't see any big reason for making MAXVALUE a special case