Bug #21846 | Query with memory table returns different results if indexed | ||
---|---|---|---|
Submitted: | 26 Aug 2006 1:43 | Modified: | 5 Sep 2006 14:32 |
Reporter: | Glynn Durham | Email Updates: | |
Status: | Not a Bug | Impact on me: | |
Category: | MySQL Server: Memory storage engine | Severity: | S3 (Non-critical) |
Version: | 5.0.25-BK, 5.0.24 | OS: | Linux (Linux, Windows) |
Assigned to: | Sergey Vojtovich | CPU Architecture: | Any |
[26 Aug 2006 1:43]
Glynn Durham
[26 Aug 2006 8:31]
Valeriy Kravchuk
Thank you for a bug report. Verified just as described with 5.0.25-BK on Linux: mysql> create index city_ccode_hash on City(countrycode); Query OK, 4079 rows affected (0.03 sec) Records: 4079 Duplicates: 0 Warnings: 0 mysql> select Country.Name 'Country', City.Name 'City', City.Population, MAX(ci ty2.Population) 'Largest' from Country, City, City city2 where City.CountryCod e = Country.Code and city2.CountryCode = Country.Code and Continent = 'Europe ' GROUP BY Country.Name HAVING `Population` = `Largest`; ... 9 rows in set (0.68 sec) mysql> alter table City drop index city_ccode_hash; Query OK, 4079 rows affected (0.03 sec) Records: 4079 Duplicates: 0 Warnings: 0 mysql> create index city_ccode_btree using btree on City(countrycode); Query OK, 4079 rows affected (0.35 sec) Records: 4079 Duplicates: 0 Warnings: 0 mysql> select Country.Name 'Country', City.Name 'City', City.Population, MAX(ci ty2.Population) 'Largest' from Country, City, City city2 where City.CountryCod e = Country.Code and city2.CountryCode = Country.Code and Continent = 'Europe ' GROUP BY Country.Name HAVING `Population` = `Largest`; ... 15 rows in set (0.70 sec)
[5 Sep 2006 14:32]
Sergey Vojtovich
The query described above is wrong. HAVING is applied _after_ GROUP BY and there is no guarantee that GROUP BY returns MAX(Population) as `Population`.