Details

    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

          Activity

            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

            monty Michael Widenius added a comment - 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

            People

              monty Michael Widenius
              anikitin Andrii Nikitin (Inactive)
              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.