Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-20393

Exception is not captured by sqlexception handler on UPDATE with LEFT JOIN

    XMLWordPrintable

Details

    • Bug
    • Status: Open (View Workflow)
    • Major
    • Resolution: Unresolved
    • 10.3.17, 10.4.7
    • 10.4
    • Stored routines
    • None
    • lubuntu 16.04

    Description

      DROP TABLE IF EXISTS tab1;
      DROP TABLE IF EXISTS tab2;
      DROP PROCEDURE IF EXISTS sp_test;

      CREATE TABLE tab1 (t1_col1 int primary key);
      CREATE TABLE tab2 (t2_col1 int primary key);

      INSERT tab1 VALUES (1), (2), (3), (4);
      INSERT tab2 VALUES (1), (2), (3), (4);

      CREATE PROCEDURE sp_test()
      BEGIN

      DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN
      GET DIAGNOSTICS CONDITION 1 @HANDLER_TEXT = MESSAGE_TEXT;
      SELECT @HANDLER_TEXT;
      END;

      UPDATE tab1 SET t1_col1 = 1 WHERE t1_col1 = 4;
      UPDATE tab1 INNER JOIN tab2 ON t2_col1 = t1_col1 SET t1_col1 = 2 WHERE t1_col1 = 4;
      UPDATE tab1 LEFT JOIN tab2 ON t2_col1 = t1_col1 SET t1_col1 = 3 WHERE t1_col1 = 4;

      – The UPDATE using LEFT JOIN is not captured by the continue handler. Why?

      END;

      CALL sp_test();

      Output:

      +---------------------------------------+
      | @HANDLER_TEXT                         |
      +---------------------------------------+
      | Duplicate entry '1' for key 'PRIMARY' |
      +---------------------------------------+
      +---------------------------------------+
      | @HANDLER_TEXT                         |
      +---------------------------------------+
      | Duplicate entry '2' for key 'PRIMARY' |
      +---------------------------------------+
      ERROR 1062 (23000) at line 1: Duplicate entry '3' for key 'PRIMARY'
      

      Attachments

        Activity

          People

            sanja Oleksandr Byelkin
            tgj1970 Thomas G. Jensen
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

              Created:
              Updated:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.