MySQL Bugs: #5231: Crash in stored procedure with SELECT IFNULL(SUM(c1), 0) in sub-CALL

Bug #5231	Crash in stored procedure with SELECT IFNULL(SUM(c1), 0) in sub-CALL
Submitted:	26 Aug 2004 15:11	Modified:	16 May 2005 12:35
Reporter:	Per-Erik Martin	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0	OS:
Assigned to:	Oleksandr Byelkin	CPU Architecture:	Any

Description:
The server crashes on:
SELECT IFNULL(SUM(c1), 0) INTO V_Var FROM bar;
when executed in a sub-CALL, but not in an immediate
CALL. Although the construction is a bit silly, it shouldn't
crash.

How to repeat:
DROP PROCEDURE IF EXISTS foo;
delimiter //;
CREATE PROCEDURE foo()
   BEGIN
   DECLARE V_Var INTEGER;

   SELECT IFNULL(SUM(c1), 0) INTO V_Var
        FROM bar;
   END//
DROP PROCEDURE IF EXISTS foo1//
CREATE PROCEDURE foo1()
   BEGIN
      CALL foo();
      CALL foo();
   END//
delimiter ;//
-- These works...
call foo();
call foo();
-- ... but this crashes.
call foo1();

I saw on some other mysql bug that calls to user defined stored procedures are semi-case sensitive.  The initial call to the procedure is case sensitive but all subsequent calls are case insensitive.  I have fought with this problem for a week and found it buried in another bug not pertaining specificly to stored procedure call issues.  I have tried a porition of this and found it to be true.  The same might apply here:

to replicate try this and pay attention to case of "foo":

DROP PROCEDURE IF EXISTS foo;
delimiter //
CREATE PROCEDURE foo()
BEGIN
DECLARE V_Var INTEGER;
SELECT IFNULL(SUM(c1), 0) INTO V_Var FROM bar;
END//
delimiter ;
call FOO();
should get ERROR 1289 (42000):  PROCEDURE " does not exist
call foo();
should get ERROR 1146 (42S02) Table '<current db>.bar' doesn't exist
( which means the sotred procedure was at least found )
call FOO();
should get ERROR 1146 (42S02) Table '<current db>.bar' doesn't exist
(  becasue the initial case sensitive call has been issued )

No, stored procedure names are always case insensitive.
There is however a mistake in the "how to repeat" example; the table creation
is missing:

create table bar ( c1 int );

(It doesn't matter if it's empty or not.)

Don't mean to be a pain but I struggled with this for a few days so I'm curious as to what is going on.  It seems that the subsequent calls to FOO() drop the connection to the server.
If the initial call to stored procedures are not case sensitve then please explain this:

mysql> use test;
Database changed
mysql> CREATE TABLE BAR ( cl int );
Query OK, 0 rows affected (0.07 sec)

mysql> delimiter //
mysql> create procedure foo()
    -> BEGIN
    -> DECLARE v_var integer;
    -> SELECT IFNULL(SUM(cl), 0) INTO v_var FROM BAR;
    -> END//
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> call FOO();
ERROR 1289 (42000): PROCEDURE )
                               Õ does not exist
mysql> call foo();
Query OK, 0 rows affected (0.05 sec)

mysql> call FOO();
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> call foo();
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql> call FOO();
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    1
Current database: test

ERROR 1289 (42000): PROCEDURE o  does not exist
mysql> use test;
Database changed
mysql> call foo();
Query OK, 0 rows affected (0.00 sec)

mysql> call FOO();
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> call foo();
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql>

What version are you running? It works perfectly for me, regardless of case.

The real issue here (with the original test case) is that with the indirect call too
'foo1', it crashes in my_error() at line 2298 in item.cc, because the variable 'name'
is NULL. The NULL itself is not the problem, it shouldn't detect an "illegal reference"
at all. The exact cause of this we haven't been able to determine yet.
(*ref)->fixed is 0, which it shouldn't be, but the real mystery is why this only happens
with the indirect call. (With the direct call to 'foo', it doesn't even call this function.)

We're running 5.0.0-alpha.

>[25 Oct 11:19am] Bill Yarnell
>We're running 5.0.0-alpha.

Then I strongly suggest that you upgrade to 5.0.1-alpha.
5.0.0 was an early release which is ten months old. A lot of things have
changed since then.

Thank you for bugreport, but this bug is already fixed in current repository:
+ CREATE TABLE BAR ( cl int );
+ create procedure foo()
+ BEGIN
+ DECLARE v_var integer;
+ SELECT IFNULL(SUM(cl), 0) INTO v_var FROM BAR;
+ END//
+ call FOO();
+ call foo();
+ call FOO();
+ call foo();
+ call FOO();
+ call FOO();
+ call foo();
+ DROP PROCEDURE IF EXISTS foo;
+ drop table BAR;
+ CREATE PROCEDURE foo()
+ BEGIN
+ DECLARE V_Var INTEGER;
+ SELECT IFNULL(SUM(c1), 0) INTO V_Var
+ FROM bar;
+ END//
+ DROP PROCEDURE IF EXISTS foo1//
+ Warnings:
+ Note  1305    PROCEDURE foo1 does not exist
+ CREATE PROCEDURE foo1()
+ BEGIN
+ CALL foo();
+ CALL foo();
+ END//
+ call foo();
+ ERROR 42S02: Table 'test.bar' doesn't exist
+ call foo();
+ ERROR 42S02: Table 'test.bar' doesn't exist
+ call foo1();
+ ERROR 42S02: Table 'test.bar' doesn't exist