MySQL Bugs: #6866: stored procedures are errornous in conjunction with views

Bug #6866	stored procedures are errornous in conjunction with views
Submitted:	29 Nov 2004 2:29	Modified:	10 Jun 2005 20:08
Reporter:	Harald Gölles	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.2	OS:	Linux (linux)
Assigned to:	Igor Babaev	CPU Architecture:	Any

Description:
when creating a stored procedure that selects data from a view, connection gets lost or wrong data is returned.

ERROR 2013 (HY000): Lost connection to MySQL server during query
(randomly, seems to occur more often if query - interval decreases)

sometimes wrong results are returned:
it seems the left joined table is cross joined, and all rows of t3 are returned.

mysqld was compiled with
CFLAGS="-O2 -mcpu=pentium3" CXX=gcc CXXFLAGS="-O2 -mcpu=pentium3 -felide-constructors" \
        ./configure --prefix /var/mysql5 --with-tcp-port=3307 --with-unix-socket-path=/var/run/mysql5.sock \
        --with-collation=latin1_general_cs --with-charset=latin1 \
        --without-debug --with-extra-charsets=complex --enable-thread-safe-client \
        --enable-local-infile --enable-assembler --disable-shared \
        --with-client-ldflags=-all-static --with-mysqld-ldflags=-all-static \
        --without-geometry

How to repeat:
use test;

drop table t;
drop table t2;
drop table t3;

create table t (t1 int, t2 int, t3 int, data varchar(500));
create table t2 (t2 int, data2 varchar(500));
create table t3 (t3 int, data3 varchar(500));

insert into t2 (t2, data2) values (1, 'a');
insert into t2 (t2, data2) values (2, 'b');
insert into t2 (t2, data2) values (3, 'c');

insert into t3 (t3, data3) values (4, 'd');
insert into t3 (t3, data3) values (5, 'e');
insert into t3 (t3, data3) values (6, 'f');

insert into t (t1, t2, t3, data) values (1,1,4,'xx');

drop view tv;
create view tv as
select t.*, t2.data2, t3.data3 from t
inner join t2 on t.t2 = t2.t2
left join t3 on t.t3 = t3.t3
order by t.t1, t2.t2, t3.t3;

drop procedure tp;
DELIMITER //
create procedure tp (_t1 int)
begin
select * from tv where t1 = _t1;
end
//
DELIMITER ;

call tp (1);
call tp (1);
call tp (1);
call tp (1);

results in:

mysql> call tp (1);
+------+------+------+------+-------+-------+
| t1   | t2   | t3   | data | data2 | data3 |
+------+------+------+------+-------+-------+
|    1 |    1 |    4 | xx   | a     | d     |
+------+------+------+------+-------+-------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> call tp (1);
+------+------+------+------+-------+-------+
| t1   | t2   | t3   | data | data2 | data3 |
+------+------+------+------+-------+-------+
|    1 |    1 |    4 | xx   | a     | d     |
|    1 |    1 |    4 | xx   | a     | e     |
|    1 |    1 |    4 | xx   | a     | f     |
+------+------+------+------+-------+-------+
3 rows in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

mysql> call tp (1);
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql> call tp (1);
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysql5.sock' (111)
ERROR:
Can't connect to the server
mysql>
mysql> call tp (1);
No connection. Trying to reconnect...
Connection id:    1
Current database: test

+------+------+------+------+-------+-------+
| t1   | t2   | t3   | data | data2 | data3 |
+------+------+------+------+-------+-------+
|    1 |    1 |    4 | xx   | a     | d     |
+------+------+------+------+-------+-------+
1 row in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

Suggested fix:
?

Here is a slightly simpler test case (SuSE 9.2, 5.0.3-alpha-debug) 
 
create table t (s1 int); 
create view v as select * from t; 
create procedure p () select * from v; 
call p(); 
/* the return is 'empty set' but the server has crashed */

This no longer crashes the server. Instead a syntax error is reported, but this is
independent of the use of a stored procedure. The additional simplified test case
is now working.

mysql> call tp (1);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'test`.`t` join `test`.`t2` on((`test`.`t`.`t2` = `test`.`t2`.`t2`))) left join `' at line 1
mysql> 
mysql> select * from tv where t1 = 1;  
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'test`.`t` join `test`.`t2` on((`test`.`t`.`t2` = `test`.`t2`.`t2`))) left join `' at line 1
mysql> select t.*, t2.data2, t3.data3 from t inner join t2 on t.t2 = t2.t2 left join t3 on t.t3 = t3.t3 order by t.t1, t2.t2, t3.t3;
+------+------+------+------+-------+-------+
| t1   | t2   | t3   | data | data2 | data3 |
+------+------+------+------+-------+-------+
|    1 |    1 |    4 | xx   | a     | d     |
+------+------+------+------+-------+-------+
1 row in set (0.04 sec)

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/internals/25641

ChangeSet
  1.1952 05/06/06 06:05:11 igor@rurik.mysql.com +3 -0
  sp.test, sp.result:
    Added a test case for bug #6866.
  sql_select.cc:
    Fixed bug #6866.
    Bug was due to the fact that on_expr was not backed up
    for the second execution of the stored procedure.

This fix will appear in 5.0.7.

Noted in 5.0.7 changelog.

Noted in 5.0.7 changelog.