Description:
Using KEY(CHAR, DECIMAL) gives strange results.
It happens when first part of index is CHAR or VARCHAR only, second part DECIMAL and query matches CHAR = value AND DECIMAL = value
This bug does not exist in 4.0 because of improved DECIMAL
How to repeat:
CREATE TABLE `l` (
`l1` char(12) default NULL,
`l2` decimal(8,0) default NULL,
KEY `l1` (`l1`,`l2`),
KEY `l2` (`l2`)
) TYPE=MyISAM;
-- Populate it with more than 1000 rows to make sure MySQL will use indexes
INSERT INTO l VALUES('A1234567890', 3);
mysql> SELECT * FROM l WHERE l1 = 'A1234567890' AND l2 = 3;
+-------------+------+
| l1 | l2 |
+-------------+------+
| A1234567890 | 03 |
+-------------+------+
-- Single row out of all
SELECT COUNT(*) FROM l WHERE l1 = 'A1234567890' AND l2 = 3;
+----------+
| COUNT(*) |
+----------+
| 1 |
+----------+
1 row in set (0.00 sec)
SELECT COUNT(*) FROM l USE KEY(l2) WHERE l1 = 'A1234567890' AND l2 = 3;
+----------+
| COUNT(*) |
+----------+
| 1107 |
+----------+
SELECT COUNT(*) FROM l IGNORE KEY(l1) WHERE l1 = 'A1234567890' AND l2 = 3;
+----------+
| COUNT(*) |
+----------+
| 1107 |
+----------+
Description: Using KEY(CHAR, DECIMAL) gives strange results. It happens when first part of index is CHAR or VARCHAR only, second part DECIMAL and query matches CHAR = value AND DECIMAL = value This bug does not exist in 4.0 because of improved DECIMAL How to repeat: CREATE TABLE `l` ( `l1` char(12) default NULL, `l2` decimal(8,0) default NULL, KEY `l1` (`l1`,`l2`), KEY `l2` (`l2`) ) TYPE=MyISAM; -- Populate it with more than 1000 rows to make sure MySQL will use indexes INSERT INTO l VALUES('A1234567890', 3); mysql> SELECT * FROM l WHERE l1 = 'A1234567890' AND l2 = 3; +-------------+------+ | l1 | l2 | +-------------+------+ | A1234567890 | 03 | +-------------+------+ -- Single row out of all SELECT COUNT(*) FROM l WHERE l1 = 'A1234567890' AND l2 = 3; +----------+ | COUNT(*) | +----------+ | 1 | +----------+ 1 row in set (0.00 sec) SELECT COUNT(*) FROM l USE KEY(l2) WHERE l1 = 'A1234567890' AND l2 = 3; +----------+ | COUNT(*) | +----------+ | 1107 | +----------+ SELECT COUNT(*) FROM l IGNORE KEY(l1) WHERE l1 = 'A1234567890' AND l2 = 3; +----------+ | COUNT(*) | +----------+ | 1107 | +----------+