MySQL Bugs: #20492: Subsequent calls to stored procedure yield incorrect result if join is used

Bug #20492	Subsequent calls to stored procedure yield incorrect result if join is used
Submitted:	16 Jun 2006 0:41	Modified:	4 Apr 2007 5:57
Reporter:	Morgan Tocker
Status:	Closed
Category:	Server: SP	Severity:	S3 (Non-critical)
Version:	5.0.22-standard-log	OS:	Linux (Linux)
Assigned to:	Bugs System	Target Version:
Tags:	rt_q1_2007

Description:
I have a stored procedure 'a', which is defined as;

DELIMITER ;;

DROP PROCEDURE IF EXISTS `a`;;

CREATE PROCEDURE `a`()

BEGIN

TRUNCATE TABLE t1;

INSERT INTO t1
	SELECT * from v1;

END;;

Every first execution of the stored procedure is quite fast, but subsequent calls are much
slower.

mysql> call a();
Query OK, 343 rows affected (0.09 sec)

mysql> call a();
Query OK, 343 rows affected (39.39 sec)

On inspecting the processlist during the second call a(), I found this:
mysql> show full processlist;
+----+------+-----------+-------+---------+------+----------------------+-----------------
-----------------+
| Id | User | Host      | db    | Command | Time | State                | Info      |
+----+------+-----------+-------+---------+------+----------------------+-----------------
-----------------+
| 93 | root | localhost | test5 | Query   |    6 | Copying to tmp table | INSERT INTO t1
        SELECT * from v1 |
| 94 | root | localhost | NULL  | Query   |    0 | NULL                 | show full
processlist      |
+----+------+-----------+-------+---------+------+----------------------+-----------------
-----------------+
2 rows in set (0.00 sec)

** Important Note **
v1 is a view which joins to other views.  If I 'materialize' any of these underlying
views, the problem dissapears.  If I run "TRUNCATE TABLE t1; INSERT INTO t1 SELECT * from
v1;", I can *not* reproduce the problem.  It seems somehow related to using stored
procedures.

The definition to these views has been attached privately (as it contains other
information).  

How to repeat:
mysql> call a();
Query OK, 343 rows affected (0.09 sec)

mysql> call a();
Query OK, 343 rows affected (39.39 sec)

Suggested fix:
unknown.

Thank you for a bug report. Verified just as described (on your private test case), also
with 5.0.23-BK on Linux:

mysql> delimiter //
mysql> CREATE PROCEDURE `a`()
    ->
    -> BEGIN
    ->
    -> TRUNCATE TABLE t1;
    ->
    -> INSERT INTO t1
    -> SELECT * from v1;
    ->
    -> END//
Query OK, 0 rows affected (0.04 sec)

mysql> delimiter ;
mysql> call a();
Query OK, 343 rows affected (0.05 sec)

mysql> call a();
Query OK, 343 rows affected (31.45 sec)

And yes, thread was in the same state:

mysql> show processlist;
+----+------+-----------+------+---------+------+----------------------+------------------
---------------+
| Id | User | Host      | db   | Command | Time | State                | Info             
           |
+----+------+-----------+------+---------+------+----------------------+------------------
---------------+
|  2 | root | localhost | test | Query   |   29 | Copying to tmp table | INSERT
INTO t1 SELECT * from v1 |
|  4 | root | localhost | test | Query   |    0 | NULL                 | show processlist 
              |
+----+------+-----------+------+---------+------+----------------------+------------------
---------------+
2 rows in set (0.00 sec)

Changed title, as the problem is in incorrect result on subsequent calls.  Here is a
minimal test case:

CREATE TABLE t1 (t1_id INT);

INSERT INTO t1 VALUES (0);
INSERT INTO t1 VALUES (1);

CREATE TABLE t2 (t2_id INT);

INSERT INTO t2 VALUES (2);

CREATE VIEW v1 AS
SELECT t1.t1_id, t2.t2_id
FROM t1 JOIN t2 ON t1.t1_id = 0;

CREATE VIEW v2 AS
SELECT t1.t1_id AS t1_id_1, v1.t1_id AS t1_id_2, v1.t2_id
FROM t1 LEFT JOIN v1 ON t1.t1_id = 0;

CREATE TABLE t3 SELECT * FROM v2;

DELIMITER |
CREATE PROCEDURE p()
BEGIN
  DELETE FROM t3;
  INSERT INTO t3 SELECT * FROM v2;
END |

Then do:

mysql> CALL p();
Query OK, 2 rows affected (0.01 sec)
mysql> SELECT * FROM t3;
+---------+---------+-------+
| t1_id_1 | t1_id_2 | t2_id |
+---------+---------+-------+
|       0 |       0 |     2 |
|       1 |    NULL |  NULL |
+---------+---------+-------+
2 rows in set (0.01 sec)

mysql> CALL p();
Query OK, 3 rows affected (0.01 sec)
mysql> SELECT * FROM t3;
+---------+---------+-------+
| t1_id_1 | t1_id_2 | t2_id |
+---------+---------+-------+
|       0 |       0 |     2 |
|       0 |       1 |     2 |
|       1 |    NULL |  NULL |
+---------+---------+-------+
3 rows in set (0.00 sec)

Because of GROUP BY in initial data only slowdown was noted.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/11548

ChangeSet@1.2248, 2006-09-07 18:51:00+04:00, kroki@moonlight.intranet +3 -0
  BUG#20492: Subsequent calls to stored procedure yield incorrect result
             if join is used
  
  For procedures with selects that use complicated joins with ON expression
  re-execution could erroneously ignore this ON expression, giving
  incorrect result.
  
  The problem was that optimized ON expression wasn't saved for
  re-execution.  The solution is to properly save it.

Investigated the opportunity to simplify this code.
Failed to come up with a way.
This patch is approved.

Queued to 5.0-runtime and 5.1-runtime.

We are being bitten by the same bug.  I've included a test case below similar to what we
are doing.

DROP TABLE IF EXISTS my_foo;
DROP TABLE IF EXISTS my_bar;

CREATE TABLE my_foo
(
    uid INTEGER UNSIGNED PRIMARY KEY
) Engine=InnoDB;

CREATE TABLE my_bar
(
    uid INTEGER UNSIGNED PRIMARY KEY,
    other INTEGER UNSIGNED
) Engine=InnoDB;

INSERT INTO my_foo VALUES (1), (2);
INSERT INTO my_bar VALUES (1, 1), (2, NULL);

SELECT b.uid, f.uid
FROM my_bar b
LEFT JOIN my_foo f
ON b.other = f.uid
WHERE b.uid = 1;

SELECT b.uid, f.uid
FROM my_bar b
LEFT JOIN my_foo f
ON b.other = f.uid
WHERE b.uid = 2;

DROP PROCEDURE IF EXISTS my_procedure;

DELIMITER //

CREATE PROCEDURE my_procedure
(i_uid INTEGER UNSIGNED)
BEGIN
    SELECT b.uid, f.uid
    FROM my_bar b
    LEFT JOIN my_foo f
    ON b.other = f.uid
    WHERE b.uid = i_uid;
END//

DELIMITER ;

CALL my_procedure(1);

CALL my_procedure(2);

CALL my_procedure(1);

SELECT b.uid, f.value
FROM my_bar b
LEFT JOIN my_foo f
ON b.other = f.uid
WHERE b.uid = 1;

I've tested the patch that is associated with this bug and it did not seem to fix the
problem that I was seeing.  If you take a look at my example, calling:

call my_procedure(1);
call my_procedure(2);
call my_procedure(1);

shows the problem.  I may have applied the patch incorrect, but I don't think this is the
problem.

Can you try my test data with your patch?

Thanks,
Kevin

Hello Kevin,

Thanks for the test case, we will certainly look into it.

Hello Kevin,

I ran you test case, and here's what I get:

mysql> CALL my_procedure(1);
+-----+-----+
| uid | uid |
+-----+-----+
|   1 |   1 |
+-----+-----+

mysql> CALL my_procedure(2);
+-----+------+
| uid | uid  |
+-----+------+
|   2 | NULL |
+-----+------+

mysql> CALL my_procedure(1);
+-----+-----+
| uid | uid |
+-----+-----+
|   1 |   1 |
+-----+-----+

Could you please describe in what way these results are wrong, and what do you expect them
to be?  But if you do get different results, then perhaps the patch was applied
incorrectly, or you hit another bug that was also fixed then (I'm not sure what version
you are using, I'm using current BK tree, it may not be the same as your version + patch).
 In any case, I think I can't repeat your problem.

Thanks!

Hi,

I am using 5.0.22 with the patch.  Given your results, I'm convinced that you are correct
(either I applied the patch incorrectly or something else got fixed between 5.0.22 and
5.0.32).  I've included the output that I am seeing.  However, at this point I'm going to
see if I can upgrade to the latest MySQL and apply the patch (that should solve my
problem).

Thanks!
Kevin

p.s.  My output:

mysql> CALL my_procedure(1);
+-----+-----+
| uid | uid |
+-----+-----+
|   1 |   1 |
+-----+-----+

mysql> CALL my_procedure(2);
+-----+------+
| uid | uid  |
+-----+------+
|   2 | NULL |
+-----+------+

mysql> CALL my_procedure(1);
+-----+------+
| uid | uid  |
+-----+------+
|   1 | NULL |
+-----+------+

Fixed in 5.0.40 and in 5.1.17

Noted in 5.0.40, 5.1.17 changelogs.

For a stored procedure containing a SELECT statement that used a
complicated join with an ON expression, the expression could be
ignored during re-execution of the procedure, yielding an incorrect
result.

The patch also fixes the problem reported as bug #27388.