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

Wrong results when calling a stored procedure multiple times with different arguments

    XMLWordPrintable

    Details

      Description

      When calling the same stored procedure multiple times with different arguments over the course of a single connection, the later calls may return wrong result sets.

      The following example illustrates the issue. First, some setup:

      DROP TABLE IF EXISTS foo;
          
      CREATE TABLE foo (
      	id int NOT NULL,
      	PRIMARY KEY (id)
      ) ENGINE=InnoDB;
       
      INSERT INTO foo VALUES (1), (2);
       
      DROP TABLE IF EXISTS bar;
       
      CREATE TABLE bar (
      	id int NOT NULL,
      	id_foo int NOT NULL,
      	PRIMARY KEY (id)
      ) ENGINE=InnoDB;
       
      INSERT INTO bar VALUES (1, 1);
       
      DROP PROCEDURE IF EXISTS test_proc;
       
      DELIMITER //
      CREATE PROCEDURE `test_proc`(IN `param` int)
      LANGUAGE SQL
      READS SQL DATA
      BEGIN
      	SELECT DISTINCT f.id
      	FROM foo f
      	LEFT OUTER JOIN bar b ON b.id_foo = f.id
      	WHERE (param OR b.id IS NOT NULL);
      END//
      DELIMITER ;

      Now, create a new connection to the DBMS and run the following statements:

      CALL test_proc(0);
      CALL test_proc(1);

      I get one result row for each statement (with the content id=1). However, the second statement should return two rows (1 and 2).

      Create a new connection again, and run the following:

      CALL test_proc(1);

      When I do that, I get the correct result (1 and 2). Running any further sequence of the above two statements on that same connection appears to yield the correct results as well (e.g. if we follow with CALL test_proc(0); and then CALL test_proc(1); again, it will still return the correct rows 1 and 2).

      Note: This is probably the same issue described in this StackOverflow question: https://stackoverflow.com/questions/43363500/mysql-inconsistent-query-results-in-procedure-with-temp-tables-empty-resultset

        Attachments

          Activity

            People

            • Assignee:
              igor Igor Babaev
              Reporter:
              medo Simeon Maxein
            • Votes:
              1 Vote for this issue
              Watchers:
              7 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: