MySQL Bugs: #37785: Order by works different for Memory storage engine

Bug #37785	Order by works different for Memory storage engine
Submitted:	1 Jul 2008 21:47	Modified:	2 Jul 2008 17:27
Reporter:	Hema Sridharan	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Memory storage engine	Severity:	S3 (Non-critical)
Version:	mysql-6.0-backup, mysql-maria	OS:	Linux
Assigned to:	Sveta Smirnova	CPU Architecture:	Any

Description:
1) I create database and tables in the database.
2) I insert some values in the tables.
3) I perform select operation using order by. I get different results for memory storage engine compared against all other engines. 

CREATE DATABASE bup_ts;
CREATE TABLE bup_ts.cap(
id SMALLINT,
country CHAR(20),
city VARCHAR(20),
population BIGINT,
capital CHAR(20)
);
INSERT INTO bup_ts.cap VALUES
(5,'US','sacramento',0,'DC'),(6,'IND','jammu',0,'Deli'),
(7,'Russia','xx',0,'Moscow'),(8,'IND','hyd',647747,'Deli');

How to repeat:
mysql> CREATE DATABASE bup_ts;
Query OK, 1 row affected (0.00 sec)

mysql> use bup_ts;
Database changed

mysql> CREATE TABLE bup_ts.cap(
    -> id SMALLINT,
    -> country CHAR(20),
    -> city VARCHAR(20),
    -> population BIGINT,
    -> capital CHAR(20)
    -> );
Query OK, 0 rows affected (0.04 sec)

mysql> INSERT INTO bup_ts.cap VALUES
    -> (5,'US','sacramento',0,'DC'),(6,'IND','jammu',0,'Deli'),
    -> (7,'Russia','xx',0,'Moscow'),(8,'IND','hyd',647747,'Deli');

mysql> select * from cap order by population;
+------+---------+------------+------------+---------+
| id   | country | city       | population | capital |
+------+---------+------------+------------+---------+
|    5 | US      | sacramento |          0 | DC      |
|    6 | IND     | jammu      |          0 | Deli    |
|    7 | Russia  | xx         |          0 | Moscow  |
|    8 | IND     | hyd        |     647747 | Deli    |
+------+---------+------------+------------+---------+

mysql> alter table cap engine=memory;
Query OK, 4 rows affected (0.04 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from cap order by population;
+------+---------+------------+------------+---------+
| id   | country | city       | population | capital |
+------+---------+------------+------------+---------+
|    6 | IND     | jammu      |          0 | Deli    |
|    7 | Russia  | xx         |          0 | Moscow  |
|    5 | US      | sacramento |          0 | DC      |
|    8 | IND     | hyd        |     647747 | Deli    |
+------+---------+------------+------------+---------+

If you notice here, table has been altered to memory engine type. Then if we perform the select * from cap using order by, shows different order of the table compared to other engine types (Innodb, Falcon, Myisam)

Thank you for the report.

Verified as described. Only team trees affected.

order is enforced only by ORDER BY/GROUP BY - different implicit order is not a bug

'reproduced' with InnoDB too :)

mysql> create table o1 (a int primary key, b int) engine=MyISAM;
Query OK, 0 rows affected (0.06 sec)

mysql> insert into o1 values (3,1),(2,1),(1,1);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from o1 order by b;
+---+------+
| a | b    |
+---+------+
| 3 |    1 | 
| 2 |    1 | 
| 1 |    1 | 
+---+------+
3 rows in set (0.00 sec)

mysql> alter table o1 engine=InnoDB;
Query OK, 3 rows affected (0.38 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from o1 order by b;
+---+------+
| a | b    |
+---+------+
| 1 |    1 | 
| 2 |    1 | 
| 3 |    1 | 
+---+------+
3 rows in set (0.00 sec)

If only the team trees are affected it's possible that this is an unintended change in behavior.