MySQL Bugs: #16474: SP crashed MySQL

Bug #16474	SP crashed MySQL
Submitted:	13 Jan 2006 5:18	Modified:	31 Mar 2006 6:50
Reporter:	Frank Bao	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S2 (Serious)
Version:	5.0.18/5.0.19 BK	OS:	Windows (Windows XP/Linux)
Assigned to:	Per-Erik Martin	CPU Architecture:	Any

Description:
I have a SP as below, which is simplified from a SP works well in my project.

CREATE PROCEDURE trans()
BEGIN
     /* flag to indicate if cursor move to the end */
   DECLARE done INT DEFAULT 0;

   DECLARE cTime INT default 0;

   DECLARE nowTime INT default 0;

   DECLARE varsys INT;

   DECLARE varvalue INT;

   DECLARE var1, var2, var3 INT DEFAULT 0;   

   DECLARE allTime CURSOR FOR SELECT DISTINCT `time` FROM `testflow` where varsys in (1,2,3) ORDER BY `time`;

   DECLARE allData CURSOR FOR SELECT `time`,`varsys`,`varvalue` FROM testflow where varsys in (1,2,3) ORDER BY `time`,varsys;

    /* set done to 1 when cursor move to the end */
   DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1; 

   DROP TABLE IF EXISTS `aftertrans`;

   CREATE TABLE `aftertrans` ( `time` INT DEFAULT 0, `1` INT , `2` INT , `3` INT, PRIMARY KEY (`time`)) ENGINE = MyISAM ;

     /* Get the initial value */
   OPEN allTime;
   OPEN allData;
   FETCH allTime INTO cTime;
   FETCH allDATA INTO nowTime, varsys, varvalue;

    /* loop body */
   REPEAT
      BEGIN           
         /* add all var_value with same time point to a row */
         WHILE  (cTime = nowTime AND done != 1) DO
            IF varsys = 1 THEN
               SET var1 = varvalue;
            ELSEIF varsys = 2 THEN
               SET var2 = varvalue;
            ELSEIF varsys = 3 THEN 
               SET var3 = varvalue;
            END IF;
            FETCH allDATA INTO nowTime, varsys, varvalue; /* move cursor to next */
         END WHILE;

         /* Insert a row */
         INSERT INTO `aftertrans` VALUES (cTime, var1, var2,var3) ;

         /* clear all variables*/
         SET var1 = 0;
         SET var2 = 0;
         SET var3 = 0;

         /* fetch next time */
         FETCH allTime INTO cTime;
      END;
   UNTIL done = 1 END REPEAT;

   /* close cursor */
   CLOSE allTime;
   CLOSE allData;
END

    I use MySQL Administrator 1.1.6 to create this SP, and it was created successfully. But whenever I call it from MySQL Query Browser 1.1.18, the server crashed. It has the same logic with a working SP, which to transform a table:

CREATE TABLE testflow (
   `time`  INT,
   `varsys` INT,
   `varvalue` INT,
    PRIMARY KEY(`time`)
)ENGINE = MyISAM;

to another table:

CREATE TABLE aftertrans(
   `time` INT,
   `1`  INT,
   `2`  INT
)ENGINE = MyISAM;

where 1, 2 is value of testflow.varsys.
    Ideally, the value of corresponding varsys should be inserted into `aftertrans`.

How to repeat:
1. Create `testflow` using syntax listed above
2. Create SP `trans()` using syntax listed above
3. Call `trans()`

Thank you for the bug report. I was able to repeat;

    ->    /* close cursor */
    ->    CLOSE allTime;
    ->    CLOSE allData;
    -> END
    -> //
Query OK, 0 rows affected (0.03 sec)

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

miguel@hegel:~/dbs/5.0> libexec/mysqld
030413  7:49:09  InnoDB: Started; log sequence number 0 57638
030413  7:49:09 [Note] libexec/mysqld: ready for connections.
Version: '5.0.19-debug'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution
mysqld: item.cc:834: virtual longlong Item_sp_variable::val_int(): Assertion `fixed' failed.
mysqld got signal 6;

A smaller testcase:

create procedure bug16474()
begin
   declare nowtime int default 0;
   declare varvalue int;
   declare varsys int;
   declare alldata cursor for select time,t.varsys,varvalue from testflow t
     where t.varsys in (1,2,3) order by time,varsys;

   open alldata;
   fetch alldata into nowtime, varsys, varvalue;
   close alldata;
end

The problem is the use of the local variable 'varsys' which is the same as the column name in testflow. Note the use of 'varsys' in the "order by" clause.
Renaming the variable, or prefixing the column with 't.' in the query, works around the problem.
(It's still a bug of course, it should not crash in any case.)

An even shorter test case:

create table t3 ( x int );
delimiter //
create procedure bug16474()
begin
   declare y int;

   select t3.x from t3 order by y;
end//
delimiter ;
call bug16474();

The variable 'y' does not have to have the same name as a column, any variable will do.

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/3701

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/4232

Pushed into 5.0.20, merged into 5.1.8

This might require some clearification in the documentation.
The confusing issue is the special treatment of integers in "order by", where an explicit integer 'n' means the 'n:th' column. (Now a deprecated feature I think.)
The "order by" parameter can be either:
1) an integer
2) a column name
3) an expression

Only in 1) is it interpreted as a column index. Case 3) is the confusing one, since pointless  exressions like "1+1" will not affect the ordering (i.e. it does not mean "2:nd column").
Since variables are expressions these too do not change the ordering:

set @x = 1;
select t3.x from t3 order by @x;

create procedure bug16474()
begin
   declare y int;

   select t3.x from t3 order by y;
end

i.e. "@x" and "y" above are interpreted as expressions.

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

Documented bugfix in 5.0.20 and 5.1.8 changelogs, with a warning about non-standard/deprecated functionality. Closed.