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

IF NOT EXISTS AND NOT EXISTS crash DB server

    XMLWordPrintable

    Details

      Description

      Call stored procedure with IF NOT EXISTS AND NOT EXISTS condition crash the DB server.

      The case can be reproduced as follows:

      1. Create tables 'test' and 'test2' and insert values into them:

      CREATE TABLE `test` (
        `name` varchar(100) NOT NULL,
        `address` varchar(100) DEFAULT NULL,
        PRIMARY KEY (`name`));
       
      CREATE TABLE `test2` (
        `name` varchar(100) NOT NULL,
        `address` varchar(100) DEFAULT NULL,
        PRIMARY KEY (`name`));
       
      INSERT INTO test VALUES ('a', 'aaa');
      INSERT INTO test2 VALUES ('a', 'aaa');
      

      2. Create stored procedure 'test_if':

      DELIMITER ;;
      DROP PROCEDURE IF EXISTS `test_if` ;
      CREATE  PROCEDURE `test_if`(
              IN in_name NVARCHAR(20)
      )
      BEGIN
       
          If NOT EXISTS (select name from test where name = name)
          AND NOT EXISTS (select name from test2 where name = name)
          THEN
             select "NOT EXISTS";
          ELSE
             select "EXISTS";
          END IF;
       
          SELECT * FROM test;
      END ;;
      DELIMITER ;
      

      3. Run: call test_if('a'); (OK, no error)
      4. Run again: call test_if('a'); , it will crash the DB server

      The error message 'error.txt' is attached for your reference.

      Please investigate.

      Thanks,

      Chow King Tak

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              sanja Oleksandr Byelkin
              Reporter:
              ktchow_ogcio Chow King Tak
              Votes:
              2 Vote for this issue
              Watchers:
              9 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.