Description:
Hi all,
looks like MySQL has problems computing agregatee lists if no aggregation function is contained in the SELECT column list and the optimizer decides to use the strategy "Using index for group-by". In the given example too few results have been returned. The example is based on the world training database.
Tested with 5.0.3-beta and 5.0.4-beta
linux:/home/nixnutz/src/training/data # uname -a
Linux linux 2.6.8-24.14-smp #1 SMP Tue Mar 29 09:27:43 UTC 2005 i686 i686 i386 GNU/Linux
Best regards,
Ulf
How to repeat:
Load the table dump and execute the following SQL commands.
mysql> select version();
+------------------+
| version() |
+------------------+
| 5.0.4-beta-debug |
+------------------+
1 row in set (0.00 sec)
mysql> select country, language from country_language where language = 'Russian';
+---------+----------+
| country | language |
+---------+----------+
| AZE | Russian |
| BLR | Russian |
| EST | Russian |
| FIN | Russian |
| GEO | Russian |
| ISR | Russian |
| KAZ | Russian |
| KGZ | Russian |
| LTU | Russian |
| LVA | Russian |
| MDA | Russian |
| RUS | Russian |
| SJM | Russian |
| TJK | Russian |
| TKM | Russian |
| UKR | Russian |
| UZB | Russian |
+---------+----------+
17 rows in set (0.00 sec)
mysql> explain select count(*), country, language from country_language where language = 'Russian' group by country;
+----+-------------+------------------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------------+-------+---------------+---------+---------+------+------+--------------------------+
| 1 | SIMPLE | country_language | index | NULL | PRIMARY | 33 | NULL | 983 | Using where; Using index |
+----+-------------+------------------+-------+---------------+---------+---------+------+------+--------------------------+
1 row in set (0.00 sec)
mysql> select count(*), country, language from country_language where language = 'Russian' group by country;
+----------+---------+----------+
| count(*) | country | language |
+----------+---------+----------+
| 1 | AZE | Russian |
| 1 | BLR | Russian |
| 1 | EST | Russian |
| 1 | FIN | Russian |
| 1 | GEO | Russian |
| 1 | ISR | Russian |
| 1 | KAZ | Russian |
| 1 | KGZ | Russian |
| 1 | LTU | Russian |
| 1 | LVA | Russian |
| 1 | MDA | Russian |
| 1 | RUS | Russian |
| 1 | SJM | Russian |
| 1 | TJK | Russian |
| 1 | TKM | Russian |
| 1 | UKR | Russian |
| 1 | UZB | Russian |
+----------+---------+----------+
17 rows in set (0.00 sec)
mysql> explain select country, language from country_language where language = 'Russian' group by country;
+----+-------------+------------------+-------+---------------+---------+---------+------+------+---------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------------+-------+---------------+---------+---------+------+------+---------------------------------------+
| 1 | SIMPLE | country_language | range | NULL | PRIMARY | 33 | NULL | 492 | Using where; Using index for group-by |
+----+-------------+------------------+-------+---------------+---------+---------+------+------+---------------------------------------+
1 row in set (0.00 sec)
mysql> select country, language from country_language where language = 'Russian' group by country;
+---------+----------+
| country | language |
+---------+----------+
| TJK | Russian |
+---------+----------+
1 row in set (0.01 sec)
