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:
None 
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
Description:
Running a query involving a join with a memory table, the query returns a *different* result set when the table has a HASH index.  In the initial test performed with 5.0.24 on Windows, the query returned different result sets on different runs when the memory table had a BTREE index.  (Yes, two runs of the same query on the same data returned different results.) 

Sorry the repeatable section is so long, but it is clear.

How to repeat:
Using the world database, run the following from mysql:

##########
########## BIG BAD BUG!!
##########
########## Run this script in mysql, with the world.sql file.
##########
########## Tested 25-Aug-06, mysqld-max-nt 5.0.24, Windows binary
########## Same result on    mysqld-nt 5.0.24, Windows binary
########## Tested on         mysqld-debug 5.0.22, Windows binary, different result noted below
##########
########## Switching sql_mode between '' and 'traditional' had no impact.
##########

# Build a fresh world database

#################### 
# NOTE: For first run of this file, uncomment the next four lines.
#       For subsequent runs, comment these lines out again, *especially*
#       to see somewhat *random* results in the last query.

 drop database world;
 create database world;
 use world;
 source world.sql;

# First pass on the query

use world;

alter table city engine=myisam;

# Not sure what this query really should do.  Intent was to list the largest city in
# each country in Europe.  Add city.name to the GROUP BY clause makes the problem results
# below go away.

select country.Name 'Country', city.Name 'City', city.Population, MAX(city2.Population) 'Largest' 
from country, city, city city2 where city.CountryCode = country.Code 
and city2.CountryCode = country.Code 
and Continent = 'Europe' 
GROUP BY country.Name 
HAVING `Population` = `Largest`;

######
# 46 records returned

# 

alter table city engine = memory;

select country.Name 'Country', city.Name 'City', city.Population, MAX(city2.Population) 'Largest' 
from country, city, city city2 where city.CountryCode = country.Code 
and city2.CountryCode = country.Code 
and Continent = 'Europe' 
GROUP BY country.Name 
HAVING `Population` = `Largest`;

######
# Same query again, 46 records returned

# 

create index city_ccode_hash on city(countrycode);

select country.Name 'Country', city.Name 'City', city.Population, MAX(city2.Population) 'Largest' 
from country, city, city city2 where city.CountryCode = country.Code 
and city2.CountryCode = country.Code 
and Continent = 'Europe' 
GROUP BY country.Name 
HAVING `Population` = `Largest`;

######
# Same query again, 9 records !!!!!

#

alter table city drop index city_ccode_hash;

create index city_ccode_btree using btree on city(countrycode);

select country.Name 'Country', city.Name 'City', city.Population, MAX(city2.Population) 'Largest' 
from country, city, city city2 where city.CountryCode = country.Code 
and city2.CountryCode = country.Code 
and Continent = 'Europe' 
GROUP BY country.Name 
HAVING `Population` = `Largest`;

######
# Same query again, 14 records, or 16 records, or 19, or 13, or... !!!!!!!!!!!!!!!!!!!!!!!!
# On mysqld-debug 5.0.22, this last query returned 46 records.

alter table city drop index city_ccode_btree;
[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`.