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
• Status: Verified
• Category: MySQL Server: Merge storage engine
• Severity: S3 (Non-critical)
• Version: 5.0.67/5.1/6.0/4.1
• OS: Linux
• CPU Architecture: Any

[21 Apr 2009 16:33] mike hamrick

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.

[23 Apr 2009 15:12] MySQL Verification Team

Results with and without primary key

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

[23 Apr 2009 15:25] MySQL Verification Team

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