Bug #11044 SELECT DISTINCT on indexed column returns inconsistent results
Submitted: 2 Jun 2005 15:59 Modified: 14 Jul 2005 18:11
Reporter: Chris Back Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.0.6-beta OS:Linux (linux)
Assigned to: Timour Katchaounov CPU Architecture:Any

[2 Jun 2005 15:59] Chris Back
Description:
When selecting the distinct values for an indexed column, sometimes I get zero results, sometimes the correct results. For example, a table with this structure:
mysql> desc tblTest;
+------------+------------------+------+-----+------------+-------+
| Field      | Type             | Null | Key | Default    | Extra |
+------------+------------------+------+-----+------------+-------+
| varcharCol | varchar(255)     | NO   | PRI |            |       |
| dateCol    | date             | NO   | PRI | 0000-00-00 |       |
| intCol     | int(10) unsigned | NO   | PRI | 0          |       |
| doubleCol  | double           | YES  |     | NULL       |       |
+------------+------------------+------+-----+------------+-------+
 answers this query:
mysql> select distinct varcharCol from tblTest where dateCol = '2005-01-07';
Empty set (0.00 sec)

however, after dropping the primary key:
mysql> alter table tblTest drop primary key;
Query OK, 220 rows affected (0.09 sec)
Records: 220  Duplicates: 0  Warnings: 0

mysql> select distinct varcharCol from tblTest where dateCol = '2005-01-07';
+------------+
| varcharCol |
+------------+
| A          |
| AA         |
| AAPL       |
| ABC        |
...
| AYE        |
| AZO        |
+------------+
55 rows in set (0.00 sec)

which is what I would expect the first time. 
I also tested this against 5.0.3-beta with the same results.
I also tried both table handlers, innoDb and MyISAM, with the same results.

This query, which I believe should be another way of getting the desired data, also returns zero rows:
select varcharCol from tblTest where dateCol = '2005-01-07' group by varcharCol

Thanks for your help,

Chris Back

How to repeat:
I will send along the mysqldump for this table, repeat by following the same query chain as in the description.
[2 Jun 2005 16:01] Chris Back
gzip'd dump of sample table

Attachment: tblTest.sql.gz (application/postscript, text), 3.50 KiB.

[2 Jun 2005 16:01] Chris Back
the sample table has been uploaded, tblTest.sql.gz
[2 Jun 2005 23:24] Jorge del Conde
Thanks for your bug report.
[2 Jun 2005 23:24] Jorge del Conde
I was able to reproduce this using 5.0 from bk
[2 Jun 2005 23:46] Miguel Solorzano
The issue I found is that the key for some reason should be corrupted
when the table is created from the dump, after a check table the
query works. The log-error not shows nothing.

miguel@hegel:~/dbs/5.0$ bin/mysql -uroot test < /home/miguel/dbs/5.0/tblTest.sql
miguel@hegel:~/dbs/5.0$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 5.0.7-beta-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> select distinct varcharCol from tblTest where dateCol = '2005-01-07';
Empty set (0.01 sec)

mysql> check table tblTest;
+--------------+-------+----------+----------+
| Table        | Op    | Msg_type | Msg_text |
+--------------+-------+----------+----------+
| test.tblTest | check | status   | OK       |
+--------------+-------+----------+----------+
1 row in set (0.00 sec)

mysql> select distinct varcharCol from tblTest where dateCol = '2005-01-07';
+------------+
| varcharCol |
+------------+
| A          |
| AA         |
| AAPL       |
| ABC        |
| ABI        |
| ABK        |
[10 Jun 2005 13:46] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/25864
[13 Jun 2005 14:50] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/25932
[14 Jun 2005 9:52] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/25970
[13 Jul 2005 20:11] Timour Katchaounov
Pushed in 5.0.8
[14 Jul 2005 18:11] Paul Dubois
Noted in 5.0.8 changelog.
[27 Jan 2006 21:25] S Wagner
I am also experiencing this error. However my query does not involve using DISTINCT. Here is an example of a problem query. SELECT * FROM wicdatacom.wwticketmonitor w. This happens for other SELECT queries on other tables as well. 99.9% of the time I get the correct result. However once in awhile I get zero results, sometimes I also recieve a result set that is not zero, but less results than it should be. This became a big problem for me since I am building an ajax application that polls the database fairly rapidly so the incorrect result shows up often. I was able to resolve this by dropping the indexes on the problem tables. I am running MySql ver. 4.0.25
[2 Feb 2006 15:53] Timour Katchaounov
Mr. Wagner,

whatever the problem you mention is, it is not related at all to this bug.
This bug was due to a feature only present in v. 5.0 and later.
Please report the problem you encountered as a separate bug.
Thank you.