MySQL Bugs: #33653: keys in merge tables

Bug #33653	keys in merge tables
Submitted:	3 Jan 2008 15:02	Modified:	4 Feb 2008 5:34
Reporter:	Leszek Rychlewski	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: Merge storage engine	Severity:	S3 (Non-critical)
Version:	14.12 Distrib 5.0.22	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Looks like key order in merge table and sub-tables must match and mismatches are silently ignored.

How to repeat:
mysql> create table t1 (a int, b int, key (b), key(a));
Query OK, 0 rows affected (0.08 sec)

mysql> create table tm (a int, b int, key (a)) engine=merge union=(t1);
Query OK, 0 rows affected (0.06 sec)

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

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

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

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

Suggested fix:
make sure the order of the keys is identical in all tables (same as columns)

I've tested this on 5.0.44 and 5.1.22 with the same result/error message.

Please, check with a newer version, 5.0.45 at least. On 5.0.54 I've got:

C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -uroot -proot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.54-enterprise-gpl-nt MySQL Enterprise Server (GPL)

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

mysql> drop table t1;
Query OK, 0 rows affected (0.16 sec)

mysql> create table t1 (a int, b int, key (b), key(a));
Query OK, 0 rows affected (0.09 sec)

mysql> create table tm (a int, b int, key (a)) engine=merge union=(t1);
Query OK, 0 rows affected (0.08 sec)

mysql> insert t1 values (1,1);
Query OK, 1 row affected (0.03 sec)

mysql> insert t1 values (1,2);
Query OK, 1 row affected (0.03 sec)

mysql> select * from t1 where a=1;
+------+------+
| a    | b    |
+------+------+
|    1 |    1 |
|    1 |    2 |
+------+------+
2 rows in set (0.03 sec)

mysql> select * from tm where a=1;
ERROR 1168 (HY000): Unable to open underlying table which is differently defined
 or of non-MyISAM type or doesn't exist

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".