Description:
A memory table, compared to mysiam, is returning results from a select backwards. This happens when using a where clause containing an ENUM value.
I'm not sure I'm explaining this right, but the test case is very clear.
How to repeat:
# The setup...
CREATE TABLE `myisam_sort` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`fl` FLOAT NOT NULL ,
`filter` ENUM( 'us', 'de', 'uk' ) NOT NULL ,
INDEX ( `filter` )
) TYPE = MYISAM ;
CREATE TABLE `memory_sort` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`fl` FLOAT NOT NULL ,
`filter` ENUM( 'us', 'de', 'uk' ) NOT NULL ,
INDEX ( `filter` )
) TYPE = memory;
CREATE TABLE `raw_vals` (
`fl` float NOT NULL,
`filter` char(2) NOT NULL
) ENGINE=MyISAM;
# put data in source table
INSERT INTO raw_vals (`fl`,`filter`) VALUES
(5.5,'US'),(10.5,'US'),(1.7,'US'),
(6.5,'UK'),(9.5,'UK'),(1.2,'UK'),
(5.23,'DE'),(8.5,'DE'),(4.2,'DE');
# move data into sorted tables for fast lookups
# (presorted data means queries won't need expensive ORDER BY clauses.)
INSERT INTO memory_sort (`fl`,`filter`) SELECT `fl`,`filter` FROM raw_vals ORDER BY fl DESC;
INSERT INTO myisam_sort (`fl`,`filter`) SELECT `fl`,`filter` FROM raw_vals ORDER BY fl DESC;
# Now watch the craziness as selects are done...
SELECT * FROM `myisam_sort`;
#+----+------+--------+
#| id | fl | filter |
#+----+------+--------+
#| 1 | 10.5 | us |
#| 2 | 9.5 | uk |
#| 3 | 8.5 | de |
#| 4 | 6.5 | uk |
#| 5 | 5.5 | us |
#| 6 | 5.23 | de |
#| 7 | 4.2 | de |
#| 8 | 1.7 | us |
#| 9 | 1.2 | uk |
#+----+------+--------+
#9 rows in set (0.00 sec)
SELECT * FROM `memory_sort`;
#+----+------+--------+
#| id | fl | filter |
#+----+------+--------+
#| 1 | 10.5 | us |
#| 2 | 9.5 | uk |
#| 3 | 8.5 | de |
#| 4 | 6.5 | uk |
#| 5 | 5.5 | us |
#| 6 | 5.23 | de |
#| 7 | 4.2 | de |
#| 8 | 1.7 | us |
#| 9 | 1.2 | uk |
#+----+------+--------+
#9 rows in set (0.00 sec)
SELECT * FROM `myisam_sort` WHERE filter='us';
#+----+------+--------+
#| id | fl | filter |
#+----+------+--------+
#| 1 | 10.5 | us |
#| 5 | 5.5 | us |
#| 8 | 1.7 | us |
#+----+------+--------+
#3 rows in set (0.00 sec)
SELECT * FROM `memory_sort` WHERE filter='us';
#+----+------+--------+
#| id | fl | filter |
#+----+------+--------+
#| 8 | 1.7 | us |
#| 5 | 5.5 | us |
#| 1 | 10.5 | us |
#+----+------+--------+
#3 rows in set (0.00 sec)
