MySQL Bugs: #11044: SELECT DISTINCT on indexed column returns inconsistent results

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

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.

gzip'd dump of sample table

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

the sample table has been uploaded, tblTest.sql.gz

Thanks for your bug report.

I was able to reproduce this using 5.0 from bk

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        |

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

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

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

Pushed in 5.0.8

Noted in 5.0.8 changelog.

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

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.