MySQL Bugs: #13538: Query giving error if referred field is indexed

Bug #13538	Query giving error if referred field is indexed
Submitted:	27 Sep 2005 20:02	Modified:	27 Sep 2005 20:58
Reporter:	Alessandro Polverini	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.1.11a-4sarge1	OS:	Linux (Debian Sarge)
Assigned to:	MySQL Verification Team	CPU Architecture:	Any

Description:
Hello,
I have a table with a lot of records (around half million).
This is the table structure:
mysql> desc Operation;
+----------------+--------------+------+-----+---------+-------+
| Field          | Type         | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+-------+
| id             | varchar(30)  |      | PRI |         |       |
| date           | datetime     | YES  | MUL | NULL    |       |
| sessionId      | varchar(100) | YES  |     | NULL    |       |
| loggedUid      | varchar(100) | YES  |     | NULL    |       |
| workingUid     | varchar(100) | YES  |     | NULL    |       |
| siteId         | varchar(30)  | YES  | MUL | NULL    |       |
| nut            | varchar(30)  | YES  |     | NULL    |       |
| op             | varchar(50)  | YES  | MUL | NULL    |       |
| elapsed        | int(11)      | YES  |     | NULL    |       |
| opExceptionId  | varchar(30)  | YES  |     | NULL    |       |
| requestDate    | datetime     | YES  | MUL | NULL    |       |
| url            | text         | YES  |     | NULL    |       |
| result         | text         | YES  |     | NULL    |       |
| remoteAddress  | varchar(16)  | YES  |     | NULL    |       |
| referer        | varchar(255) | YES  |     | NULL    |       |
| firstSessionId | varchar(100) | YES  |     | NULL    |       |
| userAgent      | varchar(255) | YES  |     | NULL    |       |
+----------------+--------------+------+-----+---------+-------+

This is the query I want to execute:
SELECT referer,count(*) as num FROM `Operation` group by referer order by num desc limit 5;
giving the result:
+---------------------------------------------------------------+--------+
| referer                                                       | num    |
+---------------------------------------------------------------+--------+
| NULL                                                          | 177936 |
| http://editarea.com/iw/multiadm/main/index.jsp                |   7930 |
| http://iw.nibbles.it/iw/multiadm/main/index.jsp               |   5280 |
| http://www.editarea.com/iw/core/main/page.jsp?menuId=menu1563 |   4810 |
| http://dominuse.editarea.com/iw/adm/main/index.jsp            |   3736 |
+---------------------------------------------------------------+--------+
5 rows in set (32.59 sec)

I now create an index:
mysql> create index referer on Operation(referer);
Query OK, 397017 rows affected (1 min 31.50 sec)
Records: 397017  Duplicates: 0  Warnings: 0

and re-exec the query, that this time gives the error:
mysql> SELECT referer,count(*) as num FROM `Operation` group by referer order by num desc limit 5;
ERROR 1032 (HY000): Can't find record in ''

I'm stuck and don't know how to solve the problem.
I tried:
- changing the column size
- changing the collation
- doing both
without any results.

How to repeat:
Download the dump here (warning, 7.5Mb file, I was unable to delete more records and reproduce the problem):
http://nb1.nibbles.it/OperationDump.sql.bz2

And re-exec the query on your server: the behaviour should be the same (I hope :) ).

I do not have other version of mysql around to make tests on other platform.

System used is Dual Opteron with 4Gb RAM, and kernel 2.6.11-1-k7-smp.

Suggested fix:
none

We need a test case, if you can upload the tables for to repeat in
our side:

ftp://ftp.mysql.com:/pub/mysql/upload

with a name like bug13538.*

would be nice.

Thanks in advance.

Sorry I meant the dump of the tables not the tables.

Hello Miguel,
I've uploaded the file bug13538.sql.bz2 in the ftp site indicated by you.

Hth,
Alex

I was unable to repeat with current source server, then most probably
an issue already fixed which was present on your server version:

mysql> SELECT referer,count(*) as num FROM `Operation` group by referer order by
    -> num desc limit 5;
+---------------------------------------------------------------+--------+
| referer                                                       | num    |
+---------------------------------------------------------------+--------+
| NULL                                                          | 177936 |
| http://editarea.com/iw/multiadm/main/index.jsp                |   7930 |
| http://iw.nibbles.it/iw/multiadm/main/index.jsp               |   5280 |
| http://www.editarea.com/iw/core/main/page.jsp?menuId=menu1563 |   4810 |
| http://dominuse.editarea.com/iw/adm/main/index.jsp            |   3736 |
+---------------------------------------------------------------+--------+
5 rows in set (15.37 sec)

mysql> select version();
+------------------+
| version()        |
+------------------+
| 4.1.15-debug-log |
+------------------+
1 row in set (0.00 sec)

Thank you for the feedback and bug report.

Thanks for your help, I'll forward the bug the debian mantainers of the package.

Alex