Bug #28601 Incoherent results when using GROUP BY (regression)

Submitted: 22 May 2007 16:49 Modified: 23 May 2007 9:04
Reporter: Jorge Bernal Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server: Parser Severity:S3 (Non-critical)
Version:5.0.41 OS:Any
Assigned to: CPU Architecture:Any

[22 May 2007 16:49] Jorge Bernal
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);
[22 May 2007 16:50] Jorge Bernal
It might be a duplicate or related to #28337, but not really sure.
[23 May 2007 9:04] Sveta Smirnova
Thank you for the report.

I can not repeat described behaviour with current sources, but can with 5.0.41.

As bug #28337 is "Documenting" state now, I don't clssify this one as packaging problem, but will set status to "Can't repeat"

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.062 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.