[MDEV-15035] Wrong results when calling a stored procedure multiple times with different arguments Created: 2018-01-22  Updated: 2018-07-26  Resolved: 2018-04-27

Status: Closed
Project: MariaDB Server
Component/s: Optimizer, Stored routines
Affects Version/s: 5.5, 10.0, 10.1, 10.2.12, 10.2
Fix Version/s: 5.5.61

Type: Bug Priority: Major
Reporter: Simeon Maxein Assignee: Igor Babaev
Resolution: Fixed Votes: 1
Labels: stored_procedures, wrong_result
Environment:

Ubuntu Xenial, 64-Bit
Windows 10


 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

 Comments   
Comment by Simeon Maxein [ 2018-01-23 ]

This is just guesswork from playing around with this trying to find a workaround, but it feels to me like

  • If the parameter is 0, mariadb optimizes the left outer join into an inner join since it works for this case.
  • subsequent calls in the same session use the same strategy even though it is no longer valid

I can work around the problem by replacing b.id IS NOT NULL with IF(b.id IS NOT NULL, 1, 0), which probably prevents the optimizer from understanding that it can use an inner join.

Comment by Alice Sherepa [ 2018-01-24 ]

Thanks for the report and testcase!
Reproduced as described, on MariaDB 5.5-10.3 with Innodb and MyISAM, not reproducible on Mysql 5.7.19

CALL test_proc(1); ### from the same connection, where procedure was created
 
+------+-------------+-------+--------+---------------+---------+---------+---------------+------+----------+-----------------+
 
| id   | select_type | table | type   | possible_keys | key     | key_len | ref           | rows | filtered | Extra           |
 
+------+-------------+-------+--------+---------------+---------+---------+---------------+------+----------+-----------------+
 
|    1 | SIMPLE      | b     | ALL    | NULL          | NULL    | NULL    | NULL          |    1 |   100.00 | Using temporary |
 
|    1 | SIMPLE      | f     | eq_ref | PRIMARY       | PRIMARY | 4       | test.b.id_foo |    1 |   100.00 | Using index     |
 
+------+-------------+-------+--------+---------------+---------+---------+---------------+------+----------+----------------
 
| Note  | 1003 | select distinct `test`.`f`.`id` AS `id` from `test`.`foo` `f` join `test`.`bar` `b` where `test`.`f`.`id` = `test`.`b`.`id_foo` |
 
+-------+------+---------------------------------------------------------------------------------------------------------------------------------+
 
1 row in set (0.00 sec)
 
+------+
 
| id   |
 
+------+
 
|    1 |
 
+------+
 
### from the other connection
 
+------+-------------+-------+-------+---------------+---------+---------+------+------+----------+-------------------------------------------------+
 
| id   | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                                           |
 
+------+-------------+-------+-------+---------------+---------+---------+------+------+----------+-------------------------------------------------+
 
|    1 | SIMPLE      | f     | index | NULL          | PRIMARY | 4       | NULL |    2 |   100.00 | Using index; Using temporary                    |
 
|    1 | SIMPLE      | b     | ALL   | NULL          | NULL    | NULL    | NULL |    1 |   100.00 | Using where; Using join buffer (flat, BNL join) |
 
+------+-------------+-------+-------+---------------+---------+---------+------+------+----------+--------------------------
 
 
 
| Note  | 1003 | select distinct `test`.`f`.`id` AS `id` from `test`.`foo` `f` left join `test`.`bar` `b` on(`test`.`b`.`id_foo` = `test`.`f`.`id`) where 1 |
 
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------+
 
+----+
 
| id |
 
+----+
 
|  1 |
 
|  2 |
 
+----+
 
2 rows in set (0.00 sec)

Comment by Oleksandr Byelkin [ 2018-04-25 ]

OK to push!

Comment by Igor Babaev [ 2018-04-27 ]

A fix for this bug was pushed into 5.5.
Merges into higher versions may be not quite smooth.

Generated at Thu Feb 08 08:18:12 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.