MySQL Bugs: #44386: SELECT DISTINCT primary_key fails to return distinct values on MRG

Bug #44386	SELECT DISTINCT primary_key fails to return distinct values on MRG_MyISAM table.
Submitted:	21 Apr 2009 16:33	Modified:	23 Apr 2009 15:25
Reporter:	mike hamrick	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Merge storage engine	Severity:	S3 (Non-critical)
Version:	5.0.67/5.1/6.0/4.1	OS:	Linux
Assigned to:		CPU Architecture:	Any

Description:

I create three MyISAM tables, each containing 1 row with the same value for the primary key.  I then create a MRG_MyISAM table that merges them together.  I SELECT DISTINCT primary_key from the MRG_MyISAM table and I do not get distinct results.

How to repeat:
mysql> create table foo (id int primary key, mystring char(25)) ENGINE=MyISAM;
Query OK, 0 rows affected (0.01 sec)

mysql> create table bar (id int primary key, mystring char(25)) ENGINE=MyISAM;
Query OK, 0 rows affected (0.00 sec)

mysql> create table baz (id int primary key, mystring char(25)) ENGINE=MyISAM;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into foo values (1, 'foo');
Query OK, 1 row affected (0.00 sec)

mysql> insert into bar values (1, 'bar');
Query OK, 1 row affected (0.00 sec)

mysql> insert into baz values (1, 'baz');
Query OK, 1 row affected (0.00 sec)

mysql> create table qux (id int primary key, mystring char(25)) ENGINE=MRG_MyISAM UNION=(foo,bar,baz);
Query OK, 0 rows affected (0.02 sec)

mysql> select distinct id from qux;
+----+
| id |
+----+
|  1 | 
|  1 | 
|  1 | 
+----+
3 rows in set (0.02 sec)

Suggested fix:

I don't know for sure why this is happening, but I assume that MySQL is being clever and realizes that `id` is a primary key, so in most cases DISTINCT is redundant and is optimized out.  Maybe a special case for the MRG_MyISAM table is needed.

Results with and without primary key

Attachment: results-bug44386.txt (text/plain), 11.06 KiB.

Thank you for the bug report. Verified as described on current source servers and older release 4.1.22.