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

[26 Aug 2004 15:11] Per-Erik Martin
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();
[30 Sep 2004 17:41] Bill Yarnell
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 )
[1 Oct 2004 13:48] Per-Erik Martin
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.)
[8 Oct 2004 15:40] Bill Yarnell
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>
[12 Oct 2004 16:16] Per-Erik Martin
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.)
[25 Oct 2004 18:19] Bill Yarnell
We're running 5.0.0-alpha.
[26 Oct 2004 9:14] Per-Erik Martin
>[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.
[16 May 2005 12:35] Oleksandr Byelkin
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