Description:
Very briefly:
1. INSERT data A..Z
2. SELECT w/MIN and GROUP BY some of data X
3. Correct result is returned
4. DELETE from data just X
5. INSERT data X again
6. Run same query as #2 again
7. Incorrect result is returned
Longer example. "/tmp/bug" is all the data (i.e., A..Z). "/tmp/bug2" is a sub-set of /tmp/bug (i.e., X):
mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> describe Host_Hits;
+-----------+--------------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+--------------------------+------+-----+---------+----------------+
| id | int(10) unsigned | | PRI | NULL | auto_increment |
| ymd | int(10) unsigned | | | 0 | |
| hms | int(6) unsigned zerofill | | | 000000 | |
| host_id | int(10) unsigned | | | 0 | |
| hit | varchar(255) | | | | |
| status | smallint(5) unsigned | | | 0 | |
| bytes | int(10) unsigned | YES | | 0 | |
| ref_id | int(10) unsigned | YES | | NULL | |
| username | varchar(64) | YES | | NULL | |
| se_hit_id | int(10) unsigned | YES | | NULL | |
+-----------+--------------------------+------+-----+---------+----------------+
10 rows in set (0.06 sec)
mysql> show indexes from Host_Hits;
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Host_Hits | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
1 row in set (0.00 sec)
mysql> load data infile '/tmp/bug' into table Host_Hits;
Query OK, 551 rows affected (0.09 sec)
Records: 551 Deleted: 0 Skipped: 0 Warnings: 0
mysql> select * from Host_Hits where ymd=20050912 and host_id=42 order by hms;
+-----+----------+--------+---------+---------------+--------+-------+--------+----------+-----------+
| id | ymd | hms | host_id | hit | status | bytes | ref_id | username | se_hit_id |
+-----+----------+--------+---------+---------------+--------+-------+--------+----------+-----------+
| 216 | 20050912 | 154425 | 42 | / | 200 | 3110 | NULL | NULL | NULL |
| 217 | 20050912 | 154435 | 42 | / | 200 | 3110 | NULL | NULL | NULL |
| 218 | 20050912 | 154449 | 42 | / | 200 | 3110 | NULL | NULL | NULL |
| 219 | 20050912 | 154512 | 42 | /mysqlreport | 200 | 4467 | 15 | NULL | NULL |
| 220 | 20050912 | 154519 | 42 | /mysqlprofile | 200 | 2919 | 26 | NULL | NULL |
| 221 | 20050912 | 154523 | 42 | /optimize | 200 | 2248 | 27 | NULL | NULL |
| 222 | 20050912 | 154539 | 42 | /optimize | 200 | 2248 | 28 | NULL | NULL |
| 223 | 20050912 | 154545 | 42 | /intricacies | 200 | 2226 | 28 | NULL | NULL |
| 224 | 20050912 | 154547 | 42 | /scripts/ | 200 | 2236 | 33 | NULL | NULL |
| 225 | 20050912 | 154552 | 42 | /feedback | 200 | 1560 | 33 | NULL | NULL |
| 226 | 20050912 | 154557 | 42 | /consult | 200 | 2035 | 36 | NULL | NULL |
| 227 | 20050912 | 154618 | 42 | / | 200 | 3110 | 39 | NULL | NULL |
+-----+----------+--------+---------+---------------+--------+-------+--------+----------+-----------+
12 rows in set (0.05 sec)
mysql> SELECT ref_id, se_hit_id, MIN(hms) FROM Host_Hits WHERE ymd=20050912 AND host_id=42 GROUP BY host_id;
+--------+-----------+----------+
| ref_id | se_hit_id | MIN(hms) |
+--------+-----------+----------+
| NULL | NULL | 154425 |
+--------+-----------+----------+
1 row in set (0.06 sec)
mysql> delete from Host_Hits where ymd=20050912;
Query OK, 442 rows affected (0.09 sec)
mysql> load data infile '/tmp/bug2' into table Host_Hits;
Query OK, 442 rows affected (0.09 sec)
Records: 442 Deleted: 0 Skipped: 0 Warnings: 0
mysql> SELECT ref_id, se_hit_id, MIN(hms) FROM Host_Hits WHERE ymd=20050912 AND host_id=42 GROUP BY host_id;
+--------+-----------+----------+
| ref_id | se_hit_id | MIN(hms) |
+--------+-----------+----------+
| 39 | NULL | 154425 |
+--------+-----------+----------+
1 row in set (0.06 sec)
mysql> \q
That last SELECT, where ref_id is returned as 39 is incorrect, as can be seen from the SELECT *, the record where hms is 154425 has NULL for ref_id.
Background info:
ymd and hms is a chopped up timestamp, Year-Month-Day, Hour-Minute-Second. host_id is a unique ID for whatever host. The idea is to select the first record for a given host on a given day.
Workaround:
SELECT ref_id, se_hit_id, hms FROM Host_Hits WHERE ymd=20050912 AND host_id=42 ORDER BY hms LIMIT 1;
Attached are min_bug.sql to create the exact table I'm using, min_bug-data_all.txt (aka /tmp/bug), and min_bug-data_20050912 (aka /tmp/bug2).
How to repeat:
1. Load min_bug.sql
2. Load infile min_bug-data_all.txt
3. SELECT ref_id, se_hit_id, MIN(hms) FROM Host_Hits WHERE ymd=20050912 AND host_id=42 GROUP BY host_id;
4. delete from Host_Hits where ymd=20050912;
5. Load infile min_bug-data_20050912.txt
6. SELECT ref_id, se_hit_id, MIN(hms) FROM Host_Hits WHERE ymd=20050912 AND host_id=42 GROUP BY host_id;
