Bug #31235 ORDER BY on MEMORY table does not working in Cursor in Stored Procedure
Submitted: 27 Sep 2007 12:49 Modified: 27 Sep 2007 13:07
Reporter: Jeroen Binnekamp Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S3 (Non-critical)
Version:MySQL 5.0.45-community-nt OS:Windows (Vista)
Assigned to: CPU Architecture:Any

[27 Sep 2007 12:49] Jeroen Binnekamp
Description:
Using ORDER BY in a cursor does not seem to work on MEMORY tables. I have been able to reproduce this only on my local 
MySQL 5.0.45-community-nt on Windows Vista.

This does not seem to be a problem on our Database servers running under Linux.
(tested with 5.0.18-Max)

How to repeat:
To Reproduce run script below, after running the script check the contents of the targetdates table, the dates should be ordered but they are not, when you change the engine of sourcedates to MYISAM or INNODB you will see that the result is ordered.:

drop database if exists testbase;
create database testbase;

use testbase;

/*
  Create the source table only containing a date field.
*/
create table sourcedates (
  MyDate DATE NOT NULL,
  PRIMARY KEY(MyDate)
) ENGINE=MEMORY;
/*
  Replace line above with e.g.
) ENGINE=INNODB;
  or
) ENGINE=MYISAM;
  and it does work.
*/

/*
  Fill the source table with some unordered dates.
*/
insert into sourcedates (MyDate) values (20070401);
insert into sourcedates (MyDate) values (20070201);
insert into sourcedates (MyDate) values (20070301);
insert into sourcedates (MyDate) values (20070101);

/*
  Create the target table where the dates need to be copied to.
*/
create table targetdates (
  MyDate DATE NOT NULL,
  PRIMARY KEY(MyDate)
) ENGINE=MEMORY;

/*
  Create the stored procedure used to copy the dates.
*/
DELIMITER $$

DROP PROCEDURE IF EXISTS CopyDates $$
CREATE PROCEDURE CopyDates ()
BEGIN
  DECLARE z INT default 0;

  DECLARE currentdate DATE;

  DECLARE sp1_cursor CURSOR FOR SELECT * FROM sourcedates ORDER BY MyDate;
/*
  ORDER BY is ignored when table sourcesdates is ENGINE=MEMORY
  (Running the above query from e.g. Query Browser sorts just fine even the ENGINE=MEMORY
*/

  DECLARE CONTINUE HANDLER FOR NOT FOUND SET z = 1;

  OPEN sp1_cursor;

  myloop: LOOP

    FETCH sp1_cursor INTO currentdate;
    IF z THEN
      CLOSE sp1_cursor;
      LEAVE myloop;
    END IF;

    insert into targetdates (MyDate) values (currentdate);

  END LOOP;

END $$

DELIMITER ;

/*
  Run the stored procedure.
*/
call CopyDates();
[27 Sep 2007 13:07] MySQL Verification Team
Thank you for the bug report. I can't repeat the issue reported with latest
Windows source server running on Windows Vista, maybe it was fixed somehow:

mysql> show variables like "%version%";
+-------------------------+---------------------+
| Variable_name           | Value               |
+-------------------------+---------------------+
| protocol_version        | 10                  |
| version                 | 5.0.50-nt           |
| version_comment         | Source distribution |
| version_compile_machine | unknown             |
| version_compile_os      | Win64               |
+-------------------------+---------------------+
5 rows in set (0.00 sec)

mysql> select * from targetdates;
+------------+
| MyDate     |
+------------+
| 2007-01-01 |
| 2007-02-01 |
| 2007-03-01 |
| 2007-04-01 |
+------------+
4 rows in set (0.00 sec)