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

sql_mode=ORACLE: Triggers: Understand :NEW.c1 and :OLD.c1 instead of NEW.c1 and OLD.c1

Details

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

    Description

      When running in sql_mode=ORACLE, MariaDB should understand :NEW and :OLD as references to new and old row values in addition or instead of NEW and OLD without colon.

      DROP TABLE t1;
      		 
      CREATE TABLE t1 (a INT, b INT, c INT);
      		 
      DROP TRIGGER tr1;
      		 
      CREATE TRIGGER tr1 BEFORE INSERT ON t1
      		 
      FOR EACH ROW
      		 
      DECLARE
      		 
        cnt INT := 0;
      		 
      BEGIN
      		 
        IF :NEW.a IS NULL THEN cnt:=cnt+1; END IF;
      		 
        IF :NEW.b IS NULL THEN cnt:=cnt+1; END IF;
      		 
        IF :NEW.c IS NULL THEN :NEW.c:=cnt; END IF;
      		 
      END;
      		 
      /

      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
          Summary sql_mode=ORACLE: Understand :NEW.c1 and :OLD.c1 instead of NEW.c1 and OLD.c1 sql_mode=ORACLE: Triggers: Understand :NEW.c1 and :OLD.c1 instead of NEW.c1 and OLD.c1
          monty Michael Widenius made changes -
          Description When running in {{sql_mode=ORACLE}}, MariaDB should understand {{:NEW}} and {{:OLD} as references to new and old row values in addition or instead of {{NEW}} and {{OLD}} without colon.
          {code:sql}
          DROP TABLE t1;
          CREATE TABLE t1 (a INT, b INT, c INT);
          DROP TRIGGER tr1;
          CREATE TRIGGER tr1 BEFORE INSERT ON t1
          FOR EACH ROW
          DECLARE
            cnt INT := 0;
          BEGIN
            IF :NEW.a IS NULL THEN cnt:=cnt+1; END IF;
            IF :NEW.b IS NULL THEN cnt:=cnt+1; END IF;
            IF :NEW.c IS NULL THEN :NEW.c:=cnt; END IF;
          END;
          /
          {code}
          		When running in {{sql_mode=ORACLE}}, MariaDB should understand {{:NEW}} and {{:OLD}} as references to new and old row values in addition or instead of {{NEW}} and {{OLD}} without colon.
          {code:sql}
          DROP TABLE t1;
          CREATE TABLE t1 (a INT, b INT, c INT);
          DROP TRIGGER tr1;
          CREATE TRIGGER tr1 BEFORE INSERT ON t1
          FOR EACH ROW
          DECLARE
            cnt INT := 0;
          BEGIN
            IF :NEW.a IS NULL THEN cnt:=cnt+1; END IF;
            IF :NEW.b IS NULL THEN cnt:=cnt+1; END IF;
            IF :NEW.c IS NULL THEN :NEW.c:=cnt; END IF;
          END;
          /
          {code}
          monty Michael Widenius made changes -
          Parent MDEV-10142 [ 56873 ]
          Issue Type Task [ 3 ] Technical task [ 7 ]
          alvinr Alvin Richards (Inactive) made changes -
          Labels Compatibility
          alvinr Alvin Richards (Inactive) made changes -
          Labels Compatibility
          bar Alexander Barkov made changes -
          Component/s Parser [ 10201 ]
          Fix Version/s 10.2.2 [ 22013 ]
          Fix Version/s 10.2 [ 14601 ]
          Resolution Fixed [ 1 ]
          Status Open [ 1 ] Closed [ 6 ]
          bar Alexander Barkov made changes -
          Assignee Alexander Barkov [ bar ]
          bar Alexander Barkov made changes -
          Fix Version/s 10.3.0 [ 22127 ]
          Fix Version/s 10.2.2 [ 22013 ]
          alvinr Alvin Richards (Inactive) made changes -
          Labels Copa
          alvinr Alvin Richards (Inactive) made changes -
          Labels Copa Compatibility
          alvinr Alvin Richards (Inactive) made changes -
          Labels Compatibility Compatibility NRE-307517
          alvinr Alvin Richards (Inactive) made changes -
          NRE Projects NRE-307517
          alvinr Alvin Richards (Inactive) made changes -
          Labels Compatibility NRE-307517 Compatibility
          alvinr Alvin Richards (Inactive) made changes -
          NRE Approved Yes [ 10304 ]
          bar Alexander Barkov made changes -
          Labels Compatibility 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 [ 76684 ] MariaDB v4 [ 150770 ]

          People

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