MySQL Bugs: #19641: Merge table does not work properly with primary keys

Bug #19641	Merge table does not work properly with primary keys
Submitted:	9 May 2006 16:11	Modified:	29 Jun 2006 19:51
Reporter:	Predrag Ostojic	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Merge storage engine	Severity:	S2 (Serious)
Version:	5.0.18/4.1BK/5.0BK/5.1BK	OS:	Linux (Linux 9.2)
Assigned to:	Antony Curtis	CPU Architecture:	Any

Description:
Select statement on merge tables that include the '=' operator on primary keys (or unique keys) return only rows from the first table in the union, see example in the "how to repeat" section. It works OK with other operators like '>' and '>='. 

I tried to work around the problem by omitting the Primary Key clause from the merge table but then I got a weird result - no rows are lost but now the values from the last column are lost, see another example.

The above work around actually works for non-primary unique keys.

A possible work around that would work for primary keys is to replace the '=' condition with a pair of '<' and '>' conditions but I suspect this would have significant performance penalties.

How to repeat:
Example 1
========

mysql> create table t1 (i int, a varchar(16), primary key(i) );
Query OK, 0 rows affected (0.04 sec)

mysql> create table t2 (i int, a varchar(16), primary key(i) );
Query OK, 0 rows affected (0.04 sec)

mysql> insert into t1 values(1,"one");
Query OK, 1 row affected (0.00 sec)

mysql> insert into t2 values(1,"two");
Query OK, 1 row affected (0.00 sec)

mysql> create table tm (i int, a varchar(16), primary key(i) ) engine = merge union = (t1,t2);
Query OK, 0 rows affected (0.04 sec)

mysql> select * from tm;
+---+------+
| i | a    |
+---+------+
| 1 | one  |
| 1 | two  |
+---+------+
2 rows in set (0.00 sec)

mysql> select * from tm where i = 1;
+---+------+
| i | a    |
+---+------+
| 1 | one  |
+---+------+
1 row in set (0.00 sec)

mysql> select * from tm where i <= 1;
+---+------+
| i | a    |
+---+------+
| 1 | one  |
| 1 | two  |
+---+------+
2 rows in set (0.00 sec)

mysql> select * from tm where i < 2;
+---+------+
| i | a    |
+---+------+
| 1 | one  |
| 1 | two  |
+---+------+
2 rows in set (0.00 sec)

Example 2
========

mysql> drop table tm;
Query OK, 0 rows affected (0.00 sec)

mysql> create table tm (i int, a varchar(16) ) engine = merge union = (t1,t2);
Query OK, 0 rows affected (0.04 sec)

mysql> select * from tm;
+------+------+
| i    | a    |
+------+------+
|    1 | NULL |
|    1 | NULL |
+------+------+
2 rows in set (0.00 sec)

mysql> select * from tm where i = 1;
+------+------+
| i    | a    |
+------+------+
|    1 | NULL |
|    1 | NULL |
+------+------+
2 rows in set (0.00 sec)

Thank you for the bug report.

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

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

mysql> create table t1 (i int, a varchar(16), primary key(i) );
Query OK, 0 rows affected (0.00 sec)

mysql> create table t2 (i int, a varchar(16), primary key(i) );
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t1 values(1,"one");
Query OK, 1 row affected (0.00 sec)

mysql> insert into t2 values(1,"two");
Query OK, 1 row affected (0.00 sec)

mysql> create table tm (i int, a varchar(16), primary key(i) ) engine = merge
    -> union = (t1,t2);
Query OK, 0 rows affected (0.00 sec)

mysql> select * from tm;
+---+------+
| i | a    |
+---+------+
| 1 | one  | 
| 1 | two  | 
+---+------+
2 rows in set (0.01 sec)

mysql> select * from tm where i = 1;
+---+------+
| i | a    |
+---+------+
| 1 | one  | 
+---+------+
1 row in set (0.00 sec)

mysql> select * from tm where i <= 1;
+---+------+
| i | a    |
+---+------+
| 1 | one  | 
| 1 | two  | 
+---+------+
2 rows in set (0.01 sec)

Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/.

See http://dev.mysql.com/doc/refman/5.0/en/merge-table-problems.html

"A MERGE table cannot maintain uniqueness constraints over the entire table. When you perform an INSERT, the data goes into the first or last MyISAM table (depending on the value of the INSERT_METHOD option). MySQL ensures that unique key values remain unique within that MyISAM table, but not across all the tables in the collection."

and an example at http://dev.mysql.com/doc/refman/5.0/en/merge-storage-engine.html

"Note that the a column is indexed as a PRIMARY KEY in the underlying MyISAM tables, but not in the MERGE table. There it is indexed but not as a PRIMARY KEY because a MERGE  table cannot enforce uniqueness over the set of underlying tables."