Bug #57356 MyISAM and InnoDB output difference on LIMIT query when PRIMARY KEY is used

• Submitted: 10 Oct 2010 21:21
• Modified: 11 Oct 2010 1:23
• Reporter: Roel Van de Paar
• Status: Not a Bug
• Category: MySQL Server: InnoDB storage engine
• Severity: S1 (Critical)
• Version: 5.0, 5.1, 5.5.6rc
• OS: Any
• CPU Architecture: Any

[10 Oct 2010 21:21] Roel Van de Paar

Description:
Without PRIMARY KEY(`chars`(100)):

mysql> SELECT * FROM a LIMIT 500,1;
+------+-------+
| id   | chars |
+------+-------+
|  501 | ☺§    |
+------+-------+
1 row in set (0.00 sec)

mysql> SELECT * FROM b LIMIT 500,1;
+------+-------+
| id   | chars |
+------+-------+
|  501 | ☺§    |
+------+-------+
1 row in set (0.00 sec)

With PRIMARY KEY(`chars`(100)):

mysql> SELECT * FROM a LIMIT 500,1;
+------+-------+
| id   | chars |
+------+-------+
|  501 | ☺§    |
+------+-------+
1 row in set (0.00 sec)

mysql> SELECT * FROM b LIMIT 500,1;
+------+-------+
| id   | chars |
+------+-------+
|  754 | ☻‗    |
+------+-------+
1 row in set (0.00 sec)

How to repeat:
DELIMITER //
DROP PROCEDURE IF EXISTS difference//
DROP TABLE IF EXISTS a//
DROP TABLE IF EXISTS b//
CREATE PROCEDURE difference(IN numrows INT)
BEGIN
DECLARE a INT;
SET a = 1;
CREATE TABLE `a` (`id` int, `chars` blob) ENGINE=MyISAM;
CREATE TABLE `b` (`id` int, `chars` blob) ENGINE=InnoDB; 
WHILE (a < numrows) DO
INSERT INTO a VALUES (a,CHAR(a));
INSERT INTO b VALUES (a,CHAR(a)); 
SET a=a+1;
END WHILE;
END;
//
DELIMITER ;
CALL difference(1000); 
SELECT * FROM a LIMIT 500,1;
SELECT * FROM b LIMIT 500,1;

DELIMITER //
DROP PROCEDURE IF EXISTS difference//
DROP TABLE IF EXISTS a//
DROP TABLE IF EXISTS b//
CREATE PROCEDURE difference(IN numrows INT)
BEGIN
DECLARE a INT;
SET a = 1;
CREATE TABLE `a` (`id` int, `chars` blob, PRIMARY KEY(`chars`(100))) ENGINE=MyISAM;
CREATE TABLE `b` (`id` int, `chars` blob, PRIMARY KEY(`chars`(100))) ENGINE=InnoDB; 
WHILE (a < numrows) DO
INSERT INTO a VALUES (a,CHAR(a));
INSERT INTO b VALUES (a,CHAR(a)); 
SET a=a+1;
END WHILE;
END;
//
DELIMITER ;
CALL difference(1000); 
SELECT * FROM a LIMIT 500,1;
SELECT * FROM b LIMIT 500,1;

[10 Oct 2010 21:23] Roel Van de Paar

Without PRIMARY KEY(`chars`(100)):

mysql> explain SELECT * FROM b LIMIT 500,1;
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | b     | ALL  | NULL          | NULL | NULL    | NULL |  825 |       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
1 row in set (0.00 sec)

With PRIMARY KEY(`chars`(100)):

mysql> explain SELECT * FROM b LIMIT 500,1;
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | b     | ALL  | NULL          | NULL | NULL    | NULL |  786 |       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
1 row in set (0.00 sec)

[10 Oct 2010 21:49] Peter Laursen

also reproducible for me with MySQL 5.1.51 (64 bit Windows)

[10 Oct 2010 21:52] Peter Laursen

and on 5.5.6

[10 Oct 2010 21:58] Roel Van de Paar

Verified on 5.5.6rc. Same result with InnoDB Plugin.

[10 Oct 2010 21:59] Peter Laursen

and also 5.0.90

[11 Oct 2010 0:51] MySQL Verification Team

What is the bug? Order isn't guaranteed unless you use and ORDER BY clause.

[11 Oct 2010 1:23] Roel Van de Paar

Correct, forgot about ORDER BY requirement.