Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-10142 PL/SQL parser
  3. MDEV-12533

sql_mode=ORACLE: Add support for database qualified sequence names in NEXTVAL and CURRVAL

Details

    • 10.2.2-3, 10.2.2-1, 10.2.2-2, 10.2.2-4, 10.1.18

    Description

      The SQL standard syntax for NEXT VALUE FOR works for both non-qualified and qualified sequence names:

      SET sql_mode=ORACLE;
      		 
      DROP SEQUENCE IF EXISTS s1;
      		 
      CREATE SEQUENCE s1;
      		 
      SELECT NEXT VALUE FOR s1;
      		 
      SELECT s1.nextval;


      +-------------------+
      		 
      | NEXT VALUE FOR s1 |
      		 
      +-------------------+
      		 
      |                 1 |
      		 
      +-------------------+
      		 
      +------------+
      		 
      | s1.nextval |
      		 
      +------------+
      		 
      |          2 |
      		 
      +------------+


      SELECT NEXT VALUE FOR test.s1;


      +------------------------+
      		 
      | NEXT VALUE FOR test.s1 |
      		 
      +------------------------+
      		 
      |                      3 |
      		 
      +------------------------+

      Qualified sequence names also work with IBM DB2 syntax for PREVIOUS VALUE FOR

      SELECT PREVIOUS VALUE FOR test.s1;


      +----------------------------+
      		 
      | PREVIOUS VALUE FOR test.s1 |
      		 
      +----------------------------+
      		 
      |                          3 |
      		 
      +----------------------------+

      Under terms of this task we'll add support for qualified sequence names for Oracle syntax, to make these queries work:

      SELECT test.s1.nextval;


      SELECT test.s1.currval;

      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-12883 CREATE SEQUENCE with huge MAXVALUE

          • Minor - Minor loss of function, or other problem where easy workaround is present.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            bar Alexander Barkov created issue -
            bar Alexander Barkov made changes -
            Field Original Value New Value
            Parent MDEV-10142 [ 56873 ]
            Issue Type Task [ 3 ] Technical task [ 7 ]
            bar Alexander Barkov made changes -
            Description The SQL standard syntax for {{NEXT VALUE FOR}} and {{PREVIOUS VALUE FOR}} works for both non-qualified and qualified sequence names:
            {code:sql}
            SET sql_mode=ORACLE;
            DROP SEQUENCE IF EXISTS s1;
            CREATE SEQUENCE s1;
            SELECT NEXT VALUE FOR s1;
            SELECT s1.nextval;
            {code}
            {noformat}
            +-------------------+
            | NEXT VALUE FOR s1 |
            +-------------------+
            | 1 |
            +-------------------+
            +------------+
            | s1.nextval |
            +------------+
            | 2 |
            +------------+
            {noformat}
            {code:sql}
            SELECT NEXT VALUE FOR test.s1;
            {code}
            {noformat}
            +------------------------+
            | NEXT VALUE FOR test.s1 |
            +------------------------+
            | 3 |
            +------------------------+
            {noformat}
            Looks good.


            Under terms of this task we'll add support for qualified sequence names for Oracle syntax, to make these queries work:
            {code:sql}
            SELECT test.s1.nextval;
            {code}
            {noformat}
            {code:sql}
            SELECT test.s1.currval;
            {code}
            		The SQL standard syntax for {{NEXT VALUE FOR}} and {{PREVIOUS VALUE FOR}} works for both non-qualified and qualified sequence names:
            {code:sql}
            SET sql_mode=ORACLE;
            DROP SEQUENCE IF EXISTS s1;
            CREATE SEQUENCE s1;
            SELECT NEXT VALUE FOR s1;
            SELECT s1.nextval;
            {code}
            {noformat}
            +-------------------+
            | NEXT VALUE FOR s1 |
            +-------------------+
            | 1 |
            +-------------------+
            +------------+
            | s1.nextval |
            +------------+
            | 2 |
            +------------+
            {noformat}
            {code:sql}
            SELECT NEXT VALUE FOR test.s1;
            {code}
            {noformat}
            +------------------------+
            | NEXT VALUE FOR test.s1 |
            +------------------------+
            | 3 |
            +------------------------+
            {noformat}

            Qualified sequence names also work with IBM DB2 syntax for {{PREVIOUS VALUE FOR}}
            {code:sql}
            SELECT PREVIOUS VALUE FOR test.s1;
            {code}
            {noformat}
            +----------------------------+
            | PREVIOUS VALUE FOR test.s1 |
            +----------------------------+
            | 3 |
            +----------------------------+
            {noformat}

            Under terms of this task we'll add support for qualified sequence names for Oracle syntax, to make these queries work:
            {code:sql}
            SELECT test.s1.nextval;
            {code}
            {noformat}
            {code:sql}
            SELECT test.s1.currval;
            {code}
            bar Alexander Barkov made changes -
            Description The SQL standard syntax for {{NEXT VALUE FOR}} and {{PREVIOUS VALUE FOR}} works for both non-qualified and qualified sequence names:
            {code:sql}
            SET sql_mode=ORACLE;
            DROP SEQUENCE IF EXISTS s1;
            CREATE SEQUENCE s1;
            SELECT NEXT VALUE FOR s1;
            SELECT s1.nextval;
            {code}
            {noformat}
            +-------------------+
            | NEXT VALUE FOR s1 |
            +-------------------+
            | 1 |
            +-------------------+
            +------------+
            | s1.nextval |
            +------------+
            | 2 |
            +------------+
            {noformat}
            {code:sql}
            SELECT NEXT VALUE FOR test.s1;
            {code}
            {noformat}
            +------------------------+
            | NEXT VALUE FOR test.s1 |
            +------------------------+
            | 3 |
            +------------------------+
            {noformat}

            Qualified sequence names also work with IBM DB2 syntax for {{PREVIOUS VALUE FOR}}
            {code:sql}
            SELECT PREVIOUS VALUE FOR test.s1;
            {code}
            {noformat}
            +----------------------------+
            | PREVIOUS VALUE FOR test.s1 |
            +----------------------------+
            | 3 |
            +----------------------------+
            {noformat}

            Under terms of this task we'll add support for qualified sequence names for Oracle syntax, to make these queries work:
            {code:sql}
            SELECT test.s1.nextval;
            {code}
            {noformat}
            {code:sql}
            SELECT test.s1.currval;
            {code}
            		The SQL standard syntax for {{NEXT VALUE FOR}} works for both non-qualified and qualified sequence names:
            {code:sql}
            SET sql_mode=ORACLE;
            DROP SEQUENCE IF EXISTS s1;
            CREATE SEQUENCE s1;
            SELECT NEXT VALUE FOR s1;
            SELECT s1.nextval;
            {code}
            {noformat}
            +-------------------+
            | NEXT VALUE FOR s1 |
            +-------------------+
            | 1 |
            +-------------------+
            +------------+
            | s1.nextval |
            +------------+
            | 2 |
            +------------+
            {noformat}
            {code:sql}
            SELECT NEXT VALUE FOR test.s1;
            {code}
            {noformat}
            +------------------------+
            | NEXT VALUE FOR test.s1 |
            +------------------------+
            | 3 |
            +------------------------+
            {noformat}

            Qualified sequence names also work with IBM DB2 syntax for {{PREVIOUS VALUE FOR}}
            {code:sql}
            SELECT PREVIOUS VALUE FOR test.s1;
            {code}
            {noformat}
            +----------------------------+
            | PREVIOUS VALUE FOR test.s1 |
            +----------------------------+
            | 3 |
            +----------------------------+
            {noformat}

            Under terms of this task we'll add support for qualified sequence names for Oracle syntax, to make these queries work:
            {code:sql}
            SELECT test.s1.nextval;
            {code}
            {noformat}
            {code:sql}
            SELECT test.s1.currval;
            {code}
            bar Alexander Barkov made changes -
            Description The SQL standard syntax for {{NEXT VALUE FOR}} works for both non-qualified and qualified sequence names:
            {code:sql}
            SET sql_mode=ORACLE;
            DROP SEQUENCE IF EXISTS s1;
            CREATE SEQUENCE s1;
            SELECT NEXT VALUE FOR s1;
            SELECT s1.nextval;
            {code}
            {noformat}
            +-------------------+
            | NEXT VALUE FOR s1 |
            +-------------------+
            | 1 |
            +-------------------+
            +------------+
            | s1.nextval |
            +------------+
            | 2 |
            +------------+
            {noformat}
            {code:sql}
            SELECT NEXT VALUE FOR test.s1;
            {code}
            {noformat}
            +------------------------+
            | NEXT VALUE FOR test.s1 |
            +------------------------+
            | 3 |
            +------------------------+
            {noformat}

            Qualified sequence names also work with IBM DB2 syntax for {{PREVIOUS VALUE FOR}}
            {code:sql}
            SELECT PREVIOUS VALUE FOR test.s1;
            {code}
            {noformat}
            +----------------------------+
            | PREVIOUS VALUE FOR test.s1 |
            +----------------------------+
            | 3 |
            +----------------------------+
            {noformat}

            Under terms of this task we'll add support for qualified sequence names for Oracle syntax, to make these queries work:
            {code:sql}
            SELECT test.s1.nextval;
            {code}
            {noformat}
            {code:sql}
            SELECT test.s1.currval;
            {code}
            		The SQL standard syntax for {{NEXT VALUE FOR}} works for both non-qualified and qualified sequence names:
            {code:sql}
            SET sql_mode=ORACLE;
            DROP SEQUENCE IF EXISTS s1;
            CREATE SEQUENCE s1;
            SELECT NEXT VALUE FOR s1;
            SELECT s1.nextval;
            {code}
            {noformat}
            +-------------------+
            | NEXT VALUE FOR s1 |
            +-------------------+
            | 1 |
            +-------------------+
            +------------+
            | s1.nextval |
            +------------+
            | 2 |
            +------------+
            {noformat}
            {code:sql}
            SELECT NEXT VALUE FOR test.s1;
            {code}
            {noformat}
            +------------------------+
            | NEXT VALUE FOR test.s1 |
            +------------------------+
            | 3 |
            +------------------------+
            {noformat}

            Qualified sequence names also work with IBM DB2 syntax for {{PREVIOUS VALUE FOR}}
            {code:sql}
            SELECT PREVIOUS VALUE FOR test.s1;
            {code}
            {noformat}
            +----------------------------+
            | PREVIOUS VALUE FOR test.s1 |
            +----------------------------+
            | 3 |
            +----------------------------+
            {noformat}

            Under terms of this task we'll add support for qualified sequence names for Oracle syntax, to make these queries work:
            {code:sql}
            SELECT test.s1.nextval;
            {code}
            {code:sql}
            SELECT test.s1.currval;
            {code}
            bar Alexander Barkov made changes -
            Summary Add support for database qualified sequence names in NEXTVAL and CURRVAL sql_mode=ORACLE: Add support for database qualified sequence names in NEXTVAL and CURRVAL
            bar Alexander Barkov made changes -
            Labels Compatibility
            bar Alexander Barkov made changes -
            bar Alexander Barkov made changes -
            Status Open [ 1 ] Confirmed [ 10101 ]
            bar Alexander Barkov added a comment - - edited

            Pushed to bb-10.2-ext, 10.3, bb-10.2-compatibility.

            bar Alexander Barkov added a comment - - edited Pushed to bb-10.2-ext, 10.3, bb-10.2-compatibility.
            bar Alexander Barkov made changes -
            Assignee Alexander Barkov [ bar ]
            bar Alexander Barkov made changes -
            Status Confirmed [ 10101 ] In Progress [ 3 ]
            bar Alexander Barkov made changes -
            Component/s Parser [ 10201 ]
            Fix Version/s 10.3.1 [ 22532 ]
            Resolution Fixed [ 1 ]
            Status In Progress [ 3 ] Closed [ 6 ]
            anikitin Andrii Nikitin (Inactive) made changes -
            serg Sergei Golubchik made changes -
            Workflow MariaDB v3 [ 80381 ] MariaDB v4 [ 151973 ]

            People

              bar Alexander Barkov
              bar Alexander Barkov
              Votes:
              0 Vote for this issue
              Watchers:
              1 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.