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:Microsoft Windows (Windows XP/Linux)
Assigned to: Bugs System

[13 Jan 2006 5:18] Frank Bao
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()`
[13 Jan 2006 10:11] Miguel Solorzano
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;
[9 Mar 2006 13:46] Per-Erik Martin
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.)
[10 Mar 2006 11:40] Per-Erik Martin
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.
[10 Mar 2006 13:04] Bugs System
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
[28 Mar 2006 12:16] Bugs System
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
[29 Mar 2006 18:12] Konstantin Osipov
Pushed into 5.0.20, merged into 5.1.8
[30 Mar 2006 9:22] Per-Erik Martin
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.
[31 Mar 2006 6:50] Jon Stephens
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.