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

    • Type: Bug
    • Status: Open (View Workflow)
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 10.3.17, 10.4.7
    • Fix Version/s: 10.3, 10.4
    • Component/s: Stored routines
    • Labels:
      None
    • Environment:
      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

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

              Dates

              Created:
              Updated: