Bug #12072 | Incorrect results when using GROUP BY after applying an index | ||
---|---|---|---|
Submitted: | 20 Jul 2005 21:02 | Modified: | 21 Jul 2005 0:38 |
Reporter: | . . | Email Updates: | |
Status: | Closed | Impact on me: | |
Category: | MySQL Server | Severity: | S1 (Critical) |
Version: | 5.0.9 | OS: | Windows (WinXP) |
Assigned to: | CPU Architecture: | Any |
[20 Jul 2005 21:02]
. .
[20 Jul 2005 21:12]
. .
MySQL Administrator Backup dump of the database before applying the index
Attachment: zootest.sql (text/plain), 1.69 KiB.
[21 Jul 2005 0:38]
MySQL Verification Team
Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release. If necessary, you can access the source repository and build the latest available version, including the bugfix, yourself. More information about accessing the source trees is available at http://www.mysql.com/doc/en/Installing_source_tree.html Additional info: c:\mysql\bin>mysql -uroot test Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 to server version: 5.0.10-beta-nt Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> CREATE DATABASE zootest; Query OK, 1 row affected (0.03 sec) mysql> USE zootest; Database changed mysql> CREATE TABLE `zoo` ( -> `animal_id` int(11) NOT NULL, -> `species` varchar(128) NOT NULL, -> PRIMARY KEY (`animal_id`) -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1; Query OK, 0 rows affected (0.11 sec) mysql> INSERT INTO `zoo` (`animal_id`,`species`) VALUES -> (1,'monkey'), -> (2,'monkey'), -> (3,'monkey'), -> (4,'monkey'), -> (5,'monkey'), -> (6,'elephant'), -> (7,'elephant'), -> (8,'elephant'), -> (9,'lion'), -> (10,'giraffe'); Query OK, 10 rows affected (0.02 sec) Records: 10 Duplicates: 0 Warnings: 0 mysql> SELECT species, COUNT(animal_id) AS countanimal_id FROM zoo -> WHERE species NOT LIKE 'monkey%' -> GROUP BY species ORDER BY species; +----------+----------------+ | species | countanimal_id | +----------+----------------+ | elephant | 3 | | giraffe | 1 | | lion | 1 | +----------+----------------+ 3 rows in set (0.02 sec) mysql> ALTER TABLE `zootest`.`zoo` ADD INDEX `Index_2`(`species`); Query OK, 10 rows affected (0.19 sec) Records: 10 Duplicates: 0 Warnings: 0 mysql> SELECT species, COUNT(animal_id) AS countanimal_id FROM zoo -> WHERE species NOT LIKE 'monkey%' -> GROUP BY species ORDER BY species; +----------+----------------+ | species | countanimal_id | +----------+----------------+ | elephant | 3 | | giraffe | 1 | | lion | 1 | +----------+----------------+ 3 rows in set (0.02 sec)