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

sql_mode=ORACLE: Understand optional routine name after the END keyword

Details

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

    Description

      When running with sql_mode=ORACLE, MariaDB (the bb-10.2-compatibility branch) does not understand the optional function or procedure name after the END keyword which ends the entire routine definition. We'll extend the parser to understand the optional name, so this script is parsed without syntax errors:

      SET sql_mode=ORACLE;
      		 
      DROP PROCEDURE p1;
      		 
      DELIMITER /
      		 
      CREATE PROCEDURE p1 AS
      		 
      BEGIN
      		 
      END p1; -- Notice p1
      		 
      /
      		 
      DELIMITER ;

      In case of stand-alone routines we'll also allow qualified names: END test.p1;

      Originally this task included changes for package routines, but it does not any more:

      Optional routine names are also possible inside a package body definition:

      DELIMITER $$
      		 
      CREATE PACKAGE BODY test2 AS
      		 
        FUNCTION f1 RETURN INT AS
      		 
        BEGIN
      		 
          RETURN 10;
      		 
        END f1;
      		 
        PROCEDURE p1 AS
      		 
        BEGIN
      		 
          NULL;
      		 
        END p1;
      		 
      END test2;
      		 
      $$
      		 
      DELIMITER ;
      		 
      DROP PACKAGE test2;

      In case of package routines the name specified in END cannot be qualified.

      If the name specified in CREATE does not match the name specified in END, an error will be reported.

      Under terms of this patch we'll only fix traditional (stand-alone) stored functions and procedures.
      Handling the name after the END for package routines will be done in package related tasks (e.g. MDEV-10591 or MDEV-11952)

      Attachments

        Activity

          Ascending order - Click to sort in descending order
          bar Alexander Barkov created issue -
          bar Alexander Barkov made changes -
          Field Original Value New Value
          Fix Version/s 10.3 [ 22126 ]
          bar Alexander Barkov made changes -
          Description
          When running with {{sql_mode=ORACLE}}, MariaDB (the {{bb-10.2-compatibility}} branch) does not understand the optional function or procedure name after the {{END}} keyword which ends the entire routine definition. We'll extend the parser to understand the optional name, so this script is parsed without syntax errors:

          {code:sql}
          SET sql_mode=ORACLE;
          DROP PROCEDURE p1;
          DELIMITER /
          CREATE PROCEDURE p1 AS
          BEGIN
          END p1; -- Notice p1
          /
          DELIMITER ;
          {code}
          		When running with {{sql_mode=ORACLE}}, MariaDB (the {{bb-10.2-compatibility}} branch) does not understand the optional function or procedure name after the {{END}} keyword which ends the entire routine definition. We'll extend the parser to understand the optional name, so this script is parsed without syntax errors:

          {code:sql}
          SET sql_mode=ORACLE;
          DROP PROCEDURE p1;
          DELIMITER /
          CREATE PROCEDURE p1 AS
          BEGIN
          END p1; -- Notice p1
          /
          DELIMITER ;
          {code}
          In case of stand-alone routines we'll also allow qualified names: {{END test.p1}}.

          Optional routine names are also possible inside a package body definition:
          {code:sql}
          DELIMITER $$
          CREATE PACKAGE BODY test2 AS
            FUNCTION f1 RETURN INT AS
            BEGIN
              RETURN 10;
            END f1;
            PROCEDURE p1 AS
            BEGIN
              NULL;
            END p1;
          END test2;
          $$
          DELIMITER ;
          DROP PACKAGE test2;
          {code}
          In case of package routines the name specified in {{END}} cannot be qualified.
          bar Alexander Barkov made changes -
          Description When running with {{sql_mode=ORACLE}}, MariaDB (the {{bb-10.2-compatibility}} branch) does not understand the optional function or procedure name after the {{END}} keyword which ends the entire routine definition. We'll extend the parser to understand the optional name, so this script is parsed without syntax errors:

          {code:sql}
          SET sql_mode=ORACLE;
          DROP PROCEDURE p1;
          DELIMITER /
          CREATE PROCEDURE p1 AS
          BEGIN
          END p1; -- Notice p1
          /
          DELIMITER ;
          {code}
          In case of stand-alone routines we'll also allow qualified names: {{END test.p1}}.

          Optional routine names are also possible inside a package body definition:
          {code:sql}
          DELIMITER $$
          CREATE PACKAGE BODY test2 AS
            FUNCTION f1 RETURN INT AS
            BEGIN
              RETURN 10;
            END f1;
            PROCEDURE p1 AS
            BEGIN
              NULL;
            END p1;
          END test2;
          $$
          DELIMITER ;
          DROP PACKAGE test2;
          {code}
          In case of package routines the name specified in {{END}} cannot be qualified.
          		When running with {{sql_mode=ORACLE}}, MariaDB (the {{bb-10.2-compatibility}} branch) does not understand the optional function or procedure name after the {{END}} keyword which ends the entire routine definition. We'll extend the parser to understand the optional name, so this script is parsed without syntax errors:

          {code:sql}
          SET sql_mode=ORACLE;
          DROP PROCEDURE p1;
          DELIMITER /
          CREATE PROCEDURE p1 AS
          BEGIN
          END p1; -- Notice p1
          /
          DELIMITER ;
          {code}
          In case of stand-alone routines we'll also allow qualified names: {{END test.p1;}}

          Optional routine names are also possible inside a package body definition:
          {code:sql}
          DELIMITER $$
          CREATE PACKAGE BODY test2 AS
            FUNCTION f1 RETURN INT AS
            BEGIN
              RETURN 10;
            END f1;
            PROCEDURE p1 AS
            BEGIN
              NULL;
            END p1;
          END test2;
          $$
          DELIMITER ;
          DROP PACKAGE test2;
          {code}
          In case of package routines the name specified in {{END}} cannot be qualified.
          bar Alexander Barkov made changes -
          Description When running with {{sql_mode=ORACLE}}, MariaDB (the {{bb-10.2-compatibility}} branch) does not understand the optional function or procedure name after the {{END}} keyword which ends the entire routine definition. We'll extend the parser to understand the optional name, so this script is parsed without syntax errors:

          {code:sql}
          SET sql_mode=ORACLE;
          DROP PROCEDURE p1;
          DELIMITER /
          CREATE PROCEDURE p1 AS
          BEGIN
          END p1; -- Notice p1
          /
          DELIMITER ;
          {code}
          In case of stand-alone routines we'll also allow qualified names: {{END test.p1;}}

          Optional routine names are also possible inside a package body definition:
          {code:sql}
          DELIMITER $$
          CREATE PACKAGE BODY test2 AS
            FUNCTION f1 RETURN INT AS
            BEGIN
              RETURN 10;
            END f1;
            PROCEDURE p1 AS
            BEGIN
              NULL;
            END p1;
          END test2;
          $$
          DELIMITER ;
          DROP PACKAGE test2;
          {code}
          In case of package routines the name specified in {{END}} cannot be qualified.
          		When running with {{sql_mode=ORACLE}}, MariaDB (the {{bb-10.2-compatibility}} branch) does not understand the optional function or procedure name after the {{END}} keyword which ends the entire routine definition. We'll extend the parser to understand the optional name, so this script is parsed without syntax errors:

          {code:sql}
          SET sql_mode=ORACLE;
          DROP PROCEDURE p1;
          DELIMITER /
          CREATE PROCEDURE p1 AS
          BEGIN
          END p1; -- Notice p1
          /
          DELIMITER ;
          {code}
          In case of stand-alone routines we'll also allow qualified names: {{END test.p1;}}

          Optional routine names are also possible inside a package body definition:
          {code:sql}
          DELIMITER $$
          CREATE PACKAGE BODY test2 AS
            FUNCTION f1 RETURN INT AS
            BEGIN
              RETURN 10;
            END f1;
            PROCEDURE p1 AS
            BEGIN
              NULL;
            END p1;
          END test2;
          $$
          DELIMITER ;
          DROP PACKAGE test2;
          {code}
          In case of package routines the name specified in {{END}} cannot be qualified.

          If the name specified in {{CREATE}} does not match the name specified in {{END}}, an error will be reported.

          Under terms of this patch we'll also fix {{CREATE PACKAGE}} and {{CREATE PACKAGE BODY}} to return an error if the name in {{CREATE}} does not match the name in {{END}}.

          bar Alexander Barkov made changes -
          Status Open [ 1 ] In Progress [ 3 ]
          bar Alexander Barkov made changes -
          issue.field.resolutiondate 2017-02-20 15:22:00.0 2017-02-20 15:22:00.241
          bar Alexander Barkov made changes -
          Component/s Parser [ 10201 ]
          Fix Version/s 10.3.0 [ 22127 ]
          Fix Version/s 10.3 [ 22126 ]
          Resolution Fixed [ 1 ]
          Status In Progress [ 3 ] Closed [ 6 ]
          bar Alexander Barkov made changes -
          Labels Compatibility need_review
          bar Alexander Barkov made changes -
          Description When running with {{sql_mode=ORACLE}}, MariaDB (the {{bb-10.2-compatibility}} branch) does not understand the optional function or procedure name after the {{END}} keyword which ends the entire routine definition. We'll extend the parser to understand the optional name, so this script is parsed without syntax errors:

          {code:sql}
          SET sql_mode=ORACLE;
          DROP PROCEDURE p1;
          DELIMITER /
          CREATE PROCEDURE p1 AS
          BEGIN
          END p1; -- Notice p1
          /
          DELIMITER ;
          {code}
          In case of stand-alone routines we'll also allow qualified names: {{END test.p1;}}

          Optional routine names are also possible inside a package body definition:
          {code:sql}
          DELIMITER $$
          CREATE PACKAGE BODY test2 AS
            FUNCTION f1 RETURN INT AS
            BEGIN
              RETURN 10;
            END f1;
            PROCEDURE p1 AS
            BEGIN
              NULL;
            END p1;
          END test2;
          $$
          DELIMITER ;
          DROP PACKAGE test2;
          {code}
          In case of package routines the name specified in {{END}} cannot be qualified.

          If the name specified in {{CREATE}} does not match the name specified in {{END}}, an error will be reported.

          Under terms of this patch we'll also fix {{CREATE PACKAGE}} and {{CREATE PACKAGE BODY}} to return an error if the name in {{CREATE}} does not match the name in {{END}}.

          		When running with {{sql_mode=ORACLE}}, MariaDB (the {{bb-10.2-compatibility}} branch) does not understand the optional function or procedure name after the {{END}} keyword which ends the entire routine definition. We'll extend the parser to understand the optional name, so this script is parsed without syntax errors:

          {code:sql}
          SET sql_mode=ORACLE;
          DROP PROCEDURE p1;
          DELIMITER /
          CREATE PROCEDURE p1 AS
          BEGIN
          END p1; -- Notice p1
          /
          DELIMITER ;
          {code}
          In case of stand-alone routines we'll also allow qualified names: {{END test.p1;}}

          h2. Originally this task included changes for package routines, but it does not any more:
          Optional routine names are also possible inside a package body definition:
          {code:sql}
          DELIMITER $$
          CREATE PACKAGE BODY test2 AS
            FUNCTION f1 RETURN INT AS
            BEGIN
              RETURN 10;
            END f1;
            PROCEDURE p1 AS
            BEGIN
              NULL;
            END p1;
          END test2;
          $$
          DELIMITER ;
          DROP PACKAGE test2;
          {code}
          In case of package routines the name specified in {{END}} cannot be qualified.

          If the name specified in {{CREATE}} does not match the name specified in {{END}}, an error will be reported.

          Under terms of this patch only fix traditional (stand-alone) stored functions and procedures.
          Handling the name after the {{END}} for package routines will be done in package related tasks (e.g. MDEV-10591 or MDEV-11952)



          bar Alexander Barkov made changes -
          Description When running with {{sql_mode=ORACLE}}, MariaDB (the {{bb-10.2-compatibility}} branch) does not understand the optional function or procedure name after the {{END}} keyword which ends the entire routine definition. We'll extend the parser to understand the optional name, so this script is parsed without syntax errors:

          {code:sql}
          SET sql_mode=ORACLE;
          DROP PROCEDURE p1;
          DELIMITER /
          CREATE PROCEDURE p1 AS
          BEGIN
          END p1; -- Notice p1
          /
          DELIMITER ;
          {code}
          In case of stand-alone routines we'll also allow qualified names: {{END test.p1;}}

          h2. Originally this task included changes for package routines, but it does not any more:
          Optional routine names are also possible inside a package body definition:
          {code:sql}
          DELIMITER $$
          CREATE PACKAGE BODY test2 AS
            FUNCTION f1 RETURN INT AS
            BEGIN
              RETURN 10;
            END f1;
            PROCEDURE p1 AS
            BEGIN
              NULL;
            END p1;
          END test2;
          $$
          DELIMITER ;
          DROP PACKAGE test2;
          {code}
          In case of package routines the name specified in {{END}} cannot be qualified.

          If the name specified in {{CREATE}} does not match the name specified in {{END}}, an error will be reported.

          Under terms of this patch only fix traditional (stand-alone) stored functions and procedures.
          Handling the name after the {{END}} for package routines will be done in package related tasks (e.g. MDEV-10591 or MDEV-11952)



          		When running with {{sql_mode=ORACLE}}, MariaDB (the {{bb-10.2-compatibility}} branch) does not understand the optional function or procedure name after the {{END}} keyword which ends the entire routine definition. We'll extend the parser to understand the optional name, so this script is parsed without syntax errors:

          {code:sql}
          SET sql_mode=ORACLE;
          DROP PROCEDURE p1;
          DELIMITER /
          CREATE PROCEDURE p1 AS
          BEGIN
          END p1; -- Notice p1
          /
          DELIMITER ;
          {code}
          In case of stand-alone routines we'll also allow qualified names: {{END test.p1;}}

          h2. Originally this task included changes for package routines, but it does not any more:
          Optional routine names are also possible inside a package body definition:
          {code:sql}
          DELIMITER $$
          CREATE PACKAGE BODY test2 AS
            FUNCTION f1 RETURN INT AS
            BEGIN
              RETURN 10;
            END f1;
            PROCEDURE p1 AS
            BEGIN
              NULL;
            END p1;
          END test2;
          $$
          DELIMITER ;
          DROP PACKAGE test2;
          {code}
          In case of package routines the name specified in {{END}} cannot be qualified.

          If the name specified in {{CREATE}} does not match the name specified in {{END}}, an error will be reported.

          Under terms of this patch we'll only fix traditional (stand-alone) stored functions and procedures.
          Handling the name after the {{END}} for package routines will be done in package related tasks (e.g. MDEV-10591 or MDEV-11952)



          bar Alexander Barkov made changes -
          Labels Compatibility need_review Compatibility Sto need_review
          bar Alexander Barkov made changes -
          Labels Compatibility Sto need_review Compatibility need_review
          bar Alexander Barkov made changes -
          Component/s Stored routines [ 13905 ]
          serg Sergei Golubchik made changes -
          Labels Compatibility need_review Compatibility
          serg Sergei Golubchik made changes -
          Workflow MariaDB v3 [ 79690 ] MariaDB v4 [ 151725 ]

          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.