Description:
In 5.0.27, the following query gives a different result than 5.0.41
5.0.27
------
mysql> SELECT c1.Continent, City.Name, City.Population FROM Country AS c1 INNER JOIN City ON City.CountryCode = c1.Code WHERE City.Population = (SELECT MAX(City.Population) FROM City INNER JOIN Country ON Country.Code = City.CountryCode WHERE Country.Continent = c1.Continent);
+---------------+-------------------+------------+
| Continent | Name | Population |
+---------------+-------------------+------------+
| Oceania | Sydney | 3276207 |
| South America | São Paulo | 9968485 |
| Africa | Cairo | 6789479 |
| Asia | Mumbai (Bombay) | 10500000 |
| North America | Ciudad de México | 8591309 |
| Europe | Moscow | 8389200 |
+---------------+-------------------+------------+
6 rows in set (1 min 15.86 sec)
5.0.41
------
mysql> SELECT c1.Continent, City.Name, City.Population FROM Country AS c1 INNER JOIN City ON City.CountryCode = c1.Code WHERE City.Population = (SELECT MAX(City.Population) FROM City INNER JOIN Country ON Country.Code = City.CountryCode WHERE Country.Continent = c1.Continent) GROUP BY Continent;
+-----------+-----------------+------------+
| Continent | Name | Population |
+-----------+-----------------+------------+
| Asia | Mumbai (Bombay) | 10500000 |
+-----------+-----------------+------------+
1 row in set (1 min 12.28 sec)
Actually, the GROUP BY clause seems useless, but still, the result should be as previous versions.
How to repeat:
Run the following query on a clean world database.
SELECT c1.Continent, City.Name, City.Population FROM Country AS c1 INNER JOIN City ON City.CountryCode = c1.Code WHERE City.Population = (SELECT MAX(City.Population) FROM City INNER JOIN Country ON Country.Code = City.CountryCode WHERE Country.Continent = c1.Continent);
